locked
table relation in power pivot RRS feed

  • Question

  • I've created a power pivot for a customer and address entity. pls find the table relation

    customer

    custskey  custid     custname
    ======  =====   =======
    1              11          a
    2              12          b
    3              13          c

    Fact
    Custskey Addrskey
    ====== ========
    1             1     
    2             2
    3             3

    address
    addrskey custid   addrid    addr_n
    ====== =====  =====   ======
    1            11         1             a1
    2            12         2             a2
    3           13          3             a3

    relationship using Fact (custskey) -> customer (custskey) & Fact (addrskey) -> address (addrskey). Here the fact table just to hold ID column. But when browse the pivot, the result is not as expected. drag custname & addr_n in rowlist (only dimension) its creating a Cartesian product. if i try the same design in olap, its working as expected. do i miss anything in power pivot design. is it differ from olap design?

    Result
    Custid    Custname   addr_n
    =====   =======   ========
    11           a               a1
                                    a2
                                    a3
    12           b               a1
                                    a2
                                    a3
    13          c                a1
                                   a2 
                                   a3                              

    How to get expected result without changing the model in power pivot...help is much appreciated.

    P.S. If i drag a key column (fact) into value field, then i can get the expected result. I read from other posts that minimum one value column is required in power pivot if we choose attribute from two different dimension.

    Thanks in advance.


    • Edited by msbimsbi Sunday, June 24, 2012 6:45 PM
    Saturday, June 23, 2012 6:51 PM

Answers

  • Msbimsbi -

    I don't believe this necessarily represents an issue in your model or relationships.  Technically, if no measure is specified, the Cartesian product correctly represents all possible intersections between 2 dimensions that are only related through the Fact table.  It just looks like pivot tables against PowerPivot models don't automatically hide empty cells until a measure is specified, while SSAS-Multi-dimensional cubes hide empty cells by default even when a measure isn't specified.  Probably related to the existance of a default measure in AS-Multi-dimensional cube. 

    To prove this out and test your PowerPivot model & relationships against the same data in a multi-dimensional AS cube, you can alter your pivot table settings against both cubes and see if you get the same results.  In Excel, go to Pivot Table - Options - Display tab - select the checkbox for "Show items with no data on rows".  This should produce the same behavior against both.  This is similar to setting "Show empty cells" when browsing a Multi-dimensional or Tabular cube in Management Studio. 

    Hope that helps.  If you still have issues, please post more details.


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com


    Sunday, June 24, 2012 9:59 PM
    Answerer

All replies

  • Msbimsbi -

    I don't believe this necessarily represents an issue in your model or relationships.  Technically, if no measure is specified, the Cartesian product correctly represents all possible intersections between 2 dimensions that are only related through the Fact table.  It just looks like pivot tables against PowerPivot models don't automatically hide empty cells until a measure is specified, while SSAS-Multi-dimensional cubes hide empty cells by default even when a measure isn't specified.  Probably related to the existance of a default measure in AS-Multi-dimensional cube. 

    To prove this out and test your PowerPivot model & relationships against the same data in a multi-dimensional AS cube, you can alter your pivot table settings against both cubes and see if you get the same results.  In Excel, go to Pivot Table - Options - Display tab - select the checkbox for "Show items with no data on rows".  This should produce the same behavior against both.  This is similar to setting "Show empty cells" when browsing a Multi-dimensional or Tabular cube in Management Studio. 

    Hope that helps.  If you still have issues, please post more details.


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com


    Sunday, June 24, 2012 9:59 PM
    Answerer
  • Thank you for your reply. If my understanding is correct, we should have related measure in value to slice multiple lookup table in pivot. Please let me know.

    Tuesday, June 26, 2012 6:19 AM
  • Based on your description, there's nothing wrong with the relationships you created.  The difference is the apparent default behavior of pivot tables against PowerPivot models vs UDM cubes. 

    Regardless, I'm assuming you're goal is to use the pivot table to slice up measures.  As soon as you drop a measure into the values area, the behavior should be as expected.


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com


    Wednesday, June 27, 2012 2:56 PM
    Answerer
  • Thank you for your reply...currently we have a model with seven dimension with no measure column...we have created a tactless fact table with only keys to join all these dimensions... Is it possible in power pivot to slice only dimension with out measure(key column) in value layout...because user wants to slice only the multiple dimension combination.. But due to cartesian product we are asking user to place the key column from fact in value layout which they are not accepting... We are thinking of moving to cube... Please let me know if it's possible with above approach in powerpivot thanks in advance
    Thursday, July 5, 2012 6:56 PM
  • Any model without measures defeats the purpose of analysis. On rows and columns you can have attributes but how would you link the row and column matrix?

    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog

    Friday, July 6, 2012 5:31 AM
  • Msbimsbi -

    Typically with factless facts, you would use a count measure to see the frequency of the intersections of the dimensions.  So in this case, you could create a simple COUNTROWS measure over one of the FK columns and use that in the values area.  Also, you can set the intersecting dimensions on different axes, and use the count measure to display the actual intersections and the missing ones.

    Another less elegant approach, would be to copy the dimension attributes into the factless fact using RELATED in calculated columns.  Then just plot those calculated columns on the pivot table axes.  This will take away the power and elegance of the dimensional model, but ultimately will satisfy your requirement of removing the cartesian behavior.  I'm actually embarassed to suggest this approach, being a dimensional model advocate, but it sounds like your users could care less about dimensional design anyway.  So just slamming all of your data into a data-dump table may be the way to go. 

    Hope that helps.


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com


    Friday, July 6, 2012 1:51 PM
    Answerer
  • Thanks again,...i know its a basic rule for any dimensional modeling with FACT & Dimension. It's an out of box question from user point of view. Will try to build a good dimensional model with user acceptance. Topic is very useful to me.

    Friday, July 6, 2012 3:35 PM