In Part Two of this series I showed how to perform a simple MDX Insert, copying a single cell of data in an ASO cube. In this post I will show how to create a slightly more complicated calculation.
Keep in mind that you need to be using Essbase in OAC for this to work. It will not work in Essbase 11.1.2.4 or prior.
WITH
I'm going to use the MDX 'With' statement in order to create a calculated member. The With statement comes first in an MDX script. You can use it to define an Essbase member on the fly. You can then use that member in other calculations or in the output of an MDX query. To keep things simple and easy to reproduce for those of you following along at home, I will recreate the Variance member in ASOSamp.Basic.
First I added a member in the outline called Stored Variance.
Next I created my MDX statement. The 'With' statement comes first. In this case I called my member on the fly _Variance. I put an underscore in front of members on the fly so they won't conflict with any Essbase members and so that I can quickly see what's being created dynamically in MDX. Of course, if you happen to use underscores to begin member names in your Essbase outline (heaven help you and) find a different method.
The syntax here is hopefully straightforward. You define the member name AS some formula which goes in single quotes. Here I am subtracting [Prev Year] from [Curr Year].
Next comes the INSERT statement. Remember from part two that the syntax is "(source tuple)" TO "(target tuple)".
Then the SELECT statement is added surrounded by parenthesis. In this case I'm putting the _Variance member on the columns and then the Crossjoin of all the level zero members from the remaining dimensions on the rows. Notice that I don't need to bother leaving out shared or dynamic members.
Once I validate and save the script, I can run the job. To do that I go to the jobs screen in the Jet UI.
Then select New Job -> Run MDX.
Finally, I select the MDX script I want to run.
This calculation took 6.9 seconds to run with the standard ASOSamp.Basic data.
Then the SELECT statement is added surrounded by parenthesis. In this case I'm putting the _Variance member on the columns and then the Crossjoin of all the level zero members from the remaining dimensions on the rows. Notice that I don't need to bother leaving out shared or dynamic members.
Once I validate and save the script, I can run the job. To do that I go to the jobs screen in the Jet UI.
Then select New Job -> Run MDX.
Finally, I select the MDX script I want to run.
This calculation took 6.9 seconds to run with the standard ASOSamp.Basic data.
Why?
Why might I want to run a calculation like this? In this case it doesn't make a lot of sense. ASO should be able to handle a simple variance calculation with little effort. But what about a rate times volume calculation? Or a foreign currency calculation? Those get much trickier -- see why here.
The Old Way
You could run this calculation with the Execute Allocation MaxL statement. Here's the statement:
execute allocation process on database ASOSamp.Basic with
pov "CROSSJOIN(Except([Measures].LEVELS(0).MEMBERS,{[Avg Units/Transaction],[% of Total]}),
CROSSJOIN(Descendants([MTD],LEVELS([Time],0)),
CROSSJOIN([Transaction Type].LEVELS(0).MEMBERS,
CROSSJOIN([Payment Type].LEVELS(0).MEMBERS,
CROSSJOIN([Promotions].LEVELS(0).MEMBERS,
CROSSJOIN([Age].LEVELS(0).MEMBERS,
CROSSJOIN([Income Level].LEVELS(0).MEMBERS,
CROSSJOIN([Products].LEVELS(0).MEMBERS,
CROSSJOIN([Stores].LEVELS(0).MEMBERS,[Geography].LEVELS(0).MEMBERS)))))))))"
amount "([Curr Year]-[Prev Year])"
target ""
range "{[Stored Variance]}"
spread;
Notice that I had to add in an Except() statement to remove members with formulas. I also had to change the way I was pulling the Time dimension members because they too had some formulas. When I ran the script I got these warnings:
So I probably should have removed those shared members from the POV as well. What a pain...
This script took 7.9 seconds to run. I understand that 1 second is not a lot of time but in this case the MDX Insert was about 15% faster than the Execute Allocation. I've always seen performance improvement for MDX Insert when comparing the two methods. Over the course of many longer calculations, the performance improvement can be significant.
[DBNAME: Basic] The argument [POV] ignores the duplicate member [Flat Panel]
[DBNAME: Basic] The argument [POV] ignores the duplicate member [HDTV]
[DBNAME: Basic] The argument [POV] ignores the duplicate member [Digital Recorders]
[DBNAME: Basic] The argument [POV] ignores the duplicate member [Notebooks]
So I probably should have removed those shared members from the POV as well. What a pain...
This script took 7.9 seconds to run. I understand that 1 second is not a lot of time but in this case the MDX Insert was about 15% faster than the Execute Allocation. I've always seen performance improvement for MDX Insert when comparing the two methods. Over the course of many longer calculations, the performance improvement can be significant.
NON EMPTY
In case you're wondering, I did try adding a NON EMPTY directive before the rows set and it yielded no performance improvement.
Appendix
MDX Script for easy copy and paste:
WITH
MEMBER Years.[_Variance] AS
'[Curr Year]-[Prev Year]'
INSERT
"(Years.[_Variance])" TO "(Years.[Stored Variance])"
INTO ASOSamp.Basic FROM
(
SELECT
{Years.[_Variance]} ON COLUMNS,
{CROSSJOIN([Measures].LEVELS(0).MEMBERS,
CROSSJOIN([Time].LEVELS(0).MEMBERS,
CROSSJOIN([Transaction Type].LEVELS(0).MEMBERS,
CROSSJOIN([Payment Type].LEVELS(0).MEMBERS,
CROSSJOIN([Promotions].LEVELS(0).MEMBERS,
CROSSJOIN([Age].LEVELS(0).MEMBERS,
CROSSJOIN([Income Level].LEVELS(0).MEMBERS,
CROSSJOIN([Products].LEVELS(0).MEMBERS,
CROSSJOIN([Stores].LEVELS(0).MEMBERS,[Geography].LEVELS(0).MEMBERS)))))))))} ON ROWS
FROM ASOSamp.Basic
)
