Are You Ready For Power BI?

Microsoft has positioned Power BI to be the industry-leading tool for simple self-service desktop data mash-ups but it also has a great heritage in serious business analytics.  I’ve had numerous conversations lately with new consulting clients; both business leaders and technical professionals, who recognize the promise of Power BI and are anxious to put it to work solving real data problems.  Are you ready to use Power BI to tackle your data challenges?  On the 27^th of August, I’ll lead an online virtual workshop, “Building Business Intelligence Solutions with Power BI” that will show you how to put Power BI to work.  I’ll demonstrate techniques to use with serious business data but we’re also going to have some fun with data from the National UFO Reporting Center.  This database contains all of the publicly-available UFO sighting information since Thomas Jefferson reported seeing a flying light in the sky up to the latest flying saucer sighting today. 

There are a lot of Power BI demos and articles out in the Interweb and Blogosphere so in this article, I want to give you some useful guidance to help you get started and to decide if Power BI is right for you.

What Does Power BI Cost?

Somewhere between free and affordable.  Power BI is free - up to a very gracious data capacity and feature set limit.  In short, the free subscription service provides everything you need to get started with serious data analysis and, for a lot of people, everything they will ever need.  No bait-and-switch here.  Seriously.  For more demanding business collaboration users who need to store more than one GB of data per user, Power BI Pro is about $10 a month.  Detailed pricing information is here.

What, Exactly, is Power BI and Where Did It Come From?

The main components of Power BI are the evolution of three products: Power Query, Power Pivot and Power View.  Microsoft introduced SQL Server Analysis Services nearly 18 years ago which can quickly perform analysis of complex, multi-terabyte data cubes and models.  Power BI data models share the SSAS architecture, similar to Power Pivot and SSAS tabular projects. 

The query editor incorporates Power Query, a streamlined data acquisition and mash-up instrument based on the powerful M data transformation script language within an easy-to-use desktop tool. 

Power View was the genesis of Power BI report visualizations, which include several standard and advanced charting and presentation widgets for dashboard-style reporting.  Beyond graphic presentation, report visuals are highly-interactive.  Nearly every visual element on a report serves as a data slicer for other report visuals and aggregated values.  Reports and dashboards are touch-friendly and work on a variety of mobile and desktop devices.

Will the Real Power BI Please Stand Up?!

The evolution of this product is a little confusing.  It reminds me of family weekend drives with my Dad when I was a kid.  He loves to explore and would try different routes, wandering around the forests of the Pacific Northwest, desserts of the Southwestern US and all over Europe.  We found interesting places, enjoyed a lot of scenery and every once in a while, we found a route we’d prefer not to take again.  There were maps on the walls of our home with hundreds of pins to mark the trails and commemorate all the places we had visited.

Likewise, Microsoft’s Business Intelligence toolset has been all over the map, usually with good direction but occasionally, not-so-much.  Power BI is quite literally, the culmination of many road trips; features tuned and perfected in some areas and evolving in others.  Without regaling in this extensive history, and turning this short article into a novel, Power BI has emerged from the refiner’s fire of many products, projects and experiments into a lean, mean reporting machine.  What a long, strange trip it’s been but I like where it’s going.

Today, there are actually two different Power BI products.  All of the “Power…” tools I mentioned were first created as Excel add-ins and in early 2014, Power BI for Office 365 enabled reports to be published and shared through an Office 365 Enterprise subscription.  It was a good first step but it wasn’t for everyone.  The latest incarnation doesn’t require an Office 365 subscription or for the model and report author to use a specific version of Excel.  The Excel-integrated add-ins will still be supported but Excel is no longer a necessary investment for data modeling and report authoring a’ la Power BI 2.0, in general availability since July 2015.  One advantage of using Excel to author data models is that I can use Excel Pivot Tables to visualize the data and the new Power BI reports and dashboards when the published workbook lives in a OneDrive folder. 

Data from (just about) Anywhere

There are currently 38 different data connectors that let you import data from just about any data source using the Power Query-driven query editor.  And we’re not just talking about SQL Server, OLEDB and ODBC sources.  The list includes Hadoop FS, Dynamics CRM, QuickBooks, Open Air NetSuite, Google Analytics, ZenDesk, Salesforce and GitHub, XML web services, OData and a variety of text formats to name a few.

The bottom line here is that if you can connect to it, we can get that data into a semantic model for reporting.  We can mash it up with other data sets to fill-in gaps and create a more complete business view and answer questions that have never been asked by people who probably believed that they couldn’t get answers so they never bothered to ask.

Collaboration Without Heavy Infrastructure

I do consulting work for large companies and when I show Power BI, a common question is “can we use it on-premises”.  Today, the answer is “Yes. Well, sort of but it depends”.  Small businesses have no problem using the cloud because they’re nimble and prone to adopt new services faster than big organization with stodgy business cultures.  Aside from a few legitimate roadblocks for cloud adoption, the vast majority of companies have plans to move portions of their infrastructure to the cloud.  Why?  Because, it’s cheap, that’s why.

You can leave all or part of your Power BI solution on-premises by using the data management gateway to synchronize published models with on-prem SQL Server or the Analysis Services connector to keep it all in your data center and then use published dashboards and reports to share and collaborate.  The Power BI REST API also gives programmers the ability to push data into a published model.  It’s also worth noting that more on-prem capabilities are in the works as we speak.

Building Business Intelligence Solutions with Power BI – Hands-on Workshop

Here’s a teaser for the three-hour workshop on August 27:

Attendees are provided with all the files and resources needed and partially-completed projects to catch-up and get started at different phases, and a completed solution for reference.  I start with an introduction to the Power BI service, subscriptions and the online experience.  Next, we install the Power BI Dashboard and start a new project.  We will connect to the National UFO Reporting Center and extract every publicly-available UFO sighting record for the past 115 years.

We’ll work through all three phases of design, beginning with data source acquisition and query design.  Here, we cleanse and transform source data and then prepare it for modeling.

I will cover query design from simple, menu-driven features through complex scripting using the robust Power Query M transformation script language.

After the data is modeled and measures are defined, reports enable interactive analysis and discovery.  A rich set of visualization controls make report and dashboard design flexible, fun and interesting.

We will wrap up by publishing the completed project to Power BI online, create a dashboard and use Q&A natural language queries to converse with the model, producing additional visuals and insights.

The new Power BI desktop and online platform is an exciting new frontier for self-service analysis and serious BI for small and large businesses.  For details and registration information, visit this page to learn more about  the “Building Business Intelligence Solutions with Power BI” eLearning series.