Saturday, January 8, 2011

OBIEE Using a MSSQL stored procedure as datasource

A MSSQL stored procedure can return a table. You can use this table as a normal datasource in your repository.

Let’s  create a basic procedure


-- =============================================
-- Author:        John Minkjan
-- =============================================
CREATE PROCEDURE sp_dim_Accounts   
    SELECT     dim_Account_ID, Account_Code, Account_Name
FROM         dim_Accounts


In the repository create a new table:


Make the type stored procedure:


Add the columns manually:


Add the execute script:



You will see that the table symbol has changed:


You can now join it as a normal table to the rest of your model.

Till Next Time


Mithun said...

Hi John Minkjan, it is very much useful.. thanks a lot.

At the same time can I know how to proceed if I don't have any tables but only Stored Procedure to pull the data..?

Because with only stored procedure while checking the consistency Im getting error as there is no join...

John Minkjan said...


Why don't you join against a "dummy" table?

Mithun said...

Hi John Minkjan,

Thanks a lot for this, yes I can do with dummy table.

Now I have one doubt, suppose the stored procedure which we are using in the Physical layer to get the data is having some input parameters, the how can we pass the values for these parameter dynamically?

I understood we call the stored procedure as you have mentioned above,but what I'm trying to do is provide a prompt for the user so that they can provide values through that prompt which in turn will be mapped to the input parameter of the stored procedure.

If this is possible could you please let me know how to do this..?


John Minkjan said...


Add your prompt to a server variable:


Mithun said...

Hi John Minkjan,
I’m too happy to see your reply. Actually I got stuck up with this issue. You mentioned below I got confused about adding the prompt to the server variable;

You mean the session/repository variable?

I can brief my case here,
For example, I have UserID, User Name as my sp result, and User ID is the SP input parameter.

So my expression is EXEC SP_NAME @User_ID = ‘VALUEOF(NQ_SESSION.UserID)’

Also the UserID variable is initialized with a default value.
Here UserID is the variable I have created (Session variable), now how can I create a prompt (so that that will have the possible valued of the UserID, and the user can select a value from the dropdown and let the Sp run for that value)

Should I create another SP which will give the UserID values and then create a prompt on that, then map it to the above variable? I’m not sure whether the method to solve this is.

It would be a great help from your side if you can let me know how to do this. I’m very new to this technology, so really sorry if my email looks so massive.
Could you please provide me the steps to how to create the prompt and do the mapping to the above variable?

Also, if I have a tabled valued function (MSSQL function, with some input variable) what will be the expression?

I have initialized the variable with default value then given the expression as :

Select * from functionname(@User_ID = ‘VALUEOF(NQ_SESSION.UserID)’)

But I’m getting error as the NQ_SESION variable doesn’t have a value.

Looking forward your help to understand these things and to solve my issues.

Thanks a lot,