You can use an existing data set with LiveQuery or DirectQuery connection as the shared data source for multiple Power BI reports. You can also create multiple reports from Power BI Service (website) connecting to the same data set. However, this new feature in Power BI Desktop version April 2017 is a big step forward; You can now create reports in Power BI Desktop pointing to a central data set. This is a great step towards making your data sets centralized and using it consistently from many reports. In this post I’ll show you how this feature works. If you want to learn more about Power BI; read Power BI book from Rookie to Rock Star.
Get Data from Power BI Service
You need to install Power BI Desktop version April 2017 or later versions to have this feature. At the time of writing this post this feature is still preview, and you need to enable it through going to path below:
File -> Options and Settings -> Options
In the Options window go to Preview Features, and enable this option;
You will need to close Power BI Desktop and Open it. after re-opening Power BI Desktop, in Get Data section you will see the new option: from Power BI Service
For accessing to Power BI service you need to login to your Power BI account from Power BI Desktop, and then you will see list of work spaces under your account and data sets under each.
After selecting a data set, a LIVE QUERY connection will be created to that data set, and your Power BI Desktop will turn into a mere data visualization tool (exactly similar to having a LiveQuery connection to SSAS). You cannot edit the data source, you cannot change relationships, you cannot create new measures or calculations. However, you can create all visualizations you want based on this model.
As you can see in screenshot above Get Data from other data sources will be disabled, and you will have only report tab available in Power BI Desktop.
If you want to make any changes in the model, you need to open the Power BI Desktop file that contains the model, and make changes from there.
What This Change Means?
At first you might think; what is the big benefit of having this feature? the short answer is to have reports based on a centralized data set. the long answer is here;
Central Data Model
Previously if you could use Power BI for getting data from multiple data sources, combining them, data mashup and transformation, building a great model with hierarchies and calculations, and build visualizations based on it, all in one file. If you wanted to create another file with the same model, you should copy the existing one, and make changes in it, and for maintaining you would have a headache of maintaining multiple models to be exactly similar! Now you can simply build a model once, and re-use it multiple times.
Multiple Development Environment
Developing everything in one Power BI file is good for one developer. However, when multiple developers want to work on the same file, then multiple files are needed. With this new feature the visualization layer can be easily separated from the model. Multiple Power BI files can be pointed to the same model, and multiple developers can work on it.
Report Level Sharing
You can share reports in multiple ways, but all of those methods share the whole report not particular pages. If you want to share some pages with some users, and others with other users, you will need to create multiple reports, or in other words; multiple Power BI files. With the new change you can easily switch to this method, it will not cost you anything. with the old method you had to copy everything, and redundancy problem will happen again with big maintenance task overhead.
You’ve realized how good is this feature, and how the next generation of Power BI reports will shape in the future. You might think what is the limitation of this feature? This feature is still preview, and there will be lots of updates for it in the future, however at the time of writing this post, here are limitations:
- One Data Source: You can not combine another data source with an existing model. If you want to do that, go back to the original model, and bring the data set there.
- Change of Model is not possible; You can change the model only through the original file. other new files will be just connection to that data source for visualization.
- LiveQuery Data Sources won’t be listed; if you have SSAS LiveQuery connection, you won’t be able to connect to it in this way. Why? because you can simply connect to SSAS itself with LiveQuery, you don’t need an extra layer!
- Streaming Data Sets are not supported; If you have a streaming data set from REST API, Azure Stream Analytics, or PubNub, you cannot access it from here. I hope this feature be enabled very soon 🙂 it would be the second big step forward in this area.
In summary this features is a game changer in the architecture of Power BI solutions. You can now use a centralized model, which is isolated from visualization layer. For very large data sets, and enterprise solutions, still SSAS live connection might be the best because of the size of data. However, if the size is not issue, then Power BI can be easily used as a centralized data model approach. Because of this big change I give 5-star rank to this month update of Power BI, well done team 🙂
14 thoughts on “One Data Set, Multiple Reports; Power BI Next Big Step Forward”
Great news!!! But is can we use this new feature to apply Row Level Security to our new files in Power Bi accounts that aren’t pro accounts???
Row Level Security is a pro feature. Getting data from a data set is different, you cannot use it for applying RLS. as soon as you apply RLS pro feature comes through again.
This is indeed a critical feature that will change workflows and installations for the better. Unfortunately for now, the feature DOES NOT support SSAS OLAP or Tabular data sources. I really hope this is added when the feature gets rolled out as a permanent part of the product. Unfortunately, because we are a Microsoft shop and use Microsoft databases exclusively, this feature as it stands does us no good at all.
Hi Peter. SSAS live connections would work directly from Power BI desktop. you can already have multiple Power BI reports pointing to the same SSAS live connection.
I was REALLY excited by this until I realized how limited it is.
BUT I’d REALLY like the ability to allow a dev (like me for example) in Desktop, full capability to edit this data in the data Model (like or relationships) and to be able to include data from other sources in the same Dataset like a normal Desktop file.
Without this capability I really have trouble seeing the value of this.
On the other hand WITH these features I can see building a central PBI.COM Dataset to collect and store data that then gets used as the “base” data for PBID multiple PBID PBIX files. This would separate the data from the UI better as in Desktop most of the Modelling is transformations and calculations directly to be sources for the Charts and Matrixes , etc. in the Reports.
I’m surprised this was done this way.
To say that this way the PBID user is controlled by the original dev is presumptuous and short-sighted.
Is there a possibility that this will come soon?
The point for this feature is that you can build a model and publish it as a central model for others to use (mainly for doing extra visualizations). If others be able to edit that model, then it is not central anymore! it will be another copy of this model!
If you want a model to be available for a group of people to edit, then you can use work spaces for that. create a work space, publish the model there, and give edit access to group of people who want to manage it. everyone in that group can download *.pbix file and edit it and then publish it again.
However, the ability to connect from Power BI Desktop to Power BI Service data set is mainly built for power users, who want to just create additional visualizations on the existing model. not changing the model.
I am using the single data model for multiple reports in my id but i want to publish these reports on another id.
for example i have published report A with data model and after that I created Report B from the dataset of A in ID X now I want to Publish the Report A and B in Another ID Y.(without creating the reports)
Kindly provide me a solution as soon as possible.
Hi, by ID do you mean Power BI Account?
You can use a workspace, and publish the model under that workspace, then you can access it from another account which has access to that workspace as well.
Can we merge/combine two or more .pbix file into power bi desktop to make it one .pbix file with the existing data in the files ??
Hi Shashank. Not with the live connection from Power BI Desktop unfortunately.
another option is to create a new pbix file and get data from both sources separately and then combine
I don’t fully understand the bit under ‘Report Level Sharing’. How does the centralized dataset contribute to facilitating sharing of specific report pages rather than entire reports?
by that I mean; sometimes people want to apply security based on the visual. So they can create a few pages of visuals as a report and share it. considering that it all consumes one central dataset, this is just building visual effort, and doesn’t mean to create a copy of the dataset.
With combination of one dataset, and several reports.
Is it possible to set-up RLS for each individual reports?
RLS is defined at the dataset level. when you have it in your shared dataset, then reports will all follow that.