Usage Tracking in OBIEE
Down load UsageTracking Web Catalog
UsageTracking Tables Creation Scripts
Introduction
Oracle BI server supports collection of usage statistics which can use in a variety of ways such as who is running what and when database optimization, aggregation strategies, figuring out most used reports, billing departments as per their usage etc.
Goal
We want to achieve a reporting not the usage of OBIEE. If you install OBIEE you will find a Usage Tracking rpd and catalog in below path
<OBIEE Install Dir>\OracleBI\server\Sample\usagetracking. Alternatively you can download the same from here as well
Down load UsageTracking Web Catalog
Once you have rpd and web catalog for usage tracking- you will need to peform three steps to make everything working.
Step1 : Merge usage tracking rpd with existing rpd
Merge usage tracking rpd with existing rpd
Step 2: Merger usage tracking catalog with existing catalog
Step 3: configuration for storing usage tracking in file or database tables
The best way to store usage tracking is in database tables.Oracle installation directory already have scripts for those tables.If you are just interested storing the usage tracking in file there is very few configuration. But remember whichever the way you choose file or database there is a performance implications.
I will discuss configurations later in another post.
Check this Oracle by Example for detail information with screen prints
Oracle DAC System Properties
There are lots of DAC System Properties available which can be handy during inital set up and consequent improvement in the system.Most of the people just configures DAC server host ,DAC server OS and sometimes dac server port and keep rest of the parameter as is.
However sometimes changing those parameter makes lots of sense.it may be for debugging/testing or performance improvement.
Let go through these parameter one by one. Lets address the important ones first.
select any of the DAC property and click on Edit to see info on that parameter.
Here is one line description of few DAC server properties.
Server log level – Level of Details DAC collects during execution.Oracle support may ask to set this to higher value to debug the issue
Scheduler.Poll.Interval - Value in seconds as to how often DAC server will poll for changes in Schedule configuration.
Test Run – When set to true, DAC ignores the errors during the execution. This is a helpful parameter during intial dac testing.
SQL Trace -Flagging the SQL Trace to true hints the Database connectivity layer of the DAC Server to enable SQL tracing. Thus every sql that is fired by the DAC server is spooled to the appropriate output log file.Oracle support may ask to set this as true to debug the issue
Script After Every ETL/Script before Every ETL – This parameter along with post_etl/pre_etl runs the task which needs to be excuted as pre and post etl
Oracle DAC repository Import
Please are the steps to perforem DAC repository Import
Read DAC repository Export Steps here
Step 1 :Log on to the DAC where you want to Import the repository.
Step 2: Keep a backup of existing repository (if needed) and proceed with the DAC Repository Import. Navigate to Tool -> Repository Management -> Import
Step 3: Change the Import/Export folder and select various options by checking the checkboxes as shown in the screenshot below. Check only option you want to import
Note: if you check user data as well then after import dac will not allow you to log in which existing DAC repository password. You will have to use the credentials of your exported repository
Respond to the prompt in the confirmation window below and clik Yes to continue.
Step 4 : windows like below will display showing that its doing an import. Once done , click ok
Step 5: After import dac will have parameter of Repository from where you have imported.Make sure you manually re configure all the parameter as displayed in below screen print
Some time DAC server does not start after import. Please go through the log. Most of the time its port issue where DAC server is running. Manually kill the process running on DAC port ( default 3141) and re start the DAC serve.
1. ‘Importing Tables’ window will display the progress. Once done, click
Oracle DAC repository Export
Below are the step which are needed to do DAC Repository Export.
If you are looking DAC repository Import please read my post on Import
Step 1 :Log on to the DAC:
Step 2 : Navigate to Tools -> DAC Repository Management -> Export
Step 3 : Select the Export Folder on your desktop, check the Categories and Application List as follows and click on OK to start DAC Repository Backup.
Select the Export Folder.
Select the options:
Step 4: Repository export will start. Once finished, press OK.
Note: Time taken for explore to complete depends on network speed.
Step 5 : verify the folder where you have export the contents. It should looks something like below.
Read DAC repository Import please read my post on Import
Implicit Fact Column in OBIEE
Often time you will come across scenario where in presentation catalog you have 2 or more fact tables and couple of dimensions. And not all the fact tables are joined with all the dimensions.
And when you just query the data out of dimension which is not joined to a fact table. It will give you an error like ..Can not navigate to the fact table.
Matter of fact is : Whenever you query the data just from the dimension it does not just read the data from dimension but it join that dimension with the fact and then get the data from dimension. Pretty weird huh…
So to resolve this what you will need an implicit column in presentation catalog so whenever you query data from dimension it goes through the table where you have defined an implicit column.
In BMM layer , create a dummy logical column and called it as implicit column , map that column to any number of your choice. Please refer the below screen shot.
Once you are done creating the implicit column. Double click on the presentation catalog and select the newly crated column.
Now if you query just a dimension tables- you will be just fine. ( Again it has to join in BMM and Physical where you have defined implicit column.)
OBIEE Administrator Task
I often get questions via Contact US page on as below. And decided to write a small post to address this
what are the duties of OBIEE Admin ?
I want to know the day to day activities of OBIEE Admin, Can you please
send me if is there any material regarding admin tasks of OBIEE.
How obiee admin work differs between windows and linux environments ?
Lets address above questions -
Over all OBIEE Administration work includes
1> Installations – OBIEE repository Server , OBIEE Presentation Sever , oc4j or any other web server , JDK , Oracle Client Instlaation ,Clustering(not everyone does clustering) , Helping user with OBIEE briefing book component and OBIEE MS Office component installations , BI Publisher
2> Configurations – Configuraing MUDE( Multi User Development Environment) OBIEE scheduler , Usage Tracking , Security integration between rpd and BI Publisher ,Repository Sever and Presentation Server configuration changes , Cluster Configs ,Cache etc
3>Security – Over all security , SSO , OBIEE content and Data level security etc
3>Merge and Migrations – OBIEE Repository and Catalogs as well as BI Publisher reports merge and migrations
4>Space Management- Server space management for various log file , cache files , back up files etc
5>Monitoring – Monitoring the over all obiee usage on server and capture the usage spikes etc to improve the performance
6> Uptime – Over all uptime of server
Linux v/s Window Environment – Basic Obiee Administration activity on both Windows and Linux are pretty much the same. Its way of doing it differs on 2 environments.
e.g starting/stoping services , Taking back up rpd and web cat ,changing some config parameters like cache etc.
Bottom Line - One will be do the tasks on Linux as one does on Windows.As both Platforms are different you will have to do it differently to achieve the same task.
Best place to start – I would suggest go through Oracle By Example for OBIEE, then read OBIEE documentation and internet blogs and most important get your hand dirty by working on Admin tasks.
Thanks for Reading & Happy Learning ….
Oracle Data Warehouse Administration Console (DAC) interview questions
In this post I will discuss some interview questions on Data Warehouse Administration Console (DAC) which checks the key concepts about tool.
Check back often as I will be adding the answers here as an when I will get some time over the week ends.
If you are looking for some DAC an electronic subject this is a wrong place. Here I will be discussing all about Oracle DAC or DAC which controls the execution of Informatica mappings.
1> Over all architecture of DAC ?
2> Why we should use DAC and not control all execution through informatica ?
3> Can we run multiple execution plan at the same time in DAC ?
4> Explain DAC export/import
5> Have you change any of the DAC parameters ? If so which one and why ?
6> How do you Determine the Informatica Server Maximum Sessions Parameter Setting in DAC?
7> Can dac send an email just in case of any failures ?
8> Can you execute the sql scrip through DAC ? If yes how ?
9> in DAC How you can disable table indexes before loading and enable the index once load is complete ?
10>Let say you are running the normal incremental load. But just for today you want to extract data from AP_INVOCIES_ALL from 12/12/2011? How you can achieve this ?
11>How DAC Determines the Order of Task Execution within an Execution Plan ?
12>What are Micro ETL Execution Plans ? How can you Build and run them ?
13>From you past experience – explain scenario where Micro ETL Execution Plans produced wrong results on reports?
14> Let say you can not use DAC scheduler to schedule you execution plan. What other options do you have ? How you can achieve this ?
15>Does DAC keeps track of refresh dates for all the source/target tables ?
16>Consider the scenario as below for task T1
Primary Source has not null last refresh date
Primary Target has null last refresh date
Will task T1 executes in full or incremental ?
17>Explain the upgrade/merge options for DAC 7.8.4 & below and new versions ?
18> Using DAC command line – write a script to check weather informatica services are up or not ?
19>Can we have two DAC server on the same machine ?
20>Explain briefly What kind of DAC Repository Objects Held in Source System Containers ?
21>What is Authentication file ? If you have dac client installed can you access DAC repository without Authentication file ?
22>Explain Index, Table and Task Actions in DAC ?
23>How DAC Handles Parameters at Runtime ?
24> How DAC Determines Tasks Required for any given subject area Subject Area ?
25>Difference between Homogeneous and Heterogeneous execution plans.
Good Luck for your interview…