Data Model - Update All Without Updating Pivot Tables

[Paul Rockliffe]

Excel 2013 with Data Model rather than Power Pivot, I have a process I'm using to produce various subsets of my overall dataset. My process generates 6 .csv files which are built as tables in a Relational Database. I've added each table to the Data Model and setup the correct relationships so that I have my 'database' available in Pivot Tables to build the outputs required.

Part of the reason behind this setup is that it allows me to build an Excel template, run my process using different parameters or filters to produce a bespoke dataset, then use Data -> Update All to populate the template with the new data. Works really well in general, I know it's not ideal, but it's the best I can do with the tools available to me.

Anyway, that's the background, I've hit a snag. The design of the database is such that the relationship between some elements in the Pivot Table is not defined until all the data has been refreshed. In practice that means I get all of x linked to all of y and then all of z linked to all of y. Then the Data Model loads the links and strips out all the duplication. This overwhelms the Data Model as x * y * z is a massive number of rows to produce for the pivot table.

If I have no Pivot Tables in the Workbook then the update process works, but obviously rebuilding the template is then a pain. I'm looking at spending this evening stripping back the Pivot Tables to the bare minimum to allow the update to run, then rebuilding them, but that's a rubbish way to do it so I thought I would ask if anyone know a bodge I could try to get this to work?

I'm thinking if I could update the Data Model Connections with the Pivot Tables suspended, then update those individually afterwards that might just about work, though i'm not confident.

So any ideas?

Thanks!

 

[Fazza]

hi, Paul

My attention is drawn to " the relationship between some elements in the Pivot Table is not defined until all the data has been refreshed."

I don't understand. In my mind when the pivot table is created, its definition includes these relationships.

Such as if it pulls data from two tables, the definition might be : SELECT A.*, B.whatever FROM A INNER JOIN B ON A.ID = B.ID

How is it different for the pivot tables you have? When/how are the relationships defined??

regards, Fazza

 

[Fazza]

PS.

My comments come from zero knowledge of Excel "data models".

I'm used to having pivot table data from multiple tables/worksheets/files/databases. With relationships defined by SQL.