Thursday, June 20, 2013

Setting Up Usage Tracking in Oracle BI 11g

Setting Up Usage Tracking in Oracle BI 11g Part 1

I have seen several post in OTN. How to implement Usage Tracking in OBIEE 11g( Here am providing step by step process for Usage Tracking implementation. Please feel free your comments on this post.

Step 1:

You have check for the existence of the S_NA_ACCT Usage Tracking table in the database Repository Creation Utility (RCU) schema. Please verify the table in data base like below.

Step 2:

Creating the Database Object in the Physical Layer

Open satyawrite_BI0003.rpd in offline mode in the BI Administration Tool. 

Step 3:
Right-click inside the Physical layer white space and select New Database to open the Database properties dialog box

Click the General tab and name the database Satya Usage Tracking and select In the Database drop-down list, select Oracle 11g.

Click the Connection Pool tab and click Add to open the Connection Pool dialog box.

Name the connection pool Usage Tacking Connection Pool and Enter the data source name, username, and password. In this example, the following are entered:


User name: dev_biplatform

Password: admin1234

Right-click the Satya Usage Tracking database object and select New Object>Physical Schema and Name the physical schema Usage Tracking Schema and then click OK.

Right-click the Usage Tracking Connection Pool, select Import Metadata, and import the dev-biplatform.S_NQ_ACCT table into Satya Usage Tracking.

The dev_biplatform schema is added under Satya Usage Tracking.

Creating the Business Model Layer for Usage Tracking:

Right-click in the Business Model and Mapping layer white space and select New Business Model (Satya Usage BMM) then Right-click Satya Usage BMM and select New Object>Logical Table.

Name the table Measures and click OK.

Repeat and add three more tables - TimeTopic, and User to the Satya Usage BMM  business model.

Drag the following three physical columns from Satya Ranki Reddy Usage Schema>S_N_ACCT to the Measures logical table in the Satya Usage BMM to business model. For each column, right-click the logical column and select Rename, and then rename as follows.

Physical Column
Query Count
Row Count
Total Time Seconds

Physical Column
Start Date
Start Hour Minute
End Hour Minute

Physical Column
Logical SQL
Subject Area

Right-click the column Query Count and select Properties and Click the Aggregation tab and select Count from the Default aggregation rule drop-down list. Click OK.

Similarly, set the Aggregation rule for other logical columns in the Measures logical table as follows:

Logical Column
Aggregation Rule
Row Count
Total Time Seconds

Set Start Date as the logical key for the Time logical table. Right-click the Time logical table and select Properties.

Similarly you have to set Logical SQL as the logical key for the Topic logical table and Set User Name as the logical key for the User logical table.

Use the Business Model Diagram to create logical joins from Measures to Time. Right-click the Satya Usage BMM to business model and select Business Model Diagram>Whole Diagram --> Click the New Join icon from the menu and create logical joins from Measure to TimeTopic, and User as shown below.

Drag the Satya Usage BMM business model to the Presentation layer to create the Presentation layer objects. Rename the subject area to Satya Usage BMM.

Please see the next article for Oracle Enterprise Manager (EM) steps :  Usage Tracking Part 2

Satya Ranki Reddy

Setting Up Usage Tracking in Oracle BI 11g Part 2

Setting Up Usage Tracking in Oracle BI 11g Part 2

Here am going to providing, configure usage tracking through Oracle Enterprise Manager (EM) by performing the following steps.

Log into EM. In this example, the user name is satya and the password is admin123.

Select WebLogic Domain>bifoundation_domain from the left navigation pane then Click the WebLogic Domain drop-down list in the right pane.

Select System MBean Browser from the list and Expand Application Defined MBeans>oracle.biee.admin.

Expand bifoundation_domain then Lock the domain to update it. Expand BIDomain and select the BIDomain MBean where group=ServiceNote: Expand Show MBean Information in the right pane.

'Click the Operation tab then Click the Lock link. On the Operation:lock page, click the Invoke button.

Click the Return button then Expand BIDomain.BIInstance.ServerConfiguration, and then select the BIDomain.BIInstance.ServerConfiguration MBean.

Click the Attributes tab. Scroll down and click UsageTrackingCentrallyManaged. Set Value to true in the drop-down list then Click the Apply button. When you see the confirmation message for the update, click the Return button.

Similarly, set the UsageTrackingEnabled attribute to true to enable usage tracking and Set the UsageTrackingDirectInsert attribute to true to enable direct insertion.

Set the UsageTrackingPhysicalTableName attribute to the name of the fully qualified database table for collecting query statistic information, as it appears in the Physical layer of the Oracle BI repository. In this example, it is set as follows:

"Satya Usage Tracking"."Satya Ranki Reddy Usage Schema"."S_NQ_ACCT".

Set the UsageTrackingConnectionPool attribute to the name of the fully qualified connection pool for the query statistics database, as it appears in the Physical layer of the Oracle BI repository. In this example, it is set as follows:

"Satya Usage Tracking"."Usage Tacking Connection Pool"

Note: For usage tracking insertions to succeed, the connection pool must be configured with a user ID that has write access to the back-end database. Also, it is recommended that the connectivity type support international data.

After applying your changes, release the lock on the domain, as follows:

Return to the BIDomain MBean where group=Service under oracle.biee.admin, Domain:bifoundation_domain, BIDomain.
Click the Operations tab.

Click one of the first commit operations.

Go to the Oracle Business Intelligence Instance page and click Restart All on the Availability>Processes tab.

Creating and Running to Populate Usage Tracking Table:

Login to OBIEE answers then create report and check the usage tracking functionality.

Create and run the following analysis in the Satya Usage BMM subject area:

And see the result.

Usage Tracking implementation Part 1:   Usage Tracking Part 1

Good Luck !!!.

Satya Ranki Reddy.

Wednesday, June 19, 2013

Date format converting using Evaluate function in OBIEE 11g.

I have seen several posts in OTN. How to change date format using evaluate function in OBIEE 11g. Oracle by default date column and in OBIEE display everything including the minutes and seconds, users usually asks to remove that. Client will ask to sometimes display only the month in a more and only day format not required time stamp.

Here am providing one of solution in the repository using Evaluate function and the popular to_char function.

Solution 1:  Displaying date Year and Month.

While applying evaluate function in answers level in OBIEE 11g you will get below error.

Formula syntax is invalid.
[nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. EVALUATE_SUPPORT_LEVEL inside NQSConfig.INI is not set to support EVALUATE. (HY000)
SQL Issued: SELECT cast(Evaluate('to_char(%1,%2)',"D_CALENDAR_DATES"."DATE_TIME"."CALENDAR_DATE",'YYYY-Mon') as varchar(10)) FROM "PROD_EDW"
OK (Ignore Error) 


You have to set the EVALUATE_SUPPORT_LEVEL in NQSConfig.INI file like below.

 Before modification:

After Modification:

You have to restart BI services like below.

Then you can check in the report level.

1. I am going to apply evaluate function in report level.

Click ok and click the result tab.

Satya Ranki Reddy

Date Format Change

Custom Data Format for dates in OBIEE 

I have seen lot of post in OTN forum, How to change Date format in report in OBIEE 11g .By default the date is displayed as it is specified in user’s locale definition file which contains setting for displaying language, date format etc. We can change that using Custom Data Formatting.


In my report data for date column like below.

 Step: 2

Change the Custom Date Format to DD-MMM-YYYY. The results are displayed as following:

We can separate the date using characters such as slash (/) or hyphen (-).  Here is a short list of formats used for date display:

Date in the locale's short date format
Date in the locale's long date format
Day of the month in two-digit format (01)
Abbreviated name of the day of the week in the user's locale (Mon for Monday)
Full name of the day of the week in the user's locale (Monday)
Numeric month (1 for January)
Numeric month in two digit format (01 for January)
Abbreviated name of the month in the user's locale (Jan)
Full name of the month in the user's locale (January)
Year in two-digit format (01 for 2001)
Year in four- digit format (2001)

Using Evaluate function method for change  the date format see this post.


Satya Ranki Reddy

Monday, June 17, 2013

How to reset forgotten OBIEE 11G RPD password

How to reset forgotten OBIEE 11G RPD password

I have seen several posts in OTN. How to reset the rpd password in OBIEE 11g. Here am providing step by step process to get rpd password in OBIEE 11g.


In 10g reset password petty much easy (10g Reset the rpd password), but in 11g you can't reset the password.For the context of this blog post, I would like to show you how to know the forgotten password of a deployed repository. Here am providing wonderful solution please try this and give me your comments on this post.

Login to Weblogic EM like below.

Step 2:  Navigate  to credentials store page:-

Step 3:
Expand the -- This is the key map which stores all the repository passwords that are deployed.

I have forgot the password is satyawrite_BI0003.rpd, I want to know the existing password in this rpd. Please follow the steps am sure you will get the your password.


Open the command mode:

Login to below path:

C:\Users\redsatya\Oracle>cd \

C:\>cd Oracle\Middleware\oracle_common\common\bin



Execute the below command then you will get web logic offline mode like below.


Step 6:

You have to connect weblogic offline mode using below command .

Ex:  wls:/offline> connect("weblogic","weblogic123","redsatya212:7001")
Connecting to t3://redsatya212:7001 with userid weblogic ...

Once executed successfully the above command then you will get message like below.

Successfully connected to Admin Server 'AdminServer' that belongs to domain 'bif

Warning: An insecure protocol was used to connect to the
server. To ensure on-the-wire security, the SSL port or
Admin port should be used instead.

Step 7:

Execute the listCred () command to get the password of satyawrite_BI0003 repository

Ex:   wls:/bifoundation_domain/serverConfig> listCred("","reposito

Once executed the above command in command mode you will get rpd password like below or screen shot as well.

Location changed to domainRuntime tree. This is a read-only tree with DomainMBea
n as the root.
For more help, use help(domainRuntime)

[Name : tempvalue, Description : null, expiry Date : null]
PASSWORD:satyawrite123  ---- This is my existing rpd password.

Good to see the password....... Cheer's.

Satya Ranki Reddy