OBIEE – iBot Tracking

We have Delivers functionality in obiee to schedule report delivery; and we also have a model provided for tracking application usage.  What we don’t seem to have is a model for tracking iBot usage.
I have a requirement to deliver an email to an Administrative group once the daily data load is complete, or an error report should the load fail.  This is fine, out of the box Delivers functionality.  However, the client does not want to recieve the report more than once per day; we can do this as a conditinoal request, but how do we know whether a report has already been emailed out today.
Below are the steps that I have completed in order to provide a Subject Area for Usage Tracking – iBots.  I make no effort to cover out of the box usage tracking or out of the box Delivers functionality; this information is alreadys available in abundance, but please ask if you are unsure.
In The Physical Layer
If you have already deployed Usage Tracking then the following tables will be available from your database; import them in the physical layer.

• S_NQ_JOB
• S_NQ_JOB_PARAM
• S_NQ_INSTANCE

These tables are shown below, among others.  Create an Alias for each of these tables to represent  your dimensions.  Create an additional Alias for the table S_NQ_INSTANCE to represent your fact table.
Please ignore the table and alias for S_NQ_ERR_MSG; it is not necessary for my requirement; but, an optional extra.

Create the following Physical Tables and Alias'

Primary Keys for these Alias Tables should be created as below.

• Dim_S_NA_JOB=JOB_ID
• Dim_S_NQ_JOB_PARAM=JOB_ID
• Dim_S_NQ_INSTANCE=JOB_ID, INSTANCE_ID
• Fact_S_NQ_INSTANCE=JOB_ID, INSTANCE_ID

The Screenshot below shows how these Alias tables should relate to one another.  The Joins themselves are listed below the screenshot.

Create physical Joins as shown

• Dim_S_NQ_JOB.JOB_ID = Fact_S_NQ_INSTANCE.JOB_ID
• Dim_S_NQ_JOB.JOB_ID = Dim_S_NQ_JOB_PARAM.JOB_ID
• Dim_S_NQ_INSTANCE.INSTANCE_ID = Fact_S_NQ_INSTANCE.INSTANCE_ID AND Dim_S_NQ_INSTANCE.JOB_ID = Fact_S_NQ_INSTANCE.JOB_ID

In The Logical Layer
we have two dimensions to create and one fact table. I’ll go through these three tables individually below; if a change is not detailed then you don’t need to make it.
Dim – iBot Agent
We’ll start with the Job Dimension. Drag the Alias Dim_S_NQ_JOB into the Usage Tracking Business Model and open the table source for this logical table; edit it as below. Create Job Dimension Table Source as shown
You can see that we have added an Inner Join to the Alias Dim_S_NQ_JOB_PARAM. And thats it. We can now map the columns that we need as below. Please remove all other columns for logical table.
You can see that we have added an Inner Join to the Alias Dim_S_NQ_JOB_PARAM.  And thats it.  We can now map the columns that we need as below.  Please remove all other columns for logical table.

Map the Columns as shown

We also need to restrict the rows returned by this table; navigate to the Content Tab.  Particularly we are restricting S_NQ_JOB and S_NQ_JOB_PARAM to a 1:1 mapping.

Configure the Where Clause for the table

Dim – iBot Instance
I have called this dimension Dim – iBot Instance.  Drag the Alias Dim_S_NQ_INSTANCE into the Usage Tracking Business Model.  This Logical Table requires only a single source, we can move straight to the Column Mapping tab.  Please map the columns as below and remove all other columns.

Map Columns as shown

Again, we need to restrict the rows returned for this logical table; navigate to the Content tab and complete as below.

Add the Where Clause to the table

I found it useful to add four additional columns to this dimension.  Below I will show the expressions that I used for these columns.
Dim – iBot Instance: Additional Start Date Column

Start Date column is useful

Dim – iBot Instance: Additional End Date Column 

As is this one

Dim – iBot Instance: Amend Status Column

Make the Status Column more useful

Dim – iBot Instance: Additional Action Flag Column

'Y' If Email Sent, 'N' Otherwise

Fact – iBot Instances
The fact table uses a single source; drag the Alias Fact_S_NQ_INSTANCE into the Usage Tracking Business Model; navigate to the Column Mapping tab and configure the Facts. No where clause need be added to this Logical Table.

Map the Fact Columns

I have added a Fact measure for the Elapsed Execution Time of an iBot; the expression for the column is shown in the screenshot.  The column as aggregates as a sum.

This additional fact column is likely to be useful

In The Presentation Layer
In the Presentation Layer I have exposed the majority of columns that exist in the logical layer.  I have added a couple more columns other than those detailed above; and no doubt you will come up with your own useful additions.

Create your Presentation Catalog

The End Report
Thats about it for the RPD.  In my case I finished up creating the report below.  I add this report as a condition for an iBot; only send an email if this report shows no results.  This should mean that the iBot only sends the email once per day.

Create your condition Report

There are lots of extensions to this schema that could enhance it greatly.  I have thought of a couple this morning, but as its not required I’ll not have the time to implement.  Hopefully, you found this helpfull/interesting; let me know if you extend it in an interesting way.  Good Luck!