PowerPivot and DAX papers,posts,FAQs,webcasts,samples

In SAP financial systems it's very common to use hierarchies (cost center hierarchies, profit center hierarchies, G/L account hierarchies, etc.). The SAP reporting tools provide automatic subtotals for the various levels in the hierarchies.

It's not clear to me how I can accomplish this in PowerPivot.

Does anyone have any thoughts on how to model/implement hierarchies in PowerPivot? If so, please share them. That would be great and much appreciated.

Thanks,

---Hugo

I would have two tables:
1) a fact table with one field containing the bottom level of the hierarchy
2) a separate dimension table with your complete hierarchy

Then create a relationship between the two tables on the common field containing the bottom level of the hierarchy.

For an alternate hierarchy structure, create an additional dimension table and again link to the fact table on the bottom level of the hierarchy.

ruve1k is totally right - his described approach will show related attributes under one table and this kind of helps user to understand that there might be some relationship between these attributes. Just make sure you hide key fields from the fact table in the pivot view.

PowerPivot does not have hierarchies. But when you drop any number of attributes to rows or columns area, you see results that are kind of hierarchy that depends on your data. If you will drop in country, then state into rows area, you will see "kind of hierarchy".

For example you have 2 independant columns - Country and State/Province. When you will drop then into Rows area, then you will see data state names under US country and Province names under Canada country if your fact data is correctly defined.

Well I just build a PowerPivot on the test database IDES from SAP on the Revenue area. Hierarchies in Time (off course), Material, Customer and others work fine.

In the Dashboard you see a hierarchy in the Distribution Channel Pivot Table.

Grtz, Frits