Excel storage and Pivot table report

[peter8848]

Hi,

We have a data around 300k rows and 20 columns and then a power-pivot report and the file size is like 40MB, I tried the data model and move power-pivot report into a seperate workbook in order to save file size for emailing but it looks like we cannot re-fresh it unless the workbook contains 300k row opened. Is it a better way from doing it? Should we keep our data in access rather than in excel however we like to refresh the power-pivot too.

Cheers,

Peter

 

[Kamolga]

I do not know if you are familiar wit macros and events to launch them.
Usually my pivots are in the same folder, so my way to refresh is that when I come on the tab, a have a macro that refreshes the pivot. I built it so that any user has always updated values without pressing anything.

As it is in a specific workbook, you can have a refresh macro on opening the workbook.

Private Sub Workbook_Open()
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh<strike></strike>

End Sub

Before that , you can have a

'if not open, source workbook.open, refresh and then close'.
​

With this method, the issue is let's say you open the pivot workbook, modify a few lines in the 300k rows and close it, then you can not refresh the other workbook. It is possible to have a source workbook before close event:

Private Sub Workbook_Open()
 [COLOR=#006400]*Your macro "if the pivot workbook is opened, refresh it"[/COLOR]
End Sub

It is also possible to refresh the pivot on every value change of the 300k sheet but you will have a very slow file and no "undo" as every time a macro is launched (every time a value changes) you can not get back before this action with undo button/CTRL+Z, so you have to close without saving.

 

[peter8848]

Hi Kamolga,

Thanks for your great advice and yes Macro may help for automated refresh. However, like you said we miss out the chance for undo as Undo will help us to locate some errors etc. So the key is, is it somewhere or somehow we can do a Pivot refresh in a separate workbook while not opening the source data in another holding platform (Now as another excel workbook)? Will keep the data in Access or some other form will help?

Also storing the data in the workbook is limit to its size etc.

Cheers,

Peter

 

[sandy666]

try PowerQuery then PivotTable if necessary

btw. PivotTable has option: Refresh data when opening the file
so vba for refreshing On_Open is not required

imho, refresh on change any value is not a good idea in case you have big data there, it will take time only on each change.

using PowerQuery you can refresh data from closed source file.

 

[peter8848]

Hi Sandy666,

Thanks for your great advice, could you please explain a bit more how do I use power query? Did I use it in the PivotTable one in the workbook and link it to the another Workbook data source or change the Workbook with data into a different sort of format?

Also how would I refresh the Pivotable only for the changes not the entire 300k rows in the Pivotable workbook?

Cheers,

Peter

 

[sandy666]

ab ovo, you've db access
you can take data from there via PowerQuery, Transform Data as you need then use PivotTable to show result

you can choose which Data you want for each PT

if you refresh PT it will refresh PQ in background and new data will appear in you PT

the same with excel file treated as source of data

all PTs using local PowerQuery which is in background on your destination file, so there is only one external connection to source file

no unnecessary data (tables) loaded into the sheet

what is PowerQuery
Microsoft Power Query for Excel Help
Getting Started with Get & Transform

so, you can use formulas, PT, Power PT, PQ and even VBA with all pros/cons of each method

this is your choice

 

[peter8848]

Hi Sandy666,

Thanks again for your reply. I tried to use the connection only under data section without create data model in my PT workbook but it seems I can only link to one Table not multiple tables like power pivot. However, if I link the multiple table and create data table in my PT Workbook then I can use multiple table to generate PowerPivot, is it possible just to create links only in the power query and still use mutliple tables and create relationship to make new power Pivot in my PT workbook?

In addition, with the source data file, what is the difference between using Excel and Access? My excel file now is 50MB and it is slow now and is it better to use Access to hold the data and also put automatic formulas like what we did in Excel?

Cheers,

Peter

 

[sandy666]

with DataModel PowerQuery is for transform your data tables, relationship you can create in DataModel
so, steps are, load into PQ, transform data as you need , load data into DataModel, create relationship, create PivotTable as you need

 

[peter8848]

Hi Sandy666,

thanks for your advice.

But somehow when you load into PQ and transform the data model the file gets bigger when you create Power pivot reports as data model still take up the spaces maybe half of the size, I was just trying to create links only for pivot table without creating data table but it seems like i can only link to one table from the data source file not multiple table like I do for power pivot? Any chance I can create links only not data model for power pivot?

Cheers,

Peter

 

[sandy666]

Hm, you can try all with PQ without DataModel (PowerPivot) but no pain - no gain.
You'll need build your tables in PQ to be ready to use with standard PivotTable (from INSERT tab)
PQ use "ghost" data from source(s) so file isn't so big but.... if you load data from PQ into the sheet file size will grow so you need plan your work and tables
eg. join (merge, group, transform, etc.) few tables into one (in proper way) so you can avoid too many tables taking up space, but in fact the file size depends the most on what and how much you load into the sheet.
Power Query is not a panacea for all Excel's problems but it can help a lot.

--
sorry for the ungrammatised post