TM1 MDX in TI Process

[dharav9]

Hi, All
I have placed following MDX in dimension and created subset. I did not find any issue.
When i place same MDX in TI process then receiving an error - dynamic subset cannot be created.
'{FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|DimEmp|'] )}, 0)},
(
(['|cubPlanEmp|'].(['|DimEmp|'].CurrentMember,['|DimMsr|'].[FTE]) = 0)
AND
(
(['|cubPlanEmp|'].(['|DimEmp|'].CurrentMember,['|DimMsr|'].[BonusRate]) > 0)
OR
(['|cubPlanEmp|'].(['|DimEmp|'].CurrentMember,['|DimMsr|'].[Action]) <>"")
)
)
)}';

Can anyone please throw lights on concept if missing?

Thank You
Dharav

[Wim Gielis]

Do not use CurrentMember. Make your expressions 'absolute' by specifying real element names and/or attributes and/or cube intersections, and so on. There is no CurrentMember inside a TI process.

[lotsaram]

Do not use CurrentMember. Make your expressions 'absolute' by specifying real element names and/or attributes and/or cube intersections, and so on. There is no CurrentMember inside a TI process.

I don't understand this advice at all. There's nothing wrong with using CurrentMember. In this context it is the right thing to do in the filter as you want to evaluate the filter relative to the context of each leaf from the first set.

[Wim Gielis]

Apologies. I must have mixed up the concepts CurrentMember and TM1SubsetBasis. Please disregard my post.

[Steve Rowe]

Most likely reason is that your MDX in the context it is being used at runtime is not returning any members. TM1 will not let you create an empty MDX subset.

Standard practice (mine anyway) is to add the first element from the dimension to the start of the MDX statement and then delete the first element from the subset once it is created. This has the advantage that you can create an empty subset and strip the MDX from the subset at the point of creation.

This suppresses the "null" error and may make your code run faster since the MDX is not being re-evaluated.

[lotsaram]

.... TM1 will not let you create an empty MDX subset.

Standard practice (mine anyway) is to add the first element from the dimension to the start of the MDX statement and then delete the first element from the subset once it is created. This has the advantage that you can create an empty subset and strip the MDX from the subset at the point of creation.

Actually it can let you save an empty set (or invalid MDX, which will of course result in an empty set). All you need to do is include an optional "dimension name" argument in the SubsetCreateByMDX function.
e.g.
SubsetCreatebyMDX( 'SubName', 'MDX_Expression', '[DimName]', [AsTemp] )
The last 2 arguments are optional
- DimName (string) blank unless supplied. If supplied then the subset will be created even if the set is empty
- AsTemp (Boolean 1/0). Default=0 if not supplied

[dharav9]

Thank You All for your time and input.

@lotsaram: Thank You for the following extension of the function. It works as expected.

SubsetCreatebyMDX( 'SubName', 'MDX_Expression', '[DimName]', [AsTemp] )
The last 2 arguments are optional
- DimName (string) blank unless supplied. If supplied then the subset will be created even if the set is empty
- AsTemp (Boolean 1/0). Default=0 if not supplied

Thank You
Dharav

[dharav9]

Hi, All

I just tried to export the data in asciioutput from the view created through MDX as we communicated. I am not able to export data either manually (right click on view and export - it generates unknown error) or through TI (it does not generate output. When i open the view then i do able to see the value as it supposed to be. I even tried to put viewconstruct in prolog before extracting data but no luck.

When we use MDX filter based on cube values, do we need to consider anything else before exporting it out.

Code:
IF (SubsetExists(DimEmp,sSub)=1);
SubsetDestroy(DimEmp,sSub);
ENDIF;
sMDX=
'{FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|DimEmp|'] )}, 0)},
(
(['|cubPlanEmp|'].(['|DimEmp|'].CurrentMember,['|DimMsr|'].[FTE]) = 0)
AND
(
(['|cubPlanEmp|'].(['|DimEmp|'].CurrentMember,['|DimMsr|'].[BonusRate]) > 0)
OR
(['|cubPlanEmp|'].(['|DimEmp|'].CurrentMember,['|DimMsr|'].[Action]) <>"")
)
)
)}';
SubsetCreateByMDX(sSub,sMDX,DimEmp,0);
# View Subset Assign
ViewSubsetAssign(cubPlanEmp, cubView, DimCat,sSub);
ViewSubsetAssign(cubPlanEmp, cubView, DimCo,sSub);
ViewSubsetAssign(cubPlanEmp, cubView, DimDept,sSub);
ViewSubsetAssign(cubPlanEmp, cubView, DimDiv,sSub);
ViewSubsetAssign(cubPlanEmp, cubView, DimEmp,sSub);

#====Above code successfully creates view as i needed.
DATASOURCECUBEVIEW = cubView;
Header=1;

sFile = 'G:\Test.csv';

#Data
IF (Header=1);
AsciiOutPut(sFile,'Category','Company','Department','Division','Employee','Measure','Value');
Header = 2;
ENDIF;

ASCIIOUTPUT(sFile, vCat,vCo, vDept,vDiv,vEmp,vMsr,vValue);

can you please share your insight on concept if i am missing something?

Thank You
Dharav

[Steve Rowe]

It is probably your view definitions, do you have the view set to exclude consolidations and or ruled values? If you have and the source contains data points of this type then they won't be excluded in the export.

These are on by default.

See below for the usual culprit

https://www.ibm.com/support/knowledgece ... csset.html

[Reddington]

Can they easily be turned off by mistake?

[dharav9]

Hi, Steve & Reddington

1) My view contains data at leaf levels only and having non-rule derived values
2) Before i put the post, i already tried consolidation function with "0" flag (just to make sure i am not missing any basic)

It seems the MDX driven subset which process the dimension elements based on the cube value associated with each combination of other dimension elements causing this issue. When i stack all dimension on rows except measure dimension on columns, my view generated null values. When i put MDX driven subset on rows, measure dim on column and other dimension on context then value process and i see expected values.

I opened PMR with IBM. If i hear from them then i will update it here.

@Reddington: On a lighter note, are you a Raymond Reddington? - I love this name as i am (BlackList) fan.

Thank You
Dharav

[Mark RMBC]

Hi,

So you have the dimensions category, company, department, division, employee and measure in your cube
and your MDX refers to only the employee and measures dimension? Also your Action measure is a string.

I am surprised the MDX returns the correct values under those circumstances.

regards,
Mark

[dharav9]

Hi, Mark

So you have the dimensions category, company, department, division, employee and measure in your cube
and your MDX refers to only the employee and measures dimension? Also your Action measure is a string.

I am surprised the MDX returns the correct values under those circumstances.

Yes, those are dimensions. only Employee Dimension subset is based on MDX. All elements of Measure dimension are part of the view and all of these elements are leaf level elements. Yes, Action measure is string.

Thank You
Dharav

[PavoGa]

I think I know what your problem is.

The clue about the problem is that it only returns values when the other dimensions, not referenced in the dimEmp MDX, are placed as context items in the view, but when placed as row dimensions produce no results.

When those other dimensions are placed as context items and you choose elements which have values, the MDX in dimEmp automatically uses those as the "currentmember" context for those dimensions with regards to the query.

When the other dimensions are placed in the rows, then the dimEmp MDX is using the default member of the dimension as context for the MDX query. This is what is happening inside the TI. You can test this by using {[dimname].currentmember} in each of the other dimensions in the subset editor to see what element is the default.

You have a couple of ways of handling this. One is to use GENERATE to provide context for the dimEmp MDX query for each of the other dimensions. Think of this as looping through each leaf element of the other dimensions. Depending on the size of the other dimensions, this could be VERY slow.

Another way is to select a single measure that is always populated for the intersections you want to evaluate. Set the view up accordingly with all leaf members of dimEmp. Use CELLGETN or CELLGETS as appropriate for the other measures in the DATA procedure and do your conditional there with ITEMSKIP. This will probably be the quickest.

Another alternative is a mix of GENERATE in the MDX and the second method. I would have to know your data better in order to give a concise recommendation.

And two last tips. One, if the MDX function returns a set, you do not have to use {} which eliminates clutter. Two, if you use the EXPAND function, your MDX gets a bit cleaner to read:

Code: Select all

sMDX= EXPAND(
     'FILTER(TM1FILTERBYLEVEL( TM1SUBSETALL( [%DimEmp%] ), 0),
          ([%cubPlanEmp%].([%DimMsr%].[FTE]) = 0)
          AND
          (
          ([%cubPlanEmp%].([%DimMsr%].[BonusRate]) > 0)
               OR
          ([%cubPlanEmp%].([%DimMsr%].[Action]) <>"")
          )
     )');

With regards to returning an empty subset with MDX, I use a variation of what Steve does.

Code: Select all

sMDX = 'UNION( {[dimname].currentmember},
	MDXBLAH,
	ALL)';
SubsetCreateByMDX(subname, sMDX, 1);
SubsetElementDelete(dimName, subName, 1);

This ensures the subset MDX will work 100% of the time and the deletion automatically converts to a static subset which will perform better.

[Mark RMBC]

Hi,

I managed to get this work in a fashion, i.e. to asciioutput where the FTE = 0 and the Bonus Rate > 0.

The problem comes with the string element, i.e. Action element. Under no circumstances could I get it to ascii where FTE = 0 and Action <>""

Incidentally I found the following to be more robust than ([%cubPlanEmp%].([%DimMsr%].[Action]) <>""):

Code: Select all

(NOT(ISEMPTY([%cubPlanEmp%].([%DimMsr%].[Action])))

So I would say if you do have these string measures create an accompanying numeric measure and use the numeric rather than the string.

Though rather than adding the extra measure you could ditch the mdx current member approach and use if statements in your TI to ascii the appropriate data!

regards,
Mark