SQL Server Data Modeling

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 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

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

 

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

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

 

Snowflake schema Ex:

Multiple facts and dimensions connected:

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 

List of Related Microsoft Certification Courses:

SSIS	Power BI
SSRS	SharePoint
SSAS	SQL Server DBA
SCCM	BizTalk Server
Team Foundation Server	BizTalk Server Administrator