Accessing Power BI Report Server data from other applications

[cssathya]

Are there ways in which the data model stored within a Power BI Report Server (PBIX) can be accessed from other applications for consumption? There seem to be options such as REST API and OData Feed to access information and also potentially from other Power BI reports than the one to which the dataset is attached, but the documentation is a bit light.

The use case here is that we have a bunch of data sources connected to a Power BI dashboard. We would like to leverage this integrated view to query information from other applications (either a custom application or commercial like ArcGIS) to pull the data from Power BI Report Server and connect with other data that those applications may internally have and display in those applications.

[Anonymous · Accepted solution]

What you're looking for isn't really supported. There are other methods of achieving something similar.

You can host your data in a SSAS Tabular model. This can be accessed via several PBIX reports as a live data source or as an imported datasource. You can import a PBIX data model into SSAS Tabular though its a not exactly a painless experience. Once you have it there you can get it into Visual Studio. See this

http://biinsight.com/import-power-bi-desktop-model-ssas-tabular-2016/

The REST API doesn't expose an oData endpoint for the datasources used by the PowerBI reports. You can of course access DataSets built using conventional datasources (not PBI attached just regular SSRS datasources) via oData from PowerBI reports

https://docs.microsoft.com/en-us/power-bi/report-server/access-dataset-odata

If you created a datasource and then some datasets off the back of it you could use these as sources in multiple PBI reports and refresh the datasets on schedule.

Thus you could "share" datasets between reports. Sort of. But it requires you to generate the initial PBIX and then transalte the datasets into conventional SSRS DataSources and DataSets somehow, so its more engineering and more of a mnagaed data model approach which is kind of the way you are leaning anyway.

The problem this has and indeed using an existing PBIX as a data source is that if someone changes the PBIX (removes a column or renames somehting for example) then all the downstream reports that use it are going to be broken. So a degree of management and impact assessment is probably needed anyway when taking a common data sources approach.

That last bit is very definitely an opinion rather than a hard fact.

S

[cssathya]

Thanks for the explanation. So while a 'dataset' can potentially be shared between multiple PBI dashboards/reports, it's not really setup currently to serve as the base model for other applications or even with SSRS based reports, and it looks like SSAS needs to step in at that point, as you mentioned. Can you elaborate on or provide links / pointers regarding your comment on the 'not-so-painless' experience of moving a PBIX based model to SSAS Tabular model? I am leaning towards that direction for future scalability.

[Anonymous]

If you open the PBIX in PowerBI Desktop then start DAX Studio you will be able to connect to the PBIX that's open in PowerBI Desktop.

If you look in the bottom right corner of the DAX studio screen you will see its connected to 

Then you can use this to connet using SSMS where you can script the tabular model out

This will give you the script you can execute against a regular SSAS instance

You need to edit the name obviously replaicng the GUID with somehting useable

{
  "create": {
    "database": {
      "name": "19bc289f-1522-412c-a270-de6614d49927",
      "compatibilityLevel": 1400,

As you try and execute this XMLA (that is actually JSON) you will find a number of invalid properties and other bits that your SSAS instance will choke on. I usually find I can just remove the relevant bit (extra property). I assume these are features/properties that are available in the newer version of SSAS Tabular (if you notice mine showed version 15.0.1.210 when connected to the PBI Desktop instance, my installed SQL2016 SSAS instance shows 13.0.4466.4. I would assume that if you were using the latest greatest flavour of SLQ vNext that the JSON will be entirely fine. One day feature parity will make the current BI stack train wreck of incompatible features a distant memory (I can dream)

Hey presto a PBIX migrated into a TAB SSAS instance. Though now you have to faff about loading data into it etc.

Totally seamless experience 🙂

[kaarel]

Hi!

Power BI Report Server also runs hidden Analysis Services (SSAS tabular) instance in the background. This is how it works with Power BI Report Server.

1) Run CMD as admin.

2) TASKLIST /FI "imagename eq msmdsrv.exe"     (this will show running instances of SSAS)

3) netstat /ano | findstr "12620"  (replace 12620 with the correct instance, and then You'll get the port nr)

4) Go to C:\Program Files\Microsoft Power BI Report Server\PBIRS\ASEngine and change msmdsrv.ini file settings  <ListenOnlyOnLocalConnections>0</ListenOnlyOnLocalConnections> This will make it avaible from outside of the Power BI Report Server computer. You can also try to change the port from there, so it would stay the same?

5) Enjoy connecting to the Power BI desktop models uploaded to PBI Report Server

PS! Models only became available in the hidden SSAS instance, once the reports are refreshed or opened in the Power BI Report Server.