mdx help in TI

[subha.cognos]

Hi All:

Could you please do needful if it is possible solution or not?

A Cube: below dimensions
BU
Index summary
dpt
PY
scenario
version
measures (these five elements are expenses,resource,band,key goals,layer with picklist. for these measures users are mapping through template. these five elements mapping will be individual dimension in the Target B cube)


B Cube: below dimensions
BU
version
Index summary
dpt
PY
scenario
expenses
resource
band
mgt goals
layers
measures

I have written a TI process to load the data from A cube B Cube. first i will be zeroing out based the parameters in the target cube (year, dpt, version, index summary for that particular dept) and then i will be loading the data into target view. which is working fine.

but my new requirement is to zeroout the target cube view except for Crisis (layer dimension) and demand ( index summary dimension) combination. could you please do needful of writing the mdx that i can use in the TI process?
i hope it is possible?

[subha.cognos]

In tm1, is this possible to write a mdx subset using where clause between two different dimensions?

[Wim Gielis]

Hello,

This will not be very easy to do it in 1 step.

One approach could be to select all level 0 cells (given the parameter values) in the data source through the Prolog tab.
Then in the Data tab, write IF conditions and Itemskip to exclude the selected slice that you want to preserve.
Below the Itemskip do a simple CellPutN of 0 to the cell that you are treating.
With transaction logging turned off during the process this will still be very quick.

Alternatively, and I doubt whether your server version has access to it, but try ViewCreateByMDX.
The syntax will not be so easy and I haven’t done this particular exercise myself but it might be possible.

You could also associate the slice of data to be preserved with a different measure. Then you can just zero out all data except that measure.

Easiest way is to do it in 2 steps. 2 zero outs after each other, carefully making sure to have the right selections.
Zero out 1 has all selections but in the first restricted dimension, omit the elements to be left untouched.
Zero out 2 has all selections but in the second restricted dimension, omit the elements to be left untouched.
Idea submitted by George Tonkin.