TM1 MDX in TI Process

Post Reply
dharav9
Posts: 45
Joined: Wed Aug 15, 2018 3:18 pm
OLAP Product: TM1
Version: 10.3
Excel Version: 2016

TM1 MDX in TI Process

Post by dharav9 » Tue Jan 07, 2020 8:15 pm

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
MVP
Posts: 2310
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: TM1 MDX in TI Process

Post by Wim Gielis » Tue Jan 07, 2020 8:41 pm

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.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 model with AutoHotKey as a service or application

lotsaram
MVP
Posts: 3340
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: TM1 MDX in TI Process

Post by lotsaram » Wed Jan 08, 2020 6:39 am

Wim Gielis wrote:
Tue Jan 07, 2020 8:41 pm
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.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

Wim Gielis
MVP
Posts: 2310
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: TM1 MDX in TI Process

Post by Wim Gielis » Wed Jan 08, 2020 7:27 am

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

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 model with AutoHotKey as a service or application

User avatar
Steve Rowe
Site Admin
Posts: 1975
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: TM1 MDX in TI Process

Post by Steve Rowe » Wed Jan 08, 2020 10:15 am

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
MVP
Posts: 3340
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: TM1 MDX in TI Process

Post by lotsaram » Wed Jan 08, 2020 10:39 am

Steve Rowe wrote:
Wed Jan 08, 2020 10:15 am
.... 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
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

dharav9
Posts: 45
Joined: Wed Aug 15, 2018 3:18 pm
OLAP Product: TM1
Version: 10.3
Excel Version: 2016

Re: TM1 MDX in TI Process

Post by dharav9 » Wed Jan 08, 2020 4:20 pm

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
Posts: 45
Joined: Wed Aug 15, 2018 3:18 pm
OLAP Product: TM1
Version: 10.3
Excel Version: 2016

Re: TM1 MDX in TI Process

Post by dharav9 » Wed Jan 08, 2020 6:41 pm

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

User avatar
Steve Rowe
Site Admin
Posts: 1975
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: TM1 MDX in TI Process

Post by Steve Rowe » Wed Jan 08, 2020 9:36 pm

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
Posts: 1
Joined: Fri Oct 04, 2019 9:21 am
OLAP Product: Jedox
Version: latest
Excel Version: 2013

Re: TM1 MDX in TI Process

Post by Reddington » Thu Jan 09, 2020 11:51 am

Can they easily be turned off by mistake?

dharav9
Posts: 45
Joined: Wed Aug 15, 2018 3:18 pm
OLAP Product: TM1
Version: 10.3
Excel Version: 2016

Re: TM1 MDX in TI Process

Post by dharav9 » Thu Jan 09, 2020 3:23 pm

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
Regular Participant
Posts: 209
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: TM1 MDX in TI Process

Post by Mark RMBC » Thu Jan 09, 2020 4:45 pm

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
Posts: 45
Joined: Wed Aug 15, 2018 3:18 pm
OLAP Product: TM1
Version: 10.3
Excel Version: 2016

Re: TM1 MDX in TI Process

Post by dharav9 » Thu Jan 09, 2020 6:38 pm

Hi, Mark
Mark RMBC wrote:
Thu Jan 09, 2020 4:45 pm

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

User avatar
PavoGa
Community Contributor
Posts: 395
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.05
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: TM1 MDX in TI Process

Post by PavoGa » Sun Jan 12, 2020 3:28 pm

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.
Ty
Cleveland, TN

Mark RMBC
Regular Participant
Posts: 209
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: TM1 MDX in TI Process

Post by Mark RMBC » Mon Jan 13, 2020 9:22 am

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

Post Reply