I want to create a Junk Dimension holding for example the following attribute, can you help me out there is little to read about junk dimensions and SSAS
C and D points to colum A in the fact table
1 and ) point to column D in the fact table.
From a design point of view you can have a look at Kimball here where the junk dimension is reffered to as a mystery dimension. It applies to SSAS 2005.
In SSAS 2005 they are refered to as fact table dimensions. When you run the cube wizard you can mark the fact table / measure group as both a fact table and dimension table and the wizard will let you pick the attributes in the fact table for the junk dimension.
You can only have one fact table dimension in each cube.
It is also possible to build named queries on top of fact table attributes and create them as dimension tables with a one-to-one relation with the fact table.
The other approach is to put your "dimensions" into columns in the junk dimension and create rows that are the cross product.
ID A ColA B ColB
== == ====== == ======
1 C Credit 1 True
2 C Credit 0 False
3 D Debit 1 True
4 D Debit 0 False
Then when you load your fact records you join colA from the fact table to ColA from the junk dimension and do the same for ColD and insert the matching ID. Then you can build your junk dimension as normal in SSAS and have attributes for ColA and ColD.
How does the performance of a Fact Table Dimension compare to the Junk Dimension (as proposed by Darren)? Processing-wise and query-wise? Most importantly does the Fact Table Dimension approach (especially when there is a one-to-one relation to the Fact Table) have a performance penalty -- or is SSAS in the background clever?
- Edited by A_B_M Monday, March 04, 2013 4:01 PM