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
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…
Informatica integration service issue with DAC
If you are getting below error and/or DAC is not able to test integration services please read on ..
ERROR:
The command: [pingserver] is deprecated. Please use the command [pingservice] in the future.
WARNING: Lost server connection.
ERROR: Cannot connect to Integration Service [Integration_Service:4001].
I have see that May times DAC acts so weirdly.
DAC is able to test all the physical data source and repository services but only thing he has porblem with is Informatica Integration services.So how its not able to test that.
DAC and informatica services runs file and even able to connect from command prompt.
This may have happen because of multiple reasons
like
1> New Oracle client installation or ORACL_HOME path variable changes
2> Informatica related changes like hot fix etc
3> Any changes in the DAC java libraries and or Oracle client java libraries
If you saw above error try to copy the ojdbc6.jarfrom your <Oracle Client Home\jdbc\lib> to <DAC Home> \lib and
Edit the batch or shell file which start the server (startserver.bat or startserver.sh) and change the max memory to 1024 if it not already set.
start %JAVAW% -server -Xms256m -Xmx1024m -cp %DACCLASSPATH% -Duser.dir=%DAC_HOME% com.siebel.etl.net.QServer
Once done bounce the DAC server.
By doing these two things most probably this will fix your issue.
Thanks for reading..
OBIEE interview questions
Below are list of questions you may face during interview. This is part of overall effort to put the interview questions for OBIEE, DAC, Informatica , Oracle Pl\sql and DW Concepts
Thing to remember, it also depends on weather interview you are facing is for consulting or full time ? in requirement OBIEE is good to have skill or mandatory ? As question and depth of it vary based on that.
One suggestion Always be prepared. Its better to be over prepared. I have seen that people with lots of experience sometimes does not perform well. And only reason for that is lack of preparation. Working day to day job and attending interview are two different things.
So lets get started…
I will keep updated this blog to add answers and more questions as well. So stop by often
Below are obiee questions
OBIEE
– Explain the architecture of OBIEE including client components.
– What is the difference between Siebel and OBIEE over all and architecture wise? ( If your resume says Siebel experience that you are likely to encounter this question.
– What are level based measures. Explain it with practical example.
– What is connection pool ? What kind of connection pool you were using ?
– What was the max connection in you connection pool ? How did you come up with that number ?
– Some report is performing very poorly and takes about 15 mins to show results. What would you do to make it faster ? from where you would start ?
–Let say you want to create a new column in BMM layer. That can be derived from an existing column in BMM/physical layer. How would you create that column ?
– How would the aggregation looks if in above – you base new column on BMM column vs physical columns.
– What are the various utilities provided by OBIEE ? Which one did you use most in the past ?
– Time series related questions
– How was the security set up in your last project ?
– In case of ldap authentication, how user session was getting populated ?
– What is umdl ? How can you generate udml file from Repository ?
– Explain : How can you change the connection pool using udml command line utility.
– If you have aggregate tables – Then how/where you specify that report should go to Aggregate table and not detail table.
– Have you implement the data level security ? What are various ways you can implement that ?
– What is system session variable ? When does it get initialized ?
– What is static and dynamic variables ? When does these variable get refresh ?
– If moved catalog from one environment to another. Which service(s) you need to bounce ?
– Can you change the connection pool in online mode to be effective ?
– If you change the column names in presentation layer. Would existing reports referring these columns work or break ? why ?
– Can you implement snowflake schema in OBIEE ? If yes then how ? if not then why ?
– Which configuration file you have changed in OBIEE ? What have you change in them and why ?
If you are attending interview at big brand names then I will also encourage you to visit glassdoor.com as they have very specific questions based on company