# Too many small dimension tables

4 posters

Page

**1**of**1**## 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.

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.

**bandik**- Posts : 4

Join date : 2010-06-24

## Re: Too many small dimension tables

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.

## Re: Too many small dimension tables

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"?

**sa**- Posts : 1

Join date : 2011-05-19

## Too many small dimension tables

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

What will be recommended approach in this case ?

These two many Dimensions are not related.

Many thanks for the help.

Krupali

**bandik**- Posts : 4

Join date : 2010-06-24

## Re: Too many small dimension tables

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)?

## Re: Too many small dimension tables

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?

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?

**VHF**- Posts : 236

Join date : 2009-04-28

Location : Wisconsin, US

Similar topics

» Very Small Dimension Table

» Storing Date Keys in dimension tables versus fact tables

» Number of Columns in Fact Tables vs. Dimension Tables

» Using the Date Dimension for Summary Fact Tables or new specialized Month Dimension?

» Dimension Design with intermediate tables between fact and dimension

» Storing Date Keys in dimension tables versus fact tables

» Number of Columns in Fact Tables vs. Dimension Tables

» Using the Date Dimension for Summary Fact Tables or new specialized Month Dimension?

» Dimension Design with intermediate tables between fact and dimension

Page

**1**of**1****Permissions in this forum:**

**cannot**reply to topics in this forum