Friday, May 22, 2015

Switching on to PowerBI

One of the most powerful applications just about every business has at their fingertips is Excel. What this product can do is truly amazing when you explore it fully. The downside is that most people use less than ten percentage of the product capabilities.

Some of the more advanced features of Excel are Pivot tables. In essence, these allow you to create basic data cubes to easily slice and dice your raw data to create information that has value and provide insight. Sadly again, few people even know what Pivot tables are, let alone even used them.

The growing demand in our increasing data driven world is having a method of producing meaningful information from a vast array of raw data input sources. Microsoft is providing such a solution in the form of PowerBI.

The latest version of PowerBI from Microsoft is now available in preview for free. You can sign up at:

http://www.powerbi.com

When you do you’ll be presented with some sample Retail Analysis data that look like this:

image

As interesting as that is it is a little abstract. Where you begin to appreciate the role that PowerBI can play for you is when you select the option to Get Data.

image

Here you’ll now see a variety of sources that PowerBi can report on. One of the options you’ll see from the above screen shot is Google Analytics web data.

image

When you connect up your Google Analytics you’ll get a new dashboard, as you see above, with all of the information about your web site. In this case, I’m look at data from my www.anzacsinfrance.com web site.

image

At the top of the dashboard you’ll see a box into which you can type a free form query. So if I type “total hits” the dashboard automatically shows me the result as you see above.

image

If I now extend that query to “total hits last month” the result is immediately updated and displayed as shown above.

image

If I extend that further to “total hits last month in turkey” I see a result of 6 website views from Turkey in the last month.

image

If I extend that once more to “total hits last month in turkey compared to france” i now get a graph as shown above.

Hopefully, you can see the possibilities and the depth of reporting that is possible. And of course you can pin these queries to your dashboard so they display upon your return.

image

Apart from the adhoc dashboard you can create multi page reports. The above is an example from my web site data. Again, you can customise these easily in the web browser or download the PowerBI Designer for your desktop.

As you saw earlier, one of the data sources I can use is a plain old Excel file, uploaded from your desktop or saved in OneDrive consumer or OneDrive for Business.

image

Once the spreadsheet is available to PowerBI, now you can start creating reports based on the Excel Pivot tables I alluded to earlier. You do this by simply dragging and dropping the desired column heading into the appropriate locations (axis, vales, etc). The difference here is that I am doing this in a browser in a way that I can pin the result to a dashboard and report.

image

I can also now quickly and easily change the style of graph that is display, change the data I report on, create more results on the page or create additional pages effortlessly.

image

I can also easily share my dashboards with others when they are complete.

Microsoft is announcing more and more options for data sources with PowerBI. One of the upcoming options will be analytics from Office 365 with the soon to be released Office 365 content pack for PowerBI.

Whats new in Office 365 Administration from Microsoft Ignite 2

Which should allow you to produce report from Office 365 like you see in the above screen shot taken from the announcement blog post.

At the core of PowerBi is the concept of data sets. Raw data sets are ordered by Excel style Pivot tables with the results being surfaced through PowerBI. Thus, to get meaningful results you need to understand Excel Pivot tables. If you don’t now might be the time to fire up that version of Excel and start learning!

Hopefully, giving you this small inkling of what is possible with PowerBI will inspire you to dive into the product and learn what it can offer. I know I am and am amazed everyday with what is possible and will be detailing more in upcoming posts. Given that it is also in preview and free to access is an even better reason not to hesitate but to dive in now and power up with PowerBI.