A few days ago on GitHub, I received a request to explain how to assert the results of some calculations with measures and also how to automate these tests with NBi. This blog post is the complete answer to this question.
To illustrate the case, I’ll use the Adventure Works 2012 multidimensional cube, but you can do the same with any other database. In this cube, you’ve a measure-group with the measures: Internet Gross Profit, Internet Sales Amount, Internet Gross Profit Margin. As you can guess, the last measure is the result of the division of the two firsts (expressed in percentage). My test will assert that this is still the case when I’m slicing by customers’ country.
To achieve this, I’m defining a system-under-test with a result-set defined in a MDX query:
<system-under-test>
<resultSet>
<query>
<![CDATA[
select
{
[Measures].[Internet Gross Profit],
[Measures].[Internet Sales Amount],
[Measures].[Internet Gross Profit Margin]
} on 0,
{
[Customer].[Country].members
} on 1
from
[Adventure Works]
]]>
</query>
</resultSet>
</system-under-test>
Asserting the calculations’ result can be executed by a all-rows and a predicate where you’ll check that the content of the last measure is equal to the quotient of the two firsts.
Due to the complex names of columns for MDX queries, it’s recommended to use aliases. With NBi, we can define an alias based on columns’ position (starting at 0). The position is 0 will be for the column [Customer].[Country].members, we can skip it. But columns 1 to 3 will receive aliases:
<alias column-index="1">Profit</alias>
<alias column-index="2">Sales</alias>
<alias column-index="3">Margin</alias>
The test will need to compute the quotient between Profit and Sales and check that this value is equal to the value returned by the cube in the column Margin. This can be expressed in the following expression:
<expression name="calculate">
Round(Profit/Sales, 4) = Round(Margin, 4)
</expression>
The Round function is provided by the underlying library for the evaluation of these expressions: NCalc. In this case, it’s extremely useful to avoid rounding issues during computations. Due to the percentage, I didn’t round to 2 digits after the decimal separator but to 4.
Based on the expression defined above, we can use the predicate true. Each row not validating the equality defined above will return false and not validate the predicate. The test is a all-rows so if any row is not validating the predicate then the test will fail.
Some developers will perhaps try to use the predicate equal in place of true, but unfortunately, at this moment, NBi can only handle fixed value for the equal predicate and it’s not possible to specify a column This limitation is invalidating the approach with equal.
The full code for this test is
<test name="Profit, Sales and Margin">
<system-under-test>
<resultSet>
<query>
<![CDATA[
select
{
[Measures].[Internet Gross Profit],
[Measures].[Internet Sales Amount],
[Measures].[Internet Gross Profit Margin]
} on 0,
{
[Customer].[Country].members
} on 1
from
[Adventure Works]
]]>
</query>
</resultSet>
</system-under-test>
<assert>
<all-rows>
<alias column-index="1">Profit</alias>
<alias column-index="2">Sales</alias>
<alias column-index="3">Margin</alias>
<expression name="calculate">
Round(Profit/Sales, 4) = Round(Margin, 4)
</expression>
<predicate operand="calculate" type="boolean">
<true />
</predicate>
</all-rows>
</assert>
</test>
The second part of this blog post is about the automation of generation of this kind of tests. Let’s start by defining our test-cases. To define them, I’ll put the aliases that I’d like to use (column alias), the expected formulae (column expression), the exact name of the measure (column measure) and how many digits are needed for rounded comparisons (column round). In this example, I created two test-cases. The first test-case will check that Profit is equal to Sales minus Cost and the last one is asserting that Margin is equal to Profit divided by Sales.
alias;expression;measure;round
Sales,Cost,Profit;Sales-Cost;[Measures].[Internet Sales Amount],[Measures].[Internet Total Product Cost],[Measures].[Internet Gross Profit];2
Profit,Sales,Margin;Profit/Sales;[Measures].[Internet Gross Profit],[Measures].[Internet Sales Amount],[Measures].[Internet Gross Profit Margin];4
To load this CSV file in genbiL, I’ll need to use the load file function:
case load file 'calculations.csv';
Note that in this CSV file each column is separated by a semi-column (;). In the columns alias and measure, I’ve more than one value and each of these values are separated by a coma (,).
To parse this CSV file with genbiL, I’ll need to use the function split. This function is transforming a single value into an array of values using a separator.
case split column 'alias' with value ',';
case split column 'measure' with value ',';
After these two lines of code, the test-cases’ scope consists in two cases and each of them has an array of three elements in the columns alias and measure.
Next steps are straightforward: loading the template (that we’ll compose just after this), generate the tests and save them.
template load file 'calculations.nbitt';
suite generate;
suite save 'calculations.nbits';
Once we’ve the CSV file and the genbiL script, we still need to build a template. There are two tricky parts in this template. The first one is to generate the list of measures in the MDX queries. Elements of this list must be separated by a coma. If you’ve three elements, you’ll need two comas.
select
{
[Measures].[Internet Gross Profit],
[Measures].[Internet Sales Amount],
[Measures].[Internet Gross Profit Margin]
} on 0,
To achieve that with the StringTemplate engine, you’ll need to use the separator keyword (doc) for any variable containing an array.
select
{
$Measure; separator=",
"$
} on 0,
The second tricky part sounds similar but is really different. defining the list of aliases. In this case there is no separator, if you’ve three elements, you’ll need exactly the same xml elements three times. An additional detail is that we’ll need to add some figures starting at 1 and increasing on each new element. Hopefully StringTemplate has the concept of anonymous templates that can be really helpful for this. In this case we’ll explain that for each value contained in the array of column alias, we’ll have to create an xml element alias and add an attribute column-index with an incremental value. Note that the incremental value is automatically assigned to $i$ by StringTemplate and is starting at 1. Each element contained in the array of alias will be assigned to the variable x in the anonymous template. The anonymous template is surrounded by the pipe and the right curly brace.
$alias:{ x | <alias column-index="$i$">$x$</alias>}$
The template will also the StringTemplate functions trunc returning all the elements of an array except the last one and the function last returning the last element of an array.
The full listing for the template is available here under:
<test name="$trunc(alias); separator=" and "$ to calculate $last(alias)$">
<system-under-test>
<resultSet>
<query>
<![CDATA[
select
{
$measure; separator=",
"$
} on 0,
{
[Customer].[Country].members
} on 1
from
[Adventure Works]
]]>
</query>
</resultSet>
</system-under-test>
<assert>
<all-rows>
$alias:{ x | <alias column-index="$i$">$x$</alias>}$
<expression name="calculate">
Round($expression$, $round$) = Round($last(alias)$, $round$)
</expression>
<predicate operand="calculate" type="boolean">
<true />
</predicate>
</all-rows>
</assert>
</test>
And voilà, now you just need to run your genbiL script and you’ll get your two tests well generated. Feel free to add new cases!
.
Ambiguity vs Information 