TI Code to map variables from data source

[dubs]

Hi All,

One problem I've had has been to do with ODBC connection data maintenance in Turbo Integrator

I have a database control cube which contains info about my particular database- ie server name, database name, user id and password and I reference this throughout my TI code so in the event of a database migration or change (a DBA I work with seems to have a mania for changing passwords ) all I have to do is update the cube.

It works well in the code but because TI uses these details in the data source tab if those details change the statement fails (well duh!) but it also means you can eradicate your variable mappings in the variables tab if you aren't careful- it still means that for the TI's to be maintainable by someone less experienced they would have to be updated manually everytime there is a DB change.

my question is are there any undocumented functions for mapping the variables in code? that way I could leave the data source and variables tabs empty and do it all in code.

I suspect not but there a lot of more knowledgeable people here

[tomok]

my question is are there any undocumented functions for mapping the variables in code? that way I could leave the data source and variables tabs empty and do it all in code.

No. If you don't specify a data source when you build the TI, or modify it, you'll only have a Prolog and Epilog tab available to you. You would have to do all your data loading in the Prolog tab which means you would have to build our own looping code with WHILE / END and inside that build a query that pulls one record at a time and a then a CellPut to write it into TM1. Not really a viable solution.

As long as you are not changing the layout of the DB query in a TI process then you should be able to go in and change anything on the Advanced tabs without losing your variables.

[dubs]

thought as much- I certainly need the metadata and data tabs and have noticed they are only present when a data source is defined in the data source tab- the only potential danger is if something does change to cause the sql statement to fail and the variables tab is clicked- I guess I worry about my system falling over if an unsuspecting developer doesn't choose 'Keep All Variables' and then saves the proc- but I guess that is what backups are for.

[Wim Gielis]

but I guess that is what backups are for.

And training users

[dubs]

I hear you but it's not neccessarily an option- it's a project I could be off of before go live - there will be a compehensive user/admin guide but these things are not always read

[Martin Ryan]

thought as much- I certainly need the metadata and data tabs and have noticed they are only present when a data source is defined in the data source tab- the only potential danger is if something does change to cause the sql statement to fail and the variables tab is clicked- I guess I worry about my system falling over if an unsuspecting developer doesn't choose 'Keep All Variables' and then saves the proc- but I guess that is what backups are for.

Have you used the TI wizard to create most of your code? If you've manually written it then it's not that much of a disaster if the user doesn't choose "Keep all variables", all that will happen is the variables types will be set back to "Ignore" and any new variables that you've created manually on the Variables tab will be deleted. But if you've followed usual practice all you have to do is change all the "Ignore"s to "Other" and all your manually created variables are manually written in the advanced tabs anyway, so no real harm done.

If you've used the TI wizard it's pretty easy to make it all manual instead. Copy the generated code out of the *** GENERATED CODE....*** sections and paste it down below that section (i.e making it all manual instead of wizard driven), then go back and change all your variables to "Other". Then click through the advanced tabs to regenerate the wizard code (which will now be none) and bob's your uncle, you now have manual only code that the wizard can't stuff up for you.

Martin

[lotsaram]

thought as much- I certainly need the metadata and data tabs and have noticed they are only present when a data source is defined in the data source tab- the only potential danger is if something does change to cause the sql statement to fail and the variables tab is clicked- I guess I worry about my system falling over if an unsuspecting developer doesn't choose 'Keep All Variables' and then saves the proc- but I guess that is what backups are for.

You will only lose variable mappings if the the variables tab is refreshed. This is a risk for any ODBC process that uses a parametised query which will only be valid at run time. The only solution is to train your admins. (Note an alternate TI editor is commercially available which does not have this annoying behaviour and will only preview the data source and refresh variables when explicitly told to do so.)

In general for any process you can switch out data sources on the prolog, or even change between ODBC, ASCII and VIEW if you want to. There is no code for assigning variables at run time - they need to be pre-defined. The trick when setting up a generic process that will allow data sources to be switched is to allow for the maximum number of variables. The data source assigned at run time can always have LESS variables than the saved process but it can never have MORE.

[dubs]

thought as much- I certainly need the metadata and data tabs and have noticed they are only present when a data source is defined in the data source tab- the only potential danger is if something does change to cause the sql statement to fail and the variables tab is clicked- I guess I worry about my system falling over if an unsuspecting developer doesn't choose 'Keep All Variables' and then saves the proc- but I guess that is what backups are for.

Have you used the TI wizard to create most of your code? If you've manually written it then it's not that much of a disaster if the user doesn't choose "Keep all variables", all that will happen is the variables types will be set back to "Ignore" and any new variables that you've created manually on the Variables tab will be deleted. But if you've followed usual practice all you have to do is change all the "Ignore"s to "Other" and all your manually created variables are manually written in the advanced tabs anyway, so no real harm done.

If you've used the TI wizard it's pretty easy to make it all manual instead. Copy the generated code out of the *** GENERATED CODE....*** sections and paste it down below that section (i.e making it all manual instead of wizard driven), then go back and change all your variables to "Other". Then click through the advanced tabs to regenerate the wizard code (which will now be none) and bob's your uncle, you now have manual only code that the wizard can't stuff up for you.

Martin

I don't tend to, I only use the Data Source tab to get the variables and activate the metadata and data tabs- I never auto generate code

I'm pretty sure that I've managed to eradicate all variables before by choosing 'keep derived variables' on a failing data source - I would have to test this but its this that worries me as what I tend to do with my manual variables is take them out of the auto-generated code part and put them in the body of my code - but if the automatic variables go the manual ones will fail, I definitely need to do more testing as it sounds like it isn't much of a problem in reality and that perhaps I'm being over cautious.

[dubs]

thought as much- I certainly need the metadata and data tabs and have noticed they are only present when a data source is defined in the data source tab- the only potential danger is if something does change to cause the sql statement to fail and the variables tab is clicked- I guess I worry about my system falling over if an unsuspecting developer doesn't choose 'Keep All Variables' and then saves the proc- but I guess that is what backups are for.

You will only lose variable mappings if the the variables tab is refreshed. This is a risk for any ODBC process that uses a parametised query which will only be valid at run time. The only solution is to train your admins. (Note an alternate TI editor is commercially available which does not have this annoying behaviour and will only preview the data source and refresh variables when explicitly told to do so.)

In general for any process you can switch out data sources on the prolog, or even change between ODBC, ASCII and VIEW if you want to. There is no code for assigning variables at run time - they need to be pre-defined. The trick when setting up a generic process that will allow data sources to be switched is to allow for the maximum number of variables. The data source assigned at run time can always have LESS variables than the saved process but it can never have MORE.

I am interested in that alternative TI editor, I will scour the net for that.

so say I generate a number of variables from a data source and change the data source in code how does it remap the variables? I guess it would match column names to variable names but I guess this is too much like 'magic' for me, like I said to Martin I need to do a few tests because I'm sure in the past I've switched data source to one that failed and lost all my variables

I guess it would be great to not have to use the data source tab to assign variables and activate the metadata and data tabs but suppose in most instances it's faster and easier for most people to set up procs in this way.

[Alan Kirk]

thought as much- I certainly need the metadata and data tabs and have noticed they are only present when a data source is defined in the data source tab- the only potential danger is if something does change to cause the sql statement to fail and the variables tab is clicked- I guess I worry about my system falling over if an unsuspecting developer doesn't choose 'Keep All Variables' and then saves the proc- but I guess that is what backups are for.

You will only lose variable mappings if the the variables tab is refreshed. This is a risk for any ODBC process that uses a parametised query which will only be valid at run time. The only solution is to train your admins. (Note an alternate TI editor is commercially available which does not have this annoying behaviour and will only preview the data source and refresh variables when explicitly told to do so.)

In general for any process you can switch out data sources on the prolog, or even change between ODBC, ASCII and VIEW if you want to. There is no code for assigning variables at run time - they need to be pre-defined. The trick when setting up a generic process that will allow data sources to be switched is to allow for the maximum number of variables. The data source assigned at run time can always have LESS variables than the saved process but it can never have MORE.

I am interested in that alternative TI editor, I will scour the net for that.

You really don't have to scour that far, it came up in discussion just the other day: http://forums.olapforums.com/viewtopic. ... =543#p3288

so say I generate a number of variables from a data source and change the data source in code how does it remap the variables? I guess it would match column names to variable names but I guess this is too much like 'magic' for me, like I said to Martin I need to do a few tests because I'm sure in the past I've switched data source to one that failed and lost all my variables

It doesn't. Changing the data source won't change your hand-crafted TI code. (As for what the TI Wizard might do to the auto-generated code... well, who really cares what that abomination might come up with, anyone who subjects themselves to the code generated by that thing are reaping what they sow.) What it may do is dump the existing variable definitions (if, say, the table headings are different), as a result of which you'll get a compile error wherever you used the original names in your code because TI will no longer know what you're referring to.

[dubs]

You really don't have to scour that far, it came up in discussion just the other day: http://forums.olapforums.com/viewtopic. ... =543#p3288

Thanks Alan, will check that out.

It doesn't. Changing the data source won't change your hand-crafted TI code. (As for what the TI Wizard might do to the auto-generated code... well, who really cares what that abomination might come up with, anyone who subjects themselves to the code generated by that thing are reaping what they sow.) What it may do is dump the existing variable definitions (if, say, the table headings are different), as a result of which you'll get a compile error wherever you used the original names in your code because TI will no longer know what you're referring to.

he he he yeah I agree

This is what I expect it to do, set up data source in data tab to define variables, override data source in prolog but keep same variables, use variables for all manner of functions in code.

then db password gets changed, TI process fails and admin opens up TI- inadvertently eradicates variables and saves- et voila TI is now broken.

I think I will state in my admin guide that if a TI process fails you only change the DB credentials in my DB cube and not to open the TI, it's just a shame that if someone does open the TI they could corrupt it - unless I could write some code which first checks for the presence of the variables....hmmm...

[blackhawk]

If you are trying to programmatically create TI scripts, there is a way to do this using XML. Here is a sample XML definition for a simple TI script that you can create and execute in the background without any user interaction.

If you want to know more, send me a private message.

Code: Select all

<Datasource Type="TAB" Name="Text" Delimiter=" " LocalFilename="E:\Data\FinancialData\FactFinance.csv"
                    Filename="E:\Data\FinancialData\FactFinance.csv" Quote="&quot;" 
                    HeaderRecords="0" ThousandsSeparator="," DecimalSeparator=".">
  <Column Name="inKey" Type="String" />
  <Column Name="inDateKey" Type="String" />
  <Column Name="inOrgKey" Type="String" />
  <Column Name="inDeptKey" Type="String" />
  <Column Name="inVersionKey" Type="String" />
  <Column Name="inAccountKey" Type="String" />
  <Column Name="inValue" Type="Numeric" />
</Datasource>

<Parameters Type="Parameters" />

<Prolog Type="Prolog">
    vCubeName = 'Financials';
    ViewZeroOut( vCubeName, 'sys_All Data' );
</Prolog>

<MetaData Type="MetaData"></MetaData>

<Data Type="Data">
    vYear = SUBST( inDateKey, 1, 4 );
    vMonth = SUBST( inDateKey, 5, 2 );

    CellPutN( inValue, vCubeName, inVersionKey, inDeptKey, vYear, vMonth, inAccountKey );
</Data>

<Epilog Type="Epilog"></Epilog>

[tomok]

If you are trying to programmatically create TI scripts, there is a way to do this using XML. Here is a sample XML definition for a simple TI script that you can create and execute in the background without any user interaction.

What third party tool does this involve, because the capacity to execute XML code does not exist in any TM1 version I am aware of.

[blackhawk]

It is called Enterprise Services. It uses XML and web services to access all objects in TM1, including TI scripts, chores, rules etc. I can provide you with more details in the commercial section or in a private message.

Sorry for the inconvenience. I hope you understand that since I work for the company, I don't want to violate the rules of the board by posting information here that would be construed as advertising.

[Paul Segal]

I can provide you with more details in the commercial section or in a private message.

Could you do it in the Commerical section then? I'd be interested to see some more detail.

Regards

[blackhawk]

Could you do it in the Commerical section then? I'd be interested to see some more detail.

Ok, Paul, I gave a long-winded answer in the commercial forum, but hopefully, it will answer everything you would want to know about it. Or at least enough to formulate more questions

See the topic: http://forums.olapforums.com/viewtopic.php?f=20&t=3970

Enjoy!

[conray]

i am facing the same situation as dubs, but then i am unable to setup correctly the process.

1. Password is set in a cube
2. TI Process was created initially using ODBC connection and manual TI codes, it was tested and it works
3. Now I am trying to implement the use ODBCOpen and ODBCOutput:

Code: Select all

#Open ODBC Connection
ODBCOpen('SqlServerName','UserID',CellGetS('cubeSystemSetting','ODBCUserPW','Value'));
ODBCOutput('SqlServerName','EXEC dbo.stored_procedure;');

I have tested the code on another seperate dummy process, the connection works fine and stored procedure was being called
4. Then i did a test, by changing the password for the UserID logging in to SQL server, and also updating the password in the System Setting cube. The password in the data source tab remains unchanged.
5. When i execute the process, an error displaying "Unable to open data source" appear, i am stuck here right now, how am i suppose to link the data source variables with the ODBCOutput command in TI?

[conray]

I guess i fixed my own problem by using the TI process local variables DatabaseUsername and DatabasePassword.

Code has been fixed to:

Code: Select all

#Sets Username and Password
DatasourceUsername='UserID';
DatasourcePassword=CellGetS('cubeSystemSetting','ODBCUserPW','Value');

#Open ODBC Connection
ODBCOpen('ODBC Source',DatasourceUsername,DatasourcePassword);
ODBCOutput('ODBC Source','EXEC dbo.stored_procedure;');

Just to highlight that we should at best try not to open the process again as it could possibly corrupt the "Variables" section of the TI process.