Exporting Cube data to Sql Server Table
-
- Posts: 90
- Joined: Tue Aug 19, 2008 4:56 pm
Exporting Cube data to Sql Server Table
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?
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?
- 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
Not sure, your best bet is to ASCIIOutput your expand statement and see if that makes sense to a proper SQL client.
Cheers
Cheers
Technical Director
www.infocat.co.uk
www.infocat.co.uk
-
- Posts: 90
- Joined: Tue Aug 19, 2008 4:56 pm
Re: Exporting Cube data to Sql Server Table
Already did that piece. still have some issues.
-
- Posts: 90
- Joined: Tue Aug 19, 2008 4:56 pm
Re: Exporting Cube data to Sql Server Table
Is anything supposed to occur after the ODBCOpen query is run?
-
- Site Admin
- Posts: 1453
- Joined: Wed May 28, 2008 9:09 am
Re: Exporting Cube data to Sql Server Table
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.
"
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.
-
- 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
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:
The result of the asciioutput is:
Anyone have any suggestions?
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') ;
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).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 )
Anyone have any suggestions?
-
- 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
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.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.
-
- 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
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
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
-
- 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
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
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
-
- 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
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.
-
- 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
Try exporting the old values using the formula below. SQL is looking for the ‘ ‘.
New Value = ''''| Old Value|'''';
New Value = ''''| Old Value|'''';
-
- 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
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:
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...
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' );
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...
-
- 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
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
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?
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?