Blog

SQL Server Data Modeling Best Practices

  • (5.0)
  •   |   1178 Ratings

SQL Server Data Modeling Best Practices

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

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 subtypes
  • 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 a 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

 Example for Fully additive facts

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

Example For Semi-additive facts

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
Example For Non-additive Facts

Frequently Asked SSAS Interview Questions 

Margin – rate is non – addictive

Margin – rate = Margin – amt /revenue

Factless fact table:

  • A fact table with no measure in it
  • Nothing to measure
  • Except for 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 a primary key (if it is composite)
  2. To generate unique values

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

Get ahead in your career by learning SQL Server through Mindmajix SQL Server Training.

Difference between star and snowflake schema

Star schema Snowflake Schema
1) 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) Denormalized 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 relationships between fact and dimensions  

Star Schema:

 Example For Star Schema

Snowflake schema Ex:

Example for Snowflake schema

Multiple facts and dimensions connected:
Multiple facts and dimensions connections

Slowly changing dimensions

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

Slowly changing dimension types:

Three types of slowly changing dimension types

Type 1

  • Updates existing record with modifications
  • Does not maintain a 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
 SSAS  SQL Server DBA
 SCCM  BizTalk Server
 Team Foundation Server  BizTalk Server Administrator

Popular Courses in 2018

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