Can anyone please share with me your experience in terms of performance when connecting to a PowerBi data model from Excel?
In my comany, there is an increased interest in developing new report requests in PowerBi. Some users will be given the opportunity to analyse their data using Excel. We have premium license.
After reading some articles online, I’m leaning towards the option of seperating the data model and and report development in two seperate pbix files. Before proposing this option to my team, I would like understand the performance impacts
From experience this totally depends on volumes and the number of changes going into your fact (the number of rows being added Daily, Hourly), the type of analysis being done and the number of users.
I would always recommend that you keep development and production separate. I think the data model should ideally be the final state the centralised version of your data. The business will no doubt want to experiment and add their own data this is fine but should ideally be done away from your central data model. Once the business have done some exploration there could be some actionable data that will provide benefit to everyone at this point you should consider adding the new data to your central model.
Something worth exploring could be shared and certified data sets. This will allow you to process your data once therefore improving performance and make it available to others in the business maintaining a central one version of the truth.
It’s easy to publish your data set to a shared workspace in the service, then download the PowerBI Excel Add- in for Excel and connect to your data set in Excel.
Here is the best read on it I have found from Matt Allington.
It’s always better to have separate files for the data model and your various reporting layouts.
It’s firstly single source of truth which is the key.
The data file can be shared across various team workspaces.
The data file is smaller and themes can be built into just the report files.