TM1 Write-Back or Upload to MS SQL Sever, or MS Access

Post Reply
appleglaze28
Regular Participant
Posts: 269
Joined: Tue Apr 21, 2009 3:43 am
OLAP Product: Cognos TM1, Planning
Version: 9.1 SP3 9.4 MR1 FP1 9.5
Excel Version: 2003

TM1 Write-Back or Upload to MS SQL Sever, or MS Access

Post by appleglaze28 »

I'd like to know if anybody can help me out on this...I'm still a rookie on using TM1, I'm not an IT person so I'm still struggling on this since I'm more of a Finance person.

Can anybody help me out with the Writeback or Uploading of data from TM1 Cubes most especially when it comes to writing the script for the ODBCOutput on the Data Tab.

Like for Example: My Data source is MS Access: Test
I created a Table : RC
With Fields: Version, vMonth, vYear, Measure, Revenue_Center, Value.
David Usherwood
Site Admin
Posts: 1453
Joined: Wed May 28, 2008 9:09 am

Re: TM1 Write-Back or Upload to MS SQL Sever, or MS Access

Post by David Usherwood »

You can do this. Assume your ODBC
a Create a view representing the data you want to output
b Create a TI with that view as a source
c In the Prolog, enter
odbcopen(<ODBC DSN>, <user>, <password>);
d In the Data tab, enter
odbcoutput(<ODBC DSN>, 'insert into table values(''' |
version |'','' |
vMonth |'','' |
etc
value | ')'
);
e In the Epilog, enter
odbcclose(<ODBC DSN>);

The real challenge is to get the right number of commas and ' quotes in the string to be executed. I recommend building the string first and writing it to a flat file for debugging ie
outputstring = 'insert into ' | ' ..........
asciioutput('test.csv',outputstring);
odbcoutput(<ODBC DSN>,outputstring)

Remember text values need quoting, numeric values don't.

(_Wouldn't_ it be nice to have decent trace and debug facilities in TI?)
Post Reply