Enabling auditing and monitoring capabilities for reporting is crucial for IT management and governance regardless if it’s enterprise level or department level deployment.  In OBIEE 11g, Oracle added more data to audit and monitor, including more BI Publisher specific information.  This article describes the steps for Enabling Usage Tracking for BI Publisher and how to visualize usage tracking data.

Sections

1. How to enable auditing from BI Publisher UI
2. How to run RCU to create Audit Schemas
3. How to create JDBC connection with Weblogic console
4. How configure the Audit Policy Settings
5. How register the Audit Storage Database to Your Domain
6. How audit events in an Audit Table

1) How to enable Auditing from BI Publisher UI:

• Go to Administrator page and click ‘Server Configuration’ menu (Step 1)
• Scroll down to the bottom, and check ‘Enable Monitor and Audit’, then hit ‘Apply’ button (Step 2).

• Step 1:

• Step 2:

2) How to run RCU to create Audit Schemas:

• Go to $RCU_HOME/bin and execute the ‘rcu’ command
• Choose Create at the starting screen and click Next.
• Enter your database details and click Next.
• Choose the option to create a new prefix, for example ‘BIP’ etc…
• Select ‘Audit Services’ from the list of schemas.

• Click next and accept the tablespace creation.
• Click Finish to start the process.

3) How to create JDBC Connection with WebLogic Console:

• Connect to the Oracle Weblogic Server administration console:http://hostname:port/console (e.g. )
• Under Services, click the Data Sources link.

• Click ‘Lock & Edit’ so that you can make changes (If not done yet)

• Click New –> ‘Generic Datasource’ to create a new data source.
• Enter the following details for the new data source:

Name: Enter a name such as ‘bip_audit_datasource’.
JNDI Name: jdbc/ bip_audit_datasource
Database Type: Oracle

• Click Next and select ‘Oracle’s Driver (Thin XA) Versions: 9.0.1 or later’ as Database Driver (if you’re using Oracle database), and click Next.

• The Connection Properties page appears. Enter the following information:

Database Name: Enter the name of the database (SID) to which you will connect.
Host Name: Enter the hostname of the database.
Port: Enter the database port.
Database User Name: This is the name of the audit schema that you created in
RCU. The suffix is always IAU for the audit schema..
Password: This is the password for the audit schema that you created in RCU.

• Click Next.

• Accept the defaults, and click Test Configuration to verify the connection.

• Click Next

• Check listed servers where you want to make this JDBC connection available.

• Click ‘Finish’

• Make sure you click ‘Activate Changes’ at the left hand side top to take the new JDBC connection in effect.

 

4) How to configure the Audit Policy Settings:

• In Oracle Fusion Middleware Control, under WebLogic Domain, right-click bifoundation_domain. From the menu, click Security and click Audit Policy, as shown in the pic:

• The Audit Policy table displays all the audited applications under the bifoundation_domain. Set the Audit Level to enable auditing for BI Publisher, as shown in the pic:

5) How to register the Audit Storage Database to Your Domain:

• Log in to EM console and  under WebLogic Domain, right-click bifoundation_domain navigate to the security provider configuration :

• Click of the Audit Service and click on configure:

• Click on the JNDI source, select the Audit Source and click on Apply.

6) How to audit events in an Audit Table:

We can use the Audit tables to do the Modelling in RPD or creating the Data Model in the BI Publisher. Here are the important Auditing Base Tables that are mainly used:

• IAU_BASE

• XMLPSERVER

The events that are audited for the BI Publisher server are:

• User Login
• User Logout
• Report Request
• Scheduled Report Request
• Report Republish
• Report Data Download
• Report Download
• Report Data Process
• Report Rendering
• Report Delivery

To see reports with the most up-to-date data from the latest extract-transform-load (ETL) process, we typically purge the Oracle BI Server Cache using a common job called, “Cache Purge.”

There are several ways to purge Oracle BI Server Cache:

• iBots/Agents bursting
• Dynamic repository variables
• A nqcmd command

As an alternative to these methods, we can script the process and run it periodically.  To execute these commands in any automated process, we depend on another app/skillset that forces us to compromise the security to access OBIEE Server.  This security bypass is due to the fact that some of the OBIEE command line statements can’t be executed external to the Oracle BI Server.

To perform this function you are compromised at the security level of the operating system (OS) or you need to setup the ‘sshpass’ (password-less login) functionality.  Many organizations will not support this in a production environments for obvious reasons.

I am very excited to announce that I have developed a free simple handy tool for the OBIEE commnity to help address this issue.   Using this tool, you can purge or seed the OBIEE Server Cache remotely.  You do not need to login to the OBIEE server or Application.   The only requirement is thing that you must be able to ping the OBIEE hostname/ipaddress on BI Server port (e.g.: 9703).

 

 

Instructions to Install:

I have created two versions of BICachePurge v1.0 utility for both Windows and LINUX/UNIX Operating Systems.

1. Download the desired version
2. Unzip the downloaded file into a directory of your choice.
3. Navigate to the BICachePurge_v1.0 folder.
4. In the BICachePurge_v1.0 root folder, Open the BIS_Param (BIS_Params.bat for WINDOWS and BIS_Params.sh for Linux/Unix) in a text editor.
5. Edit the following variables to reflect the OBIEE server where you would like to purge the cache:

• BI_SERVER
• BIS_PORT
• BI_USER
• BI_PASWD
• OBI_QUERY

Note: All the variables are mandatory.  If spaces exist in the values, then add that in quotes (“). Example: “call SAPurgeAllCache()“.  Remember to save your changes.

Sample BIS_Params.bat file configuration:

 


Now you are set to purge cache from your terminal:

• On Windows: open a DOS command prompt and navigate to <install drive>/BICachePurge_V1.0 and execute Purge_Cache.bat command.
• On Linux/Unix: on command line, Go to <install drive>\BICachePurge_V1.0 and execute ./Purge_Cache.sh command.

Sample Output of Purge_Cache.bat command:

How does it help automate the Cache Purge/Seed process?

You can use this code in different ways.

• Purge the OBIEE Server cache from your own system without logging into OBIEE online RPD or Presentation Services.
• Use this in crone job on the server to do this at regular intervals
• Execute from your own ETL servers (i.e. from DAC server to execute it as a POST Task) and wherever you want to run it without depending on the OBIEE Server.

What Is A Level-Based Measure?

A level-based measure is a column whose values are always calculated to a specific level of aggregation. Here is an example of a level-based measure using a Products and Revenue table.  A level-based hierarchy is created for Products as follows:

By default, when we drill down within the products hierarchy from Brand level to Product level, the corresponding Revenue drills down to Product level.

Level-Based Measures in Oracle Business Intelligence (OBIEE)

If we want to view the revenue aggregated to the Brand level then:

• We create another measure called ‘Brand Revenue’ which is a duplicate of ‘Revenue’ in the repository (RPD).
• Click on its properties and in the ‘Levels’ tab specify the logical level as ‘Product Brand’ for Logical Dimension ‘H2 Product’.  Save the RPD.

• When Brand Revenue is dragged into our analysis, it shows Revenue for Brand level, no matter how far we drill down from the Brand level.

In addition to familiar SQL aggregation functions such as Sum, Max, Min, Average, and Count, OBIEE also supports an aggregation function called “Aggregate”. This function is available for use in formulas written in Answers.

Business Model

To illustrate the use of the Aggregate function, consider the following simple business model that has one dimension and three facts. Two facts (“Qty Sold” and “Amt Sold”) have an aggregation rule of Sum in the metadata. “Avg Price” is defined as “Amt Sold”/“Qty Sold”.

 

 

 

 

 

 

 

 

 

 

 

 

 

The business model has two tables as it sources, “Time” and “POSTest”.

 

 

Query Using Sum

Consider a query that calculates the Qty Sold for each month and also calculates the total Qty Sold for all month, using the SUM function in a formula.

 

 

 

This query generated the following SQL. Interestingly,
the value in the SUM QS column was calculated
on the result set by OBIEE.

 

select T463.MONNAME as c1,
sum(T2144.QS) as c2,
T463.YEARMONTH as c4
from
TIME T463,
POSTEST T2144
where ( T463.DATE_ = T2144.DATE1 )
group by T463.MONNAME, T463.YEARMONTH
order by c1, c4

Query With Grand Total Using Default Rule
If instead of using the SUM function in a formula we had simply requested a grand total, we would have seen this.

 

 

 

 

The logical query used the REPORT_SUM function:

 

SELECT "Time Dim"."Month Name" saw_0, Facts."Qty Sold" saw_1, REPORT_SUM(saw_1 BY ) FROM AggregateTest ORDER BY saw_0

 

Again, if you look at the physical query, you can see that the grand total is being calculated by OBIEE from the result set.

 

WITH 
SAWITH0 AS (select sum(T2144.QS) as c1,
T463.MONNAME as c2,
T463.YEARMONTH as c3
from 
TIME T463,
POSTEST T2144
where ( T463.DATE_ = T2144.DATE1 ) 
group by T463.MONNAME, T463.YEARMONTH)
select distinct SAWITH0.c2 as c1,
SAWITH0.c1 as c2,
SAWITH0.c3 as c4,
SAWITH0.c1 as c6
from 
SAWITH0
order by c1, c4

 

Query with Grand Total Using Server Complex Aggregate

The same query, with the aggregation rule on Qty Sold changed to “Server Complex Aggregate”, produces a logical query using the AGGREGATE function. AGGREGATE is used with BY followed by a null, which means aggregate over all rows.

SELECT “Time Dim”.”Month Name” saw_0, Facts.”Qty Sold” saw_1, AGGREGATE(saw_1 BY ) FROM AggregateTest ORDER BY saw_0

The physical query generated by OBIEE was:

WITH 
SAWITH0 AS (select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3
from 
(select sum(T2144.QS) as c1,
T463.MONNAME as c2,
T463.YEARMONTH as c3,
ROW_NUMBER() OVER (PARTITION BY T463.YEARMONTH ORDER BY T463.YEARMONTH ASC) as c4
from 
TIME T463,
POSTEST T2144
where ( T463.DATE_ = T2144.DATE1 ) 
group by T463.MONNAME, T463.YEARMONTH
) D1
where ( D1.c4 = 1 ) ),
SAWITH1 AS (select sum(T2144.QS) as c1
from 
POSTEST T2144)
select SAWITH0.c2 as c1,
SAWITH0.c1 as c2,
SAWITH0.c3 as c4,
SAWITH1.c1 as c5
from 
SAWITH0,
SAWITH1

In this query, the derived table D1 evaluates to this:

 

 

 

 

SAWITH0 evaluates to:

 

 

 

 

SAWITH1 evaluates to

 

 

The physical query taken as a whole evaluates to

There are a couple of things to notice here. One is that by using the Server Complex Aggregate rule, the aggregation formula in the logical query for the grand total changes from REPORT_SUM(saw_1 BY )  toAGGREGATE(saw_1 BY ). The physical query also changes in form. The grand total is not directly calculated in the physical SQL generated for Report_Sum, whereas it is calculated in a separate SQL block when the logical SQL uses Aggregate.  The Aggregate function is a message to the BI Server saying, essentially, “determine how to aggregate by using the information in the metadata”.

Using the Aggregate Function in Answers

The fact that the presentation server generates logical SQL using the Aggregate function is a clue that the Aggregate function can be used in a formula in Answers (even though Aggregate is not listed as an aggregate function in the expression builder!).

 

 

 

 

 

 

 

 

When writing a formula with Aggregate, you can use “(..by)”.

 

 

 

 

 

 

Using this formula, here are the results:

 

 

 

 

The logical SQL generated is almost the same as the logical SQL generated in the previous example, with the column label “Qty Sold” used instead of “saw_1”.

 

SELECT “Time Dim”.”Month Name” saw_0, Facts.”Qty Sold” saw_1, Aggregate(Facts.“Qty Sold” by) saw_2 FROM AggregateTest ORDER BY saw_0

The physical SQL generated is exactly the same.

Complex Aggregation Rule

Consider what happens when Avg Price (defined in the metadata as Amt Sold/Qty Sold)  is included in the query.

 

 

 

 

 

The BI Server generates the following physical SQL, with Avg Price calculated in the last Select block.

 

WITH 
SAWITH0 AS (select sum(T2144.QS) as c1,
sum(T2144.AMT) as c2,
T463.MONNAME as c3,
T463.YEARMONTH as c4
from 
TIME T463,
POSTEST T2144
where ( T463.DATE_ = T2144.DATE1 ) 
group by T463.MONNAME, T463.YEARMONTH)
select distinct SAWITH0.c3 as c1,
SAWITH0.c1 as c2,
SAWITH0.c2 / nullif( SAWITH0.c1, 0) as c3,
SAWITH0.c4 as c4
from 
SAWITH0
order by c4

 

Now we’ll include a Grand Total in the query.

The logical SQL generated uses the Aggregate function for Avg Price.

SELECT “Time Dim”.”Month Name” saw_0, Facts.”Qty Sold” saw_1, Facts.”Avg Price” saw_2, REPORT_SUM(saw_1 BY ), AGGREGATE(saw_2 BY ) FROM AggregateTest ORDER BY saw_0

This results in the following physical SQL being generated.

WITH

SAWITH0 AS (select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3,

D1.c4 as c4

from
(select sum(T2144.QS) as c1,
sum(T2144.AMT) as c2,
T463.MONNAME as c3,
T463.YEARMONTH as c4,
ROW_NUMBER() OVER (PARTITION BY T463.YEARMONTH ORDER BY T463.YEARMONTH ASC) as c5
from
TIME T463,
POSTEST T2144
where  ( T463.DATE_ = T2144.DATE1 )
group by T463.MONNAME, T463.YEARMONTH
) D1

where  ( D1.c5 = 1 ) ),

SAWITH1 AS (select sum(T2144.QS) as c1,
sum(T2144.AMT) as c2

from

POSTEST T2144)

select SAWITH0.c3 as c1,
SAWITH0.c1 as c2,
SAWITH0.c2 / nullif( SAWITH0.c1, 0) as c3,
SAWITH0.c1 as c4,
SAWITH0.c4 as c6,
SAWITH1.c2 as c7,
SAWITH1.c1 as c8
from
SAWITH0,
SAWITH1

SAWITH0 evaluates to

 

 

 

 

 

SAWITH1 evaluates to

 

 

 

The result set for whole query evaluates to

 

 

 

 

Notice that the physical query does not calculate the grand total for Avg Price directly. The BI Server calculates the result (20.26) from these results as 12842/634 (or c7/c8).

We can use the Aggregate function directly in Answers by writing a formula like this:

 

 

 

 

 

 

 

 

 

 

Using Aggregate we could write a formula to compare the monthly Avg Price to the Avg Price for the whole time period.

 

 

 

 

 

 

 

Knowing how to use the AGGREGATE function can be a useful addition to your OBIEE tool set.