I recently read an article asserting, that perhaps, just possibly, the customer is not always right. In fact, the less profitable the customer is, the less right they are. OK, so it didn't say it quite that way, but the basic idea was companies should consider customer profitability when setting their customer service goals. Higher levels of customer services should be provided to the more profitable customers, and unprofitable customers (with little hope of future profitability) should be "fired". As with all good management articles, there was a clever little hook to make the technique memorable. This one happened to use a metallurgical metaphor (or is it an analogy?). Your most profitable customers are your Platinum customers. These are your tier 1 customers, interested in advanced service levels, extra support, or other highly profitable value-added services. Your Gold customers are also quite profitable, but not to the extent your Platinum customers are. Next is your Iron customers. They are profitable, but not overly so. If you spend TOO much time and money, they may become…..a lead customer. These are your unprofitable customers; those that demand unreasonable amounts of customer service, continually ask for freebees, bounce checks, and generally make your CFO sad. In addition, Lead customers don't have season tickets to the Padres, luxury boxes, or country club memberships.
I know what you're thinking…"this sounds fantastic, but how can I track customer-focused metrics by profitability segment, and present this information in an executive dashboard using PerformancePoint and the Microsoft BI stack?". I'm glad you asked.
Essentially, we need a customer-focused scorecard with different actual and target values for a given metric in each customer segment. For example, it will not suffice to provide a "Support Calls Per Month" metric in the customer scorecard. We need a Support Calls Per month for Platinum customer, a Support Calls Per Month for Gold Customers, and so on... because we are striving to provide different levels of support based on customer tier.
First of all, as all good armchair-economists do, let me preface this with some assumptions. Lets assume we have a data warehouse…Lets also assume that we do not have the time or resources to provide data feedback into a CRM. If this was the case, it would probably be preferable to calculate per customer profitability in the financial system, implement business logic to categorize profitability ranges into customer profitability tiers, and send this information back to the CRM. Not only would this information then be available in almost real time to support and sales staff, these tiers would be available as members of a Customer Tier dimension... A subject for another blog.
In this case I'm going to use SQL Server Analysis Services to create calculated members in the customer dimension in order to create customer profitability tiers. This calculated member will compute the average of any applicable measure (in this case I will use support calls per month as an example). In PerformancePoint, we then create a Key Performance Indicator using the desired measure (support calls per month) as the value for the metric, and filter the customer dimension for the appropriate customer tier. We drag and drop it into a dashboard, pretty it up, and deploy to our SharePoint 2007 site. The boss is impressed with how quickly this was done and raises his expectations for future projects.
In my toy problem here, I already have a SQL Server Analysis Service cube consisting of sales facts (FactInternetSales) and a customer dimension. Also, information from the support system has already been cleansed, transformed, and loaded into this data warehouse, where it lives quite happily in another fact table in the same cube (FactSupport_Custom).
In SQL Server Business Intelligence Development Studio, I open that cube, go to the calculations tab and create a calculated member for each customer tier:
Note that the parent hierarchy is Customer.Customer, with the parent member [All Customers]. This allows me to later filter the cube by this new "member" in the customer dimension.
The expression for the Platinum customer computes the average of ANY MEASURE included in the query for just those customers with [Internet Gross Profit Margin]>50%:
AVG(FILTER([Customer].[Customer].[All Customers].Children,[Measures].[Internet Gross Profit Margin]>.50))
This is then available as a member in your favorite multi-dimensional analysis tool. The below screenshot shows average profitability for each calculated "member" for 2003. (Note that I've already filtered out the non-calculated customer members)
If I drag my Support Calls Per Month measure in I get:
This tells us that in 2003 the average support calls per month for our Platinum customers is 5.638, 5.529 for our Gold customers, and so on.
Surfacing the information in PerformancePoint
At this point, if you are a back office kind of person, your job is done. Tell the Business Analysts that they can filter the customer dimension by customer tier to create Tier-specific KPIs and go to lunch.
OR….
From PerformancePoint Dashboard Designer, create a new KPI, click on the data mappings for the "Actual" Metric:
Click on Change Source, and select the data source for your cube
Select the desired measure (remember, in this case we will be getting the AVERAGE of any measure we select)
In the Select a Dimension part of the dialog, click on the filter and select the Customer.Customer hierarchy (this is the parent hierarchy that we selected when created our calculate member in a previous step).
In the default column, select the default link and filter by your desired profitability segment:
Repeat for each KPI, set your targets FOR EACH TIER then drag into a scorecard, add the scorecard to a dashboard, and deploy.
Of course, this may not be the ideal solution in many cases. Using Calculated Members in a large cube may have performance implications, and you may not get all the analytics you want out of it for reasons I won't go into here. The point, however, is that scorecards based on customer segments (in this case profitability segments), can be implemented relatively easily, provided your organization has some level of BI maturity.
Go Chargers.
Recently a client of mine had a need to display some financial ratios in a PerformancePoint dashboard. These were relatively standard ratios, yet the source data from which these ratios were calculated (a SQL Server Analysis Cube) did not pre-calculate these ratios for us. While it was decided ultimately these calculations should be done at the cube level, in the interest of time, we realized that the PerformancePoint KPIs would have to do the calculations for us, at least in the short term.
Unfortunately there isn't a very business-user focused method of doing calculations within a PerformancePoint KPI. You can't simply select your Current Assets measure and divide by your Current Liabilities measure. Instead you must use an MDX tuple formula. I really hope that eventually some sort of expression builder is available for these situations, but in the mean time, here are the steps to create a ratio based on balance sheet data in the Adventure Works DW database.
Create an Adventure Works data source, and point it to the Finance cube in the Adventure Works DW Analysis Services database. (It's actually just a Finance perspective but that's another blog)
![clip_image001[7]](http://blogs.interknowlogy.com/downloads/scottlarson/ComputingFinancialRatiosInPerformancePoi_E43A/clip_image0017.png)
Create a new KPI, and edit the data mappings for the Actual "metric" by clicking on the link
Click "Change Source" and select the Adventure Works source you just created
![clip_image001[9]](http://blogs.interknowlogy.com/downloads/scottlarson/ComputingFinancialRatiosInPerformancePoi_E43A/clip_image0019_3.png)
Click on the "Use MDX tuple formula" and type in the appropriate MDX formula . (you DO know MDX, right?)
![clip_image001[11]](http://blogs.interknowlogy.com/downloads/scottlarson/ComputingFinancialRatiosInPerformancePoi_E43A/clip_image00111.png)
Note: For demonstration purposes the above MDX statement reference members by name, not key. In a real implementation it would be wise to reference them by ID. By the way, for those that care, this is my favorite MDX resource: http://www.databasejournal.com/features/mssql/article.php/1495511.
Add this and any other ratios to a scorecard, add the scorecard to a dashboard, and deploy the dashboard.
I'm beginning to suspect that the use of PerformancePoint, at least in it's early releases, will require at least functional knowledge of MDX and PerformancePoint Expression Language (PEL) for PerformancePoint Planning, neither of which are exactly business-user friendly. That being said, many of these types of calculations will be similar, so it's not unrealistic for a very data-savvy business user to be able to handle some of these expressions without TOO much I.T. assistance.