Building the Ultimate Microsoft CRM Dashboard in Under an Hour

Ten Simple Steps to Ultimate CRM Dashboard Nirvana

Introduction

So, I have a pet peeve about reporting capabilities in most, if not all, CRM systems. They’re awful. Trying to build out a meaningful and appealing dashboard in CRM systems is like trying to come up with a witty analogy before having your first cup of coffee in the morning…which is what happened here.

I also have a pet peeve with most blogs in that most, not all, tend to be short and cover a very specific topic without really looking at or considering the big picture. Take every blog article I have ever seen about connecting to Microsoft CRM Online’s OData service from Power Query. Useful to a degree but completely fails to cover why you are connecting to CRM Online’s OData service from Power Query. Likely, because you are trying to build a visually stunning dashboard that brings true insights to your organization. Stopping at just showing that it is possible to connect to the OData feed leaves out crucial information that is needed in order to actually realize your CRM dashboard. Enough whining, this article fixes all of that and will take you step-by-step through building a visually stunning CRM dashboard in probably less than an hour with no coding outside of Excel DAX formulas which I have included. Literally, anybody of fair to middling intelligence and technical skills can pull this off.

My original goal in building my CRM dashboard was to build an awesome dashboard that delivered real business value and could be hosted in Microsoft’s Power BI with regularly scheduled updates. Couple of issues with that last part that I’ll cover later but by and large, this document will get you to that goal.

What You Will Need

· Excel 2010 or 2013 with PowerPivot and Power Query add-ins (I STRONGLY recommend the 64-bit version of Excel)

· Microsoft CRM Online or on-premises instance

Here We Go

1. First, go get your CRM Online’s OData Service link. You will find it under Settings | Customizations | Developer Resources and is the last one on the page. It’s heading is “Organization Data Service” and underneath that you will see “Protocol: OData (REST)”. The link will be in the format “https://[your org].crm.dynamics.com/XRMServices/2011/OrganizationDataSvc/. For some reason, right-clicking does not seem to work so the easiest thing to do is to click on the link and then copy the link from your browser.

2. Open up Excel and head straight to the Power Query tab. In the “Get External Data” portion of the ribbon, click on “From Other Sources”. If you have the latest version of Power Query, you will see an option for “From Dynamics CRM Online”. If you don’t see it, you can use the “From OData Feed” option. Paste in the link that you copied in step 1 and click OK. At this point, you may have to authenticate using your organizational credentials. In the end, you should see something like this on your right-hand side of Excel

clip_image001

3. The first thing you will likely want are your Opportunities. Scroll down until you see OpportunitySet. No, not OpportunityCloseSet, three down from that. Click on it to highlight it, click the Edit button and you should end up in the Query Editor window as shown below.

clip_image003

4. Now, see those weird little arrow things next to some of the column titles? Turns out those are actually important. Click on them to expand the columns that you want because believe it or not, [Record] and [Table] aren’t terribly useful when it comes to analyzing your sales information. Feel free to expand and grab everything but that may take you past your hour. Here’s a short list of the things that I think are important. Note that anywhere that you see a “.” means that an expansion took place. The portion before the “.” is the column that was expanded and the portion after the “.” is the expansion field that was selected.

· Description

· ActualValue.Value

· OwnerId.Name

· ActualCloseDate

· StepName

· EstimatedCloseDate

· CreatedOn

· PurchaseTimeframe.Value

· CustomerId.Name

· ProposedSolution

· CustomerNeed

· FinalDecisionDate

· EstimatedValue.Value

· StateCode.Value

· Name

· CloseProbability

5. Now, rename your query to “Opportunities” because this is going to look nicer to your end users and then expand the button all the way on the left “Close & Load” and choose “Close & Load To…”. In the Load To window shown below, choose “Only Create Connection” and check “Add this data to the Data Model”. Click the Load button.

clip_image004

6. Rinse and repeat steps 3-5 for LeadSet and SystemUserSet. I will caution you on expanding and loading everything for LeadSet. I ran into some problems where when doing that it would conk out in the middle of things because of a problem with a particular record. I believe it had something to do with a reference to an opportunity or other object that no longer existed, no real idea other than that it would give me a 404 right in the middle of the data load. Yes, I sent frowny faces to Microsoft. So, best to load only what you really need. Here is my short list for LeadSet

· StatusCode.Value

· BudgetAmount.Value

· CreatedOn

· Subject

· CompanyName

· FullName

· PurchaseTimeframe.Value

· OwnerId.Name

· Description

And for SystemUserSet

· FullName

Before loading, rename LeadSet to “Leads” and SystemUserSet to SalesPeople. Also, you will likely want to filter your SystemUserSet to just your sales folks and the only column you really need is the FullName column. Scroll over, click on the heading to highlight the column and then expand the Remove Columns button and choose Remove Other Columns. Another way of getting these is to create another query to either LeadSet or OpportunitySet, expand OwnerId to OwnerId.Name. Remove all other columns and choose to Remove Duplicates.

In the end, you should have tables for Opportunities, Leads and SalesPeople in your data model.

7. OK, so far so good but you need more data. Remember what I said in the Introduction about some of that crucial information that you need? Of course you don’t, you skipped right past the Introduction. Well, no matter, what you are still missing are the human readable values for some of the fields in your CRM OData feed. CRM has field codes for certain things like Opportunity State. Opportunity State comes through as a 0, 1 or 2 which is pretty useless to humans. So, you need some sort of mapping as to what those codes stand for. When I originally did this exercise, I manually looked up online the enumerations for these codes and created SharePoint Online custom lists to do the mappings which I then brought into my data model via Power Query. Painful. So, I decided to create an Azure Data Marketplace project that contained all of the enumerations for just about anything you could want. The easiest way to get and grab this data is to head out to the Azure Data Marketplace: https://datamarket.azure.com/home. Sign in with an organizational or Microsoft account. Search for “CRM Enumeration” and you should find my project. Click on the link and SIGN UP.

While you are in the Azure Data Marketplace, you might also subscribe to Boyan Penev’s DateStream feed. This is essentially a table of dates that is useful for time intelligence type stuff. Won’t help you in Power View since it doesn’t seem to support time intelligence features but if you want to play around with time intelligence you can use standard Pivot Tables and Pivot Charts.

Back to Excel. Now that you have subscribed to the Azure data feed(s), go to the Power Query tab, expand the From Azure button and choose From Microsoft Azure Marketplace. You should see CRM Enumerations listed in your Navigator pane. Expand it and grab the data sets that you need. I would recommend:

· OpportunityState

· LeadState

· PurchaseTimeframe

· MonthOfYear

You may still need some SharePoint lists. For example, in my organization we have a custom option set called “Service Offering”. It comes through in the CRM OData feed as an unintelligible code. The SharePoint custom list I have includes two columns, a “Service Offering” column that contains the human readable service offering titles and a “Code” column that contains the CRM code for the service offering. You can find this information by going to Settings | Customizations | Customize the System, finding the custom option set and looking at the properties for each option. This trick can also come in handy if you don’t want to mess around with the DateStream feed and just want a simple list of Years or Quarters that you can wire to multiple tables like Opportunities and Leads. You can then create a slicer on your Years, for example, and both Opportunities and Leads will be filtered by your slicer. That is, if you are displaying both Opportunities and Leads on the same Power View dashboard.

8. The next thing you will need to do are some custom calculations. To create custom calculated fields, click on the PowerPivot tab in Excel and click the Manage button. Pick a tab and scroll all the way to the right. I’ve listed out the custom calculations from my own data model for Opportunities and Leads below. Use them or create your own. Egads! It’s a table! The horror. Sorry, I’ve worked with far too many digital folks in my career apparently.

Leads

Name

Explanation

Formula

Created On

Puts the created on date/time into a standard format for use with a time intelligence table

=DATE(YEAR([CreatedOn]),MONTH([CreatedOn]),DAY([CreatedOn]))

Created Year

For wiring to a Year table

=YEAR([Created On])

Created Month

For wiring to a Month table

=FORMAT([Created On],"mmmm")

Created Month Sort

Sort field for Created Month

=MONTH([Created On])

Lead Count

Useful for displaying a friendly name to users when displaying lead aggregates

=1

Budget

Gets rid of blanks in BudgetAmount.Value

=IF(ISBLANK([BudgetAmount.Value]),0,[BudgetAmount.Value])

Opportunities

Name

Explanation

Formula

Created On

Puts the created on date/time into a standard format for use with a time intelligence table

=DATE(YEAR([CreatedOn]),MONTH([CreatedOn]),DAY([CreatedOn]))

Created Year

For wiring to a Year table

=YEAR([Created On])

Created Month

For wiring to a Month table

=FORMAT([Created On],"mmmm")

Created Month Sort

Sort field for Created Month

=MONTH([Created On])

Created Quarter

For wiring up to a Quarters table

=IF([Created Month Sort]<4,"Q1",IF([Created Month Sort]<7,"Q2",IF([Created Month Sort]<10,"Q3","Q4")))

Est. Close Date

Normalizes EstimatedCloseDate in case there are blanks and standardizes time value for use with a time intelligence table. Note that if it is blank, pushes the estimated close date a year from today and places it on December 31st

=IF(ISBLANK([EstimatedCloseDate]),DATE(YEAR(TODAY())+1,12,31),DATE(YEAR([EstimatedCloseDate]),MONTH([EstimatedCloseDate]),DAY([EstimatedCloseDate])))

Est. Close Year

For wiring to a Year table. Note that if EstimatedCloseDate is blank, pushes the estimated close date a year from today

=IF(ISBLANK([Est. Close Date]),YEAR(TODAY())+1,YEAR([Est. Close Date]))

Est. Close Month

For wiring to a Month table. Note that if EstimatedCloseDate is blank makes the Est. Close Month December

=IF(ISBLANK([Est. Close Date]),"December",FORMAT([Est. Close Date],"mmmm"))

Est. Close Month Sort

Sort field for Est. Close Month. Makes it 12 (December) if EstimatedCloseDate is blank

=IF(ISBLANK([Est. Close Date]),12,MONTH([Est. Close Date]))

Est. Close Quarter

For wiring up to a Quarters table, yep makes it Q4 if it is blank.

=IF([Est. Close Month Sort]<4,"Q1",IF([Est. Close Month Sort]<7,"Q2",IF([Est. Close Month Sort]<10,"Q3","Q4")))

Actual Close Date

Normalizes ActualCloseDate for use with time intelligence date table, does not default blanks

=DATE(YEAR([ActualCloseDate]),MONTH([ActualCloseDate]),DAY([ActualCloseDate]))

Actual Close Year

For wiring to a Year table

=YEAR([Actual Close Date])

Actual Close Month

For wiring to a Month table

=IF(ISBLANK([Actual Close Date]),"",FORMAT([Actual Close Date],"mmmm"))

Actual Close Month Sort

Sort field for Actual Close Month

=MONTH([Actual Close Date])

Actual Close Quarter

For wiring up to a Quarters table

=IF(ISBLANK([Actual Close Date]),"",IF([Actual Close Month Sort]<4,"Q1",IF([Actual Close Month Sort]<7,"Q2",IF([Actual Close Month Sort]<10,"Q3","Q4"))))

Days Open

If the opportunity is not closed, then the number of days it has been open is TODAY minus the created on date, otherwise it is the Actual Close Date minus the Created On date

=IF(ISBLANK([Actual Close Date]),ROUNDUP(1.*TODAY()-[Created On],0),ROUNDUP(1.*[Actual Close Date]-[Created On],0))

Days to Close

Same as above. Why? Makes more sense to the users to use one or the other in difference contexts in the dashboards. Reads better.

=IF(ISBLANK([Actual Close Date]),ROUNDUP(1.*TODAY()-[Created On],0),ROUNDUP(1.*[Actual Close Date]-[Created On],0))

Est. Close Days Variance

Ever wonder how accurate your sales people are at predicting when deals will close? This will help with that.

=IF(ISBLANK([Actual Close Date]),ROUNDUP(1.*TODAY()-[Est. Close Date],0),ROUNDUP(1.*[Actual Close Date]-[Est. Close Date],0))

% Probability

Format this as a percentage and change the default aggregation to average

=[CloseProbability]/100

Opp Count

Useful for displaying a friendly name to users when displaying aggregates

=1

Wins

Useful for displaying a friendly name to users when displaying aggregates

=IF([StateCode.Value]=1,1,0)

Losses

Useful for displaying a friendly name to users when displaying aggregates

=IF([StateCode.Value]=2,1,0)

Open

Useful for displaying a friendly name to users when displaying aggregates

=IF([StateCode.Value]=0,1,0)

% Win

Format as a percentage and change the default aggregation to average. Comparing this to % Probability is a great way to determine just how overly optimistic your sales people are.

=IF([StateCode.Value]=1,1,0)

Est. Value

Normalizes EstimatedValue.Value. Format to currency

=IF(ISBLANK([EstimatedValue.Value]),0,[EstimatedValue.Value])

Act. Value

Normalizes ActualValue.Value. Format to currency

=IF(ISBLANK([ActualValue.Value]),0,[ActualValue.Value])

Pipeline Value

Basically what this does is depending on the sales pipeline stage assigns a % value to the Est. Value of an opportunity. Stage 1 is 20%, stage 2 is 40%, stage 3 is 60%, stage 4 is 80% and if it is a closed deal, 100%. Good way to judge how mature a sales pipeline is versus just a raw sum value. Format to currency.

=IF([Wins],[Act. Value],IF(FIND("1",[StepName],1,0),[Est. Value]*.2,IF(FIND("2",[StepName],1,0),[Est. Value]*.4,IF(FIND("3",[StepName],1,0),[Est. Value]*.6,[Est. Value]*.8))))

Pipeline Quarter

For wiring to a Quarters table, places the deal in the right quarter depending on if it is closed or open

=IF([Wins],[Actual Close Quarter],[Est. Close Quarter])

Pipeline Year

For wiring to a Year table, places the deal in the right year depending on if it is closed or open

=IF([Wins],[Actual Close Year],[Est. Close Year])

Once you have all of the calculations, you will probably want to go through and format certain columns. In addition, I would recommend hiding certain columns from end users. For example, hide CreatedOn but leave “Created On”, things like that. No sense confusing end users. To hide a column, right click the column header and select “Hide from Client Tools”.

9. OK, we have all of the data, we have all of the calculations, time to wire it up. Since you are already in PowerPivot, switch to diagram view. It’s the little button that says “Diagram View”. Tricky. It is on the right of the ribbon if you are on the Home tab of the ribbon. It looks like this:

clip_image006

Wire up your data. You might end up with something like below.

clip_image008

Here the relationships in a…wait for it…table!

clip_image009

Yours will be different, just remember that if you are using OpportunityState from the CRM Enumerations feed, wire up the Value column from that feed to StateCode.Value. The easiest way to form the relationship is to simply drag one column to another. If you find that annoying, click the Manage Relationships button from the Design tab and you can enter them into a table as show above. I find it annoying when people apostrophe yours.

You may notice that I have more than one Year table. Why? Well, because I need a slicer wired up to different columns of the same table for different purposes on different dashboards. So an easy solution is to just create a copy of my Year Power Query and load the same information to another table. Don’t judge me.

If you intend to use Power BI’s Q&A feature you might also want to create some synonyms for better natural language querying. From the Advanced tab and then click the Synonyms button all the way on the right of the ribbon. A pane will show up on the right-hand side. Click a table in the diagram view and then in the Synonyms pane, click a column and start typing comma separated synonyms.

10. Finally, time for pretty pictures. Close the PowerPivot window and in Excel go the Insert tab and click on the Power View button. Once this loads, you can create something like below in no time.

clip_image011

The big red boxes are redacted information. Here it is with some notes:

image

In Conclusion

Once you have your data model built, building out dashboards is super easy. As you can see above, I have built out multiple dashboards in order to focus on different information or different ways of visualizing the same information. Once you have your dashboards built, load your Excel file into Power BI. Now everyone can get access to it from a web browser. If you don’t want everyone to have access to it, find the document in your SharePoint Online document library and edit who has access to it. From your Power BI splash page click on the ellipses for your dashboard and make it a Featured Report and add it to Q&A.

So, a note about refreshes. As crazy as this might seem…as of this writing Microsoft Power BI does NOT support Power Query queries that use Azure cloud credentials. I would like to rant but I will refrain. I’m sure it’s coming but it isn’t there now. However, because this dashboard was built using all cloud resources (CRM Online, SharePoint Online and the Azure Data Marketplace), anyone with access can edit the dashboard in Excel and refresh the data. So not the end of the world but this is the end of this article.

The Buckeye Ranch

If you find this article and/or the mentioned data feed valuable, please consider making a donation to The Buckeye Ranch (http://www.buckeyeranch.org/make-a-donation.html). The Buckeye Ranch is an incredible organization that does fantastic work with youths in Ohio.

Last edited Apr 4, 2015 at 4:44 PM by smoupre, version 3