Usage Tracking in OBIEE 11g

Why Usage Tracking

   It tracks the usage of the Reports and Dashboards. We will get to know the usage tracking while clicking on Manage Sessions. But using that data we can’t build the statistics. We can achieve by using Usage Tracking feature. It collects data for each query and inserts into the database table, So that we can build our analysis reports based on the data. We can do analysis like,

 How many users are logged in a particular period

 Which query takes long time to execute

 Reports runs from which Subject Area

 What is the record count in a report

      etc..

Implementation

Check for the table, S_NQ_ACCT table in DEV_BIPLATFORM (In this example DEV3_BIPLATFORM)
DEV_BIPLATFORM should contains write access
Import S_NQ_ACCT table into Repository Physical Layer
Create  logical tables (In this example : 1 fact and 3 Dimensions) by using the same Physical table       (S_NQ_ACCT) and create logical join between these tables
Create Usage Tracking Subject Area
Configuring Usage Tracking in em (Enterprise Manager)
Check whether Manage Session data and Usage Tracking Subject Area data same or not

Picture speaks a thousand words...

DENSE_RANK as a filter in OBIEE

  When we are using other than OBIEE function in a filter, we  will be getting an error saying that, "ora-30483 window functions are not allowed here at OCI call" .

Here is the solution for DENSE_RANK in a filter.

RANK is the OBIEE funtion, so that we can use RANK in filter condition.
Criteria :

Result :

Query : Physical query for the above report from Mangae sessions as below

DENSE_RANK is not an OBIEE function, so that we use EVALUATE function.

Criteria : Get Rank1 & 2 results using DENSE_RANK

Result : 

Query : Physical query for the above report from Mangae sessions as below. Query also throws same error as Results, getting in OBIEE.

Solution : From the 1st criteria, we will get Rank 1 result. We want Rank 1 & 2. It is from 2nd criteria.

If we get the query same as 1st Query (RANK function physical query), then we can apply DENSE_RANK in a filter i.e DENSE_RANK filter should come as inner query but not in Having condition.

Apply both RANK and DENSE_RANK in a filter with "OR" operation. Issue resolved.

After applying both the filters with "OR" operation, then we can see the query same as RANK function.

Logic behind : When User want to see the TOPN results, there is no chance to enter 0 (Zero) . He should enter 1 or more than that. As we use RANK with "OR" operation, even though user enters 1 then RANK and DENSE_RANK gives the same result. 

Import data from EXCEL to Oracle table using PL/SQL Developer

After long time I am writing this article.

Here I am explain how we can import EXCEL data into Oracle table by using PL/SQL developer.
Here is the sample EXCEL sheet.

Now create a table with the columns, "EID","ENAME" and "Salary" in the database as below.

Choose "ODBC Importer" from Tools Menu. 

Select "Excel Files" from the drop down and click on "Connect". Now choose the Excel file and click "ok".

Note : Drives can be choose from the drop down as marked in the above image

Listed the all sheets in selected Excel file. Choose the sheet which having data. We can see preview of the data from "Result Preview".

Click on "Data to Oracle" tab and select "Owner" , "Table" from drop down.

In this case I created table "Test" under "SYS" owner. After selecting the table fields will be mapped automatically, because we created the columns withe same name of the excel columns. Check the preview and click on "Import".

Now Excel data transfer into Oracle table, "TEST".