When you are working with dimensional modeling there are some situations where several dimensional attributes don’t make since to be in any other dimension. When this occurs you can combine them into one dimension call a Junk Dimension.
In this example you will see a person type dimension that only made since to be in a dimension together. This client has 4 different types of people in their system. The junk dimension will contain each possible combination of these person types.
To avoid having to reload the fact table every time a person type is added or changed, you want to add the logic to the views. You should always use views as your source for the DSV in your cube.
The Junk Dimension query look likes so:
Select 100000 PersonTypeSK, ‘Non A’ A, ‘Non ‘ B, ‘Non C’ C, ‘Non D’ D
Union all
Select 100001 PersonTypeSK, ‘A’ A, ‘Non B’ B, ‘Non C’ C, ‘Non D’ D
Union all
Select 100010 PersonTypeSK, ‘Non A’ A, ‘B’ B, ‘Non C’ C, ‘Non D’ D
Union all
Select 100011 PersonTypeSK, ‘A’ A, ‘B’ B, ‘Non C’ C, ‘Non D’ D
Union all
Select 100100 PersonTypeSK, ‘Non A’ A, ‘Non B’ B, ‘C’ C, ‘Non D’ D
Union all
Select 100101 PersonTypeSK, ‘A’ A, ‘Non B’ B, ‘C’ C, ‘Non D’ D
Union all
Select 100110 PersonTypeSK, ‘Non A’ A, ‘B’ B, ‘C’ C, ‘Non D’ D
Union all
Select 100111 PersonTypeSK, ‘A’ A, ‘B’ B, ‘C’ C, ‘Non D’ D
Union all
Select 101000 PersonTypeSK, ‘Non A’ A, ‘Non B’ B, ‘Non C’ C, ‘D’ D
Union all
Select 101001 PersonTypeSK, ‘A’ A, ‘Non B’ B, ‘Non C’ C, ‘D’ D
Union all
Select 101010 PersonTypeSK, ‘Non A’ A, ‘B’ B, ‘Non C’ C, ‘D’ D
Union all
Select 101011 PersonTypeSK, ‘A’ A, ‘B’ B, ‘Non C’ C, ‘D’ D
Union all
Select 101100 PersonTypeSK, ‘Non A’ A, ‘Non B’ B, ‘C’ C, ‘D’ D
Union all
Select 101101 PersonTypeSK, ‘A’ A, ‘Non B’ B, ‘C’ C, ‘D’ D
Union all
Select 101110 PersonTypeSK, ‘Non A’ A, ‘B’ B, ‘C’ C, ‘D’ D
Union all
Select 101111 PersonTypeSK, ‘A’ A, ‘B’ B, ‘C’ C, ‘D’ D
You will notice the Person Type key look similar to a binary number, and that is exactly what it needs to be. The 10,000 added on is to ensure it is an integer with all the numbers showing even with all the person types at zero.
Now the view for the fact table can be altered to use this dimension right away, without having to reload it. This is because you can tell which person type a person is based on the measures. If the person is missing a measure in that person type field then they are not that person type and vice versa.
Here is the part of the fact table query that calculates the person type key:
100000+
(Case when Person A Measure IS null Then 0 else 1 end) +
(Case when Person B Measure IS null Then 0 else 10 end) +
(Case when Person C Measure IS null Then 0 else 100 end) +
(Case when Person D Measure IS null Then 0 else 1000 end)
as PersonTypeSK
Now you can add the person type dimension to the DSV and you cube. Don’t’ forget to create the relationship in the DSV.
