Now that BOL is being overhauled my list of things that it misses out is getting shorter and shorter…
Anyway, did you know that there are two syntaxes you can use to define calculated members in your MDX Script? There’s good old CREATE MEMBER, which Visual Studio itself uses, and there’s also the more concise CREATE syntax. Here’s the BNF:
CREATE [HIDDEN] [<parent>.]<name> = <expression>
Nothing special about it really; just note that if [<parent>] is missing it creates the member on the Measures dimension, and if you need to specify properties such as FORMAT_STRING etc for the calc then you need to either use assignments or edit the DDL of the MDX Script.
The only person I’ve ever seen use this syntax is Mosha in a few of his blog posts. I don’t know why it was introduced apart from the fact that is is more concise; I do know that in one instance it behaves differently from CREATE MEMBER. Take the following MDX Script an put it onto the Adventure Works cube:
–an example of the old syntax
CREATE
MEMBER CURRENTCUBE.[MEASURES].[Old Style] AS 1,VISIBLE
= 1 ;–an example of the new syntax
CREATE
[New Style] = 1;
SCOPE
([Date].[Day Name].&[1]);
THIS=100;
END
SCOPE;
SCOPE([Date].[Day Name].&[2]);
THIS=100;
END SCOPE;
CREATE
MEMBER CURRENTCUBE.[MEASURES].[Old Style 2]
AS 1,
VISIBLE
= 1 ;
If you run the following query:
select
{[Measures].[Old Style], [Measures].[New Style], [Measures].[Old Style 2]} on 0,[Date].[Day Name].members on 1
from [adventure works]
You’ll see that the cell for [Old Style 2] and [Sunday] doesn’t contain the value 100, whereas the cell for [Old Style 2] and [Monday] does. When I found out about this I was told that it was a bug, but was also the result of mixing old style calculated member definitions with MDX Script assignments, and that if you were going to be doing assignments in your cube then you should always use the new style calculated member definition syntax. So perhaps it is a good idea to use the new style syntax all the time – although I know in my case, old habits will die hard.
Hi Chris,
Interesting article, but I disagree with both your conclusions: a) that it\’s a bug, and b) that there\’s a difference between "old style" and "new style". I think the result you see is simply the new Solve Order behavior in a script: sequence determines precedence. Here\’s an analogous set of script commands (all using "new style") that displays a similar effect but making it clear that the sequence of the statements explains the results:
Create [New Style1] = 1;
[Date].[Day Name].&[1]=100;
Create [New Style2] = 2;
[Date].[Day Name].&[2]=200;
Create [New Style3] = 3;
[Date].[Day Name].&[3]=300;
Of course, you have to change your query to show the three "new style" measures. But in every case, in the resulting display the statement later in the script "wins" over the earlier statement. That same explanation works for your example.
And thanks for turning me on to the new style! I hadn\’t picked up on it before–it really does make the script look cleaner.
Cheers,
Reed
No, I promise you it is a bug! I have the dev team on record about it…
In your example you\’re showing the behaviour I would expect from the new style calculations. The point about my example is that out of the two assignments that take place before the declaration of [Old Style 2] only the first is applied – a clear inconsistency.
Hi Chris,
This has turned out to be much more interesting than I had thought.
After replicating your original scenario, I was trying to figure out why the behavior different between Monday and Tuesday. That\’s when I thought I found that sequencing gave the same result.
You are definitely correct that the "new style" behaves differently than the "old style". And I missed the difference when I looked at it before. I would argue, however, that it\’s not really a bug–although if MS wants to call it a bug, that\’s their business; there are plenty of things I think are bugs that they don\’t acknowledge :-).
The whole issue appears to be simply that the "new style" script command increments the Calculation Pass, while the "old style" calculation member command does not. So whenever you create a new style calc member right after a script command, the pass gets incremented and the calc member wins. If you create old style calc members right after a script command, they\’re still on the same pass, so solve order comes into play. The reason they call it a bug is probably because they\’re trying to make solve order completely irrelevant. If you just add ,SOLVE_ORDER = 1 to an "old style" calculated member, it suddenly wins, and thus acts just like a "new style" one.
Here\’s the series of commands that clarified it for me:
CREATE [PassNumber] = CalculationCurrentPass();
[Date].[Day Name].&[1]=100;CREATE MEMBER CURRENTCUBE.[MEASURES].[Old1] AS 10,SOLVE_ORDER=1;Create [New1] = 1;[Date].[Day Name].&[2]=200;CREATE MEMBER CURRENTCUBE.[MEASURES].[Old2] AS 20,SOLVE_ORDER=1;CREATE [New2] = 2;[Date].[Day Name].&[3]=300;CREATE MEMBER CURRENTCUBE.[MEASURES].[Old3] AS 30,SOLVE_ORDER=1;CREATE [New3] = 3;[Date].[Day Name].&[4]=400;
Here\’s the MDX query that I used to minimize having to tweak the MDX whenever I changed the script. Stepping through the script as it executed was very revealing.
select Measures.AllMembers – Measures.Members on 0,[Date].[Day Name].members on 1from [adventure works]
Thanks again for the topic. It has been very interesting.
Cheers,
Reed
Interesting… thanks, Reed. Here\’s an excerpt of the email I got from the dev team when I reported this problem (where Measures.Test was my old-style calc):
… calculated members are actually created at the start of the script processing regardless of where they are defined in the script. That means that MEASURES.TEST exists during the assignments and since no specific measure is mentioned in the scope, all measures – including TEST – are covered by it. This means that TEST is getting assigned to. There is a bug which I have just filed in how the server does this assignment which is why re-assignment of the TEST measure is happening inconsistently.