MDX Query

[jimshen]

Hello,

Try to get data cross the tm1 client servers via ODBO by MDX, and had the problem.

When I use ON ROWS statement for leaf element, say, account, I would expect account elements appears in one column. However, It a bit anoying that all parents also appear in multiple columns. It does not present the table it should be if outside tm1 explorer.

My question is:
1. How can we get rid of those parents we do not need and restrict elements in one column?
2. How can we have mutiple dimension elements on rows?

The query I am using is as follows:

WITH
SET [ABG-Account Set] AS
'{TM1SORT({TM1FILTERBYLEVEL({TM1DRILLDOWNMEMBER({[PL_Account].[Account All-Attributable_profit]},ALL,RECURSIVE)},0)}, ASC)}'
SELECT
{[ABG-Account Set] } ON ROWS,
NON EMPTY { [Period].[Jul] , [Period].[Aug] } ON COLUMNS
FROM [GL_PL]
WHERE statement

This is only for test of Account, but I need more columns like Department.

Anybody could give me a clue?

Thanks in advance

Jim

[rmackenzie]

Try to get data cross the tm1 client servers via ODBO by MDX, and had the problem.

You wouldn't be the first person to have a problem with this. The TI interface for ODBO is, in my opinion, quite flakey. Also, my understanding is that no-one is in a rush to do anything about it.

1. How can we get rid of those parents we do not need and restrict elements in one column?

You're subset definition looks fine. I don't understand why the the fully qualified names are returned instead of just the name of the element in the subset. You might find that sometimes the row doesn't contain the fully qualified name - and that's where it starts to get flakey.

2. How can we have mutiple dimension elements on rows?

By using the CROSSJOIN function, or probably you should use NON EMPTY CROSSJOIN to implement zero suppression. But you are going to see the existing problem multiple times - in that all the dimensions on rows will have partial or fully-qualified names taking up lots of columns.

If you are trying to send data between servers, perhaps you can consider output and input via text files?

[jimshen]

Hi Mackenzie,

You are right that the qualified name can be in different colomn!

The reason to consider this approach is that I had only read permission from the source server. I am not sure if TI from target server could control the objects in source server.

I did an alternative way by slicing the view out and saved it as text file. However, as you can see, this process is not smonthly as one click one.

I got the info somewhere that TM1 does not support MDX view generating except subset. Does document mention this?

Thank you for your helpful reply.

Regards

Jim

[rmackenzie]

The reason to consider this approach is that I had only read permission from the source server. I am not sure if TI from target server could control the objects in source server.

You could look at replication to have data synchronised across two different servers, but this functionality is not always reliable. If you search for 'replication' on this forum you'll find lots of peoples experiences and some very useful information on this.

I did an alternative way by slicing the view out and saved it as text file. However, as you can see, this process is not smonthly as one click one.

Yes, migrating data in text files isn't as simple, but it is more reliable once you have got it up and running.

I got the info somewhere that TM1 does not support MDX view generating except subset. Does document mention this?

The documentation on MDX hasn't changed much for quite a long time and a number of releases (except I haven't investigated it that much for 10.1). MDX views do work, but there's not much official guidance on using this functionality.

Robin