Is there a resolution or alternative when experiencing Power Pivot memory errors?

[ez08mba]

Problem: I'm experiencing some 'not enough memory' errors with Power Pivot. What I'm uncertain of in reading posts here and on the Internet in general, is whether I can resolve this issue or need to seek an alternative solution - which I'm open to suggestions.

What I'm trying to do: Each table I mention here is a separate Excel (xlsx) file. I have an PowerPivot.xlsx that I'm using to build my PowerPivot Data Model from other Excel files.

I have a single column table (xlsx file) with unique values to act as a Primary Key which acts as the primary key in a 1 to Many relationship to a table I want to pull data from into a Pivot. My primary key table (i.e., KEY_SOURCE) has approximately 6800 unique values. My related table (i.e., CHANNEL_DATA) has 66,000 rows with 7 columns. If I just attempt to associate the Key to one column of the Channel table causes an insufficient memory error after about 3-5 minutes. However, this is concerning in the fact that I have 4 more tables (xlsx files) I hoped to link in relation with just as many rows (66, 000) and the same number or more columns.

What I have tried: I have done the best that I can eliminate unnecessary columns and other data.

This really doesn't seem like a lot of information to me for a corporate business, so I'm a little taken back I'm not even started and having this issue - but this may simply be the reality. I was hoping for something I could try so I could continue using the PowerPivot, or whether there was an alternative, like treating each file as a OBDC, or something. Curious anyone's thoughts.

Thanks!!

 

[Matt Allington]

This is a very small report. Are you using 32 or 64 bit Excel? This article is old, but it still relevant in the section on this topic. Which Versions of Excel come with Power Pivot?

 

[ez08mba]

This is a very small report. Are you using 32 or 64 bit Excel? This article is old, but it still relevant in the section on this topic. Which Versions of Excel come with Power Pivot?

Thanks for the article. I'm using 64-bit. My Ram is 16GB

 

[ez08mba]

Here are the errors I receive in sequence each time I attempt selecting one (and only) column from the key (Source table (xlsx file)) and Destination Channel (2nd table (xlsx file)).

 

[ez08mba]

Sorry for the continued reply. The two files I am using combine make up less than 3MB in size. If I would link all off the files I need, combined they would only be approx. 30MB in size. Yet 3MB + Excel are using 9+GB of memory during processing and almost 5GB when reading and that is the value when I receive the errors. After clearing them, I'm done to 107MB.

Processing....

Reading:

After errors are cleared....

 

[Matt Allington]

It is very weird. Is the data confidential? Can you share it? If not, what is the content of the table? If it is text it could be an issue. I would start again. create a blank workbook and import the data. Make sure you are using Power Query to import the data

 

[ez08mba]

It is very weird. Is the data confidential? Can you share it? If not, what is the content of the table? If it is text it could be an issue. I would start again. create a blank workbook and import the data. Make sure you are using Power Query to import the data

It is confidential data. These are XLSX files created from CSV extracts. However, they are imported through Power Pivot external data option and selecting Excel File. I had no idea on Power Query or saw a step involving Power Query. Do I need to try that? If so, I'm not sure I know how I would do that through Power Pivot?

 

[ez08mba]

@Matt Allington Ok, I learned about Power Query and imported my data into it. It found some errors and I fixed those and I saved them as newly created files without any errors. I then imported those into Power Pivot without errors but I'm still having the same issue. It is all texts.

 

[Matt Allington]

Assuming you have a reasonably recent version of PB, you should be able to just go to the data tab, get data, from file, from text/CSV and load it up from there. Make sure you click "load to" and select "only create connection" and "add to data model". It's worth a try

 

[ez08mba]

@Matt Allington So, you may have me at a disadvantage. This is the first I've used Power Pivot (PP) and Power Query (PQ), so I'm at a loss as to whether in creating a "connection only" if I did this correctly because I still received the same issue of memory.

Based upon your thoughts and suggestion, I started from scratch with my 8 CSV files and transformed the data. As you suggested, I did "Load to" > "Only Create Connection" > imported into the Data Model. Since I didn't bother removing duplicate rows, I had 133,000 rows; which still shouldn't be a problem as I understand. I still received the same memory errors, and the memory usage was barely significant despite the add'l rows.

I know it probably doesn't say much, but I included the screen shot below and highlighted the Routes CSV field I'm trying to add, then it just fails. I guess I'm not sure if I'm supposed to be selecting something from the Queries & Connections first before attempt to create the pivot? Below, within the pivot on the left is the Source acting as a key, which is a deduped list of the Source Channel under Routes CSV. I'm trying to do the simplest thing and be able to filter Destination Channel(s) based on the Source. Once I understand how to do it, then I will build out based upon additional files and fields. I'm also trying to determine how I could provide you sample files without compromising confidentiality.