Learning SSAS with Me –Junk Dimension

Introduction

There are lot of concepts the SSAS have. Understanding the concept makes us easy to design an effective multi-dimensional model. In all of our article, we are trying to represent them as we understand it as a developer and trying to provide simple example that make the thing easier to understand.

The example that we provide may not be related to real world example but its helps to understand the situation and provide suitable solution.

There are another concept of Dimension is call the Junk Dimension. Which is playing very important role when designing multi-dimensional database.

In this article we are trying to understand the usages of Junk dimension in case of multi-dimensional database and how it is created. 

Understand Junk Dimension

In our point of view, SSAS have different type of solution. Understanding the problem is the main thing to get a suitable solution. What we want to mean is, the solution may differ depending on case scenario.

First we have to understand what a Junk dimension is.

Our multi-dimensional model have one fact table and several Dimension Table.

The Dimension table joined with Fact table by foreign key relation (simple Star schema concept). For each and every records of the Fact Table the dimension table makes the relation.

Think about a situation where we have huge number of Dimension table and we find that some of the dimension table are very small in size and containing only flag information (Like: Male – M, Female –F, Married, single etc.)  .

It not only increases the number of dimension table (increasing the maintenance cost of Dimension) but also decrease the performance of Data Retrieval from Cube.

In the example scenario, by analyzing our multi-dimensional model we understand that we have 5 (five) Dimension that have carry only flag (Yes-No, m/f, true/false) type values and we want to combined all the 5 (five) Dimension into a single Dimension.

After combining we want to make join this single Dimension to our Fact table.

Combining the Small/Flag based dimension and make a single dimension is called the Junk Dimension.

We must keep it in mind that the number of records (data volume) in the Dimension that are the part of Junk Dimension must be low. So we have to choose the dimension carefully.

[ Junk Dimension ] = [ Low data Volume ]+ [ Flag like value ]

Let’s Take an Example

To understand it properly let’s take an example.

We identified that in our multi-dimensional model, we have 2 Dimension object that have flag like values with low data volume.

Dimension Table:DIMSex

ID	Sex
0	U
1	M
2	F

Dimension Table:DIMMaritalStatus

ID	Status
1	Married
2	Unmarried

Both the Dimension table is joined with our Fact less Fact Table.

Fact Table:FACTEmployeeDtls

EmpID	Sex	MaritalStatus
1	1	1
2	1	2
3	1	1
4	1	2
5	1	1
6	2	1
7	2	1
8	2	1
9	2	1
10	0	2

Now we want to use both of our Dimension Table (DIMSex, DIMMaritalStatus) and make it a single Table (DIMSexMaritalStatus).

How to make a single Dimension

There are two type of process or methodsinvolved to make the single dimension.

Process - 1 [By using the CROSS Joinwithin Dimension Table]

We have to join both the dimension table in every way of possibilities with a Surrogate Key.

Dimension Table:DIMSexMaritalStatus

KeyID	SexID	Sex	MaritalID	MaritalStatus
1	0	U	1	Married
2	0	U	2	Unmarried
3	1	M	1	Married
4	1	M	2	Unmarried
5	2	F	1	Married
6	2	F	2	Unmarried

Formula is:

If Dimension-A have m records and Dimension-B have n records, then the outcome of every possible combination is m X nnumber of records.

FROM[ DIMENSION Table 1 ]
CROSS JOIN[ DIMENSION Table 2 ]

Process - 2[By using the Fact Table]

This process comes under picture when the CROSS join of dimension have huge number of records. Then we are going to make Left Join with Fact Table to get the specified output.

FROM[ FACT Table ]
LEFT JOIN[ DIMENSION Table 1 ]
LEFT JOIN[ DIMENSION Table 2 ]

Working with Fact Table

Now we use a Lookup to replace the Surrogate Key of newly making Dimension to our fact table.

In our case the Lookup key should be

[DIMSexMaritalStatus].[SexID] = [FACTEmployeeDtls].[Sex]

AND

[DIMSexMaritalStatus].[MaritalID] = [FACTEmployeeDtls].[MaritalStatus]

Fact Table:FACTEmployeeDtls

EmpID	Sex	MaritalStatus	KeyID
1	1	1	3
2	1	2	4
3	1	1	3
4	1	2	4
5	1	1	3
6	2	1	5
7	2	1	5
8	2	1	5
9	2	1	5
10	0	2	2

Joining of Dimension and Fact

DIMSexMaritalStatus	(PK)		(FK)	FACTEmployeeDtls
	KeyID	JOIN	KeyID

Hope you understand it.

Posted by: MR. JOYDEEP DAS