This is the continuation of the OBIEE - Oracle Business Intelligence Enterprise Edition(Part 1)

1. Oracle BI Repository
   
   

Oracle BI server stores metadata in repositories i.e. in physical layer

The Oracle BI Administration tool has a GUI that allows server administrators to set up these repositories

Admin tool is used to

Import metadata from databases and other data sources like xml

Simplify and reorganize the imported metadata into business models

In presentation layer, the business model is structured so as to meet the expectation of business users who request BI information via oracle BI UI tools

Open the admin tool in order to open the rpd file.

The rpd file has three layered architecture namely

• Physical layer
  
  
• BMM layer
  
  
• Presentation layer

1. Physical layer
   
   

The physical layer containing physical object creates the data source and import the meta data.

2. Business Model and Mapping layer
   
   

The entire data modelling is done in BMM layer. It takes the request from the user and process that to the BI server engine and creates a physical sql query and then it fetch the database and the get the results back to the user.

3. Presentation layer
   
   

Presentation layer defines UI
The modelling is done in the presentation layer as per the end user requirements. Subject areas map to the business model. Presentation tables may map to logical tables. Presentation columns maps to logical columns.

4. Repository directory
   
   

By default, the repositories are stored in the repository subdirectory where oracle BI software is installed: ORACLE_INSTANCE\bifoundation\OracleBIServerComponent\bi_instance_name_obisn\repository

1. Repository modes
   
   

• The repository files can be opened for editing in offline mode or online mode
  
  
• In Offline mode all rpds in local machine can be opened since the repository is not loaded into Oracle BI Server memory
  
  
• In Online mode the repository deployed in the server alone can be opened
  
  
• Administrators can perform tasks that are not available in offline mode
  
  

Manage scheduled jobs

Manage user sessions

Manage the query cache

Manage clustered servers

• In Enterprise Manager, the default rpd deployed into the server is displayed.
  
  

5. Publish Repository
   
   

Go to Enterprise Manager

Go to Business Intelligence, then lock and edit

6. Restart OBI Services
   
   

After publishing repository the OBI services should be restarted as shown below.

7. Reload Server Metadata
   
   

After new changes is done in repository, instead of signing in and out the server metadata can be reloaded.

8. Query Processing
   
   

There are types of queries namely logical query and physical query. The logical query involves logical dimensional tables

The BI server converts the logical query into physical query involved in the underlying database

1. A user views a dashboard and submits an analysis
   
   
2. Oracle BI presentation services makes a request to Oracle BI server to retrieve the requested data
   
   
3. Using the repository file, Oracle BI server optimizes functions to request the data from the data sources
   
   
4. Oracle BI server receives the data from the data sources and processes as necessary
   
   
5. Oracle BI server passes the data to Oracle BI presentation services
   
   
6. Oracle BI presentation services formats the data and sends it to the query client
   
   

2. Building Physical Layer
   
   

• Contains objects representing the physical data sources to which Oracle BI Server submits queries
  
  
• May contain multiple data sources
  
  
• It is the first layer built in a repository
  
  

1. Database Objects
   
   

It is the highest-level object in the physical layer

Defines the data source to which Oracle BI Server submits queries

ORCL is inherited from tnsnames.ora file

2. Database Properties
   
   

3. Connection pool
   
   

Connection pool is defined as the connection between Oracle BI server and data source

The database objects and connection pool are automatically created when you import the physical schema

Oracle call interface is recommended for connecting to an Oracle data source

The data source name (DSN) information is used to connect to a data source, the number of connections allowed, timeout information, and other connectivity-related administrative details

Multiple connection pools can be created  to improve the performance for groups of users

A single database connection is shared by selecting the Enable connection pooling checkbox that allows multiple concurrent query requests

4. Physical Table
   
   

A physical table is an object in the physical layer of the Administration tool that corresponds to a table in the physical database

Physical tables are imported from a database or another data source, and they provide necessary metadata necessary for Oracle BI server to access the tables with SQL requests

When data source definitions are imported, actual data is not moved. Data remains in the physical data source

1. Physical table properties
   Use tabs to create, view, or modify table properties.

2. Physical column
   
   

3. Key column
   
   

Defines relationships between tables by using primary key and foreign keys

Primary key identified by a key column

• Uniquely identifies single row of data
  
  
• Consists of columns or set of columns
  
  

4. Physical table alias
   
   

• An alias table is a physical virtual table object that points to a physical table object. An alias table in the physical layer is like any other alias used in standard SQL notation
  
  
• A common use of aliases is role-playing dimensions, where a single dimension simultaneously appears in the same fact table
  
  
• Alias synchronization is the act of ensuring that the source table and related alias tables have the same column definitions
  
  
• An alias table always inherits all the column definitions from the source table, and synchronization happens automatically
  
  

The changes made in the table will be reflected in alias table also. Key columns should be made manually.

Joins

3. Creation of RPD

Select File>New Repository to create new repository. Name repository, browse location where repository file is to be stored, create repository password.

Import meta data as shown below



Select metadata types> Select Tables, Keys, Foreign Keys> Next

Then select the metadata objects: D1_Calendar2, D1_Customer2, D1_Orders2 >Next



Then Verify import in physical layer: Objects imported into the physical layer

After that verify the connectivity: Tools> Update all row counts>D1_Products> View Data

Create alias table: Physical> D1_Calendar> New Object> Alias> Name the Alias>Alias added to physical layer

As soon as the alias table is created, the joins and keys should be defined as follows

Click the Keys Tab> Provide name for key> Select appropriate check box to define

Select the tables that you want to join> Click the Physical Diagram icon (9th in row)/ or right-click the objects to open the Physical Diagram> In this example, tables are initially displayed without join definitions.

The joins are made as follows



The joins can be made from fact to dimensions (one to many relationship) and is made in BMM layer or reporting layer itself.

Training Administrator

Add comment