Power BI RoundUp with SQL Pass updates – Dataflows – Paginated Reports (SSRS) – Premium Metrics App with Data Flows & Paginated Metrics – Paginated Reports in Mobile App – Mobile & Service Update for Oct – Bonus features coming to Power BI Desktop

As expected this week with SQL Pass, there has been a whole host of new capabilities and features in Power BI, so please see my roundup below with the relevant links below.

And even though there is a lot of announcements here, we are still waiting for the update to Power BI Desktop!!

Power BI Dataflows

One of the biggest updates and releases to Power BI is dataflows. I personally think that this is a MASSIVE differentiator from other players in the Self-Service space.

The first thing I would like to highlight is that this is available to both Power BI Pro and Premium Customers. Below is a table of the key differences.

Dataflows allows you to now build data purely inside the Power BI Service, that stores the data in Azure Data Lake Gen2.

There are so many advantages to this:

  • Not only does it allow the business user to easily access data and use it in their data model as a data source (like SQL Server)
  • It also allows for the linking of entities (Premium feature)
  • It allows for Incremental Refreshing (Premium Feature)
  • Linking of entities which works like Excel (Premium Feature)

I would highly recommend that you read the blog post here: Introducing: Power BI data prep with dataflows

And here is the link to the Whitepaper: Understanding Dataflows in Power BI

Power BI Paginated Reports (SSRS)

There is now the capability to be able to use SSRS in the cloud. This is really great news and I am certain that there are a lot of customers who have been patiently waiting for SSRS to be available in the cloud.

It is now available in Power BI Premium. And along with this out of the box there are a lot of features that are already there, and some additional features which will come later. No doubt it is a very different set of requirements to build something that will work in the cloud vs an On-Premise implementation.

Some of the potential new capabilities which will be coming in the future to Paginated reports are:

  • Support for scheduling and sending paginated reports using e-mail subscriptions and having attachments
  • Embedding in third-party applications
  • Authoring reports against Power BI datasets
  • The ability to seamlessly drill through from a Power BI report to a paginated report in the service

All the details can be found in the blog post: Public Preview of Paginated Reports in Power BI Premium Now Available

Power BI Premium Metrics App includes metrics for Dataflows & Paginated Reports

It is incredible to see the pace of innovation from Microsoft and the Power BI team. Even though both dataflows and paginated reports have just been released you can now already use the Power BI Premium metrics app to see metrics for dataflows and paginated reports.

You can now see the following metrics for Paginated reports

  • Total views of the report with average for row count
  • Total time spent on the reports between retrieval/processing/rendering
  • Split by hour, dataflow name, and workspace name

The following is also available for data flows

  • Total refresh count
  • Refresh reliability
  • Average/max duration and average/max wait times of dataflow refreshes by dataflow name and workspace name

You can find more details on the Power BI Premium Metrics App here: Paginated report metrics and dataflow metrics now available in Premium Capacity Metrics app

Power BI Mobile App with support for Paginated Reports

If you update your Power BI Mobile App, you will now have the capability to be able to see paginated reports directly within your Power BI mobile app.

Once again it is incredible to see all the new functionality released across the entire platform, not only to the Power BI Service, but to the mobile app also.

It is available in the Android, iOS and Windows Apps

More details can be found here: Power BI Paginated Reports also available in Power BI Mobile Apps (preview)

Power BI Mobile and Service updates for Oct 2018

A lot of the updates for the Power BI Mobile and Service app have already been covered in other blog posts.

Below is a list of the updates with the relevant links

BI-RoundUp – Power BI Spring Roadmap (SSRS in the Cloud, Common Data Service for Analytics, Incremental Refresh & more) – Power BI Report Server Update – Building the Opioid Power BI Report

After last week, it appears that the Power BI team is still very busy, and after the announcement yesterday and the download of the Spring Roadmap there is a whole host of amazing things coming in the next few months!

Power BI Spring Roadmap

I will be going through the Power BI specific sections below to make it easier to understand what is coming where.

Power BI Desktop Announcements

All the details in the roadmap have almost been released, and as you can see from above Incremental refresh is coming to Power BI. Yes, it will currently start off in Power BI premium which is understandable as with current licensing for SSAS Tabular, if you want to partition your data (Which is what the incremental refresh is doing) you require an Enterprise License.

With that being said for large organizations this is going to be incredible to have their data updating in a fraction of the time.

Next is new is the Control over linguistic schema. Using the linguistic schema, the user can control the Q&A with phrasing and synonyms. Which translates to more options to people to ask Q&A questions and get the right answers.

Power BI Service Announcements

What I am showing in the image above is an SSRS (SQL Server Reporting Services) report being in the Power BI Service. That is really incredible considering people have been asking when SSRS will come to the cloud.

Now all the reporting requirements for an organization can be accessed from one central location. And not only that but with SSRS RDL reports there will be the capability to create pixel perfect reporting. This is really exciting.

A new capability is coming where you can pin items from Apps to your own dashboard. This will mean that you can create your own dashboard with items from different App sources.

There are also going to be more performance monitoring metrics for Power BI Premium so that people who run Power BI Premium are able to understand how it is performing and if there are any areas of concern.

Along with this as previously announced P4 and P5 Power BI Premium Capacities

Data source improvements where it will be easier to administer Gateways as well as being able to understand what is running where with regards to the multiple data sources.

There is also going to be some query acceleration for DirectQuery datasets that are very large in size. This appears to be done by building in-memory aggregations ont op of data sources such as     Spark and Azure SQL Data Warehouse. Which will then return the results almost instantly.

It looks like there is going to be some AI integrated into receiving the smart alerts. Which will assist in trying to deliver the right alerts to the end users. As well as the option to customize the alerts.

When the Power BI report is in Full screen mode it will cycle through the pages, which is great for when reports are being displayed on large monitors in hallways or shared spaces.

There is the move away from having the dependency of Office 365 groups within Power BI. In the new move it will all be managed within Power BI.

This will mean that assigning workspace roles to individual users, Azure AD security groups, and Office 365 modern groups will be controlled and live within Power BI.

What will be coming to Power BI Premium is the ability to be able to create report snapshots, and then decide via the multiple options on how to process and deliver the reports.

The administrator of the App Workspace or people can subscribe other users for email subscriptions.

That this essentially is, is a central place to store all your common data in your business. For example a list of all your customers are stored in the Common Data Servers for Analytics (CDSA). Then when a user wants to use Customer details they do not have to go and find the information and bring it into their dataset, they can leverage straight out of the CDSA.

The data can be ingested using the very familiar Power Query experience, as well as consuming the Get Data experience, which will also have the capability to connect via the On-Premise data gateway.

All this will sit on Azure storage, which can be added to with your existing Azure investments.

There will be a host of options in terms of how apps can connect and leverage the CDSA. This can be from Microsoft, from 3rd party providers or even potentially your own in-house created Apps.

All these details as well as more details can be found here: Accelerating digital transformation with the spring 2018 release for Dynamics 365 and Business Application Platform

Power Report Server – March Update

As you can see above the Power BI Report server has been updated with all the latest features from Power BI Desktop. This is incredible considering that some of these features were only released this month.

All the details are in the blog post: Power BI Report Server Update – March 2018

Power BI – Building the Opioid Power BI Report

Will Thompson from the Power BI team, goes into a very detailed blog post on how he created the Opioid Power report for the Gartner Bake off. I have put this aside as I still want to go through all the options and features and see how he managed to build such an awesome report.

You can find the blog post here, as well as the option to download the PBIX: Building our report on the opioid crisis for the Gartner bakeoff

Power BI Report – SSRS (SQL Server Reporting Services) Report Performance

Have you ever wondered about how your SSRS reports performance was?

  • How many people are using the reports?
  • How fast are the reports being rendered?
  • Are people running reports, or running them to export them?
  • What is the size of the reports?

In this blog post today I am going to give a brief overview of what I did to create the SSRS Report Performance and then how to use it after you have downloaded the Power BI Template File.

Overview for SSRS Report Performance

I had a requirement where we were looking for not only the usage of our SSRS reports, but also the performance and size of the SSRS reports.

So I created it in Power BI, so that this could be seen by the required people in the organisation.

To see the details of the query that I wrote you can go into the Edit Queries and see the TSQL Query that I wrote to extract the required data.

Further in the blog post I will explain how to easily take this file and run it against your own SSRS Reporting database.

Finally, this is the first draft for this report, so any suggestions, changes or additions will be welcome. Please leave me a comment.

Below is an overview of what each Page does.

Report Usage

As you can see above, this shows details around reports usage, who has been running the reports. As well as the execution times.

I have also put in a Report Name Filter (using the Smart Filter by Okviz.com) in order to filter for reports. As well as using calendar slicers to filter for specific periods.

Reports Performance

With this report we are looking for the report performance. This enabled us to view where the time was being taken for our reports that were being rendered.

I have used the Report Format, as a means to filter the reports.

As well as providing a legend with each report format type, so that you can easily see which one is taking the longest average time to complete.

I have also included the average times for the three area’s when a report is run. As this can help when trying to identify a specific report and where it is taking the longest. So by clicking on a report name at the top, it can then filter this below.

We have noticed that when people are exporting the reports to CSV or Excel that the majority of the time is spent in the processing of the report.

Report Format

What this page does is to show the Report Format that is being run and used over time, as well as reports usage.

This enables us to get an understanding of what reports the users are using, or are they running a report, to just extract the data? Or running mobile reports more than the standard SSRS reports?

I have also put in a Report Name Filter (using the Smart Filter by Okviz.com) in order to filter for reports. As well as using calendar slicers to filter for specific periods.

Report Size

The final report, is the report size. And this is simply to see which reports are the largest. And to see the report size over time.

A thing to note, is that the current screenshots are from SQL Server 2016, and I currently can identify which is a mobile report, but I cannot identify the name of the report.

I have also put in a Report Name Filter (using the Smart Filter by Okviz.com) in order to filter for reports. As well as using calendar slicers to filter for specific periods.

Download of Power BI – SSRS Reports Performance Template

You can download the Power BI – SSRS Reports Performance Template File here:

SSRS Report Performance.pbit

How to use Power BI Template File for SSRS Reports Performance

Below are the steps to complete once you have downloaded the SSRS Reports Performance template file.

Changing the Parameters

Once you open the file you will see a screen with the following parameters as shown below:

As you can see above we have got 3 parameters to enter.

  • The first is the SSRS SQL Server Name. EG: SQLServer\InstanceName
  • The second is the Report Server Database name. EG: ReportServer
  • The final parameter is a Future Scope with the Report Speed. You can leave this as the default for now.

Click Load to load your SSRS Reports Performance analytics reports.

Uploading to the Power BI Service – Enterprise Gateway Connection

If you want to use the Enterprise Gateway connection, you must ensure that you have got it installed and configured in both the Power BI service, and on your Server that has access to your SQL Server.

NOTE: I have found that it works as expected when both the SSRS SQL Server Name and the name that you configured in the Power BI Service for the Enterprise Gateway are identical.

Now you can create a scheduled refresh and share this with users in your organisation.

Keeping history for SSRS ReportServer Database

Just as a final note, currently the SSRS ReportServer database only keeps 3 months’ worth of history.

So if you want to keep the entire history I would recommend that you extract this data into another table. After which you can then modify the query to look at your historical table. If you do require assistance with this please let me know.