[OBIEE 10g] Using A MSSQL Stored Procedure As Datasource

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

[code]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO 

-- =============================================
-- Author:        John Minkjan
-- obiee101.blogspot.com
-- =============================================
 

CREATE PROCEDURE sp_dim_Accounts   
AS
BEGIN
    SET NOCOUNT ON;
    SELECT     dim_Account_ID, Account_Code, Account_Name
FROM         dim_Accounts
END
GO

[/code]

In the repository create a new table:

Make the type stored procedure:

Add the columns manually:

Add the execute script:

EXEC [DATABASE_NAME].[SCHEMA_NAME].[PROCEDURE_NAME]

You will see that the table symbol has changed:

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