Physical Layer

It is recommended that Foreign key join is to be used to join tables in Physical layer also to create alias tables for every base table.

Foreign Key join is used to connect two tables using primary key and foreign key present in the tables. Alias acts as a pointer. Alias is the copy of the base table. New columns cannot be created in alias tables. Any changes made to base tables are automatically reflected in Alias tables. It is recommended that joins are created among the alias tables.

To open Admin tool and create new rpd file --- Programs> OBI>Administration
 
Go to File>Import  from database> select DSN created >select tables needed.
 
After the tables are imported, we can see the database tree structure with connection pool and schema. Schema consists all the tables imported. Connection Pool contains the information for Call interface (ODBC, OCI etc), schema in database etc. OCI is recommended for Oracle database.
 
Then create alias tables for the tables imported. RC (right click) on table in Physical layer > new object> alias. Create join between two tables (ofcourse fact and dimension) by selecting two tables and RC and go to physical diagram> select objects only, which pops up a new window. Select Foreign key join at the top and select dimension table and then drag to fact table. Specify the primary and foreign keys and click OK.
 
Fact is the table which stores the metric.Dimension table stores the attributes related to facts.Fact table contain measures, foreign keys of other tables (dimensions). A fact surrounded by the dimension tables and if the fact is directly connected to all dimension tables then it is Star schema. Similarly if we have any sub dimension connected to dimension but not directly connected to fact, that can be refered as Snow flake schema.

View:
A View can also be created in Physical Layer. A view is created to combine dimension and mini dimension to a new dimension. A view is used to display different columns from two or more tables.
RC on Schema > new physical table and in type select 'select' and write required query. Then go to tab columns and enter all the columns mentioned in query.

Ex: select city, country, row_id, sales from customer, sales where customer.row_id=sales.customer_id;