Troubleshooting Guide for MM

Oracle BI EE Resource

Overview

OBIEE (Oracle Business Intelligence Enterprise Edition) is yet another Business Intelligence (BI) tool. Metadata Manager can extract Metadata from Oracle BI presentation serverover http and the UDML file generated from the Oracle BI Repository RPD file.

Pre-requisites for Creating anOracle BI Resource

To load anOBIEE resource you would need to have the following setup:

Install Metadata Manager Agent on a Windows box where the Oracle Presentation server is accessible. Metadata Manager Agent Service has to be in “Started” state.

AnOBIEE user with Administrator Privileges is required to be used with Metadata Manager.

User should be able to generate the UDML from the repository RPD file using the nQUDMLGen utility.

Configuring anOracle BI Resource in MM 8.6.1

In the configuration screen of Metadata Manager for OBIEE Resource, you will need to provide the Following Information:

Agent URL: The URL of the Metadata Manager Agent running on the Windows Box where it can connect to the Oracle BI presentation server.

Source System Version: As of the MM 8.6.1 HF 3 release, only Oracle BI Enterprise Edition is supported.

Server URL: Enter the Presentation Server URL. The format of the URL is:

name>:<port>/analytics/saw.dll

The default port when Oracle BI uses tomcat is 9704 and the port when IIS server is used is 80. However the port can be configurable.

Login User:Enter the Oracle BI user name which the Metadata Manager will use to log in. Be sure this user name has permissions to the objects you wish to import. Metadata Manager recommends this user to have administrative privileges.

Login Password:Enter the password associated with the above user name which the MM will use to log in.

Repository Subset: This option is used to subset the amount of reports retrieved from the OBI Presentation Server.

Specify here a folder path, the bridge will import this folder, its child folders and all the reports contained in them. The root folder path is: / and the shared folder path is: /shared

File:The Oracle BI Administration tool natively stores metadata in a Repository RDP file.
The Oracle BI Server nQUDMLGen command line utility allows exporting the metadata to UDML format:

$OracleBIHome$\server\Bin\nQUDMLGen.exe -U Administrator -P Administrator -R paint.rpd -O paint.udml -N -Q -8

Metadata Manager uses the UDML file as input. Browse and Select this generated UDML file.

Example:

Oracle BI resource Configuration in MM:

Troubleshooting Oracle BI Metadata load failures

Incase of aOracle BI resource load failure in MM 8.6.1,

* Make sure the Metadata Manager Agent is running and the Agent URL entered is correct. You can verify this by entering the following URL in your browser:

* Check the Load Monitor log for the Oracle BI resource. The load could have failed either in MITI bridge extraction (done by MM Agent) or PowerCenter Workflows run by MM Service. If the Load has failed during the MITI extraction, identify the error in the Load Monitor “Log” tab (Error would be highlighted in RED). Search Knowledge Base for known error messages. Some known issues are discussed here.

* MITI.MIRException: org.xml.sax.SAXParseException: Invalid byte 1 of 1-byte UTF-8 sequence – This would occur if some characters, say which describe an object in the UDML file provided to MM Oracle BI resource have some special characters that are invalid to UTF-8. Please make sure to use the -8 option while generating the UDML file. This would ensure that the UDML file generated complies with UTF-8 character set. The UDML file has to be generated with the following command line options from the corresponding RPD file:

<Oracle_BI_Home>\server\Bin\nQUDMLGen.exe -U Administrator -P password -R <RPD_File_Location> -O <UDML_File_Location> -N -Q -8

*MITI.MIRException: [MBI_OBIEE_E0002] Failed to login as 'Informatica' on server ' - This error occurs when the Presentation Server URL is incorrect or the User credentials provided to login to Oracle BI presentation Server is incorrect.

Oracle BI presentation server URL should be of the format:

name>:<port>/analytics/saw.dll

To determine whether the URL is correct, you can try to access the below URL and try to login using the user account provided in the MM Oracle BI resource configuration:

*The Model is not consistent - If you observe any MITI validation errors or consistency errors, set off the MITI validation by following the below steps:

  • Open the file <PC_Home>/server/tomcat/shared/classes/IMM.properties
  • Search for the parameter names “MITI_VALIDATION_LEVEL” and set its value to “NONE”.
  • Save the file and restart the MM service.

This issue can also occur when objects in the source UDML file have extra information like "UPGRADE ID 2161732345" which MITIOBIEEBridge does not support as of MM 8.6.1 HF1. This problem can be worked around by re-generating the UDML file from the source RPD file. When using the nQUDMLGen utility, make sure to use the –N option as below:

<Oracle_BI_Home>\server\Bin\nQUDMLGen.exe -U Administrator -P password -R <RPD_File_Location> -O <UDML_File_Location> -N -Q -8

The "-N" parameter allows removal of the UPGRADE ID information.

* MITI.MIRException: [MBI_OBIEE_F0019] The UDML file does not correspond to the RPD used by the Presentation Server

- This error occurs when the UDML file used for configuring the OBIEE issue is not in sync with the data available in the configured Presentation Server. You can regenerate the UDML file from the RPD file currently being used by the Presentation Server.

The RPD file that is currently being used by the Oracle BI presentation server can be determined by looking into the configuration file:

<OracleBI_Home>\server\Config\NQSConfig.INI

This file would have the entry as below:

[ REPOSITORY ]

Star = samplesales.rpd, DEFAULT;

Here samplesales.rpd is the RPD file being used by the Oracle BI presentation server. The UDML file should be generated from this RPD file with the below syntax:

<Oracle_BI_Home>\server\Bin\nQUDMLGen.exe -U Administrator -P password -R <RPD_File_Location> -O <UDML_File_Location> -N -Q -8

* If the load has failed while running PowerCenter workflows, check for the session error in the “Session Statistics” tab of the Load Monitor logs.

* In any of the above cases, save the logs (PDF format) and send this to Informatica Support for further assistance. The log files (mm.log and mm_agent.log) can also be found the following location:

<PC_Home>/server/tomcat/temp/<MM_ServiceName>/logs/

(This is the default location but it can be overridden by editing the log4j.xml)

Required details to repro

In case of OBIEE load failures, we would need the UDML file used for the load and the corresponding RPD file from which the UDML file was generated.

If you see MITI errors, we may need to get the debug logs from the MM Agent installation. Enable debug logging as below:

On MM Agent installation, open <MM_Agent_Dir>/conf /mirsetup.xml

Search for “LogLevel” element and set it to 6 for DEBUG log. Eg: <LogLevel>6</LogLevel

MITI Agent log will be available in the location: <MMAgent_Install_Folder>\log\logService\

In case of any failures, please contact Informatica Technical Support and provide the things mentioned aboveto replicate the issue.

Known Limitations

Oracle BI Presentation Server uses secured HTTPS channel instead of HTTP

Metadata Manager does not yet work with the presentation server enabled to use the HTTPS security protocol. Presentation server should be accessible over a HTTP link.

How To WORK WITH ORACLE BUSINESS INTELLIGENCE

OBI Repository

OBI stores the repository in .rpd format. RPD is a binary file used by the BI Server to retrieve data from a source database. BI Administration tool is used to create and view the structure of the repository.

Creating a Repository:

Note:

  1. Have access to or have installed Oracle Database that contains schemas.
  2. OBI services (BI Server, BI Presentation Service) are running.

Importing Tables to Physical Layer

Steps:

  1. Go to Start → All Programs → Oracle Business Intelligence → Administrator.

The window will look like:

2. To create a new repository, go to File → New. Provide a name for the repository. Then press Save.

3. The window looks like the figure below that contains three sub windows namely: Physical, Business Model and Mapping and Presentation.

  1. To import a schema, click File > Import > from Database. ...

4. Provide the connection details(Data Source) to get the schema as follows and press OK.

5. Select the tables you want to import and press Import. The Connection Pool dialog box opens. Press

6. The Connection Pool dialog box opens. Leave the default settings and press Ok and close the Import dialog box.

7. All the imported objects present under Physical sub window. In the Physical layer of the repository, expand the GNAGARAJA schema folder and verify that the correct tables are imported.

8. Save the repository.

CREATING COMPLEX JOINS

1. Go to Manage Menu > Joins. Join Manager Window looks like:

2. Go to Action > New > Complex Join.

3. Specify a name for physical join and tables associated with the join.

Ex: Name: Dim_Join

Tables: DIM_CUSTOMER and DIM_CUSTOMER_ACTIVE

4. Mention the operator and joining attribute etc. Press Ok.

  1. Follow the same steps to create another complex join between DIM_EMPLOYEE and DIM_EMPLOYEE_EXPERIENCE. Press Ok.
  1. Close the Join Manager Window and save the repository.

Creating New Business Model

To create a new business model, perform the following steps:

1. In the Business Model and Mapping layer, right-click the white space and select New Business Model.

2. In the Business Model dialog box, name the business model GNAGARAJA and leave the Available for queries box unchecked. The Description edit box is used to add a comment for yourself or another developer.

3. Click OK to close the Business Model dialog. The new GNAGARAJA business model appears in the Business Model and Mapping layer. The red symbol on the business model indicates it is not yet enabled for querying.

We can create Logical Tables, Fields and Logical Joins.

For my example, I will drag the tables from Physical layer to Business Model.

Creating New Presentation LAYER CATALOG

1. Drag the GNAGARAJA business model from the Business Model and Mapping layer to the Presentation layer to create the GNAGARAJA catalog in the Presentation layer.

2. Expand the GNAGARAJA catalog in the Presentation layer. Notice that the tables and columns in the Presentation layer exactly match the tables and columns in the Business Model and Mapping layer.

3. Save the repository.

CONSISTENCY CHECK

Consistency check is a utility in the Administration Tool that checks if a repository has met certain requirements. Repositories and the business models within them must pass the consistency check before you can make business models available for queries. When a repository or business model is inconsistent, a detailed message alerts you to the nature of the inconsistency.

Steps:

1. Go to File → Check Global Consistency.

2. We will receive a message indicating that the repository is consistent and asking if you want to make it available for queries.

3. Click Yes to make the GNAGARAJA business model available for queries. The Consistency Check Manager displays.

4. If the Consistency Check Manager displays any Error messages, edit the repository to correct the inconsistencies and run the consistency check again.

If only Warning and Best Practices messages are displayed, we can ignore the messages for now and click Close.

5. In the Business Model and Mapping layer, notice that the GNAGARAJA business model icon has changed to indicate the business model is now available for queries (the red circle with a line is gone).

6. Save and close the Designer.

LOADING THE OBI REPOSITORY TO MM

Configuring Files

1. NQSconfig.ini

Update the Repository section of the initialization file, NQSConfig.ini. An entry in the Repository section of the initialization file instructs Oracle BI Server to load a specific repository into memory upon startup. If Oracle BI Server detects a syntax error while loading the repository, it logs the error to the server log file (...\\OracleBI\server\Log\NQServer.log).

  1. Navigate to ...\OracleBI\server\Config\ and open NQSConfig.INI with a text editor, such as Notepad.
  2. Locate the [REPOSITORY] section and change the entry to Star = NEW_REPO.rpd, DEFAULT;
  3. Save the changes and close NQSConfig.INI.

2. instanceconfig.xml

  1. Navigate to …\OracleBIData\web\config and open instanceconfig.xml with a text editor.
  2. Replace the section :

<CatalogPath>…/OracleBIData/web/catalog/samplesales</CatalogPath>

With

<CatalogPath>…/OracleBIData/web/catalog/NewFolder</CatalogPath>

NewFolder should have the name same as RPD file being used, without the extension .rpd. In our case it is New_Repo. That is:

<CatalogPath>…/OracleBIData/web/catalog/New_Repo</CatalogPath>

3. Save the file and close the editor.

Convert .rpd file into .udml

PowerCenter MM can read OBI data only from .udml format. Therefore, we need to convert rpd file to udml manually using nQUDMLGen command.

Steps:

  1. Open the command prompt and navigate to ...\OracleBI\server\bin directory.
  2. Execute:

nQUDMLGen -U userid [-P [password]] -R repository_pathname -Output_script_pathname [-8] [-N] [-Q] [-S]

Ex: nQUDMLGen -U Administrator -R C:\OracleBI\server\Repository\New_Repo.rpd
-O C:\OracleBI\server\Repository\NEW_REPO.udml -8 -N –Q

Note: Always provide -8 –N –Q options.

Also, reverse operation i.e. from udml to rpd can be done using the command;

nQUDMLExec.exe -U [userid] -P [password] -I input_script_pathname -O output_repository_pathname -8

3. udml file generated at the specified location.

Start Oracle BI Services

In this step, you start the Oracle BI Server service to load the SH repository into memory.

1. Select Start > Programs > Administrative Tools > Services.

2. Start the Oracle BI Server service. Verify that the Oracle BI Presentation Server and Oracle BI Java Host services are started. If they are not started, start them. It is not necessary to start the Oracle BI Scheduler or Oracle BI Cluster Controller services. The services can be started in any order.

3. Minimize the services window.

Status of MM Agent

  1. Start the MM Agent installed in the system.
  2. Status of MM agent can be checked using the URL: (case-sensitive)

Hostname is the name of the system where MM Agent is running.

Default portno will be 19983 for installed MM Agent or MM service Portno+1.

This URL will look like:

Status of Presentation Server

We can also check the status of OBI Presentation server using the following URL:

Hostname is the name of the system where OBI services are running.

Portno is 9407 for Tomcat configuration and 80 for IIS. (During installation).It can be configured.

This URL gives the login screen, like:

LOADING

  1. Open the Metadata Manager where you want to load the OBI resource. Login as Administrator (or any privileged user).
  2. Click create, load and schedule Resources.
  3. Click on New Resource.It will show:
  1. Select Oracle Business Intelligence under Business Intelligence. Click Next.
  2. Provide Name for the resource and its description which is optional. For example, give name an NEW_REPO. Click Next.
  1. Provide the configuration details like Agent URL, Server URL, Login user and Password etc. For File, select the udml file (NEW_REPO.udml). Click Next.
  1. Check No Schedule option and press Finish.
  1. The resource will be added to the list with Not Loaded Status.
  1. Select the resource and click Load which will start loading the resource(status: Load Request in Queue, then Load in Progress).

10. Finally, status will be Load Successful; Indexing Successful.

Command to convert .rpd file into .udml file

OBU has nQUDMLGen utility that allows to convert from RPD to UDML formats. Here its usage with standard OBI sample, Paint: $OracleBIHome$\server\Bin\nQUDMLGen.exe -U Administrator -P Administrator -R paint.rpd -O paint.udml -N -Q -8

To archive an entire Presentation Catalog or an individual catalog folder in thePresentation Catalog

  1. Log in to Oracle BI Presentation Services.
  2. Choose Settings > Administration.
  3. Click the link Manage Presentation Catalog. The Manage Catalog screen appears.
  4. If you want to archive a catalog folder (and all its subfolders) rather than the entire catalog, navigate to the folder.
  5. If you want to archive the:

■Permissions that are assigned to each item or folder, select the Keep Permissions checkbox.

If you do not select this option, the archiving process will not include any permissions. Upon unarchiving, the system will assign the parent folder’s permissions to all of the items and folders.

■Timestamps that are assigned to the item and folder that you are archiving, select the Keep Timestamp checkbox.

If you do not select this option, the archiving process will not include timestamp information.

Upon unarchiving, the system will apply a timestamp indicating the time at which the itemor folder is unarchived.

  1. Click the Archive Catalog button.The File Download dialog box appears.
  2. Click Save.The Save As dialog box appears.
  3. Complete the Save As dialog box and click Save.

Reference:

Creating and configuring Repository:

OBI Installation Steps in brief:

1