Search code examples
ssasdata-warehouse

SSAS and calculated dimention from multiple other dims

Given 3 dimentions DimA and DimB and DimC with some DimSk, DimId and DimName as attributes the problem is defined as "add to cube new attribute which is calculated as":

NewAttr =
 CASE
  WHEN DimC.DimId IN (1, 2, 3) THEN 'A ' + DimA.DimName
  WHEN DimC.DimId IN (4, 5, 6) THEN 'B ' + DimB.DimName
 END

All dimentions are referenced directly from Fact by SKs. How would You solve this in multidim SSAS cube?

!Warning! Spoilers below - try to think about solution before reading about my!

My current approach is to calculate CROSS JOIN (~100x100x100) beetween Dims IDs. Then I can calculate composite NK for DimNew as ID ~ DimA.DimId+|+DimB.DimId+|+DimC.DimId. Then I can add this ID to Fact ETLs too, and build new ETL for new dim with NewAttr as expected. Then I can add in cube new dim and add new fact column and join them by ID/SK. Should work, or is there 10x better solution?

Final Fact can be like:

FactId,  DimASK, DimBSK, DimCSK, DimNewSK
or
FactId,  DimASK, DimBSK, DimCSK, NewAttr
  • second one is fast and dirty without NewDim on db - but fact can be processed partially so distinct can produce different NewAttr for same composite NK when DimNamein dims will change in time...
Solution

  • Okey dokey! Seems like if You have all SKs in Fact then add new dimention with all needed, then calculate NewAtr, and then make new Dim in cube with all SKs as composite key - its key step.

    Its important to not use DimNewSK from new dim from identity or something as key in cube.

    Composite Key allows to make simple regular relation to fact and works without additional dummy keys.

    Pictures are 1000s of words - its as simple as looks after all: relationship composite key