Page 1 of 1
Joining two TM1 dimension in ETL process.
Posted: Wed Mar 05, 2014 10:06 pm
by 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!!
Re: Joining two TM1 dimension in ETL process.
Posted: Thu Mar 06, 2014 9:20 am
by Michel Zijlema
AmeyJoshi wrote: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
Re: Joining two TM1 dimension in ETL process.
Posted: Thu Mar 06, 2014 11:06 am
by 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.
Re: Joining two TM1 dimension in ETL process.
Posted: Thu Mar 06, 2014 10:26 pm
by 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!!
Re: Joining two TM1 dimension in ETL process.
Posted: Tue Sep 29, 2015 1:57 pm
by 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.