I’m working on several projects right now that incorporate Power BI and learning some valuable lessons along the way, so I thought I’d share some thoughts and experience. I love Power BI and I think it can perform some very cool and valuable business functions. Being challenged with solving real business problems with real data for real consulting clients; it’s natural to both find the tool’s limitations and to discover functional design patterns to solve those problems.
In the last year or so, Power BI has surfaced as a truly impressive tool for self-service projects. A data analyst can import data from just about anywhere, transform and clean it up, model the data, create some calculations, reports, graphic visuals and dashboards. The analyst can publish the whole thing to the Power BI cloud service and share it with others who have the same email domain. In this scenario, everything works great. I make a point of using the Analyst as an example because this is the sweet spot for this product, more so than for the Developer or Solution Architect wanting to integrate dashboards into a larger solution. I’m very encouraged with the capabilities to extend Power BI dashboards with programmatic data sources and real-time data from Stream Analytics and other Azure services. I’m hopeful that we will soon have more capabilities to incorporate this product into IT solutions by embedding visuals into a frame or control, passing parameters, navigating to an from a report using actions, links or expressions.
Like most any other tool, Power BI doesn’t do everything. There is a lot of effort currently underway to add more desired functionality and an active community of people asking for capabilities that may have existed in previous products. A lesson I’ve learned creating BI dashboard and reporting solutions over the past 13 years or so is that there isn’t one tool that does it all. Taking that topic a little further, as different products are developed and go in an out of vogue, the replacement products don’t always evolve forward to include functionality of older product – even from the same vendor. I work primarily in the Microsoft data platform space and I’ve come to realize that there are several factors that influence how these software products mature. From the outside, it’s really easy for a user or developer to say “hey, what are you people thinking? …Reporting Services does X, Analysis Services can do Y, Excel can do Z… why can’t you do all those things and more?”
In a recent conference presentation, a senior group program manager was sharing future investments his team at Microsoft were making into new Business Intelligence features. At the end, attendees lined up at a microphone to ask questions. It’s funny how the psychology of a large group can change into an angry mob after someone gets restless. People began asking how the leadership and product planners could have possibly omitted a particular feature or missed the mark in the product design. Some attendees spoke with a trembling, angry tone and said that if they would only add one specific feature, this would transform the product from “absolute useless” to “useful and powerful”. The reality is that people have different ideas about what features are most important and it’s hard to please everyone. By the way, the Power BI development team is paying close attention to the suggestions and questions posed in the community support site: community.powerbi.com.
I, too, have a wish list of features that, in my mind would be transformative and would make Power BI a better integrated dashboarding tool. However imperative I believe some of these features to be, they’re important from my perspective and for the solutions I build. Here’s a quick run-down of capabilities I think are important; some that exist, some are in development and some remain on my wish list:
Reporting with On-premises Data
We have this capability today using a few different approaches. I have solutions in-place that synchronize the deployed model with on-premises SQL Server using the Personal Gateway. Don’t let the confusing name fool you. If the “personal” gateway is installed on a database server or an on-prem machine with an admin account, it runs full-time as a service and can sync data any time. Power BI lets us synch data up to eight times a day at 30 minute intervals (e.g. at 1:00 AM, 1:30 AM, 8:00 AM, 8:30 AM, etc.) My experience setting up the gateway has been pretty good so far.
Using the Analysis Services connector, we can visualize data and run reports (which are deployed to the cloud) against a live, on-premises Analysis Service Tabular database.
Using the Power BI REST API, we can programmatically push results to a deployed model from anywhere. Of course, this is more work and effort but it provides more flexibility.
Currently, shared .PBIX models (meaning Power BI queries, data model, reports and dashboards) must be deployed to the PowerBI.com cloud subscription service to be shared among users who have an email address in the same domain. A user, analyst or developer could also connect to on-prem data by opening the .PBIX model file on their desktop or network file share.
As far as being able to publish to an on-prem server for sharing without the cloud service; we know this is coming but don’t have a timeline. Pyramid Analytics is working on an extension of their product that will allow Power BI Desktop to publish to a licensed Pyramid server.
Sharing Reports and Dashboards Outside Your Domain & PowerBI.com
Not a lot of options here. My consulting clients are either creating a “guest” user account on their domain or setting up a separate Power BI subscription for each of their customers.
In the first release of Power BI for Office 365, we had the ability to expose a report externally by embedding it into a web page frame but this capability is explicitly disabled in the new product, apparently for security reasons. There several people asking about this in the Power BI community forum. Several sources have suggested that a solution is in the works but there is yet no official promise of reviving this capability.
Printing and Exporting Report Data
For users who need or want to export results to Excel, PDF or to print them; a Power BI report currently doesn’t have any capabilities other than to print the screen. However, if you create your data model and reports in an Excel file with Power Pivot and Power View, you can publish to a OneDrive or OneDrive For Business (SharePoint Online) folder and then add a link to the file as a report. This adds the Excel pivot tables to the report collection and exposes the rich feature set of Excel Online. We can add Power BI reports and dashboards to the “Excel” model using the web-based report designer. You can even use the gateway to schedule refresh to on-prem data.
There are trade-offs to this approach. You must continue to use Excel as the authoring tool rather than the Power BI Desktop. I’ve ran into a 10 MB file size limitation that I was able to overcome by enabling the old Power BI add-in for the Office 365 subscription associated with the PowerBI.com subscription. This raises several questions that I have yet to completely answer. Since the Office 365 version of Power BI is going away sometime in the near future, how and when will the 10 MB file size limit be addressed? In the meanwhile, can Office 365 customers still add the old Power BI service to their accounts. The cost for the old add-in service was $40/month, added to an Office 365 Enterprise subscription.
Thanks for a good summary.
I look forward to Microsoft reveal how Power BI will support the Analytical process.
For example, to add the missing functions/icons and to put them in the right order.
Great perspective. While a group of people may not be able to agree on exactly what features make a product enterprise-level, most would agree the new Power BI does not yet meet the definition. In many ways, the standalone version is still lacking from the self-service perspective. The frequency of updates and the fact that the product team is incorporating community feedback at a rapid pace though has been extremely encouraging. Considering how it has evolved, Power BI is at an odd stage that reflects the maturity of certain components that have been around for years paired with a lack of polish and gaps in basic usability reflecting its rebirth outside of Excel. Considering how it will be a key component of and in many ways the “face” of Cortana Analytics, I hope that a lot of the gaps that you and others have mentioned are addressed soon for the sake of that endeavor.