Why do I need to use DAC ?

Data Warehouse Application Console (DAC) works together with Informatica to acomplish the ETL for pre-packaged BI applications.
Here are what happen behind the scene:

• DAC publish the changes from OLTP
• Informatica extracts the changes from the change log published by DAC as well as from the base table
• Informatica load the data to the stage tables and the target tables in the data warehouse
• DAC manage the performance by dropping indexes, truncating stage tables, rebuilding the indexes, and analyzing the tables during the process

If you do not use DAC, you have to write your own custom change capture process and need to redesign from scratch an ETL method that allow the restart of the ETL process from point of failure at record level. The biggest saving is that DAC can survive during the upgrade , while your custom processes cannot.

Where can I use a presentation variable?

• Answers :
• Title Views
• Narrative Views
• Column Filters
• Column Formulas
• Conditional Formatting conditions
• Chart scale markers.
• Gauge range settings.
• Static text.
• Direct Database Requests
• Dashboard prompts
• iBot Headlines and text

Row level security using Initialization Blocks and Session Variables

Row level security using Initialization Blocks and Session Variables

What is Initialization Block and session variable in OBIEE +?
How to use row level security in OBIEE ?
If you a familiar with the web development you should be aware of the concept call constructor. Constructor is automatically called when object is initiated. And object initiated means; object is assigned to the memory. Destructor is does the exactly opposite work by cleaning/freeing the memory used by object.
And object here is Session.
So long story short. You can use initialized the session variable under Initialization Block in OBIEE +.
Session variables are like a global variable. They are available across the repository.
Types of Session Variable
System: Session variable
System session variables are session variables that the Oracle BI Server and Oracle BI Presentation Services use for specific purposes. System session variables have reserved names that cannot be used for other kinds of variables (such as static or dynamic repository variables and no system session variables).
Examples of the System session variable
USER Holds the value the user enters as his or her logon name.
EMAIL Contains the user’s default email address for use with Answers
Non System: Session variable
The procedure for defining non system session variables is the same as for system session variables.
A common use for non system session variables is setting user filters. For example, you could define a non system variable called   USERNAME in UsageTracking.rpd repository. So that each user can just see their usage not every one else’s Usage.
Below are the steps on how to achieve this.
Step 1: Create an initialize block called setUser

Initialization Block

Variable Manager

Step 2: Create session variable called: LOGIN

Variable Creation

Step 3: Set the filter in Presentation Layer for the Column.

Variable Filter

Step 4: Run the report in Answer to verify that filter and all is working.

Answer’s Report

Wow…. We just got the data related to customer’s only
Step 5: Check the sessions
Now when report is running check in the OBIEE Administrator
go to Manage – -> Sessions

Session Manager

Great we can check the Session Variable LOGIN and its Value is Administrator which is user who is currently logged in.
It gives lots of other information about the session like
Session id which is unique for each Session
Variable contains the name of the session etc
Step 6: Check logs
Make sure that for Administrator or the user for which you want to see the logs , proper log level has been set. Logging level 5 is good enough to see the query being sent to database.
Logging level 0 Means no Logging
Logging level 7 Max logging
Please note: On production system Logging is overhead unless and until it is absolutely required.

Check Log Level

Go to \server\Log\ NQQuery.log
You will see the screen shot as attached below

Log file

OBIEE Basic interview Qns stuff

whats OLAP / OLTP?

Whats schema u r using ? how many Facts & Dim tables?

Explain all layers (or tell the process flow in Rpd)

How you join tables in SA ?

What objects available in Physical Layer?

How SA identifies Fact table?

Can u have multiple connection pools?

What is use of BMM layer ?

How to create Dims? What is Preferred Drill path?

How u use Time series Wizard (for calculation wizard ur fact table must contain at least 1 aggregations like sum, u can ask that)? Calculation wizard? Pooling of table

can u add Logical column in BMM ? whats benefit of adding that column in Physical layer.

1-2 questions on Physical layer

Data level security & Report level security

Initialization block , Session, Repository variable. & why they used

repository migration all possible ways. What is use Multi check in – Check out.

NQSConfig.INI & DBFeatures.INI how these files configured? Is there any other way to configure “DBFeatures.INI” file? Also about other config. Files.

Some questions on Cache ?

some questions on various types of reports. Pivot , graph , table .

some basic questions on Dashboard ? pages

Performance improvement at report level & at Repository level.

What are iBots , how they configured , & their use.

how much is size of ur rpd & webcatalog?

General (but important)

how is the deployment process (Dev-QA-prod)

How u support the application

load initial & incremental

how many environment

which quality process, document u follow (if interviewer work in CMM 5 level company he must be aware of those)

OBIEE Timestamp

TIMESTAMP 'YYYY-MM-DD HH24:MI:SS'

TIMESTAMP '1990-01-10 13:35:43'


Time stamp difference in obiee
I have two columns with TIMESTAMP data type(One is In time and the other is Outtime).The values are as below.
In Time Out Time
14-jan-08 1:40:45 AM 14-jan-08 1:30:45 PM
15-jan-08 3:22:53 PM 15-jan-08 5:23:54 PM
16-jan-08 3:20:50 PM 16-jan-08 4:23:54 PM
17-jan-08 3:13:44 PM 17-jan-08 7:23:54 PM
I need the out put as below

00:11:50:00
00:2:01:01
00:1:03:04
00:4:10:10


Solution:

CAST( TimestampDiff(SQL_TSI_DAY, timestamp1, timestamp2) AS CHAR(2)) || CAST( TimestampDiff(SQL_TSI_HOUR, timestamp1, timestamp2) AS CHAR(2)) || CAST( TimestampDiff(SQL_TSI_MINUTE, timestamp1, timestamp2) AS CHAR(2)) || CAST( TimestampDiff(SQL_TSI_SECOND, timestamp1, timestamp2) AS CHAR(2))

Difference between OBIEE and Siebel Analytics:

Siebel Analytics:

Siebel Analytics is a Data Warehouse that were coming out of the
box with pre configured ETL mappings from source (siebel crm) to
target (DW)with its reporting component DASHBOARD (Siebel
Answers, Disconnect analytics and Administration tool to build
dashboard reports => .rpd file)

OBIEE:

What is OBIEE

Prebuild Informatica ETL mappings are only comes if you are buying analytics application version 7x and up and the latest version is 7.9.3 with OBIEE server. Licensing fees for for informatica is included with your analytics application.

OBIEE server can be implement and run dashboard report from a physical database (DW, OLAP). You can use any ETL tool (OWB, Informatica, SSIS etc) and design and develop your own ETL mappings and load your DW.
 I think OWB comes free with Oracle 10g enterprise edition

OBIEE stands for Oracle Business Intelligence Enterprise edition
(Formerly known as siebel analytics).

OBE EE is rebundled Siebel Analytics. It is much better than Discoverer (now called OBI SE).

What is the difference between SE, SE One and EE??

SE - Primarily contains Discoverer, Oracle Reports etc. This is what Oracle had before the acquisition of Siebel and Hyperion. I would recommend not to look at SE as an option unless you have made significant investments on this before

SE1 - It primarily contains components like Oracle Answers, Dashboards, BI Publisher, Oracle DB SE1 and OWB. The reporting software for SE1 and EE are the same. But SE1 contains other components like DB SE1 and OWB. Also, it has licensing restrictions like you cannot use delivers, disconnected analytics etc and also you cannot license it for more than 50 users. So, if your requirement is satisfied alone by using Answers, Dashboards etc then you can go with SE1. But its recommended you go with EE if you have plans of adding more licenses in future that would exceed 50. Remember, you cannot schedule any reports using SE1.

EE Plus - It is the full BI software. It also contains some Hyperion components. Always start with EE plus. If you feel it is not affordable or if you feel you have no plans of expanding then go to SE1.

So a long story cut short, decide upon SE1 or EE based on what your requirements are. If you have experience in discoverer, i would still recommend to go with SE1 or EE not because Discoverer is inferior or so, but because that’s where more development is happening

When Oracle Corp. has acquired Siebel System and decided to make
siebel analytics their flagship analytic engine and renamed it
OBIEE (obiee server + ETL engine+ Reporting). Now this product
is coming with preconfigured ETL mappings and DW schema
available for Siebel CRM, Oracle E Business Suite (Financials,
HR etc.), JD Edwards, and SAP.

So keep in mind OBIEE is a complete Data Warehouse technology
(server+application+ETL+report) where Business Object (BO) is
reporting component which is one of the best reporting product
available in the market.

BO can also access and deliver reports that can be build based on
OBIEE DW tables

Difference Between OBIEE and BI Apps

what is the difference between OBIEE and BI Apps
OBIEE is a reporting/trending tool where reports can be generated using OBI Answers, Published using OBI Dashboards and Delivered using OBI IBOTS. OBIEE formerly know as siebel analytics formerly known as Nquire. Now Hyperion is mergerd/combined with OBIEE and there is place for this combination in the market (I guess).

OBIEE is same as microstrategy/cognos/business objects/sap bw/ many more in market today.
clients(in real time, no one does this) can also Ad-hoc reporting by themselves with out dashboard developers
BI Apps is a prebuilt/pre-packaged/delivered/out of box Material which is developed by Oracle Corp thinking that this OOTB dashboards(webcatalog), repository, informatica adopters will be good enough for clients who expect to invest low on human resources and use the analysis tool immediately.
most of the cases, clients may end up customizing their ERP/CRM/Applications where those customizations have to be done in Informatica, DAC and BI Apps RPD.
BI Apps mean or iincludes Informatica, DAC and RPD, WEB CATALOG etc.