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.
