Pass parameters into SQL Query in TI

[krithika331]

Hi,

I have the TI processes which are using the ODBC datasource. There are schema names and its schema aliases using which we fetch data and switch between different environments. But in order not to update the TI process everytime, we have system cube for which we are planning to do Cellgets from system cube which contains the schema names and aliases as values. So , in the SQL query instead of the schema name, we have to use the parameter in getting the alias schema value so that we can query for any environment. I have created a paramter in the Parameter tab and passing it in the SQL query in the schema calling location. ( I mean schema.tablename as ?pschema?.tablename). The SQL statement is not working in this way. I tried to define the Cellgets (systemcube) for the parameter in prolog to get alias schema value but it is also not working. Any ideas??


Thanks in Advance

[Wim Gielis]

As a first exercise, You can set up the query (completely) in the Prolog tab, using the pipe symbol to concatenate different parts of the SQL string.
Do a CellGetS co capture the cube info.
Then set the data source dynamically in the Prolog tab once you have the (correct) entire string.

[krithika331]

Thanks for the reply..

In the select query, we read as schema.tablename, So I should break the select query into parts upto the schema and concatenate with tablename and assign different variables for these parts and the schema part should be a variable to do CELLGETS from lookup cube. The Cellgets should also be used with a parameter which is defined in advanced section and so that it prompts for the schema name to get the alias schema. Is it possible to for such scenario handling the parameter/variable at the datasource section instead defining the query in the prolog??


Thanks in Advance...

[Wim Gielis]

Sure, this is possible. Did you try?

[krithika331]

Is it possible to for such scenario handling the parameter/variable at the datasource section instead defining the query in the prolog??

I have tried it in the datasource section as (select * from ?pschema?.tablename ) but it says the SQL statement failed. So, is it that I have to go writing the whole query in Prolog section and perform as we first discussed i.e. concatenating different parts of SQL ??


Thanks in Advance..

[Andy Key]

If you don't want to update the SQL in the prolog you will need to set up your TI process using SQL without any (TM1 sourced) parameters first. This will generate your variables on the Variables tab.

Now introduce your parameters into the SQL. As you move around the process, or re-open it in the editor you will get the 'SQL statement failed' message, as, at the point at which the TI is running the SQL (i.e. when it is evaluating either the variables tab or the preview pane), the parameters do not have any context. If when you are doing this you get the popup asking what you want to do with variables, select Keep All.

Once you execute the full process, the parameters will be instantiated and the SQL will execute successfully (assuming you actually have the statement correct).

Note that if you need to update the SQL so that it now includes a different number of columns, or the content of the columns change, then you will need to change your SQL back to not having parameters so that you can update the contents of the Variables tab.

[krithika331]

Hi,

I am trying as below, but it gives me the error of variable 'column' when I run the TI process. Should I consider another process for inserting the dimensions?


Code:
=====


PROLOG:

xschema= cellgets(' lookupcube', 'A', 'B');


SQLquery1 = ('SELECT DISTINCT m.column
FROM xschema.table m WITH UR;');


ODBCOpen( 'DSNname', ' userid' , 'passwd ' );
ODBCOutput( 'DSNname', SQLquery1);
ODBCClose('DSNname');

sDim1 = 'test' ;

IF ( DimensionExists ( sDim1 ) = 0 );
DimensionCreate ( sDim1 ) ;
ENDIF ;

DIMENSIONSORTORDER(sDim1,'BYINPUT','ASCENDING','BYHIERARCHY','ASCENDING');


Total='Totalhierarchy';
DIMENSIONELEMENTINSERT(sDim1,'', column,'n');
DIMENSIONELEMENTINSERT(sDim1,'',Total,'c');
DIMENSIONELEMENTCOMPONENTADD(sDim1,Total,column,1.000000);

[Wim Gielis]

Do you try to insert elements to dimensions in the Prolog tab, based on variables??? That should be done in the Metadata tab.

[declanr]

ODBCOpen( 'DSNname', ' userid' , 'passwd ' );
ODBCOutput( 'DSNname', SQLquery1);
ODBCClose('DSNname');

ODBCOpen, ODBCOutput and ODBCClose are not the functions you use for setting a datasource in the prolog.

Use DataSourceType, DataSourceNameForServer, DataSourceQuery, DataSourceUserName and DataSourcePassword or some of them depending on your requirement.