Enable ViewZeroOut against asymmetric MDX views.

[Steve Rowe]

Hi

RFE is here. https://ibm-data-and-ai.ideas.ibm.com/ideas/PAOC-I-1178

Use case as follows.

In the general case the target area of a VZO and data load looks like this, with the red areas excluded from the ViewZeroOut.

General Case VZO.png (3.71 KiB) Viewed 31781 times

Using a legacy view we have to VZO each "cell" one at a time to deliver the above, this is much slower than VZOing the whole space. (or figure out the minimum efficient symmetric views to deliver the above, hard...)

We can however create an MDX view that matches exactly the blue space above

VZO is available against MDX views as stated here.
https://www.ibm.com/docs/en/planning-an ... -mdx-views

However it does not work as expected, the filter is not applied correctly.

Given this MDX view.

SELECT NON EMPTY {{[M SalesCube2].[M SalesCube2].[Value]}} ON 0,
Filter( NONEMPTY ( {TM1FILTERBYLEVEL(TM1SubsetAll([actvsbud].[actvsbud]), 0)} *
{TM1FILTERBYLEVEL(TM1SubsetAll([region].[region]), 0)} *
{TM1FILTERBYLEVEL(TM1SubsetAll([model].[model]), 0)} *
{TM1FILTERBYLEVEL(TM1SubsetAll([account1].[account1]), 0)} *
{TM1FILTERBYLEVEL(TM1SubsetAll([month].[month]), 0)} )
,[M SalesCube2].[M SalesCube2].[ToKeep]=0)
ON 1
FROM [SalesCube2]

and these filter values, where 1 is a flag indicating the data should be retained.

Filter values.png (7.68 KiB) Viewed 31781 times

Post VZO we have the result below. From an initially fully populated view we should have data where the cells are 1 in the view above. However we only get the data retained where both cells are flagged.

Post VZO.png (16.31 KiB) Viewed 31781 times

This has been raised and rejected as a defect, since the function is performing as designed even though it clearly does not do what it says on the tin. (TS011806219 / PH52896 )

[Wim Gielis]

Voted.
That should just work, end of story.

[Mark RMBC]

Hi,

I raised an issue view ViewCreateByMDX and ViewZeroOut with IBM some time ago.

After a long and protracted period of time, this was their ultimate response:

After discussing this with my internal team I would like to provide you an explanation that this works by design.
Due to the current way that ViewZeroOut works, it cannot deal with that single MDX with asymmetric cross join.

To detail, ViewZeroOut is designed to clear areas defined by a crossjoin.
While MDX permits us to create sets that are subsets of crossjoins, the VZO action will still make a crossjoin from the axis selections you define in the MDX.
The effect is that in the test cases, we used for this replication, more cells are cleared than are described by the MDX set.

This is the current design, to do what you are looking for please avoid using nested crossjoin, but use separate ones.

Will vote for the RFE

regards,

Mark

[Steve Rowe]

Yes, I had the same experience, it's frustrating to hit this in the support process.....

Something that's not clear in the explanation I had or your one, is if it is actually possible to refactor the MDX such that what we want to do is possible? I think not but would be happy to be wrong....

[PavoGa]

Voted.

[Mark RMBC]

Hi Steve,

Sorry missed your reply from the 30th May:

Something that's not clear in the explanation I had or your one, is if it is actually possible to refactor the MDX such that what we want to do is possible? I think not but would be happy to be wrong....

Well, I would say yes and no.

No in that you don't appear to be able to do it with a single MDX statement but yes if you can find a way to derive multiple MDX statements (via maybe loops, MDX stored in a cube?), each then using a ViewZeroOut.

IBM gave an example of the way it could be done:

Code: Select all

sViewName = 'testview1';

sMDX =

'SELECT {[JobType].[JobType].[Sales]} ON 0,

{CROSSJOIN({[Account].[Account].[4999]} , {[Rate Measure].[Rate Measure].[Wire]}) ON 1

FROM [zTestMDX] WHERE ([Year].[Year].[2019])';

ViewCreateByMDX( sCube, sViewName, sMDX, 1 );

ViewZeroOut( sCube, sViewName );

sViewName = 'testview2';

sMDX =

'SELECT {[JobType].[JobType].[Sales]} ON 0,

{CROSSJOIN({[Account].[Account].[5999]} ,

           {[Rate Measure].[Rate Measure].[Circuit board], [Rate Measure].[Rate Measure].[Diodes]}) ON 1

FROM [zTestMDX] WHERE ([Year].[Year].[2019])';

ViewCreateByMDX( sCube, sViewName, sMDX, 1 );

ViewZeroOut( sCube, sViewName );

regards,

Mark

[Steve Rowe]

Hi Mark,

The example shown from IBM is super simple though, resulting in the exclusion of an entire slice from the cube. I don't need a MDX view to do this, I can just put 2019 in a set. I wouldn't really consider this as part of the use case.

This use case where the conditionality for the clear is multi-dimensional, could be represented by multiple "simple" views but devising a general case algorithm to convert one view with a conditional multi-dimensionsl clear clause into the minimum number of simple views, is beyond me. Especially at CoB on a Friday!

The good news though is that I have just been notified that my idea has been flagged for future consideration. (Hopfully not PA Engine only though, if anyones looking!)
Cheers,

[Mark RMBC]

Hi Steve,

Oh agreed, the IBM solution is so simple as to be useless but it does give the starting point to try and come up with some clever looping/cube solution.

Obviously your RFE is the not only desirable but pretty essential given how Planning Analytics has developed over the last few years.

regards,

Mark