MDX code for a Dimension - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Analysis Services (2000)

MDX code for a Dimension

Author

Topic

davidagnew37
Starting Member

33 Posts

Posted - 2004-08-12 : 11:06:33

Is it possible to write MDX code for a Dimension (in the "All member formula"??) which will specify that only certain measures are affected by that dimension - say measures A,B and not C for example.

This is becuase I have 3 measures "Actual", "Budget", "Full Year Budget" and I have a dimension called "Time" - which I only want to effect measures "Actual", "Budget" AND NOT "Full Year Budget".

griva
Starting Member

7 Posts

Posted - 2004-08-12 : 14:09:45

What do you want to happen to the "Full Year Budget" value?

davidagnew37
Starting Member

33 Posts

Posted - 2004-08-12 : 15:43:16

I don’t want the "Full Year Budget" value/measure to be filtered by one of my dimensions.

I have three measures in a virtual cube:
"Actual", "Budget" and "Full Year Budget".

The dimensions I have are:
- Account No_ / Name
- Cost Code
- Sub Cost Code
- Time/Dates
- Budget Name

Both "Actual" & "Budget" measures need to be filtered/dimensioned by:
- Account No_ / Name
- Cost Code
- Sub Cost Code
- Time/Dates (exclusive to "Actual", "Budget")

Thus have put these in one cube

AND "Full Year Budget" needs to be filtered/dimensioned by:
- Account No_ / Name
- Cost Code
- Sub Cost Code
- Budget Name (exclusive to "Full Year Budget")

THUS have put this as one cube…

I then created a virtual cube, with the 2 cubes thinking that the dimensions I created with those cubes would only filter the measures of the respective cube measures in the virtual cube. – Is this what a virtual cube is suppose to do?

The problem is when I create the virtual Cube, the Dimensions filter ALL measures. I therefore thought that MDX coding might help sort this out?

griva
Starting Member

7 Posts

Posted - 2004-08-13 : 11:27:35

If you don't want a measure to be 'touched' by a dimension, use ignore function.

For ex, the expression
([D_SBU].Currentmember,[D_CORP_RETAIL].Ignore,[D_EQP_FAM].Ignore,[D_EVENT].Ignore,[Measures].[Reserve Exposure])
will give you the Reserve Exposure measure on the SBU (whatever it is) level, and all other dimesions will be ignored.

Hyukevain
Yak Posting Veteran

66 Posts

Posted - 2004-08-16 : 00:27:31

Use the unary function in your dimension, you can spesify which one gonna be added, which one gonna multiply and so on. Hope this can help.

davidagnew37
Starting Member

33 Posts

Posted - 2004-08-16 : 04:52:19

Hi Griva,

Thanks for your help.... As you can tell I am new to cubes and MDX.

I presume you include this expression in the Dimensions properties, in the "All member formula" property box for that dimension?

Is this what you meant by your syntax. if not please could you explain the syntax a little.

([DIMENSION_A].Currentmember,[MEASURE_A].Ignore,[MEASURE_B].Ignore,[MEASURE_C].Ignore,[Measures].[Reserve Exposure])

Thanks. your help and patience(!) is appreciated.

david

quote:
Originally posted by griva

If you don't want a measure to be 'touched' by a dimension, use ignore function.

For ex, the expression
([D_SBU].Currentmember,[D_CORP_RETAIL].Ignore,[D_EQP_FAM].Ignore,[D_EVENT].Ignore,[Measures].[Reserve Exposure])
will give you the Reserve Exposure measure on the SBU (whatever it is) level, and all other dimesions will be ignored.

davidagnew37
Starting Member

33 Posts

Posted - 2004-08-16 : 16:27:09

hi griva,

...This does the trick for me.....

"....if you want your virtual
cube measures to ignore the dimensions that don't apply to them, create a
calculated member for each one with the ValidMeasure operator..

ValidMeasure([Measure].[Budget])
ValidMeasure([Measure].[Actuals])

etc

Make the original measures non visible. When you use a measure against a
dimension it doesn't recognise, it will defaultto the "all" level."

If I was a bit more technical I think your way would allow me to do what I wanted in one cube... instead of two cubes and then a virtual cube. I will give it a go soon.

thanks a lot for your help. Its really helpful getting good replies.

david

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources