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.
Till Next Time
