Power BI – What Is It, And Why Does It Matter?

Power BI for Office365 was first shown to the public last week at the Microsoft Worldwide Partner Conference. Power BI is in my opinion, the most significance advance that Microsoft has made in the area of Business Intelligence in the cloud, and it significantly advances their offering in personal Business Intelligence. I’ve been evangelizing Microsoft’s recent personal and team BI features for some time now, and this announcement builds on that momentum. The demonstration raised a lot of eyebrows, and showed off some of the great strides that Microsoft has made in this space. One little gem in there that was easy to miss was the fact that we got our first glimpse at the HTML 5 version of PowerView – at least the renderer. It also raised a number of questions as to how it works and what will be necessary to get it working optimally. I had the opportunity to speak with a project manager and managed to answer a few of these questions. This post is attempt to explain what this product is all about.

Overview

Firstly, if you haven’t already seen it, you need to see the demonstration that was done by Amir Netz during the Day 1 keynote. It’s really one of the most compelling demos that I’ve ever seen. I include it below.

Amir Netz Announces Power BI at WPC 2013

 

Microsoft has provided a web page to describe the product and let you sign up for a preview. It also contains links to download some of the constituent parts, but it doesn’t necessarily do a great job of explaining exactly what you’re signing up for, or what these downloads are for.

To start with, Power BI requires Excel 2013. This is because all of the constituent components rely on the xVelocity (PowerPivot) engine that is included with Excel 2013. Make no mistake, as far as Microsoft is concerned, the future of multi-dimensional analysis is with the xVelocity in-memory engine. This engine isn’t new – it’s what powers Power Pivot (note the space between the words Power and Pivot… that’s also new).

The “Power” branding I think is encouraging as well. The business intelligence offerings from marketing have been confusing to say the least. Check out this handy “quick reference guide” if you don’t quite understand what I mean. There’s a very large assortment of tools that come from two different product groups (even after the reorg) and it can be difficult and confusing to navigate. Marketing hasn’t made this any easier in the past, but the consistent “Power” prefix I think goes some way to remedying this.

Power BI itself is the culmination of several different projects – Data Explorer, GeoFlow, BI Sites, and work in the mobility space. The first two of these have been in the public for a little while now, and were given their release names this week – Power Query, and Power Maps respectively. In addition, Power Query reached its General Availability milestone, making it acceptable for production use. The BI Sites project has been a very well-kept secret, and last week was the first time that it has been seen in public. Finally apps were shown for both Windows 8 and iOS that should go a long way to address the deficiencies in the mobile space. Unfortunately, nothing was said about Android or Windows Phone, but I have to think that they’re not far off.

Given that there are several components to Power BI, I think that it’s worth outlining each one and how it will be used. To start with, Power Query can be used to discover, acquire and transform source data and load it into an xVelocity based data model in Excel. It can also register the data connections through a “data steward” service that will reside in Office 365 – this will allow for tenant wide discoverability of the data. Power Pivot will then be used as necessary to modify the model, create calculated measures, format columns, etc. Data analysis can then be performed in Excel, using pivot tables, charts and/or Power View. In addition Power Map can be used to visualize geospatial data on a rotatable, zoomable 3D map. Once ready, the workbook will be published to an Office 365 document library, where a BI site will find it. BI sites can then be used to perform natural (English) language queries on the data, add to the mobile applications, and to schedule data refreshes whether the data is public or private. Finally, the mobile apps can be used to consume the reports from the BI Site.

Power Query

Power Query went into general release last week along with the Power BI announcement, and you can download it here. It has been available in preview form as project “Data Explorer”. It’s an add in to Excel 2010 or 2013 that gives it Extract, Transform and Load (ETL) capabilities. I like to call it the personal version of SQL Server Integration Services, in the same way that Power Pivot is the personal version of Analysis Services, and Power View (to a lesser extent) is the personal version of Reporting Services.

As you might expect, it can pull data from a wide variety of sources. Web pages can be parsed, and tables scraped to provide tabular data. The usual suspects are all supported (SQL Server, Oracle DB2, etc.), as well as any OData feed, Azure Data Market, SharePoint lists, Hadoop and HDInsight, Active Directory, and Facebook. One of the more interesting data sources is a folder (not a file). If you point Power Query at a folder, it will merge the data from all of the files in that folder into a single result. Refreshes will pick up any new files as new data. I can see some real uses here for log files. Yes, the files do all need to be the same schema.

The killer feature for discovery here is the online search. In preview, online search gave you access to all of the Wikipedia data that has been expressed in tables. Now however, you not only have access to more public data sources, but you can publish your own data sources (on premises or otherwise) to a catalogue that resides in Office 365. Below is a screen shot from Amir’s demo.

image

Clicking on Online Search in the ribbon brings up the window on the right, where you can enter a search term, in this case “Microsoft Azure Datacenters”. You are then presented with a list of candidates, both public and private. Hovering over one of them will give you a preview of the data to be returned, and when satisfied, you can select Add to Worksheet, or if you would like to transform the data click Filter and shape, where you can modify data types, flatten relational data, merge or split fields, etc. Despite the “Add to  Worksheet” button name, the data can be loaded into the worksheet itself, the back end data model, or both. This distinction is  very important when working with very large data sets ( i.e. > 1million rows).

You can also see from the result set that the data is coming not only from Wikipedia sources, but from the Azure Data Market, and from On premises data sources. What the demo didn’t make clear was how those data sources get into the catalogue. Power Query is the answer here as well. When BI Sites are implemented in Office 365, Power Query will expose some additional UI elements. that will let you publish the query and transform that you have built into the enterprise catalogue. In fact, when you look closely at Amir’s demo screen, you will see these elements.

image

These don’t show up on a standard Power Query install

image

The transformation capabilities of Power Query are really quite impressive, but a detailed description is beyond the scope of this post. They can be seen in the version available for download. As an aside, models built with Power Pivot can be moved directly into Analysis Services. Given the relationship that I mentioned above between Power Query and Integration Services, I wonder if it’s on the roadmap to allow Power Queries to be brought in to SSIS. On the surface, it would seem to make sense, at least from where I’m standing.

Power Map

Power Map is the other component that’s available today, although as of this writing, it’s still in preview form. You can download it from here. Unlike Power Query, Power Map requires Excel 2013 – it won’t run on 2010 at all. Power Map has been available since last fall in the form of a preview as code name “Geo Flow”. It didn’t make it to the main stage in the announcement, but it’s a pretty impressive piece of technology.

What Power Map does is allow you to take a data model built with Excel and/or Power Pivot, and plot the data on the surface of a 3D map. The map can then be rotated and zoomed, and animations created around different views or time dimensions of the data. It wasn’t shown at the announcement last week, but below is a Power Map visualization of Hurricane Sandy data.

SandyImage

This was put together simply by plotting the location of the measurements, the pressure on one layer as a heat map, and the wind speed on another layer as columns. Storm category was used as the category to color the columns appropriately.

There are a number of limitations to Power Map currently, the big one is that it doesn’t yet work at all in SharePoint – it’s Excel only (and not Excel Web App). Given that fact, it’s really just a sort of one off visualization/presentation tool, no matter how cool it is. However, we did just see our first glimpse of an HTML 5 Power View viewer… could an HTML 5 Power Map viewer be far behind?

BI Sites

To my mind, BI Sites is the most exciting part of Power BI. Here is where we find the most significant advances yet in both Microsoft’s mobile and cloud BI strategy. Until now, the cloud strategy was hampered completely by the inability to keep data refreshed automatically, and mobile devices could settle only for mobile web approaches. With BI sites, we see not only a great first step into these areas, but a solid infrastructure to expand upon. Unfortunately, at the point of this writing, it’s not available. You can however sign up for the preview program here.

BI Sites is a SharePoint App, pure and simple. This fact was not even mentioned during the announcement, but I feel that it’s vitally important to understanding how it works, and what its limitations are. Once created, the app reads Excel content from your Office 365 tenant, and provides a great number of features. Let’s quickly run through a few of these features.

Data Refresh

The Achilles heel of using Office 365, at least for me, has been the inability to schedule data refreshes. The latest 2013 wave brought support for interacting with PowerPivot enabled workbooks, which was great, but data refreshes had to be done manually, and the results re-uploaded. On premises, PowerPivot for SharePoint can be used to perform this function, but PowerPivot for SharePoint is not available in Office 365. Since the data can exist both on premises, or in the public, how does a cloud based service refresh data from inside your firewall?

The answer is that you will install a service, a sort of “data gateway” somewhere in your environment. This gateway will manage the refresh of all the on premises data sources and push updates out to Office 365, NOT the other way around. This will not require any firewall configuration, and will not depend on App Fabric, or any other relatively complex infrastructure.

Once configured, the automatic refresh each individual workbook can be configured separately, as can be seen below.

image

 

Report Gallery

One of the things that you notice when you enable PowerPivot for SharePoint is a new library template, the “PowerPivot Gallery”. When you use this template, you will notice that you get a completely different UI for the library that is Silverlight based, and gives you previews, and access to data refresh capabilities. This is again absent in Office 365 due to the lack of PowerPivot for SharePoint. However BI Sites, gives you the same functionality, in a (presumably) HTML 5 based experience.

image

HTML 5

Power View is a self-service analytical reporting tool that was first introduced in SQL Server 2012 Reporting Services, and subsequently added to Excel 2013. It allows users to quickly analyze data from an embedded model, and is supported within SharePoint. It’s a great tool to get answers very quickly, but one of the concerns with it is the fact that is built with Silverlight. This limits is use, particularly in the mobile space, as Silverlight is not supported on iOS, Anroid, or (surprisingly) Windows Phone.

BI Sites show us the first version of a PowerView renderer based on HTML 5, which should work on all devices. When the workbooks are opened through the report gallery, you will see the Power View reports, but they are rendered in HTML 5. I suspect that we’ll continue to see Silverlight being used in the designer in Excel, but the rendering through BI Sites is done with HTML5, which opens up the world of mobile.

Data Catalogue

I hesitate to include this in this section, as it could just as easily be included in the Power Query section, but since it required the BI Sites capability in Office 365, I include it here. The Data Catalogue is that service that allows you to register data sources from Power Query. The catalogue lives in Office 365, and is managed from there.  Data sources in the catalogue can be discovered by Power Query users through the “Online Search” button.

The catalogue also supports features that help support the natural language features of BI Sites. These features are unclear to me at this point, but I will be sure to expand upon them once I get my hands on it.

Finally, to be sure, “Data Catalogue” is what I’m calling it in the absence of any official nomenclature from Microsoft.

Natural (English) Language Query

Natural language query was a very well kept secret. I had no idea that it was being developed until I saw it on stage. Natural language query allows a user to interrogate a data source almost as if they were performing a full text search. Based on their query, Power BI selects an appropriate visualization to display the results.

image

As you can see above, 1 shows that we’re interrogating a data model contained in the “Sales Reporting and …” workbook. 2 shows the query entered by the user in relatively plain English (no Select statements!), and 3 shows how Power BI has interpreted the natural language query. Finally, it determined that a tabular display was the best visualization to use for this result. However, many are available, and it uses the visualizations available to HTML 5 PowerView to display them. Number 4 shows other visualizations that may be relevant to the result set, and all you need to do is to select them to see them.

A few more results can be seen below.

image

image

image

New Visualizations

Any user of Power View will be familiar with the visualizations shown above. However, the one that brought the biggest applause at the announcement was the “king of the hill” visualization. This shows you a “winner” and “contenders” over a period of time, based upon a defined criteria. When applied to pop music over time, it looks something like this.

image

To get the full effect of this, I highly recommend watching the video starting at about 9 minutes in. Of course, this is a highly specific case, and I have to think that more interesting visualizations are on the way.

Limitations

As with most products, Microsoft giveth and Microsoft taketh away. Without having my hands on the full product, I have no hope of coming up with anything like a comprehensive list of limitations, but there are two that jump to mind immediately.

Power BI is limited to Office 365 only. That’s right, there will be no on premises version of this, at least not at launch. From a technical standpoint, I can’t see any reason why this must be, apart from either resourcing limitations within Microsoft, or strategic direction (or a combination of both). It remains to be seen whether Power BI is a “cloud first” product, or a “cloud only” product. Time will tell. In the meantime, those organizations that are still leery of cloud computing may miss out, but there is still a lot of goodness in the on premises offerings.

BI Sites is a SharePoint App, not a container. The workbooks themselves are still stored within document libraries in SharePoint. the default file size limit for Excel Services is 10 MB, and for SharePoint itself is 50 MB. It’s unclear to me if BI Sites relies on Excel Services (I assume that it does), and if so, your data models have a hard constraint of 10 MB. Even if Excel Services is not the bottleneck, they won’t be any larger than 50 MB. That is simply too small for some models, and will leave them out in the cold. I was told at WPC by a Product Manager that this is changing, but specifics are currently unavailable.

Mobile Apps

The final component (that is still unavailable) of Power BI is a collection of Mobile BI apps. Reports are selected in various BI sites and flagged for mobile use. Once flagged, they appear in the mobile apps and can be interacted with directly.

8358.Mobile BI.png-550x0

As an aside, I borrowed the above image from the Office 365 technology blog. Anyone that has attended my BI presentations in the past year will be familiar with the first data set in the app, a topic near and dear to my heart.

Apps were initially announced for Windows 8 and for iOS. No mention was made of Android or Windows Phone (?!?!), but I have to think that that isn’t far behind. Blackberry? I have no idea, but I wouldn’t hold my breath.

To finalize, I’m very excited about several of the new capabilities that Power BI brings, and the doors that it now opens. It’s another big step in moving Business Intelligence from the realm of the data gurus, and into the hands of those that can actually benefit from it. Once it is available to play with it, I intend to focus on it more thoroughly.

Advertisements

MVP Award, A Happy New Year Indeed

It looks like 2011 has begun very nicely for me, as I awoke to find an email from Microsoft notifying me that I had been given an MVP award for 2011! The MVP program has been around since the early 1990s, and according to the email, awards are granted to “exceptional technical community leaders who actively share their high quality, real world expertise with others. We appreciate your outstanding contributions in SharePoint Server technical communities during the past year”. Certainly more praise than I would accord to myself, but I’ll take it!

If you read this blog, what this means to you is that I will be in a better position to see and work with new products earlier, to have better access to the product teams at Microsoft, and to be better able to interact with fellow MVPs. Taken together it means that the advice offered here will be better, and hopefully more frequent.

I would also like to take the opportunity to thank Stephen Giles and Simran Chaudhry from Microsoft Canada, who both thought I was a worthwhile candidate, and who worked hard to make this happen. I would also like to thank Alain Fournier,Erik Moll,and Arshad Pathare, also all from Microsoft Canada, who have believed in Second Foundation Intelligence, over the past few years, and who are also strongly advocating on our behalf. Finally I would like to thank my business partner Ed Senez, who makes the business run, and everyone else at Second Foundation Intelligence, who has to put up with me on a daily basis.

Here’s to a great 2011!

BPOS is now Office 365

Microsoft announced the branding for the next version of BPOS today – the new name is Office 365.

We’ll see it early next year, but we already have a pretty good idea of what will be there. All of the constituent products (SharePoint, Exchange, LiveMeeting and Office Communication Server, now Lync) will be brought up to their most recent versions, and the Office Web Applications will be added in. In light of the changes, and the expected change in focus to the Office Web Apps, the new name makes a lot of sense. I’m just glad that I don’t have to call it BPOS anymore…

I’ll Be At the Microsoft World Partner Conference Next Week

The Microsoft World Partner Conference (WPC) is being held in Washington next week. It’s an different sort of conference, and I almost elected not to go this year. In the past I’ve found the sessions to be a little light (but then, I am a techie, and this is a business conference) but this year it’s a solid line up.

I’m looking forward to meeting a few of the Product Managers, particularly around the Azure and BPOS space. My spider sense is telling me that very interesting things are about to happen here, and my company is very well positioned to take advantage of the cloud, and the new emphasis on it from Microsoft.

It will also be very good to catch up with our friends from Nintex, who are not only fun, but are always working on interesting things.

If you want to follow the goings on, you can follow the #wpc10 hash tag on Twitter. I’ll probably be more active than normal on twitter, I can be found at @diverdown1964, and I may even try my hand at liveblogging,but don’t count on it.

If you’re going to be there,and you run into one of the many John White there, I’ll be the tall one. Please say hi!

Observations from AIIM SharePoint Summit

I just returned from the recent AIIM Expo show in Philadelphia. I haven’t been to AIIM since 2002 when we were building out our imaging product. I had a couple of reasons to go this time. I’ll keep the first one to myself for now, but the other was because Microsoft was making a big splash with the SharePoint Summit. I was very interested in what there messaging would be like to the “hard core” ECM market. All of the sessions that I attended were in the SharePoint Summit track.

The keynote was delivered by Eric Swift, the new GM of the division, and by Ryan Duguid, the “ECM guy” at Microsoft. Eric gave a very good talk, and I was extremely impressed by Ryan, who spoke at several sessions throughout the show.

Usability

A couple of great quotes from Ryan – ”ECM works when its invisible to the end user”. I couldn’t agree more. People will use a system when they can see a value for themselves, and when it won’t cause them much disruption. Far too often systems are “imposed” upon end users, and one thing that’s certainly true about information workers is that if they can find away around a difficult system, they’ll take it.

Ryan also said “If you can’t show users their personal payback, they’ll never adopt your system” which is likely why, according to Doculabs,50% of all ECM projects fail. According to Doculabs,this is due to the exclusive focus on one specific area of functionality required by one specific area of the business without taking into account the needs of the wider user community. All of which is saying the same thing.

The final keynote session was presented by Ryan and Bert Sandie from Electronic Arts. Their talk was on how to provide an excellent user experience, partly by using gaming principles. On the surface, that sounds odd, but it makes a ton of sense. If you makes tasks more interesting, people will be more likely to perform them.

As an example, Ryan demonstrated Ribbon Hero, which is an add-in to the Office suite. It installs a button in the ribbon, and presents you with a set of challenges. These challenges are application related tasks and it helps you to varying degrees as you perform them, and you gain skill points by doing so. It allows you to compete with others, increasing your motivation. If you really want to drive use, hand out weekly rewards for “top scores”. A perfect example of applying the gaming concept.

Another concept that came out of this session that I’ve been preaching for years is that you should always include and understand the end users in any application design.Look at what people do, don’t tell them that it’s wrong – adapt it into your solution, and ideally improve it. If you don’t provide users a means of doing what they need to do inside the organization, users will find a way to do it outside.

Bert presented an interesting case study in usability. If you are familiar with the default search page in SharePoint, you’ll know that it is even simpler than Google’s. It’s essentially a white page with a search box and a go button. EA took that page and decorated it to look almost exactly like Google’s. Of course it said Electronic Arts instead of Google, but the letters were even alternately coloured. What was interesting is that by doing that one little thing, usage of the search engine increased 30%.

Bert also demonstrated that he could show that the creation of a single document paid for their entire system, and made a final point that the right user experience combines functionality, usability and aesthetics.

I really liked this focus on usability and community, which seemed to be a theme throughout the SharePoint summit, and was really refreshing to see at an AIIM show. I think that it’s safe to say that the large ECM players have not historically been particularly interested in usability.

Records Management

Microsoft waded into the records management area with the Records Center in 2007. It didn’t exactly meet with glowing reviews, but they’re really hit it out of the park in 2010. Through the new records center it supports all of the traditional records management requirements with file plans etc, but at the same time, it brings RM to the end user through in place records management. Users no longer need to go through many steps and secret rituals to get documents under management, a document (and any other piece of content!) can be declared a record through a simple click of a button. Document routing makes sure that if necessary the content moves to the record center while leaving behind a stub.

Ryan Duguid showed a slide which indicated that if left unchecked, an organization that currently manages 2 TB of data will be managing 45 TB of data in 5 years time. However, if disposition policies were put in place that disposed of 10, 20 and 30% of content annually, that future growth number would shrink to 25, 10 and 4 TB respectively. The RM features in SharePoint 2010 can help bring this reality about

Interestingly, the next day Cyrus Mistry gave a talk on the way that Google manages their content. In essence, they don’t. The mantra is to keep absolutely everything forever, open it up to everyone and rely on search to find it. I actually agree with the opening up concept, but I think it’s impractical, not to mention legally dangerous to leave stuff lying around forever.

Cyrus also pointed out a couple of policies that I might consider implementing. One is that every Google employee writes a small blurb (very short) on their past week’s activities, and what their plans for the next week are. That is visible to everyone. I sort of like it from a few angles. Another is that users can contribute ideas to a central “idea pool”. Ideas are then voted upon, and if an idea gets enough votes, it becomes a project.

CMIS Connector Announced

At the show, it was announced that Microsoft will be shipping a connector for the Content Management Interoperability Services (CMIS) standard. This will allow SharePoint to act as a “front end” for external content management systems, and vice versa. This will allow for easy integration with legacy document management systems, and give the users of these systems a better experience without sacrificing capability.