Working with PowerPivot: Roll Your Own Hierarchies

Hierarchies, such as those you are used to seeing in Analysis Services, are not directly supported in PowerPivot.  Until it does, the crafty developer needs to roll their own.  Here are a few ways of accomplishing that task:

• When authoring the query used in the data connection, go ahead and denormalize its results into a single table.  For example, this query taken from the Adventure Works cube mashes the Geography hierarchy from the Geography dimension with the Reseller Sales Amount fact:

Read more...