Schema and Data Modelling.
This is a logical structure that defines how data is organized within a database. Database schemas provide a logical blueprint for data storage and organization, for greater user accessibility, scalability, and data integrity. This blueprint is inclusive of logical constraints such as, table names, fields, data types and the relationships between these entities. The schema does not contain the actual data itself, but rather provides the structure that the data must conform to.
Schemas commonly use visual representations to communicate the architecture of the database, becoming the foundation for an organization’s data management discipline. This process is known as Data modelling. It involves the process of modelling Database Schemas.
A data model is a diagram that visually represents a conceptual framework for organizing, defining, and showing the relationships between data elements. This visual method helps clarify complex connections between various data points, simplifying the design of efficient and well-structured databases.
Most common Schemas used on Power Bi
There are 2 main schemas used in Power Bi. They include Star Schema and Snowflake Schema.
Star Schema
A star schema is a type of schema where a single central fact table is surrounded by multiple-dimension tables. This single fact table contains facts of the data model, while the dimension tables contain descriptive properties or dimensions of the data model. This schema resembles a star shape. The Power BI engine works best with star schema.
Businesses can utilize a star schema to manage and organize large datasets based on two primary principles: facts and dimensions.
Facts: The center of the structure and provides measurement-based pieces of data. Examples of such central facts are the number of transactions, website clicks, or total purchases.
Dimension: Provides additional information about the fact, such as which customer made the purchase, where they made it from, and what product they bought.
Why is star preferred?
- Easier to understand: Their dimensions can be used to slice and dice data and facts.
- Better Performance: Since they have lesser joins and shorter paths, a better performance is guaranteed.
- Scalable: It is easier to add new dimension tables.
Snowflake Schema
A snowflake schema is a type of schema that extends the star schema by normalizing dimension tables. In this schema, the dimension tables are further broken down into sub-dimension tables, creating a more complex structure. For example, the dimension product is further divided into category and subcategory which can be seen attached to the product dimension table.
Advantage: Beneficial for reducing data redundancy in complex scenarios
Disadvantage: Slower performance due to increased table joins.
Top comments (0)