PowerPivot DAX query slow with one fact table but not with another

0
votes

I have 2 lookup tables; Members (83,000 rows) and Groups (2,500 rows).

There are 2 fact tables; GroupMembers (190,000 rows) and Metrics (650,000 rows).

I created two separate models:

1. Members, Groups and GroupMembers

2. Members, Groups and Metrics

Both models have only one calculated measure on the fact table (COUNTROWS). If I bring in the Members>MemberID on Rows and Filter on Groups>GroupID, using CountRows for values; model 2 performs super fast and model 1 is really slow.

Here are the DAX query results.

Model 1: enter image description here

Model 2: enter image description here

The only difference between the two fact tables is that GroupMembers only has unique combinations of Groups and Members, whereas the Metrics table has other columns so the Groups and Members combinations are not unique.

Both excel files can be found here: http://1drv.ms/1GdK1WK

Please help!

[EDIT] I did some further testing and found that if I duplicate the data in GroupMembers (i.e. load the same 190,000 rows twice so GroupID/UserID combination is not unique), the performance is great. Go figure! :)

I'm opening a support case with MS and will update this thread.

1
excelpowerpivotdax
What interface is that? – Petr Havlik
When you say you added it twice do you mean that you 'unpivot-ed' your data so you had more rows and fewer columns? – Jacob
Petr, you asking about the images I posted. That's SQL Server Profiler. If you "Enable PowerPivot Tracing for the current Excel session" from the PowerPivot tab > Settings window, it will create a trace file that you can open in SQL Server Profiler. – datamodel
Jacob, no not unpivot. I did a UNION ALL so that every row was added twice. – datamodel

1 Answers

0
votes

From Microsoft support team; apparently the UserID int values are too big for the query. They gave me a workaround to create an identity key column for the user table, and use that as the relationship between Users and UserGroups. I posted the workaround file in a new folder in OneDrive http://1drv.ms/1BEEmDJ

The support team will talk to the product team regarding the root issue and they'll investigate why large int columns are causing a performance issue.