2. Model the data

2. Model the data

1. 2.1 Design a data model

   • Schemas
     • Flat
       • Efficient
       • But confusing
       • Duplicated data
     • Star
       • Fields grouped logically
       • Less duplicated values, so storage is more efficient
     • Snowflake
     • Role-playing Dimension
     • Cardinality
       • 1:1 -- Both
       • 1:* -- Single | Both
       • *:* -- Modeler decide

2. 2.2 Develop a data model

   • Create
     • Calculated tables
     • Calculated columns
     • Quick Measures
     • Hierarchies
   • Implemente
     • Row-level Security
     • Dynamic row-level security
   • Q&A feature
   • Synonyms

3. 2.3 Create measure using DAX

   • CALCULATE
   • Time Intelligence
     • Gregorian calendar
     • YTD
     • DATEADD
   • Context
     • Row Context
     • Filter Context

4. 2.4 Optimize model performance

   • Use only columns that you need
   • Remove PK from Fact Tables if they com from a DW
   • Performance Analyzer
   • Store numbers as
     • Decimal --> More than 4 decimal places
     • Fixed --> Up to 4 decimal places
   • Storage Numbers is more efficient than Text
   • DirectQuery
     • Aggregate tables