[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.

[OBIEE 10g] Number Of Values In The Multiselect Prompt

 

OBIEE Number of values in the multiselect prompt

If you want more or less choices returned alter / add this in the instanceconfig.xml:

{Prompts}

{MaxScrollValues}100{/MaxScrollValues}

{/Prompts}

[OBIEE 11g] Integrate Oracle Weblogic Server With OID

 

Integrate Oracle WebLogic Server with OID

This is on “How to integrate Oracle WebLogic Server with OID (Oracle Internet Directory) for user Login/Authentication” By Default Oracle WebLogic Server uses security realm with name “myrealm” that uses embedded LDAP server (two default users WebLogic & OracleSystemUser) that acts as data store for Authentication, Authorization, Credential Mapping and Role Mapping Provider.

You can view Embedded LDAP Server configuration from WebLogic Console Page (/console) : Domain -> Security -> Embedded LDAP Server

 

If you wish to login to WebLogic Server using users in Oracle Internet Directory (more on OID here) or allow access to your WebServices to users in OID (OWSM Policy) then you will have to define new Authentication Providers in your WebLogic realm (myrealm) . Authentication Provider supported by WebLogic are LDAP, RDBMS, Windows NT, SAML, Password Validator, Identity Assertion etc..

Before I jump to step by step OID (LDAP) authentication provider configuration for WebLogic, lets first check few important things


1.You can configure one or more (minimum one) Authentication Provider in a security realm in WebLogic.

2. For multiple Authentication Providers, they are called in order in which they were configured in the security realm.

3. Control Flag: (as shown in 4th image from top) is used to control how authentication providers are used in login sequence. These are also called as JAAS control flag and values are : REQUIRED , REQUISITE, SUFFICIENT, OPTIONAL.

REQUIRED - The Authentication provider is always called, and the user must always pass its authentication test. Regardless of whether authentication succeeds or fails, authentication still continues down the list of providers

REQUISITE - The user is required to pass the authentication test of this Authentication provider. If the user passes the authentication test of this Authentication provider, subsequent providers are executed but can fail (except for Authentication providers with the JAAS Control Flag set to REQUIRED).

SUFFICIENT - The user is not required to pass the authentication test of the Authentication provider. If authentication succeeds, no subsequent Authentication providers are executed. If authentication fails, authentication continues down the list of providers.

OPTIONAL - The user is allowed to pass or fail the authentication test of this Authentication provider. However, if all Authentication providers configured in a security realm have the JAAS Control Flag set to OPTIONAL, the user must pass the authentication test of one of the configured providers.

 

Things required to configure OID as authentication Provider in WebLogic

1. OID Server Name

2. OID Port

3. Password for cn=orcladmin account -> WebLogic Server uses this account to connect to LDAP Server

4.BASE DN of your user base in OID -> cn=users, [default OID realm] (usually of format cn=users, dc=uk, dc=co, dc=focusthread)

5.BASE DN of your group base in OID -> cn=groups, [default OID realm] (usually of format cn=groups, dc=uk, dc=co, dc=focusthread)



How to configure OID as Authentication Provider in WebLogic

1. Login to WebLogic Console -> Security Realm -> myrealm

2. Select tab Providers -> Authentication -> Default Provider 

 

3. Change Control Flag (JAAS Flag) from REQUIRED to SUFFICIENT

4. Click on New to add new Authentication Provider

5. Select OracleInternetDirectoryAuthenticator

6. Change control flag to SUFFICIENT for OID Authentication Provider added above

7. Select Provider Specific tab and enter your OID server details
Do not forget to change “User Base DN” and “Group Base DN” as per your OID

8. Bounce WebLogic Server

To test if Integration is working :Create a group (Administrators or Deployers or Operators or Monitors) and dummy user as member of group in OID and try to login to WebLogic Server using user in OID who is member of one of group in OID.

You can enable debug on Login (Security) to find debug messages in log file for actions (behind the scenes in WebLogic Server)

Servers -> AdminServer -> Debug Select WebLogic -> Security and click on Enable button
You can see debug messages in $DOMAIN_HOME/servers/AdminServer/logs

[OBIEE 11g & ODI 11g] Oracle Data Integrator And OBIEE 11g Integration

Oracle Data Integrator and OBIEE 11g Integration

Here we will see some of the connectivity options to OBIEE 11g using the JDBC driver. You’ll see based upon some connection properties how the physical or presentation layers can be utilized. In the integrators guide for OBIEE 11g you will find a brief statement indicating that there actually is a JDBC driver for OBIEE.


In OBIEE 11g its now possible to connect directly to the physical layer. In ODI 11g the Oracle BI technology is shipped with the product along with KMs for reverse engineering, and using OBIEE models for a data source.

When you install OBIEE in 11g a light weight demonstration application is preinstalled in the server, when you open this in the BI Administration tool we see the regular 3 panel view within the administration tool.

To interrogate this system via JDBC (just like ODI does using the KMs) need a couple of things; the JDBC driver from OBIEE 11g, a java client program and the credentials. In my java client program I want to connect to the OBIEE system, when I connect I can interrogate what the JDBC driver presents for the metadata. The metadata projected via the JDBC connection’s DatabaseMetadata  changes depending on whether the property NQ_SESSION.SELECTPHYSICAL is set when the java client connects.

Let’s use the sample app to illustrate. I have a java client program here that will print out the tables in the DatabaseMetadata, it will also output the catalog and schema. For example if I execute without any special JDBC properties as follows

•java -classpath .;%BIHOMEDIR%\clients\bijdbc.jar meta_jdbc oracle.bi.jdbc.AnaJdbcDriver jdbc:oraclebi://localhost:9703/ weblogic mypass


Then I get the following returned representing the presentation layer, the sample I used is XML, and has no schema;

Catalog                          Schema    Table


Sample Sales Lite              null        Base Facts

Sample Sales Lite               null       Calculated Facts

…        ...                    ...                  .....
Sample Targets Lite            null         Base Facts

 

Now if I execute with the only difference being the JDBC property NQ_SESSION.SELECTPHYSICAL with the value Yes, then I see a different set of values representing the physical layer in OBIEE;

•java -classpath .;%BIHOMEDIR%\clients\bijdbc.jar meta_jdbc oracle.bi.jdbc.AnaJdbcDriver jdbc:oraclebi://localhost:9703/ weblogic mypass NQ_SESSION.SELECTPHYSICAL=Yes

The following is returned;


Catalog                        Schema                 Table

Sample App Lite Data   null                    D01 Time Day Grain

Sample App Lite Data    null                   F10 Revenue Facts (Order grain)

…

System DB (Update me)

…


If this was a database system such as Oracle, the catalog value would be the OBIEE database name and the schema would be the Oracle database schema. Other systems which have real catalog structure such as SQLServer would use its catalog value.

Its this ‘Catalog’ and ‘Schema’ value that is important when integration OBIEE with ODI.

For the demonstration application in OBIEE 11g, the following illustration shows how the information from OBIEE is related via the JDBC driver through to ODI. 

 

In the XML example above, within ODI’s physical schema definition on the right, we leave the schema blank since the XML data source has no schema. When I did this at first, I left the default value that ODI places in the Schema field since which was ‘’ (like image below) but this string is actually used in the RKM so ended up not finding any tables in this schema! Entering an empty string resolved this.

 

Below we see a regular Oracle database example that has the database, schema, physical table structure, and how this is defined in ODI.

Remember back to the physical versus presentation layer usage when we passed the special property, well to do this in ODI, the data server has a panel for properties where you can define key/value pairs. So if you want to select physical objects from the OBIEE server, then you must set this property.

An additional changed in ODI 11g is the OBIEE connection pool support, this has been implemented via a ‘Connection Pool’ flex field for the Oracle BI data server. So here you set the connection pool name from the OBIEE system that you specifically want to use and this is used by the Oracle BI to Oracle (DBLINK) LKM, so if you are using this you must set this flex field.