Are your Voyager calculations showing the wrong values? It could be a solve order problem. This article explains what solve order is, how Voyager uses it and how to fix problems with solve orders.
A Quick example
Imagine you have the following Voyager cross-tab:
A | B | % A vs B | |
Jan | 10 | 8 | 80 % |
Feb | 25 | 5 | 20 % |
Mar | 3 | 6 | 200 % |
Total | 38 | 19 | ??? |
Imagine you have two columns that represent the sales for two different products, product A and product B. Imagine you have a third column that calculates the percentage of one against the other so you can compare the performance of the two products. Imagine you have selected three months of the year on the rows. You have also created a calculation that sums up the months. What does the cell in the bottom right hand corner show? Here there is an intersection between two calculations so it could be one of two things. It could calculate the sum as defined by the ‘Total’ calculation and sum up all the percentages in the final column and display 300%. It could take the percentage and display the Total of A against the Total of B and display 50%. In this situation you would want the percentage calculation to be used and 50% to be displayed. Is that what happens for you? How does the computer know which one to display?
If your OLAP server is Microsoft Analysis Services the answer will depend on where the percentage and total calculations were created and the relative value of a property of the calculations called solve order.
Server calculations and client calculations
Microsoft Analysis Services allows calculations to created in two fundamentally different places. The first type of calculation can be thought of as being created on the OLAP ‘server’. These calculations are baked into the definition of a particular cube and can be available to anyone who consumes that cube. An example could be a measure which calculates profit from two other revenue and cost measures. Server calculations don’t have to be measures. They could be differences between two product categories or months and reside on product or date hierarchies.
Client calculations are created by client applications. Client calculations have two possible scopes: session and query. A session calculation is only available in the session of the user that created it and will disappear when the session ends. A query scoped calculation only exists for the specific MDX query it was defined in. So unlike server calculations, client calculations are only available to a particular instance of a client application using the calculations at a particular time.
For Microsoft Analysis Services 2005 and 2008 the default behavior is that all client calculations are calculated after any server calculations have been calculated. To understand what this means for the example above I will create something similar using the Adventure Works sample cube on Analysis Services 2008.
A longer example
The first step is to create a percentage based calculation on the server to represent the calculation on columns in the quick example. The following screen shot of Microsoft’s BI Studio with the Adventure Works cube definition loaded shows a calculation displaying the definition of a percentage of one product category against another.
Here is how this calculation appears in Voyager.
Now in Voyager if I create a sum calculation totaling up the three months displayed:
I get the following:
Notice the value in the bottom right hand corner is not the desired value. It is the total of the three percentages but formatted as a currency so it shows $0.32 rather than 31.51% as it would appear when formatted the same as the other percentages.
This is a demonstration of client calculations being calculated after server calculations, so the value that appears in the bottom right hand cell is the last value that was calculated which is that of the client calculation which has summed up the three percentage values.
This can be further demonstrated by creating the equivalent of the total calculation on the server and the equivalent of the percentage calculation on the client and viewing all the results together.
Here only one of the four possible combinations gives the desired answer of 10.54%. This combination is where the Total calculation is located on the server and the percentage calculation is on the client. This gives the correct answer as the client calculation i.e. the percentage is evaluated after the sum calculation.
It is also possible to get the two server calculations to give the correct answer. It is possible to tell the server which order to evaluate its calculations. This can be done either by changing the order of the calculations in the master list of calculations in the cube definition or by explicitly specifying different values for the calculations SOLVE_ORDER property.
The following screen shot shows the master list of calculations with the total and percentage calculations in the correct order to get the desired result.
As the percentage calculation is after the total calculation it will be calculated second. Even if the calculations were in the opposite order in this list you can specify a specific solve order for each one by directly editing the cube’s master MDX script as follows:
Here you can see that even though the percentage calculation appears above the sum calculation in the script it also has a higher solve order (underlined in red) so will be executed after a calculation with a lower solve order.
Whichever method is used, the results that Voyager will now display are as follows:
Notice this change has had no effect on the client calculations values.
The next logical question would be is there something that could be done to the two client calculations to get the desired result? Unfortunately Voyager creates these types of client calculations with exactly the same solve order so which calculation is evaluated first is ambiguous. In this example, when the two client calculations intersect it happens to evaluate the sum last. If I were to create a percentage calculation on the date hierarchy and a sum on the category dimension I get the opposite behavior as can be seen in the following screen shot.
Here you can see that the value in the bottom right hand corner is the percentage of the sums rather than the sum or the percentages. Experiments seem to suggest that this behavior is determined by hierarchy i.e. calculations on Category get evaluated before those on Date as swapping the axis or switching the order the calculations were created in has no effect.
The special case
In the above examples we have seen that client calculations always get evaluated after server calculations. This is true for all calculations that Voyager has apart from one. This is the Rollup calculation. The rollup calculation uses a special MDX function call Aggregate which always get evaluated first. The aggregate function uses the cubes aggregation rules for that particular part of the cube. In many cases this will be equivalent to a sum, but if your cube may have other aggregations like average, count or something more exotic.
Here is a screen shot indicating how a rollup calculation can be created in Voyager.
The following screen shots shows the results of this calculation compared to the other ones we have been using in this example.
Given that the aggregations in this particular part of the Adventure Work cube are summation aggregations the rollup calculation is performing the same mathematics as our total calculation. Notice however that the rollup calculation now gives the desired result for both the client and server created percentage calculation as it is calculated before other server calculations and also client calculations (as client calculations come after server calculations).
A summary of Voyager’s use of Solve Order
Voyager creates the calculations used in the example with a solve order of one. Voyager has other types of calculations which are not created with a solve order of one. Given that a calculation with a higher solve order will be evaluated after ones with a lower solve order it is useful to know which calculations are created with which solve order.
There are two main methods or creating calculations in Voyager. One is through the calculation dialogue shown in screen shots already to create the total and percentage calculations. This other is the visual total dialogue shown here:
All calculations created through the visual total dialogue above have a solve order of two except the ‘Count’ visual total which has a solve order of three.
All calculations created through the other calculation dialogue have a solve order of one, except the rollup calculation which is a special case as described and has no solve order defined, and the ‘Rank’ and ‘Percentage Contribution’ calculations which have solve orders of two.
An example of where the knowledge of the various solve order of Voyager client calculations is important is where various Voyager calculations interact. In the following screen shot there is a sum calculation on columns that totals up the two products displayed on columns. There is also a ‘count’ visual total on rows.
As the count visual total has a solve order of 3 and the sum calculation has a solve order of 1 then the count calculation will be calculated after the sum. This means that the number in the bottom right hand corner is the count of the entries in the sum column rather than the sum of the counts.
What Voyager doesn’t let you do
Voyager’s choice of solve orders for its calculations work for most situations most of the time. However there are bound to be situations where the business question you want to answer requires your calculations to have different solve orders from the ones Voyager sets.
The solution therefore seems straight forward: give Voyager the ability for users to set their own solve orders. This will work fine for client calculations interacting with other client calculations, but as we have seen: all client calculations are evaluated after server calculations regardless of the solve order. But we have also seen that server calculations can be given solve orders. So why can’t client and server calculations use their solve orders together to give us the results we want?
In Analysis Services 2000 this is exactly what happened. There was no distinction between when a server calculation and when a client calculation would be evaluated, it was all determined by solve order. This did mean however that client applications, in order to be sure to get the correct results, would need to know what solve orders any calculations on the server had. This posed a problem as the cubes would generally not be designed by the same people who designed the client tool (these would generally be people working for different companies who would probably never even have any contact). Microsoft took the decision in MSAS 2005 and beyond to separate the evaluation of server and client calculations so that developers of client applications could be sure that any calculations they created would be evaluated after whatever would happen on the server making the behavior of their client much more predictable.
However, there was obviously resistance, or at least some good examples of where this wasn’t always a good idea as from service pack two of MSAS 2005, Microsoft introduced a way get the MSAS 2000 behavior back and specify that a calculation could be evaluated before a server calculation. Client calculations could now be created with a property that tells the cube to evaluate the calculation with the other server calculations rather than the client calculations.
The use of this property can be demonstrated in the following example. Using the same calculations we created on the server before where the server calculation for the percentage was specified with a solve order of 10. We can create some MDX to simulate what Voyager was doing.
The Total calculation is a client calculation defined in the MDX. We can see that the bottom right hand value is incorrect.
If the MDX is now modified to include the new property: SCOPE_ISOLATION = CUBE
This now gets the correct result.
Therefore for an OLAP client, like Voyager, to offer the full range of control over calculated values it would have to offer the user both control over solve order and control over the option to have calculations evaluated with the server calculations.
Further reading
Explanations of solve order and the scope isolation property can be found on MSDN.