Enable ViewZeroOut against asymmetric MDX views.

Suggest and discuss enhancements for TM1
Post Reply
User avatar
Steve Rowe
Site Admin
Posts: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Enable ViewZeroOut against asymmetric MDX views.

Post by 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
General Case VZO.png (3.71 KiB) Viewed 9958 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
Filter values.png (7.68 KiB) Viewed 9958 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
Post VZO.png (16.31 KiB) Viewed 9958 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 )
Technical Director
www.infocat.co.uk
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Enable ViewZeroOut against asymmetric MDX views.

Post by Wim Gielis »

Voted.
That should just work, end of story.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Mark RMBC
Community Contributor
Posts: 292
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: Enable ViewZeroOut against asymmetric MDX views.

Post by 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
User avatar
Steve Rowe
Site Admin
Posts: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: Enable ViewZeroOut against asymmetric MDX views.

Post by 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....
Technical Director
www.infocat.co.uk
User avatar
PavoGa
MVP
Posts: 616
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Charleston, Tennessee

Re: Enable ViewZeroOut against asymmetric MDX views.

Post by PavoGa »

Voted.
Ty
Cleveland, TN
Mark RMBC
Community Contributor
Posts: 292
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: Enable ViewZeroOut against asymmetric MDX views.

Post by 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
User avatar
Steve Rowe
Site Admin
Posts: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: Enable ViewZeroOut against asymmetric MDX views.

Post by 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,
Technical Director
www.infocat.co.uk
Mark RMBC
Community Contributor
Posts: 292
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: Enable ViewZeroOut against asymmetric MDX views.

Post by 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
Post Reply