Wednesday, March 20, 2013 11:32 PMReceiving the "Capacity exceeds maximum capacity" error in PowerPivot. My workstation is Windows 7 (64-bit) and I am running Office 2013 (32-bit). I have also run this test on a server with Office 2013 (64-bit) and received the same capacity error message. The PowerPivot workbook is reading an Oracle database using the SQL option. The SQL select is bringing back less than 200 columns. Many of the columns have translation logic (CASE, TRIM, NVL, etc.). The SQL runs fine through Oracle SQL Developer. If I use the the SQL to create a table (using the create table as) the resulting table has a row length less than 10,000 bytes. I have put filters in the WHERE clause to bring back less than 20 rows. Based on the maximum capacity limits for PowerPivot it seems like I shouldn't be having this issue (my row length is well withing the 64K limit). If I reduce the number of columns returned (to 100 columns instead of the 200 columns) PowerPivot successfully brings back the rows.
Tuesday, March 26, 2013 4:02 PM
Update: The exact same SQL does not receive the "Capacity exceeds maximum capacity" error when executed through SQL Server Data Tools as part of a Tabular Data Model project. Therefore, it seems like there are limitations to SQL executed through the PowerPivot Excel data model that do not exist for SQL executed through SQL Server tabular models. Do you know of documentation that may explain why the SQL executes as part of the SQL Server project but does not execute through Excel?
Also, using OEM (Oracle Enterprise Manager) I can see that Excel is submitting the SQL to Oracle and that Oracle successfully processes the statement and returns the results. The capacity error occurs as Excel is parsing the results.