Publishing Data from TM1 to an ODBC source

[cw1194]

I realize this will sound eerily similar to a question just posted but the source and destinations were reversed..when setting up the ODBC connection to publish FROM TM1 to access...what should I select for the Database?
I am trying to publish the entire cube called Sales. Also, do I need to do anything in the configuration with System Database?
I believe I am solid in the advanced tab for entering code.

Thanks

[Martin Ryan]

what should I select for the Database?

Your source for the TI should be a CubeView.

When setting up the ODBC connection as describe by Alan Kirk you should set up an Access database. The ODBC connection wizard confuses things a little by having multiple drivers for .mdb databases, but I think you'll be ok with any of them. I tend to use the first which is "Driver do Microsoft Access (*.mdb)"

Is that what you meant?

When you finish creating the ODBC connection make sure you give it a useful name without spaces or punctuation, e.g. 'myAccessDatabase'. Your username and password are likley to both be empty strings when you write the ODBCOpen function in the Prolog of your TI.

Cheers,
Martin

[David Usherwood]

It doesn't have to be Access of course - I use SQL Server (2000) for most database work and that's fine too.
If you talk to a data engine which has security then you need to put a password in the ODBCOpen - and it's not encrypted - yecchh.
If you use SQL server with integrated login then the TM1 server gets the access to SQL assigned to the _user_ under which the TM1 server is running.
And as a QI-ish aside, for Martin:
My belief is that
"Driver do Microsoft Access (*.mdb)"
refers to the Portuguese version of the Access driver.
Not that it matters...

[Alan Kirk]

If you talk to a data engine which has security then you need to put a password in the ODBCOpen - and it's not encrypted - yecchh.

Yes, that was an issue with processes which had an ODBC data source as well up until somewhere around one of the late 8.3's or 8.4, from memory. It was stored in the .pro file in plain text, though it's now encrypted.

I can't see any way that that could be implemented for the ODBCOpen statement as things stand though, since it's part of the main body of the code. The only way they could change that, and I agree that it probably WOULD be better changed, would be for some form of encrypted variable to be assignable. This would involve a change of both the .pro file structure, and an overhaul of the GUI.

And we couldn't have that, because then people might expect a find and replace function to be added.

I'm not sure whether Chris (the original poster) has got this working yet; he sent me some questions about his particular test implementation via PM earlier today and we had a few exchanges on the subject, but he seemed to be on the right track when we last spoke.

[yyi]

had this going before then got sidetracked, lost the .pro (while cleaning the data\dir on dev), lost my memory. all i can recall is a .vue then a odbcoutput(dsn, sql ..)
it'd be great if someone can post a working sample with |pipes, string/num handler..

[cw1194]

I have finally moved past the 'cannot connect to remote machine' error thanks to the advice from the board.
However I am still not getting data to populate into my Access Database.
This is the entirety of my Data Tab in TI:
#****GENERATED STATEMENTS START****
#****GENERATED STATEMENTS FINISH****


ODBCOutPut( 'Sendaccount', ('INSERT INTO Cubeview ( Value ) VALUES ( Value)' ));

I simplified my SQL Statement to only pull the numeric data and insert it into the MS Access DB called Cubeview.

Is there something wrong with my setup or is there a lack of declaration that is keeping the data from being sent?


Regards,
Chris

[Eric]

Try

Code: Select all

ODBCOutPut( 'Sendaccount', Expand('INSERT INTO Cubeview ( Value ) VALUES ( Value)' ));

[cw1194]

Thank you Eric...however I am still getting nothing. I'm sure my mistake is painfully simple. Is there a process to see exactly what the process will try to pull
other than in the preview window. By that I mean is there another way to show the output from the execution of the SQL Statement?

Regards

[Eric]

If you are referencing variable the must be enclosed in %


Exmaple
ODBCOutPut( ‘TransData’, Expand( 'INSERT INTO SALES ( MONTH, PRODUCT, SALES ) VALUES ( "%V0%", "%V1%",%V2% )' ) );

[paulsimon]

cw

You can create a statement like this, ahead of the ODBC statement

asciioutput('temp.txt',Expand('INSERT INTO Cubeview ( Value ) VALUES ( Value)' ) ) ;

This will output the SQL to the file temp.txt in the Server Database directory.

ODBCOutPut( 'Sendaccount', Expand('INSERT INTO Cubeview ( Value ) VALUES ( Value)' ));

I would then suggest that you paste the generated SQL code in to your SQL query engine to make sure that it runs correctly there. Make sure that you log on with the same user id and password that you use in your TI query so that there are no possibilities of security issues.

Check that you have an appropriate ODBCOpen in your Prolog and an ODBCClose in your Epilog.

The ODBCOutput should be in the Data tab.

Your ODBC DSN needs to be defined on the Server, usually as a System DSN. Your Server needs to be running under a User Id that has local Admin rights. I have seen a few people set up servers under limited accounts that they cannot see the DSN.

You could of course try it on a local server first, then you can eliminate those issues.

Hope this helps. I would definitely try the SQL that gets generated in your SQL Query tool, as TI can sometimes trap error messages that would be useful.

Regards


Paul Simon

[Alan Kirk]

If you are referencing variable the must be enclosed in %


Exmaple
ODBCOutPut( ‘TransData’, Expand( 'INSERT INTO SALES ( MONTH, PRODUCT, SALES ) VALUES ( "%V0%", "%V1%",%V2% )' ) );

Chris,

Eric's syntax should work except for one little thing; Access likes square brackets around its field names. I modified the syntax for the cube => database export that I described to you in PMs last week to use Eric's.

This worked:

Code: Select all

s_SQL = '';

s_SQL = Expand( 'INSERT INTO TM1_Test  ([Version], [State], [Metric], [Value] ) VALUES ("%Version%", "%zzTestStates%", "%zzTestValues%", %Value%)'  );

ODBCOutput( s_Source , s_SQL);

However remove the square brackets around the field names, and it doesn't.

[Martin Ryan]

Not sure how long your code actually is, because I think you're posting a simplified version. Until version 9.4 you're expanded query must be less than 255 characters - which is not a lot.

I think you can help yourself a bit by making sure your variable names are really short (e.g. v1 not MonthlySales) and you might even have to do something similar in the columns of the Access table you're exporting to. Then, for aesthetics' sake, you can link that into a second table with decent column names.

Martin

[deepu_sree]

Hi Guys,
I need an urgent help on this.
Problem statement:
I need to write data from the cube to the Access DB.
Note:My DSN is called POC.mdb, my database is Access_DB. Actuals is a number column.
I just select ACtuals from the cube and load it into the Access DB.

TI Process that I've created:
Prolog section:
odbcopen('POC.mdb','','');
odbcoutput('POC.mdb',Expand('insert into Access_DB ([actuals]) values(1)'));

Epilog section:
odbcclose('POC.mdb');

Variables tab:
How do we configure the variables tab? Should i configure all the variables as "Data"?

Please help asap.

Regards,
Deepu.

[tomok]

Make them "Other" in the variables tab. You also need to move the 2nd line from your Prolog tab to the Data tab. Keep in mind code in the Prolog tab is executed one time, before any records are processed. Code in the Data tab is executed once for each record encountered so your ODBCOutput statement should be in the Data tab.