Joining two TM1 dimension in ETL process.

[AmeyJoshi]

Hi Experts,

I am new to TM1 mdx query,actually I am ETL developer. Currently we are planing to pull the data directly from TM1 into our ETL process.
For this is I am creating TM1 mdx query in our ETL process.
Below is the query which I have created:-

Code: Select all

Select {[Branch].members} on rows, {[Time].members} on columns from mycube

I am getting records for Branch column but the for Time column all the values are 0.
How can I join these two dimensions?

Appreciate all your help!!

Thanks!!

[Michel Zijlema]

Hi Experts,

I am new to TM1 mdx query,actually I am ETL developer. Currently we are planing to pull the data directly from TM1 into our ETL process.
For this is I am creating TM1 mdx query in our ETL process.
Below is the query which I have created:-

Code: Select all

Select {[Branch].members} on rows, {[Time].members} on columns from mycube

I am getting records for Branch column but the for Time column all the values are 0.
How can I join these two dimensions?

Appreciate all your help!!

Thanks!!

Hi,

My 1st thought here (although I'm not an expert in this area) is that you also need to select a measure to 'display' in your defined grid...

Michel

[David Usherwood]

I tested this (in 2005!) against TM1 and found it to work - so I don't think your code is far off. But I would tread cautiously - TM1's implementation of MDX is quite quirky and in some versions was known to be a memory hog. However Cognos BI talks to TM1 via MDX so at least the interface is getting some attention - though the code which BI generates makes me shudder....

Code: Select all

select {year.[2000]} on columns, 
non empty (account.members * company.members ) on rows
from pandlsum
where (scenario.budget)

Probably worth saying which ETL tool you are using.

[AmeyJoshi]

Thanks for the help!!

We are using ETL Tool-Datastage 8.7 to pull the records from TM1 through mdx query.
I have ran the query with 'non empty' command and it returned 0 records .... .... this means all the results are having some 0 value. When I removed the 'non empty' clause there are almost around 40K records all with duplicates and 0 value. I am looking two join two dimensions based on the matching record(equi join)...can we do this mdx query?

Appreciate all help!!!

Query:- some thing like this (this is sql query)...

Code: Select all

SELECT 
{[TIME].[2013]} on columns,
([Branch].[999999],[Company].[88888]) on rows
from mycube

Output something like this:-

Code: Select all

 
TIME	BRANCH	COMPANY
 2013	999999	88888

Can we get the above output from Mdx query ??
Thanks in Advance!!

[PavoGa]

Did you ever figure this out? I have a very similar problem in I'm trying to build a view (using MDX) in one cube based on the data in another.