OBIEE Analysis Date Format as 0/0/0 [Solved]

Issue: We recently came across an error where client raised a defect that date comes up as 0/0/0 for some of the records.

Analysis: First we checked the particular records in the EDW target schema. We could see that the records are having date as null in the physical tables.

Cause: The root cause of this defect is that in the physical layer of the RPD for this particular column the nullable flag is not ticked. Since the nullable flag is not ticked, OBIEE expects a value here and substitutes 0/0/0 12:00:00 AM for null values.

The nullable flag property in OBIEE RPD Physical column object.

This could also happen if you manually change the data type from datetime to date. OBIEE imports date as datetime by default.

Resolution: Change the data type to datetime and tick the nullable flag. Once done, save and upload RPD to the server. Now clear your cache, reload files and metadata and check again. If that still doesn't change anything then do a restart of your presentation service.

OBIEE Analysis View Display Error Maximum cells exceeded Error EY692ZW9 [Solved]

Issue: When trying to export a OBIEE Analysis to 

View Display Error Maximum total number of cells exceeded (Configured Limit: 50000). Error Codes: EY692ZW9

Cause: Max rows parameter and tag not set at instanceconfig.xml file.

Solution: In the instance config file at 

/user/app/oracle/product/fmw/12.2.1/user_projects/domains/bi/config/fmwconfig/biconfig/OBIPS add a sub tag under <pivot> and <table> tags.

Set this parameter accroding to your requirement. We wanted to take some very big excel files and hence set the same as below,

<MaxCells>Value</MaxCells>

For the same to get effected you must once restart the BI Presentation service from Enterprise Manager. 

OBIEE 12c Analytics Login Authenitcation Failure - NQODBC nQSError: 10018 [SOLVED]

This is one of the issues that took up a lot of time of ours.

Issue: OBIEE throws the following error when trying to login to analytics using weblogic username and password.

[OBIPS] [NOTIFICATION:1] [] [saw.securitysubsystem.checkauthentication.runimpl] [ecid: 667216e2-5177-4c25-86d2-b97ad8d56fa7-000002b2,0:1:1] [tid: 1830131456] [SI-Name: ] [IDD-Name: ] [IDD-GUID: ] [userId: ] Authentication Failure.

Odbc driver returned an error (SQLDriverConnectW).

State: 08004.  Code: 10018.  [NQODBC] [SQL_STATE: 08004] [nQSError: 10018] Access for the requested connection is refused.

 (08004)

State: HY000.  Code: 43113.  [nQSError: 43113] Message returned from OBIS.

 (HY000)

State: HY000.  Code: 43126.  [nQSError: 43126] Authentication failed: invalid user/password. (HY000)[[

File:checkauthentication.cpp

Line:1534

Location:

saw.securitysubsystem.checkauthentication.runimpl

saw.threadpool.asynclogon

saw.threads

]]

Cause: We spent a lot of time trying to find the root cause of the issue. We checked metadata schemas, boot.properties, multiple restarts, created new users and many others. The actual cause was that we have two LDAP's defined, one default weblogic LDAP and the other client LDAP server. 

The problem was that the Client LDAP server was throwing a connection error while trying to connect. The LDAP server administrator credentials were changed.  

The authentication process first checks credentials against client LDAP. Which refused the connection and hence the error,

Code: 10018. [NQODBC] [SQL_STATE: 08004] [nQSError: 10018] Access for the requested connection is refused.

Resolution: Update the Client LDAP providers with the latest credentials and then restart the server. 

Another work around is to change the client LDAP provider control flag to optional.

OBIEE dashboard prompt not working after passing correct data value [Solved]

Issue: OBIEE Dashboard prompt not filtering report correctly despite passing same data value.

Cause: The issue was that the actual database column say for eg: X_STATUS was having trailing spaces in database. In OBIEE when you create a dashboard prompt from this column, OBIEE automatically does a trim of the data and presents it to you.

To make things simple, imagine I am having a status 'Accepted     '  in database. Note that there are 5 trailing spaces in this column in the physical table. When you create a dashboard prompt the trailing spaces are removed and shown inside prompt. So the data in the prompt is 'Accepted' instead of  'Accepted     '. Now what happens when you prompt is that the filter passed in physical SQL would be as below

SELECT X_STATUS FROM TABLE_NAME WHERE X_STATUS='Accepted'

Now when the query reaches the table, X_STATUS column is searched for 'Accepted' instead of the original data with 5 trailing spaces. Hence a match is not found.

Resolution: Issue debugging was followed in steps:

First we checked if the dashboard prompt presentation column and report filter column was the same. It was the same.

Next we checked the physical query log and found the query passed to database. Checked for a cache metadata hit. Next we took the query and executed in database. This gave as no result as we explained earlier.

Next we did a DISTINCT on the X_STATUS column. Which gave us all distinct values, from the value we copied the Accepted data column and pasted it inside two single quotes (''). If there are spaces in the data the quotes will not end after the last letter.

The solution is to do a trim on the column in OBIEE report filter or the better solution is to do a trim on the actual physical column in Data warehouse.

Lesson Learned: The lesson learned was that when you create a dashboard prompt. OBIEE automatically does a trim function on it.

Replace NULL with Decimal 0.00 in OBIEE Pivot Table

Issue description: Replacing null code for pivot table not showing 0 with decimal places. 

Solution: As you all know the solution is very widely known to all. It is as simple as going to the corresponding measure, click on properties, go to Data Format, select custom format and paste in the following code,

#,##0;-#,##0;0

The problem with the above code is that it will not help you show decimal places, to show decimal places all you have to do is to change the code as below,

#,##0;-#,##0.00;0

note that I have replaced the second 0 as 0.00 which will make sure that data in the column will have two decimal places. Again places where there are null this doesn't apply and will show up as 0 instead of 0.00. So to correct this all you have to do is to replace the value after the second ; as 0.00 as below,

#,##0.00;-#,##0.00;0.00

This will ensure that null values are replaced by 0.00 and not 0. Also not null values will have two decimal points. 

Let us know if this worked out for you. Works well for OBIEE 12c. 

What is the OBIEE RPD File and it's Significance ?

The RPD file or Repository is the heart and soul of an OBIEE instance. We discussed earlier in one of our previous posts that Oracle BI has two kinds of metadata, one that exist in database tables and the other which exists in file format. The RPD file is the one of the OBIEE metadata that exists in physical file format, the other being the presentation catalog. 

What does the OBIEE RPD contain ?

The OBIEE RPD is where all the metadata related to a data warehouse or a data source is stored. It basically contains all the OLAP tables which are used to populate graphs and charts, the physical joins between the data. The star schema design/BMM design which tells the BI Server how OBIEE server should fire the query to the data warehouse/database.

The Repository is mainly composed of three layers:

1.Physical Layer 

 This is where you import all your Data warehouse tables. You create your database connection here along with the credentials to connect to the warehouse schema. You can also specify options like connection pooling, timeout option and various other database parameters.

Here you create Alias Tables for all the tables and define the joins between these tables. The joins defined here are inner joins. You can safely say that things relating to the database resides here. 

2.Business Model and Mapping(BMM) Layer

The BMM layer or Business Model and Mapping Layer is where you define the relation between the tables that you had earlier imported to your Physical Layer. Here you define your BMM diagram,  create hierarchies for your dimensions define complex joins between your database tables, set aggregation for measures.  You can also define complex calculations to facts here, however it is recommended to push all complex calculations to database or during the data load to the data warehouse.

Inside the RPD, arguably this is the most important layer as this is where each business process is defined in a way that OBIEE server understands. It is very crucial that the BMM model is designed properly for a compact BI Analytics implementation.

3. Presentation Layer

The presentation layer is what the end user or the BI Author user would see when they log in to the Analytics. The BI Author or the report developer pulls data from this layer using the web UI. It is important that the dimensions and facts are properly organised in this layer so that somebody developing the report would be able to easily pull in columns and build a report.

RPD Location - You can find the RPD file under the Middleware Home folder in your OBIEE server. The exact location to find the RPD file is as below,

MW_HOME/instance/instance1/bifoundation/OracleBIServerComponent/Repository/

In this folder you will be able to find the Repositories that have been deployed over time. To get the currently used RPD name go to Enterprise Manager > CoreApplication > Deployment Tab.

Which component of OBIEE manages the RPD file ?

It is the OracleBIServer component which comes under OracleBI11g components that handles and communicates to the repository file. Every time a request comes from the Web UI(Presentation layer). The OBIEE server refers to the metadata residing in Repository file to convert the same to a Physical SQL query. The data from the SQL query is sent back to analytics for use in reports.

What does the term RPD file refer to in OBIEE 11g ?

If you are new to OBIEE, you might be wondering what the term RPD stands for. Most developers refer to RPD as the repository file, while some people refer to it as Rapid File Database. There are also other names like Repository Design, Repository Dictionary etc. The truth is that there is no definition for the abbrevation in any Oracle documentation. However the most commonly used definition would be Rapid File Database.

Sample App Lite RPD that comes default with OBIEE Installation.

The RPD file or the repository file is the physical metadata file for OBIEE server where relationship between Datawarehouse tables and their related objects are stored. 

BEA-090402:OBIEE Boot Identity Not Valid - Authentication Denied [SOLVED]

Issue: We faced the issue on a Linux Server when trying to Start Weblogic Script. 

<Authentication denied: Boot identity not valid; The user name and/or password from the boot identity file (boot.properties) is not valid. The boot identity may have been changed since the boot identity file was created. Please edit and update the boot identity file with the proper values of username and password. The first time the updated boot identity file is used to start the server, these new values are encrypted.>

Cause: This issue could be because the credentials inside the boot.properties file is not valid. This credentials could change while stopping the weblogic server and you are prompted to give password here and if you give the wrong credentials during shutdown it will be written to the boot.properties file.

Issue in Terminal Window

Resolution: The issue can be resolved by changing the password in the boot.properties file and restarting again.

What is RCU in OBIEE 11g ?

The RCU or the Repository Creation Utility is a small application that comes with OBIEE 11g Installation files. The purpose of the RCU is to load the two Operational Metadata schemas for OBIEE Installation into an RDBMS system, 

• BIPLATFORM Schema
• MDS (Metadata Services) Schema

It basically runs a list of .SQL scripts that create new tables to store OBIEE operational metadata which will later be used by the Oracle BI 11g Instance. 

It makes life easier by running the table creation metadata so that user doesn't have to go through the pain of executing scripts in RDBMS one by one. 

[BIP 11g] Restarting/Stopping BI Publisher 11g Services

Here is how to restart BI Publisher 11g Services from Console. 

Go to console, if you are inside the server just open localhost:7001/console or just open hostname:7001/console. Once inside the console from the domain structure panel on the left side select 'Deployments' as seen below.

Go to bifoundation_domain > Deployments

 Next select BI Publisher from the list of services listed 

 

Next go the tab on the top with 'Start' and 'Stop' buttons and Click on Stop > Force Stop Now

Confirm the action

Check the BI Publisher URL to confirm that  BI Publisher is down. 

Once confirmed, Select 'bipublisher' and select Start > Servicing all requests .

 

Now check the BI Publisher URL again,

Import Cloudera Impala Tables to OBIEE Repository [Solved]

Recently we had an opportunity to work on a demo where the OBIEE data source was Cloudera Impala. So had to create a new ODBC source to import Impala tables to OBIEE repository. So here are the steps.

First you need to install the ODBC Driver for windows from Cloudera's Page.  Now install the Driver 

Installing is basically a very easy task. Advisable to gracefully stop BI Services before Installing as the Installer might ask you to stop Oracle Application Server.

Check for Sample DSN Conneciton

 Once you are done with ODBC driver installation go to run > ODBC and check if you have an entry named 'Sample Cloudera Impala DSN' to confirm that the driver Installation has successfully completed. 

Now click to add a new System DSN and select Cloudera Impala Driver as seen in the image below,

Select Cloudera Impala Driver from the list of drivers

Next provide connection details to your Cloudera Impala Server as seen below.

Default port for Cloudera Impala is 21050. Once you are done giving the details for the connection click on test to test connectivity as seen below,

 Now go to the OBIEE repository and Import metadata as shown below, Select the recently created Impala ODBC data source and proceed.

 

 

Select the Impala Tables to imported and Click Finish. Check the Physical Layer of the repository to see if the Impala Table has been successfully imported. You can use view data to feature to confirm the connection correctly.  

Table in Physical Layer of OBIEE Repository

 Tags: Big Data Cloudera Impala Server ODBC connectivity with OBIEE and BI Tools.

OBIEE: What is a Driving Table and when is it preferred ?

A driving table is a feature available in OBIEE using which one can improve performance of cross database joins. However this feature is very limited and can be counter productive. 

Here is how Oracle defines a Driving Table,

Driving tables are for use in optimizing the manner in which the Oracle BI Server processes cross-database joins when one table is very small and the other table is very large. Specifying driving tables leads to query optimization only when the number of rows being selected from the driving table is much smaller than the number of rows in the table to which it is being joined. 

When you specify a driving table, the Oracle BI Server will use it if the query plan determines that its use will optimize query processing. The small table (the driving table) is scanned, and parameterized queries are issued to the large table to select matching rows

This feature is available in the BMM(Business Model and Mapping Layer) of repository where you define a logical join between two tables, Here you will be able to see an option called Driving Table where you can select one of the two tables in the join. Make sure you are selecting the table with the less number of rows, usually preferred when data in one of the tables is very less ( <1000) and the other table is very large. 

Driving Table feature in Logical Join of OBIEE RPD.

Reference: Driving Table - Oracle 

What are Alias Tables and It's significance in OBIEE Physical Layer

An Alias Table or Alias in OBIEE is a physical table which references to a different physical OLAP/OLTP table as it's source.

Alias tables are often used for Repository development over Physical tables as they have the following properties,

1. Enables reuse of a physical table.
2. Avoids circular joins that can form when importing OLTP source tables.
3. Create self joins between tables, for eg: Manager is also an Employee.
4. Enables to easily identify source tables and adhere to data warehousing naming standards.
5. Makes physical query generated easier to understand.
6. Can override source table's caching properties like Cacheable, Cache never expires and cache persistence time. 

Alias Tables in OBIEE 11g - Example.

How are OBIEE Metadata files stored ?

The metadata for an OBIEE Instance is stored both as Database tables and as Physical files.

Metadata in Database tables are stored in the Metadata Schema's

MDS (Metadata Services Schema)

BIPLATFORM 

The Physical metadata files for OBIEE are the

• Repository (RPD)
• Catalog Files

The repository file can be found at the following location and will have the extension .rpd. 

MW_HOME/instance/instance1/config/OracleBIServerComponent/coreapplication_obips

The currently active RPD name can be checked from EM or from the instanceconfig.xml file. The repository file contains the details of Physical Tables, Physical and logical mappings, Presentation tables etc. 

The catalog files are located the physical directory below,

MW_HOME/instance/instance1/config/OracleBIPresentationServiceComponent/coreapplication_obips1

The catalog contains Metadata for reports,prompts, dashboards etc and their permissions. The physical directory for the latest catalog displayed can be found from the EM.

What is OPMN and it's functions ?

OPMN stands for Oracle Process Management and Notification System that acts as a management service for different oracle components. In an OBIEE instance, the OPMN is responsible for controlling the different Oracle BI 11g components.

Functions of OPMN are the following

• Start and stop the OBIEE components separately or at once. 

Oracle BI 11g system components

• Restart an OBIEE component upon failure.

The different Oracle BI components that OPMN works on are the following

1. BI Presentation Services
2. BI Server
3. BI Schedulers
4. BI Cluster Control
5. BI JavaHosts

The OPMN batch file can be accessed from the below location from the Middleware Home folder.

FMW_HOME/instances/instance1/opmnctl.bat

You perform the following operations using the opmnctl batch file,

1. Start All
2. Stop All
3. Start Procedure
4. Restart Procedure 
5. Stop Procedure
6. Status

eg CMD scripts:

• Start all - opmnctl startall
• Stop all - opmnctl stopall
• Startproc - opmnctl startproc ias-component=OracleBIServerComponent
• Restart process - opmnctl restartproc ias-component=OracleBIServerComponent
• Stop process - opmnctl stopproc ias-component=OracleBIServerComponent
• Status - opmnctl status You can also invoke the OPMN services from Enterprise Manager.

Find version of Weblogic Server

There are multiple ways to check the version of a Weblogic Server.

Here is how to check the version of Weblogic Server even if the server is down or up.

Weblogic Server Down

Go to the MiddleWare Home folder in your system/server. Then inside the MW_HOME folder go to the folder that starts with wlserver and inside the folder go to .product file

Go to Weblogic Folder inside MiddleWare home and open the .product file.

Note: Make sure you enable 'show hidden files' option in windows. 

Check for the entry WLS_PRODUCT_VERSION.

Inside the .product Properties file check for WLS_PRODUCT_VERSION and it's corresponding entry, In our case it is 10.3.5.0 

Weblogic Server Up

When the weblogic sever is up and running, we can check the version by going to the console appilication at http://SERVERHOSTNAME;7001/console and at the right hand bottom corner you will be able to see the version of weblogic as in the screenshot below, 

Oracle BI Applications hardware requirements document

Scenario: We were looking out for a document to get the minimum recommended hardware configuration for OBIA 11g. Since this was for a demo purpose we required to install OBIA Apps and target database/data warehouse in the same node. 

This is from a technical note we came across in Oracle Support.

OBIA Apps 11g

Target Database

Link to the technical note: - https://goo.gl/M38toV 

[OBIEE 11g] Find Weblogic Nodemanager Port Number for OBIEE 11g

For Enterprise OBIEE Installation the default port number is 9556. There are two methods to find the Nodemanager port number,

1. From Weblogic Console
2. From MW_HOME location

The Nodemanager port number can also be checked from the console application, After logging into console go to the following path,

Home >Summary of Servers >bifoundation_domain >Summary of Machines >MACHINE_HOST_NAME

Path to check Node Manager details

Listen port is the Node Manager Port Number

To confirm the same, follow these steps

1. Open Middleware Home folder
2. Open Oracle_BI1
3. Open the install folder
4. Open the ports.properties file and check for the below entry,

NODE_MANAGER_PORT=9556

The port which the NODE_MANAGER_PORT file is referring to will be the port used by Weblogic Nodemanager. You can also find more entries in the ports.properties file. 

OBIEE Interview: Why is OCI preferred over ODBC in RPD ?

Question: Why is OCI preferred over ODBC in OBIEE RPD? 

Answer: 

OCI is preferred over ODBC only when the source database system is Oracle since OCI stands for Oracle Call Interface and is a native driver for Oracle database connections. You can go for an ODBC connection for the same as well because ODBC stands for Open Database Connectivity. 

Since OCI is a native driver for Oracle, using an OCI connection over ODBC improves performance while connecting to an Oracle datasource from OBIEE RPD. OCI also provides access to many native database features otherwise not accessible from ODBC.

Question: Is an OCI connection preferred when connecting to a Database other than Oracle?

Answer: 

No. It is not possible. Since OCI is a native driver to connect to Oracle Datasource it can't be used to connect to data sources other than Oracle.

Links to read more:

1. http://twobiee.blogspot.in/2012/10/odbc-or-oci.html
2. http://obieemanu.blogspot.in/2011/01/odbc-vs-oci.html

[Oracle11g] Set Oracle Database User Account and Password to Never Expire

Here is a small tip on how to create a new user in Oracle 11g and set the password to never expire. This would come in handy when you are creating new metadata schema's for OBIEE, when for long time these schema's are not in use they tend to get locked and OBIEE services will fail to start.

So in order to avoid this scenario it is better to create a new Oracle user profile and set the parameters to unlimited. Please follow the steps below

After you create a new Oracle user, say for example OBIUSER then the next step is to create  a new User Profile OBIUSER_PROFILE as below

CREATE PROFILE OBIUSER_PROFILE LIMIT
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
PASSWORD_LIFE_TIME UNLIMITED
FAILED_LOGIN_ATTEMPTS UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_GRACE_TIME UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED;

If you check the DDL statement above you can see that we have set properties like PASSWORD_LIFE_TIME, PASSWORD_LOCK_TIME, FAILED_LOGIN_ATTEMPTS to unlimited. Hence all the users having this user profile will never get expired. 

The next step is to assign this new User Profile characteristic to the new user OBIUSER,

ALTER USER OBIUSER PROFILE OBIUSER_PROFILE;

Now this user/schema password will never expire. 

References: Oracle Docs