Home  >  Blog  >   MSBI

SQL Server Data Modeling

Rating: 5
  1. Share:
MSBI Articles

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 skillset and give a boost to your career with the SQL Server Certification Training Course.

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

MindMajix Youtube Channel

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

Difference between star and snowflake schema

Star schemaSnowflake Schema
1) Here all dimensions directly connected to fact table dimensions doesn’t contain any hierarchies1) It is just like star schema but dimensions contain sub-dimensions so dimensional hierarchies are available
2) Denormalized2) Normalized
3)Less joins, query performance faster3) More joins, query performance slow
4) More memory occupies4) Less memory occupies
5) Business people can easy to understand5) Complex to understand
6) Designed for DWH OLAP applications6) Designed for OLTP more and OLAP less
7) Fewer tables, consolidated7) 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
 SCCM BizTalk Server
 Team Foundation Server BizTalk Server Administrator


Join our newsletter

Stay updated with our newsletter, packed with Tutorials, Interview Questions, How-to's, Tips & Tricks, Latest Trends & Updates, and more ➤ Straight to your inbox!

Course Schedule
MSBI TrainingMay 25 to Jun 09View Details
MSBI TrainingMay 28 to Jun 12View Details
MSBI TrainingJun 01 to Jun 16View Details
MSBI TrainingJun 04 to Jun 19View Details
Last updated: 03 Apr 2023
About Author


Technical Content Writer

read more
Recommended Courses

1 / 15