Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Heterogeneous Security Dimensional Model

2 posters

Go down

Heterogeneous Security Dimensional Model Empty Heterogeneous Security Dimensional Model

Post  mishka 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

Back to top Go down

Heterogeneous Security Dimensional Model Empty Re: Heterogeneous Security Dimensional Model

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

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

http://aginity.com

Back to top Go down

Heterogeneous Security Dimensional Model Empty Re: Heterogeneous Security Dimensional Model

Post  mishka 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

Back to top Go down

Heterogeneous Security Dimensional Model Empty Re: Heterogeneous Security Dimensional Model

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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