Extraction of TM1 DATA TO SQL DW

Post Reply
skumar888
Posts: 1
Joined: Thu Jul 28, 2022 11:44 am
OLAP Product: TM1
Version: TM1 10.1
Excel Version: office 365

Extraction of TM1 DATA TO SQL DW

Post by skumar888 »

Team,
I am new to TM1, we are trying to extract some important finance KPI'S info to SQL DW, from DW point of view i am ok,
since i don't have any exposure in TM1 i am feeling difficulty in preparing documenattion
Ex: I need to extract gross margin KPI numbers, below report(income statement) is developed in excel ad in
and my requirement is to extract Gross margin (277603) to DW. Last line
This is very basic question, since i don't have exposure, kindly help me by providing the steps.



May_YTD Actuals
Upstream Downstream TOTAL Description

Total Upstream Production Volumes
281,437 - 281,437 Export Gas (MMCF)
274,319,474 - 274,319,474 Export Gas (MMBTU)
26,633 - 26,633 Liquids (MBOE)
73,539 - 73,539 TOTAL (MBOE)

Share of Upstream Sales
Sales Volumes
63,054 - 63,054 Gas (MMCF)
61,459,860 - 61,459,860 Gas (MMBTU)
5,882 - 5,882 Liquids (MBOE)
16,391 - 16,391 TOTAL (MBOE)

Downstream Sales Volume Statistics
- 274,689,355 274,689,355 Produced N.Field Gas (MMBTU)
- - - (MMBTU)
- 274,689,355 274,689,355 TOTAL (MMBTU)

Revenues
83,155 - 83,155 Export Gas
19 - 19 Re-injected Ethane
- - (83,174) Elimination on Consolidation
- 665,660 665,660 Sale of Gas
- - - Sale
387,528 - 387,528 Liquids
470,702 665,660 1,053,187 Total Revenues
- 371,655 371,655 Gas Purchases
- (740) (740) Delivery Failure - Gas Supply
- 637 637 Buy Back Tarrif
- - - Service charges on gas storage
- - (83,174) Elimination on Consolidation
- 16,506 16,506 Lease Rent
470,702 277,603 748,305 Gross Margin
ascheevel
Community Contributor
Posts: 216
Joined: Fri Feb 15, 2013 5:49 pm
OLAP Product: TM1
Version: PA 2.0.9.1
Excel Version: 365
Location: Minneapolis, USA

Re: Extraction of TM1 DATA TO SQL DW

Post by ascheevel »

You can update an ODBC datasource from TM1.
Niko
Posts: 28
Joined: Tue Feb 15, 2022 6:43 am
OLAP Product: IBM Cognos TM1
Version: PA 2.09 / TM1 11.7.00000.42
Excel Version: EXCEL 2019
Location: Asia

Re: Extraction of TM1 DATA TO SQL DW

Post by Niko »

You can use odbc as ti datasource. In prolog first use odbcopen function to open odbc connection,then in the data use odbcoutput function to insert data to your dw. Finally use odbcclose in the epilog to close the connection. Most important is you should install odbc driver in the tm1 server before coding ti.
User avatar
orlando
Community Contributor
Posts: 163
Joined: Fri Aug 04, 2017 8:27 am
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365

Re: Extraction of TM1 DATA TO SQL DW

Post by orlando »

skumar888 wrote: Fri Jul 29, 2022 10:26 am Team,
I am new to TM1, we are trying to extract some important finance KPI'S info to SQL DW, from DW point of view i am ok,
since i don't have any exposure in TM1 i am feeling difficulty in preparing documenattion
Hello,

the best way would of course be to get a TM1 expert to explain to you how to do it with your database. With the help of experts (consultants, freelancers, etc.) your company has built up the TM1 database - I just assume.

If you want to fight your way through it yourself, this tutorial might help:

https://www.mssqltips.com/sqlservertip/ ... -tm1-cube/

Many greetings
orlando
Post Reply