CODEWITHSUNDEEP
sql-serverssasanalysisbusiness-intelligence
Dhaval Faria
Dhaval Faria

Reputation: 61

Analysis service create recursive hierarchy

I have following table:

CatId    CatName    parent     CatId

1        Category   1           NULL
2        Category   2           NULL
3        SubCat     1           1
4        SubSubCat  1           3
5        SSSubCat   1           4

In Analysis Service I want to create Hierarchy in dimension such that it allows me to drill down till N Level.. Currently I am able to do it only 2 levels.. Category and Sub Category.. but I would like to go till N level if N level is not possible atleast till 4-5 levels.

Upvotes: 3

Views: 5152

Answers (2)

bplantes
bplantes

Reputation: 21

In AdventureWorks, the Employee dimension has an example of this. Assuming your category is on your fact table:

  • Set your ParentCatID to be a FK of CatID in the DSV
  • Reference your Parent attribute as the Parent Attribute type in the Dimension Hierarcy manager
  • Add the attribute into your hierarchy

The nested levels should be able to be browsed in your Category Hierarchy.

Upvotes: 2

Scott L Holmes
Scott L Holmes

Reputation: 111

The type of Hierarchy you appear to be attempting is called a Parent-Child Dimension. SSAS will use recursive joins to "explode" your data into a tree shape.

But your table as you describe it is a little confusing. So I am offering a solution that requires you to rethink your table a little. A classic Parent-Child will have for each node (record) in the hierarchy:

  • A key (ID) for the node
  • The literal text (Name) for the node
  • A foreign key called the parent

In your example, the column labelled "parent" appears to be superfluous. The last column in your example (called "CatID") is what the Parent of the dimension usually looks like. If you consider that each record in the table is a "child", the parent of the child acts as a pointer back to some record that owns or contains that record. At the highest level in the hierarchy, records will have no Parent so the Parent column is set to NULL.

Rename the second "CatID" to "parent" and remove or rename the original column called "Parent" (you don't need it). If you tweak your table as I suggest, you should check that the highest level is correct by running the following query:

SELECT CatID, CatName, parent FROM mytable WHERE (parent IS NULL)

Then to get the next level down run the following query:

SELECT HighestLevel.CatID, HighestLevel.CatName, HighestLevel.parent, Level2.CatID AS Level2ID, Level2.CatName AS Level2Name
FROM mytable AS HighestLevel
INNER JOIN mytable AS Level2 ON HighestLevel.CatID = Level2.parent
WHERE (HighestLevel.parent IS NULL)

Note the recursive INNER JOIN. Run at least one more query to view another level down to verify that the keys are "expanding" the way you expect:

SELECT HighestLevel.CatID, HighestLevel.CatName, HighestLevel.parent, Level2.CatID AS Level2ID, Level2.CatName AS Level2Name, Level3.CatID AS Level3ID, Level3.CatName AS Level3Name
FROM mytable AS HighestLevel
INNER JOIN mytable AS Level2 ON HighestLevel.CatID = Level2.parent
INNER JOIN mytable AS Level3 ON Level2.CatID = Level3.parent
WHERE (HighestLevel.parent IS NULL)

You could keep adding levels as necessary to convince yourself that the data is correct. This is essentially what SSAS is doing when it builds a Parent-Child hierarchy.

Finally, you'll add this table to the DSV and create a Parent-Child Dimension. That's a bit more complicated and this looks like a great starter article. SSAS will keep adding levels as necessary until it runs out of data.

Upvotes: 2

Related Questions