When building a dimensional modelling solution, somewhere along the line you will almost certainly have at least one multi level hierarchy within one or more dimensions. A good example of this is a Product hierarchy, which might go:
Department->Sub Department->Type->Product
Typically for each level member you will have an ID (be it numeric or alpha numeric) and a Description. In the Adventure Works database there is a product hierarchy that goes: Department->Sub Department->Model->Product, so this seems like a nice example to share this tip.
Lets have a look at how you might store this data in your conformed dimension:
So here, Product 709 rolls into Mountain Bike Socks, Socks and Clothing.
Building this in a cube is simple, but sometimes not all reports are suited to cube and are better delivered using SQL.
Lets consider a requirement whereby the user wants a dump of all sales between dates and wants to filter by any level in the Product hierarchy (e.g. any Clothing, or just Socks).
There are many ways to provide this solution but there is 1 way I like.
Assuming that you would do this in a stored procedure, you might pass in each level ID for Category, Subcategory, Model and Product as a parameter and then determine what to filter on based on their selection (e.g. If they selection ‘All’ for Subcategory do not filter on this and so on. Doing this is complicated and gives me a headache, you might use 4 different almost identical SQL statements and have IF (SubcategoryIDSelection = ‘All’) then run this query, otherwise run another query. Confused? I am. You may use dynamic SQL to build up the query (which is a nightmare to maintain / understand what is going on).
The way I like to do achieve this is to store a unique hierarchy key as a string for each product. So for Product 709 we would have: ‘0003_0023_000013_000709’ as the ProductHierarchyKey (we pad the keys to make them sortable). This makes filtering on any level of the hierarchy very easy. For example if the user requests to see all sales for Clothing_Socks we pass into the procedure ‘0003_0023’ as the value we can then do a select and filter like this:
1: SELECT
2: p.*
3: FROM dbo.D_Product p
4: WHERE
5: p.ProductHierarchyCode like '0003_0023%'
Which will bring back products 709 and 710. As you can see, it doesn’t matter what level you pass into the procedure, it will always be the same query (no need to build any funky logic or use dynamic SQL). It also makes it easier to filter on multiple selections, e.g. the user might want to see sales for ‘Socks’ and ‘Road Frames’ together. So we would pass in ‘0003_0023,0002_0014’ then split the parameter by ‘,’ into a table variable (I will let you decide how to do this as there are many techniques) and then execute SQL similar to:
1: SELECT
2: p.*
3: FROM dbo.D_Product p
4: INNER JOIN @SelectedProducts sel
5: ON
6: p.ProductHierarchyCode like sel.Value + '%'
This approach, for me, is far simpler and easier to understand.
Hope it helps.