Category: Data Modeling

Business Intelligence, Data Engineering, Data Integration, Data Modeling, Data Strategy, Data Warehousing August 5, 2026

The Fundamentals of Star Schemas

Introduction

If there is one data modeling concept that every data analyst, data engineer, and Power BI developer should understand, it is the star schema.

Although modern analytics platforms provide powerful capabilities for importing, transforming, and analyzing data, the quality of your reports ultimately depends on the quality of your data model. A well-designed star schema makes reports easier to build, improves query performance, reduces complexity, and helps ensure accurate calculations.

Whether you’re creating a Power BI semantic model, designing a Microsoft Fabric Warehouse, building an Azure SQL data warehouse, or developing a traditional enterprise data warehouse, the star schema remains the industry standard for organizing analytical data.

What Is a Star Schema?

A star schema is a data modeling technique that organizes data into two primary types of tables:

Fact tables
Dimension tables

The fact table sits in the center of the model, while the dimension tables surround it, creating a shape that resembles a star.

             Product
                |
Customer --- Sales Fact --- Date
                |
            Salesperson
                |
             Geography

This simple design makes it easy for reporting tools to aggregate data while allowing users to filter information by various business attributes. Star Schema models are sometimes called Dimensional Models.

Fact Tables

A fact table stores measurable business events.

Each row typically represents a single business transaction or event.

Examples include:

Sales transactions
Orders
Shipments
Inventory movements
Website visits
Financial journal entries

Fact tables usually contain:

Numeric values (sales amount, quantity, cost)
Foreign keys pointing to dimensions
Sometimes transaction identifiers

Example:

DateKey	ProductKey	CustomerKey	Quantity	SalesAmount
20260701	125	845	3	$149.97
20260701	450	210	1	$89.99

Notice that the fact table stores very little descriptive information. Instead, it references other tables.

Dimension Tables

Dimension tables describe the facts.

Rather than storing numbers, they store descriptive attributes used for filtering, grouping, and reporting.

Examples include:

Customer
Product
Date
Employee
Store
Vendor
Geography

A Product dimension might contain:

ProductKey	ProductName	Category	Brand	Color
125	Wireless Mouse	Accessories	Contoso	Black

A Customer dimension may include:

Customer Name
City
State
Country
Customer Segment
Industry

These tables provide the business context needed to interpret the facts.

Why Is It Called a Star Schema?

When diagrammed visually, the fact table appears in the middle while dimension tables radiate outward.

           Product

Customer  Sales  Date

         Geography

        Salesperson

Unlike more complicated database designs, each dimension typically connects directly to the fact table.

This simplicity is one of the reasons star schemas are so effective.

Relationships

In a star schema:

One dimension row relates to many fact rows.
Fact tables contain foreign keys.
Dimension tables contain primary keys.

For example:

			
Product
---------
ProductKey (Primary Key)
          |
          |
Sales Fact
----------
ProductKey (Foreign Key)

		

This creates a one-to-many relationship, which is ideal for analytical workloads.

What Is Granularity?

Granularity refers to the level of detail stored in the fact table.

Examples:

Daily Sales: One row per day.

Transaction Sales: One row per individual purchase.

Order Line Sales: One row for every item sold on an order.

The more detailed the granularity, the more flexible the reporting becomes.

Choosing the correct grain is one of the most important design decisions in a star schema.

Common Dimension Tables

Nearly every warehouse includes some common dimensions.

Date Dimension

Perhaps the most important dimension.

Contains attributes like:

Date
Year
Quarter
Month
Month Name
Week
Fiscal Year
Holiday Indicator

Instead of calculating these repeatedly, reports simply reference the Date dimension.

Product Dimension

Contains:

Product Name
Category
Subcategory
Brand
Color
Size
SKU

Customer Dimension

Contains:

Customer Name
Region
Industry
Segment
Customer Type

Geography Dimension

Contains:

Country
State
Province
City
Postal Code
Sales Territory

Measures vs Attributes

Understanding the difference between measures (stored in Fact tables) and attributes (stored in Dimension tables) is essential.

Measures (Fact Table)

Sales Amount
Cost
Quantity
Profit
Hours Worked

Attributes (Dimension Tables)

Product Name
Customer Name
State
Department
Month
Category

A simple way to think about it:

Numbers that are aggregated belong in fact tables. Descriptive information belongs in dimension tables.

Benefits of Star Schemas

1. Better Performance

Reporting engines such as Power BI are optimized for star schemas.

Fewer joins result in:

Faster queries
Better compression
Reduced memory usage

2. Simpler Reports

Users can easily understand:

Sales by Month
Sales by Customer
Sales by Product
Sales by Region

Instead of navigating dozens of interconnected tables, report authors work with a clean, intuitive model.

3. Easier Maintenance

Changes to one dimension rarely affect other dimensions.

Adding a new Product Category only requires updating the Product dimension.

The fact table usually remains unchanged.

4. Improved Data Quality

Because descriptive information is stored once, duplication is minimized.

For example, “Florida” exists once in a Geography dimension instead of appearing in millions of sales rows.

5. Scalability

Star schemas scale exceptionally well.

Many enterprise warehouses contain:

Billions of fact rows
Millions of customers
Hundreds of thousands of products

The design continues to perform efficiently.

Star Schema vs Flat Tables

Some beginners attempt to place every column into one enormous table.

While this may seem easier initially, it creates several problems:

Duplicate data
Larger storage requirements
Slower refreshes
Poor compression
Difficult maintenance

A star schema separates repeated descriptive information from transactional data, making the model both smaller and faster.

Star Schema vs Snowflake Schema

A related design is the snowflake schema.

Instead of storing all descriptive information in a single dimension, dimensions are normalized into multiple tables.

Example:

			
Product
   |
Category
   |
Department

		

Advantages:

Less duplicated data
Smaller dimension tables

Disadvantages:

More joins
More complicated reports
Slower query performance
Harder for business users to understand

For Power BI and most analytics solutions, a star schema is generally preferred.

Best Practices

When designing a star schema:

Determine the grain of each fact table before loading data.
Use surrogate keys for dimension relationships when appropriate.
Keep dimensions descriptive and facts numeric.
Avoid storing repeated descriptive data in fact tables.
Use conformed dimensions (such as Date or Customer) across multiple fact tables.
Create one-to-many relationships from dimensions to facts.
Keep the model as simple as possible.
Hide technical key columns from report consumers.
Use meaningful table and column names.
Document the purpose of each fact and dimension.

Common Mistakes to Avoid

New developers often make these mistakes:

Building one giant table containing everything.
Creating many-to-many relationships unnecessarily.
Using bidirectional filtering without a clear need.
Mixing transaction-level and summary-level data in the same fact table.
Including calculated totals in fact tables instead of calculating them in reports.
Storing descriptive text in fact tables.
Ignoring the importance of a proper Date dimension.

Avoiding these pitfalls leads to cleaner, more maintainable models.

Star Schemas in Power BI

Power BI is designed to work exceptionally well with star schemas.

Benefits include:

Faster report performance
Better DAX calculation behavior
Simpler filter propagation
Easier report development
Improved semantic model organization

Microsoft recommends using star schemas whenever possible when designing Power BI semantic models.

Real-World Example

Imagine a retail company tracking sales.

Sales Fact

Sales Amount
Quantity
Discount
Cost

Connected to:

Date
Customer (contains Customer Segment)
Product
Store (contains Region)
Employee
Promotion

Business users can easily answer questions such as:

Which products sold the most this month?
Which region generated the highest profit?
What is the average order value by customer segment?
Which promotions increased sales?
How did sales compare year over year?

This flexibility is one of the major reasons star schemas have become the standard for business intelligence.

Frequently Asked Questions

Can a star schema have multiple fact tables?

Yes. Many enterprise data warehouses include multiple fact tables, such as Sales, Inventory, Budget, and Returns, all sharing common dimensions like Date, Product, and Customer.

Why shouldn’t descriptive columns be stored in fact tables?

Doing so increases duplication, wastes storage, and makes updates more difficult. Dimension tables provide a single source of truth for descriptive information.

Are star schemas only used in Power BI?

No. Star schemas are widely used in Microsoft Fabric, Azure Synapse Analytics, SQL Server, Oracle, Snowflake, Amazon Redshift, Google BigQuery, and many other analytics platforms.

Is a star schema required?

Not always, but it is considered the best practice for most analytical reporting and business intelligence solutions because it balances simplicity, performance, and scalability.

Conclusion

The star schema is one of the most important concepts in modern analytics and data warehousing. By separating measurable business events into fact tables and descriptive business information into dimension tables, it creates a model that is easy to understand, efficient to query, and scalable for organizations of any size.

Whether you’re building dashboards in Power BI, designing a Microsoft Fabric Warehouse, or developing an enterprise data warehouse, mastering star schema fundamentals will help you create faster reports, more reliable analytics, and data models that are easier to maintain over time. A solid star schema is not just a design choice—it is the foundation of effective business intelligence.

Thanks for reading!

Business Intelligence, Data Development, Data Engineering, Data Modeling, Data Security, Data Transformation, Data Visualization, Power BI August 2, 2026August 2, 2026

Understanding the Power BI Semantic Model

Introduction

One of the most important concepts in Microsoft Power BI is the Semantic Model. While reports and dashboards are what users see, the semantic model is the intelligence that sits behind them. It organizes data, defines business logic, and ensures that reports produce consistent, accurate results.

A well-designed semantic model makes report development faster, simplifies maintenance, improves performance, and creates a single version of the truth for an organization.

What Is a Power BI Semantic Model?

A Power BI Semantic Model is a structured collection of data, relationships, calculations, and business rules that provides a business-friendly view of your data.

Think of it as the translation layer between your organization’s raw data and the reports your users consume.

Instead of report developers needing to understand dozens of database tables and SQL queries, they simply connect to a semantic model that already contains:

Imported or connected data
Relationships between tables
Measures
Calculated columns
Hierarchies
Data formatting
Security rules
Business definitions

The semantic model allows users to analyze data without needing to understand where the data originally came from.

Why Is the Semantic Model Important?

The semantic model serves as the foundation for nearly every Power BI report.

Some of its biggest benefits include:

Creates a single source of truth
Eliminates duplicated business logic
Improves report consistency
Simplifies report development
Improves report performance
Makes security easier to manage
Enables report reuse across teams

Without a semantic model, every report developer would need to create their own calculations for example, resulting in inconsistent numbers across reports.

What Makes Up a Semantic Model?

A semantic model typically contains several key components.

Tables

The business data that users analyze.

Examples include:

Sales
Customers
Products
Employees
Dates

Relationships

Relationships connect tables together so Power BI understands how information relates.

For example:

Sales → Customer

Sales → Product

Sales → Date

Proper relationships eliminate the need for complicated report calculations.

Measures

Measures perform calculations at query time.

Examples:

Total Sales
Average Order Value
Profit Margin
Year-to-Date Sales

Measures are generally preferred over calculated columns for aggregations because they are more flexible and consume less storage.

Calculated Columns

Calculated columns create new values that become part of the data model.

Examples include:

Full Name
Profit Category
Fiscal Quarter

Hierarchies

Hierarchies make navigation easier.

Example:

Year → Quarter → Month → Day

Data Formatting

Semantic models define:

Currency formats
Percentages
Decimal places
Date formats

This ensures reports display information consistently.

Row-Level Security (RLS)

Security rules determine which data each user is allowed to see.

For example:

Regional managers only see their own region.
Sales representatives only see their own customers.

How Is a Semantic Model Created?

The typical process looks like this:

Connect to one or more data sources.
Clean and transform data using Power Query.
Load the data into Power BI.
Create relationships.
Create measures using DAX.
Configure formatting.
Build hierarchies.
Configure security.
Publish the semantic model to the Power BI Service.

Once published, reports can connect directly to the semantic model rather than importing data again.

How Is a Semantic Model Maintained?

Like any business asset, semantic models require ongoing maintenance.

Common maintenance activities include:

Refreshing data
Adding new tables
Creating or updating relationships
Updating business calculations
Optimizing model performance
Reviewing and updating security
Creating new columns or removing unused columns
Documenting business definitions
Monitoring refresh failures
and more

A well-maintained semantic model becomes increasingly valuable over time.

Shared Semantic Models

One of the greatest strengths of Power BI is the ability to share a semantic model across many reports.

Instead of creating ten separate datasets containing the same sales data:

Build one high-quality semantic model.
Allow many reports to connect to it.

Benefits include:

Consistent calculations
Less duplicated work
Smaller storage footprint
Easier maintenance
Better governance
Faster report development

This approach is sometimes called the “build once, report many” strategy.

Best Practices

When designing semantic models, consider the following recommendations.

Use a Star Schema

Organize data into:

Fact tables
Dimension tables

This improves both performance and usability.

Hide Technical Columns

Hide columns that report authors should not use.

Examples:

Primary keys
Foreign keys
Internal IDs

This creates a cleaner report authoring experience.

Create Measures Instead of Repeating Calculations

Store business calculations centrally.

Instead of recreating “Total Sales” in every report, define it once inside the semantic model.

Use Meaningful Names

Instead of:

SalesAmt

Use:

Total Sales

Business-friendly names improve usability.

Remove Unnecessary Data

Only import:

Needed tables
Needed columns
Needed rows

Smaller models perform better.

Document Business Logic

Describe:

Measures
KPIs
Calculations
Business rules

Future developers will appreciate the documentation.

Optimize Relationships

Avoid unnecessary many-to-many relationships when possible.

Keep relationships simple and easy to understand.

Securing a Semantic Model

Security should be considered from the beginning rather than added later.

Important security practices include:

Use Row-Level Security (RLS) when different users should see different data.
Apply workspace permissions using the principle of least privilege.
Secure the underlying data source.
Protect sensitive information using sensitivity labels when appropriate.
Limit who can modify the semantic model.
Review permissions regularly.

Good security protects both the data and the business.

Common Mistakes to Avoid

Many new Power BI developers make similar mistakes.

Building a Separate Model for Every Report

Instead, reuse a shared semantic model whenever possible.

Importing Every Column

Extra columns increase model size and reduce performance.

Creating Duplicate Measures

One calculation should exist only once.

Poor Naming

Names like:

Measure1

Calc2

Table3

make models difficult to maintain.

Ignoring Relationships

Incorrect relationships often produce incorrect totals.

Always validate relationship directions and cardinality.

Excessive Calculated Columns

Use measures whenever practical for aggregations.

Skipping Documentation

Undocumented models become difficult to maintain as teams grow.

How to Make Your Semantic Model More Valuable

Organizations receive the greatest value when they treat the semantic model as a shared enterprise asset.

Some ways to maximize its value include:

Develop reusable measures.
Standardize business definitions.
Encourage report developers to connect to existing semantic models.
Validate and certify trusted semantic models for organization-wide use.
Monitor usage to identify opportunities for improvement.
Regularly review performance and security.
Keep the model simple, clean, and well documented.

As adoption grows, the semantic model becomes the central foundation for business reporting.

Frequently Asked Questions

Can multiple reports use the same semantic model?

Yes. In fact, this is one of the primary design goals of Power BI. A single semantic model can support dozens—or even hundreds—of reports while ensuring consistent calculations and business definitions.

What is the difference between a semantic model and a report?

The semantic model contains the data, relationships, measures, and business logic. A report is the visual presentation that connects to and displays information from the semantic model.

Can a semantic model connect to multiple data sources?

Yes. A semantic model can combine information from databases, spreadsheets, cloud services, data warehouses, data lakes, and many other supported data sources.

Who should create semantic models?

Ideally, semantic models are created and maintained by BI developers, data engineers, analytics engineers, or Power BI developers who understand both the organization’s data and its business rules.

When should a new semantic model be created?

A new semantic model should generally be created only when the data serves a different business domain or has substantially different security, refresh, or performance requirements. Otherwise, extending an existing shared semantic model is often the better choice.

Can security be applied inside the semantic model?

Yes. Row-Level Security (RLS) can restrict which rows users see, and Object-Level Security (OLS) can hide specific tables or columns from certain users when supported. These features help enforce data access policies consistently across all reports that use the model.

Summary

The Power BI semantic model is the foundation of effective business intelligence. It transforms raw data into a reusable, business-friendly resource by defining relationships, calculations, security, and business logic in one central location.

Organizations that invest in well-designed, shared semantic models benefit from more consistent reporting, faster report development, improved performance, stronger governance, and easier maintenance. By following best practices—such as using a star schema, creating reusable measures, documenting business logic, securing data appropriately, and encouraging report reuse—you can build semantic models that deliver lasting value across the organization.

Thanks for reading!

Analytics, Business Intelligence, Data Modeling, DP-900, Microsoft Certification, Power BI, Power Query May 10, 2026

Describe features of data models in Power BI (DP-900 Exam Prep)

This post is a part of the DP-900: Microsoft Azure Data Fundamentals Exam Prep Hub. 
This topic falls under these sections:
Describe an analytics workload (25–30%)
   --> Describe data visualization in Microsoft Power BI
      --> Describe features of data models in Power BI

Note that there are 10 practice questions (with answers and explanations) for each section to help you solidify your knowledge of the material. Also, there are 2 practice tests with 60 questions each available on the hub below the exam topics section.

A data model is the foundation of any effective report in Microsoft Power BI. It defines how data is structured, related, and calculated, enabling efficient analysis and meaningful visualizations.

For the DP-900 exam, you should understand how data models work, their key components, and best practices.

What Is a Data Model in Power BI?

A data model is a logical representation of data that includes:

Tables
Relationships
Calculations

It allows Power BI to:

Combine data from multiple sources
Enable filtering and aggregation
Support interactive reporting

Key Features of Power BI Data Models

1. Tables

Data models consist of one or more tables, which can come from:

Databases
Files (Excel, CSV)
Cloud sources

✔ Tables contain rows (records) and columns (fields)

2. Relationships

Relationships define how tables are connected.

Types of Relationships

One-to-many (1:*) → Most common
Many-to-one (*:1)
Many-to-many (:)

Key Concepts

Primary key → Unique identifier in one table
Foreign key → Reference in another table

✔ Relationships enable filtering across tables

3. Schema Design (Star Schema)

Power BI models commonly follow a star schema:

Fact tables → Contain measurable data (e.g., sales)
Dimension tables → Contain descriptive data (e.g., customer, product)

✔ This structure improves performance and usability

4. Measures and Calculated Columns

Power BI uses DAX (Data Analysis Expressions) for calculations.

Measures

Calculated at query time
Used in aggregations (e.g., SUM, AVERAGE)

Calculated Columns

Computed during data load
Stored in the model

✔ Measures are preferred for performance

5. Data Types

Each column has a defined data type:

Text
Number
Date/Time
Boolean

✔ Correct data types ensure accurate calculations and visuals

6. Hierarchies

Hierarchies allow users to drill down into data.

Example

Year → Quarter → Month → Day

✔ Used for interactive reporting and exploration

7. Filtering and Cross-Filtering

Relationships enable:

Filter propagation between tables
Cross-filtering in visuals

✔ Example:
Selecting a product filters related sales data

8. Data Granularity

Granularity refers to the level of detail in data.

Fine-grained → detailed (e.g., individual transactions)
Coarse-grained → summarized (e.g., monthly totals)

✔ Consistent granularity is important for accurate analysis

9. Model Optimization

Well-designed models:

Use fewer tables when possible
Avoid unnecessary columns
Use measures instead of calculated columns
Follow star schema design

✔ Improves performance and usability

10. Relationships Direction (Filter Direction)

Relationships can filter:

Single direction (default, recommended)
Both directions (used cautiously)

✔ Incorrect settings can lead to ambiguous results

Typical Data Modeling Workflow in Power BI

Load data into Power BI
Clean and transform data (Power Query)
Define relationships
Create measures and calculations
Build reports and visuals

Why This Matters for DP-900

On the exam, you may be asked to:

Identify components of a data model
Understand relationships and keys
Differentiate between measures and calculated columns
Recognize star schema design
Understand filtering behavior

Summary — Exam-Relevant Takeaways

✔ A data model includes:

Tables
Relationships
Calculations

✔ Key features:

Relationships (1:*, :)
Star schema (fact + dimension tables)
Measures vs calculated columns
Hierarchies and filtering

✔ Best practices:

Use star schema
Prefer measures over calculated columns
Maintain consistent granularity

✔ Exam tips:
👉 Fact table = metrics (numbers)
👉 Dimension table = descriptive attributes
👉 Measure = dynamic calculation
👉 Calculated column = stored value

Go to the Practice Exam Questions for this topic.

Go to the DP-900 Exam Prep Hub main page.

Data Development, Data Education & Training, Data Modeling, Databases, DP-900, SQL, Uncategorized May 10, 2026

Identify common Structured Query Language (SQL) statements (DP-900 Exam Prep)

This post is a part of the DP-900: Microsoft Azure Data Fundamentals Exam Prep Hub. 
This topic falls under these sections:
Identify considerations for relational data on Azure (20–25%)
   --> Describe relational concepts
      --> Identify common Structured Query Language (SQL) statements

Note that there are 10 practice questions (with answers and explanations) for each section to help you solidify your knowledge of the material. Also, there are 2 practice tests with 60 questions each available on the hub below the exam topics section.

Understanding basic SQL statements is essential for working with relational data and is a key requirement for the DP-900 exam. You are not expected to be an advanced SQL developer, but you should recognize common SQL commands, their purpose, and when they are used.

What Is SQL?

Structured Query Language (SQL) is the standard language used to:

Query data
Insert new data
Update existing data
Delete data
Define database structures

SQL is used across relational database systems, including Azure services like:

Azure SQL Database
Azure Database for PostgreSQL
Azure Database for MySQL

Categories of SQL Statements

SQL statements are typically grouped into categories:

Category	Purpose
DDL (Data Definition Language)	Define and modify database structures
DML (Data Manipulation Language)	Work with data in tables
DQL (Data Query Language)	Retrieve data
DCL (Data Control Language)	Manage permissions

For DP-900, focus primarily on DDL, DML, and DQL.

1. Data Query Language (DQL)

SELECT

Used to retrieve data from a table.

			
SELECT Name, City
FROM Customers;

You can filter results:

			
SELECT Name
FROM Customers
WHERE City = 'Seattle';

💡 Key Points:

Most commonly used SQL statement
Can include filtering, sorting, and grouping

2. Data Manipulation Language (DML)

INSERT

Adds new rows to a table.

			
INSERT INTO Customers (Name, City)
VALUES ('John', 'Seattle');

UPDATE

Modifies existing data.

			
UPDATE Customers
SET City = 'Austin'
WHERE Name = 'John';

DELETE

Removes rows from a table.

			
DELETE FROM Customers
WHERE Name = 'John';

💡 Important:
Always use a WHERE clause with UPDATE and DELETE to avoid affecting all rows.

3. Data Definition Language (DDL)

CREATE

Creates new database objects such as tables.

			
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(100),
    City VARCHAR(50)
);

		

ALTER

Modifies an existing table.

			
ALTER TABLE Customers
ADD Email VARCHAR(100);

DROP

Deletes a table or database object.

DROP TABLE Customers;

💡 Warning:
DROP permanently removes the object and its data.

4. Additional Common SQL Clauses

WHERE

Filters rows:

			
SELECT * FROM Orders
WHERE Amount > 100;

ORDER BY

Sorts results:

			
SELECT * FROM Orders
ORDER BY Amount DESC;

GROUP BY

Aggregates data:

			
SELECT City, COUNT(*)
FROM Customers
GROUP BY City;

JOIN

Combines data from multiple tables:

			
SELECT Orders.OrderID, Customers.Name
FROM Orders
JOIN Customers
ON Orders.CustomerID = Customers.CustomerID;

💡 DP-900 Tip:
You don’t need deep JOIN knowledge — just understand that JOINs combine related tables.

SQL in Azure

SQL is used across many Azure services:

Azure SQL Database

Fully managed relational database
Uses T-SQL (Microsoft’s SQL variant)

Azure Synapse Analytics

Used for analytical queries on large datasets

Azure Database for PostgreSQL

Uses PostgreSQL SQL dialect

Why This Matters for DP-900

On the exam, you may be asked to:

Identify what a SQL statement does
Match commands to their purpose (SELECT, INSERT, etc.)
Recognize DDL vs DML
Understand basic query concepts like filtering and sorting

Summary — Exam-Relevant Takeaways

✔ SELECT → Retrieve data
✔ INSERT → Add new data
✔ UPDATE → Modify existing data
✔ DELETE → Remove data

✔ CREATE / ALTER / DROP → Define and modify structures
✔ WHERE → Filter results
✔ ORDER BY → Sort data
✔ GROUP BY → Aggregate data
✔ JOIN → Combine tables

✔ SQL is the standard language for relational databases

Go to the Practice Exam Questions for this topic.

Go to the Additional Practice Questions for this topic.

Go to the DP-900 Exam Prep Hub main page.

Data Modeling, Databases, DP-900, Microsoft Certification May 10, 2026

Describe normalization and why it is used (DP-900 Exam Prep)

This post is a part of the DP-900: Microsoft Azure Data Fundamentals Exam Prep Hub. 
This topic falls under these sections:
Identify considerations for relational data on Azure (20–25%)
   --> Describe relational concepts
      --> Describe normalization and why it is used

Note that there are 10 practice questions (with answers and explanations) for each section to help you solidify your knowledge of the material. Also, there are 2 practice tests with 60 questions each available on the hub below the exam topics section.

Normalization is a foundational concept in relational database design. For the DP-900 exam, you are expected to understand what normalization is, why it is important, and how it improves data quality and efficiency.

What Is Normalization?

Normalization is the process of organizing data in a relational database to:

Reduce data redundancy (duplicate data)
Improve data integrity
Ensure logical data relationships

This is done by splitting data into multiple related tables and defining relationships between them using keys.

Why Normalization Is Used

Normalization is used to solve common data problems in poorly designed tables.

1. Reduce Data Redundancy

Without normalization, the same data may be repeated across multiple rows.

Example (Unnormalized Table):

OrderID	CustomerName	CustomerCity	Product
1	John	Seattle	Laptop
2	John	Seattle	Mouse

Here, customer information is duplicated.

After Normalization:

Customers Table

CustomerID	Name	City
1	John	Seattle

Orders Table

OrderID	CustomerID	Product
1	1	Laptop
2	1	Mouse

Now, customer data is stored once and referenced using a key.

2. Improve Data Integrity

Normalization ensures that data remains accurate and consistent.

Without normalization:

Updating a customer’s city requires changing multiple rows
Missing one update leads to inconsistent data

With normalization:

Data is updated in one place only
Consistency is maintained automatically

3. Prevent Data Anomalies

Normalization helps prevent common issues:

Insert anomaly: Cannot add data without unrelated data
Update anomaly: Inconsistent updates across rows
Delete anomaly: Deleting one record removes important data

Normalized designs eliminate these problems.

4. Improve Data Organization

Normalized databases:

Clearly separate different entities (customers, orders, products)
Use relationships to connect data logically
Make databases easier to maintain and scale

Understanding Normal Forms (Simplified for DP-900)

Normalization is often described in stages called normal forms. For DP-900, you only need a basic understanding:

First Normal Form (1NF)

No repeating groups or multi-valued fields
Each column contains atomic (single) values

Second Normal Form (2NF)

Meets 1NF
All non-key columns depend on the entire primary key

Third Normal Form (3NF)

Meets 2NF
No dependency between non-key columns

💡 DP-900 Tip:
You do NOT need to memorize formal definitions — just understand that normalization reduces redundancy and improves integrity.

Trade-Offs of Normalization

While normalization has many benefits, there are trade-offs:

Advantages

✔ Reduces duplicate data
✔ Improves consistency
✔ Simplifies updates
✔ Enhances data integrity

Disadvantages

✖ Requires more tables
✖ Queries may require joins
✖ Can slightly impact performance for complex queries

Normalization vs Denormalization

Understanding this comparison is important for the exam:

Feature	Normalization	Denormalization
Data Redundancy	Reduced	Increased
Data Integrity	High	Lower
Query Complexity	Higher (joins required)	Lower
Performance	Slower for reads	Faster for analytics
Use Case	Transactional systems (OLTP)	Analytical systems (OLAP)

Where Normalization Is Used in Azure

Normalization is commonly applied in relational database services such as:

Azure SQL Database
Azure Database for PostgreSQL
Azure Database for MySQL

These services are typically used for transactional workloads, where data integrity is critical.

Why This Matters for DP-900

On the exam, you may be asked to:

Identify why normalization is used
Recognize normalized vs unnormalized structures
Understand how normalization affects data integrity
Distinguish normalization from denormalization

Summary — Exam-Relevant Takeaways

✔ Normalization organizes data into multiple related tables
✔ It reduces data redundancy
✔ It improves data integrity and consistency
✔ It prevents insert, update, and delete anomalies
✔ It is commonly used in transactional (OLTP) systems
✔ It may require joins when querying data

Go to the Practice Exam Questions for this topic.

Go to the DP-900 Exam Prep Hub main page.

Data Modeling, DP-900, Microsoft Certification May 10, 2026

Practice Questions: Describe normalization and why it is used (DP-900 Exam Prep)

Practice Questions

Question 1

What is the primary purpose of normalization in a relational database?

A. Increase data duplication
B. Improve query speed for analytics
C. Reduce data redundancy and improve integrity
D. Store unstructured data

✅ Answer: C

Explanation:
Normalization reduces duplicate data and improves consistency and integrity.

Question 2

Which issue is MOST likely to occur in a non-normalized table?

A. Faster query performance
B. Data redundancy
C. Reduced storage requirements
D. Simpler queries

✅ Answer: B

Explanation:
Without normalization, the same data is often stored multiple times.

Question 3

A database stores customer details repeatedly in every order record. What problem does normalization solve in this scenario?

A. Improves indexing
B. Reduces redundancy
C. Enhances encryption
D. Increases data size

✅ Answer: B

Explanation:
Normalization separates customer data into its own table to avoid duplication.

Question 4

Which anomaly occurs when updating the same data in multiple rows leads to inconsistent values?

A. Insert anomaly
B. Delete anomaly
C. Update anomaly
D. Query anomaly

✅ Answer: C

Explanation:
Update anomalies happen when duplicate data is not consistently updated.

Question 5

Which approach is commonly used in normalized database design?

A. Storing all data in a single table
B. Splitting data into related tables
C. Removing all relationships between data
D. Using only unstructured formats

✅ Answer: B

Explanation:
Normalization divides data into multiple related tables.

Question 6

Which statement best describes First Normal Form (1NF)?

A. Data must be encrypted
B. Tables must include foreign keys
C. Each column contains atomic (single) values
D. Tables must be denormalized

✅ Answer: C

Explanation:
1NF requires that each field contains indivisible values and no repeating groups.

Question 7

What is a trade-off of normalization?

A. Increased data redundancy
B. Simpler queries with no joins
C. More complex queries due to joins
D. Reduced data integrity

✅ Answer: C

Explanation:
Normalized databases often require joins, making queries more complex.

Question 8

Which type of workload benefits MOST from normalized data?

A. Analytical workloads
B. Transactional workloads
C. Streaming workloads
D. Machine learning workloads

✅ Answer: B

Explanation:
Transactional systems prioritize data integrity, which normalization supports.

Question 9

Which action is an example of normalization?

A. Combining multiple tables into one
B. Storing duplicate data for faster reads
C. Separating customer and order data into different tables
D. Removing relationships between tables

✅ Answer: C

Explanation:
Normalization separates data into logical entities and links them via keys.

Question 10

Why might a system choose denormalization instead of normalization?

A. To improve data integrity
B. To reduce redundancy
C. To improve read performance for analytics
D. To enforce strict schemas

✅ Answer: C

Explanation:
Denormalization is often used in analytical systems to improve query performance.

✅ Quick Exam Takeaways

For DP-900, remember:

✔ Normalization = reduce redundancy + improve integrity
✔ Involves splitting data into related tables
✔ Prevents:

Insert anomalies
Update anomalies
Delete anomalies

✔ Used primarily in transactional (OLTP) systems
✔ Trade-off = more joins / more complex queries
✔ Opposite concept = denormalization (used in analytics)

Go to the DP-900 Exam Prep Hub main page.

Data Modeling, Databases, DP-900, Microsoft Certification, SQL May 10, 2026

Identify features of relational data (DP-900 Exam Prep)

This post is a part of the DP-900: Microsoft Azure Data Fundamentals Exam Prep Hub. 
This topic falls under these sections:
Identify considerations for relational data on Azure (20–25%)
   --> Describe relational concepts
      --> Identify features of relational data

Note that there are 10 practice questions (with answers and explanations) for each section to help you solidify your knowledge of the material. Also, there are 2 practice tests with 60 questions each available on the hub below the exam topics section.

Relational data is one of the most fundamental concepts in data management and a core focus area for the DP-900 exam. Understanding how relational data is structured, stored, and accessed will help you confidently answer questions related to databases, querying, and Azure data services.

What Is Relational Data?

Relational data is data that is organized into tables (relations) consisting of:

Rows (records)
Columns (attributes or fields)

Each table represents a specific entity, such as customers, orders, or products. Relationships between tables are defined using keys.

Core Features of Relational Data

1. Tabular Structure (Rows and Columns)

Relational data is stored in a structured, tabular format:

Each row represents a single record
Each column represents a specific attribute

Example:

CustomerID	Name	City
1	John	Seattle
2	Maria	Austin

This structure makes relational data easy to query and understand.

2. Predefined Schema

Relational databases enforce a fixed schema, which defines:

Table structure
Column names
Data types (e.g., INT, VARCHAR, DATE)

This ensures:

Data consistency
Data validation
Predictable structure

3. Use of Keys

Keys are essential for uniquely identifying records and linking tables.

Primary Key

Uniquely identifies each row in a table
Cannot contain duplicate or null values

Example: CustomerID

Foreign Key

Links one table to another
Establishes relationships between tables

Example: Order.CustomerID → Customer.CustomerID

4. Relationships Between Tables

Relational data supports relationships such as:

One-to-One
One-to-Many
Many-to-Many

Example:

One customer can have many orders (one-to-many)

These relationships allow complex data models to be built efficiently.

5. Structured Query Language (SQL)

Relational data is accessed and manipulated using Structured Query Language (SQL).

SQL is used to:

Query data (SELECT)
Insert data (INSERT)
Update data (UPDATE)
Delete data (DELETE)

Example:

SELECT Name FROM Customers WHERE City = 'Seattle';

6. Data Integrity and Constraints

Relational databases enforce data integrity through constraints such as:

PRIMARY KEY
FOREIGN KEY
NOT NULL
UNIQUE
CHECK

These rules ensure that:

Data is accurate
Relationships remain valid
Invalid data is prevented

7. Normalization

Relational data is often normalized to reduce redundancy and improve consistency.

Normalization involves:

Splitting data into multiple related tables
Eliminating duplicate data
Ensuring dependencies are logical

Example:

Instead of storing customer details in every order row, store them in a separate Customers table.

8. ACID Transactions

Relational databases support ACID properties, ensuring reliable transactions:

Atomicity → All or nothing
Consistency → Valid state maintained
Isolation → Transactions don’t interfere
Durability → Changes persist

This is especially important for transactional workloads.

Relational Data in Azure

Azure provides several services for working with relational data:

Azure SQL Database

Fully managed relational database
Supports SQL queries
High availability and scalability
Ideal for OLTP applications

Azure Database for PostgreSQL

Managed open-source relational database
Supports PostgreSQL features and extensions

Azure Database for MySQL

Managed MySQL database service
Suitable for web and application workloads

These services support structured data, relationships, and SQL-based querying.

Why This Matters for DP-900

On the exam, you may be asked to:

Identify characteristics of relational data
Recognize table-based structures
Understand keys and relationships
Distinguish relational data from non-relational data
Match relational workloads to Azure services

Summary — Exam-Relevant Takeaways

✔ Relational data is stored in tables (rows and columns)
✔ It uses a fixed schema with defined data types
✔ Primary and foreign keys define relationships
✔ Data is accessed using SQL
✔ Supports data integrity constraints
✔ Often normalized to reduce redundancy
✔ Ensures reliability with ACID transactions

✔ Common Azure services:

Azure SQL Database
Azure Database for PostgreSQL
Azure Database for MySQL

Go to the Practice Exam Questions for this topic.

Go to the DP-900 Exam Prep Hub main page.

Data Modeling, Databases, DP-900, Microsoft Certification May 10, 2026

Practice Questions: Identify features of relational data (DP-900 Exam Prep)

Practice Questions

Question 1

Which structure is used to store relational data?

A. Key-value pairs
B. Graph nodes and edges
C. Tables with rows and columns
D. JSON documents

✅ Answer: C

Explanation:
Relational data is organized in tables consisting of rows and columns.

Question 2

What is the purpose of a primary key in a relational table?

A. To link tables together
B. To uniquely identify each row
C. To store duplicate values
D. To define column data types

✅ Answer: B

Explanation:
A primary key uniquely identifies each record and cannot contain duplicates or null values.

Question 3

Which element is used to create relationships between tables?

A. Index
B. Column constraint
C. Foreign key
D. Schema

✅ Answer: C

Explanation:
A foreign key links one table to another by referencing a primary key.

Question 4

Which statement best describes a schema in a relational database?

A. A collection of unstructured files
B. A visual report of data
C. The structure defining tables, columns, and data types
D. A backup of the database

✅ Answer: C

Explanation:
A schema defines how data is structured, including tables, columns, and data types.

Question 5

Which language is used to query relational databases?

A. Python
B. JSON
C. SQL
D. HTML

✅ Answer: C

Explanation:
Structured Query Language (SQL) is used to query and manage relational data.

Question 6

Which constraint ensures that a column cannot contain null values?

A. UNIQUE
B. CHECK
C. NOT NULL
D. FOREIGN KEY

✅ Answer: C

Explanation:
The NOT NULL constraint ensures that a value must be provided for that column.

Question 7

Which concept reduces data redundancy by organizing data into multiple related tables?

A. Indexing
B. Normalization
C. Partitioning
D. Replication

✅ Answer: B

Explanation:
Normalization reduces redundancy and improves data integrity.

Question 8

Which type of relationship allows one record in a table to relate to many records in another table?

A. One-to-one
B. One-to-many
C. Many-to-one
D. Many-to-many

✅ Answer: B

Explanation:
One-to-many relationships are common in relational databases (e.g., one customer → many orders).

Question 9

Which property ensures that all parts of a transaction succeed or fail together?

A. Consistency
B. Isolation
C. Atomicity
D. Durability

✅ Answer: C

Explanation:
Atomicity ensures transactions are completed fully or not at all.

Question 10

Which Azure service is specifically designed for relational data?

A. Azure Blob Storage
B. Azure Cosmos DB
C. Azure SQL Database
D. Azure Data Lake Storage

✅ Answer: C

Explanation:
Azure SQL Database is a fully managed relational database service.

✅ Quick Exam Takeaways

For DP-900, remember:

✔ Relational data = tables (rows + columns)
✔ Schema defines structure
✔ Primary keys uniquely identify rows
✔ Foreign keys create relationships
✔ Use SQL for querying
✔ Constraints enforce data integrity
✔ Normalization reduces redundancy
✔ Supports ACID transactions

Go to the DP-900 Exam Prep Hub main page.

Business Intelligence (BI) Development, Data Modeling, Power BI March 22, 2026

How to delete multiple fields (including measures) at the same time in Power BI

You find that you need to delete many fields (which can include measures) from a Power BI model / project, such as after removing a part of the solution that is no longer needed or will not be a part of the current release.

From the “Report View”, you can delete only one field at a time. However, you can delete multiple at a time from the “Model View”.

In your Power BI report, click the “Model View” in the left navigation pane.

Then, in the Data pane on the right, hold down the Ctrl key and click on each of the field that you want to delete.

All the fields you clicked on will be “selected” and you should see that they are.

Then, click the Delete key -or- right-click the fields and select “Delete from model”.

A “Delete items” confirmation dialog will pop up. After confirming that you have selected the fields you really want to delete, click “Yes”, or click “Cancel” to cancel your action.

Good luck!

Analytics, Business Intelligence, Data Analysis, Data Modeling, Data Visualization, Power BI January 25, 2026January 25, 2026

Power BI Drilldown vs. Drill-through: Understanding the Differences, Use Cases, and Setup

Power BI provides multiple ways to explore data interactively. Two of the most commonly confused features are drilldown and drill-through. While both allow users to move from high-level insights to more detailed data, they serve different purposes and behave differently.

This article explains what drilldown and drill-through are, when to use each, how to configure them, and how they compare.

What Is Drilldown in Power BI?

Drilldown allows users to navigate within the same visual to explore data at progressively lower levels of detail using a predefined hierarchy.

Key Characteristics

Happens inside a single visual
Uses hierarchies (date, geography, product, etc.)
Does not navigate to another page
Best for progressive exploration

Example

A column chart showing:

Year → Quarter → Month → Day
A user clicks on 2024 to drill down into quarters, then into months.

Here is a short YouTube video on how to drilldown in a table visual.

When to Use Drilldown

Use drilldown when:

You want users to explore trends step by step
The data naturally follows a hierarchical structure
Context should remain within the same chart
You want a quick, visual breakdown

Typical use cases:

Time-based analysis (Year → Month → Day)
Sales by Category → Subcategory → Product
Geographic analysis (Country → State → City)

How to Set Up Drilldown

Step-by-Step

Select a visual (bar chart, column chart, etc.)
Drag multiple fields into the Axis (or equivalent) in hierarchical order
Enable drill mode by clicking the Drill Down icon (↓) on the visual
Interact with the visual:
- Click a data point to drill
- Use Drill Up to return to higher levels

Notes

Power BI auto-creates date hierarchies unless disabled
Drilldown works only when multiple hierarchy levels exist

Here is a YouTube video on how to set up hierarchies and drilldown in Power BI.

What Is Drill-through in Power BI?

Drill-through allows users to navigate from one report page to another page that shows detailed, filtered information based on a selected value.

Key Characteristics

Navigates to a different report page
Passes filters automatically
Designed for detailed analysis
Often uses dedicated detail pages

Example

From a summary sales page:

Right-click Product = Laptop
Drill through to a “Product Details” page
Page shows sales, margin, customers, and trends for Laptop only

When to Use Drill-through

Use drill-through when:

You need a separate, detailed view
The analysis requires multiple visuals
You want to preserve context via filters
Detail pages would clutter a summary page

Typical use cases:

Customer detail pages
Product performance analysis
Region- or department-specific deep dives
Incident or transaction-level reviews

How to Set Up Drill-through

Step-by-Step

Create a new report page
Add the desired detail visuals
Drag one or more fields into the Drill-through filters pane
(Optional) Add a Back button using:
- Insert → Buttons → Back
Test by right-clicking a data point on another page and selecting Drill through

Notes

Multiple fields can be passed
Works across visuals and tables
Requires right-click interaction (unless buttons are used)

Here is a short YouTube video on how to set up drill-through in Power BI

And here is a detailed YouTube video on creating a drill-through page in Power BI.

Drilldown vs. Drill-through: Key Differences

Feature	Drilldown	Drill-through
Navigation	Same visual	Different page
Uses hierarchies	Yes	No (uses filters)
Page change	No	Yes
Level of detail	Incremental	Comprehensive
Typical use	Trend exploration	Detailed analysis
User interaction	Click	Right-click or button

Similarities Between Drilldown and Drill-through

Despite their differences, both features:

Enhance interactive data exploration
Preserve user context
Reduce report clutter
Improve self-service analytics
Work with Power BI visuals and filters

Common Pitfalls and Best Practices

Best Practices

Use drilldown for simple, hierarchical exploration
Use drill-through for rich, detailed analysis
Clearly label drill-through pages
Add Back buttons for usability
Avoid overloading a single visual with too many drill levels

Common Mistakes

Using drilldown when a detail page is needed
Forgetting to configure drill-through filters
Hiding drill-through functionality from users
Mixing drilldown and drill-through without clear design intent

Summary

Drilldown = explore deeper within the same visual
Drill-through = navigate to a dedicated detail page
Drilldown is best for hierarchies and trends
Drill-through is best for focused, detailed analysis

Understanding when and how to use each feature is essential for building intuitive, powerful Power BI reports—and it’s a common topic tested in Power BI certification exams.

Thanks for reading and good luck on your data journey!