Share PowerPivot Data Model with another Workbook - Excel 2016 Professional Plus

[jdorby]

Hi - I'm currently using Excel 2016 Professional Plus. I have a workbook that contains a large data model. I have 20+ reports I need to create and this data model will be used as the repository that supplies all the information for those 20+ reports. However, every site/blog I read (granted most are from 2015 or 2016) says that you can't share a data model between workbooks unless you share the data model on sharepoint or use some kind of SQL Services. For whatever reason, I can't use Sharepoint or SQL and am trying to determine if I can share data models between workbooks. From what I can tell, I'd have to house all 20+ reports in the same workbook that contains the data model, which is just not possible with how large that file size would become. Is there any way I can share either a live or even a copy version of the data model to another workbook? I'm fluent in VBA and would have to think there's a way to at least take a snapshot form the workbook containing the data model and use that snapshot to update another workbook that could contain a copy of that data model. Any help is greatly appreciated!!

Thanks,

Jim

 

[SpillerBD]

Watching this...
I would think that creating a Worksheet with a Report and then Sending that sheet to a New Workbook would hold the connection. Problem I expect is that to update the New Work Book, the source Book would need to open and refreshed and then the New Workbook refreshed before closing the source Book. You could Macro that.
Otherwise, for the short term at least, create duplicate Workbooks with the same Data model. yuck!

I do wonder how much a file size would really grow since all the reports are based on the same data model. Ideally, there are no loads to a Table and no real data in the Workbook sheets. Everything is in the data model which is highly compressed. https://powerpivotpro.com/2016/02/sort-data-load-improved-compression/

 

[jdorby]

Hi Spiller - thanks for the reply!

So, your suggestion about just copying the existing workbook with the existing data model and saving a new version was my initial thought. However, my plan was to have 10+ reports already set up in the new workbook where I could just refresh the data model and all the cubevalue formulas I already had pre-written in the pre-formatted reports would just refresh and completed within seconds. However, if new data comes into the data model on a monthly basis and I have to save the new data model anew every month, then there's no way for me to have pre-written/pre-formatted reports in the second workbook since I'll be saving a new monthly copy of the data model on top of it each month. In theory, every new month I could create everything over from scratch, actually write the VBA to produce the 10+ reports from scratch and write all the formulas and apply all the formatting. But that would be so time consuming and so difficult that I have to think there's another way. I should really only have to set up my reports ONCE, refresh the data in the data model, and then they just repopulate. This seems like such an obvious ask that I figured Microsoft would have come up with a solution by now outside of sharing the data model to Sharepoint. Once again, thanks for replying.

AND, I just confirmed, creating a report in the workbook that contains the data model, then moving that report/sheet into a new workbook does NOT bring over the data connections. All cubevalue formulas go to "N/A" when you take that sheet out of the workbook that contains the data model.

 

[SpillerBD]

There are also some pitfalls of CUBEVALUEs I think you are dealing with. I avoid them despite their usefulness and how they allow grabbing a particular value out of the Cube/Data model.
For myself, daily I refresh a Workbook and email it out (don't ask) and it refreshes 5 queries. 24 sheets and 10 pivot Tables based on the same Data Model with corresponding Pivot charts. My biggest load is 15k-20k rows and the file size runs 6-9 MB depending on the source file sizes.
I also have a Workbook that runs about 30MB
The previous ones I've mentioned are run on my corporate hardware using 32-bi OS and Excel 2013.
The biggest files I've worked on have hit a 100MB, all Data Model and including Power Pivot and custom measures, but I've had to use my own 64-bit machine.

PS. I wish I could afford Rob Collie's seminars on PowerPivot. Until then I'll just keep watch Mike Girvin's YouTube channel, though I haven't been doing that as much as i should lately.

 

[jdorby]

So are you saying that the daily workbook you refresh and email out that contains 24 sheets and 10 pivot tables encompasses all the reporting you need to do and all the reports are therefore all included in the same workbook as your data model? Ideally, I'd want to do the same, but my data model workbook without any of the included reports I need to build in is already ~33mb. The data model is needed because there are multiple fx rate conversion scenarios that tie to forecast and actuals along with actuals, budget, forecast dollar amounts and metrics. If I want to get away from PowerPivot completely I suppose I could write some VBA to convert all the amounts with their respective fx rates with normal calculations instead of linking tables and relationships together that the PowerPivot requires. My worry is that even that will be larger than 33mb. My base data is 909k rows and 17 columns. It should all be in SQL, obviously, which is probably the next battle I should fight.

I have all of Collie's books. I need to look into his seminars and see if I can get those expensed lol. Thanks again for the help. Glad I'm not the only one having troubles with these.