OBIEE Basics

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

1. RPD Oracle BI/Server/Repository/RPD file
2. 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

1. Check if RAM capacity is 1GB
2. Install Oracle 10g Client
3. Install JDK
4. 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

1. Physical
2. 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,

1. expression builder to create a new logical column with a formula
2. 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.