OBIEE 11G Repository Design Tips for the Physical Layer
The following is a list of tips to use when designing the physical layer:
1) It is recommended that you use table aliases frequently in the Physical layer to eliminate extraneous joins, including the following:
a) Eliminate all physical joins that cross dimensions (inter-dimensional circular Joins) by using aliases.
a) Eliminate all physical joins that cross dimensions (inter-dimensional circular Joins) by using aliases.
b) Eliminate all circular joins (intra-dimensional circular joins) in a logical table source in the Physical Model by creating physical table aliases.
For example, say you have a Customer table that can be used to look up ship-to addresses, and using a different join, to look up bill-to addresses. Avoid the circular joins by aliasing the table in the Physical layer so that there is one instance for each purpose, with separate joins.
If you do not eliminate circular joins, you might get erroneous report results. In addition, query performance might be negatively impacted.
2) You might import some tables into the Physical layer that you might not use right away, but that you do not want to delete. To identify tables that you do want to use right away in the Business Model and Mapping layer, you can assign aliases to physical tables before mapping them to the business model layer.
3) An opaque view (a Physical layer table that consists of a SELECT statement) should be used only if there is no other solution to your modeling problem. Ideally, a physical table should be created, or alternatively a materialized view. Opaque views prevent the Oracle BI Server from generating its own optimized SQL, because they contain fixed SQL statements that are sent to the underlying data.
For example, say you have a Customer table that can be used to look up ship-to addresses, and using a different join, to look up bill-to addresses. Avoid the circular joins by aliasing the table in the Physical layer so that there is one instance for each purpose, with separate joins.
If you do not eliminate circular joins, you might get erroneous report results. In addition, query performance might be negatively impacted.
2) You might import some tables into the Physical layer that you might not use right away, but that you do not want to delete. To identify tables that you do want to use right away in the Business Model and Mapping layer, you can assign aliases to physical tables before mapping them to the business model layer.
3) An opaque view (a Physical layer table that consists of a SELECT statement) should be used only if there is no other solution to your modeling problem. Ideally, a physical table should be created, or alternatively a materialized view. Opaque views prevent the Oracle BI Server from generating its own optimized SQL, because they contain fixed SQL statements that are sent to the underlying data.
No comments:
Post a Comment