Physical layer

1)   I would like to know in which cases we will use ' select object(s) only ' , ' object(s) and direct joins ' and ' object(s) and all joins ' of physical layer to join tables, since i am new to this field ( 2 month's ) please can any one explain me...

select object(s) only
If u select the tables dim1 dim 2,fact1 from physical layer and u need to know the join conditions between those selected tables then we ll go with above option
object(s) and direct joins
Select tables and click on "object(s) and direct joins" u ll direct joins between the tables
object(s) and all joins
For this we will get all the joins associated in physical layer 

2)   When we import the tables to the physical layer and after that there are few changes to the tables like adding or deleting columns. Do those changes reflect in the physical layer tables as well or do we have to import the modified table again to the physical layer and delete the previous table

After importing tables in physical layer if you modify those tables in database those changes will not be reflected in physical layer. You don’t have to import tables again if you add only one or more columns. You can simply create physical column in rpd with the same name and data type as of database. In the same way if you delete any column in database table then you can delete that column from RPD.

3)   Is Fact table is necessary in Physical Layer. I don't have any fact. i have only 5 Dimensions. How can join the tables in the physical layer.

No join in physical layer . Drag and drop 5 dim tables from physical layer to bmm layer and create a dummy table  say fact and join 5 dim tables to dummy table  then drag 5 dim tables from bmm layer to presentation layer. Create reports

4)   I am trying to import tables from database to the physical layer. when we select objects to add to physical layer which boxes to we need to check below. I know we need to check 'Tables' and 'Keys'. Other than these two do we check any other box

that two box (tables and keys) are sufficient for import tables to repository.

5)   When I try to do a foreign key join in Physical layer a message pops up saying ' A matching table key doesnt exist in table. Do you want to create one'. Sould I just say 'yes' to it. Any idea why this message is coming?

f you want create manually a foreign key, you should click 'Yes'. That message appears when OBIEE can't establish an automatic relationship between keys of tables you are joined.

6)   After I have created all business model and the presentaion layer,the table structure in the database is changed.
In order to synchronize the physical layer with the database do i need to create a new physical layer again?or is there any other way I can do it.

n rpd,
go to Tools > Utilities > Update Physical Layer
click on Execute...

give the source name and execute.

anyway, keep backup before doing this..

The wizard does not add columns or tables to the repository that exist in the back-end database but not in the repository. Additionally, the Wizard doesn't update column Key assignments. It checks that there is a column in the repository that matches the column in the database, and then, if the values don't match, the wizard updates the type and length of the column in the repository.

I want the wizard to add the columns to the table in the physical layer and remove the column from the table if the column doesn't exist in the database.

7)   I 've created a duplicate table in physical layer , When i check view data on duplicate, getting an error. (Table or view doesn't exist). How to get out of it.

1 When you create a duplicate in rpd, it wll create a new table having #1 in the end. For eg: if I create a duplicate of Sales then in rpd it will create as Sales#1 and when u do a update count or view data on duplicated table it wll run on "sales#1" and since sales#1 will not be there in ur db it will throw an errror that table or view does not exist. So, better to create a alias and use it instead of duplicate.

2 When you duplicate a table, you create a new physical table with a new name. If this table is involved in a query, the SQL FROM clause will list this table. If the table does not exist in the database, then an error will occur. 
Creating an alias creates a copy of the table in metadata that will be referenced in SQL with a new alias name. The alias name in SQL will be derived from the ID given to the alias in the metadata. 

The important point is that you are not creating an object in the metadata that requires you to create a new object in the physical database. If you duplicate a table in metadata, then that new table must map to a separate table in the physical database. If it doesn't exist, you will need to create it or you will get a SQL error

8)   I have 5 dimension tables. I need to take 5 reports individually for those tables. Do i need to create dummy table in the physical layer for fact? Please suggest me...

9)   i have two tables in physical layer , and two tables are different and dont have any joining condition . All i want is i want to see those two tables data indvidually in Answers .

You can create a dummy table and make joins in BMM layer.
Because you need to have a fact and dimension join condition, otherwise you will get an error.

10)                Physical Layer Naming Convention 

_F for Fact
_D for Dimensions
All in UPPER case

For Alias use CLOSING DATE (W_DAY_D)

11)                physical layer have some data. but i want to decrease the data in the physical layer without delete. how u can do. how many ways are there to do this process? 

Create opaque view in physical layer so that restrict the data in view itself

12)                How to I update the table in the physical layer in the OBI admin tool when:

1. the data in the underlying database table changes (meaning the records/rows are added or deleted in the database)
2. the fields in the underlying database table change (meaning columns are added or deleted)
3. the size of the field changes (meaning the type or length of the columns change)
4. only the table names/column names in the underlying database change.

In which of the above cases do I have to import again through the connection pool. If I have to import again do I have to create physical joins in the physical layer and complex joins in the BMM layer and mapping to the new table as LTS and any expressions in the column mappings again?

1. the data in the underlying database table changes
(meaning the records/rows are added or deleted in the
database):

You don't need to take any action here unless you are concerned with query caching. In that case use 'event polling table'. Lookup documentation for event polling table.

2. the fields in the underlying database table change
(meaning columns are added or deleted)

You will need to manually add those in physical layer and propogate to business and presentation layer.

3. the size of the field changes (meaning the type or
length of the columns change)

Go to utilities - update physical layer and select the affected presentation layer.

4. only the table names/column names in the
underlying database change.
You will need to manually change those in physical layer. No need to and propogate to business and presentation layer ans its is automatically taken care of.

In which of the above cases do I have to import again
through the connection pool. If I have to import
again do I have to create physical joins in the
physical layer and complex joins in the BMM layer and
mapping to the new table as LTS and any expressions
in the column mappings again?

No need in any case to import manually. 

if i create a new column of a table in physical layer of a table.This new cloumn is not show in database.How can i synchronize with database ???


he physical layer only refers to what already exist in the DB. 

If you have a (new) column in a table in your DB, then you can create a new physical column in the respective physical table in physical layer so BI server knows about it.

However, it is not possible to create a column in your database by just creating a physical column in physical layer. 
2 After creating the column in the database, simply import the column(s) using 'File > Import Database..'. This will bring up the Import screen. Navigated thru Database, Schema, and Table to select the Column(s) you need. After you have selected the column(s) click on 'Import'.

What difference does it make in defining the keys in the physical layer in the following two ways.

I have a fact table with the following columns
PERKEY
PRODKEY
MKTKEY
UNITS
DOLLARS

As the name suggests perkey, prodkey and mrkkey are the keys in the table.

Approcah 1:
Select the table.
Right click and in properties of the table click on keys tab and
select new key and
check all the three columns.

Approach 2:

Select the table
Right click and in properties of the table click on keys tab and
select new key and
check perkey only
again select a new key
check PRODKEY only
again slect a new key
check matkey.
In this way you will find the three keys in the properties

Approach 1: will give u just one key which is actually combination of 3 cols from the table acting as composite here. Like country, state, city ...all these 3 cols will together form a foreign key in fact and will be used together during join conditions

Approach 2: will give u 3 keys each based on 3 individual cols and are used seperately using join conditions.

One more way is to auto create keys at the time of join definition which will give u a key based on number of cols used in join conditions.


Duplicate table and Alias table in the physical layer 


> aliases are created to break the circular joins (which are not supported by obiee)
-> and, as per apps standards no operation can be done on base tables which are imported directly to physical layer. Only aliases are joined and will be used in BMM layer
-> useful to re-use the table more than one time , best example: W_DAY_D in apps rpd
2 Have you gone through the link saichand provided?It is well explained in that thread.Duplicate will carry all joins of original table.Alias will not carry any joins.Moreover if you create any column in original table.It will automatically reflect in alias whereas in duplicate it wont.
Why is it necessary to create a view in the physical layer when we can create the table or a view in the database?
Does it affect performance in any case?
The view in the physical layer is not transparent to the database.. it's opaque.

This view (rpd) is isolated with the database.. 
if we want we can deploy this view in database.

But, all time we may not have necessary permissions to deploy this view or to create this view manually in db. Customer may not accept this all time.
At that time we go for view creating in physical layer of rpd. 

There's always performance issue using view. Using direct tables, execution time will be less...

when we are having very complex reports, we go for writing views in physical layer... but remember, by writing views in rpd, it may not support the ad-hoc query, which is one of the objectives of Oracle BI.


I make one View of 10 tables , and import this view IN PHYSICAL LAYER . Is it necessory to make fact table in Business Model and Mapping layer?.


create two individual logical tables in bmm layer with your view as only source. Now set Complex joins to make a logical table as fact. 
In this way you avoid creating aliases and self-joins.


 Diffence between physical layer and BMM layer 


The Administration Tool is a Windows application that allows the Oracle BI Administrator to create and edit repositories.

Physical layer: Represents the physical structure of the data sources to which the Oracle BI Server submits queries. 
ie, Connection Pool-contains information about the connection to the database. 
You can specify the Joins between the tables and you can change the Data Types of the Columns.

BM & M layer: Represents the logical structure of the information in the repository. 
ie, Uou can create Dimension, Hierarchies, Measures, Calculations, Aggregations and Time Series

Presentation layer: Represents the presentation structure of the repository. This layer allows you to present a view different from the Business Model and Mapping layer to the users. 


2 The physical layer is the layer of the Logical Business Model where you define the physical data model of your data source. It contains information about the physical data sources.

The most common way to create the schema in the Physical layer is by importing metadata (table, view, …) from databases and other data sources. If you import metadata, many of the properties are configured automatically based on the information gathered during the import process. You can also define other attributes of the physical data source, such as join relationships, that might not exist in the data source metadata.


In the Business Model

In the business model, OBI recommends using complex logical joins instead of foreign key logical joins.

When Complex joins are used in the business layer then they are really acting as placeholders. They allow the OBI Server to decide on which are the best joins define in the Physical Layer to satisfy the request.

In a Dimension, you may have multiple Logical Table sources that all join to one fact table. The OBI Server will then decide the best joins to use to meet the requirements.





I already have oracle physical table in Physical layer imported last month. But the physical table definition changed . It has 3 extra columns. How do I get those 3 columns in physical layer in RPD? Do I have to import the table again?


you can create columns physically or can import. 
While importing select only those columns to be imported...
It'll import only new (selected) columns into physical tables..
2 Instead of importing it again to physical layer ...just create those three columns with same name as in "database" and "data type " too....click on view data check whether u are able to get the newly added columns data or not