Discover all about Power BI in Office 365
As it happens with Microsoft products, there are two competing versions of the same thing and eventually one gets phased out. Now it’s Power BI for Office 365’s turn. It had a good run, but Microsoft is finally killing off the initial iteration of Power BI to let its new service move in and run the show. I’ve been wondering when this would happen for quite a while now, and its finally happened. Honestly I believe it’s for the better, but there are a few things that still haven’t come to fruition. In your Office 365 Message Center, you might have seen this message on the right.
How Did We Get Here? And What’s Power BI?
“Bring the power of data to every user in your organization”
Microsoft has been putting tremendous effort into changing the game of Business Intelligence, wrenching it from the clutches of data analyst / report creator / SQL guru (think PerformancePoint 2007, SQL Server Reports, SQL analysis and integration services) and making it accessible for the savvy business user. This began with the introduction of Power Pivot and Power View functionality introduced in SQL Server 2012. This was required to lay the foundation for the base technology to really catapult the transformation of BI as you know it. Add-ins for Excel 2010 were released which included Power Pivot and Power Query. These add-ins were free, but you had to download and install them. You could build your data and visualizations in Excel, then upload them to a SharePoint site that had Power Pivot configured, which allowed web-based consumption of these great new visuals. It was a great start.
Then along came Excel 2013. The Power Pivot add-in was now included (but had to be enabled), we still had to download Power Query, but now we could also download Power Map. Sweet! To be sure we’re all on the same page, let’s clear up any confusion on these Power add-ins.
Ok so you want to make a “dashboard” of sorts. What are the steps involved? Well, at a very high level, you will be doing the following:
- Collect your data to be used in your dashboard / report from a data source
- Modify the data, making any calculations necessary as desired, in essence creating a data model
- Visualize that data model graphically in a report or on a dashboard using charts, graphs, etc.
Clear so far? Each of these steps maps to an add-in for Excel:
1. Collect your data to be used in your dashboard / report from a data source (Power Query)
2. Modify the data, making any calculations necessary as desired, in essence creating a data model (Power Pivot)
3. Visualize that data model graphically in a report or on a dashboard using charts, graphs, etc. (Power Pivot)
4. Visualize that data model in 3D geographically (Power Map)
Got it? These add-ins are really game changers, and put all the power at the hands of the business, not IT. You don’t need Access, you can build a complete relational data model in Excel! How cool is that?
Looking for Power Pivot samples? Get them here!
So while all this was going on at the client level in Excel, another initiative started. All this was great, but what about the server side of this? Where do we put all these Excel files?
As you likely know, Microsoft has its cloud-hosted service offering called Office 365. For one price you get email hosting, SharePoint sites, OneDrive, licenses for Microsoft Office, etc. You can add additional optional services a la carte to your subscription. One of those offerings was a service called Power BI. Microsoft defines Power BI as:
“Power BI is a cloud-based business analytics service that gives you a single view of your most critical business data. Monitor the health of your business using a live dashboard and create rich interactive reports…”
Around July of 2013, it was announced at the Worldwide Partner Conference a new feature set was coming. If you had an Office 365 subscription, you could add Power BI for a per-user per month fee. Basically this was some fancy wrapping around a document library in SharePoint Online launched via a Power BI SharePoint app. It gave you this different more visual rendering of your Excel Power View reports right in the browser. Additional functionality was created called Q&A, where you could pose natural language questions to your data and have Power BI auto-magically generate the answer in the form of a number, graph, chart, etc.
Freakin’ cool! A Windows 8 app was also created to provide the ability to get your reports on the go. This was made fully available to Office 365 customers around February of 2014.
After a while, it became evident that the service wasn’t being updated really at all, nor was the limited functionality Windows 8 app changed or updated. There were a couple updates dealing with getting data connectivity to your on-premise environment to refresh data using what was called Data Gateways. The data refresh options were very limited, and stayed that way for quite a while.
Then, we got the idea as to why. Microsoft had been working on a completely separate and standalone Power BI environment over in Azure. You would see this referred to as “new experience”, where the Power BI in Office 365 was the “current experience”. This was confusing for a lot of people, and it was uncertain what was going to happen. Would this new experience be integrated into Office 365? When? When should I use which? This standalone service was in Preview for quite awhile, getting updates and fixes very often.
New and Shiny
ow we get to why you’re here reading me go on about Power BI. In July of this year, Microsoft announced that the standalone Power BI service was coming out of preview and into general availability. Throughout the preview and especially now in general availability, the new Power BI service is being constantly updated, and has tons of new features that the offering in Office 365 never dreamed of. It seems every time I turn around I see connectors to new data sources, as well as new improvements and custom visuals or push data to power BI through the new REST API. There is also new mobile apps released for the service, as well as a new Windows app. Not to mention a pretty big reduction in price. Now Power BI is either FREE or $10/user for the Pro offering.
So how has our dashboard story changed in this service? You do this all in browser but your options are somewhat limited. Instead of Excel, you use the accompanying tool called Power BI desktop which replaces Excel as the tool to build your reports and visuals. All of the Excel add-ins were baked in and relabeled to help with confusion. You can still import data from Excel and even previous visuals and reports, it just won’t be your continued authoring source for Power BI (though you can keep doing that for local purposes). Let’s look at our new story:
- Collect your data to be used in your dashboard / report from a data source (Get Data tab in Power BI Desktop)
- Modify the data, making any calculations necessary as desired, in essence creating a data model (Data tab in Power BI Desktop)
- Visualize that data model graphically in a report or on a dashboard using charts, graphs, etc. (Report tab in Power BI Desktop)
- Visualize that data model in 3D geographically (Insert Map visualization in Power BI Desktop)
- NOTE – it’s not the same as the Excel version
- Publish your Reports to Power BI (Power BI Desktop)
- Create a dashboard in Power BI and pin visualizations from various Reports (Power BI browser)
With all this new and shiny, what happens to the old version? As I showed you at the start, Microsoft has finally announced they are deprecating the Power BI for Office 365 service as of December 31st, 2015. On a side note, I want to point out that all of these new and cool visualizations are also being baked into Excel 2016, as well as all of the add-ins mentioned before. Want to have baked in forecasting? You got it.
Microsoft has provided some guidance on the differences between the old and new experiences, but specifically the new steps to do old tasks with supplemental information on the Migrating to the new Power BI experience page and accompanying whitepaper. So where do you go from here?
- Read the whitepaper (linked from the Migrating page above) to learn how to actually migrate your content and reports over to the new Power BI
- Review all of the data sources you can connect to from the start from Power BI or Power BI Desktop
- Review the Power BI basic concepts page
- Head over to the Getting Started with Power BI page
- Read the FAQ if you have questions
- Download some samples and try them out
- Follow the Power BI blog, especially taking a moment to read the latest entry for Introducing the DIY Guide to Dazzling Data
- Participate in the Power BI community
- Get some awesome custom 3rd party free visuals to use in your reports from the Power BI visuals gallery
- When you want to dive deeper, start playing with measures, calculated columns and DAX formulas. Read more here and here!
If you need help in getting started with Power BI or need some advice or guidance, please contact us!