Business Model Design
In Oracle Analytics, you can configure the logical layer in your semantic model in many different ways. Oracle recommends that you follow the best practices described here, so you can avoid several errors at runtime and significantly decrease your maintenance workload.
Best Practice
-
Use star schemas in business models
Data structure in the physical layer may come in many different forms. Having star schemas in the physical layer is useful for performance but it isn’t mandatory. However, no matter the structure of the physical layer, your business model should always be star schemas.
Each logical table can include multiple physical tables, either in the same logical table source or split across multiple logical table sources.
Description of the illustration ceal_star_schema_logical_layer.jpg -
Use a separate dimension logical table for each dimension
-
Don’t combine or merge dimensions into one logical table
-
Use a separate fact logical table for each fact
The same goes for facts, you don’t want to end up with a single fact logical table called “Facts – Stuff”!
-
Use a separate logical table for “Compound” facts
A compound fact is the place where you put derived expressions that combine metrics from multiple fact tables. For example, if you have Fact Order and Fact Opportunity, include a calculation with the formula “# Opportunities/ # Orders” in separate logical table Fact Compound Opportunity & Order.
-
Prefix logical table names with Dim – or Fact – or Fact Compound –
-
Assign unique business columns as dimension primary keys wherever possible
-
Rename logical columns to use presentation names
-
Keep only used columns in the logical layer
Description of the illustration ceal_keep_usedcols_logical_layer.jpg -
Don’t assign logical primary keys on logical fact tables
Logical primary keys are needed only on dimension tables.
-
Create “dummy” measures to separate facts into various groups, if required
Description of the illustration ceal_create_dummy_measures.jpg -
Ensure almost every fact logical column has an aggregation rule set.
