The 20 golden rules of OBIEE's RPD modeling

Physical Layer
1, always use foreign join in the physical layer, do not use complex join

2, when the data model is a star, the physical table is built with aliases (dim_,fact_ or Fact_agg as the prefix)

3, if possible, configure your connection pool to use a local driver to connect to the physical database. For example, use OCI instead of ODBC to connect to an Oracle database.


Business model Layer
4, all logical tables should start with dim_,fact_ or Fact_compund_.

5, the column names of all physical layers should not appear in the logical layer. The name of the logic must be "business-oriented". For example, use $ revenue instead of dollars

6, physical primary keys and surrogate keys should not appear in the business model layer.

7, the Dimension logical table must specify a logical key. This logical health should be business-oriented, such as "Employee Login" rather than "EMPLOYEE_PK"

8, dimension logical tables must contain only dimension attributes, and they should never contain any measure columns (there are aggregation rules).

9, the fact that the logical table should not specify a logical key.

10, in the fact logical table, each column is a measure column, and the aggregation rule is specified.

11, when defining a logical connection for two logical tables, use only "Complex join" (the default setting should also be used, unless you need to handle cross-database joins, you need to specify drivingtable)

12, the business model should contain only logical stars and should not be snowflake-shaped.

13, each dimension logical table should have a corresponding dimension hierarchy.

14, each dimension level is set to the appropriate number of elements. You typically specify a child hierarchy that is more than the number of elements in the parent hierarchy.

15, the appropriate "content level" should be set for each dimension and the fact logic table. The only scenario where you don't need to set a specific dimension's "content level" is when there is no logical relationship.

16, do not merge all measures into a single fact logical table. For example, you should put the "Forecast sales" and "Actual sales" measures into two logical tables---"Fact_sales" and "Fact_forecast"

Presentation Layer
17, when you have multiple subject areas, these common dimensions are listed in the same order in each topic area.

18, the name of the presentation layer's table should not start with dim or fact. If the table in the subject area is dragged directly from the logical layer, remove the prefix.

19, the Time dimension table is listed in the first position of each topic area. The Presentation layer table containing the facts should be listed at the bottom, and the presentation table should be called "measures"

20, there should never be a logical association of objects selected by the user from the subject area. If you have any objects selected from the same subject area that cannot coexist, then your subject area must be incorrectly designed.

The 20 golden rules of OBIEE's RPD modeling