OBIEE 11116 repository on MS-SQL I

We had to do a POC on some MS-SQL data. Instead of bringing an extra Oracle DB we used an existing MS-SQL instance.

1. Create a repository database:

We called ours “Repositories”

2. Enable case-sensitive collation

ALTER DATABASE Repositories COLLATE Latin1_General_CS_AS

3. Enable Row versioning

ALTER DATABASE Repositories SET READ_COMMITTED_SNAPSHOT ON

 

OBIEE Using a MSSQL stored procedure as datasource adding a variable:

If our stored procedure uses a variable:

[code]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        John Minkjan
-- obiee101.blogspot.com
-- =============================================
ALTER PROCEDURE sp_dim_Accounts   
@UserName varchar(50) = 'me'
AS
BEGIN
    SET NOCOUNT ON;
    SELECT     dim_Account_ID, Account_Code, Account_Name, @UserName as USERNAME
FROM         dim_Accounts
END
GO

[/code]

You can add the variable by using the VALUEOF() procedure:

 

@VARIABLENAME = N’VALUEOF(NQ_SESSION.VARIABLE_NAME)’

Till Next Time

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.

Till Next Time