Sunday, July 21, 2013 at 1:14PM
Melissa Coates in Power BI, Power Pivot, Power View, SQLServerPedia Syndication, Self-Service BI

A preview of Power BI will be available later this summer. Here’s a recap of what I’ve learned so far + why I think this is a very big deal for the world of Microsoft Business Intelligence.

Excel is Primary Tool for Self-Service BI

There are four components to Power BI, and all are delivered via Excel. Microsoft is capitalizing on the fact that a huge percentage of business users are most comfortable in an Excel environment. Historically a large portion of Microsoft BI was developed with SQL Server and SharePoint tools – i.e., software predominantly available to IT and BI professionals.

The newest Excel capabilities lower the entry point of BI to anyone who has Excel and an interest in working with data. SQL Server and SharePoint will continue to be my professional-level tools of choice for creating and delivering Corporate BI solutions. Meanwhile Excel has emerged as the primary mechanism for creation and delivery of Self-Service BI solutions. This continues to build upon the Personal BI > Team BI > Corporate BI framework promoted by Microsoft using a combination of Microsoft Office that’s installed on the desktop, Office 365 in the cloud, SQL Server and SharePoint.

Four Components to Power BI

Two of the Power BI components are for data preparation: Power Pivot and Power Query. The other two are for data visualization: Power View and Power Map.

Power Pivot. Imports and integrates data from various sources for the purpose of creating an in-memory data model. This allows a functional user to combine various data sources which improves its value – classic examples here are the addition of weather data or demographics data to corporate sales data. The Power Pivot data model also supports calculations, derived fields, aggregates, hierarchies and key performance indicators. The ability to create a data model with a relatively low learning curve is a very big deal for data analysts, particularly for one-time analysis type of situations. It can also be helpful to IT and BI folks for prototyping.

Power Query. Formerly Codename Data Explorer, this tool will be able to search for data (just like a web search experience) online or within your corporate data sources. The selected data set is immediately imported into an Excel table – this search feature alone is a game-changer. Some of the native data connectivity it offers are truly distinctive and make data access far easier than it’s been in the past. It also allows you to do things like merge data, rename columns, replace values and other data modifications in a step-by-step process. A very big deal here is that the step-by-step data cleansing and shaping process is savable and therefore repeatable. You can also view the state of the data before and after each step. Optionally, you can bring the data into your Power Pivot model to continue working with the data even further.

Power View. Power View is an interactive data visualization tool. It does certain things like highlighting and cross-filtering really well with very little learning curve. Working with data in Power View bears similarities to working with Excel Pivot Tables as well as to PowerPoint. In addition to charts, graphs, and table data, Power View also supports maps which pan and zoom via integration with Bing Maps. The naturally interactive behavior of Power View is what makes it a very big deal.

Power Map. Formerly Codename GeoFlow, Power Map is a 3D mapping tool. What makes it unique is the ability to save scenes and play them over time in a tour.

Cloud-Based Self-Service BI

Power BI will be delivered in Office 365 (which actually uses SharePoint Online). Although the preview that starts later this summer will use Office 365, all 4 Power BI tools are available to use as Excel add-ins now (some functionality is not fully supported within on-premises SharePoint). Considering Microsoft has a cloud-first strategy, it’s not a big surprise that the initial preview will be with Office 365. It’s unclear currently if all Power BI functionality will be available to on-premises SharePoint sites following the initial cloud-based rollout. I hope so.

This focus on the cloud is a big deal, especially for small and medium-sized businesses. It can also be a good thing for business units that need self-service capabilities but don’t have IT support or resources to fire up the required infrastructure. Even large corporate environments can find some benefits to augmenting their on-premises BI environment with the cloud – especially if they want to upgrade to newer versions in the cloud faster than they can upgrade on-premise versions (i.e., a company-wide upgrade to Office 2013 can be a big task). Creating the SharePoint and SQL Server server infrastructure to support Excel Services, PowerPivot for SharePoint, Reporting Services, and Analysis Services is not an easy task. Not having to invest so much time, effort, and cost into setting up this infrastructure will be welcomed by many organizations. However, there are certainly some obstacles which prevent some companies from using Excel in Office 365 even if they wanted to. It will be interesting to see to what extent companies embrace the cloud, or utilize a hybrid approach to on-premise and cloud BI.

Mobile Support

Quite obviously mobile support is a big deal … Microsoft has announced native mobile applications for Windows 8, Windows RT, and the iPad. HTML5 will be used for browser-based mobile delivery on other types of devices. The way I understand it, to see a report on a mobile device it’ll need to be published to a Power BI workspace in O365, and in the properties of the report you’d set it as a “Mobile Favorite.” Translation: companies with Excel and Power View in SharePoint now won’t get to take advantage of this mobile functionality right away.

My understanding is Silverlight won’t be immediately removed from the stand-alone Power View application in or the SharePoint PowerPivot Gallery, but I’m guessing that’ll come in time.

Data Search and Refresh

Office 365 will utilize collaborative BI workspaces optimized for BI functionality. Users will be able to publish Excel workbooks to workspaces and securely share them with coworkers. Reusable data connections can be published, and data refreshes can be handled on-demand or on a schedule. Data refreshes can access corporate data on-premises as well as data online (with proper security credentials of course). Being able to search and access your enterprise data is a very big deal (this sounds like something the IT folks will need to facilitate for self-service users to take advantage of).

Queries created from Power Query will be able to be searched and shared among users. This sharing of queries is a big deal – anything that promotes reusability & consistency gets a big thumbs-up from me.

Tracking of Data Usage

A new feature I’m particularly excited about is the tracking of data usage. Stats re: how often data sources & reports are accessed will be readily accessible to those who build models and reports. This is currently a feature of Central Administration in SharePoint, or by querying the Power Pivot Management Data tabular model if you don’t want to grant Central Admin access to a non-SharePoint administrator. I look forward to seeing how easily this information can be reviewed for one report, one data source, or for many reports and data sources.

Easy access to these stats is a big deal because if you see usage going way up, then there’s an awareness that this particular report or data source has become critical. This might mean a data refresh failure is of greater urgency, or data quality is of even greater concern. Let’s say it’s a Power Pivot model that’s become critical and it continues to grow larger and more complex – that might be a perfect scenario to upgrade the self-service Power Pivot model to a corporate Tabular model in Analysis Services.

Data Stewardship

Lastly, a big component of Power BI is the concept of data stewardship. There’s not a lot of public information about this available yet. This will include management of security, data refresh permissions, tracking data usage, as well as a few other things that we’ll learn more about when the preview is available. I’ve typically thought of data stewardship with the mindset of data quality and/or master data management, so I’m extremely interested in seeing the direction Microsoft will take with this. Regardless of how you define it, data stewardship is the kind of thing that can really make a self-service BI initiative more successful and/or less problematic – so I think this concept of data stewardship will end up being a very big deal.

Finding More Information

Register for the “Microsoft Power BI for Office 365” preview – Office Site