Exporting Cube data to Sql Server Table

Post Reply
cdhodge2002
Posts: 90
Joined: Tue Aug 19, 2008 4:56 pm

Exporting Cube data to Sql Server Table

Post by cdhodge2002 »

Has anybody ever done this?

My current formula is this, I have put it in the Data tab in my TI process.
ODBCOutput('TM1 Output',expand('insert into expensesexport ([versions],[subsidiaries],[expenses],[expensemeasures],[MTH],[VAL]) values ('%v1%','%v2%','%v3%','%v4%','%v5%',%v6%)'));

I have the ODBCOpen formula in my prolog tab and the ODBCClose in my epilog. I have checked to make sure that the log in that I created works as well. The error that I get say that the sql statement is incorrect, but the statement does recognize the values in the TM1 cube. Does anybody have any ideas as to what might be wrong with my query?
User avatar
Steve Rowe
Site Admin
Posts: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: Exporting Cube data to Sql Server Table

Post by Steve Rowe »

Not sure, your best bet is to ASCIIOutput your expand statement and see if that makes sense to a proper SQL client.
Cheers
Technical Director
www.infocat.co.uk
cdhodge2002
Posts: 90
Joined: Tue Aug 19, 2008 4:56 pm

Re: Exporting Cube data to Sql Server Table

Post by cdhodge2002 »

Already did that piece. still have some issues.
cdhodge2002
Posts: 90
Joined: Tue Aug 19, 2008 4:56 pm

Re: Exporting Cube data to Sql Server Table

Post by cdhodge2002 »

Is anything supposed to occur after the ODBCOpen query is run?
David Usherwood
Site Admin
Posts: 1453
Joined: Wed May 28, 2008 9:09 am

Re: Exporting Cube data to Sql Server Table

Post by David Usherwood »

I think you've relied on the syntax in the help file - which is not always a good thing. If you are inserting string values, you need to quote them - whoever wrote the helpfile didn't know that. So I second Steve's suggestion - the sql statement written to a flat file should look like

"
insert into expensesexport (
versions,
subsidiaries,
expenses,
expensemeasures,
MTH,
VAL
)
values (
'Actual',
'Sub1',
'Salaries',
'Current',
'Mar',
12345.67
)
"
(Note - SQL is OK with linebreaks and the output can be easier to diagnose)

I personally don't bother with expand as it seems to me that it doesn't do anything you can't do by building the string yourself with | .

It's a tricky area, joining two environments together - neither will help you with the other. Good luck.
RJ!
Community Contributor
Posts: 219
Joined: Mon Jul 23, 2012 8:31 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010

Re: Exporting Cube data to Sql Server Table

Post by RJ! »

I've got a similar issue, I'm trying to send the data from a text file to an SQL database.

Our current set up is using MS Access to import the text file and then "append" the new transaction data into our SQL database, we want to automate this via a TM1 process.
I seem to be able to generate the exact same SQL statement from Tm1 that I use in my Visual Studio but I get this error:
12004 ERROR 2012-09-10 00:59:16.543 TM1.Process Process "123 JC Trans Daily 3": : Could not initialize process

This is my code:

Code: Select all

#========= P R O L O G =========


# Open the ODBC Connection
ODBCOpen('TM1DBP_UAT', '', '');

#Define Variables
MyCube = 'z_ctrl_measures';

Period = CellGetS (MyCube , 'Current Trans Daily Date', 'String 1');


#=============== D A T A ===============


# Use ODBC link to Insert Trans_Temp table to the Transactions_Details table.
#Syntax:
# ODBCOutPut( 'ODBC Connection Name', Expand( 'INSERT INTO TableName ( Coulmn1,Coulmn2,Coulmn3,Coulmn4,Coulmn5, Coulmn6 )' | '
# VALUES (''%v1%'',''%v2%'',''%v3%'',''%v4%'',''%v5%'',''%v6%'',%v7%)'));



sqlStmt = Expand( 'INSERT INTO TRANSACTION_DETAIL (COY_NO , GL_ACCOUNT , RC, TRANS_DATE ,
TRANS_CODE , DESCRIPTION , TRANS_SOURCE , TRANS_REF , AMOUNT , LV , TRANS_DATE_EFF , SEQUENCE_NO ,
BATCH_NO , TM1_MONTH , FDL_B , TM1_Daily , TYPE , MAPA , MAPB , MAPC , MAPD , PRODPROC ,
AMOUNT_AVG  ) ' | ' VALUES (''%COY_NO%'' , ''%GL_ACCOUNT%'' , ''%RC%'', ''%TRANS_DATE%'' , ''%TRANS_CODE%'' , ''%DESCRIPTION%'' ,
''%TRANS_SOURCE%'' , ''%TRANS_REF%'' , %AMOUNT% , ''%LV%'' , ''%TRANS_DATE_EFF%'' , ''%SEQUENCE_NO%'' , ''%BATCH_NO%'' ,
''%TM1_MONTH%'' , ''%FDL_B%'' , ''%TM1_Daily%'' , ''%TYPE%'' , ''%MAPA%'' , ''%MAPB%'' , ''%MAPC%'' , ''%MAPD%'' , ''%PRODPROC%'' ,
%AMOUNT_AVG%  )') ;

# Remove the Quote Characters from the SQL
DatasourceASCIIQuoteCharacter='';

Asciioutput ( '\\aussydtm002\tm1\temp\sql Daily 3.txt', sqlStmt );

ODBCOutput('TM1DBP_UAT', sqlStmt ) ;

#========= E P I L O G =========

# Execute process for check to see if Balances send in the SQL query match to the balances in z_ctrl_measures cube
ExecuteProcess('123 JC Trans Daily 3a', 'cubename', 'z_ctrl_measures',
'z_ctrl_measures' , 'Current Trans Daily Date', 'z_string' , 'TM1_Daily' );

# Close the ODBC link to the SQL DB
ODBCClose('TM1DBP_UAT') ;
The result of the asciioutput is:
INSERT INTO TRANSACTION_DETAIL (COY_NO , GL_ACCOUNT , RC, TRANS_DATE ,TRANS_CODE , DESCRIPTION , TRANS_SOURCE , TRANS_REF , AMOUNT , LV , TRANS_DATE_EFF , SEQUENCE_NO ,BATCH_NO , TM1_MONTH , FDL_B , TM1_Daily , TYPE , MAPA , MAPB , MAPC , MAPD , PRODPROC ,AMOUNT_AVG ) VALUES ('2400' , '0222513000' , '1180', '120906' , '61' , 'TAX' ,'PT' , '00426664' , 116.180 , '52' , '120906' , '00001' , '9743' ,'Sep 2012' , '' , '06 Sep 2012' , 'FMS' , '' , '' , '' , '' , '' , 0.000 )
I have requested our Tech team to confirm that our TM1 Admin account has access to TM1DBP_UAT and they say it does (though not exactly sure on how to prove this).

Anyone have any suggestions?
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Exporting Cube data to Sql Server Table

Post by tomok »

RJ! wrote:Our current set up is using MS Access to import the text file and then "append" the new transaction data into our SQL database, we want to automate this via a TM1 process.
This isn't 64-bit TM1 by chance is it? There is no 64-bit driver for Microsoft Access so you can't use it as a database when using the 64-bit version of TM1.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
RJ!
Community Contributor
Posts: 219
Joined: Mon Jul 23, 2012 8:31 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010

Re: Exporting Cube data to Sql Server Table

Post by RJ! »

Hi Tomok,

Just to confirm, we are not using MS Access as the "database", it is merely used to execute the code to append our transactions from the text file into our main SQL Database tables on a SQL Server 2008.

We are trying to eliminate that MS Access step and have a TM1 process execute the SQL to append the transaction file to the table in SQL Server, but get the error above :?
RJ!
Community Contributor
Posts: 219
Joined: Mon Jul 23, 2012 8:31 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010

Re: Exporting Cube data to Sql Server Table

Post by RJ! »

Just another note, I have run the Process without the code to do the SQL update - ODBCOutput('TM1DBP_UAT', sqlStmt ) ;

I still get the same result... "Could not initialize process"

And just to confirm that I have access to the ODBC source, I can retrieve the transactions from our Drill process :?
Duncan P
MVP
Posts: 600
Joined: Wed Aug 17, 2011 1:19 pm
OLAP Product: TM1
Version: 9.5.2 10.1 10.2
Excel Version: 2003 2007
Location: York, UK

Re: Exporting Cube data to Sql Server Table

Post by Duncan P »

Can you check that the parameter names for the process that you are calling through ExecuteProcess are spelt correctly, and also the process name itself.
jorelb
Posts: 71
Joined: Fri Feb 13, 2009 1:41 am
OLAP Product: IBM Planning Analytics Cloud
Version: 2.0.9 IF (2)
Excel Version: 2016

Re: Exporting Cube data to Sql Server Table

Post by jorelb »

Try exporting the old values using the formula below. SQL is looking for the ‘ ‘.

New Value = ''''| Old Value|'''';
RJ!
Community Contributor
Posts: 219
Joined: Mon Jul 23, 2012 8:31 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010

Re: Exporting Cube data to Sql Server Table

Post by RJ! »

Hi Jorel,

My code does have the single quotation marks...

BTW - found that the Could not Initialise Process was due to this line of code:

Code: Select all

# Execute process for check to see if Balances send in the SQL query match to the balances in z_ctrl_measures cube
ExecuteProcess('123 JC Trans Daily 3a', 'cubename', 'z_ctrl_measures',
'z_ctrl_measures' , 'Current Trans Daily Date', 'z_string' , 'TM1_Daily' );
Now the process completes successfully, though it is still not updating the Data into the SQL database.
I have actually pasted the SQL from my asciioutput into an SQL tool & the line updates perfectly, still no idea why it won't work from the TM1 process...
RJ!
Community Contributor
Posts: 219
Joined: Mon Jul 23, 2012 8:31 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010

Re: Exporting Cube data to Sql Server Table

Post by RJ! »

ok, for sh!ts & giggles, i put in my username & password into the ODBCOpen and ran the process, then put in the WRONG password and it still process successfully without actually updating the SQL database :shock:

Shouldn't I get some error if i was really connecting to the ODBC source or is it ignoring the password in the code & using the system password that is already knows?
Post Reply