Centralised Data Model

New Contributor

I’m seeking your expert support please, I'm sorry if the inquiry would seem blur but I don’t know the proper terms to describe my inquiry here, so, I thought to check with you on casual basis.


I’ve an Excel file which has many and many power queries loading in tables, there’s another Excel file where I’ve connections to the first file tables, thru, I don’t know what this one is called, data model? OLAP? I’m not quite sure, but you get it when you click on Power Pivot ribbon, anyways, one of the data model tables is in the first image for your reference and the second image shows the connection pane.





Now, this data model different tables are in the background of course, they’re not visible, however, which is in the front visible are the Power Pivots that have this data model different tables as their base source after the different data model tables got connect thru relations.


The question here, could I keep the data model in a file and build the pivot tables in separate files? I mean, could this data model be external and centralized for many power pivot files?


Why am I asking? Because I need to separate the power pivots in groups in separate files without the need to have the data model tables duplicated in each file, because if a column header is changed in the queries file, you will need to update the data model file, so having the data model duplicated in many files means that I’ll need to update each model, which is tedious.


Is there a way to optimize?




1 Reply
As far as I know, the data model "belongs" to a workbook and cannot be shared between workbooks. In any case, even if this were possible, changing a field name (column name) in that central data model would cause havoc in the linked files if that column happened to be used in a pivot table or filter.