Too many small dimension tables

I am facing a design challenge. I am designing a Dimension model for a new BI platform for an ODS (OLTP) sysetm.

The operational sytem (source here of data ) has about 10 smaller lookup tables - of totally unrelated to each other and number of records are less than 8. Each of the fact tables links to about 2 - 5 of these small dimensions.

I am thinking of having a Junk Dimension instead of maintaing separate 8 dimension tables. The structure of this junk dimension would be
DimKey, DimType, DimTypeID DimValue
1 , Dim1, 1, 'ABC'
2, Dim1, 2, 'ACB'
3, Dim2, 1, 'BBB'
4, Dim2, 2, 'CCC'

Dim1, Dim2 represent the smaller lookup tables (which only contain TypeID, Value).

Is this a recommended approach ? Fact tables will have an individual column for each of these smaller dimensions. The only difference is "instead of maintaining smaller mini dimension tables - flatten them in a single table as illustrated above."

Has any of you had the smae situation ? Is there a design tip I can refer to for this situation. Any thoughts are extremely appreciated.

Is this a recommended approach ? Fact tables will have an individual column for each of these smaller dimensions. The only difference is "instead of maintaining smaller mini dimension tables - flatten them in a single table as illustrated above."

No. Logically you are still maintaining 8 tables and nothing has changed on the facts.

A junk dimension contains combinations of attributes on a single row. The reason to consider a junk table in your situation is to reduce the number of FKs on the facts. If it doesn't make sense to combine some of the attributes, then keep things as they are. The table you propose only confuses the issue.

bandik wrote:I am thinking of having a Junk Dimension instead of maintaing separate 8 dimension tables.

We have a dimension in the form you describe and I regret that the consultant who suggested it got his way. It slightly simplifies ETL development but tends to encourage the creation of more small dimensions because they're so easy to slot into it. (The path of least resistance.)

Do you have a good reason to not want 8 extra tables, or is this just a quest for "tidiness"?

Its just for the sake of convenience. Too many dimension tables (around 10) with record count less than 5 - is not clean. There will be 10 foreign keys to these dim tables.

What will be recommended approach in this case ?

These two many Dimensions are not related.

Many thanks for the help.

Krupali

bandik wrote:Its just for the sake of convenience. Too many dimension tables (around 10) with record count less than 5 - is not clean. There will be 10 foreign keys to these dim tables.

Some questions...
How do the number of foreign keys get reduced if you use 10 different rows in one table (as opposed to 1 row in 10 tables)?

Why would 10 dimension tables not be a clean design?

With the cardinality so low, why would it matter if the values are unrelated (in a junk dimension)?

When dealing with unrelated items I can appreciate your reluctance to put them together in a junk dimension, but it is like the kitchen junk drawer that is full of a bunch of miscellaneous items in one handy place. If you do a junk dimension by combining 3 dimensions with 5 rows each you end up with 5*5*5=125 rows (assuming you populate all possible combinations.)

As already recommended, either keep the 8 individual dimensions (perhaps with a 8-bit tinyint keys to minimize space in the fact table) or else group them together into one or more miscellaneous junk dimension(s).

You indicated some fact tables would use only 2 of your small dimensions while other might use up to 5. Is there any kind of grouping that would make sense when collecting them into junk dimensions? In other words reduce your 8 individual dimensions to to maybe 2 or 3 junk dimensions that have common useage patterns, so that most fact tables would only need to refer to one junk dimenson?