Use of Mini-Dimensions

I am helping design a data warehouse using the Kimball methods as our basis. The overall topic of the DW is Literature and the we have one factless fact table that is a Products_FT. This table has several dimensions including one that is Product_Description. This table contains (along with a lot of other things):
Subject_Language
Content_Language
Category

All of these have a one to many relationship to the Product_Description. So my question is this, do I:
1. create row for each Product description with one Subject_Language, Content_Language, Category and have multiple rows for a Product?
2. create a Product_Description, Subject_Language, Content_Language, and Category Dimensions and put each of them into the Fact table as FKs?
3. Create the four dimension tables and use a bridge table to combine the various dimension keys?

If I use option #3 do I use the PK from the bridge table or the Product_Description table?

Hi - please can you add a bit more explanation on what your fact and dims actually contain e.g. what are your measures in your fact? I'm not clear what your product is - when you say your DW Topic is Literature I assumed that meant books but that doesn't seem to be the case if you say a product can have multiple subject/content languages, for example.
Thanks

I work for SIL International. We work worldwide with Translation and Literature needs in "minority" languages. These are languages with lower speaker populations, some of which do not even have a written language yet (about 2000 of the world's 7000 languages are still waiting for translation).
The fact table is factless. That is, the questions that it will answer are counts of the intersections of dimensions. For instance, "How many Languages are there in Africa that do not have translations programs started?"

Some of the deliverables from a Language project could contain multiple languages (Content_Language), and be about a country's 25 languages (Subject_Language), and the Category of the deliverable could fall into multiple areas (Children's Literature, Health Literature, etc). So a deliverable could have a one-to-many relationship with Content_Language, and Subject_Language, and Category). So an Ebola booklet written for Teenagers[2 categories] in 3 local Languages [3 Content_Languages] might have no subject_language.

So when I denormalize these deliverables I can do it in several different ways. I am just looking for some wisdom about what would be the best way to do this, multiple rows, separate dimensions, or use a bridge table to reduce the number of FKs in the fact table.

Thanks for asking.

Sounds more interesting than most projects I come across! Also sounds quite challenging so please take the following as suggestions to think about rather than necessarily solutions.

1. Start with your reporting requirements. The one that you mention involves identifying intersections that don't exist, in which case you are probably looking at having to build a coverage fact table as well as a product fact table (see Kimball's chapter on Promotions for further information if you need it) - so you'll need to ensure they both follow a common structure that allows you to answer the questions you have; given the number of independent and multi-value attributes you have you may end up having to build multiple coverage and product fact table - trying to force all these relationships together may not work or you end up with so many bridge tables that the model doesn't perform well or is very difficult to query

2. Think clearly about what the grain of your fact table(s) should be and once you have defined it, make sure you don't break it. If you can increase the grain of your fact table then you can reduce the number of bridge tables you may need.
For example, if most products have more local languages than subject languages it might make sense for the grain of the fact table to be product + local language (which removes the need for a local language bridge table) - or maybe it makes more sense, given the way your business thinks about its products, to have the grain of the fact table product + subject language (or something else).

3. I think Category is probably the most obvious candidate to implement with a bridge table - so I'd probably start with that in your design

Hope this helps even if it doesn't actually solve your problems?

Nick, thanks very much for your suggestions. Yes this is one of many data warehouses with "fun" designs but that have real meaning to the results.

If I am reading your suggestions correctly, bridge tables are the way to go. So I am down to my 2nd and 3rd idea. That is progress!

Question about bridge tables:
1. Can/Should they relate only two items, or is it good design to relate three item in one bridge table?
2. I have never seen a bridge table joined to another bridge table, does that happen in good designs? I think it sounds complicated.
3. Is it the PK of the bridge table that goes into the Fact table or (as I saw in one example) does the bridge relate two tables (Product and category) and it is the Product PK that goes into the Fact table?

Thanks for the input!

Hi - my thoughts on your questions:

1. A bridge table should only relate two different entities together
2. I've never seen bridge tables related to bridge tables either - it doesn't sound like a good idea and I wouldn't do it
3. It depends what the relationship is. If you are trying to relate a multi-valued Dim directly to a Fact (e.g. a Fact has many Categories) you need to use the Bridge Key in the fact. Alternatively you can look at creating group dimensions and relating Fact > Group > Bridge > Dim.
If you are relating Dims to Dims (even if the Dim key you join to is on a Fact rather than in the Dim) then the Key in the Fact is the Dim key. For example, a Fact has a Product SK and that Product has multiple categories then you have Fact.Product_SK > Bridge.Product_SK > Bridge.Category_SK > Category_Dim.Category_SK

Nick, again thanks!

I am going to have to think on your example in #3. It looks like you have a Fact attached to a bridge attached to a bridge that is attached to a dimension. But we said in #2 that bridge tables shouldn't be linked. Hmmmm.

I think what I am asking is do I:
Fact_Table(Product_FK) <-- Dim_Product --> Bridge_Product_Category <--- Dim_Category or
Fact_Table(Bridge_Product_Category_FK) <-- Bridget Product_Category <--- Dim_Product
<--- Dim_Category

Bridge.Product_SK > Bridge.Category_SK
Sorry - I meant by this that "Bridge" has two columns: Product_SK and Category_SK - not that there are 2 Bridge tables

Ahhh, OK now I get it. Thanks again!