Thursday, July 25, 2013

OBIEE Client Connectivity setup

If you are using OBIEE 11g client tools to create your own RPD or connecting to a Database to import metadata or trying to open the RPD online, you need to first configure the connectivity options for the OBIEE client.

You can connect to a DB source using either ODBC or OCI. If you want to connect via OCI, follow these steps.

  1. Install a Database client on your client machine. e.g. Oracle Database client for 11g. You can also install Oracle XE on your local machine.
  2. Make sure the database client libraries are available in the system path. If not, append this to the System path. Alternately you can set it in the bi_init.bat located at C:\Program Files\Oracle Business Intelligence Enterprise Edition Plus Client\oraclebi\orahome\bifoundation\server\bin

    OBIEEClient01

  3. Locate where your tnsnames.ora file is present (Usually you can find it in  D:\app\Administrator\product\11.2.0\client_1\network\admin).In the tnsnames.ora file create an entry for the DB you want to connect to. For example to connect to the local Database on my laptop, the entry is shown.
    ORCL11GRT = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl11grt) ) )
  4. copy the tnsnames.ora file from your Database location to the OBIEE client location. This is usually in C:\Program Files\Oracle Business Intelligence Enterprise Edition Plus Client\oraclebi\orahome\network\admin

    Note: If the network –> admin directories are not present, you must create them.

  5. Restart the BIAdminTool and try to import the metadata from the server mentioned in the tns entry. If the configuration is correct and there is no network problems, you should be able to connect and import the metadata in your RPD.

Wednesday, July 24, 2013

Reset sys password in Oracle DB

If you have forgotten the sys password for an Oracle Database, you can reset it by logging in as an OS user belonging to the dba group. Here is how to do it.

First you need to login to the Database server. In my case this was a Windows server and I logged in as the Administrator user though Remote Desktop.

Open a command prompt window and type in

C:\>sqlplus /nolog

you should get a sqlplus command prompt. type away the following commands to change the password.

SQL> connect /@POP as sysdba

SQL> alter user sys identified by newpwd;

the /@POP is to connect to the POP instance of the database. I had multiple instances on my Server.

“newpwd” is the new password for the sys user.

Monday, July 22, 2013

[nQSError: 17003] Oracle gateway error: OCIEnvNlsCreate or OCIEnvInit failed to initialize environment.

 

Happened to get this error out of the blue on a working OBIEE box.  Reason: somebody somehow messed up with the database client HOME path.

The exact error message:

[nQSError: 17003] Oracle gateway error: OCIEnvNlsCreate or OCIEnvInit failed to  initialize environment. Please check your Oracle Client installation and make sure the correct version of OCI libraries are in the library path.

  1. If you are getting this error when you are testing a test/production OBIEE box, check the oracle database home path is correctly set.
  2. If you are connecting through database client, check if the OCI client libraries path is  set.
  3. If you are using the BIAdmin Tool and trying to import  metadata using a connection pool, check the OCI library path is correctly set. If it is, then check if the BIAdmin tool was correctly initialized during startup. this script is called bi-init.cmd located in $INSTANCE_HOME\bifoundation\OracleBIApplication\coreapplication\setup

In my case it was because of the BIAdmin tool, initialization. you can manually run the initialization script in a Command Window and then start the BIAdmin tool from that Command windows –> like this:

C:\OBIEE11g_Home\Oracle_BI1\bifoundation\server\bin>C:\OBIEE11g_Home\instances\instance1\bifoundation\OracleBIApplication\cor
eapplication\setup\bi-init.cmd

C:\OBIEE11g_Home\Oracle_BI1\bifoundation\server\bin>admintool.exe

After this,  the problem disappears and you can import the meta data using the connection pool in the RPD using the BI Admin Tool.

If you don’t want to initialize BI Admin tool manually every time, On Windows systems jut make sure that the shortcut to BIAdmin tool is configured like this in the “Target” part.

C:\Windows\system32\cmd.exe "/cC:\OBIEE11G_HOME\instances\instance1\bifoundation\OracleBIApplication\coreapplication\setup\bi-init.cmd coreapplication_obis1 2 && C:\OBIEE11G_HOME\Oracle_BI1\bifoundation\server\bin\AdminTool.exe"

BIAdminTool_properties

Wednesday, July 17, 2013

OBIEE 11g Security part 1

Handling of security in 11g is very different from how it was done in 10g. The differences are due to the integration of OBIEE 11g with the Oracle Fusion Middleware. In OBIEE 11g you have to deal with users –> Groups –> Application Roles –> Application policies. To know more about these terms please refer to the Official Oracle docs.

Managing security is carried out using the following tools.

  1. WebLogic console –> manage users, groups and providers.
  2. Fusion Middleware enterprise manager –> mange Application Roles and Policies
  3. BI Administration Tool –> Manage RPD level security, Row Level Access, Data restriction, Logical column restriction, etc.
  4. Administration page in Analytics –> restrict features of OBIEE Analytics, Subject Area Access.

Discussing or covering up the entire topics on security is an overkill for a blog post and so I am going to cover it in parts. This part deals with row level access or showing different sets of data for different users based on their access rights.

Summary of row-level security access.

  1. Import the database table which has got information about the data access rights.
  2. create a session variable and define corresponding initialization block or row-wise initialization blocks in case multiple values have to be used in the filter.
  3. apply filter condition at the Application role level to restrict data.
  4. bounce the services and test the security access.

I’m making use of the BISAMPLE schema that is provided by Oracle.

create a database table that contains the mappings for the users and their access rights. In this case, I’m creating a table for users who have access to specific Regions. You can create this table in the BISAMPLE schema.

create table RT_USER_REGION ( username varchar(255) NOT NULL, region varchar(25) NOT NULL, CONSTRAINT userregion_unique UNIQUE(username, region) );

populate some data into this table.

insert into rt_user_region VALUES('rtuser1','AMERICAS'); insert into rt_user_region VALUES('rtuser2','APAC'); insert into rt_user_region VALUES('rtuser3','APAC'); insert into rt_user_region VALUES('rtuser3','EMEA');

create users rtuser1, rtuser2 and rtuser3 in the WebLogic administration console. create group called RTUSERS for these users and add all these users into this group. Using the enterprise manager, add this group to a newly created SalesPersonnel Application Role.

Open your RPD using the BI Admin Tool. Import this table into the physical layer. Also create a new connection pool to be used later for session variable initialization.security01

create a session variable USER_REGION and its corresponding initialization blocksecurity02

in the initialization block dialog, click ‘Edit Source Data’ button and select the connection pool you created earlier. give the default initialization string as

select region from rt_user_region where upper(username) = upper(':USER')

Note: the above query assumes that a single value is returned. If we have users assigned to multiple regions, then we should use a row-wise initialization block, I will show this at the end of this blog post. rtuser3 is specifically created for this purpose.

please see below the screen shot of the completed initialization block dialog.security03

In the BI Admin tool go to Manage –> Identity and select the Application Role you have created earlier.

security04

Double click or right click –> properties on the SalesPersonnel Application Role, then click the Permissions button.

security05

In the User/Application Role Permissions dialog box, click the green color ‘Plus’ Add button.

security06

Next, you will apply the filter: you can apply the filter at the presentation level or at the logical model level.

  1. If applied to the table at the presentation level,  the filter is applied only to the table that belongs to that specific subject area, even if you have multiple subject areas from the same logical model.
    1. If we apply the filter to the D3 Customers table (where the Region column is present), then the filter is applied to an analysis which contains columns from the D3 Customers table.
    2. If you want to filter the analysis even if no columns from D3 Customers was selected , then you should apply the filter to the associated fact table which in this case is F1 Revenue.
  2. If applied at the logical model level, then the filter will be applied to every subject area that uses that logical table from the model.

In this scenario, Apply the filter the Logical Model level, so that the access restriction is enforced throughout (and in any future analyses). Double click on D3 Customers or use the select button.

security07

define the expression by clicking the white area under the Data Filter heading clicking the edit expression button.

security08

use this expression.

"RTSales"."D3 Customers"."REGION" = VALUEOF(NQ_SESSION."USER_REGIONS")

 security09

Click on OK and finish the steps and save your RPD. Bounce the BI Services and test the data security access you just implemented.

logging in as rtuser1 will only show the Region values for AMERICAS. logging in as rtuser2 will only show APAC values.

security10

security11

Row wise initialization block:

If multiple regions are allocated to the Sales Personnel, then you will have to use the row wise initialization block so that the the multiple values returned by the query are available to be used as the filter.

follow these steps for using row-wise initialization block. This concept is also used in this blog post.

In the BI Admin Tool, go to Manage –> Variables. In the left hand side part of the dialog box select Session –> Initialization blocks.

create a new session initialization block by right clicking on an empty area on the right hand side and selecting the menu option.

click the ‘Edit Data Source’ button and select the connection pool as shown

security12

give the default initialization string as

select 'USER_REGIONS', region from rt_user_region where upper(username) = upper(':USER')

The variable is automatically defined in the ‘USER_REGIONS’ part of the query. The completed dialog box looks like this.

security13

if you login as rtuser3 you will be able to view regions APAC and EMEA.

security14