​Recently, I have been working on a project that utilizes Power BI, so I wanted to share a short summary of what I have found by highlighting three good functions offset by three missing functions. The information here will help you evaluate if this is the right tool for your project.

The catalyst for my project is a popular use case: Management needs a dashboard that provides visual information that can be drilled into to uncover weaknesses. As in many organizations, the data is there, but it just needs to be surfaced in a visual manner.     

In my case, the data is in SharePoint lists. For this proof of concept, I chose to do this via Office 365 using Power BI.  Microsoft is feverishly updating Power BI nearly on a weekly basis. You can see how active they are by following this blog: http://blogs.msdn.com/b/powerbi/.  Power BI is a special type of site that uses Excel workbooks saved in a document library rendering the dashboards which are in the workbook created via Power View. 

Here are my findings.

The Good

The first item I needed to do was pull information from my SharePoint instance to show in Power BI.  Utilizing Excel with Power Query made this very simple. In other words, I did not have to utilize Power Pivot or any other type of technology.  As shown in the screen shot below, I open Excel, click Power Query, Choose to pull data from a SharePoint List, Sign in to 365, and then choose my list via URL.

The second item is to now shape the data that was pulled in via the list.  By shape, I mean pick the columns to show, create columns off of columns, and change column data types.  Think of this as SQL Server Analysis Services for the common person.  When it is complete, you can publish this work as a Data Catalog Query.  This action takes your work and saves it to the cloud not just as a query in your workbook.

With this action, you can choose to share this with just yourself, the enterprise or specific groups and people as shown below.

Your query is now searchable and reusable.  The promise of self-service BI can be seen right here.  Think of what this used to take: a developer using SSIS to pull in the data, shaping the data, writing the connection strings and then finally making that available to you, the user.  Now, the user is doing all these roles in a tool they understand.

Finally, still using Excel, one creates the Power View report using just a ribbon button off the Insert tab.  Once the report is created, the Excel file is loaded into the document library and ready for viewing via Power BI.

The Bad

I will be the first to say "The Bad" is very subjective and probably not good vernacular.  These are really deficiencies which stop the product for being ready to prime time.  Will these be addressed in the future? Probably.  But right now, when evaluating the product for your organization, these three deficiencies may be show stoppers.

The first thing I tried to do was color code my visual objects.  For instance, I have a scale from 1 to 10.  1-3 being the highest risk is red, 8-10 being the lowest risk is green, 4-7 is yellow.  Seems simple enough, but when I create the charts, the colors are arbitrarily assigned as seen below.  That will not work as it is important in most use cases that colors indicate status as visual indicators.

The second item I needed to do was combine my chart types.  I have different types of data that relate and need to be compared.  It would look like something to the following:

Finally, the last must-have that I was unable to do was drill down to see specific records.  Using the "Recent Home Sales" example above, my customer wanted to click on February and the get a line chart, by date showing other metrics that make up the home sales, such as homes sales by owner, or homes sales by government program. 

I do want to add that there are at least a couple of ways to set up hierarchies in Power BI via Power Query but this did not meet my need. 

Final Observation (for now)

Power BI is an incredibly powerful and compelling tool that is useful right now.  It is fantastic for doing quick prototypes, empowering the user, and creating simple reports.  As I have shown, one can get up and running with it quickly.  And, I have not even touched on Power BI Designer!

In the interest of fairness, I know via the Power BI Support forums that formatting is being planned as I write this.  Microsoft plans to address this sometime later this summer so my deficiency about color assignment will most likely be addressed.  Also, I know (again, via the forum) that Microsoft is actively working on integrating SSRS reports into Power BI.

Mark Twain said, “The secret to getting ahead is getting started.”  Consider the factors I laid out, follow the support forum for answers (http://support.powerbi.com), and utilize this tool today.  You and your organization will be better for it.