KPI Cause and Effect Graph
A View from the UDM into Relationships Between Information Workers

Originally posted on on


With the release of the Business Scorecard Manager and the new Key Performance Indicator (KPI) feature of Analysis Services 2005 (SSAS), information workers will no doubt sharpen their focus on KPI as a measure of business performance. A single-minded focus on KPI may produce unintended consequences, however. Efforts by information workers in the same company to improve certain of their KPI may have adverse effects on their colleagues’ KPIs. What I offer to forestall such unintended consequences is my “KPI Cause and Effect Graph,” which will show how actions to improve one KPI have side-effects for other KPIs.

Key Performance Indicators

Key Performance Indicators are measures that help business managers and other information workers assess the effectiveness of the activities within their domains towards achieving one or more business objectives. (The most salient KPI in the business world is net profit, which is under the domain of the CEO.) These measures generally have been established by business analysts and managers who have architected a business model and have set measures that will be indicative of the health of that business model. KPIs generally state a target goal value. These goals are determined at some point, such as prior to the start of a fiscal year. It is believed that if every information worker meets the objectives, the business will be successful.

The number of KPIs in an organization can vary from a single KPI of net profit for an independent contractor to hundreds or thousands for a large enterprise. For example, hospitals may adopt the Institute of Medicine’s 700+ established KPIs spanning a wide range of functions (financial, clinical, process) recommended for hospitals to provide quality healthcare. KPIs can be high-level like net profit (a formula consisting of more than one measure), as subjective as “customer satisfaction”, or as elemental and empirical as “number of resolved cases”. In any case, KPIs are numbers, whether they be directly measured values or the result of a formula (that is comprised of measures).

KPIs in themselves don’t contain any knowledge about how they can be manipulated. It’s up to the information worker entrusted with the KPI to devise processes, plans, and actions that will move the value of a KPI in the favorable direction towards the goal value. However, blind attention to the value of KPIs alone or a “whatever gets the job done” attitude can be a recipe for long-term disaster. Why would an information worker take actions that can result in disaster? There are several:

1. The information worker isn’t aware of the side-effects of his action. If an enterprise is large and complex, the side-effects of creative actions of a manager or other information worker would be hard to track.
2. The information worker is well aware of the harm it’s causing another party, but hey, “It’s survival of the fittest, every man for himself.” In fact, it’s possible the information worker is even preying upon another information worker.
3. The information worker may be taking a mortgage out on his career, but it serves its purpose and he/she will deal with the consequences later. (This is akin to athletes taking steroids. The gain is immediate and short-term, but the benefits (lots of success and money) outweigh the potential disabilities that may or may not come in time, or will be treatable in the future.) I don’t think it’s hard to think of examples in the business world of short-sighted actions for short-term KPI improvements.

No Free Lunch

The business adages--“there is no free lunch,” “zero-sum game,” and “transference of cost” reflect their counterparts within the laws of physics-“conservation of mass and energy” and “for every action there is an equal, but opposite reaction.” This essentially means that there is always a “price” for our actions. The price isn’t always as obvious and direct as “an eye for an eye”. Keep in mind too that it doesn’t have to be “one man’s loss is another man’s gain”. Often the party paying the price is not even contemplated or known by the other. This is the basis for the value of a KPI Cause and Effect Graph.

“Transference of cost” is the result of actions intended to improve a KPI that blatantly shifts burden somewhere else. A good example of transference of cost is those annoying phone menus customers must navigate to receive support. The idea is to allow a company to support many more customers with fewer support personnel. However, the extra waiting time that a customer must endure to finally reach an answer is increased. But that’s OK: it’s the customer’s time that is wasted and not the company’s. If you think about it, you’ll notice many examples of transference of cost that companies hope you never notice. (Don’t think too hard about it … it’ll get you angry.)

The Unified Dimensional Model (UDM) - One Version of the Truth

One of the foundational principles of Analysis Services 2005’s UDM is “one version of the truth.” This means that properly designed, it is the blueprint, the DNA, of data for a business enterprise. Examples of its functionality are the UDM maintains the location and other information of all data sources throughout an enterprise and how they relate to each other and it provides features that store resolved conflicts of terminology throughout an enterprise (you say “price” and I say “cost”).

KPIs are the highest level of measures defined by the UDM. They consist of a formula, which in turn consists of measures, and they have additional properties providing the semantics for a device intended to let information workers know how they are doing. The relationships between the following four UDM objects are the links of the KPI Cause and Effect Graph:


Measures in SSAS terms are the raw numbers from fact tables. Aggregating them in some manner, such as summing them, finding the largest or smallest value, or the average value is the product of interest from data warehouses for information workers.

Calculated Measures

Calculated measures are formulas based on measures and other calculated measures. Common types of formulas are ratios or comparisons such as from one period to another or one store versus the average of all stores.


KPIs are based on measures and calculated measures. Of the most interest for this Graph is a formula composed of measures and calculated measures that conveys the status of the KPI. The status defines what it means for the KPI to be good, bad, or OK. Therefore, a KPI has a one to many relationship with calculated measures and measures.


Perspectives are views of a UDM. They are intended to provide a view to an information worker of a particular role consisting of only things that are of interest to them. Otherwise, a UDM of a large enterprise would expose hundreds or thousands of extraneous dimensions, measures, and KPIs that the information worker would need to wade through. Perspectives have a one to many relationship with KPIs. For this Graph to be the most effective, there should be one perspective to each information worker role.

KPI Cause and Effect Graph

If the SSAS objects described above are designed with those guidelines in mind, the KPI Cause and Effect Graph can be mined from the UDM and used to help illustrate who is paying for your lunch. Figure 1 depicts a simple UDM consisting of five measures, three calculated members each based on one or more measures, five KPIs each based on one or more measures and/or calculated measures, and four perspectives used by information workers of four different roles.

Four information workers (IW1 through IW4) view the objects of a UDM filtered through four perspectives (P1 through P4). Each perspective exposes at least one KPI that is of interest to the respective information worker. Each of those KPIs is a formula based on calculated measures and measures. Some of those measures of calculated measures are used by more than one KPI. Because of this, if one of the information workers intends to improve an assigned KPI by taking actions to move a measure, that information worker could inadvertently affect the KPI of another information worker; sometimes for the worse.

The Graph can be generated by mapping relationships in a UDM using AMO (Analysis Management Objects) as follows:

• A relationship between a KPI and a perspective for each KPI in a perspective.
• A relationship between a calculated measure or measure and a KPI by parsing the status formula for each KPI.
• A relationship between a calculated measure and another calculated measure or measure for each formula of a calculated measure.

Figure 1 – KPI Cause and Effect Graph

As an example of how this Graph works, suppose:

1. IW1 is the sales manager. IW1 is charged with raising total sales from the store and Internet sales channels.
2. IW2 is the billing manager in charge of billing sales from the stores. IW2 is charged with ensuring customers are promptly billed for the goods they purchased through the stores.
3. IW3 are billing clerks charged with billing the customers for store and Internet sales.
4. K1 is a KPI measuring the total sales. This is IW1’s main KPI.
5. M1 is the total store sales. M1’s value is $1000.
6. M2 is the total Internet sales. M2’s value is $500.
7. M3 is the store sales that are billed. M3’s value is $800.
8. M5 is the Internet sales that are billed. M5’s value is $450.
9. CM1 is the total sales (sum of M1 and M2), which is $1500.
10. K2 is a KPI measuring the ratio of billed store sales (M3/M1), which is .80.
11. CM2 is the ratio of store sales to billed sales.

The sales manager (IW1) is concerned that slower than expected Internet sales (M2) is dragging down her main KPI, total sales (K1). In order to improve things, the sales manager dreams up a campaign to spur store sales. It does indeed work. Store sales (M1) rise from $1000 to $2000. The sales manager is happy. However, the increased store sales lowers the billing manager’s (IW2) KPI, percent of sales billed to the customers (K2), the percentage of the amount of store sales billed. K2 is now .40 ($800/$2000). The actions of the sales manager directly and adversely affected the interests of billing manager.

The billing manager has three choices: Make the sales manager stop generating so many sales, hire more billing clerks (IW4), or get the billing clerks to work harder. The billing manager knows that asking the sales manager to lower sales is out of the question. Hiring more billing clerks is also nixed due to budget constraints. So the billing manager cracks the whip on the billing clerks. By working 18 hour shifts, the billing clerks manage to dramatically increase the amount of sales billed to the customer to $1500. Now, billing manager’s KPI is up to .75 ($1500/$2000); not quite back to where it was at, but satisfactory. We can say that the actions of sales manager indirectly affected the billing clerks. That’s because the billing manager could have taken other actions that wouldn’t have affected the billing clerks.

Using this graph, the sales manager would realize that her actions would adversely impact the billing manager. Ideally, they would meet to collaborate on a plan that will result in minimal side-effects. The billing manager would probably realize that the plans they develop will probably impact other information workers as well.

Sample Code

The attached Zip file contains sample C# (.NET Framework 2.0) code that I wrote. It opens a UDM using AMO and generates a rudimentary KPI Cause and Effect Graph. This code is solely for demonstration purposes. I may build upon it at some time and will post an updated version if I do so.

Figure 2 shows a sample run of the application on a test cube that mirrors the example above.

Figure 2 – Window of the KPIRelationships sample application.

Figure 2 shows that there are two perspectives, Sales Manager and Store Sales Billing Manager. Drilling down, we see the following:

1. The sales manager is assigned one KPI: Total Sales.
2. The Total Sales KPI is composed of the Total Sales calculated measure.
3. The Total Sales calculated measure is composed of two measures representing two sales channels: Store Sales and Internet Sales.
4. The Store Sales measure is used by two calculated measures: TotalSales and PercentStoreSalesBilled.
5. The PercentStoreSalesBilled calculated measure is what the Percent Store Sales Billed KPI is based upon.
6. The Percent Store Sales Billed KPI is assigned to the Store Sales Billing Manager who views it through the perspective of the same name.

What we see here (and is highlighted by the “salmon” background) is that two KPIs each of interest to two information workers of very different types share a common measure; Store Sales. Therefore, if either information worker executes actions directed at changing this measure, the KPI of the other will be directly affected.

Here are a few ideas for improvement:

• Use Visio to render the graph. The intent is for the graph to look more like Figure 1 than Figure 2.
• Add more logic to determine whether changes to a measure by one information worker is beneficial or detrimental to the KPIs of another information worker. The status MDX expression of a KPI allows for logic (with CASE and IIF) constructs that conditionally determines what “better” and “worse” means for a KPI.

Lastly, there are a few quirks with the sample application. I’ll fix them someday …

1. Be sure the name of your calculated member is enclosed in square brackets: [Measures].[CalculatedMember] AS [Measures].[M1]


Related Articles: