News

Power BI for Office 365 Goes Live

• By Kurt Mackie
• 02/10/2014

Microsoft released Power BI for Office 365 today, indicating it's ready for production environments.

The product has hit its "general availability" milestone, meaning that organizations can purchase it and get service level agreement coverage under Office 365 plans. The pricing for the service was published back in January.

Power BI for Office 365 is a subscription-based service that relies on various software components (see Table), with some of them being cloud-enabled through Microsoft's datacenters. Microsoft recommends having an Office 365 E3 account to use it. Moreover, to consume business intelligence content, users need to be covered by the Power BI licensing.

Microsoft claims that Power BI supports hybrid scenarios that tap on-premises infrastructure, as well as the cloud. However, that claim is not so clear. Microsoft MVP Andrew Brust has noted that on-premises approaches have depended on Silverlight for visualizations and they presently lack the HTML 5 support that's used by some of the Power BI for Office 365 components.

Self-Service BI Concept
For end users, Power BI is principally centered on the familiar Excel spreadsheet program. The idea is to make the process simple enough so that office workers can even build the data models. Per that concept, Excel gets used as the main tool to consume both external and internal data. IT pros still control the data access using the Data Management Gateway component of Power BI, but office workers with access privileges supposedly can build out, and perhaps maintain, their own data models, which can be shared.

The degree to which this whole scenario becomes "self-service BI," as Microsoft describes it, is up for debate and would depend upon the expertise within an organization. Much of the modeling complexities are handled in the Power Pivot component of Power BI for Office 365, which is the main tool used to shape the data that's later used for charting, mapping and other graphical visualizations. A good illustration showing the sort of steps that an office worker might have to go through to create such data models can be found in a demo by Peter Myers, a BI expert for Bitwise Solutions. His "End-to-End Power BI" demo is part of a PASS Business Analytics Conference "sneak peak," which can be accessed here.

Another aspect of the Power BI for Office 365 solution is the ability of office workers to share their reports through the Power BI Sites portal. Data models can be set up to be automatically maintained so that the data stay fresh. The setup to refresh the data models can be maintained by IT pros using the Data Management Gateway application of Power BI, which gets installed on premises. Workers can select from saved data models and saved queries and combine them to create new reports, according to Microsoft's Power BI scenario.

Microsoft made searching for external data, as well as internal organizational data, more easy from the menu structure in Power Query. The search capability of Power Query was updated back in December, adding support for Sybase IQ databases and other data sources.

Organizations can explore data models with Power BI's Q&A component by typing "natural language" queries in a text box rather than using SQL queries. Q&A has been seen as one of the more impressive features of Power BI. It's capable of interpreting a query typed in English by returning an expected visualization of the data.

There's also a mobile "anywhere access" element to Power BI. The product currently supports Windows devices, using any supported HTML 5-capable browser or Microsoft's Power BI App. It's not supported on mobile phones or Android devices at present, according to a Microsoft FAQ. The Power BI team is currently working on delivering support for the Apple iPad, but there are no plans to add Excel for Macintosh support.

Microsoft Demos Power BI
Microsoft first unveiled Power BI for Office 365 in July and has demonstrated its capabilities at various points. Last week, members of the press and analysts got a demo of Power BI's capabilities going into general availability.

The online demo broke no new ground, but it showed how organizations can use Power BI to tap into publicly available data. The Webinar was presented by both Michael Tejedor, a senior product manager on the Microsoft Business Intelligence team, and Dandy Weyn, a technical product manager for Power BI.

The presenters revealed that Microsoft is building a public data catalog with the aim of including all of the world's publicly available data in it. For instance, the public Wikipedia site alone represents more than 500,000 data sets to tap, they said. Subscribers to the Power BI for Office 365 service will get access to a private version of that data set, according to Tejedor. The data sets get accessed through Power Query, with options to pull from the Web, from a file, from a database or from "other data sources." Those other data sources can include sources such as a SharePoint site, an OData feed, the Windows Azure Marketplace, a Hadoop file, Active Directory, Exchange and Facebook, among others.

The demo, presented by Weyn, tapped into New York City's 311 data, which is a compendium of nonemergency service responses based on local geographies, such as the city's boroughs.

First off, Tejedor explained a bit about how some of Power BI's components, namely Power Query, Power Pivot, Power View and Power Map, can be used to illustrate data trends. It's possible to load table data into system memory to process large data sets.

"Power Query is a new experience for Excel that makes it a lot easier for me to first discover the data, find the data that I want to pull into Excel, and then once I've found it, for an end user to really shape it," Tejedor explained. "So, allowing them to able to pull that data, transform it and land it inside of Excel. Once I've got my data inside Excel,…[the next] feature is Power Pivot. Power Pivot is a modeling environment that allows me to build connections with my data, be able to create KPI, create hierarchies -- the ability for me to add my own IT to the data, and be able to really analyze that data very quickly by processing the data in memory. So we can now analyze hundreds of millions of rows of data inside Excel by leveraging the latest in-memory technology. Then, once we've got our data model just right…it's really about being able to build those reports, be able to build those visualizations, be able to uncover the insights and being able to tell the story of the data. And that's where Power View and Power Map come into play."

Weyn explained during the demo that he had pulled in more than two years of NYC's 311 data, consisting of 3.3 million records. Power Query can retrieve data from the Web or a Web API, and the data then get loaded into memory. He showed how it's possible to map that data from a table to see what the most common problems were, based on the 311 calls. The biggest problem turned out to be calls about heating issues. However, the most common problem in July was with the water system. Another example of the modeled 311 data was noise complaints, which tended to spike on the weekends, as well as during the Superbowl, in NYC.

Weyn said that modeling the 311 data was an example of how businesses can take publicly available data sets and use them to spot potential business opportunities or trends.

Bing Maps is built into Excel so it's also possible to create geospatial representations of data models. Options to explore the data show up in checkbox inside Excel on the right side, based on the fields that are available in the data model. Users simply select one of those fields and it will show up in the Bing Map representation. Weyn described using Bing Maps to create a heat map of the 311 complaints. It's also possible to show the complaints over time, which can be exported into videos to be shared.

One of the more interesting questions floated during the talk was about the ability of Power BI to access unstructured, semistructured and "big data," enabled by Hadoop or Microsoft's HDInsight. Big data implementations are designed to handle petabytes of data, but Power BI is limited by Excel's capability to consume data sources that have file sizes of up to 256 MB.

"If I go to Windows Azure HDInsight, which is a Microsoft Hadoop distribution running on Windows Azure, what I can do is I can have a full Hadoop cluster and I am able to run high queries on that Hadoop cluster and then pull that result set directly within Power Query and thus enable that in Power BI and enable that as part of data refresh," Weyn said. "But the sweet spot is when you start to combine data sets. I can have data that sits on Windows Azure HDInsight and then merge that with other data that may be sitting in another database. So the combination of relational, OData feed, structured, unstructured…are natively built into the power of Excel."