PowerPivot Calculation

[lrobbo314]

I created a spreadsheet with 5-6 different pivot tables, each with its own pivot chart.

Then I started playing with doing the same thing only with PowerPivot.
So, I was able to add my tables to the data model and recreate my pivot tables and charts.
The only thing is that it is so much slower than the first method.
I am not dealing with a lot of data either. I'm talking about hundreds of records, not thousands.
If I hit "Refresh All" Excel will go for at least 10 seconds with the cursor turning into the thinking wheel and the status bar saying "Reading Data Hit ESC to Cancel".
Why is it doing this? I can't imagine it should be so slow. If I change the values on the slicer for the dates, it updates my charts in about a second or so, but it is still noticeably slower than the first option.
Maybe it has something to do with the table relationships. I don't know. I have a table for manpower and a table for traffic. Each has 3 relationships in total. 1 to a date table, 1 to a zone table, and 1 to a shift table. So, between the 2 tables there are 6 joins in total.
Does anyone have any thoughts about this?
Thanks in advance for the help. PowerPivot is pretty new for me.

 

[jafa1970]

Hopefully some folks with more experience will chime in but a couple of points:

1. How have you added data (tables) to the data model? Have you used linked tables or imported as a data connection?
--> If you used Linked tables, try starting a new Excel file and in PowerPivot using Get External Data > From Other Sources and use Excel table, then open the source file(s) and import each data table individually
2. Have you created measures using PPT or are you using implicit measures (ie dragging a number field from a table onto the Values area of the pivot)?
--> If using explicit (ie one you have created) measures, perhaps post the code here so it can be reviewed.
3. Are you using Refresh All from the PivotTable menu, or the PowerPivot window? One refreshes the pivots, one refreshes the data. If refreshing the data is slow with small tables, that sounds like a network connectivity issue. If refreshing Pivot tables, then I suspect (1) above is in play.

Cheers
Phil

 

[lrobbo314]

Thanks for the reply.
I added 2 tables that are in an access database. There are 3 other tables that I added that are on an excel sheet, but they are small tables. They are my tables with unique values actually.

I created measures, I think. That is to say, I made formulas in PowerPivot that I use in my pivot tables. They are in the calculation area. I think that's 2 hat measures are, right?

Having said that, I also dragged and dropped items into the values area that were just columns in my table.

I refreshed all from excel, which was what took a long time. It can't be network connectivity because thus is all on local drives.

I can post some of what's in the ppvt calculations tomorrow when I get to work.

Thanks again.

 

[jafa1970]

Based on all of that I probably cannot help you further, as you seem to have things set up as I would expect. Hopefully someone else will have some thoughts. Still post more detail when you get a chance though, the more info the better for people to offer suggestions.

Cheers

 

[scottsen]

Suppose I could point to Performance Archives | Tiny Lizard but unless you have a lot or rows (100k+) you should have to really work to make it slow

Info on measures (vs calc columns) here: Calculated Fields vs Calculated Columns | Tiny Lizard

If you post a link to your workbook (dropbox,onedrive,googledrive) I'm happy to take a look at what's up...