KPI Cause and Effect Visio Graph

A couple of weeks ago I posted a blog on an idea I have for a graph that can potentially be mined out of an Analysis Services 2005 Unified Dimensional Model (UDM). This graph consists of relationships between perspectives, KPIs, calculated measures, measures, and member properties used in calculated members. With this graph, information workers can determine the effects of their efforts to improve one or more of the KPIs for which they are entrusted. Please refer to that blog for background on this graph.


This blog requires familiarity with the UDM and a certain level of experience with SQL Server 2005 (for example, an expectation that I don’t need to explain how to attach a database or open an MDX query window).

I say "can potentially be mined" in the above paragraph because the design of the UDM would require some special consideration. Ideally, there would be pretty much one perspective to each information worker role and the more elemental the measures, the more accurate the relationships between KPIs.

This focus of this blog is the Visio graph I developed to further help convey the idea of the KPI Cause and Effect Graph. Figure 1 depicts a very simple graph. The rectangles represent perspectives, the ovals represent KPIs, the triangles represent calculated measures, the hexagons represent measures, and the heptagons (factor is the only one) represent a member property that was utilized by a calculated member. The fundamental concept is illustrated in this picture: What is of common concern to two information workers? In this case, it means what measures are common to the KPIs of the two information workers, Store Sales Billing Manager and Sales Manager.


Figure 1 shows a sample of the Visio graph illustrating the Sales Manager and Store Sales Billing Manager are concerned with KPIs (Total Sales and Percent Store Sales Billed, respectively) that share a common measure: Store Sales (the hexagon highlighted in cyan). Such a common interest presents a potential for a conflict of interests. If the Sales Manager needed to drastically increase sales for some reason, she would know that the Store Sales Billing Manager would be affected because they are concerned with KPIs that are formulated with a common measure.


The situation where I see this graph as having its greatest value is during times where an information worker must play catch up, which happens to every information worker. Many information workers have goals to meet at various levels of granularity such as monthly, weekly or daily. I many cases, the annual goal is where the information worker is bonused and the goals at the finer granularity are meant to track progress throughout the year. However, goals at a finer granularity are rarely met in a smooth manner. We'll unavoidably have good days and bad days or encounter situations out of our control that impede our progress (ex: tending to a sick child). If towards the end of the year we find that we’re behind on our goal and decide to ramp up production to meet the goal, it could adversely impact other information workers and have cascading consequences that this graph would help us to identify.

Figure 1 – Visio 2003 representation of a KPI Cause and Effect Graph

At this point, this Visio graph doesn’t implement all of the major ideas that I have in mind. There is a long way to go. I chose to release this package now because I think there’s enough here to get the idea across. It’s not my intent to write a commercial software product, just a demo of my thoughts around this subject. And with my extremely packed schedule (BI is really in demand right now), it would be months before I could release a version of this sample code that fully implements all of my ideas and is optimally written.


There are two main categories of features yet to be implemented: Adding objects and relationships that probably don’t exist in the UDM and enriching the properties of the relationships themselves.


Regarding adding objects and relationships, the cost of many actions aren’t recorded anywhere. That may be because it is known but immeasurable (ex: good will or excessive hardship on the employees) for some reason. However, uncaptured costs have real impact in the real world. Ignoring such costs can be an irresponsible and short-sighted way to run a company. Therefore, this Visio graph should have the ability to append such things to the set of objects and relationships pulled from a UDM. This goes back to the notion of running a business being a zero-sum game that I mentioned in the original blog. The idea of supplementing this graph with objects outside the UDM is akin to how Data Source Views (DSV) in the UDM provides features that capture enterprise-wide business knowledge (ex: friendly names, named calculation, and relationships between tables from separate data sources).


Another category of objects that aren’t captured in the UDM are the choice of actions an information worker can take to improve a KPI for which he/she is responsible. The action that an information worker chooses to take determines what measures are ultimately affected, and thus, what other information workers are ultimately affected.


Regarding the properties of the relationships, the characteristics of relationships can sometimes be more interesting and relevant than the objects themselves. This is a big subject, beyond what I’d like to get into here. But it’s easy to see that in our daily lives two objects can remain the same, but circumstances make the relationship different. Actually, I’ll discuss this quite a bit in an SCL article I’m close to completing.


See the section, Planned Improvements, at the end of this blog for elaboration on some of those topics.


The purpose of this software is to demonstrate the KPI Cause and Effect Graph idea, enough for a proof of concept. Although I do intend to continue developing this code, this code should not to be considered for production purposes as is.


For simplicity, please install this demo software on your personal development machine, for which you have administrator rights.


The sample software I developed consists of four pieces:


1.    A managed DLL that reads an SSAS cube and creates a collection of relationships between perspectives, KPIs, calculated measures, measures, and member properties used in calculated measures.

2.    A Windows form application that will display these relationships in a TreeView.

3.      A Visio document with VBA macros that graphically display these relationships.

4.      A sample database and an XMLA script that generates an SSAS database upon which the examples are based.

To fully utilize this sample, you will need Visio 2003, Analysis Services 2005 and Visual Studio 2005. However, there are two sets of exercises. Demo 1 only requires Visio 2003 and the Visual Studio 2005 for the managed DLL, KPIRelationshipsLibrary.dll. The second set requires access to a processed cube, therefore, Analysis Services 2005 is required. However, although you may have access to a fully processed cube, it may not have KPIs, perspectives and calculated measures defined in a way that fully utilizes this idea. So, I’ve included the data objects that I used for these demos: the SQL Server 2005 database and an XMLA script to generate the Analysis Services 2005 database.

Security note: Because of the macro in the Visio document, Visio’s security level must be set to medium. This setting will prompt you to decide on enabling or disabling the macro. To do this, go into Visio, select Tools/Options, select the security tab, click the “Macro Security” button, select “Medium” security.

To install the Visio document, managed DLL, and sample code:

1.      Unzip the attachment below to C:\. Be sure to have "Use Folder Names" selected.

2.      Compile (Build) the managed DLL.

3.      Open KPIRelationships.sln using Visual Studio 2005. 

4.      Build the project.

5.      Add the reference to the managed DLL to the Visio document.

6.      Open the Visio Document.

7.      Select Tools/Macros/Visual Basic Editor

8.      Select Tools/References

9.      Locate KPIRelationshipsLibarary and check it.

10.  Click OK.

11.  To install the sample databases for use with Demo 2:

I have run this program on ten or so cubes with success. However, there’s no telling what forms of calculated measures may break my simple parsing algorithm. Therefore, I suggest using the included  small sample database and SSAS project for the exercises. 

Known Issues

Following are known issues that are fixable and can be worked around, but I don’t have the time to fix them right now. Many of the issues involve upgrading the formula parser (KPINetwork.GetFormulaParts). They are listed in order of importance and I will release an update in a few weeks with these issues resolved.

1.    Objects of different types (perspective, KPI, calculated measure, measure, member property) must have unique names as the objects are referenced solely by their names at this time.

2.    References to measures or calculated measures used in the formulas of KPIs or calculated measures must be enclosed in brackets ([ and ]). For example, Measure.Sales must be written in the formula as [Measures].[Sales].

3.    There is a problem with relationships in Visio attaching themselves to other objects when I try to move it. This seems to happen if the relationship passes under other objects. I’m not a Visio VBA whiz, so I haven’t been able to get to the bottom of it. What seems to work is that I click on the object I intend to move. Then “left-click” the object to begin moving it. I really hope to resolve this in a future release.

4.    If you use a calculated measure in the formula of another calculated measure, be sure that the calculated measure in the formula is already defined. For example, if CM2=CM1/3, be sure CM1 is defined before CM2.

5.    Calculated measures in the MDX script must begin exactly as: CREATE MEMBER CURRENTCUBE.[MEASURES]. This is the key by which the parser determines whether or not the command is a calculated measure. This is the way the BIDS UI formats it anyway if you enter your calculated measures using the “form view”.

Other issues can be found as comments in the code itself.

Demo 1 Script for using the Visio Document Sample

This script demonstrates the intent for the KPI Cause and Effect Graph that I've incorporated so far. It is packaged with relationships mined from a sample cube. This script can be performed with just Visio 2003 installed. It will not need to connect to Analysis Services.

As a reminder, this sample does consist of a very small set of objects. However, the value of this graph becomes more pronounced when the dozens or hundreds of KPIs and Information Worker roles in an enterprise are considered.

Exercise 1 – Introducing the Sample Graph

Figure 2 – Object Window showing information about the Total Sales KPI.

Exercise 2 – Finding Common Measures

This exercise demonstrates how to find measures common to two different information workers. The value of this is to point out how the actions of one information worker can affect another. The result of this exercise is shown in Figure 1 above.

1.    Run the ResetColors macro.

·         From the Visio menu bar, select Tools/Macros/Macros.

·         Select ThisDocument.ResetColors

·         Click the Run button.

·         All of the shapes will be reset to white and the relationships will be reset to thin blue lines.

2.    Select the Sales Manager and Store Sales Billing Manager perspectives (the two rectangle shapes towards the lower-left corner).

3.    Run the CommonMeasures macro to trace up the graph.

·         From the Visio menu bar, select Tools/Macros/Macros.

·         Select ThisDocument.CommonMeasures. The measures that are common to these two perspectives will be highlighted. In this case, that is Store Sales.

Exercise 3 – Tracing Objects

Figure 3 depicts the effects of tracing the Internet Sales measure. The purpose of doing this is to determine who will be affected by changes to the Internet Sales manager. We can see that three out of the four information workers are affected. Most obviously, the Internet Sales Manager is interested and in fact is the one looking for this information. The others are less obvious, but we can trace the paths. Both the CEO and Sales Manager are interested in the Total Sales KPI, which leads to the Total Sales calculated measure, which leads to Internet Sales.

1.    Run the ResetColors macro.

·         From the Visio menu bar, select Tools/Macros/Macros.

·         Select ThisDocument.ResetColors

·         Click the Run button.

·         All of the shapes will be reset to white and the relationships will be reset to thin blue lines.

2.    Select the Internet Sales measure shape (the hexagon with “Internet Sales” as text towards the right side).

3.    Run the Trace macro to trace up the graph.

·         From the Visio menu bar, select Tools/Macros/Macros.

·         Select ThisDocument.Trace. A window titled “Trace Internet Sales” will appear.

·         Leave all the defaults. “Up the Hierarchy” will trace the Internet Sales relationships.

·         Click “OK”. The Visio document should look like Figure 3.

4.    Run the Trace macro to trace down the graph.

·         From the Visio menu bar, select Tools/Macros/Macros.

·         Select ThisDocument.ResetColors.

·         Select the “Sales Manager” perspective (the rectangle with the text “Sales Manager” at the lower-left corner.

·         From the Visio menu bar, select Tools/Macros/Macros.

·         Select ThisDocument.Trace. A window titled “Trace Sales Manager” will appear.

·         Select the “Down the Hierarchy” option button.

·         Click “OK”. The nodes that directly affect the Sales Manager perspective are highlighted.

Figure 3 – Tracing a Measure to find all of the objects it affects.

Demo 2 Script for the Visio Document Connected to Analysis Services 2005

The prior set of exercises focused more on the end result of the Visio document. And, it didn’t require a connection to Analysis Services, just Visio. This set of exercises explores functionality while connected to Analysis Services.

Exercise 1 – Loading a KPI Cause and Effect Graph from Your Own Cube

This script takes you through the process of creating a KPI Cause and Effect Graph from your own cube. However, if you don’t have cubes with KPIs and perspectives defined, no relationships will be downloaded. So, for POC purposes, you can load the sample SQL Server database and SSAS database I’ve included. It’s the same cube that the figures in this blog illustrate.

Figure 4 – Visio macro that will load objects from a cube.

Exercise 2 – Retrieving Current KPI Values

This exercise demonstrates how to retrieve and display current KPI values from the cube:

Figure 5 – Visio macro to load current values from a cube.

Planned Improvements

Surely, there are many minor enhancements that will improve the “abilities” and smooth out the rough edges. However, what is listed in this section are features that push forward on the roadmap for the KPI Cause and Effect Graph. Here is a list of improvements I’ve planned for the near future (whenever I will ever have the time):

  1. Include the breakdown of named calculations from the Data Source View (DSV). For example, a measure called “Sales” is probably the product of unit price and the number of units. However, this measure would be used so extensively that it wouldn’t be a best practice to define Sales as a calculated member just so this graph can be provided with the breakdown. However, under many circumstances, it would be OK to define a named calculation for Sales in the data source view. This improvement would parse the named calculation providing the information: The Sales measure is composed of the price and units fields in the Sales table of the DSV.
  2. Addition of shapes and relationships in the Visio diagram that don’t exist in the UDM. There are many “prices to pay” for our actions that aren’t captured in any software. For example, most companies don’t capture in a measurable manner the “good will” a company should build for an information worker that performs above and beyond the call of duty.
  3. “Effect on Value”. Figure 2 shows a combo box for this value, but it doesn’t do anything at this time. This value determines how the value of a shape affects the value of a related shape. For example, the status of the “Total Sales” KPI would be adversely affected by an increased Sales Goal. This feature begins outfitting the KPI Cause and Effect graph with capability better analyze the effects of an information worker’s chosen action to improve a KPI. There is a difference between an information worker’s actions to stick to the plan, which results in reaching the goal in a smooth manner. There are also complex relationships. For example, the sales manager (in charge of total sales) and the Internet Sales Manager would benefit from increased Internet Sales. However, the Sales Manager may have a conflict of interest if increased Internet sales cannibalizes store sales.
  4. Improved formula parser. This will fix many of the “known issues” as well as open the door for making the KPI Cause and Effect Graph of much greater BI value. Part of the parser improvement will include looking for patterns. For example, if a KPI’s status formula is the common “(sales-goal)/goal”, an extensible pattern searcher would recognize that an increase in a goal measure adversely affects the status of the KPI.