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

OBIEE11g Customizing

Download this info from Oracle: http://www.oracle.com/technetwork/middleware/bi/customizing-oracle-biee-11g-176387.pdf

Till Next Time

OBIEE a new year

First of all best wishes for the new year!

Yes, indeed it has been to long for me to do some serious blogging. Why? well mostly because I was working extremely long hours on a non OBIEE project. Believe it or not there are still some other BI tools around ;-) .

In the past year we finally saw the launch of 11g. Looking around in the market we see that after the first buzz everybody went into a sort of waiting mode. Why? One of the main arguments seems to be the investment needed to convert from 10g to 11g. Not only do you need to invest the time, but on smaller systems new hardware might be required. A other argument seems to be the change in systems management which might require a reshuffle in the IT department.

10g customers are still very satisfied, new customers often go for an out of the box dashboard package for a specific branch or application. I haven’t seen many start from scratch projects with 10g in the last 6 months.

Well my new year resolution is to pick all my blogging activities again..

Till Next Time