Business Intelligence (BI) Concepts

• Business Intelligence (BI) is a collection of tools and methods converting raw data into actionable insights.
• BI enables data-driven decision-making and automation, reducing time spent on manual tasks.

Classical BI Architecture

• Data originates from sources like Excel, CSV, SQL, XML, and PDF files.
• ETL processes prepare and clean data via extraction, transformation (removing duplicates, replacing values, adding columns), and loading.
• A Data Warehouse serves as a centralized relational database for querying.

Traditional vs. Self-Service BI

• Traditional BI relies on BI developers for report creation.
• Self-Service BI empowers data analysts to independently retrieve, clean, and model data.

Power BI Overview

• Power BI evolved from Excel add-ins, including Power Query, Power Pivot, Power View, and Power Map.
• It comprises Power Query for data retrieval and transformation, a semantic model for storing interconnected tables and calculations, and interactive reports and dashboards.

Project Lifecycle

• Extracting and cleaning data is done using Power Query.
• Data is structured using tables and relationships in the modeling phase.
• Interactive dashboards are created.
• Reports are deployed via Power BI Service.

Data Modeling

• Relationships define links between tables.
• Cardinality defines the numerical relationship between the rows in the tables, with One-to-Many being the most common.
• Cross-filtering can be single (recommended) or both (avoided due to performance issues).
• Star schemas, with fact tables at the center and dimension tables surrounding them, are preferred; snowflake schemas, with sub-dimensions, are less common, similarly constellation schemas are also used in some cases.

Power Query (ETL)

• Cleaning and modifying data through transformations is key.
• Appending stacks tables vertically, while merging joins them horizontally.
• Parameters allow for dynamic data filtering.
• Load only necessary columns, verify data types, use parameters, and filter data early.

DAX (Data Analysis Expressions)

• Measures are dynamic calculations performed on-the-fly, using functions like SUM() and AVG().
• Calculated columns are static and stored in the model, which is less efficient.
• CALCULATE() modifies the context of a measure.
• Time intelligence functions include SAMEPERIODLASTYEAR(), DATESYTD(), and DATESINPERIOD().

Reporting Best Practices

• Use themes and correct color palettes to ensure consistency.
• Avoid white backgrounds.
• Use icons and rounded corners to enhance readability.
• Space and align visuals to avoid clutter.
• Select visuals appropriate for the data.
• Use tooltips and drill-through pages to improve interactivity.

Power BI Service & Sharing

• The Power BI Service is a cloud platform for publishing and sharing.
• Workspaces are containers for reports and models with roles like Viewer, Contributor, Member, and Admin.
• Apps are collections of dashboards and reports shared with users.
• Cloud data sources update automatically.
• On-premises data requires a Data Gateway for updates.
• Static RLS involves manual assignment of roles, while Dynamic RLS uses user emails for automatic filtering.