PowerPivot performance issue with normalized model

[cr731]

I'm having a performance issue with my data model and trying to figure out if it's just a bad design or if there is some solution that I'm missing.

My model consists of a fact table containing Customer Code, Product Code, Value Type (i.e. Volume, Revenue, Cost), Value, and a few attributes (i.e. market, geography, etc.) like below

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Product Code
[/TD]
[TD]Customer Code
[/TD]
[TD]Value Type
[/TD]
[TD]Value
[/TD]
[TD]Market Code
[/TD]
[TD]Geography Code
[/TD]
[/TR]
[TR]
[TD]Prod1
[/TD]
[TD]Cust1
[/TD]
[TD]Revenue
[/TD]
[TD]100
[/TD]
[TD]AA
[/TD]
[TD]US1
[/TD]
[/TR]
[TR]
[TD]Prod2
[/TD]
[TD]Cust1
[/TD]
[TD]Revenue
[/TD]
[TD]200
[/TD]
[TD]BB
[/TD]
[TD]US2
[/TD]
[/TR]
[TR]
[TD]Prod3
[/TD]
[TD]Cust2
[/TD]
[TD]Revenue
[/TD]
[TD]50
[/TD]
[TD]CC
[/TD]
[TD]US3
[/TD]
[/TR]
[TR]
[TD]etc.
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


This table is 1.9 million rows.

Then, I have related dimension tables, relating to the Customer, Product, and each Attribute column of the fact table.

The largest of the dimension tables are Customer (6,000 rows) and Product (1,400 rows) - the rest are only a few hundred rows.

I have measures written to sum based on the Value Type, for instance

= CALCULATE( SUM( Fact[Value] ), Fact[Value Type] = "Revenue" )

Now, if I drag in my customer from the Customer dimension table into a pivot, it calculates my measures near instantly.

If I then add Product from the Product table, it completely hangs and runs out of memory or else takes an excessively long time to run (minutes, way too long for an average user to wait).

I assume this is because PowerPivot is calculating for every possible combination of Customer x Product (6,000 x 1,400 = 8.4M combinations).

This much be a common issue with models like this and wondering how do I overcome this? I prefer not to flatten the entire model into one table as I have many dimension levels that are much cleaner to view on their own. But as it stands, it basically is inoperable if trying to view Customer + Product combination level of detail.

Am I missing something here?

Thanks

 

[Matt Allington]

Are you saying that you are adding product to the pivot table while customer is still in the pivot? If so, Power Pivot is not your problem. The problem is you are asking Excel to render 8.4 million values into a sheet.

The principle is to only render the numbers you want to look at instead of extracting them all just in case you might want to look at them later. It is a bit like you going to the web and searching for a phone number, but the browser tries to send you every phone number in the world. That will obviously take a lot longer.

your data model design looks excellent to me.

 

[cr731]

Thanks for the response.

I'm not actually expecting Excel to return 8.4 million rows - there are 90,000 unique combinations of customer/product in the fact table, but within that, there are 6,000 unique customers and 1,400 unique products, so that's where I was thinking it was trying to calculate every possible combination since I'm adding the fields from the customer/product tables, not the fact table (based on reading this: AutoExist, Cross Table Filtering, and You - PowerPivotPro).

I do agree with you that the purpose of the pivot is to provide summary level information, I'm just trying to anticipate what a user might do and want to avoid having the file crash.

 

[Matt Allington]

OK. But the point is that it is the rendering of the answers that is the issue, not Power Pivot. For a demo, write this measure and put it in a pivot table (with nothing on rows or columns)

=sumx(summarize(fact,dimCust[customer number], dimProduct[product code]),fact[value])

This formula will iterate over every possible combination of customer and product (8 million rows) and add up the value column. See how long it takes when it is only asked to render a single value as the result.