Friday, October 28, 2016

In Action: Technical Preview of Power BI Reports in SSRS

Yesterday the SSRS team made the announcement that we can now consume Power BI reports inside Reporting Services. You can upload your Power BI files (.pbix) to the Report Manager, or deploy them to the Report Server from Power BI Desktop if you like.
To start off, I really like the features and the things you can do, however I also found some issues I will be talking about later.

How to get started

The SSRS team did a pretty good job describing How to get started with the Technical Preview, so I won't repeat that here, just read their blog.
A few caveats:
  • Your virtual machine name in Azure needs to be all lowercase letters, in Chrome the error message displays correctly, but in IE the error message you receive is not very helpful, according to Koen.
  • I used West Europe as my Azure Region, so I had to login to the RDP as WESTEUROPE\nvvroenhoven
After having gone through the setup of your Azure VM you'll have to wait 10-20 minutes for your VM to be provisioned and you're good to go.

What does it look like?

After logging in to your VM you can open the report manager via the SSRS Preview shortcut on the desktop and you end up with an overview of the available KPI's, Mobile reports and Power BI Desktop Reports available on the server:

Open a Power BI Desktop report

Clicking on one of the PBI Desktop Reports gives you the Power BI report (which was created with 1 page) in the report manager interface:

Edit a Power BI Report

You can edit the reports in Power BI Desktop by clicking on the 3 dots in the upper right corner of the report tile in the web portal and clicking "Edit in PowerBI Desktop". By the way, I believe there's a space missing between Power and BI :-).

When you open the report in PBI Desktop you also get a pop-up saying they're busy opening your report, the links in that pop-up are still on the ToDo list I guess because they are pointing to :-)

SSAS Live connection

As stated in an earlier announcement by the SSRS team: "This preview supports Power BI reports that connect "live" to Analysis Services models – both Tabular and Multidimensional (cubes). We plan to extend support to additional data sources in a future preview." You can see this by looking at the Data Source settings and in the lower right corner of the report in PBI Desktop. As a consequence of connecting live to SSAS you are not able to edit the data model or adjust any queries.

Saving a PBI Desktop file to SSRS

When you save the file you have the option of saving it to disk or directly to SSRS.

When you choose the option of SSRS make sure to type in http://localhost/reports instead of https for this VM.

If you've already saved it once to SSRS and click save (or CTRL+S) afterwards in PBI Desktop, it keeps this setting and saves it automatically to SSRS.

Manage a Power BI Report

You can also manage the report item with the following properties:

Manage Data sources

For now, only live connections to SSAS are supported, but that will certainly change in the future. You can also use credentials to connect to the SSAS instance, but for now I'll use the default settings. 

At my first attempt to test the connection I got an error message, the next attempts did work correctly, no idea why that happened.

Manage Security

You can also customize the security for a specific report, before doing so you get a confirmation dialog box to confirm your settings differing from the parent folder:

After I clicked Customize security, but didn't alter anything, I started to run into some issues I'll describe here.

Issues with the Technical Preview (released October 27th)

Test connection

I encountered this error when I tried to test the connection to the SSAS Tabular cube. The second attempt did work correctly. I made no changes to the settings, I only clicked around a bit.

Customize security

After clicking the button "Customize security" I tried opening the report (I renamed the report to Sample Sales Report 2) again but the Power BI logo kept displaying.

Opening the report in PBI Desktop also didn't work and resulted in the following error:

Also when I open the .pbix-file from explorer and try to save it to SSRS I get the following error:

Any of the other reports (where I didn't alter the security settings) opens and saves perfectly fine. So in my opinion these errors all relate to the same issue with the security settings.

After undoing the security changes, so clicking on "Use same security as parent folder" everything was working fine again.


All in all some great new features have been added! I bumped into some minor issues that I'm sure will be resolved in later versions.