Heterogeneous Security Dimensional Model

Hello, I inherited a DataWarehouse with a hybrid Model. We have a Security dimension table (PK is SecurityId) that has several different types of securities (i.e stocks, bonds, cash). Each of these types of Securities has its own table with specific attributes related to the type and a SecurityId FK pointing back to the Security dimension table. How can I make this model work in the Analysis Services Cube. I have read that it is possible to do, but still have not figured out how. I am very new to Analysis Services and Cube building. Any ideas are appreciated.

Thank you

I'm not sure what you mean by 'hybrid' model. What you describe is pretty standard for the situation you describe. Basically you have a primary dimension and sub-type dimensions.

I assume the FK relationship you are referring to is an indentifying relationship. It that is the case, all it is saying is the primary key value of the sub-type dimension is the same as the primary key of the primary dimension. From a star schema point of view it doesn't mean you join the two (i.e. snowflake), but rather, you can use the same fact table FK to join to both dimensions. It is important to construct the queries this way so that the optimiser recognizes the query as a star join and acts accordingly. (In more current versions of SQL Server).

From a SSAS point of view, when you are building a cube for all securities, you typically only include attributes from the primary dimension. Otherwise, you build specific cubes for specific types of securities, which includes attributes from that particular subtype dimension.

If you must include all attributes for all types, you can construct a view which brings the primary and subtype dimensions together using outer joins. It won't perform very well, and I'm not sure if having such a cube makes much sense.

Thank you very much for the information. It all makes sense and I will try both, creating an inclusive view and creating separate Cubes.

Thanks again.