Recursive hierarchies or non recursive, pros and cons
Posted by thomasivarssonmalmo on July 13, 2008
- 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.
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.