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:        Kartheek Dachepalli
-- Description : This procedure returns Account details from Account dimension
-- =============================================
CREATE PROCEDURE sp_dim_Accounts   
AS
BEGIN
    SET NOCOUNT ON;
    SELECT     dim_Account_ID, Account_Code, Account_Name
FROM         dim_Accounts_tab
END
GO

[/code]
In the repository create a new table:

Make the type stored procedure:

Add the columns manually: If we select 'Stored Proc' as table type we need to add columns with data types as shown below.

Add the execute script:
while adding the execute code please select your technology. Here I selected sql server 2012 as my database version is 2012.

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.