Usage Tracking in OBIEE 11g (11.1.1.5.0)

(updated for 11.1.1.7.0)

When usage tracking is enabled, the BI server collects information related to usage such as the query that was sent to the server, how much time it took to execute it, etc. This information can help in data warehouse optimization and optimization of the BI system as a whole.

when creating the schemas using the repository creation utility (rcu) during OBIEE installation, the below table was created which stores the usage tracking information.

Schema Name	Table Name
DEV_BIPLATFORM	S_NQ_ACCT

Be sure you remember the above schema password.

Open up BI Admin Tool and create a new repository or use your existing one. create a new database –> add a connection pool –> import metadata

got to $ORACLE_INSTANCE\config\OracleBIServerComponent\coreapplication_obis1 and edit the NQSConfig.INI file

[USAGE_TRACKING]

ENABLE = YES; 

DIRECT_INSERT = YES; 

#==============================================================================
#  Parameters used for inserting data into a table (i.e. DIRECT_INSERT = YES).
#
#PHYSICAL_TABLE_NAME = "<Database>"."<Catalog>"."<Schema>"."<Table>";  # Or "<Database>"."<Schema>"."<Table>" ;
PHYSICAL_TABLE_NAME = "UsageDB"."DEV_BIPLATFORM"."S_NQ_ACCT"; 
#CONNECTION_POOL = "<Database>"."<Connection Pool>";
CONNECTION_POOL = "UsageDB"."Usage Connection Pool"; 
BUFFER_SIZE = 250 MB; 
BUFFER_TIME_LIMIT_SECONDS = 5; 
NUM_INSERT_THREADS = 5; 
MAX_INSERTS_PER_TRANSACTION = 1;

notice that there is no data present in the usage table yet.

save the NQSConfig file, save your rpd and deploy it using the FM Enterprise Manager.

login to Answers and create / run an analysis and observer the output.

you can confirm that usage tracking has been enabledby viewing  the log file

usage tracking records will now be available in the S_NQ_ACCT table.

you can make better  use of this information in the true business intelligence sense by creating a dimensional data model for the data in the S_NQ_ACCT table and creating some analysis in your presentation layer. This is nicely illustrated in the OBE tutorial  Setting Up Usage Tracking in Oracle BI EE

In OBIEE 11.1.1.6.0, / 11.1.1.7.0 the usage tracking settings can be done from Fusion Middleware enterprise manager.

Login to FM enterprise manager. on the LHS tree view, navigate to Farm_bifoundation_domain –> Welblogic Domain –> bifoundation_domain. right click to get the context menu

Expand Application Defined MBeans –> oracle.biee.admin –> Domain: bifoundation_domain.

Lock the domain, as follows: ref [Oracle docs]

1. Expand BIDomain and select the BIDomain MBean where group=Service.

2. Display the Operations tab.

3. Click the lock link.

Expand BIDomain.BIInstance.ServerConfiguration, then select the BIDomain.BIInstance.ServerConfiguration MBean.

Ensure that the UsageTrackingCentrallyManaged attribute is set to true. When UsageTrackingCentrallyManaged is set to false, the following parameters are managed using the NQSConfig.INI file (just like in OBIEE 11.1.1.5) on each Oracle BI Server computer rather than the System MBean Browser:

• SummaryAdvisorTableName

• SummaryStatisticsLogging

• UsageTrackingConnectionPool

• UsageTrackingDirectInsert

• UsageTrackingEnabled

• UsageTrackingPhysicalTableName

Set the UsageTrackingEnabled attribute to true to enable usage tracking.

Set the UsageTrackingDirectInsert attribute to true to enable direct insertion.

Set the UsageTrackingPhysicalTableName and the UsageTrackingConnectionPool with the values as shown previously above in the NQSConfig.INI file

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

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

2. Display the Operations tab.

3. Click one of the commit operations.

Restart OBIEE services.

Patching OBIEE 11g

Download the required patch from oracle support. Lets say in this case I have downloaded the Patch 13067844 for the write back error.

you will find the OPatch utility in $ORACLE_BIHOME directory e.g D:\OBIEE11g\Oracle_BI1\OPatch

Note that this OPatch utility is in the oracle BI home and not the oracle database home.

before you apply the patch, stop all OBIEE services.

unzip the patch to its directroy. lets say D:\InstallFiles\13067844

cd to the unzipped patch directory and set the ORACLE_BIHOME and verify the OPatch utility.

Restart the OBIEE services.

If you need to deInstall the patch, the command is

opatch rollback –id <patchid>

e.g.

opatch rollback -id 13067844

For detailed instructions especially related to pre-requisites for individual patches , please refer to the README.txt file for the respective patch file.

Writeback Analysis in OBIEE 11g - part 1

writeback –> ability to modify data that is displayed in an analysis in oracle answers.  very crude way of putting it. Please refer to the OBIEE docs from Oracle for the limitations.

Interestingly, this contradicts the basic concept of an OLAP system, which is primarily meant for fetching data rather than inserting data. However there are some special cases, where you would want this functionality. There are certain design considerations too that are involved with the data warehouse, which we will discuss in another post.

I’m creating a demo table for this purpose

create table JT_DEMO_WRITEBACK
(
    CUST_KEY    NUMBER  PRIMARY KEY,
    CUST_NAME VARCHAR2(255) NOT NULL,
    CUST_RATING NUMBER NOT NULL
);

lets insert some data to look like this:

Login to Oracle Answers. got to Administration page. Under security click the “Manage Privileges” link

change the “Write Back to Database” privilege

start the BI Admin Tool and open your rpd. In the physical layer, create a new connection pool called “WB Connection Pool”. Import this table. create an alias for this table.

disable caching on your physical table that you want to write back to.

create a new Business Model to look like this

we want the Customer Rating field to be editable(writable) in our Answers page.

Create the new subject area in the presentation layer as shown below. for simplicity purpose, I’m setting an implicit fact for the WB Test Subject area.

set the permission to read write for the relevant users

save your rpd and check for consistency.

Enable writeback in the configuration file by Modifying  the instanceconfig.xml file located in $(ORACLE_INSTANCE)\config\OracleBIPresentationServicesComponent\coreapplication_obips1

add the following to the instanceconfig.xml file

<WebConfig xmlns="oracle.bi.presentation.services/config/v1.1">
   <ServerInstance>
      <!-- added by JT -->
      <LightWriteback>true</LightWriteback>

</ServerInstance>
</WebConfig>

Deploy your rpd and restart the BI services.

create a new Analysis as shown

go to the Advanced tab and note the column ids in the xml code. we will use these ids in our xml template to specify the SQL to update the customer rating field.

In the criteria tab, mark the customer rating column writable ( this option is enabled in Answers if you enabled the writable flag for the column in the rpd).

go to results tab –> edit view (pencil icon) –> Table view properties (the xyz icon) and enable writeback option and specify the template name

create your template file which contains the SQL code for the update.

<?xml version="1.0" encoding="utf-8" ?>
<WebMessageTables xmlns:sawm="com.siebel.analytics.web/message/v1">
<WebMessageTable lang="en-us" system="WriteBack" table="Messages">

<WebMessage name="wb_cusRating">
<XML>
<writeBack connectionPool="WB Connection Pool">
<insert> </insert>
<update>
UPDATE BISAMPLE.JT_DEMO_WRITEBACK SET CUST_RATING=@{c8f6dc321eca7766c} WHERE CUST_KEY=@{c6657b10f6946b91b}
</update>
</writeBack>
</XML>
</WebMessage>
</WebMessageTable>
</WebMessageTables>

save the above text to a new file called writebacktemplateJT.xml put it in the location $(ORACLE_INSTANCE)\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\analyticsRes\customMessages

note:

1. you may want to create customMessages directory if it does not exist.
2. the line <WebMessage name="wb_cusRating"> specifies the template name.
3. save your analysis to a folder as “WB Test”.

Restart OPMN services. login to Answers page with the user, whom you have given the necessary rights. open the “WB Test” analysis

notice that the Cust Rating  field is editable, change the value and click on the update button. OBIEE updates the data to the physical database and refreshes the page with the new values.

note: you might get the writeback error in red as shown below

There seems to be a bug with the write back feature in OBIEE 11g. A patch
is currently available only for 64 bit Win systems. Bug 13067844, Patch
13067844. After installing the patch, the writeback works properly.

Data Source Connectivity from BI Admin Tool

When creating a new repository or while importing from a data source using OCI interface, you get the “connection has failed” error.

This is because, the BI Admin tool is not able to find the TNS entry. to resolve this,

Copy your tnsnames.ora (from your oracle database home\network\admin) to {Oracle_BI1}\network\admin directory & {oracle_common}\network\admin directory

OR

go to $ORACLE_INSTANCE\bifoundation\OracleBIApplication\coreapplication\setup

add this entry in the user.cmd / user.sh file

set TNS_ADMIN=D:\app\product\11.2.0\dbhome_1\NETWORK\ADMIN

you might want to restart the BI Admin Tool.