02 physical layer.pdf

8/19/2019 02 Physical Layer.pdf

1/39

OBIEE,OBIA,ODI,INFORMATICA ONLINE TRAININGS [email protected],09959531832

1. Physical Layer ----------------------------------------------------1

a) Fdggd

b)

fdgdf

2. Bmm

1. Physical Layer

1) Contains information about the physical data sources.

2)

The most common way to create the schema in the Physical layer is by importing metadata

from databases and other data sources.

3) It is having the direct relation between the repository and database.

4) The query will be executed from the physical layer only.

5)

We can't rename the object names in the physical layer.

6)

Contain multiple data sources

1.1 Physical Layer Components


2/39


1)

Database

2) Connection Pool

3) Catalog

4)

Schema

5) Physical Table

6) Physical Column

7)

Key Columns

Database objects

Highest – level object


3/39


Defines data source to which bi server submits queries

Physical Schema

1) Import from database Automatically Created or we can create manually

2) Contains Tables and Columns

Physical table

1) It one of the object in Physical Layer

2)

usually imported from a database or another data source or manually u can create

tables

3) Contains one or more Columns

4) A virtual physical table can be a stored procedure or a Select statement.

Physical Columns

1)

When we import schema Columns are automatically created

2)

You can create manually with same name and same data type.

3)

Columns will be PK , FK , Measures and Descriptive Columns

Key Columns

• Primary Key

1)

Uniquely identifies a single row of data

2) Comprised of a column or set of columns

3) Set of columns represent a compound or composite key

4) Identified by icon

• Foreign Key


4/39


1) Refers to the primary key columns in another table

2) Comprised of a column or set of columns

Physical key and physical foreign key relationship

1) A primary key and foreign key relationship defines a one-to-many relationship

between two tables.

2) A foreign key is a column or a set of columns in one table that references the primary

key columns in another table.

3) The primary key is defined as a column or set of columns where each value is unique

and identifies a single row of the table.

4)

You can specify primary key and foreign keys in the Physical Table Diagram or by using

the Keys tab and Foreign Keys tab of the Physical Table dialog box

Create physical Keys for physical tables

Step 1:Double click on SAMP_CUSTOMERS_D tableselect keyskey name as

cust_key columnsselect cust_key


5/39


Step 2:create physical keys for

SAMP_PRODUCTS_D= PROD_KEY

SAMP_TIME_DAY_D=CLAENDER_DATE

Joins In SQL

1.

Equi join.

2. Non equi join.

3. Cross join.

4. Self-join.

5.

Outer join(left, right, full)

Joins

1)

Physical Join or Simple Join

2)

Complex Join


6/39


Physical JoinS

1) Equalent to Equi Join

2) Accepts only = operator

3) Most used join in physical Layer

4) Primary Key – Foreign Key relationships between tables

5) When you import keys in a physical schema, the primary key-foreign key joins are

automatically defined.

Complex joins

1) a. Equivalent to Non Equi join

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

3)

c. Less used join in Physical layer.

4)

Complex joins are used to express relationships that do not involve a Primary Key –

Foreign Key relationship

Create complex joins

1)

Import scott schema

2)

Join emp table and sal table

3)

Join condition is emp.sal>=sal.losal and emp.sal


7/39


SAMP_REVENUE_F

SAMP_TIME_DAY_D


8/39


Step 2: Click the New Join button on the toolbar

Step 3:

Select the SAMP_TIME_DAY_D. CALENDAR_DATE column, and then select

SAMP_REVENUE_F.BILL_DAY_DT to join the tables. Ensure that the Expression edit box (at the botto

contains the following expression:


9/39


. Click OK to close the Physical Foreign Key dialog box. The join is visible in the Physical Diagram.

"orcl".""."BISAMPLE"."SAMP_TIME_DAY_D"."CALENDAR_DATE" =

orcl".""."BISAMPLE"."SAMP_REVENUE_F"."BILL_DAY_DT"

"orcl".""."BISAMPLE"."SAMP_PRODUCTS_D"."PROD_KEY" =

"orcl".""."BISAMPLE"."SAMP_REVENUE_F"."PROD_KEY"

"orcl".""."BISAMPLE"."SAMP_CUSTOMERS_D"."CUST_KEY" =

"orcl".""."BISAMPLE"."SAMP_REVENUE_F"."CUST_KEY"

. Click the Auto Layout button on the toolbar.


10/39


13

.

Select File > Save or click the Save button on the toolbar to save the repository.

Click No.


11/39


.

Complex join

Step 1: import tables from SCOTT schema

EMP

DEPT

Step 2 :right click on connection poolimport metadataselect foreign Keys

Step 3:

1.2 Database Types

1.2.1 Relational data sources.

1) During the import process, some ODBC drivers provide the Oracle BI Server with the

database type.

2) However, if the server cannot determine the database type, an approximate ODBC type is

assigned to the database object. Replace the ODBC type with the closest matching entry

from the database type drop-down menu.


12/39


1.2.2 Multidimensional data sources

1) Microsoft Analysis Services and SAP/BW are the only supported XMLA-compliant data

sources currently available.

2)

When you import a multidimensional data source, you need to select the appropriate

database type and version.

1.1)

Creating Physical Layer Folders

1) Creating Physical Layer Catalogs and Schemas

2) Using a Variable to Specify the Name of a Catalog or Schema

3) Setting Up Display Folders in the Physical Layer

1.2)

Creating Physical Layer Catalogs and Schemas

1) Catalogs are optional ways to group different schemas.

2)

A catalog contains all the schemas (metadata) for a physical database object.

3) A schema contains only the metadata information for a particular user or application.

4) Model the Physical layer after the way your data source is structured.

Note the following:

1) You must create a physical database object before you can create a physical catalog object

or a physical schema object.

2) After you implement a certain type of grouping, you cannot change it later. For example, if

you decide to implement database > schema > table, you cannot add a catalog afterward.

1) Creating Catalogs

In the Physical layer of a large repository, administrators can create physical catalogs that

contain one or more physical schemas.

To create a catalog:

1. In the Physical layer of the Administration Tool, right-click a physical database and select

New Object, then select Physical Catalog.

http://docs.oracle.com/cd/E23943_01/bi.1111/e10540/physicallayer.htm#i1006194http://docs.oracle.com/cd/E23943_01/bi.1111/e10540/physicallayer.htm#i1006215http://docs.oracle.com/cd/E23943_01/bi.1111/e10540/physicallayer.htm#i1006215http://docs.oracle.com/cd/E23943_01/bi.1111/e10540/physicallayer.htm#i1006194


13/39


2. In the Physical Catalog dialog, type a name for the catalog.

3. Type a description for the catalog, and then click OK.

2) Creating Schemas

The schema object contains tables and columns for a physical schema. Schema objects are

optional in the Physical layer of the Administration Tool.

To create a schema:

1. In the Physical layer of the Administration Tool, right-click a physical database or physical

catalog and select New Object, then select Physical Schema.

2. In the Physical Schema dialog, type a name.

3.

Type a description for the schema, and then click OK.

Using a Variable to Specify the Name of a Catalog or Schema

You can use a variable to specify the names of catalog and schema objects. For example, you

have data for multiple clients and you structured the data source so that data for each client

was in a separate catalog. You would initialize a session variable named Client, for example,

that could be used to set the name for the catalog object dynamically when a user signs on to

the Oracle BI Server.

You specify the session variable to use in the Dynamic Name tab of the Physical Catalog or

Physical Schema dialog.

Note:

The Dynamic Name tab is not active unless at least one session variable is defined.

To specify the session variable to use in the Dynamic Name tab:

1. In the Name column of the Dynamic Name tab, click the name of the session variable that

you want to use. The initial value for the variable (if any) is shown in the Default Initializer

column.

2. To select the highlighted variable, click Select.


14/39


The name of the variable is displayed in the dynamic name field, and the Select button

toggles to the Clear button.

To remove assignment for a session variable in the Dynamic Name tab:

Click Clear to remove the assignment for the variable as the dynamic name.

The value not assigned is displayed in the dynamic name field, and the Clear button toggles

to the Select button.

To sort column entries in the Dynamic Name tab:

You can sort the entries in a column by clicking the Name or Default Initializer column

heading. Clicking a column heading toggles the order of the entries in that column between

ascending and descending order, according to the column type.

Setting Up Display Folders in the Physical Layer

You can create display folders to organize table objects in the Physical layer. They have no

effect on query processing. After you create a display folder, the selected tables appear in the

folder as a shortcut and in the Physical layer tree as an object. You can hide the objects so that

you only view the shortcuts in the display folder

Note:

Deleting a table in a display folder deletes only the shortcut to that object. When you delete a

column in a display folder, however, the column is actually deleted.

To set up a physical display folder:

1.

In the Physical layer of the Administration Tool, right-click a physical database and select

New Object, then select Physical Display Folder.

2. In the Physical Display Folder dialog, type a name for the folder.

3.

To add tables to the display folder, click Add. Then, in the Browse dialog, select the fact or

physical tables you want to add to the folder and click select.

Alternatively, you can drag one or more physical tables to the display folder after you close

the dialog.


15/39


4. Click OK.

Physical Table

1) Usually imported from a database or another data source.

2) They provide the metadata necessary for the Oracle BI Server to access the tables with SQL

requests

3) A virtual physical table can be a stored procedure or a Select statement

4) In addition to importing physical tables, you can create virtual physical

5)

tables in the Physical layer, using values in the Table Type field in the Physical Table dialog

box.

6) Creating virtual tables can provide the Oracle BI Server and the underlying databases with

the proper metadata to perform some advanced query requests

TABLE PROPERTIES

in phsyical layer we can create types of tables.

1) phsyical

It is a regular phsyicle table which exists in a phsyical database.

here any phsyical object which phsyically exists in database or pshycal database

can be created as a phsyicle type of table.

2) select

in select type of table we can write any valid SQL.


16/39


generally it is a in-line view concept in database.

in case if we want to use any SQL output as a table then we can select table type

as select and write the SQL in SQL text box.

3) Procedure

Procedure helps to design any procedure output in physical layer.

In this case procedure output columns will become columns in physical object.

Dynamic table name

We can also provide table name in physical layer dynamically at run time.

We can use repository variables to generate table name dynamically and replace accordingly.

Cache

Table cache property enables query output to be cached and maintained as per cache

persistent time.

We can see more on cache manager

Deploy view

We can see this option enabled for the table which have created with table type select.

By selecting deploy view obiee will create a physical view on database against the

gainst which specified under

Select type database Sql.


17/39


Verify Connection

1 . Select Tools > Update All Row Counts .

2 . When update row counts completes, move the cursor over the tables and observe that row

count information is now visible, including when the row count was last updated.

3 . Expand tables and observe that row count information is also visible for individual columns.


18/39


4 . Right-click a table and select View Data to view the data for the table.


19/39


5 . Close the View Data dialog box when you are done. It is a good idea to update row counts or

view data after an import to verify connectivity. Viewing data or updating row count, if

successful, tells you that your connection is configured correctly.


20/39


Alias Table

An Alias table (Alias) is a physical table with the type of Alias

It is a reference to a logical table source, and inherits all its column definitions and some

properties from the logical table source.

To reuse an existing table more than once in your physical layer (without having to import it

several times)

In alias tables, columns cannot be added, deleted, or modified.

You cannot delete source tables unless you delete all its alias tables first


21/39


You can change the source table of an alias table, if the new source table is a superset of the

current source table.

1 . It is recommended that you use table aliases frequently in the Physical layer to eliminateextraneous joins and to include best practice naming conventions for physical table names.

Right-click SAMP_TIME_DAY_D and select New Object > Alias to open the Physical Table

dialog box.

2 . Enter D1 Time in the Name field.

3 . In the Description field, enter Time Dimension Alias at day grain. Stores one record for each


22/39


day.

4 . Click the Columns tab. Note that alias tables inherit all column definitions from the source

table.


23/39


5 . Click OK to close the Physical Table dialog box.

6 . Repeat the steps and create the following aliases for the remaining physical tables.

SAMP_ADDRESSES_D = D4 Address SAMP_CUSTOMERS_D = D3 Customer

SAMP_PRODUCTS_D = D2 Product

SAMP_REVENUE_F = F1 Revenue


24/39


Key Columns

•

Primary Key

– Uniquely identifies a single row of data

– Comprised of a column or set of columns

• Set of columns represent a compound or composite key

– Identified by icon

• Foreign Key

– Refers to the primary key columns in another table

–

Comprised of a column or set of columns

physical key and physical foreign key relationship

1) A primary key and foreign key relationship defines a one-to-many relationship between two

tables.

2) A foreign key is a column or a set of columns in one table that references the primary key

columns in another table.

3) The primary key is defined as a column or set of columns where each value is unique and

identifies a single row of the table.

4) You can specify primary key and foreign keys in the Physical Table Diagram or by using the


25/39


Keys tab and Foreign Keys tab of the Physical Table dialog box

Joins

Physical Join

Complex Join

Physical Join

When you import keys in a physical schema, the primary key-foreign key joins are

automatically defined.

Any other joins within each database or between databases have to be explicitly defined to

express relationships between tables in the physical layer.

1) Equalent to Equi Join

2)

Accepts only = operator

3) Most used join in physical Layer

Represent the Primary Key – Foreign Key relationships between tables in the Physical layer

Complex joins

Complex joins are used to express relationships that do not involve a Primary Key – Foreign

Key relationship

Used to formulate the join when building the SQL

1)

Equivalent to Non Equi join

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

3)

Less used join in Physical layer

Create Keys and Joins


26/39


Select the five alias tables in the Physical layer.

Right-click one of the highlighted alias tables and select Physical Diagram > Selected Object s)

Only to open the Physical Diagram. Alternatively, you can click the Physical Diagram button

on the toolbar.

Selected Object s) Only.

Displays only the selected objects. Joins appear only if they exist between the objects

that you select.

Object s) and Direct Joins. Displays the selected objects and any tables that join to the

objects that you select.

Object s) and All Joins.

Displays the selected objects, as well as each object that is related directly or indirectly to the

selected object through some join path. If all the objects in a schema are related, then using

this option diagrams every table, even if you only select one table


27/39


3 . Rearrange the alias table objects so they are all visible.


28/39


4 . You may want to adjust the objects in the Physical Diagram. If so, use the toolbar buttons to

zoom in, zoom out, fit the diagram, collapse or expand objects, select objects, and so forth:

5 . Click the New Join button on the toolbar.

6 . Click the F1 Revenue table and then the D1 Time table. The Physical Foreign Key dialog box


29/39


opens. It matters which table you click first. The join creates a one-to-many (1:N) relationship

that joins the key column in the first table to a foreign key column in the second table.

7 . Select the D1 Time. CALENDAR_DATE column, and then select F1 Revenue.BILL_DAY_DT to join the tables. Ensure that the Expression edit box (at the bottom) contains the following

expression:

"orcl".""."BISAMPLE"."D1 Time"."CALENDAR_DATE" = "orcl".""."BISAMPLE"."F1

Revenue"."BILL_DAY_DT"

8 . Click OK to close the Physical Foreign Key dialog box. The join is visible in the Physical

Diagram.


30/39


Please be aware of the following upgrade considerations for Oracle BI EE 11g Release 1(11.1.1.5): Joins in the Physical and Business Model diagrams are now represented by a line

with an arrow at the "one" end of the join, rather than the line with crow �s feet at the "many"

end of the join that was used in previous releases. When creating joins in the Physical and

Business Model Diagrams, you now select the "many" end of the join first, and then select the

"one" end of the join. In previous releases, joins in the diagrams were created by selecting the

"one" end of the join first.

9 . Repeat the steps to create joins for the remaining tables. Use the following expressions as a

guide. Please notice that D4 Address joins to D3 Customer.

"orcl".""."BISAMPLE"."D2 Product"."PROD_KEY" = "orcl".""."BISAMPLE"."F1 Revenue"."PROD_KEY"


31/39


"orcl".""."BISAMPLE"."D3 Customer"."CUST_KEY" = "orcl".""."BISAMPLE"."F1 Revenue"."CUST_KEY"

"orcl".""."BISAMPLE"."D4 Address"."ADDRESS_KEY" = "orcl".""."BISAMPLE"."D3

Customer"."ADDRESS_KEY"

10 . Click the Auto Layout button on the toolbar.

11 . Your diagram should look similar to the screenshot:.

12 . Click the X in the upper right corner to close the Physical Diagram.

13 . Select File > Save or click the Save button on the toolbar to save the repository.


32/39


14 . Click No when prompted to check global consistency. Checking Global Consistency checks for

errors in the entire repository. Some of the more common checks are done in the Business

Model and Mapping layer and Presentation layer. Since these layers are not defined yet, bypass

this check until the other layers in the repository are built. You learn more about consistency

check later in this tutorial.

15 . Leave the Administration Tool and the repository open for the next topic.

Congratulations! You have successfully created a new repository, imported a table schema

from an external data source into the Physical layer, created aliases, and defined keys and

joins.

In the next topic you learn how to build the Business Model and Mapping layer of a


33/39


repository.

Database object

Importing a schema automatically creates a database object for the schema but you need to set

up the database properties.

If you create a database object manually, you need to manually set up all database elements

such as connection pool, tables, and columns.

Allow direct database requests by default (check box)

Allow populate queries by default (check box)

Data Source Definition

CRM Metadata Tables

Database

Virtual Private Database

Persist Connection Pool

Allow direct database requests by default check box)

1)

When checked, allows everyone to execute physical queries.

2) The Oracle BI Server will send unprocessed, user-entered, physical SQL directly to an

underlying database.

3)

The returned results set can be rendered in Oracle BI Presentation Services, and then charted,

rendered in a dashboard, and treated as an Oracle BI request.

4)

If you want most but not all users to be able to execute physical queries, check this option andthen limit queries for specific users or groups

Allow populate queries by default check box)

1) When checked, allows everyone to execute POPULATE SQL.


34/39


2) If you want most but not all users to be able to execute POPULATE SQL, check this option

and then limit queries for specific users or groups

CRM Metadata Tables

Not available for multidimensional data sources. When selected, the import utility will look for

the table definition in Oracle's Siebel CRM specific tables. For Siebel CRM tables, the import

reads the Siebel metadata dictionary to define the definition of physical tables and columns (it

does not read the database data dictionary). This is for legacy Siebel Systems sources only.

Virtual Private Database

1) Identifies the physical database source as a virtual private database (VPD).

2) When a VPD is used, returned data results are contingent on the users authorization

credentials.

3)

Therefore, it is important to identify these sources.

4) These data results affect the validity of the query result set that will be used with Caching.

Persist Connection Pool

1)

A persist connection pool is a database property that is used for specific types of queries

(typically used to support Marketing queries).

2) In some queries, all of the logical query cannot be sent to the transactional database

because that database might not support all of the functions in the query.

3) This issue might be solved by temporarily constructing a physical table in the database and

rewriting the Oracle BI Server query to reference the new temporary physical table.

You could use the persist connection pool in the following situations:


35/39


Populate stored procedures.

Use to rewrite the logical SQL result set to a managed table. Typically used by Oracle's

Siebel Marketing Server to write segmentation cache result sets.

Perform a generalized sub query.

Stores a nonfunction sub query in a temporary table and then rewrites the original sub

query result against this table.

Reduces data movement between the Oracle BI Server and the database and supports

unlimited IN list values and might result in improved performance.

SQL Features Supported by a Database

1) When you import the schema or specify a database type in the General tab of the Database

dialog box, the Feature table is automatically populated with default values appropriate for

the database type.

2) These are the SQL features that the Oracle BI Server uses with this data source.

3) You can tailor the default query features for a database.

4) For example, if a data source supports left outer join queries but you want to prohibit the

Oracle BI Server from sending such queries to a particular database, you can change this

default setting in the Feature table.

Ask DBMS

A button that is used only if you are installing and querying a database that has no Features

table. It allows you to query this database for Feature table entries

Default

A check box that identifies default SQL features. Default SQL features that are supported by thedatabase type of the data source are automatically selected.

Find

A button that allows you to type a string to help you locate a feature in the list.


36/39


Find Again

A button that becomes available after you click Find. Allows you to perform multiple searches

for the same string.

Revert to Defaults

A button that restores the default values.

Value

A check box that allows you to specify additional SQL features. Select to enable a query type or

clear to disable a query type.

It is strongly recommended that you do not disable the default SQL features.

connection pool

1) The connection pool is an object in the Physical layer that describes access to the data source.

2)

It contains information about the connection between the Oracle BI Server and that data

source.

3) The Physical layer in the Administration Tool contains at least one connection pool for each

database.

4) When you create the physical layer by importing a schema for a data source, the connection

pool is created automatically.

5)

You can configure multiple connection pools for a database.

6) Connection pools allow multiple concurrent data source requests (queries) to share a single

database connection, reducing the overhead of connecting to a database.

create different connection pools for the same database?

Generally we go for 2 CPs--1) For Initialization blocks and 2) for Reporting

Better to create two different DSN for the same database with different user id and password

2)now create multiple connection pool in the same database in u r RPD physical layer .


37/39


I had that approach ready i.e to create a read only schema. But which is that i cannot import

from multiple schemas with in a same database. The only situation you use multiple

Connection Pools within the same database is when you want to separate the query's from the

initialization blocks. You use the same credentials for both Connection Pools. The first one is

automatically selected by the query while you point your initialization block to the second one.

options in Connection pool

Call interface

Data source name

Enable connection pooling

Execute on Connect

Execute queries asynchronously

Isolation level

Maximum connections

Name

Parameters supported

Permissions

Require fully qualified table names

Shared logon

Timeout (Minutes)

Use Multithreaded Connections


38/39


Alias Table in OBIEE

Alias table means it is a reference table for the physical Table. When you create any alias table,

then the alias table will inherit all the properties of the physical table.

We cannot modify the alias table properties, like1. Cannot rename the column name.2. Cannot change the column data type.

3. Cannot delete the columns

But we can only change the alias table name according to our naming convention.

Steps to create Alias table :

1. Right click on the physical table, New Object -->Alias

2. Give the name for alias table. Then click on OK.

Advantages of Alias Table:

1. OBIEE does not support Circular join in BMM layer, so we can overcome from this problem

by creating alias table for one of the table in that circular join.

2. OBIEE does not support self-join, so we can overcome from this problem by creating alias

table.

3. If a fact table having relationship with dimention table on more than one key, then we can

create aliases for the dimension talbe, then we can join fact table to these alias table on different

keys.Eg : consider, there is a order table, it has two columns in that, namely, order_date and

required_date, both columns have to be joined to the Time dimension. In such situation we can

create alias for the Time dimension, like Order_Date_dim and Required_date_dim. Now join theorder table to these tables on different keys.

Opaque view 1) A sql query is called as opaque view.2) If we need a new table then go for physical table (or) materialized view. In worst

situation go for opaque view.

3) Opaque views are not supported by non-relational db.

4)

It can be used as fact table or Dimension TableSELECT

P.BRAND,

P.PROD_KEY,T.CALENDAR_DATE,

T.PER_NAME_YEAR,

R.REVENUEFROM


39/39


SAMP_REVENUE_F R,SAMP_PRODUCTS_D P,SAMP_TIME_DAY_D T

Alias Table Duplicate Table

Its a mirror image of the Parent Table Its a Copy of the Parent Table

We can't edit or modify Alias Table

We can edit/modify/delete columns in

Duplicate table

Changes made in Parent Table is

automatically reflected in Alias table

Changes made in Parent table are not

reflected in Duplicate table

Used : When there is a need to avoid self-join

or circular join

Used when there is a need to use few

columns of Parent table.
http://1.bp.blogspot.com/-HTzHfNg1Jcc/UCT62qnMgqI/AAAAAAAAAgs/Q0XruW6vgWQ/s1600/2.JPG

02 physical layer.pdf

Documents