🚧 Under construction — VoTechWorks is being built. A free tech program — complimentary, as our commitment to get America working.
voTechWorksGrade 13 · a vocational technology learning center, by urbanicity
newSchoolWorks.com › VoTechWorks › Technology & Design › Data Science & Analytics › Dashboards: Design, Build, and Deploy with Power BI and Tableau › Lesson 3
TEC-DATA85-07 · Lesson 3 · 10 clock hours · demo

Data Modeling: Star Schemas and Relationships in Power BI

Objective. Students construct a star-schema data model with at least one fact table and two dimension tables, define correct relationship cardinality and cross-filter direction, and explain why fan-out and many-to-many relationships cause incorrect aggregations.

⚠ Pending professional review

This lesson, its worked examples and its practice questions has not yet been verified by an industry-certified professional or by a certification or approval body. It is a study draft and may contain errors. Do not use it as instructions for work on a job site or on live equipment. Always follow your instructor, your supervising professional, and current official references and regulations.

VoTechWorks program approval and accreditation are still in progress. Credentials are issued only by the certifying bodies named in each program, after their own exams.

What Is a Star Schema?

A star schema is a data modeling pattern in which a central fact table connects to one or more dimension tables through foreign-key relationships. When you draw the model on paper, the fact table sits in the middle and the dimension tables radiate outward like points on a star. This layout is the foundation of analytical data models and is the pattern Power BI is optimized to work with.

The fact table stores measurable, numeric events — sales transactions, web-page views, sensor readings. Each row is one event. The fact table is usually wide (many rows) and contains foreign keys that point to the dimension tables.

Dimension tables store the descriptive context for those events — who, what, where, when. A Date dimension, a Product dimension, and a Customer dimension are classic examples. Dimension tables are usually narrow (fewer rows, more columns describing attributes).

A model with one fact table and two dimension tables already qualifies as a star schema. When a dimension itself connects to a second dimension, the structure is called a snowflake schema; Power BI handles both, but the pure star is preferred because it produces fewer joins at query time and is easier to maintain.

Relationships, Cardinality, and Cross-Filter Direction

In Power BI Desktop, you connect tables by drawing a relationship line between matching key columns. Every relationship has two properties you must set deliberately: cardinality and cross-filter direction.

Cardinality describes how many rows on each side of the relationship match one row on the other side. The standard setting for a star schema is Many-to-One (\*:1): many rows in the fact table share one matching row in the dimension table. For example, thousands of sales rows might all reference the same Product ID that appears exactly once in the Product dimension. Power BI also supports One-to-One and Many-to-Many; those settings have specific, narrow uses described below.

Cross-filter direction controls which table can filter the other when a user interacts with a visual. In a standard star schema, set this to Single so that the dimension filters the fact table — never the reverse. Bidirectional filtering allows the fact table to filter the dimension as well, which seems convenient but frequently produces ambiguous filter paths and incorrect results, especially in models with more than two tables.

Why Fan-Out Causes Wrong Aggregations

Fan-out happens when Power BI follows a relationship in a direction that multiplies rows unexpectedly. Imagine a Sales fact table linked to a Returns fact table through a Product dimension, with bidirectional filtering turned on. A filter placed on Sales can travel through Product and reach Returns, inflating or distorting the Return totals. The visual shows numbers that appear plausible but are mathematically wrong — one of the most dangerous data errors because it is silent. Always keep cross-filter direction on Single unless you have a specific, tested reason to change it, and document that reason.

The Many-to-Many Problem

A Many-to-Many (\:\) relationship arises when neither key column contains unique values. For example, a table of store locations and a table of product categories, where one store carries many categories and one category appears in many stores, form a true many-to-many. Power BI allows you to set this cardinality directly, but it does so through a hidden intermediate step that can produce duplicate counts in aggregations.

The safe, explicit solution is a bridge table (also called a junction table): a third table whose rows represent each unique store-category pair, with a foreign key to each side. The bridge table breaks the many-to-many into two Many-to-One relationships, which Power BI handles cleanly. Leaving an active many-to-many relationship without a bridge table is a model defect that will appear on the PL-300 exam and will cause real reporting errors in production.

Building the Star Schema in Power BI Desktop

1. Load your tables using Get Data or open the provided .pbix starter file.

2. Switch to Model view (the diagram icon on the left rail).

3. Drag the foreign-key column from the fact table onto the primary-key column of the dimension table. Power BI draws a relationship line and guesses cardinality.

4. Double-click each relationship line to open the Edit Relationship dialog. Verify that cardinality is Many to One (\*:1) and cross-filter direction is Single. Correct anything Power BI guessed incorrectly.

5. Confirm that dimension primary-key columns contain no duplicate values (use Column tools → Column quality in Power Query if in doubt).

6. Repeat for each dimension table until every foreign key in the fact table is connected.

Documenting the Model

After building the model, create an entity-relationship (ER) diagram in draw.io or Lucidchart. Label each table, mark primary keys (PK) and foreign keys (FK), use standard crow's-foot or UML notation for cardinality, and draw an arrow showing filter direction. Documentation is not optional: in a professional environment, undocumented models become unmaintainable. In this course, your ER diagram is part of the graded deliverable.

Accuracy and Data Integrity as Safety Practices

Data science has no electrical hazards that require lockout/tagout, but it has its own form of safety: data integrity. Incorrect relationship settings produce wrong aggregations that flow into dashboards, which then inform business decisions. Treat an unverified many-to-many relationship or an untested bidirectional filter the way a technician treats an unlocked energy source — do not leave it active until it is confirmed safe. Always validate totals in a simple visual (a card or table) against a known-correct source before publishing any report.

Key terms10

Fact tableThe central table in a star schema that stores numeric, measurable events (such as sales or transactions) and contains foreign keys linking to dimension tables.
Dimension tableA table that provides descriptive context for fact-table events, such as Date, Product, or Customer; dimension tables have a unique primary key.
Star schemaA data model pattern in which one or more fact tables connect to multiple dimension tables through Many-to-One relationships, forming a star shape in the diagram.
CardinalityThe numerical relationship between rows on each side of a table relationship; common settings in Power BI are Many-to-One, One-to-One, and Many-to-Many.
Cross-filter directionThe setting that controls which table can filter the other across a relationship; Single means the dimension filters the fact table only; Bidirectional allows filtering in both directions.
Fan-outAn unintended row multiplication that occurs when filters travel across multiple fact tables through shared dimensions with bidirectional filtering, producing inflated or incorrect aggregation results.
Many-to-Many relationshipA relationship in which neither key column contains unique values; without a bridge table, this setting can cause duplicate counts and incorrect totals in Power BI visuals.
Bridge tableA junction table that resolves a many-to-many relationship by listing each unique combination of the two sides as its own row, converting the single many-to-many into two Many-to-One relationships.
Primary key (PK)A column in a dimension table whose values are unique and non-null, used to identify each row and serve as the 'one' side of a Many-to-One relationship.
Foreign key (FK)A column in the fact table whose values reference the primary key of a dimension table, establishing the join between the two tables.
In class: Live instructor demo followed by immediate replication: instructor builds a star schema from three tables on the projector; students replicate every step simultaneously and call out discrepancies. · Cardinality diagnosis: students receive a pre-built model with two incorrect relationship settings (many-to-many and wrong cross-filter direction); they diagnose the symptom (wrong totals in a sample visual), correct the relationships, and record before/after screenshots in their lab log. · Schema documentation: students draft an entity-relationship diagram of their model using draw.io or Lucidchart, labeling primary keys, foreign keys, cardinality symbols, and filter direction arrows.
Materials: Power BI Desktop, provided .pbix starter file, draw.io or Lucidchart (free tier), Microsoft documentation on model relationships, lab log
Check: ER diagram reviewed by instructor; pass requires correct cardinality on all relationships, no active many-to-many relationships without a bridge table, and a written one-sentence justification for each cross-filter direction. PL-300 mapping: Model the data domain.

Practice check8

Answer each question, then check it. Nothing is recorded; this is your own practice.

Model the data domain (PL-300)
1. In a Power BI star schema, which table typically sits at the center of the model and contains foreign keys?
Model the data domain (PL-300)
2. A data model has a Sales fact table and a Product dimension table. Many sales rows share the same Product ID, which appears once in the Product table. What is the correct cardinality setting for this relationship?
Model the data domain (PL-300)
3. A report shows total revenue figures that are significantly higher than the known correct value. After investigation, you find that two fact tables share a dimension and all relationships are set to Bidirectional. What is the most likely cause?
Model the data domain (PL-300)
4. A student sets a relationship between two tables to Many-to-Many in Power BI without adding a bridge table. Which outcome is most likely when a measure sums a numeric column through this relationship?
Model the data domain (PL-300)
5. What is the purpose of a bridge table in a Power BI data model?
Model the data domain (PL-300)
6. When should you choose Bidirectional cross-filter direction in a Power BI relationship?
Model the data domain (PL-300)
7. In an ER diagram for a Power BI star schema, what does a crow's-foot symbol on the end of a relationship line closest to the fact table indicate?
Model the data domain (PL-300)
8. A student's Power BI model has a Date dimension connected to both a Sales fact table and a Budget fact table, with cross-filter direction set to Single on both relationships. A visual using a measure from the Sales table and a slicer from the Date dimension works correctly. What describes the filter flow?
Record this lesson on my transcript. Sign in with your key and enrollment email to record lessons (enroll free if you have no key). This page stays open to read and practise.
← Previous lessonProgram planNext lesson →