IBM Cognos TM1 and Stored Procedures

We often use ODBC as Datasource Type in a Turbo Integrator (TI) process and load data into IBM Cognos TM1 cubes or build metadata.  In majority of the cases, we end up writing SQL code in Query box, to fetch the information that is required to perform the intended task.  Once in a while, we are required to work with a Stored Procedure (SP) in IBM Cognos TM1.

We will  look at how to execute an SP from TI. I will be using DB2' sample database to demonstrate the example.  SP name is TM1SP1 and its body is as follows.  A very simple SP, which accepts department number as a parameter, it then brings back the name and manager number associated with that department.

CREATE OR REPLACE PROCEDURE TM1.TM1SP1 (IN SrchNo char (3))
    DYNAMIC RESULT SETS 1
    LANGUAGE SQL
BEGIN
    DECLARE CUR1 CURSOR WITH RETURN TO CALLER FOR
        SELECT DeptNo, DeptName, MgrNo FROM TM1.DEPARTMENT WHERE DeptNo = SrchNo;
    OPEN CUR1;
END

Create a new TI process using ODBC as datasource type.  Choose the appropriate DSN, key in username and password.

For MS SQL if single-signon is enabled then, there is no need to key the username and password.  In our case, DB2 is not configured.  Therefore a named user and password is required to connect to the database.

 To run a stored procedure in DB2, you will need to use CALL function.  In our case, to run the TM1SP1 stored proc, the command will be 

In MS SQL or Oracle, you will use Execute SP_Name to run a SP.  Alternately you could abbreviate the Execute command to Exec and it will still work.

The preview pane of the TI task will now show the output of the stored procedure.  The test SP was written to return 3 columns from the Department table.  Preview pane in the TI window will show these 3 columns.

You can now switch to the Variable tab in TI window and work forward from there.

So far so good. This method will work.  As long as the stored procedure does NOT modify the data (i.e. there are no insert/update/delete statements in it).

Let's move on to Employee table in our sample database and look at Salary of Ted

SELECT EmpNo, FirstNme, LastName, Salary FROM TM1.Employee where EmpNo = '000100'; 

We will write an SP that increments salary of an employee by 1,000

CREATE OR REPLACE PROCEDURE TM1.TM1SP2 (IN SrchNo char (6))
    DYNAMIC RESULT SETS 1
    LANGUAGE SQL
BEGIN
    UPDATE TM1.Employee
    SET Salary = Salary + 1000
    WHERE EmpNo = SrchNo;
END

Every time a call to the SP is made, it increments the salary of employee number (passed as parameter) by 1,000.  Going by the above steps, if we end up creating a SP and write our TI as below

When you hit preview pane, you will see the results as below:

Ted's salary is already incremented by 1,000 to 87,150.  Let's proceed to variables tab and define the available 4 variables as Other.  In data tab, let's put this statement

AsciiOutput (GetProcessErrorFileDirectory | 'sk.txt', EmpNo, FirstNme, LastName, NumbertoString (salary));

Save the process and run it.  After the process is run, open the text file - sk.txt in our case.  Salary of Ted is incremented by another 1,000 to 88,150!


While the intention was to run the SP once and increment the Salary for given employee, by 1,000.  Net effect is that Salary is now incremented by 2,000.  Another unintended consequence of this approach is that, if someone just opens this TI process in TM1 Architect, then also the Salary is incremented!

Therefore, if the SP we are working with modifies the data, then it is better to run it in the prolog by using TI function DatasourceQuery.  

In our example, while we want to run the Stored Procedure in Prolog of Cognos TM1's TI, in the initial query pane, there is a need to have a valid SQL.  Since the Stored Procedure returns 4 columns, we will write out a sql that gets 4 columns but does nothing.

 When you hit Preview this is what is seen

Prolog tab will have the following code.

vs_SQL = 'Call TM1.TM1SP2 (''000100'')';
DatasourceQuery = vs_SQL;

Let the AsciiOutput statement remain as-is in the Data tab.  Set Ted's Salary back to 86,150.  

From TM1 Architect, run the modified TI process.  If we check the output file as well as the DB2 Table contents, we will now see that Salary is incremented by 1,000 alone.  Even if someone repeatedly opens the TI, the Salary is not changed - unless the process is run.

Summary:

• To run a stored procedure from a TI in IBM Cognos TM1, we can write the statement in the query pane.  This is ok as long as the SP fetches the data
• If however, the SP we want to work with modifies the data in some fashion (be it insert, update, delete or some combination of these) then writing the statement in the query pane, will lead to double/triple/quadruple/multiple executions, causing lot of grief