Power BI is a business intelligence and data visualization tool developed by Microsoft that allows users to connect to multiple data sources, transform and model data, and create reports and dashboards.

This article covers the various types of tables, schemas, data relationships, and data models in Power BI.

Tables

To support BI analysis, data is organized into multiple tables, each serving a specific purpose: fact tables and dimension tables.

Fact Table

The fact table is the central table in the dataset. It stores transaction data and measurable business information. Using a dataset that represents information about students, teachers, subjects, schools, and locations as an example, the fact table is the larger table that contains all of that information consolidated in one place.

The fact table has the following characteristics:

  • Many columns, combining both:
    • Descriptive columns — student_name, school_name, assessment_type, gender, etc.
    • Numeric columns — values that can be aggregated, such as age, fees, and performance_score, which can be used with SUM or AVERAGE in DAX measures
  • Repeated values — country, region, teachers, schools
  • ID columns (foreign keys) — student ID, teacher ID, subject ID, school ID

Foreign keys link the fact table to dimension tables.

The fact table is the central table in a star schema or snowflake schema of a data warehouse.

Dimension Table

Dimension tables help filter, group, and describe the fact table. They connect to the fact table and sit at the edge of a star or snowflake schema, with their primary key mapped to a foreign key in the fact table.

Using the student dataset as an example, you could create separate dimension tables for students, subjects, and schools. The student dimension table would contain the student ID, student name, region, age, and other student-specific details. Dimension tables generally answer questions like: Who? What? Where? When? Which category? Which subject? Which teacher?

Schemas

A schema refers to the overall structure of how tables are organized and related in a data model. It defines how many tables exist, how they connect, and how data flows between them.

Star Schema

A star schema is a data model with one central fact table and several dimension tables surrounding it. It is called a star schema because its structure resembles a star — the fact table sits in the middle and the dimension tables radiate outward. Star schemas reduce data duplication, improve query performance, and make models easier to understand.

Snowflake Schema

A snowflake schema is a variation of the star schema where dimension tables are further normalized into additional related tables, creating a more layered structure.

Relationships

Relationships connect the fact table to dimension tables. The fact table sits on the “many” side of a relationship, while dimension tables sit on the “one” side. These relationships are what turn a schema into a working data model.

Relationships are built using keys:

  • Primary key — a column that uniquely identifies each row in a dimension table. For example, subject_id appears only once in the subject table.
  • Foreign key — the matching key in another table. For example, subject_id in the fact table can repeat because one subject can be associated with many different students.

Cardinality

Cardinality describes how rows in one table relate to rows in another. The main relationship types in Power BI are:

  • One-to-many — one row in a dimension table relates to multiple rows in the fact table. This is the most common type used in star schemas.
  • One-to-one — each row in one table corresponds to exactly one row in another table.
  • Many-to-many — rows in both tables can relate to multiple rows in the other, typically requiring a bridging table or special handling in Power BI.

Understanding cardinality is essential for building accurate data models, as the wrong relationship type can lead to incorrect calculations and misleading reports.