What is the best way to model a ragged hierarchy in SSAS for both ROLAP and MOLAP?

I'm currently trying to model a ragged hierarchy in SSAS and I'm stuck.

First, I was trying to model it like so:

ItemKey,Level0Key,Level1Key,Level2Key
Item1,Lvl0-1,Lvl1-1,Lvl2-1
Item2,Lvl0-1,Lvl1-1,Lvl2-1
Item3,Lvl0-1,Lvl1-1,Lvl2-2
**Item4,Lvl0-1, , **

Where the last line in this example had blanks for the "missing" levels.

Here, ROLAP managed to interpret the hierarchy okay, but MOLAP mode wound up mis-classifying members from the fact table, so the aggregations were off.

Next, I changed it to this based on the AdventureWorksDW example SalesTerritory dimension which was a ragged hierarchy:

ItemKey,Level0Key,Level1Key,Level2Key
Item1,Lvl0-1,Lvl1-1,Lvl2-1
Item2,Lvl0-1,Lvl1-1,Lvl2-1
Item3,Lvl0-1,Lvl1-1,Lvl2-2
**Item4,Lvl0-1,Item4,Item4**

I'm taking advantage of the HideIfOnlyChildAndSameNameAsParent to hide the members.

Now MOLAP's numbers line up, but in ROLAP land, I'm having major slowdowns because level 1 of my hierarchy has about 10000 members - SSAS goes out to lunch on an expansion.

So obviously I'm missing something, but I haven't seen many examples on the "right" way to do a ragged hierarchy.

Thanks for any answers.

Solution

Generally, "ragged hierarchy" is an immediate translation to "parent-child" for me. You'd want it to go like so:

ItemID    ItemKey    ParentID
1         Lvl0-1     null
2         Lvl1-1     1
3         Lvl2-1     2
4         Lvl2-2     2
5         Item1      3
6         Item2      3
7         Item3      4
8         Item4      1

SSAS will generate this Parent/Child for you once you tell it what the ParentID is. Rather automagic. Also, here I used ItemID as a surrogate key, and used the ParentID which pointed to that. There are a long list of reasons to use integers, but for Parent/Child, what you should do is use the ID as the Key column, and then add ItemKey as an attribute/relationship to said key column. You can then order Item (missing from this schema) by Attribute Key and select ItemKey. You can also choose whether or not to show that property to the user.

SSAS, by default, assumes that a null or self-referential parent key is a root node. I generally use null because they're easier for me to see when I'm scrolling through records. But, this is a preference thing.