Usage Tracking in OBIEE 11g

Usage Tracking in OBIEE 11.1.1.7.0 

Why Usage Tracking:-
Usage Tracking is used to monitor system and ad‐hoc query performance and to
provide input on usage trends for use in billing users and departments for resources.
Usage tracking is particularly useful in determining user queries that are creating
performance bottlenecks, based on query frequency and response time. When usage
tracking is enabled, the Oracle BI Server collects usage tracking data for each query
and writes statistics to a usage tracking log file or inserts them directly to a database
table. For the purposes of this tutorial, you will use a database table, which is the
recommended leading practice.
Here We will separate Usage Tracking Process to 4 Steps
I. Creating Uasge Tracking Table
II. Creating and Customizing rpd file
III. changing the Configuration in EM

IV. Testing the Usage Tacking in Answers

StepI: Creating Usage Tracking Table
If we latest OBIEE 11g installed in our machine, open sqlplus and connect

DEV_BIPLATFORM schema.

write the below query:

select table_name from tabs where table_name like='S%';

 The table in the name S_NQ_ACCT is related to usage tracking. so we donot have any
 manual work in the usage tracking table creation. So before setting up the usage
 tracking we couldn't have any rows in the table

StepII: Setting up the Repository File
1. Create 'New Database' by right clicking on the empty space in physical layer of the

rpd and name it as 'UsageTracking

2. Right click on the physical layer object 'UsageTracking' ‐>New Object ‐> select
'Connection Pool and enter the detail like below screen
Connection Pool Name: UsageTracking ConnectionPool
Data Source Name: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=Bose‐PC)(PORT=1521)))

(CONNECT_DATA=(SERVICE_NAME=ORCL)))

Password: Admin123
Click 'OK' after you enter the password and you will be prompted to confirm the password. Type the

same password.

3. Import the 'S_NQ_ACCT' table from DEV_BIPLATFORM Schema to the 'UsageTracking'

database repository.

4. Double Click on the DEV_BIPLATFORM physical folder and change it 'UsageTracking

Schema' So that It would like below screen

6. Drag the following three physical columns from Usage Tracking

Schema>S_NQ_ACCT to the Measures logical table in the UsageTracking business

model. For each column, right‐click the logical column and select Rename, and then

apply aggregation rule

7. Drag the following three physical columns from UsageTracking

Schema>S_NQ_ACCT to the Time logical table in the UsageTracking business model.

8. Here is the example screen screen for how to create Key for Time Logical table

Under Keys Tab ‐> type 'Time_key' in the key name and select 'Start Date' in the

columns.

9. Drag the following two physical columns from UsageTracking Schema>S_NQ_ACCT

to the Topic logical table in the UsageTracking business model. Rename them

Do not forget to to apply 'Logical SQL' as a key column for this logical table.

10. Drag the USER_NAME physical column from UsageTracking Schema>S_NQ_ACCT

to the User logical table in the UsageTracking business mode. Rename it to User

Name and apply this column as a key to this table.

Below is the complete BMM Layer Model Screen for UsageTracking:

11. Right click on the UsageTracking BMM Layer folder ‐> select 'Business Model

Diagram' ‐> Whole Diagram and the create joins between the tables as shown in the

12. Just drag the UsageTracking folder to Presentation Layer of the repository.

Now Save the repository and check the consistency check. RPD works are done!!!

StepIII: Setting up the EM

1. Open EM window using the link http://localhost:7001/em by entering weblogic

username and password

Expand WebLogicDomain and the click 'bifoundation_domain' in the left pane. Now

select 'System MBean Browser' in the WebLogic Domain in the right side pane like show

in the picture

2. expand 'Application Defined MBeans' and the expand 'oracle.biee.admin' in that

3. Expand 'BIDomain' in oracle.biee.admin and click on second 'BIDomain' as show in the

picture. Now select 'operation' tab from right side pane and then click 'lock'

Click 'Invoke'. Once we get the screen like below, click 'Return'

4.. Now expand BIDomain.BIServer.ServerConfiguration under oracle.biee.admin from
the left pane. Now select all the following option from the right side pane and apply
the change using the following steps
UsageTrackingCentrallyManaged
UsageTrackingConnectionPool

StepIV: Checking results with Answers using UsageTracking Subject Area
Now Browse some analysis from the existing dashboard or create some reports and

create one report using "UsageTracking" subject for the details.

Thanks

IQ

Experience Level ~3

Informatica

Qn1 – load file containing 100 records in a target file, following below approach

1-10 à file 1

11-20à file 2 .. and so on

Qn2 - Active and passive transformations

Qn3 – Is sorter and Union active transformation if yes, why?

Qn4 – I am having a file as below

EID, ENAME,SAL

1,Robert, 30

2,William, 40 .. until 10 records

I want a footer record in a flat file target with the above records which would make the file look as below

EID, ENAME,SAL

1,Robert, 30

2,William, 40 .. until 10 records

The sum of Salary is 70

(considering only 2 rows given in the example above) . This question was most frequently asked.

Qn5 – Which transformation create Caches.

Qn6 – I have a file with some duplicate records. I want all the unique records pushed in a file and repeated records pushed to a different file.

Src

1,1,2,3,4,5,5,6,7,2,1,6,3,5

Tgt 1- unique

1,2,3,4,5,6,7

Static and Dynamic lookup cache

Qn7 – Lookup is an active or passive transformation

Qn8 – SCD 2 implementation

Qn9 – Different caches in Lookup Transformation.

Qn10 – Difference between Static and Dynamic lookup cache

Qn11- Different type of ports and their order of execution of ports in Informatica expression transformation.

UNIX

Qn1 – command used to replace string

Qn2 – Command to fetch third column from a CSV file

Qn3 – Status of last executed command

SQL

Qn1– delete all remaining records except the latest record

DELETE FROM student

WHERE ApplicationDateTime <> (SELECT max(ApplicationDateTime)

                              FROM student s2

                              WHERE s2.StudentID  = student.StudentID)

Qn2 – second highest salary

Qn3 – Analytical functions. This was asked in a twisted manner. The question asked was I have data as below,

EmpID	Dept	Sal
1	10	100
2	20	400
3	10	800
4	30	400
5	20	300
6	20	300

I want the output to look like

EmpID	Dept	Sal	Total Sal
1	10	100	900
2	20	400	1000
3	10	800	900
4	30	400	400
5	20	300	1000
6	20	300	1000

Where total Sal comprises of data summed up dept wise.

Experience level 5+

Informatica

Qn1 - I have a file in the below format

USD

50,55,40,23

60,34,20,36

INR

1,3,4,6

5,3,6,5

EUR

80,84,85,88

85,86,88,89

I want the output in the below tabular format

Currency	Val1	Val2	Val3	Val4
USD	50	55	40	23
USD	60	34	20	36
INR	1	3	4	6
INR	5	3	6	5
EUR	80	84	85	88
EUR	85	86	88	89

Qn2 - 3 Flat files having same structure are to be loaded in the target table.. We need to load the file in the target table ad ensure no duplicates are loaded.

2 scenarios here

1)      There can be duplicates in the file which needs to be avoided.

2)      The files can have duplicates between them i.e  File 1 and file 2 can have same record. We need to avoid these as well.

Qn3 – This was asked as a follow up on other question. If my joiner transformation receives inputs from 2 pipelines coming from the same source, is there a specific property I need to set to allow that?

Qn4 – Cache files created in informatica and what will the files contain.

SQL

Qn1 – I have data in a table as below

Seq	Emp ID	Start Date	End Date
1	1	1/1/2016
2	2	2/20/2016
3	3	3/11/2016
4	1	1/1/2017
5	2	8/31/2016
6	1	3/1/2017

The above table has some data discrepancies, wherein the end dates have been deleted accidently. We need to write a single update statement, which would fill the end dates as below

Seq	Emp ID	Start Date	End Date
1	1	1/1/2016	12/31/2016
2	2	2/20/2016	8/30/2016
3	3	3/11/2016	NULL
4	1	1/1/2017	2/28/2017
5	2	8/31/2016	NULL
6	1	3/1/2017

So end date in seq 1 is start date of seq 4 minus 1 day.

Most of the questions asked to 3+ were repeated to 5+ as well. Also there were a few adhoc questions asked as people were solving scenario based qns trying to understand the reason behind a particular implementation like, why a sorter is used here, order of ports when writing the variable logic, etc.