Pass Parameters into TI including ODBC links

[louby321]

Hi all

I have several TI processes which I'm rewriting and making more generic, by using parameters.
I'm stuck on how to do this with TI's that are pulling data off an Access database, to populate a cube.

So, for example. I have a process that in the datasource tab, has Datasource Type as ODBC,
Data Source Name = 'DataSourceName' and
Query is select * from TableName.

I would like to scrap this and have it all writen as code in the Prolog section,
with 'DataSourceName' and 'TableName' being passed in as parameters.

Is this possible, and if so what functions do I need to use in the code. I can only see ODBCOpen and ODBCOutput.

Many thanks for any help.

Louise

[ppillai]

You can create two parameters in the Parameter tab
eg pDataSource and pTableName

In the Prolog tab then you can set the datasource name

DatasourceNameForServer=pDataSource;

and in the Query you can pass in the table name parameter

Select *
from ?pTableName?

When you run the TI process it will ask for you to enter these parameter values, or you can execute this process from another process by passing the parameter in the ExecuteProcess funtion.

ExecuteProcess( 'Process Name', 'pDataSource', Datasource name,'pTableName',Table name);

[jstrygner]

I think this may help you:
http://forums.olapforums.com/viewtopic. ... 766#p12799

[paulsimon]

Hi

This is a common issue, particularly when different ODBC DSNs need to be used in different environments, or when you want your Dev TM1 Server to be able to run against either the Dev database or the Production database for volume testing.

I set up an ODBC Info cube with dimensions of Environment, and ODBCmeas.

The Environment dimension might have

Dev
UAT
Prod

The ODBCmeas are

ODBCDSN
UserId
Password

Then I can record the details for each environment (Be sure that general users have NONE access to this cube so that they cannot see the password which unfortunately has to be plain text)

I then have an Info cube where I record the the current ODBC environment, eg Dev

In the Prolog I read the current ODBC environment from the Info cube. I then use this to access the ODBC Info cube and pull out the ODBC DSN, User Id and Password. I then use the DataSource TI local variables to change to source to the appropriate one at run time.

This is a standard bit of code that can be pasted in to any of your processes. There is no need to parameters.

By the way, if you have no security on Access, you can just use a user id of Administrator and a blank password.

The

Select X
from ?vTable?

will get you past the table issue. Just make sure that you set vTable appropriately in the Prolog.

When you first put ?vTable? in to your SQL, you should immediately save the process, and select Keep All Variables. The SQL will fail, but so long as you select Keep All Variables, your variables will remain, and the process will run correctly so long as you set vTable to something valid in the Prolog, as the SQL isn't run until the MetaData or Data tab.

Regards


Paul Simon

[ub14]

Hi,

We are trying to execute TI process from anoother TI process with the below 3 parameters DataSourcename, Userid and Password for DB. I am trying to override the default values given in the first tab Tourbe Integrator(DataSource Tab, Data Source Name, UerName, PasssWord). When i provide different datasourcename, the process failing, how can we override these values when we execute from different TI


ExecuteProcess('PROCESS1','PDataSource',DataSourceNameForServer,'PUserName',DataSourceUsername,'Ppassword',DataSourcePassword);