Ken Stewart
New Member
- Joined
- Dec 16, 2019
- Messages
- 1
- Office Version
- 365
- Platform
- 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.
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.
