My colleague Jon Axon sent round a very interesting mail at work this morning, detailing a new method he’s using for testing during cube development. Rather than try to paraphrase it, here’s what he sent:
When I’m modelling cubes, I generally tend to create a basic model which produces correct figures, and then tweak it for performance e.g. refine calculations, play around with dimension settings; this is similar to code refactoring and an important part of this is constant repetition of unit tests to ensure you don’t alter the behaviour of the cube as you improve performance i.e. the numbers stay the same. You could always hack some ADOMD code to run the same query on your new cube iteration and on an established baseline cube and compare cellsets, but this is a pain. However thanks to a couple of MDX tricks you can actually go a surprisingly long way to producing a generic framework for this purely within an MDX query, as shown below.
The gist of it is that you produce a fixed baseline cube with correct behaviour, and then copy-and-paste this within the same database to create a version of the cube for refining; only the latter is subequently altered. The two cubes don’t have to have identical structure (e.g. identical dimensionality), however you can only compare values of cells whose context is defined by identically named members in both cubes (e.g. you can’t compare measure M in one cube to measure N in another). The template is as follows; only the sections marked as USER DEFINED need to be changed i.e. a couple of set/member definitions at the top, and the cube name at the bottom of the query (unfortunately MDX syntax doesn’t allow us to group these items into a single location). Think of the user-defined section as parameters for a procedure, these are:
TestDomain: a set expression defining the subcube (minus measures) whose values you want to compare across your two target cubes.
TestMeasures
: the set of measures to be compared within the context of the subcube defined above.
TargetCube: a string specifying the name of the cube (within the same database as the cube in the FROM clause) against which the comparison is to be made.
DisplayDetails
: a string "Y" or "N" indicating whether executing the query should display a detailed report i.e. the results of the comparison on a cell-by-cell basis, or whether an "executive summary" should be given i.e. a single cell indicating complete success or at least one failure (the first such failure will be indicated).
Here is an example:
WITH
/* USER DEFINED SECTION */
SET TestDomain as ‘NonEmptyCrossjoin(Except(DimA.Members, {DimA.DefaultMember}),
Except(DimB.Members, {DimB.DefaultMember}),
Except(DimC.Members, {DimC.DefaultMember}))’
SET TestMeasures as ‘{BadCalc}’
MEMBER Measures.TargetCube as ‘"TestCubeDeltaA"‘
MEMBER Measures.DisplayDetails as ‘"Y"‘
/* END USER DEFINED SECTION */
SET FinalMeasures as ‘Except(TestMeasures, {TargetCube, CurrentTuple, Indicator, Result, DisplayDetails})’
MEMBER Measures.CurrentTuple as ‘")"‘
CELL CALCULATION Calc for ‘({Measures.CurrentTuple})’ as ‘Iif(CalculationCurrentPass() = 129, "(", "") +
Iif(Dimensions(129 – CalculationCurrentPass()) IS Measures, "",
Dimensions(129 – CalculationCurrentPass()).CurrentMember.UniqueName +
Iif(CalculationCurrentPass() = (130 – Dimensions.Count), "", ",")) +
CStr(CalculationPassValue(Measures.CurrentTuple, -1, RELATIVE))’,
CALCULATION_PASS_NUMBER = 129, CALCULATION_PASS_DEPTH = 128,
CONDITION = ‘CalculationCurrentPass() > (129 – Dimensions.Count)’
MEMBER Measures.Indicator as ‘Iif(Len(Generate(FinalMeasures,
Iif(FinalMeasures.Current = LookupCube(CStr(TargetCube), "{{" + CStr(CurrentTuple) + "} * {" +
TupleToStr(FinalMeasures.Current) + "}}.Item(0)"), "", "x"))) > 0,
"MISMATCH!", "OK")’
MEMBER Measures.Result as ‘Iif(Count(Filter(TestDomain, CStr(Indicator) <> "OK") as failures) = 0, "All OK", "First failure: " + TupleToStr(failures.Item(0)))’
SELECT
StrToSet(Iif(UCase(CStr(DisplayDetails)) = "Y", "Union(FinalMeasures, {Indicator, Result})", "{Result}")) on 0,
StrToSet(Iif(UCase(CStr(DisplayDetails)) = "Y", "TestDomain", "{TestDomain.Item(0).Item(0)}")) on 1
FROM
/* USER DEFINED SECTION */
TestCube
/* END USER DEFINED SECTION */
One interesting "geeky" point about this aside from any useful purpose it may have is that it demonstrates a bona fide situation in which Chris’ generic "currentmember from every dimension" cell calculation (see his blog) is necessary to work around MDX limitations; the semantics of LookupCube() are such that to my knowledge there is no other way of accomplishing this generically.
Unfortunately due to both the cell calculation and the LookupCube(), performance can decrease spectacularly as the subcube size increases (particularly when DisplayDetails is set to "Y"), so try to restrict use to relativey small cellsets (as one would expect of unit tests) rather than entire cubes at once. I’ve made no particular effort to tune any of this template so you may well be able to improve it dramatically.
You can of course also accomplish a lot of this with less performance overhead by merging two such target cubes into a single virtual cube and comparing measures directly, but this imposes further restrictions e.g. only one custom rollup formula can be in operation at a time on a specific dimension in a virtual cube, whereas by using LookupCube() across two base cubes you can employ separate rollups on each (in fact this is exactly the situation for which I originally came up with this).
NB/ if you get cells reporting COM errors then this is typically due to you requesting something in the LookupCube() that can’t be found; check your TestDomain and TestMeasures definitions to ensure they are common to both cubes.
