Hi all
this is just a general question re what drives files size in a Power Pivot model. I need to share my files with other users so they need to be as small as possible as most users only have a basic computer set up. i have created a file with only a very small amount of data in the data model and several Power Query queries (load to data model or connection only). The source data file is only ~1MB.
The analysis was ~ 2MB in size but when I added 2 more queries (dependent on other queries), the file size started to increase significantly (30MB). To try to reduce the size, I started to delete various items:
1. deleted some queries => no change in file size
2. deleted all the queries => no change in file size
3. deleted some tables in the data model => no change in file size
4. deleted all the tables in the data model => no change in file size
5. checked the pivot table caches => multiple caches of 0 records (I assume from the Power Pivot model)
6. deleted some sheets => no change in file size
7. deleted all the sheets except one => file size reduced to very small size
So, I have a number of questions:
1. is it possible to reduce the file size gradually by removing some items only
2. how can I identify what is contributing to the size
3. what is best practice in terms of creating an analysis file to ensure minimum size (I understand about # columns/rows and # unique data but am wondering about other elements eg - creating new pivot tables by copying from existing sheets rather than inserting a new one; creating queries a certain way; creating connections a certain way etc)
4. is there anything else that can help?
I am finding that I have to re-create the file from the beginning and check the file size after every new data import, table creation etc to manage the problem.
This is just not practical.
Any help appreciated.
PS I did not experience this problem with Power Pivot 2010 but I was not using Power Query in those models either.
this is just a general question re what drives files size in a Power Pivot model. I need to share my files with other users so they need to be as small as possible as most users only have a basic computer set up. i have created a file with only a very small amount of data in the data model and several Power Query queries (load to data model or connection only). The source data file is only ~1MB.
The analysis was ~ 2MB in size but when I added 2 more queries (dependent on other queries), the file size started to increase significantly (30MB). To try to reduce the size, I started to delete various items:
1. deleted some queries => no change in file size
2. deleted all the queries => no change in file size
3. deleted some tables in the data model => no change in file size
4. deleted all the tables in the data model => no change in file size
5. checked the pivot table caches => multiple caches of 0 records (I assume from the Power Pivot model)
6. deleted some sheets => no change in file size
7. deleted all the sheets except one => file size reduced to very small size
So, I have a number of questions:
1. is it possible to reduce the file size gradually by removing some items only
2. how can I identify what is contributing to the size
3. what is best practice in terms of creating an analysis file to ensure minimum size (I understand about # columns/rows and # unique data but am wondering about other elements eg - creating new pivot tables by copying from existing sheets rather than inserting a new one; creating queries a certain way; creating connections a certain way etc)
4. is there anything else that can help?
I am finding that I have to re-create the file from the beginning and check the file size after every new data import, table creation etc to manage the problem.
This is just not practical.
Any help appreciated.
PS I did not experience this problem with Power Pivot 2010 but I was not using Power Query in those models either.