TM1 Forum

Posted: **Mon Sep 26, 2011 11:34 am**

After playing around in report studio, and a lot of random clicking, I managed to union 2 TM1 cubes in a query following an interesting guide from IBM:
http://www.ibm.com/developerworks/data/ ... ge495.html

I was interested to find that the query generated by Report Studio was in 'Cognos SQL', even though the data sources are both TM1 cubes.

Code: Select all

with 
Union17(Version,
                Month2,
                Year3,
                Fund,
                Amount,
                Measure_Flag) as 
    ((select 
           Expenses.Version  as  Version,
           Expenses."Month"  as  Month2,
           Expenses."Year"  as  Year3,
           Expenses.Fund  as  Fund,
           Expenses.Amount  as  Amount,
           Expenses."Measure Flag"  as  Measure_Flag
     from 
           TABLE(_ROWSET("Expenses")) Expenses
    )
     UNION 
    (select 
           Revenue.Version  as  Version,
           Revenue."Month"  as  Month2,
           Revenue."Year"  as  Year3,
           Revenue.Fund  as  Fund,
           Revenue.Amount  as  Amount,
           Revenue."Measure Flag"  as  Measure_Flag
     from 
           TABLE(_ROWSET("Revenue")) Revenue
    )
    )
select 
       Union17.Year3  as  levelkey,
       XSUM(case when Union17.Measure_Flag = 'Net Revenue' then Union17.Amount
end  for Union17.Year3 )  as  Net_Revenue,
       XSUM(case when Union17.Measure_Flag = 'Total Costs' then Union17.Amount
end  for Union17.Year3 )  as  Total_Costs
 from 
       Union17
 group by 
       Union17.Year3

Does this mean that on some level it is possible to query TM1 cubes with SQL?

TM1 Forum

Union of cubes Querying TM1 Cubes using SQL in report studio

Union of cubes Querying TM1 Cubes using SQL in report studio