Welcome, Guest
Username Password: Remember me
Please ask here your questions related to DAX and PowerPivot for Excel or PowerPivot for SharePoint. To post your question or reply you need to login first!
  • Page:
  • 1

TOPIC: Can PowerPivot handle "hierarchies"?

Can PowerPivot handle "hierarchies"? 1 year, 10 months ago #69

  • hdegroot
  • OFFLINE
  • Fresh Boarder
  • Posts: 1
  • Karma: 0
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

Re:Can PowerPivot handle "hierarchies"? 1 year, 10 months ago #70

  • ruve1k
  • OFFLINE
  • Fresh Boarder
  • Posts: 3
  • Karma: 0
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.
Last Edit: 1 year, 10 months ago by ruve1k. Reason: clarification

Re:Can PowerPivot handle "hierarchies"? 1 year, 10 months ago #71

  • Vidas
  • OFFLINE
  • Moderator
  • Posts: 65
  • Karma: 3
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.
Please click on the "Thank you" button for replies that are helpful!

Re:Can PowerPivot handle "hierarchies"? 1 year, 3 months ago #189

  • Luiindezon
  • OFFLINE
  • Fresh Boarder
  • Posts: 3
  • Karma: 0
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
Revenue.jpg
Greetz,
Frits Nagtegaal
www.newfrontiers.com
Building BI solutions on SAP without SAP BW
Last Edit: 1 year, 3 months ago by Luiindezon.
  • Page:
  • 1
Moderators: Vidas, dgosbell
Time to create page: 0.28 seconds
 

Varigence Vivid