Data modelling in Power BI is the process of connecting and structuring your data to build a foundation for analysis and visualization. It involves organizing and creating relationships between different datasets, ensuring that your reports and dashboards can pull the right insights from your Model.
Model refers to the structure that defines how data is organized, related, and represented in a way that is easy for users to understand and interact with. It abstracts the complexities of raw data and presents a user-friendly view for analysis and reporting. The semantic model in Power BI is commonly referred to as a Tabular Model, and it includes several key components:
- Tables: Organized collections of data, similar to database tables.
- Relationships: Links between tables that define how data in one table relates to data in another.
- Measures: Calculated values, often using DAX (Data Analysis Expressions), such as sums, averages, or custom metrics.
- Columns: Fields within tables, which can be calculated or raw data.
- Hierarchies: Groupings of columns that provide drill-down capabilities (e.g., Year > Quarter > Month).
- Metadata: Information that describes data types, names, and formats, providing context to users interacting with the model.
Steps to Develop the Model:
- Connect to Data:
- Transform and Prepare Data:
- Define Business Logic (Add DAX Calculations):
- Publish the Model to Power BI:
Why is the Semantic Model Important?
- Reports load quickly: If your model is efficient, users won’t have to wait long to see updated charts and graphs.
- Data refreshes smoothly: When new data comes in, your model can handle updates without slowing down.
- Less memory and CPU are used: This means more models can be hosted without overloading the system, which saves costs.
Start Schema
The star schema design in Power BI provides an efficient and user-friendly way to organize data, enabling better reporting performance, easy filtering, and accurate analysis. It ensures that the data model supports fast, scalable queries, making it ideal for business intelligence tasks.
Think of a star schema like a pizza.
- The pizza itself: This is your fact table. It's the main part, filled with lots of data points (like the toppings on a pizza).
- The toppings: These are your dimension tables. They add details and flavor to the pizza (fact table). You can choose different toppings (dimensions) to get different kinds of pizza.
Here's a breakdown of how it works:
- Fact tables store events or observations: Imagine the pizza as a record of an event, like a sale. The toppings (dimensions) would be the details of that sale, like the product, customer, and date.
- Dimension tables describe business entities: These are the "toppings" that give context to the events in the fact table. They provide details like who, what, when, where, and how.
- Fact tables focus on summarization: The pizza itself (fact table) is all about summarizing the event. You can calculate things like total sales or average order value.
- Dimension tables focus on filtering and grouping: The toppings (dimension tables) let you filter and group the data. For example, you could filter for sales in a specific month or group sales by product category.
- Unique keys in dimension tables: Each topping (dimension) has a unique identifier (like a product ID or customer ID). This helps connect the toppings to the pizza (fact table).
- Relationships between fact and dimension tables: The toppings are related to the pizza (fact table). So, if you choose a specific topping (dimension), it will affect the data shown in the fact table.
- Efficient queries and reporting: A well-designed pizza (star schema) is easy to eat (query). You can quickly get the information you want because everything is organized in a clear way.
- Multiple fact and dimension tables: You can have many different pizzas (fact tables) with different toppings (dimension tables). This gives you flexibility in how you analyze your data.
Analytic queries
Analytic queries in Power BI are the underlying calculations that produce the visuals you see on your reports. They involve three main steps:
- Filtering: This step determines which data points to include in the analysis. It's like narrowing down your focus to specific parts of the data.
- Grouping: This step divides the data into categories or groups. It's like organizing data into buckets.
- Summarizing: This step calculates a single value from the grouped data. It's like getting a summary of the data.
- DAX: Analytic queries are written in DAX, a formula language. However, you often don't need to write DAX directly as Power BI can generate them automatically based on your visual configurations.
- Scopes: Filters can be applied at different levels: report-wide, page-level, or visual-level.
- Row-level security (RLS): This feature can restrict access to certain data based on user roles or permissions.
- Visual-specific requirements: Some visuals, like cards or slicers, may not require all three steps.
