Hi Chad,

When you imported the Excel data did you open the Excel file containing the data and then load to Power Query and Power Pivot all from within the same workbook?

Mynda

Mynda,

In my original test it was raw data from .xlsx into Power Pivot, and then raw data from .txt into Power Pivot (2013). I stated incorrectly above, as I didn't have Power Query available. In the end, when loading files (even into .accdb), the .csv/.txt are always handled more quickly by the software.

So, I just never understood why there's a difference. Now that I have Power Query in my toolset at work, it leads me to believe the flat files are still a better option than .xls (maybe not), but just don't understand the technical reasons.

Chad

Hi Chad,

Can you please clarify a couple of points:

1. When you load the data into the Power Pivot model are you loading data from workbook A into the Power Pivot model in Workbook B, or are you loading data in Workbook A into the Power Pivot model in Workbook A?

2. Is the raw data in Excel already formatted in an Excel Table, or not?

Mynda

Mynda,

1) Raw data is in Workbook A, then pulled into a data model in Workbook B.

2) In this case, the data in Workbook A was not in a table.

Workbook A was a download form an ERP (about ~50K records). For the test, I copied and pasted until the .xlsx was at its 1.04 M record limit. Same process for the .txt file. As noted before, the .xlsx loaded approximately 80% into Workbook B before Power Pivot failed. The .txt file with 7.5 M records uploaded into Workbook B without issue.

Chad

Hi Chad,

Thanks for clarifying. I think it will be down to the 32-bit limitation on your version of Excel.

Is the Excel file containing the 1.04M records open when you load into Power Pivot? If so, have you tried loading it with the workbook closed to see if performance is any better?

Mynda

Mynda,

No, the raw data file closed when loading. My only thought is that pulling data from .xls* carries some .xls* "baggage", formatting or otherwise, which is compounded when scaling. However, I've not been able to find the technical answer.

Also, to avoid confusing folks, I have no performance issues after the data is loaded, simply on loading into the data model. It doesn't look like Power Query would overcome the bad data source issue either, so flat files it is!

Chad 

Thanks, Chad. I'll ask Microsoft for an explanation.

Mynda

Hi Chad,

No official word from Microsoft yet, but a fellow MVP offered this explanation:

"If I were to take a wild guess, I would say that 32-bit Excel is running out of memory.

If you examine the internals of a .xlsx, it’s a complete ball of string: text mixed with code mixed with formulas.

Excel has to parse it all, lay it out in memory as a tabular structure, update it all, then extract the values.

It’s limited in the amount of RAM it can attract to do all this in: my guess is it sometimes can’t get enough?"

This makes sense to me since the .xlsx file format is actually a kind of zip file or container of items that make up the file. Bed time reading on .xlsx file format here:

https://msdn.microsoft.com/en-us/library/aa338205%28v=office.12%29.aspx?f=255&MSPPError=-2147217396

Another point to note is it's best practice to bring your data into Power Pivot via Power Query. A word of advice from another MVP:

"I will NEVER EVER, EVER import a text file directly into Power Pivot again.  Period.  Been there, done that, been burned.

When you set up your initial connection it bases the data on a preview of the first 200 rows, iirc.  And if it gets it wrong, and you build your BI on it, my recollection is that you get to rebuild the BI when you realize it.  (You can’t add a schema file afterwards to fix it.)"

Mynda

Hi Mynda,

Thanks for the info, makes sense to me. I have experienced some of the heartache from direct data connections to Power Pivot. I'd love to hear from MS if they do get back to you.

Bottom line, I'm loving Power Query!!

Chad

Hi Chad,

Microsoft say:

"Power Pivot uses ACE for reading both types of files and provide an abstraction. It might have to do with that provider’s handling of such files. I wonder if you can use the provider to read data into Access for example, Mynda, to see what the performance difference is outside Power Pivot."

So, if you have time can you try to import the same Excel and CSV files into Access to see if you get similar results to when you load into Power Pivot. This will help identify if the cause is ACE or not.

Mynda

P.S. I'm so pleased you're loving Power Query 🙂

Hi Chad,

If you're using a 64 bit operating system, and office is in the 32 bit version, if you get the Out of Memory error, or "Excel cannot complete this task with available resources." ,  you can increase the 2 Gb memory limit by using a patch:

http://ntcore.com/4gb_patch.php

There is a Microsoft Update release that does a similar thing: update-for-excel-2013-kb3115162

More details can be found in the first link.

Hi Chad,

Please test Access before using the patch Catalin suggests so that we can compare like for like.

Thanks,

Mynda

Mynda,

I attached Word document with of the results of my testing. Hopefully I did the upload correctly. Of note, it appears the Power Query engine handles .xlsx well, compared to Vertipaq. The test data set is obviously an extreme case, but the idea that this is the format of my data sources (a construct I can't change) is very real.

Let me know what you think!

Chad

Mynda,

I should note:

I tested an .xlsx file with 46 fields and 1.04M records, but very low cardinality. Power Pivot imported that file with little issue. Impressively, with that file, compression went from 177MB (raw data file) to about 700KB once loaded to Power Pivot and saving the excel file.

Chad