MDX With Member Dynamic

[MarenC]

Hi,

I am trying to make a calculation in PAW dynamic, so it always calculates the previous - current month value.

I thought that by updating the cube MDX in the "With Member" part of the code, it would make the periods dynamic.

I basically created a cube view, with previous and current period in the columns and created a calculation for prev period - current period.

However, when I change the mdx to make the period dynamic, the mdx complies ok but the calculation just disappears. Is it not possible to do this?

For illustration, he "With Member" part of the mdx before the change was as follows:

Code: Select all

WITH MEMBER [Period].[Period].[Prev - Curr Period] AS [Period].[Period].[P09]-[Period].[Period].[P10], SOLVE_ORDER = 1, FORMAT_STRING = '#,##0;(#,##0)' SELECT NON EMPTY

I then updated it to:

Code: Select all

WITH MEMBER [Period].[Period].[Prev - Curr Period] AS STRTOMEMBER("[Period].[Period]." + STRTOMEMBER( "[Period].[" + [System Variables].([System Variables Lines].[Latest Actual Period],[Data Type].[String]) + "]").PROPERTIES("Previous Period"))-STRTOMEMBER( "[Period].[" + [System Variables].([System Variables Lines].[Latest Actual Period],[Data Type].[String]) + "]"), SOLVE_ORDER = 1, FORMAT_STRING = '#,##0;(#,##0)' SELECT NON EMPTY

all the mdx following the select statement remains unchanged.

are there limitations I should be aware of and can anyone reference what they are please.

Maren

[declanr]

Looks like you are missing closing the square bracket on the wrapped strtomember:

Code: Select all

WITH MEMBER 
[Period].[Period].[Prev - Curr Period] AS STRTOMEMBER("[Period].[Period]." + STRTOMEMBER( "[Period].[" + [System Variables].([System Variables Lines].[Latest Actual Period],[Data Type].[String]) + "]").PROPERTIES("Previous Period") + “]” )-STRTOMEMBER( "[Period].[" + [System Variables].([System Variables Lines].[Latest Actual Period],[Data Type].[String]) + "]"), SOLVE_ORDER = 1, FORMAT_STRING = '#,##0;(#,##0)' SELECT NON EMPTY

Sometimes when doing more complex MDX views I take it in steps and temporarily return a string showing what the string I will later be constructing as a StrToMember etc. can be good for finding typos etc.

[MarenC]

Hi Declan,

Thank you for the reply. Your advice was very useful but didn't solve the issue. It did help me with further tests which I would like to share below:

This works, i.e. hard coding the periods within the STRTOMEMBER:

WITH MEMBER [Period].[Period].[Sep - Oct] AS STRTOMEMBER("[Period].[Period]." + STRTOMEMBER("[Period].[" + "P09" + "]").PROPERTIES("Previous Period") + "]") - STRTOMEMBER("[Period].[" + "P10" + "]")

This also works, i.e. substituting the "P10" hard coded value for a reference to the system cube:

WITH MEMBER [Period].[Period].[Sep - Oct] AS STRTOMEMBER("[Period].[Period]." + STRTOMEMBER("[Period].[" + "P09" + "]").PROPERTIES("Previous Period") + "]") - STRTOMEMBER( "[Period].[" + [System Variables].([System Variables Lines].[Latest Actual Period],[Data Type].[String]) + "]")

It doesn't work if I then substitute the "P09" with the following, when I say doesn't work the MDX compiles but the calculation disappears:

STRTOMEMBER("[Period].[Period]." + STRTOMEMBER( "[Period].[" + [System Variables].([System Variables Lines].[Latest Actual Period],[Data Type].[String]) + "]").PROPERTIES('Previous Period')+ "]")

Incidentally on the above I wondered if by putting in a closing bracket, wasn't I missing the opening bracket, but adding that in caused the MDX to error.

I then thought of adding in a new element for the Previous period in the system variables cube and referencing that, as follows:

WITH MEMBER [Period].[Period].[Sep - Oct] AS STRTOMEMBER( "[Period].[" + [System Variables].([System Variables Lines].[Previous Actual Period],[Data Type].[String]) + "]") - STRTOMEMBER( "[Period].[" + [System Variables].([System Variables Lines].[Latest Actual Period],[Data Type].[String]) + "]")

But the above doesn't work, i.e. the MDX compiles but the calculation disappears.

However, putting the previous period second does work:

WITH MEMBER [Period].[Period].[Sep - Oct] AS STRTOMEMBER("[Period].[Period]." + STRTOMEMBER("[Period].[" + "P09" + "]").PROPERTIES("Previous Period") + "]") - STRTOMEMBER("[Period].[" + [System Variables].([System Variables Lines].[Previous Actual Period],[Data Type].[String]) + "]")

What the hell is going on?

Maren

[gtonkin]

Do you have members that are called something like [Sep - Oct] in the Period dimension?

If yes, then something like this may work:

Code: Select all

WITH MEMBER [Period].[Period].[Sep - Oct] AS 
STRTOMEMBER( "[Period].[" + 
[System Variables].([System Variables Lines].[Previous Actual Period],[Data Type].[String]) + " - " +
[System Variables].([System Variables Lines].[Latest Actual Period],[Data Type].[String]) + "]")

Which should resolve to:

Code: Select all

WITH MEMBER [Period].[Period].[Sep - Oct] AS 
STRTOMEMBER( "[Period].[" + 
"Sep" + " - " +
"Oct" + "]")

and then:

Code: Select all

WITH MEMBER [Period].[Period].[Sep - Oct] AS 
STRTOMEMBER( "[Period].[Sep - Oct]")

Note: in the above, the calculated member would have the same name as a member in the period dimension. You will likely encounter issues using the same name.

[MarenC]

Hi George,

Sorry I didn't quite follow your logic.

If I had an element called, [Sep - Oct], in the period dimension, I would just use it in the cube view period subset, rather than creating a calculation wouldn't I? I.e. why would I need With Member if that were the case?

Maren

[gtonkin]

Without knowing what elements you actually have and are trying to derive, I was grasping. Sorry for the confusion.

I have had issues with specifying the dim.hierarchy and the following may work in your case:

Code: Select all

WITH MEMBER [Period].[Period].[VAR] AS 
STRTOMEMBER("[Period].[" + 
[System Variables].([System Variables Lines].[Latest Actual Period],[Data Type].[String]) +"]")-
STRTOMEMBER("[Period].[" + 
STRTOMEMBER("[Period].[" + 
[System Variables].([System Variables Lines].[Latest Actual Period],[Data Type].[String]) +"]").PROPERTIES("Previous Period") + "]")
, SOLVE_ORDER = 1, FORMAT_STRING = '#,##0.00;(#,##0.00)' 
...

[MarenC]

Hi George,

I tried your mdx and it didn't work.

Basically I am trying to do a calculation for the current and previous period (Previous Period - Current Period).
The current period at the moment is P10 and the previous period is P09

The Current period is held in a system variables cube and the value in this cube is P10.
In order to make the calculation dynamic I wanted to reference the P10 in the system variables cube and derive the previous period from this using the Previous Period attribute on the Period dimension. I then thought I would try and create a previous period element in the systems variables cube to see if it was the .properties that was causing the issue.

All the tests above are the result.

It seems to me that if you have a calculation, eg. A-B, and the A and B are represented by 2 dynamic mdx based statements that lookup to a cube, then it won't work. Having one dynamic mdx based statement that looks up to a cube value will work as long as it is the second one.

E.g.

This works (I.e. First STROMEMBER doesn't lookup a cube value but second one does):

WITH MEMBER [Period].[Period].[VarTest] AS STRTOMEMBER("[Period].[P10]") - STRTOMEMBER("[Period].[" + [System Variables].([System Variables Lines].[Previous Actual Period],[Data Type].[String]) + "]")

This does not work (which is exactly the same as above but with the 2 STRTOMEMBERS flipped around, I.e. First STROMEMBER does lookup a cube value but second one doesn't):

WITH MEMBER [Period].[Period].[VarTest] AS STRTOMEMBER("[Period].[" + [System Variables].([System Variables Lines].[Previous Actual Period],[Data Type].[String]) + "]") - STRTOMEMBER("[Period].[P10]")

This also does not work (and many variations of):

WITH MEMBER [Period].[Period].[VarTest] AS 1* ((STRTOMEMBER("[Period].[" + [System Variables].([System Variables Lines].[Previous Actual Period],[Data Type].[String]) + "]") - STRTOMEMBER("[Period].[P10]")))

Maren

[declanr]

Hi Maren,

What you've described shouldn't be an issue at all. I would suggest raising an incident with IBM to see if there are issues with the version you are running; I do a lot of period comparisons very similar to what you have described that have worked for a number of years.

I built a small mockup to mimic your scenario to show it working:

Code: Select all

WITH 
	MEMBER 
		[Period].[Period].[Latest Period]
	AS 
		StrToMember ( "[Period].[Period].[" + [System Variables].([System Variables Lines].[Latest Actual Period], [Data Type].[String]) + "]" )
	MEMBER 
		[Period].[Period].[Prior Period]
	AS 
		StrToMember ( "[Period].[Period].[" + [System Variables].([System Variables Lines].[Previous Actual Period], [Data Type].[String]) + "]" )
	MEMBER 
		[Period].[Period].[VAR]
	AS 
		StrToMember ( "[Period].[Period].[" + [System Variables].([System Variables Lines].[Latest Actual Period], [Data Type].[String]) + "]" ) 
		- 
		StrToMember ( "[Period].[Period].[" + [System Variables].([System Variables Lines].[Previous Actual Period], [Data Type].[String]) + "]" )
SELECT 
	{[Data Type].[Data Type].MEMBERS} ON 0, 
	{
		{[Period].[Period].MEMBERS},
		{[Period].[Period].[Prior Period]},
		{[Period].[Period].[Latest Period]},
		{[Period].[Period].[VAR]}
	}
	ON 1 
FROM 
	[Test]

In the short term you could look at using interim calculated measures (don't have to be displayed in the view) in order to produce the final calculation, something like this:

Code: Select all

WITH 
	MEMBER 
		[Period].[Period].[Latest Period]
	AS 
		StrToMember ( "[Period].[Period].[" + [System Variables].([System Variables Lines].[Latest Actual Period], [Data Type].[String]) + "]" )
		,SOLVE_ORDER = 1
	MEMBER 
		[Period].[Period].[Prior Period]
	AS 
		StrToMember ( "[Period].[Period].[" + [System Variables].([System Variables Lines].[Previous Actual Period], [Data Type].[String]) + "]" )
		,SOLVE_ORDER = 2
	MEMBER 
		[Period].[Period].[VAR]
	AS 
		[Period].[Period].[Latest Period] - [Period].[Period].[Prior Period]
		,SOLVE_ORDER = 3
SELECT 
	{[Data Type].[Data Type].MEMBERS} ON 0, 
	{
		{[Period].[Period].MEMBERS},
		{[Period].[Period].[Prior Period]},
		{[Period].[Period].[Latest Period]},
		{[Period].[Period].[VAR]}
	}
	ON 1 
FROM 
	[Test]

[Wim Gielis]

Good information Declan.

In the first code snippet, I would calculate the variance based on the 2 variables above.
Less hardcoded elements, shorter code too.

Code: Select all

WITH 
	MEMBER 
		[Period].[Period].[Latest Period]
	AS 
		StrToMember ( "[Period].[Period].[" + [System Variables].([System Variables Lines].[Latest Actual Period], [Data Type].[String]) + "]" )
	MEMBER 
		[Period].[Period].[Prior Period]
	AS 
		StrToMember ( "[Period].[Period].[" + [System Variables].([System Variables Lines].[Previous Actual Period], [Data Type].[String]) + "]" )
	MEMBER 
		[Period].[Period].[VAR]
	AS 
		[Period].[Period].[Latest Period]
		- 
		[Period].[Period].[Prior Period]
SELECT 
	{[Data Type].[Data Type].MEMBERS} ON 0, 
	{
		{[Period].[Period].MEMBERS},
		{[Period].[Period].[Prior Period]},
		{[Period].[Period].[Latest Period]},
		{[Period].[Period].[VAR]}
	}
	ON 1 
FROM 
	[Test]

[declanr]

Good information Declan.

In the first code snippet, I would calculate the variance based on the 2 variables above.
Less hardcoded elements, shorter code too.

Yes, that's what the second code snippet was showing how to do.

However, it's only really needed because of the issues that Maren has encountered.
In reality (if not encountering any bugs/issues) you don't need the "Latest Period" or "Prior Period" members at all (since you already have "P09" and "P10" as real members that you can show and users *SHOULD* know they are current and prior) so you could just take the "VAR" member from the first snippet.

I use a lot of MDX views in my applications and with more simple calculations like this I have not noticed any real performance issues but I would normally be inclined to use a single member as opposed to multiple members (three in this case.)
That only really changes for me when the members start to get more complex at which point I find that having the members split up can help the code be more readable and easier to make changes to if you have to reference the same thing multiple times.

I should add that the second code snippet I had above contained SOLVE_ORDER; I wasn't really thinking at the time but that can be ignored completely - you only need to reference the SOLVE_ORDER when the members are in different hierarchies that may intersect with each other.

[MarenC]

Hi,

Thank you for the further updates.

Unfortunately, putting in those extra member statements didn't change the behaviour.

I haven't had enough time to test through all the possible MDX combinations but will do so and report back when I get some spare time. Not sure that will be this of xmas though!

Maren