Where clause in MDX statement

[Catherine]

Hi,

I would need your help in writing a "where" clause in a mdx statement.

Here is the context. I'm having a process reading data in another TM1 instance through an ODBO source.
I'm reading some infomation in a cube called ClientGroups, containing 3 dimensions: }Clients, }Groups and Group (this last one contains string elements).
The MDX query as it is now works, and is the following:
SELECT CROSSJOIN({[}Groups].Members}, {[}Clients].Members}) ON ROWS, {{[Group].Group},{[Group].UniqueID},{[Group].Password}} ON COLUMNS FROM [ClientGroups]

But my porcess becomes longer to run because number of groups is increasing. So I would like to restrict the query with a Where clause. But I don't know the syntax for my need.
In fact, I want to add something like
WHERE subst([Group].Group,3,3)='ABC'

Thank you

[rmackenzie]

Try this:

Code: Select all

SELECT 
  CROSSJOIN(
    {TM1FILTERBYPATTERN( {TM1SUBSETALL( [}Groups] )}, "ABC*")}, {[}Clients].Members}
  ) ON ROWS, 
  {{[Group].Group},{[Group].UniqueID},{[Group].Password}} ON COLUMNS 
FROM [ClientGroups]

[Catherine]

Thank you for your quick answer.

It does work and reduces significantly my process duration.
Nevetheless, I do not look for group beginning with ABC but having ABC from third to sixth position. that's why I put
SELECT
CROSSJOIN(
{TM1FILTERBYPATTERN( {TM1SUBSETALL( [}Groups] )}, "*ABC*")}, {[}Clients].Members}
) ON ROWS,
{{[Group].Group},{[Group].UniqueID},{[Group].Password}} ON COLUMNS
FROM [ClientGroups]
I just want to know if there is a way to really specify the position of the string I look for.

[Catherine]

Sorry I found the answer myself
SELECT
CROSSJOIN(
{TM1FILTERBYPATTERN( {TM1SUBSETALL( [}Groups] )}, "??ABC*")}, {[}Clients].Members}
) ON ROWS,
{{[Group].Group},{[Group].UniqueID},{[Group].Password}} ON COLUMNS
FROM [ClientGroups]

[Catherine]

Should have been until the end of my test before posting...

It appears that the syntax "??ABC*" is accepted when saving the process, and the process runs without errors. BUT it doesn't bring me back the required elements...
It doesn't bring back any elements in fact...

Any ideas?

[lotsaram]

Be wary of reading in passwords to synchronize between servers! The encrypted password string can (and often does) contain unprintable characters which are seen by TI as file terminators which will cause the TI to quit when a file containing such a character is encountered.

[David Usherwood]

@lotsa, Catherine's using ODBO, so there's no 'file' to find an EOF in. But I did see her post which said performance was poor, reading - maybe - a couple of hundred strings? _Why_ is ODBO so cr^p?

[lotsaram]

@lotsa, Catherine's using ODBO, so there's no 'file' to find an EOF in. But I did see her post which said performance was poor, reading - maybe - a couple of hundred strings? _Why_ is ODBO so cr^p?

I know there's no "file" but you get a mid data source quit if any string field which TI hasn't been set to ignore as a variable contains a file termination character regardless of whether the data source is flat file, cube view or ODBC (which I have all tried), so my assumption is that the same would be true for ODBO.

At least if it is a data source it just quits. If you have a string rule that looks at the password field in }ClientProperties (doing something basic like a substring or scan) and you refresh a view containing a measure using said rule and one of the clients in the view happens to have an encrypted password string that contains a termination character then you will be rewarded with a server crash (at least on all versions of 9.5.1 I have tested.) Curiously Long() won't cause a crash ...

[Catherine]

Be wary of reading in passwords to synchronize between servers! The encrypted password string can (and often does) contain unprintable characters which are seen by TI as file terminators which will cause the TI to quit when a file containing such a character is encountered.

Thank you !
I don't seem to have any problem here but it is not a file that TI reads but directly the cube through the mdx query.
However, I had first included the password when I built the process, hoping I would be able to use it but it didn't work... I quickly had to find another solution to manage it so I can remove it from the query now !

[lotsaram]

Be wary of reading in passwords to synchronize between servers! The encrypted password string can (and often does) contain unprintable characters which are seen by TI as file terminators which will cause the TI to quit when a file containing such a character is encountered.

As David pointed out this should have read "... cause the TI to quit when a record containing such a character is encountered." (irrespective of whether the data source is a flat file, view or whatever.)

[Catherine]

@lotsa, Catherine's using ODBO, so there's no 'file' to find an EOF in. But I did see her post which said performance was poor, reading - maybe - a couple of hundred strings? _Why_ is ODBO so cr^p?

ODBO is not that bad...
The process is reading 216 000 lines, and it reads them twice because I have some codes both in Metadata and Data. It takes about 30 seconds. 30 seconds is not that bad but if I restrict it following my needs, it takes 2 seconds.

[David Usherwood]

Indeed, that's not that bad.
We looked at ODBO some time ago for moving bulk data between servers, when a certain M Ryan was working for us. He reported that it didn't scale, hence my interest (but also my leap to a conclusion). Could be that with the work done for C10, it's been optimised. It needed to be.

[rmackenzie]

We looked at ODBO some time ago for moving bulk data between servers, when a certain M Ryan was working for us. He reported that it didn't scale, hence my interest (but also my leap to a conclusion).

My experience with ODBO was that scalability issues were secondary to the crashes caused when users were acting in the model whilst the query was running! With multi-instance environments becoming more common it would definitely be good see this functionality sorted out.