Using MS SQL Server Analysis Services to create cubes on hierarchical data

Question

I have a hierarchy, like an ProductDimension hierarchy. Currently, I query this table using the Nested Sets methodology. I write the usual star-schema joins to get a time-series report of groups of Products (Batches, CountryOfOrigin, ProductLine, etc.).

I'm currently investigating using SSAS cubes- but can a cube model this?

Are there any resources that would help with the creation of a cube based on hierarchies?

Answer 1

Sounds like a good use for a parent-child relationship in a dimension?

Like the standard Employee dimension, where each employee is 'beneath' their boss in the hierarchy. A quick example is here: http://technet.microsoft.com/en-us/library/ms174846.aspx

In this dimension table, the ParentOrganizationKey column has a foreign key relationship with the OrganizationKey primary key column. In other words, each record in this table can be related through a parent-child relationship with another record in the table. This kind of self-join is generally used to represent organization entity data, such as the management structure of employees in a department.