Share data model updates across workbooks

[WPO]

Workbooks A, B, and C (and D and E and . . .) all use the same data. Each workbook has multiple complicated worksheets that I do not want to rebuild, and for various reasons they cannot be combined into a single file. How can I modify the data model in Workbook A and then easily and quickly replace the data models in Workbooks B and C with the updated one from Workbook A so that the new queries I created in Workbook A will then be available in all workbooks without having to rebuild everything from scratch?

A complicating issue is that I need to share my workbooks with multiple users on a Sharefile platform; i.e. each user accesses files from a unique path on their hard drive which is synchronized to the cloud). As a result, I don't think I can create a single data model file and point all the workbooks to that file. It appears to me that each of the workbooks needs to have their own version of the data model. (Many of the changes I need to share across workbooks deal with this issue, but that's a post for a different time.) That said, I am open to being wrong and am willing to try anything.

Thank you!

 

[Louis_Guionneau]

Are you using PowerPivot? Or staying in Excel? What's your current practice, e.g., are you using PowerQuery etc?

 

[WPO]

To answer your question generally, I do everything in/through Excel. I do not access a separate program like the Business Intelligence App or any separate Power Query builder (is there such a thing?).

To answer specifically, I launch Excel and edit the data model from there. The current data model currently has 5 queries, each querying worksheets in various other Excel files (connections only). These queries feed an append query, and the append query then creates a single table with aggregated data which our users then reference to build their own pivot tables in the local workbook. As part of all this, though Excel's data model manager, I have modified the M language source/transformation of each of the initial 5 queries so they use variables to intelligently vary the path of which Excel file each one queries. This M language tweaking is where I anticipate have frequent changes which I'd very much prefer to easily publish across all Excel files rather than manually change each query in each users' workbooks (impossible). And although not a professional coder, I am proficient with VBA and comfortable with working "under the hood."

If there is a different tool I should be using, I'd love to know about it!

 

[macfuller]

PowerBI can refresh against a data model. Perhaps you could have your "master" as the Excel data model and then the subsidiary PowerBI files could all point to the master while residing on the PowerBI server?

If everyone has to have Excel (e.g. because they have to use some Excel features to modify or further analyze the data) then I think you have to keep updating each worksheet from their own queries. If you can enforce a consistent set of folder structures then at least you can have a function to always look in (say) subfolder \MyReport\Sources for sources and \MyReport\Dashboard for the report file.

 

[WPO]

Everybody has to be able to use Excel. (Maybe this thread belongs under Excel rather than BI?) I may need to re-think how this is all working.

 

[macfuller]

I'll be very interested in other responses. I suspect all of us wrestle with the issue of having a complex data model. In a Venn diagram of all the uses, each user probably has a small intersect with the rest but they all rely on measures that pull from lots of tables in the model so you have to update the whole thing just to give each user their relevant information. You'd like to give each user just the segment they need but loading the model is a time-consuming process.