Junk Dimension in DWH

A junk dimension is a single table with a combination of different and unrelated attributes to avoid having a large number of foreign key in the fact table. In other word we can say that junk dimension is created to manage the foreign key.

Let’s see this Example.

We have a product fact table. This table has the high and low cardinality. This table is attached the n numbers of Small – small Dimension.  Below are the dimension tables.

Dim_Product_Type

Dim_Product_Type_ID	Product_Type
1	Sales
2	Purchase

 

Dim_Product_Quality

Dim_Product_Quality _ID	Product_Quality
1	High
2	Medium
3	Low

Dim_Expiry

Dim_Expiry_ID	IsExpired
1	Yes
2	No
3	NA

Dim_Tax_Information

Dim_Tax_Information_ID	Tax_Info
1	Tax Free
2	28 %
3	18%
4	12%
5	5%

Dim_Delivery_Mode

Dim_Delivery_Mode_ID	Delivery_mode
1	Dealer
2	Agent
3	Direct

These are some dimensions. If we don’t have the Junk dimension in that case we will store this information in the fact table as below

Fact_product

Product_ID	Dim_Product_Type_ID	Dim_Product_Quality _ID	Dim_Expiry_ID	Dim_Tax_Information_ID	Dim_Delivery_Mode_ID
1	1	1	1	1	1
2	1	1	1	1	2
3	1	1	1	2	2
4	1	1	2	2	2
5	1	2	2	2	2

To over coming with this we will create the Junk Dimension and here we will keep all primary of the small dimensions.

Our Junk Dimension looks like below.

Dim_Product_Junk

Dim_Product_Junk_ID	Product_Type	Product_Quality	IsExpiried	Tax_Information	Delivery_Mode
1	Sales	High	Yes	Tax free	Dealer
2	Sales	High	Yes	Tax free	Agent
3	Sales	High	Yes	28 %	Agent
4	Sales	High	No	28%	Agent
5	Sales	Medium	No	28%	Agent

Now Our Fact table look like below

Fact_product

Product_ID	Dim_Product_Junk_ID
1	1
2	2
3	3
4	4
5	5

In the fact Dim_Product_Junk_ID is show the Information product dimension for example Dim_Product_Junk_ID = 1 means

Ø  Product_type : sales

Ø  Product_Quality : High

Ø  IsExpired : Yes

Ø  Tax_Information : Tax Free

Ø  Delivery_mode : Dealer

Junk dimension has (Dim_Product_Type* Dim_Product_Quality * Dim_Expiry* Dim_Tax_Information* Dim_Delivery_Mode) Records in the table.

So total rows in this junk dimension is 2*3*3*5*3 =270 records are in our Junk dimension table.