Is Power BI Ready For Enterprise Solutions?

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:

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 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 &

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 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.

Tell The Joke Again – Blogging On Blogging

I just returned from a meeting with some of my peers at SolidQ and we were talking about the value of blogging and publishing articles.  A few days ago, another one of my peers asked me to review his first-ever blog post before it was published.  Douglas McDowell, CEO of SolidQ North America, shared a blog post he wrote earlier this year about his perspective on this, which I found quite insightful.  It’s about sharing information that someone has shared with you.  I now share this with you:

Tell the joke again

by Douglas McDowell

Have you ever retold a joke? Of course you have, we love to hear jokes and retell them. But no one ever tells the joke the same way they heard it, they change it to reflect their personality, make it funnier or fit a situation or audience better. A part of them comes through in how they retell the joke. And retelling the joke is usually as (or more) entertaining to the person retelling the joke as it is to the people hearing it.

The last few weeks I have been spending a lot of time doing one-on-one meetings with folks on the services delivery team, mostly around their quarterly plans for non-billable contributions and professional development. What a blessing that has been! It is tiring to spend your day in back-to-back meetings, but it I don’t remember when I have gotten to spend so much direct time with our people talking about how to make themselves, and in-turn SolidQ, so much better. The reality is that we all try to pack too much into our days, and it is far too easy to only focus on what has to get done and put off what could get done. But the stuff that could get done—but doesn’t necessarily have to get done—is often what is most valuable: investing in ourselves and SolidQ – the company that lets us do what we love to do.

A reoccurring theme in my quarterly planning discussions has been blogging. What a great way to reflect on who you are, where you are going and what you are finding along the way, and then reinforcing or exploring further your discoveries. Everyone I spoke with agreed wholeheartedly — but almost none of them blog, myself included. Interestingly, a common comment that people volunteered was that they do not feel like they have much new or worthwhile to blog about. Each time I heard this I directly challenged it. I challenged the notion that blogging was for the benefit of the reader… I think blogging takes a completely different form when the writer is a little selfish and writes it for their own benefit. After all, it’s their blog, its their own online journalism column about whatever they want to write about and the reader and “public” aspect of a blog is really just about accountability and sharing oneself with others. Wow, I started that concept with selfishness and ended it unselfishness. Guess that makes my point that I have convinced myself and have been trying to convince others that blogging is a win-win proposition for everyone.

I challenge you to tell the joke again.

Perhaps, if you started blogging today, you would not have a single new thing to tell the world. But I think the world would be worse off if we only told jokes we made up (painful). So… Retell a story. Explain how you figured out something obvious. Announce something exciting. Explain your reasoning behind your perspective. Share your epiphany moment, adding accountability to what you know you need to do next.

Go blog.​

Learn How to Use Datazen this Week

SQL Server Pro Magazine is hosting my 3-part series, Designing a Mobile Dashboard Solution with Microsoft DataZen on Thursday.  The first one hour session begins at 11 AM Pacific, 3 PM Eastern Time.  There are three sessions in the series which include:

Session 1: Introducing DataZen and Building a Server – 11am EDT

Session 2: Essential Mobile Dashboard Design – 1pm EDT

Session 3: Advanced Dashboard Design & Techniques – 3pm EDT

I’ll be online to chat and answer your questions.  I hope to “see” you there!

– Paul

Power BI, Self-service and Governed Data Solutions

Power BI is due to be released to the masses this week and I’m very excited to see this powerful product made available to so many for so little.  We’ve had some of the capabilities of Power BI in other Microsoft products for a few years so why is this this different and what does it really mean for most business users and IT shops?  I think there are enormous possibilities in this new frontier of easy-access, self-service analytics but there are also big challenges ahead to manage systems with a different style of discipline.

As I visit organizations interested in Business Intelligence and data analytics, and talk to people in the industry about self-service tools; I hear some common and often conflicting themes:

“Business knowledge workers want self-service tools to get answers from reliable data”

“CIO/IT leadership: let’s enable the business users to do their own reporting and analysis so our IT staff can do other things”

“Data should be centrally-managed by IT and DBAs so there is one version of the truth and the data can be secured and maintained”

“Self-service BI empowers everyone in the business to be self-sufficient and more productive”

Isn’t self-service BI easy?

On the surface, yes.  It sounds easy:  Import some data, clean it up a bit, join some tables together, write a few simple calculation formulas and throw it into a chart… and viola!  A dashboard.  With some practice and a little training, some parts will be easy but the challenges of data integration, conformity, quality and master data management are ago-old problems that will always be present when data is sourced from different systems and is handled and manipulated by different people.  This is where the democracy of self-service BI and the rigidity of governed solutions can collide; and BI projects of any scale will get complicated… regardless of the tool.

The good news is that we’ve been doing this a long time and have developed disciplines and best practices to manage blended solutions.  With careful management and diligence, ad-hoc solutions can be made to scale and endure the transition.  In the article I wrote last year, titled “Business Intelligence Roles and Team Composition”, I discuss lesson learned about balancing business-owned BI with IT-managed BI and how team members assume roles to balance solution objectives.

Another very important consideration is that the objective for most self-service analysis projects to create “good enough” models and reports for a specific purpose.  When a project graduates to a different form, intended for more users to answer more questions or for leaders to make informed decisions; different rules of data quality and reliability apply.

Back to Power BI

One of the most significant reasons I’m optimistic about Power BI as a serious business tool is that it is comprised of technologies I’ve learned to use in serious business scenarios over the past several years.  Although the new user experience is simple, the product heritage is quite extensive.  Let’s do a quick review of the products and components that have led to the current Power BI service offering from Microsoft…


The lion’s share of the work on Power Pivot and Power View begin back around 2008 and the first versions of these tools were release in 2010.  This included the Power Pivot add-in for Excel 2010 followed by integration with SharePoint, along with Power View.  Power Query was released in public preview shortly afterward and then all these tools were integrated into the first official Power BI subscription for Office 365.

This is where things got complicated for enterprise customers… Many enterprise-scale organizations don’t upgrade Microsoft Office on an aggressive schedule so business users don’t have access to these wonderful self-service BI tools in the corporate licensed copies of their desktop software.  The transition from 32-bit to 64-bit Office has been a tough nut to crack as well.  Enter the Power BI Designer (soon to be called “Power BI Desktop”)…  With the new designer, there are no Office/Excel version compatibility problems but if Excel users want to continue to use Excel, they still have that option.

Enterprise-Class BI

There are several things to consider when scaling a solution but in brief, Power BI currently offers a few options for managing data centrally or migrating datasets to managed servers.

  • The Data Management Gateway enables data in a published model to be refreshed at regular scheduled intervals with on-premises or cloud-based data sources.
  • The Analysis Services Connector enables reporting & visualization from an on-prem instance of SSAS (currently Tabular only) without copying the data to the cloud.
  • The Power BI REST API gives us the ability programmatically push data into, and refresh, the model at will.

We work with some organizations who, for various reasons, can’t utilize cloud services for some of their reporting scenarios.  Most data professionals I talk to don’t know where this boundary lies for them and it’s an important discussion to have with strategic leaders and decision makers.  Many businesses are adopting cloud services for strategic use and I’m learning to approach this topic using appropriate language to discover where those opportunities are. For example; if I were to ask a business leader if it would be OK to move their precious data to a data center offsite and outside of their IT control, she will probably say “no”.  But, if I were to ask if we could consider using an encrypted cloud service from a trusted service provider that is likely more secure than your online banking site – at a fraction of the cost and in less time, she is likely to say “let’s investigate that option”.  Sometimes, it’s a matter of presentation.  A healthcare company may not be able to allow patient  personal information to be used in a cloud-hosted data model but may have no issues publishing facility data in a securely-managed solution.

The good news is that a lot of Power BI capabilities are available for on-prem solutions with cross-compatibility between Microsoft BI products.  The path to move between cloud-based Power BI and the confines of the corporate firewall isn’t entirely concrete and is a moving target on a few fronts, as the products continue to evolve.  The two points I think about in this regard are that: one, development of these tool is moving forward at a rapid pace, giving us new options all the time.  The second point is that we’ve had a lot of experience testing the water and learning what options are supported and actually work well.