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.
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
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
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
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.
