locked
PowerPivot support hiearchies RRS feed

  • Question

  • Hi,

    I'm a beginner with PowerPivot and I think to use it for querying my dbs and dwhs.

    Does PowerPivot support an hierarchy, fe year + quarter + month?

    In particular I need to implement an hierarchy about the accounts of a company.

    Thanks

    Friday, May 13, 2011 10:39 AM

Answers

  • Psorca,

    In the current release, there is no support for hierarchies in PowerPivot. Beware also that in PowerPivot there is also no need for natural hierarchies, as it is currently in SSAS, slicing is always superfast even on non natural structures. Nevertheless, you need to take columns from tables one by one, there is no concept of hierarchy at all.

    For what concerns account hierarchies, they are normally seen as parent/child hierarchies and, even if P/C can be handled in PowerPivot (see http://sqlblog.com/blogs/alberto_ferrari/archive/2011/02/15/powerpivot-and-parent-child-hierarchies.aspx) they are a real pain in the neck, since they need advanced data modeling and DAX skills to be implemented. Thus, you can implement them, but don't expect it to be easy at all. :)

    In the next version hierarchies will be there, even if up to now no detail has been disclosed about how they will work.


    Alberto Ferrari
    Friday, May 13, 2011 11:19 AM

All replies

  • Psorca,

    In the current release, there is no support for hierarchies in PowerPivot. Beware also that in PowerPivot there is also no need for natural hierarchies, as it is currently in SSAS, slicing is always superfast even on non natural structures. Nevertheless, you need to take columns from tables one by one, there is no concept of hierarchy at all.

    For what concerns account hierarchies, they are normally seen as parent/child hierarchies and, even if P/C can be handled in PowerPivot (see http://sqlblog.com/blogs/alberto_ferrari/archive/2011/02/15/powerpivot-and-parent-child-hierarchies.aspx) they are a real pain in the neck, since they need advanced data modeling and DAX skills to be implemented. Thus, you can implement them, but don't expect it to be easy at all. :)

    In the next version hierarchies will be there, even if up to now no detail has been disclosed about how they will work.


    Alberto Ferrari
    Friday, May 13, 2011 11:19 AM
  • Hi Aberto,

    I've read your article and implemented the first version of your recursive CTEs and speficied them as a query in PowerPivot. Before to use the following version, I want to understand how I can apply these CTEs to my fact table similar to your Invoices table in the article, to show the measure as a multilevel parent-child hierarchy. Do I need to create a relation between my fact table and the query with recursive CTEs? In my fact table I have the NodeID. I'm a beginner with PowerPivot.

    Thanks

    Monday, May 16, 2011 9:02 AM
  • Psorca,

    Yes, there should be a relationship between the fact table and the hierarchy, based on the nodeId. If I might suggest, it would be better if you start implementing the solution just by copying the examples and then, after you got it working, you adapt it to your database. Otherwise, you will need to modify the formulas and - being a beginner - you can make any sort of naive mistake that would distract you from the main point of the article, i.e. understanding P/C in PowerPivot.

    It is always hard to decide whether to provide downloadables with posts, so to make readers' life easier but I believe that starting from an empty workbook and repeating the steps one by one is the best way to learn a technique. Thus, my decision not to make workbooks available and force readers to repeat the steps. :)

     


    Alberto Ferrari http://www.powerpivotworkshop.com
    Monday, May 16, 2011 9:15 AM