Excel data model creating large file size

Question

I have an Excel data model that is connected through a SQL Query that has 4M plus records. The connection is setup up as Connection only but the query shows 4.1M loaded. Is this correct if it is a connection only? My file size is now almost 30mb.

Answer 1

Thank you for the information.

All of the data is in the data model and all connections to the SQL server are connection only.

I rewrote my SQL query to sum a lot of the information. It resulted in less detail but is acceptable for my purposes at this time.

The file is still in excess of 15MB.

I am going to try to clean up the queries SQL statements.

After cleaning up the queries, I will try the Workbook Size Optimizer.

After some searching, I think this may be something I just have to learn to live with.

Answer 2

Hi SBlock1,

I'm writing this reply to follow up on this thread, may I know if you have checked rohnski2’s reply? When you have time, you are welcome to come back and share any updates here.

Regards,

George

Answer 3

I think this article addresses at least part of your question: https://exceloffthegrid.com/power-query-close-load-options/

Using the Data Model enables an extra level of data compression. I've read of examples where using the data model compression 10's and even 100's of millions of rows of data can be loaded into excel. Well beyond the stated million row limit in excel.

<snip>

...

Add this data to the Data Model

... By loading the data into a Data Model, rather than a PivotTable, the file sizes are significantly smaller.

As an illustration, I loaded 1 million rows of data (containing only two columns) into the Data Model.  I then used the Data Model to create a basic PivotTable; the file size was 794KB.  By contrast, I loaded the data directly into a PivotTable without a Data Model and the file size was 3,488KB.  Over 4 times the size!  While in this illustration, the file sizes are small, but when working with more data, the variance is even more pronounced.

</snip>

.

This article addresses another facet: https://superuser.com/questions/1475224/what-is-connection-only-mode-do-in-power-query .

Create a memory-efficient Data Model using Excel and the Power Pivot add-in https://support.microsoft.com/en-us/office/create-a-memory-efficient-data-model-using-excel-and-the-power-pivot-add-in-951c73a9-21c4-46ab-9f5e-14a2833b6a70 In Excel 2013 or later, you can create data models containing millions of rows, and then perform powerful data analysis against these models. Data models can be created with or without the Power Pivot add-in to support any number of PivotTables, charts, and Power View visualizations in the same workbook.

.

Create a memory-efficient Data Model using Excel and the Power Pivot add-in

https://support.microsoft.com/en-us/office/create-a-memory-efficient-data-model-using-excel-and-the-power-pivot-add-in-951c73a9-21c4-46ab-9f5e-14a2833b6a70

In Excel 2013 or later, you can create data models containing millions of rows, and then perform powerful data analysis against these models. Data models can be created with or without the Power Pivot add-in to support any number of PivotTables, charts, and Power View visualizations in the same workbook.

.

Here is an interesting tool. I haven't used it, but it may help shrink your file size

Workbook Size Optimizer

https://www.microsoft.com/en-us/download/details.aspx?id=38793 

The workbook size optimizer inspects the composition of the data model within your PowerPivot or PowerView enabled workbook, sees if the data in it can take less space and if possible, enables better compression.

.

Power Query Data–Should it be Loaded to the Worksheet?

https://www.sqlchick.com/entries/2013/9/7/power-query-datashould-it-be-loaded-to-the-worksheet.html 

September 7, 2013

Overview: This post discusses situations when you may *not* want to load Power Query data to the worksheet, but instead to the data model only.  One reason relates to file size limits in Power BI for Office 365; the other reason is to simplifying & de-duplicate the fields displayed in the Pivot Table Field List.