TRAINING (OBI-4) - OBIEE ADMIN TOOL - Part2 (Building Physical Layer)

PHYSICAL LAYER

• Is First layer in the repository development.
• Defines the data sources to which Oracle BI Server submits queries.
• The data sources can be of the same or different varieties.
• There can be one or more data sources in the Physical layer, including databases, flat files, XML documents.
• You can import schemas or portions of schemas from existing data sources.
• You can also create objects in the Physical layer manually.
• When you import metadata, many of the properties of the data sources are configured automatically based on the information gathered during the import process.
• After import, you can also define other attributes of the physical data sources, such as join relationships, that might not exist in the data source metadata.
  
  

Physical Layer Objects

1.) Database

• Highest/top level object in Physical layer.
• Defines the data sources to which Oracle BI Server submits queries.
  

2.) Connection Pool 

• Defines how Oracle BI Server connects to a data source.
• Specifies the ODBC or native data source name. 
• Allows multiple users to share a connection pool of data source connections.

3.) Schema Folders

•  Optional display folder that contain tables & columns for a physical schema.

• To create a schema folder, right click database object and select New Object > Physical Schema

4.) Physical Schema

• Represent the actual DB user/Schema name

5.) Physical Table

• An object that corresponds to a table in a physical data source.
• Is typically imported from a database or other data sources.
• Provides the metadata necessary for Oracle BI server to access the tables with SQL requests.

I.) Physical Table - Alias

• A virtual physical table object that points to a physical table object.

o   Right Click physical table and Select New Object > Alias

o   Provide name for Alias table and Alias table will appear with an Alias icon in physical layer.

II.) Physical Table - Select Table Type 

          

• Specifies that a physical table object is a SELECT statement.
• Select statement is called as OPAQUE VIEW.
• If need a new table then go for the physical table or materialized view, in worst situation go for opaque view.

III.) Physical Table - View Deployment

• Creates a corresponding database view for metadata views.

6.)  Physical Columns

• An object that corresponds to a column in a physical database.

7.) Key Columns

• Defines relationship between tables.

• Primary Key:
• Uniquely identifies a single row of data
• Consists of a column or set of columns
• Two or more columns acting as primary key then its called as COMPOSITE KEY
• Is identified by a key icon

• Foreign Key:
• Refers to the primary key columns in another table
• Consists of a column or set of columns

For faster execution its always a best practice to have key column in every table at Physical layer.  Also in OBIEE there is no forign key everything is a primary key.

How to build Physical Layer:

To build the Physical layer of a repository, you need to perform following steps:

1.) Create a New Repository

Already covered in - TRAINING (OBIEE-3) present @

http://technochanakya.blogspot.in/2015/12/training-obiee-3-obiee-admin-tool-part1.html

2.) Import Metadata

Already covered in - TRAINING (OBIEE-3) present @

http://technochanakya.blogspot.in/2015/12/training-obiee-3-obiee-admin-tool-part1.html

3.) Verify Connection

Refer above notes.

4.) Create Aliases

Refer above notes.

5.) Create Physical Keys and Joins

A relationship between tables is called as JOIN. In OBI Admin tool's Physical Layer, Joins represent Primary Key- Foreign Key relationship between tables.

Database Level Joins (5 types of joins in general):

1.) Equi Join

2.) Non Equi Join

3.) Outer Join(left, right, full)

4.) Self Join

5.) Cross Join

  

OBIEE (11g) Level Joins (3 types of joins in general):

1.) Foreign Key Join

> Equivalent to Equi Join @ DB Level

> Accepts only = operator

> Most used join in physical layer

2.) Complex Join

> Equivalent to Non Equi Join @ DB Level

> Accepts all operators like (=,<>,=>,,=…….etc)

> Less used join in Physical layer

3.) Logical Join

> Equivalent to Outer Join(left, right, full) @ DB Level

> BMM layer Join is called as Logical Join

> In OBIEE 10G it is also called as Complex Join

Other DB Joins in OBIEE (11g)

Self Join 

> Using Alias (in Physical Layer)

Cross Join

>Not supported by any BI tool

How to view existing joins in Physical Layer:

If Joins are imported due to relationship defined at database level, you could view these joins following below steps:

In physical layer select schema object (scott) > click on physical diagram icon  on toolbar

OR

In physical layer select schema object (scott) > right click on physical diagram > objects and all joins

You should now be able to see the joins created due to relationship imported at database level.

How to create Foreign Key (Equi) joins in Physical Layer: 

In physical layer select schema object (scott) > click on physical diagram icon  on toolbar > click on new join icon  on toolbar > Drag the Join Tool from EMP to DEPT table

While creating joins manually at physical layer we should always consider CARDINALITY (how relationship between tables eg; 1-To-1, Many-To-1, Many-To-Many). We should always start at MANY side and ends with ONE side, as this will improve the performance of sql.

How to create Complex (Non-Equi) joins in Physical Layer: 

In physical layer select schema object (scott) > click on physical diagram icon  on toolbar > click on new join icon  on toolbar >  Drag the Join Tool from EMP to SALGRADE table

Correct the Expression as sal>=losal and sal<=hisal and click OK.



Double click on the join arrow/line between EMP and SALGRADE table and you should now see the operator changed to COMPLEX and also expression must automatically converted to fully qualified name of database, schema etc.