In-Depth

First Look: Power BI for Office 365

Microsoft, which recently demonstrated the new Power BI for Office 365 at its WPC Conference in July, said the new product is aimed at those starting to transition to BI and those who want to provision BI solutions in the cloud.

• By Kurt Mackie
• 08/08/2013

Microsoft talked more about its Power BI for Office 365 service, which it is preparing to release this month as a preview.

Power BI for Office 365 combines some familiar and emerging Excel data modeling and visualization capabilities with cloud-based collaboration. Microsoft first showcased the new service at its Worldwide Partner Conference in July. The aim, per Microsoft, is to empower knowledge workers in organizations to create their own data sets and queries in a "self-service" business intelligence (BI) scenario.

Many of the capabilities currently exist in Excel tools, and Microsoft considers Power BI for Office 365 to be an extension of its existing BI systems. Michael Tejedor, a senior product marketing manager at Microsoft, explained in an online press briefing late last month that Power BI for Office 365 might be used by an organization that doesn't want to provision BI on premises, or it may be a solution for organizations that are transitioning to BI.

"We don't see it [Power BI for Office 365] as a replacement for what I can do on premise, but I can go that route when I don't have BI on premise," Tejedor said.

However, premises users will need some sort of Office 365 cloud connection. "Customers will be able to extend their existing on premises data by creating Excel and Power View reports that can connect to and stay up to date on these on-premise data sources from the cloud in Office 365," a Microsoft spokesperson explained via e-mail.

Power BI Components
Many organizations already may be familiar with how Power BI for Office 365 works from using Excel tools, and that's one of Microsoft's main marketing points. In addition, Microsoft is building access to the BI controls from within Excel. The service contains many components, which are listed in the table below. Microsoft changed some of the component names, sometimes just adding a space between words.

Power BI for Office 365 supports multiple text and file types (including XML JSON and OData), as well as various relational database management systems (such as Microsoft's SQL Server products, as well as Oracle, IBM DB2, Teradata, MySQL and PostgreSQL). It can tap into private public and private data sources, such as an organization's local data, Active Directory and SharePoint Lists, or sources such as Windows Azure Marketplace and Facebook. It can even use "big data" implementations such as Windows Azure HDInsight or Hadoop, according to a Microsoft blog post.

Microsoft's Inventory Demo
Microsoft's press briefing, under embargo till today, mostly provided an update about Power BI for Office 365 in the form of a demo by Tejedor. The main contention of the demo was that data could be shaped, modeled and visualized by knowledge workers using the familiar Excel user interface. The Q&A component was the most impressive feature of the demo. After creating a model with Power Pivot, Tejedor showed how easily it was to query the data set for additional insights using Q&A by typing common phrases into a dialog box (called a "speech bubble"). Q&A will display the visualization it thinks you want to see based on your query. "The system interprets the semantics of what I type," Tejedor said, noting that it works by comparing two objects. Some experts, such as Microsoft MVP Chris Webb, have also noted the impressiveness of Q&A in the demos that Microsoft has shown, but he remains skeptical whether Microsoft can pull off such a feat, according to a blog post.

Another impressive aspect described by Tejedor is the ability to refresh the data, which is really a refresh of the data model for the workbooks that have been published. Once the model is created and stored in the cloud, data can just get appended to the model by clicking "data refresh" in Excel. IT pros also have the ability to manage and refresh the data sets (either manually or scheduled) housed in Power BI Sites using the Data Management Gateway. This management application also can control user access to data. Tejedor said he thought that took place via Active Directory Federation Services, but this is an evolving area.

The demo that Tejedor showed was based on a real-life restaurant-bar business case, he said. It turns out that bar owners get billed based on the weight of the alcohol brands that they order. The process is a manual one, with bars typically getting monthly weighting reports from alcohol sellers. Bars can underperform their profit expectations if their drink sales revenue is lower than what would be expected based on these alcohol weight reports. Profitability gets reduced if bartenders pour more than a specified amount of alcohol per drink.

According to Tejedor, analysis performed via Power BI for Office 365 showed that six brands of alcohol out of the 139 brands served by the bar were underperforming profit expectations. Bartender inefficiency was costing the bar owner about $5,000 per month in lost profits. Half of those losses were coming from vodka stores, particularly the Crown Russe and Stolichnaya brands. In response, the bar owner bought his own scale for weighing alcohol bottles and decided to just control for the six underperforming brands. To raise bartender awareness, the bar owner also ran a contest for the bartenders, offering a trip to Hawaii if they most closely approximated the correct pour rate of 1.5-ounces per drink.

The analysis was performed by a "nontechnical" person, namely the bar owner, using Excel and various components of Power BI for Office 365. He appended the weight reports by saving them in a folder, which is another way of accessing data sources. It might be that the bartender's business insight will be worth more than the cost of the subscription, but that's difficult to determine. Microsoft isn't saying when Power BI for Office 365 will be generally available or what it will cost, but it's expected to sold on a monthly recurring subscription basis.

It's also somewhat unclear how easy it is for knowledge workers to work in Power Query to clean up data. Microsoft added the ability to unpivot columns in Power Query, which "90 percent of analysts" requested, according to Tejedor. Unpivoting data appears to be a way to change column data to rows, according to this Microsoft explanation. The process went smoothly in the demo, but it's maybe a bit obscure.

Analyst Observations
One of the more important aspects of Power BI for Office 365 is it enablement of Power View and Power Map rendering via an HTML 5-based browser running on a tablet. Microsoft plans Power View HTML 5 support for Windows 8, Windows RT and iOS tablets by the time that Power BI for Office 365 reaches general availability, which hasn't been announced yet.

Currently, the on-premises version of Power BI using Excel and SharePoint lacks this HTML 5 support because it's based on Silverlight, according to Andrew Brust, who is the founder and CEO at New York-based Blue Badge Insights, as well as being a Microsoft Regional Director and a Microsoft MVP.

"What you don't have [in the on-premises version] are the dashboard presentation that they're adding to SharePoint Online and certainly not anything HTML 5-based because Power Map and Power View visualizations are based on Silverlight now," Brust said in a phone call. He added that the really new part of Power BI for Office 365 is having a presentation layer on top of the applications and gluing it together as a cloud layer. He discounted the idea of a premises-based Power BI.

"I don't believe there is a local server implementation, at least initially," Brust said. "I believe it's Office 365 only. The closest thing to on-premise would be the stuff that's already available -- so Excel 2013, the four add-ins and SharePoint 2013, which gives you a lot of the same stuff, but it's based on Silverlight for rendering and other bits and pieces that are different from the Office 365 Power BI subscription."

Brust was similarly impressed by the Q&A feature, although skeptical because he said he had not tested it.

"What I've seen in the demo is actually very impressive because this isn't just natural language query," he said. "By giving it instructions, not only are you telling it what to query but it's interpreting how you actually want to see that rendered. So it's making intelligent decisions about what kinds of visualizations to use and so forth. That's very different from just being able to use English to query some data and get it back in text form. From what I saw it actually looks very powerful and without much competition from other vendors, but we all know that in demos those things can be made to look great. And I'm really interested in its linguistic range or what the limitations are or not, and I won't know that until I have my hands on it."

As for whether Power Pivot and Power Query would open up access by nontechnical knowledge workers, Brust offered a mixed view.

"It's accessible by people who don't have formal training on BI and data warehousing and all of the vocabulary and all of the query languages," he said. "But I think it would take somebody who is really motivated and interested in working on the data to have the patience to work with it. It's detail-oriented work, and you can't do detail-oriented work casually, but it does lower barriers to entry. I would say other pieces, especially Power Map and Power View, are very accessible. It's a more exclusive group of people who would be comfortable working with the Power Pivot add-in and with Power Query because those are the places where you are doing data shaping and data modeling and sourcing the data. Some people are going to be really fascinated by that and some are going to find it tedious."

Microsoft is repackaging its existing BI technology with the Office 365 cloud, according to Boris Evelson, a vice president and principal analyst at Forrester Research. He noted that it's no longer viable for organizations to rely on the old model of running relational databases and getting IT support for BI queries because things change too fast. The trend is more toward what Forrester calls "agile business analytics." While Excel has been a No. 1 BI tool over the years, Microsoft has plenty of competition. The competitors include QlickTech, Tibco, IBM Cognos and SAP, among others, which get assessed in a Forrester Research report, "The Forrester Wave: Self-Service Business Intelligence Platforms, Q2 2012" by Evelson. A second Forrester report examines advanced data visualization platforms.

"Pretty much every leading BI vendor has a comparable tool," Evelson said. "Where Microsoft differs is by putting this in Office 365. No. 1, you don't really need to learn a new tool. As long as you know how to use Excel, you're already a Power BI expert. And because it's in the cloud, there's no provisioning, no upfront infrastructure and capital investments -- it's very easy to procure and get going."

Relational databases have limitations for BI because of their use of fixed and rigid data schemas, he noted. Consequently, it can take days or months for IT pros to respond to a model change request. That's changing with the self-service BI approach.

"So that's where this particular technology [comes in] -- which is in-memory, it's not relational," Evelson explained. "It's basically rows and columns, and by manipulating those rows and columns right in memory, right on the screen, you build your own models. It's not that you don't have a model -- it's that you've built your own models on the fly rather than having them prebuilt by a professional. That's the real strength of these in-memory technologies."

Can Power BI for Office 365 tap "big data" sources, such as Hadoop, as Microsoft has suggested? Evelson offered a qualified, "Not really."

"Yes, you can take Hadoop data and load it into SQL Server," he said. "Typically when we talk about Hadoop, we talk about really, really large data sets. Nobody uses Hadoop on small data sets -- it just doesn't make sense. So, when you have huge data sets, petabytes of data, when you want to load a large chunk of it for detailed analysis, you can load it in SQL Server, and once you have it in SQL Server in the relational format you can take an even much smaller data set and you can load it into memory -- but now we are talking about gigabytes, not even talking about terabytes. Theoretically, am I using Power BI on Hadoop data? Yes, but we're looking at apples and oranges here." The Microsoft spokesperson clarified that "the limits are those of the workbook size itself, which is currently 250 MB and will grow over time."

Evelson said that it is important to understand the use cases for such in-memory self-service BI solutions. Microsoft's Power Pivot and BI tools from other vendors work fine for non-mission critical analyses, but he was skeptical when compliance considerations are important considerations for organizations.

"If I have a highly regulated report -- I have a certified report for the FDA and every single column, every single row on that report the FDA has to approve…can I use Power Pivot for that? Probably not, because it just gives you too much flexibility -- I can't control what someone does," Evelson said. "But when it's not a highly mission-critical report that goes to the outside -- and I really need 'what-if' analysis and I need it now -- then that's the right tool."