Business Model Best Practices

• Use star schemas in business models

  Data structure in the physical layer can 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

  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 a separate logical table called Fact Compound Opportunity & Order.

• Prefix logical table name to indicate the table type

  For example, use D for a dimension table and F for a dimension table.

  
  
  Description of the illustration sm_design_logical_table_labels.png
  

• 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

• 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 sm_design_logical_dummy_measure.png
  

• Ensure almost every fact logical column has an aggregation rule set.