DEV Community

Faith Neno
Faith Neno

Posted on

Data Modelling and Schemas in Power BI

Understanding Data modelling in Power BI
Data modelling is process of organizing data tables and defining relationships between them in a way that reflects real-world business processes. In Power BI, the data model sits between raw data sources and visualizations, acting as the foundation for calculations, filters, and aggregations.

Types of data models
1.Conceptual data modelling; It defines what data a business needs and how different concepts relate .i.e customers, product.
2.Logical data modelling; add columns and attributes facts and dimensions; how you find all this entities and how they are related.
3.Physical data modelling; This is how data is stored ,organized and accessed at the physical level i.e product key. This type of data model consists of a data management system (DBMS); certain specific properties comprise performance turning.

Terms
Dimensional model -Organizes data so its easy to retrieve for reporting purpose.
Fact table -Is an event that may or may not include measure
Dimension table -Category of information ,or a noun, descriptive.
Attribute -Column in dimension table. Descriptor of the object.

Attributes of good data model

  • Can be easily understood and consumed.

  • Provides predictable performance.

  • Large data are scalable.

  • Flexible and adaptable.

Fact and Dimension Tables
Fact Table
The fact table in a dimensional model stores the performance measurements resulting from an organization’s business process events.
The term fact represents a business measure.
Example: cost, discount, quantity sold

Fact table typically,

  • Have many rows (high granularity)
  • Store numeric values used in aggregations (SUM, AVERAGE, COUNT)

Dimension Table
Dimension Tables for Descriptive Context.
Dimension tables are integral companions to a fact table.
The dimension tables contains the textual context associated with a business process measurement event.
They describe the “who, what, where, when, how, and why” associated with the event.
Example:
Dimension tables often have many columns or
attributes. It is not uncommon for a dimension table to have 50 to 100 attributes.
Dimension tables;

  • have fewer rows than fact tables, but can be wide with many large text columns. Each dimension is defined by a single primary key.
  • contains descriptive characteristics of business process nouns.
  • Dimension attributes serve as the primary source of query constraints, grouping and report labels.

Example; Date,region,Customer details.

Star Schema
Dimensional models implemented in relational database management systems are referred to as star schemas because of their resemblance to a star-like structure.
The fact table is at the center and dimension are around it.
Their no relationship between dimension tables.
Its easier to write DAX measures.

Snowflake Schema
This is a variation of star schema.
The dimension table is not one joint away, hence increasing model complexity .

Relationships in Power BI
Relationships define how tables interact data models.
Terms
Cardinality One-to-many , one-to-one, many-to-many
Cross-filter direction Single or both
Active vs inactive relationships

Importance Good Data Modelling

  • Accuracy in reporting.
  • Improved Data Quality and Integrity
  • Enhanced performance.
  • Scalability and flexibility.

In conclusion, a good data modelling is critical because it directly impacts Power BI's performance, accuracy and sclability. A well structured model ensure fast queries, correct calculations and intuitive reporting, while poor modelling leads to slow dashboards, incorrect insights and wasted memory.

Top comments (0)