Parallel Hierarchies in MS Analysis Server 2008
ma...@giombetti.com
I am new to Microsoft Analysis Server 2008 (and new to Analysis
Services in General) and I am facing the following problem. I want to
add a dimension to my OLAP Cube (in SQL Server Business Intelligence
Development Studio) based on the following table:
----- categories -----
Child , Parent
X, A
Y, A
Z, A
X, B
Y, B
Z, B
The table categories contains a parent child hierarchy with no primary
key.
The awkward thing is, that the values contain a parallel hierarchy (X
exists as child of A as well as of B).
Without parallel hierarchies I would just add a primary key on child
and specify the parent as parent attribute in the dimension
properties.
How should I proceed to Link the categories table to my fact table? Is
it possible at all to have parallel hierarchies in an MS Analysis 2008
OLAP Cube.
Thank you a lot for you help,
Marc
Marco Russo
You need to use a many-to-many dimension relationship.
Take a look at my paper here: http://www.sqlbi.com/manytomany.aspx
Just a tip: you should create a dimension with as many as X childs as
the leaf of your hierarchy, and then you should define a many-to-many
relationship between these child nodes and a dimension where each leaf
node is a single member that occurs only once (like X, Y, Z).
Marco Russo
http://www.sqlbi.com
http://sqlblog.com/blogs/marco_russo