Thomas.I Microsoft BI & Analytics

A Site Dedicated to General BI Issues and MS BI Issues: Architecture and Technology

Recursive hierarchies or non recursive, pros and cons

Posted by thomasivarssonmalmo on July 13, 2008

Recursive dimensions in SSAS 2005 is supported only in the form of parent-child dimensions. You can check my previous entry about parent-child dimensions but shortly they rely on a single parent at the top of the dimension. The more complex form of recursive hierarchies allows several parents at the top and are called Bill Of Materials hierarchies. The later is not supported in SSAS 2005 unless you model it as many-to-many dimensions.
Lately I have met some developers that prefer to use parent-child hierarchies as a standard structure for all dimension in a data warehouse and in SSAS 2005. Even if Microsofts advice is to be careful with parent-child dimensions, like you can read in the SSAS 2005 "Performance Guide", some developers thinks that this is valid general design approach. Still they will never build a recursive hierarchy of the time dimension because even they know that you need named dimensions levels, like Year, Month and Date, in order for time calculations to work.
Let us have a look of how you can model a product dimension as a recursive structure and later the other way, non-recursive, or natural hierachies. This is a simplified version of a recursive model:
 In the product table you keep all records for all levels in the product dimension.  Members and parents are kept in the same table.  You have also a product level table that can tell on what level the different records are in the product table. The third table, ProductStructure keep the relations between all levels in the product dimension. The ProductStrucure table has a primary key and aforeign key relation between the ProductId and the ParentProductId, but if you keep several alternate hierarchies in this structure the foreign key constraint will not work.
What are the pros of this design?

  • You add new levels to the dimension without having to redesign the data model.
  • If you have gaps or null levels in a dimension a parent-child structure will allow different deep of levels within the same dimension.
  • Parent-Child hierarchies are designed for custom rollup or the case when you have facts with different granularity in the same fact table.

What are the cons?

  • In SSAS 2005 parent-child dimensions are exceptions that you should use with care and not as a standard structure.
  • If you would like to see this structure as a flat, single table dimension, you will need to be skilled in TSQL(If your datasource is SQL Server) and build, derived tables or self joins of the product table and the product structure table. Parent-child are complex strucures and each join of each table will perform worse than if you have the product attributes in a single or few tables.
  • For each level in the product dimension you will have to join the recursive table to itself and the product table if you would like to present this strucure as natural hierarchy.
  • Natural hierarchies will create better aggregations than parent-child hierarchies in SSAS 2005.
  • The product table forces you have to have the same number of attributes for all product dimension levels. Normally the leaf level of the product dimension have more attributes than the top levels. I have seen an additional attribute table added to this mess but it will only increase the numer of joins and I/O for each level you add in this model. It will get even slower and simple product dimension with 5000 records will takes minutes before it returns something. 

It think that the cons outperforms the pros by a very high magnitude.

How do you model the product dimension in a non recursive way? You can put all attributes in a single dimension table. If you have large dimension tables and frequent changes this will not be the best choice.  Here is a simple description that is a copy of the structure in the Adventure Works DW database.

Non recursive 

If you would like to add new parent attributes to the product table you will need to add a new key for that and new tables for levels above the product level. When the structure change you will need to add new tables. If you use a single product table for all attributes you add new columns.

When you write a query, with this last structure, you will only need to write standard joins and the report side of combining attributes will be much more simple, than with recursive hierarchies.

Keep you design simple and good enough.


5 Responses to “Recursive hierarchies or non recursive, pros and cons”

  1. Vidas said

    Link to this post was added to our site in the [SSAS Articles]/[Parent-Child] section.

  2. Patrik said

    HiI’m using a similar technique with parent-child relations. Another big "pro" for this solution is maintenance. Every "entity" is represented by similar groups of tables, in your case product has Product, ProductLevel and ProductStructure. The same three tables will exist for lets say customer. This makes the whole database look the same for every entity and the person maintaining will know how it works.In your con’s you say that the user will have to make many joins to make it flat and thats true but you can use a recursive query using a Common Table Expression. Using this you can use similar selects for different entities.ThanksPatrik Molin

  3. Thomas said

    "Named Value Pair" or "Entity-attribute-value model" is a data model for the Medical sector. It is a special case. I am an advocate of a normalized data warehouse and denormalized data marts that are the standard BI approaches. You will have to spend to many hours to get the data together again with the NVP approach. I would never recommend it and I guess that this is coming from developers with limited BI-architecture experience.

  4. Patrik said

    Just to clarify I’m only using this in the Data Warehouse and not in the datamarts. Dimensions in datamarts should be denormalized just as you say.

  5. Sam Kane said

    Here are this and some other articles on Parent-Child Dimension:

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: