Power BI Solutions for the Enterprise – Part 1

Power BI is a bona fide sensation.  It’s only been about seven months since the rebooted version 2 release and transition from Office 365 to PowerBI.com and Power BI Desktop.  Since that time, Gartner has moved Microsoft to a leading position on the Magic Quadrant for Business Intelligence and Analytics Platforms.  Like a young, prized race horse sprinting to the lead from the center of the pack; Power BI is being taken seriously and bigger players are considering the right timing to place bets. 

Microsoft is rated by Gartner as the having the most complete vision while Tableau and Qlik rate slightly higher in their ability to implement.  This makes perfect sense to me as “Microsoft BI” is not one simple product choice.  This is my opinion: The Microsoft BI platform has many components and offers many choices, but is certainly comprehensive and scalable.  Microsoft have always erred on the side of offering choices where some competitors have a monolithic product.  Power BI is great single-product solution for simple projects, and with room to grow as needed.  At scale, Power BI is delivering “the last mile” of data visualization beautifully on top of the robust foundation of SQL Server, Analysis Services, Integration Services, HDInsight, Stream Analytics, machine learning and all the other Azure services.  

Three months ago I attended the first meeting of a new Power BI MeetUp group in Portland, Oregon.  Last week we had a full house.  Power BI groups are popping up all over the world as users, businesses and BI practitioners are embracing this product.  The Power BI Community site currently lists 59 user groups!  Capabilities added to the product each month are numerous and pace of development is increasing.  The first wave mainly focused on features for self-service users and small group scenarios.  The next wave will address larger-scale scenarios, security, administration and developer tools for integration.  The SQL Server and BI product teams have made their intentions clear in recent announcements.  Jen Underwood’s article, “Top Takeaways from Microsoft's Reporting Roadmap” last month underscored that the new BI platform will address big business solutions in the cloud and on premises.  We’ve seen tremendous momentum but there is more progress to be made.

My son, who is in his late twenties and has a mild interest in data, was recently introduced to Power BI.  He read the marketing material and watched some demos.  He told me, “Dad, this stuff is easy.  What’s the big deal?”  I agreed - sort of; the easy stuff is easy but solving real business data problems will always be challenging, regardless of the tool.

The best analogy I can make for an enterprise-scale Power BI solution looks like this:  As a stand-alone product, Power BI addresses certain needs with tools that cover everything - ETL, data modeling, calculations, analysis and presentation.  As solutions scale, that might just be the tip of the iceberg.  For larger solutions, Power BI blends with enterprise technologies like Analysis Services and SQL Server.  In this depiction, Power BI is above the waterline and other tools may be used for everything below.

In an on-premises solution, the architecture is similar to BI solutions we’ve been building for years; using time-tested skills and design patterns.  In brief, data is sourced and transformed with tools like SSIS.  Data is cleansed, conformed and modeled in a dimensional schema stored in SQL Server relational tables, and then processed into a semantic model with SQL Server Analysis Services (either multidimensional or in-memory tabular).

A scaled Power BI solution can utilize on-premises data in one of two ways; either with scheduled refresh to synchronize data with a cloud-hosted model, or by running queries live against on-premises databases as users interact with reports.  Power BI Gateway components, essential in this scenario, may be downloaded from PowerBI.com.

We are likely to see more of the components that I depicted in the full-scale on-prem diagram added as cloud services; notably, as Microsoft continues to expand Windows Azure and Cortana Analytics.  This raises a lot of questions about specifically how these puzzle pieces fit together, but the platform is evolving rapidly.  From what I have seen, and what I know, many of the open bases will be covered in the near future.  This year you will see numerous gaps filled for enterprise customers and solution integrators.  Looking at the Azure platform rather than on-prem, data can be sourced, transformed, cleansed and hosted in a similar fashion.

Please watch for part two of this article where I’ll discuss the design approach to architect a scaled solution using Power BI.  I’ll demonstrate how to use the Power BI personal and enterprise gateways to enable these capabilities.