Power BI Roadmap Update

Many Power BI announcements are included in the Dynamics 365 Spring 2018 Release Notes (free download): https://aka.ms/businessappsreleasenotes

A Look Back at the Look Ahead to 2018

I have a few thoughts on four of these announcements (see Power BI Roadmap Thoughts below) but first, let’s briefly review several of the items from A Look Ahead to MSBI in 2018:

1. Enhanced DirectQuery Mode
   • See page 186 of the release notes
   • New feature: Query acceleration for large datasets (Public Preview)
   • “Users can create DirectQuery models over any size of data….and then accelerate common queries by building in-memory aggregations over some of the data.”
2. Incremental Data Refresh
   • See page 186 of the release notes
   • New feature: Incremental data refresh
   • “When creating a dataset in Power BI Desktop, a user configures a refresh table for each table in the model that is to be loaded incrementally and then publishes it to a Power BI Premium workspace.”
3. External Workspace Datasets
   • See page 187 of the release notes
   • New feature: Workspaces with Azure AD groups
   • I suspect that in addition to being able to assign team members to workspaces via Azure AD security groups, later in 2018 there will be the ability to publish Power BI reports to separate workspaces from the source dataset.
4. Reporting Services Reports in Power BI Service
   • See page 188 of the release notes
   • New feature: SSRS reports for Power BI Premium (Public Preview)
   • “Publish SSRS reports (.RDL format) to a Power BI Premium workspace, and view reports within the Power BI web app. No SSRS server is required.”
5. Create Email Subscriptions for Others
   • See page 188 of the release notes
   • New feature: Subscribe other users for email
   • “Set up email subscriptions for other users so they all receive relevant information.”
6. Global Slicers
   • See page 172 of the release notes
   • Report level slicers or slicer synchronization was released in the February 2018 Update to Power BI Desktop
   • “Report authors can use slicers that sync across pages and update visuals across a report”
7. Sharing a Power BI Report
   • See page 179 of the release notes
   • Was released in January 2019
   • “The report sharing capability works like the dashboard sharing feature…”

I’m cautiously optimistic that the Numeric Range slicer will be generally available soon. If it is, that could be added to the above list as it was #7 in the Look Ahead to MSBI in 2018. 

Thus, of the 11 items mentioned back on January 4th, at least 6 are either already generally available or are at least on the roadmap. I think it’s best to leave off External Workspace Datasets (or something equivalent) – for now. I’m just assuming that the separation of workspaces with Office 365 groups is a precursor to this very important capability, particularly if you’re relying on large import mode PBI datasets.

Unfortunately the Live Connection reports for Power BI Report Server didn’t make it into the March 2018 release of the Power BI Report Server. Remember, my confidence level was 4.0 for this feature so it would be a very pleasant surprise to see it make it in the next 1-2 releases (roughly July 2018 and November 2018). Until it’s released, you’ll need to be careful with the amount of RAM you have on your Power BI Report Server relative to the size and volume of Power BI reports in import mode you deploy to Power BI Report Server.

A high level takeaway could be that Power BI continues to move along rapidly, even faster than I projected.

Power BI Roadmap Thoughts

• Reporting Services Reports (RDLs) for Power BI Premium (Public Preview)

Obviously paginated reports aren’t as exciting or ‘modern’ as Power BI reports but they definitely have valid use cases and many companies rely on them. Without this capability, you’ve been tied to an SSRS server or the Power BI Report Server. Alternatively, you’ve had to use both the Power BI service and a report server on-premises or via IaaS in the cloud. Now the Power BI service can be a central hub of content for all MSBI report types (Power BI, Excel, Reporting Services).

• XMLA connectivity and API parity with Analysis Services

Wow. Do you see the image on page 189? (Visual Studio, SSMS, Tableau, Profiler, etc)

Page 189: “….you can treat Power BI workspaces as if they were Analysis Services servers.”

The ability to use the same set of tools familiar to Analysis Services developers is big. For example, when it’s time to migrate a self-service solution to a corporate BI solution, you could (as I understand it) just assign the workspace to Power BI Premium and connect to the server for the workspace for various development (Visual Studio) and management (SQL Server Management Studio) activities.

I wonder if this means that Perspectives, Display Folders, Partitions, KPIs, and other features will be available via Power BI Premium alone (not an Analysis Services server).

Lifecycle management with PBIX files has been a issue/limitation. Using OneDrive for Business for version history may be sufficient for Power BI reports but it isn’t a good option for large Power BI datasets.

This feature may also account for organizations which, while they like Power BI reports, also want to use Tableau or other data visualization tools against their Power BI datasets. I assume, for example, we could create reporting services reports (RDLs) against the Power BI premium workspace via SSDT for Visual Studio.

You may recall my Design Mode in Power BI Desktop blog post. The basic thought was that Power BI Desktop isn’t currently equipped for larger, more complex models that you see with Power BI Premium. Something closer to an IDE (integrated development environment) but more lightweight than Visual Studio would seem to make sense – something for the business power users and that a BI developer could find familiar from experience with SSDT. Given incremental data refresh and query acceleration features coming to Power BI Desktop, something like a Design Mode may still be coming. 

• Query acceleration for large datasets (Public Preview)

This truly is a game changer for many deployments and organizations that have invested in an enterprise data source to support BI (e.g. Teradata, SAP BW, etc). Let’s say that you’re currently not able to get the performance you want via DirectQuery but you also don’t want to create an import model and thus move your data and manage the data refresh process. This is a common scenario.

With this feature, perhaps conceptually similar to aggregations for SSAS Multidimensional models, the model author builds in-memory aggregations over portions of the data that will answer common queries. Therefore, past Power BI projects that couldn’t go forward because of performance issues with DirectQuery models or budget and support issues with import mode models may now have a viable option via cached aggregations for DirectQuery models.

I wonder if this feature or something similar will be available in the next release of Analysis Services. Per the release notes “users can create DirectQuery models…” I assume this feature will be exclusive to Power BI Desktop for now (not Visual Studio). Given the description for incremental data refresh, this suggests you’ll have two new modeling options in Power BI Desktop – aggregations for DirectQuery models and incremental refresh for import mode models. I’m curious if you’ll be able to specify the measures and columns you want included in the aggregations or what this aggregation design experience will be like.

I also wonder if you’ll be able to schedule when these in-memory aggregations are refreshed. Will it be similar to the cache refresh schedule (which supports dashboard tiles) for datasets with DirectQuery or live connection sources?

Finally, I’m curious if DirectQuery to Azure SQL Data Warehouse will continue to be considered an anti-pattern as noted in this blog post by the SQL CAT team. The release notes document identifies Azure SQL DW as an example data source for utilizing this feature.

Note that the release notes document doesn’t mention Power BI Premium as a requirement like the above two features or incremental data refresh.

• Incremental data refresh

This isn’t a surprise given the past whitepaper for Power BI Premium but it’s so important that I have to mention it in this post.

Per the release notes, a refresh table will be configured in Power BI Desktop for each table that needs to be refreshed incrementally. It will be very interesting to see exactly how this feature has been implemented and how it relates to partitions as we know them with Analysis Services models.

For example, does it create partition definitions and processing scripts behind the scenes? Also, if I create incremental data refresh in Power BI Desktop and then publish to a Power BI Premium workspace, will I be able to use Visual Studio and/or SSMS to modify this refresh policy via the XMLA and API Parity feature mentioned earlier?

All of this being said, the devil is always in the details and there’s no guarantee that something released as a Public Preview couldn’t remain a preview feature for a long time or contain certain important limitations. So we’ll see but I definitely like what I’m seeing thus far.

Mastering Power BI and Insight Quest Update

• My new book, Mastering Microsoft Power BI, is expected to be published over the next two weeks.
• This is almost certainly the last blog post until after the Data & BI Summit.
• I’ll be presenting DAX Query Use Cases at the SQL Saturday in New York City on 5/19/18.