Page 1 of 1

Data Refresh Question

Posted: Tue Jan 26, 2016 3:11 am
by chrisco
I have a Power Pivot model connected to a text file for my main Data Set. Approx. 606k lines of data, 8 rows. I'm using Power Query to clean it up before loading into the data model.

The Dashboard I have is actually a Profit & Loss report with cascading subtotals and multi-tiered levels (for example I could drill into Sales and see Hardware vs Software and so on).

The problem I have is when I update the text file and try to subsequently update the data model.

When I update, the Power Pivot window rolls through like its updating and then seems to freeze at "Retrieved 600k lines". If I switch to the Excel window, I see that its "reading data" which seems to take forever. In fact, I've never actually waited it out. I've let it run overnight before though but I'm not sure how long it actually took. I have noticed if I hit ESC and cancel the read, save the workbook and re-open, I would then have my workbook functioning the way I want it. This doesn't seem to be the "right" way though.

I've moved both the source file and the workbook from the network to my desktop to see if that would expedite things, but so far, no noticeable changes.

Any insight would be beneficial.

Re: Data Refresh Question

Posted: Tue Jan 26, 2016 7:20 am
by MattAllington
Interesting! It certainly is not normal, so something must be wrong. The challenge is to work out what it is. I have some questions/suggestions:
0. Do you have 64bit Excel? How much memory do you have? 600k rows should be OK, but I guess this could be a problem - not sure.
1. I assume it worked OK with a previous file, right? So chances are there is something in the new file causing the problem - trick is to find it and then filter it out in Power Query
2. When you say you use PQ to clean it up, are you reshaping the data or only "cleansing"? If you are only cleansing, I suggest trying to load the txt file into a blank workbook without any cleansing and load it to a an Excel table. That way you can actually look at the data and see how long it takes.
3. After that, take a copy of your Power Pivot workbook and save it as a new name. Change the Power Query to load as a table instead of to Power Pivot. See what happens. If you can, try to see which line the load is failing and then compare against the raw data.
4. You could try doing a binary delete on half of the columns at a time before loading to see if that fixes the problem. If it works, you know the problem is in one of the columns you deleted, so go back to them and delete half of them - check again etc etc until you find the column that has the problem.
5. If you get some idea which row has the problem (eg between 600k - 606k) you could try a large text file editor (find one on the web) and try to trim the file to only keep those rows. Then try to load those and see if it throws an error.

Re: Data Refresh Question

Posted: Thu Jan 28, 2016 4:57 am
by chrisco

You were actually helping me with the same problem over at Mr. Excel. Good to see you again! :D

to answer your questions:
0. Running 32bit. its unfortunate, but that what I'm stuck with.
1. In prior versions it seems to be fine.
2. I'm using PQ to filter and to add a date column. No major transformations in my mind. I started a new workbook importing the same information (no measures) and it updates within 15 seconds.

Actually looking at it, it looks like when the dataset is updated, the real hang up is the "reading data" part when the measures get updated. I have a total of 29 measures and cube-formulas, and 8 slicers. I did time it and it took about 18minutes to update everything. Could this be a RAM memory thing?

Re: Data Refresh Question

Posted: Thu Jan 28, 2016 5:04 am
by MattAllington
Pivot tables, cube formulas and slicers will slow the refresh of a workbook, but only after all tables are fully refreshed. From the sound of it, your query is failing before the refresh is finished - so that can't be it. You could test it by taking a copy of the workbook and loading to a table instead of he data model.

I think there is something wrong in the data set that is causing it to fail. I think the only way forward is to try to find out what. You could try he other steps I suggested earlier.

And yes I had worked out it was the same problem from Mr Excel :-)