locked
PPS 2010 with PowerPivot fact and dimension for drill-down? RRS feed

  • Question

  • Hi, I have PowerPivot 2010, SharePoint 2010 and PerformancePoint 2010.

    I am thinking to use PowerPivot as PPS 2010 data-source. But as you know PowerPivot (vertipaq engine) has great feacture now that we don't need to create INDIVIDUAL dimensions for related key (i.e. OrderNumber & LineNumber) over to 'traditional' SSAS multidimensional cube. So if I connect PPS data-source to PowerPivot, will it create seperate dimension for OrderNumber & LineNumber and will it appear in drill-down feature in right-click context? (if it is then, what dimension will be appear in right-click drill-down).
    Any idea? Thanks.


    • Edited by KM IT Monday, November 21, 2011 12:38 AM
    Monday, November 21, 2011 12:35 AM

Answers

  • But the Order table is related, it is also considered a dimension.  In the Tabular (VertiPaq) setup every table is considered a dimension and can have measures like a fact table as well.  When you drill down you will see the Order table along with any columns that you have not hidden from the end user.

    Take a look at the white paper link I original listed, you will see that every table is considered a dimension, including fact tables.  That is what helps simplify this type of design over the multidimensional.

    Here is an clipping from the white paper from page 8:

    PowerPivot Model Representation in PerformancePoint

    Dimensions and Attribute Hierarchies

    Every table, including a Fact table, in the PowerPivot window is shown as a dimension in PerformancePoint. The columns in each table are represented as an attribute hierarchy under the respective dimension in PPS

     


    Dan English's BI Blog
    Monday, December 5, 2011 12:28 PM

All replies

  • Everything is treated as a dimension and if you do not hide it in the PowerPivot model then it will also be a hierarchy that you can drill down to in your analytical reports. The name of the dimension will be the same as the table name in the model that you have defined.

    You will also be able to use the decomposition tree functionality in your dashboards with PowerPivot source.  You will not be able to show details though, that functionality is not supported in the existing PowerPivot version (this will be available in the SQL 2012 release).

    PerformancePoint Services and PowerPivot for Excel (white paper).


    Dan English's BI Blog
    Friday, November 25, 2011 2:27 PM
  • Hi, Thanks for clarification. I can understand that it will use same name of dimension it defined in table name. for example in my PowerPivot I have below dims and fact tables:

    Dimensions : Date, Product, Coutry
    Fact : Fact table ORDER with colmun DateID, ProductID, CountryID, OrderNumber, LineNumber, DespacthNumber, DespatchLine, OrderStatus, OrderQuantity, OrderValue

    As you can see above DateID, ProductID and CountryID is join with  ORDER fact table and I hide this three colmuns in PowerPivot. However for other comulns such as OrderNumber, LineNumber, DespacthNumber, DespatchLine, OrderStatus, I don't have any related Dimension tables. So how's PerformancePoint handle this when you right click for drill-down or same inside in Dashboard Designer to create new report? Any idea? Thanks.

    Sunday, December 4, 2011 11:10 PM
  • Not sure what you mean by they are not related, because those columns are related to the three dimensions you have defined.  In PowerPivot every table can be considered a dimension and a fact.  Based on your relationships you have defined between date to order, product to order, and country to order, you will be able to slice each of the columns in order by these tables and also the columns defined in order.
    Dan English's BI Blog
    Monday, December 5, 2011 2:47 AM
  • Hi,  date to order, product to order and country to order is fine. But as mention I ONLY have three dimnensions : Date, Product, Country. All rest of colmuns such as OrderNumber, LineNumber, DespacthNumber, DespatchLine and OrderStatus doesn't have asscociate dimension table in PowerPivot. 

    In traditional "multidimensional" SSAS , you need to create dimension for OrderNumber, LineMumber, DepatchNumer,.... but in PowerPivot "vertipag", we don't need to create extra dimension(s) for these colmuns.

    So how's PerformamcePoint 2010 handle when you right-click for drill-down/up? decomposition tree? if there is no ralated dimension as extra colmunn (VARCHAR type) ONLY reside in Fact table. Any idea?


    • Edited by KM IT Monday, December 5, 2011 4:27 AM
    Monday, December 5, 2011 4:24 AM
  • But the Order table is related, it is also considered a dimension.  In the Tabular (VertiPaq) setup every table is considered a dimension and can have measures like a fact table as well.  When you drill down you will see the Order table along with any columns that you have not hidden from the end user.

    Take a look at the white paper link I original listed, you will see that every table is considered a dimension, including fact tables.  That is what helps simplify this type of design over the multidimensional.

    Here is an clipping from the white paper from page 8:

    PowerPivot Model Representation in PerformancePoint

    Dimensions and Attribute Hierarchies

    Every table, including a Fact table, in the PowerPivot window is shown as a dimension in PerformancePoint. The columns in each table are represented as an attribute hierarchy under the respective dimension in PPS

     


    Dan English's BI Blog
    Monday, December 5, 2011 12:28 PM
  • Thanks. Will go through white paper and post concern later on if any.
    Tuesday, December 6, 2011 11:31 PM