Schemas and data modelling in Power BI(INTRO)

What is Data Modelling?
This is simply setting up tables, relationships, calculations, and access for analysis tasks.
Types of tables
Fact table - does not contain actual info. Contains pointers to other tables. It's really long and narrow because of the few columns.
Dimension table - each table explains one dimension of data. This is like the owner of that information. Normally shorter compared to the fact table. It has many rows.
Setting up this data is called data modelling.

Types of Database Schema

• conceptual database schema - It defines entities, relationships, and constraints without getting into implementation details. The conceptual schema is useful in the early stages of database design to map business requirements to database models.
• logical database schema - Focuses on what data is stored. This defines the tables, columns, relationships, and views.
• physical database schema - defines how the data is stored in the actual hardware.

Star Schema
Has one central fact and many dimensions connected. A star schema is used to organize data in a database so that it is easy to understand and analyze. Star schemas can be applied to data warehouses, databases, data marts, and other tools.

Linking Star Schema to POWER BI
This implements a star schema in Power BI using a sample data file.

• import data from Excel(load data from all the tables needed)
• Go to the model view and link all the tables to the fact table. Drag and drop the fact table onto all the dimensional tables that don't connect automatically to link them.
• You will get a pop-up asking if you want to link the two tables, their relationship, and cardinality(nature of the relationship). Confirm all details before clicking save.
• Double-check the relationships by clicking on the lines that connect the dimensional tables to the fact table. You can also use a manage relationships tab for this task.
• Filtering a dimension also changes data in a fact. -The arrows on a star schema enforce the filtering effect on every table.

Snowflake Schema
This is a fact of a fact relationship. In a star schema, all the tables are connected to the fact from a one side of the relationship to the many side, whereas in a snowflake schema, we can have many one side dimensions and other dimensions that are connected to the relationship from the many side.
watch the direction of the arrows in a relationship to identify a snowflake schema
Step-by-step explanation for navigating and editing a snowflake schema

Snowflake schema sample
Important - watch the directions of the arrows from the fact table in the relationship.