Microsoft Unveils BI Roadmap

Today at the SQL PASS Summit 2015, Microsoft shared its BI roadmap for next year and beyond. The BI cloud roadmap shouldn’t be a surprise to anyone. It’s centered around Power BI. The main takeaway about the on-premises roadmap is deemphasizing the role of SharePoint and Office in favor of Reporting Services. In SQL Server 2016, SSRS will be extended to support Datazen reports. A future SQL Server update would support publishing Power BI Desktop files as well.

“Just as we’ve added mobile report delivery to SSRS in SQL Server 2016, we intend to add governed Power BI Desktop report delivery in the future.”

Although this news will surely cause some commotion in a short term, I believe it’s a good news for customers and BI practitioners in a long term. For customers, you no longer need SharePoint and SQL Server Enterprise licenses if all you need is sharing some reports and dashboards. So, this move brings significant cost savings in both software licenses and operational expenses. However, if you have invested in SharePoint you can continue using its BI features in SharePoint Server 2013 and beyond. For BI practitioners, you no longer need to deal with SharePoint complexities. MVP and the community have been asking for years for a simplified deployment model and now we have it. And removing SharePoint and Office dependencies will remove adoption barriers caused by aligning release cycles across different Microsoft product groups.

I’m personally glad that SSRS was chosen as the workhorse of the on-premises BI roadmap. SSRS is a mature product and it’s a natural choice to step up to its new role. Apparently, it’s not trivial to decouple the Power BI Service from all the Azure backend infrastructure so we can host it on premises. Long live SSRS!

Power BI Embedded Dashboards Without Authentication UI

UPDATE: An updated sample is included in the Chapter 12 source code of my “Applied Power BI” book.

One of the biggest Power BI strengths is its open architecture. In addition to opening the visualization framework, Power BI introduced REST APIs that let developers embed dashboard tiles in custom web applications (embedded reports will probably follow soon). Microsoft provided a sample application (Integrate-a-tile-into-an-app) on GitHub to demonstrate how dashboard tile embedding works. This application navigates the user to an authentication UI where the user signs it to Power BI before the application can access the user content. However, there are plenty of embedded reporting scenarios where this isn’t desirable. For example, you might have a web application that already authenticates users with Forms Authentication and you don’t want to ask the user to log in to Power BI again. Instead, you might want to pass the identity of the authenticated user to Power BI. In the more advanced (but increasingly common) scenario, you would want Power BI to pass the user identity all the way to an on premises Analysis Services model so that data security works.

The bad news is that currently Power BI doesn’t support custom security. Custom security is on the roadmap but currently you can’t pass application users to Power BI. This isn’t as bad as it sounds. For example, if you have a limited number of users or external customers, you can register them in Power BI so that you know their Power BI usernames and passwords. For example, if my company does business with Acme1 and Acme2, I could register acme1@prologika.com and acme2@prologika.com with PowerBI. Then, once the acme1 user authenticates with my web application and request a dashboard, I can authenticate the user with acme1@prologika.com to Power BI.

The good news is that Power BI supports OAuth2 for security. OAuth2 is a very flexible security mechanism and it supports different flows via the grant_type parameter. One of the flows that it supports is the grant_type=password flow that allows you to avoid the Power BI authentication UI step if you know the user credentials. This is conceptually similar to how Basic Authentication works. The OAuth2 grant_type=password scenario is also referred to as two-leg authentication (the three-leg authentication is when the Authentication UI is involved).

To demonstrate this, I’ve made changes to the Microsoft sample app and uploaded the modified sample (attached to this blog). Configure it in Visual Studio as follows:

  1. In the application settings, change the ClientSecret, ClientID, UserName, and Password to match your setup. You obtain ClientID and ClientSecret when you register the application with Azure AD. The username and password must match the credentials of a registered Power BI user.
  2. In the application Build tab, make sure that the NOLOGIN conditional symbol exists.
  3. Right-click on the project and then click Manage NuGet packages. Download and install all referenced packages as they are not packaged with the sample to reduce size.
  4. Run the application. The “Sign in to Power BI” button should be disabled. When you click Get Dashboards, the app should be able to retrieve the dashboards on behalf of the Power BI user. From this point on, the app works as per the original sample. Again, the big difference is that the Authentication UI is not shown because you don’t need to collect the user credential.

Special thanks to the Power BI Team and Rui Quintino by DevScope for sharing insights. For more information about how APIs for embedded dashboard tiles work, read the “Power BI API updates roundup” blog by Lukasz Pawlowski.

In summary, the sample demonstrates how you can use OAuth2 two-leg flow to avoid authenticating the user twice and redirecting to the Power BI authentication dialog. As a disclaimer, this is not a production-ready sample (there are hardcoded links, no error handling, no refresh token flow, etc.).

SQL Server MVP for 12 Years

Microsoft awarded me again with the Most Valuable Professional (MVP) Award for SQL Server. This is an annual prestigious award given to selected individuals worldwide in recognition for their expertise and contribution to the community. Currently, there are 371 SQL Server MVPs worldwide and only 85 in the USA. This makes it 12 consecutive years for me as SQL MVP!