Enable ViewZeroOut against asymmetric MDX views.
Posted: Wed May 10, 2023 1:14 pm
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.
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.
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.
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 )
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.
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.
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.
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 )