OBIEE
--To access critical business information and to provide optimal results.
Organizes
the data in the star format with fact table(numerical data) is surrounded by
dimensional table(alphanumerical data)
Architecture
is made of 5 components
1.
Clients
Access to client information using answers, dashboards, delivers
2.Oracle BI web server
Receives data from BI server and
presents it to clients
Uses .atr web catalog files to store the
aspects of application
3.Oracle Bi server
Access the physical data and structures the
information
Components
a.rpd
(metadata)
b.Cache
(Results of queries)
c.NQSConfig.ini (Stores RPD name )
d.DBFeatures.ini (Specifies values that control SQL
generation)
e.
Log files (NQserver.log and NQQuery.log
files)
4.Oracle
Bi Scheduler (Executes the job)
5.Data
sources(Flat files or any form of data)
Sample Request
Processing
 |
|
Clients Oracle BI Web server Oracle Bi Server Data Source
1.
Repository Basics
The RPD
is having 3 Layers.
Presentation Business Model and Mapping Physical
1.
Physical Layer
Physical
Database (DSN)
 |
Connection pool
Physical Catalog Folder
Data Source Schema
Physical Table
Physical
Key Column
2. Business
Model and Mapping Layer
Logical Table
Logical Table
Sources
3.
Presentation Layer
Presentation
Catalog/Subject Area (Subject area as it appears in
Oracle
Answers Screen)
2.
Query Repository Features
Tools
Query Repository (Short Cut Ctrl
Q)
Is
a utility provided by Oracle BI tool to examine the metadata objects.
Two
modes to open rpd
1
Offline:-The Oracle BI server is not started (always to be used)
2.
Online:- The Oracle BI server is started
When
you open the rpd in online mode it will display (online) at the top in the rpd.
About
Installation
Two
folders are created when you install OBIEE
1. Oracle BI
2. Oracle BI Data
Important
Paths
- Config
file Oracle BI/Server/Config/Configuration
file (NQSConfig.ini)
How to start
the services
1.
Control
Panel -> Administrative
Tools->Services->Open ->Click on (1.Oracle BI Presentation Server and
2.Oracle BI Server)
Or
2.
1.Go
to Run àServices.msdà Click on (1.Oracle BI Presentation
Server and 2.Oracle BI Server)
Installation
Requirements
- Check
if RAM capacity is 1GB
- Install
Oracle 10g Client
- Install
JDK
- Install
OBIEE 10.1.3.4
Building
physical layer of RPD
(Stores Physical Data sources)
Can
create reports based on multiple databases (e.g. one connection pool is
pointing to Oracle and other to Siebel) having different connection pools.
Can
create multiple connection pools pointing to Same Database
Login /Pwd for
User Id in DSN :SH/SH
TNS service
Name: Sample_O (It is there in tnsnames. Ora just like vis, proj1)
DSN: workshop
(The name of Database Object /Physical Database)
Structure of
Physical layer:
Physical Database (DSN)(any
type of data i.e xml, flat file ,normalized DB) Connection pool (Created automatically and bridge bet. DS
and Oracle BI Server)
Physical Catalog Folder
Data Source Schema
Physical Table
Physical
Key Column
Two
Types of Joins
- Physical
- Logical
(Will have logical queries which will be converted to physical queries
that are having reference to physical joins)
Cacheable :
2
options
1.
Cache never expires
2.
Cache persistence time
BMM (Organizes
physical sources into dimensional business model)
BMM
objects are
Business
models (Defines Subject area)
Logical
tables (Defines Data source
mappings)
Data
source mappings (Defines logical to
physical table mappings)
Logical
columns (maps to any number of
physical columns)
Logical
primary keys
Logical
table schemas (Defines logical table joins)
Measures (Calculation with measurable quantities)
Building
Presentation Layer (Presents the business data to the
user)
Multiple
presentation catalogs can refer to same business model
Appears
as Subject Area in Oracle Answers
Presentation
objects are automatically renamed when the corresponding logical object is
renamed
Presentation
tables cannot have the same name as the presentation catalogs (error is
received)
Presentation
objects can be deleted without affecting corresponding logical objects
To
expose the key columns to other ODBC clients, check the Export logical keys
check box
Testing and
validating RPD
Ways:
Check
repository for consistency (Checks for any kinds of errors i.e. logical column
not
mapped to physical column)
Turn
on logging (Test rpd configuration by
setting logging level.)
Logs are captured in NQQuery.log
file
Level 1 logs—session id, user id,
request id
Level 2 logsàlevel
1 logs + BM name ,Presentation catalog name, rpd name
Check
business model via Oracle BI ODBC Client
Use
Oracle Answers
Check
results by inspecting SQL
Dimensional
hierarchy
Level
keys –Unique identifier for the level
Logical Level
Grand Total
level and Default root level 
logical columns
To
create a calculation measure to a fact use,
- expression
builder to create a new logical column with a formula
- Select
existing columns as the objects in the formula.
There
are three methods to create calculation measure
1.
use existing logical columns as objects in formula
2.
use physical columns as objects in formula
3.
use calculation wizards
You
use logical columns for calculation formulas that require an aggregation rule
that is applied before the calculation. You can also build calculation
measures in Answers. The advantages to building calculation measures in the
repository is the measures are built once and can be made available to all
users. The advantage of defining a logical column formula based on existing
logical columns is that you only have to define it once. When you create
formulas based on physical columns, you have to map for each physical source it
could be derived from.
Variables (Contain
values) at RPD
Level
Session (active
till the user session is active)
System are
predefined e.g. New USER
Non System are
application specific and created by developer
Repository (active
from the time BI server is started till it is shut down)
Static Values are
constant when server is running and
are initialized
Dynamic Values change
according to refresh schedule and
are initialized
Initialization
Block
(To initialize session variables and Dynamic repository variables)
One
ib can be assigned to number of variables
VALUEOF(Var_name)
to use the value of the variable
Aggregate
tables
Aggregate
tables store pre-computed results, which are measures that have been aggregated
(typically summed) over a set of dimensional attributes
Times Series Function
Ago
Calculates
the aggregated value from the current time back to a specified time period. For
example, Ago can produce sales for every month of the current quarter and the
corresponding quarter-ago sales
ToDate
aggregates
a measure attribute from the beginning of a specified time period to the
currently displayed time e.g. YTD sales
Traverse
Folder
Authority
to access objects in folders within the selected folder when the user does not
have permission to the selected folder. Example: The user is granted Traverse
Folder permission to the /shared/test folder. The user cannot access objects in
the /shared/test folder, but can access objects stored in lower-level folders,
such as /shared/test/guest.
Merging the
rpds:
There
are 2 ways 1)3 way merge with common parent
2)3 way merge with no common parent
Query
logging is normally disabled because query logging can impact performance by
producing very large log files.
