How best to synch data model among Excel, Power BI Desktop, and Server

Ken Stewart

New Member
Dec 16, 2019
Office Version
  1. 365
  1. Windows
I need advice on the best general approach to creating, maintaining, and synchronizing a shared data model across multiple platforms: Excel with Power Pivot/Query, Power BI, and Power BI Desktop. Most source data are from an ERP cloud (CostPoint), so much of the model is implicitly defined there.

ERP data are available through reports in Excel format, so these are essentially "views" of the back end. A small amount of other data come from local sources, and the model is extended appropriately. We use VB code for complex processing, so Excel is an important environment for multiple reasons.

Power Query and Power Pivot add-ins for Excel have been great, but Desktop seems to have a slight edge in capability and integration with Power BI. But to use or best use dataflows, it appears that the data should be sourced and transformed through Power BI then passed on to other environments. I'm new to this, but that's my impression so far. The big questions are:

1. Where should I focus my efforts on creating and maintaining the model?

2. How can I export, share, or otherwise synchronize model instances on the different platforms?

3. I don't expect this can be fully automated, but how can I best minimize efforts and errors creeping in?

Before I started using the above, I maintained a model in Access, related different ER report "tables," and sourced views to Excel. In this new mix, I'm guessing Access shouldn't be needed. I've also experimented some with DAX Studio, and I'm wondering if that might play a role.

I'd appreciate some big-picture pointers here. I've explored the forum and found plenty of good advice on specific details, but I'm still unsure of a general direction.

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Watch MrExcel Video

Forum statistics

Latest member