Search code examples
ssas

SSAS Dimension Design

I have a dimension Accounts which is connected to the Fact Table column Account via it's Primary Key.

All well until here. What I want to do is connect a separate table which only has a subset of the Accounts that the Fact table has and thus makes the cube process fail when I just add it to the dimension.

Example:

Table Design

The table Subset of Accounts does not contain all of the Accounts that the Fact Table has.

Attribute 3 and Account No is basically a new hierarchy I want to create.

Which is the correct way to go about this? Should I just create a dummy Attribute 3 and move Attribute 3 and Attribute 4 to the Accounts table? That's what I've been doing so far but I wanted to see if there is a better way.

Solution

  • You can create a new field called AccountNo2, and relate it to the table that contains the subset. That table must have a record whose key is for example an empty string, Atribute3 and Atribute 4 a text that indicates 'Not related', to relate the records of the fact table whose field Account No does not exist in the table of subsets. While loading the fact table, the Account No field must contain an empty string for these records. This can increase the load time of the fact table during the ETL process but reduces its size.