Create and maintain shared model on multiple platforms

[KenStewart]

I need advice on the best general approach to creating, maintaining, and synchronizing a shared data model accross 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. I use VB code for complex processing, so Excel is an important evironment 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 Desktop and 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 to avoid a horde of VLOOKUPs. In this new mix, I'm anticipating 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 the best general direction.

[v-eachen-msft]

Hi @KenStewart ,

According to your situation, why don't you use Power BI directly? Pivot/Query is the precursor of Power BI, so you could use Power BI instead of Excel.

Then you could use Power BI Desktop to connect your data source and publish report to service. In Service, you could set auto refresh.

If you must use Excel, you could use it firstly. Then save as an xlsx file and import it in Power BI desktop.

[KenStewart]

Hey, @v-eachen-msft,

Thanks very much for the feedback. I'd be happy to use much of the Power BI family in favor of Excel, but I don't believe it can be replaced for several reasons -- among them are the need to write VBA code and to distribute interactive macro spreadsheets to other users. I'm still trying to work out the best way to do so within the Power BI context.

Are you saying that I can originate Power BI dataflows from within Desktop? I want to be able to refresh reports and dashboards being distributed from Service to other users, but I thought these dataflows had to be created within Service. I also want to take advantage of Quick Insights and Q&A, which I further understand aren't available through Desktop.

As to using Excel first and propagating the model to Desktop then to Service, that was one approach I considered. That gets back to the question of creating dataflows. Right now I'm importing the "raw" ERP spreadsheets into Service using the on-premises data gateway. I use a little DAX for "light cleaning," like promoting headers and typing, then access the entitities from Desktop for final transformations, formatting, categorization, etc.

This appears to give me the most powerful and complete model for creating reports within Power BI. I can also then export the finished model back to Excel. You may be right about modeling in Excel first, but then taking advantage of Power BI modeling and saving back to Excel seems a little circular.

I guess I'm looking for suggestions on how to improve or streamline this process while still enabling the capabilities I need, as described above. An ideal step-by-step procedure might be something like:

1. Import ERP spreadshets into Desktop.

2. Perform all transformations and modeling in Desktop.

3. Export final Desktop model to Service and Excel.

4. Configure and enable dataflows within Desktop.

5. Develop Excel applications and Power BI reports/dashboards wherever most suitable.

I hope this answers your questions and clarifies some of my thinking, which may be wrong. I'd be happy to find out that I'm missing something basic and that there's a much more direct approach in practice.