Design Tips on OBIEE Physical Layer
The following is a list of tips to use when designing the Physical layer of Repository in OBIEE
1. Before importing metadata from a data warehouse into the Physical layer of your repository,eliminate all outer joins in the data warehouse. Use Extract, Transform, and Load (ETL) look-ups to find them. Eliminating outer joins results in a more consistent record set, a simplified business model, and improved performance.
2. To reduce problems that might be caused by importing physical joins, import the physical data without foreign keys and then create the keys as needed.
3. You will probably import some tables into the Physical layer that you might not use right away but that you do not want to delete. One way to identify tables that you do want to use right away in the Business Model and Mapping layer, is to assign aliases to physical tables before mapping them to the business model layer.
NOTE: To have the name of a table to which you assigned an alias appear, make sure you turn on the following option in the Tools > Options > General menu path: Display original names for alias in diagrams.
4. Use a naming standard in the Physical Layer that identifies the logical components followed by the physical table name as shown in the following example:
Created By Employee (W_PERSON_D)
NOTE: The Physical layer object name is included in the Physical SQL. By using qualified Physical Layer names, debugging is made easier.
5. An opaque view (a physical layer table that consists of a Select statement) should be used only if there is no other solution. Ideally, a physical table should be created, or alternatively a materialized view. A traditional database view is not needed because it is identical to the opaque view.
1. Before importing metadata from a data warehouse into the Physical layer of your repository,eliminate all outer joins in the data warehouse. Use Extract, Transform, and Load (ETL) look-ups to find them. Eliminating outer joins results in a more consistent record set, a simplified business model, and improved performance.
2. To reduce problems that might be caused by importing physical joins, import the physical data without foreign keys and then create the keys as needed.
3. You will probably import some tables into the Physical layer that you might not use right away but that you do not want to delete. One way to identify tables that you do want to use right away in the Business Model and Mapping layer, is to assign aliases to physical tables before mapping them to the business model layer.
NOTE: To have the name of a table to which you assigned an alias appear, make sure you turn on the following option in the Tools > Options > General menu path: Display original names for alias in diagrams.
4. Use a naming standard in the Physical Layer that identifies the logical components followed by the physical table name as shown in the following example:
Created By Employee (W_PERSON_D)
NOTE: The Physical layer object name is included in the Physical SQL. By using qualified Physical Layer names, debugging is made easier.
5. An opaque view (a physical layer table that consists of a Select statement) should be used only if there is no other solution. Ideally, a physical table should be created, or alternatively a materialized view. A traditional database view is not needed because it is identical to the opaque view.
