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.
Limitations
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.
Summary
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 🙂
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
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.
Cheers
Reza
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.
Cheers
Reza
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?
Dick
Hi,
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.
Cheers
Reza
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.
Cheers
Reza
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 ??
Please suggest
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
Cheers
Reza
Hi Reza,
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?
Thanks, Robin
Hi Robin.
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.
Cheers
Reza
Hi,
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.
Cheers
Reza