Tuesday, March 22, 2016

Developing a Business Intelligence Solution for a Professional Services Organization using Microsoft Power BI

As a big fan of Power BI from Microsoft and Go-To-Market lead for Business Intelligence and Analytics at Rightpoint, our leadership provided me with a recent challenge:

“Can you replace our current reporting environment which consisted of a relatively fragmented set of solutions with an integrated enterprise-wide reporting and dashboard solution and if so how long would it take?”

The answer is a definite yes and surprisingly it was accomplished in a matter of weeks. The purpose of this blog post is document the process I used and highlight some of the resulting dashboards and insights.


Step 1 – Understand the Requirements

Like many technologists, I sometimes think I know what the solution requires before building it. However, experience definitely tells me that building any solution without fully understanding requirements is fraught with risk. I therefore conducted a number of meetings with practice, operational, finance and marketing leaders and project managers throughout Rightpoint.

In order to facilitate these meetings, I created a set of prototype dashboards (using data from current systems) using Power BI Desktop (download at https://powerbi.microsoft.com/en-us/) to elicit feedback. The following table summarizes what I learned in terms of key performance needs from each area of Rightpoint:

High-Level Requirements




Practice Management


Project Management

Marketing Campaign Effectiveness

Account Executive Performance

Revenue and Profitability Analysis


Pipeline Health

Project Budget vs. Actual

Web Site Performance

Pipeline Velocity

Receivables, Collections and Days Sales Outstanding

Bookings/Projected Utilization

Closed Revenue

Project Budget vs. Estimate-to-Complete

Social Media Performance

Average Deal Size

Billing/WIP Performance

Margin Performance

Delivered Revenue

Project Task Budget vs. Actual

Traffic Generation Impact

Win/Loss Ratio


Project Health

Margin Analysis

Project Health

Pipeline Generation

Revenue Generation

Recruiting Performance

Training Completion

Marketing Performance

Project WIP, Billings and Collections



Step 2 – Map the Requirements to Data Entities and Data Sources

The next step was to develop an entity model for our requirements. Data modeling is a science in and of itself so I will not attempt to teach that in this blog post. Suffice it to say, the following table distills the requirements into facts (what happened) and dimensions (how we want to view the data).





Pipeline Goals


Project Health


Social Media Post


Employee Days Available


External Search


Revenue Goals



Project Task Assignments




Booking Type

Web Sessions


Web Visitors


Web Page Views


The next step was to create relationships between the entities. Nearly all of the fact entities are related to the Date dimension as we almost always want to see data represented in terms of different time periods such month, quarter, year, etc. Another example is we want to see which campaigns generated our opportunities so we need to relate the Opportunity fact entity to the Campaign dimension entity.

The resulting entity relationship model is quite an eye chart so I am not able to easily represent it in this blog post. It is worth noting that Power BI can actually create these relationships automatically as you load data from your data sources. However, I also prefer to do this in advance of the data loading in order to make certain the I populate the necessary data from the sources.

The next step was to trace each data entity and associated data attribute to the data source. In our case, we used three primary data sources: Google Analytics (Rightpoint.com web site metrics), NetSuite OpenAir (our project time recording and billing system), and Salesforce.com (our sales management solution). In addition to these three primary data sources, I also needed to capture social media data from Facebook, Twitter, Slideshare and Vimeo. The illustration below highlights the mapping of data sources to data entities.

2016-03-21 08_13_15-Settings


Step 3 – Load Data

After mapping the data entities and attributes to data sources, the next step was to load the data to Power BI. Fortunately, Power BI provides a number (more than 35 as of this blog post) of out-of-the-box connectors to help accomplish this task. For this project, I took advantage of connectors for Salesforce.com, Google Analytics, Facebook and SQL Server (OpenAir data source). One of the features about Power BI I really appreciate is the advanced query tool (formerly known as Power Query). The query tool allows me to “shape” the data so that I can:

  • Ensure that attribu