Heterogeneous Security Dimensional Model

View previous topic View next topic Go down

Heterogeneous Security Dimensional Model

Post  mishka on Wed Sep 15, 2010 6:09 pm

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

mishka

Posts : 4
Join date : 2010-09-15

View user profile

Back to top Go down

Re: Heterogeneous Security Dimensional Model

Post  ngalemmo on Thu Sep 16, 2010 12:33 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Heterogeneous Security Dimensional Model

Post  mishka on Thu Sep 16, 2010 12:56 pm

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.

mishka

Posts : 4
Join date : 2010-09-15

View user profile

Back to top Go down

Re: Heterogeneous Security Dimensional Model

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum