Answered by:
table relation in power pivot
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 cFact
Custskey Addrskey
====== ========
1 1
2 2
3 3address
addrskey custid addrid addr_n
====== ===== ===== ======
1 11 1 a1
2 12 2 a2
3 13 3 a3relationship 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
a3How 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 SSASMultidimensional cubes hide empty cells by default even when a measure isn't specified. Probably related to the existance of a default measure in ASMultidimensional cube.
To prove this out and test your PowerPivot model & relationships against the same data in a multidimensional 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 Multidimensional 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 Edited by Brent GreenwoodEditor Sunday, June 24, 2012 9:59 PM
 Marked as answer by msbimsbi Friday, July 6, 2012 3:35 PM
Sunday, June 24, 2012 9:59 PMAnswerer
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 SSASMultidimensional cubes hide empty cells by default even when a measure isn't specified. Probably related to the existance of a default measure in ASMultidimensional cube.
To prove this out and test your PowerPivot model & relationships against the same data in a multidimensional 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 Multidimensional 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 Edited by Brent GreenwoodEditor Sunday, June 24, 2012 9:59 PM
 Marked as answer by msbimsbi Friday, July 6, 2012 3:35 PM
Sunday, June 24, 2012 9:59 PMAnswerer 
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 Edited by Brent GreenwoodEditor Wednesday, June 27, 2012 2:57 PM
Wednesday, June 27, 2012 2:56 PMAnswerer 
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 advanceThursday, 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 BlogFriday, 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 datadump 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 Edited by Brent GreenwoodEditor Friday, July 6, 2012 1:52 PM
Friday, July 6, 2012 1:51 PMAnswerer 
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