SQL Server Data Modeling Best Practices

Recommended by 0 users

SQL Server Data Modeling

A data model is an abstraction of some aspect of the real world (system)

Why a data model?

  • Helps to visualize the business
  • A model is a means of communication
  • Models help elicit and document requirements
  • Models reduce the cost of change
  • Model is the essence of DW architecture based on which DW will be implemented

Levels of modeling


Conceptual modeling:

Describe data requirements from a business point of view without technical details

  • A conceptual model shows data through business eyes
  • All entities which have business meaning
  • Important relationships
  • Few significant attributes in the entities
  • Few identifiers or candidate (PK candidate) keys

Logical modeling:

  • Refine conceptual models
  • Data structure oriented, platform independent
  • Replaces many – to – many relationships with associative entities
  • Defines a full population of entity attributes
  • May use non- physical entities for dimension and sub types
  • Establishes entity identifiers
  • Has no specific for any RDBMS or configuration
  • ER diagram>key based modeling> fully attributed model

Physical modeling:

Detailed specification of what is physically implemented using specific technology

  • A physical data model may include
  • Referential integrity
  • Indexes
  • Views
  • Alternate keys and other constraints
  • Table spaces and physical storage objects

Types of Facts  

Fully additive facts:

  • It Can be summed across any and all dimensions
  • Stored in fact table

Ex: revenue, quantity



Semi additive facts:

  • It Can be summed across most dimensions but not all
  • Anything that measures a “level”
  • Must be careful with ad-hoc reporting
  • Often aggregated across the “forbidden dimension” by averaging


Non additive Facts:

  • Con not be summed across any dimension
  • All ratios are non- additive
  • Break down to fully additive components, store them in fact table


Margin – rate is non – addictive

Margin – rate = Margin – amt /revenue

Fact less fact table:

  • A fact table with no measure in it
  • Nothing to measure
  • Except the convergence of dimensional attributes
  • Sometimes store  a “1” for convenience
  • Eg: attendance, customer, assignments, coverage

 Surrogate keys:

The surrogate keys are simply system generated sequence numbers

Used generally in two situations

  1. To replace the use of primary key (if it is composite)
  2. To generate unique values

We use this surrogate key both at dimensions table and fact table

Difference between star and snow flake schema

s.no Star schema Snow flake schema
  1) Here all dimensions directly connected to fact table dimensions doesn’t contain any hierarchies2) De normalized3)Less joins, query performance faster4) More memory occupies

5) Business people can easy to understand

6) Designed for DWH OLAP applications

7) Fewer tables, consolidated

8) One too many relationship between fact and dimensions

1) It is just like star schema but dimensions contain sub dimensions so dimensional hierarchies are available2) Normalized3) More joins, query performance slow4) Less memory occupies

5) Complex to understand

6) Designed for OLTP more and OLAP less

7) More tables, elaborated

Star Schema:


Snow flake schema Ex:


Multiple facts and dimensions connected:


Slowly changing dimensions

  • Dimension source data may change over time
  • Relative to fact table, dimensions records change slowly
  • Allows dimensions to have multiple ‘profile’ over time to maintain history
  • Each profile is a separate record in dimension table

Slowly changing dimension types:

Three types of slowly changing dimension types

Type 1

  • Updates existing record with modifications
  • Does not maintain history

Type 2

  • Adds new record
  • Does maintain history
  • Maintains old record

Type 3

  • Keep old and new values in the existing row
  • Requires a design change

All the above topics will be covered under MindMajix MSBI Training


0 Responses on SQL Server Data Modeling Best Practices"

Leave a Message

Your email address will not be published. Required fields are marked *

Copy Rights Reserved © Mindmajix.com All rights reserved. Disclaimer.
Course Adviser

Fill your details, course adviser will reach you.