SQL Server Data Modeling Best Practices

  • (5.0)
  •   |   904 Ratings

SQL Server Data Modeling

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

Why a data model?
  1. Helps to visualize the business
  2. A model is a means of communication
  3. Models help elicit and document requirements
  4. Models reduce the cost of change
  5. 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
Enhance your IT skills and proficiency by taking up the SSAS Training.
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

Checkout SSAS Interview 

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
Learn how to use SQL Server, from beginner basics to advanced techniques, with online video tutorials taught by industry experts. Enroll for Free SQL Server Training Demo!

Difference between star and snow flake schema

Star schema Snow flake schema
Here all dimensions directly connected to fact table dimensions doesn’t contain any hierarchies 1) It is just like star schema but dimensions contain sub dimensions so dimensional hierarchies are available
2) De normalized 2) Normalized
3)Less joins, query performance faster 3) More joins, query performance slow
4) More memory occupies 4) Less memory occupies
5) Business people can easy to understand 5) Complex to understand
6) Designed for DWH OLAP applications 6) Designed for OLTP more and OLAP less
7) Fewer tables, consolidated 7) More tables, elaborated
8) One too many relationship between fact and dimensions  

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 
Explore SSAS Sample Resumes! Download & Edit, Get Noticed by Top Employers!Download Now!

List of Related Microsoft Certification Courses:

 SSIS  Power BI
 SSRS  SharePoint
 SCCM  BizTalk Server
 Team Foundation Server  BizTalk Server Administrator


Popular Courses in 2018

Get Updates on Tech posts, Interview & Certification questions and training schedules