Certified Business Intelligence Analyst

Introduction: The relational database model and SQL
Database concepts, SQL basics, relational database overview, Structured Query Language

Installation and first steps with MySQL workbench
MySQL workbench, installation procedure, configuring MySQL environment

Getting started: Creating a database and tables
Database creation, table design, schema definition

Practice: Create your first SQL database and insert tables into this database.

Inserting, editing, and querying values
Data insertion, data editing, basic queries

Practice: Manipulating data with SQL syntax.

Additional options for using the WHERE clause
Conditional filtering, WHERE clause functions

Practice: Creating specific requirements for the WHERE clause.

Using foreign keys in the database
Foreign key concept, referential integrity

Practice: Understand and use foreign keys within a SQL database.

Connecting queries for multiple tables
Multiple table relationships, JOIN operations

Practice: Designing queries for multiple tables.

The JOIN command: Using data from multiple tables
JOIN types, data integration

Practice: Working with the JOIN command.

Counting entries, summing values, and other arithmetic operations
Aggregate functions, arithmetic operations

Practice: Performing arithmetic operations with values.

Grouping and sorting data
Data grouping, sorting techniques

Practice: Creating grouped and sorted queries.

Introduction: More advanced SQL commands for BI
Introduction, importance of advanced SQL for BI and data analysis

Establishing conditions with the HAVING clause
HAVING clause, WHERE, SELECT

Practice: Write SQL queries using the HAVING clause in combination with WHERE and SELECT to filter grouped data based on specific conditions.

Working with wildcards
Wildcards, REGEXP, RLIKE

Practice: Use wildcards, REGEXP, and RLIKE to search for patterns in data and run queries that match specific text patterns in tables.

CASE: Binding actions to conditions
CASE WHEN, END, THEN, ELSE

Practice: Implement the CASE WHEN statement to apply conditional logic within SQL queries, modifying query outputs based on different scenarios.

Relationships between tables with foreign keys
Relationships, 1:1, 1:n, n:m

Practice: Establish relationships between tables using foreign keys and design 1:1, 1.n, and n:m relationships to query linked datasets.

Advanced options for data output
CONCAT, Alias, virtual tables, views

Practice: Create advanced query outputs using CONCAT, aliases, virtual tables, and views to customize how data is displayed and structured.

Working with dates and times
DATETIME, TIMESTAMP, DATE, TIME

Practice: Query and manipulate data with date and time functions using DATETIME, TIMESTAMP, DATE, and TIME in SQL.

Managing database access rights
Access rights, usernames, accounts, GRANT, privileges

Practice: Set up access controls for database users by using GRANT to assign privileges and managing usernames and accounts.

Transactions for safer data handling
ROLLBACK, START TRANSACTION, COMMIT

Practice: Use START TRANSACTION, COMMIT, and ROLLBACK to create safe data transactions that allow for error recovery and integrity.

Important advanced SQL queries for BI – part 1
Window functions, RANK, ROLLUP, EXTRACT

Practice: Apply window functions like RANK, ROLLUP, and EXTRACT to perform advanced data analysis and summarize data for BI purposes.

Important advanced SQL queries for BI – part 2
Window functions, LAG, LEAD

Practice: Use window functions such as LAG and LEAD to analyze sequential data and create insights from trends over time.

Importing CSV files
SV files, LOAD DATA INFILE, Table Data Import Wizard

Practice: Import data from CSV files into SQL databases using LOAD DATA INFILE and the Table Data Import Wizard.

Introduction
Introduction, role and importance of Power BI

Getting started with Microsoft Power BI: the data model and architecture
Data model, fact and dimension tables, Power BI architecture

Practice: Create a basic star schema data model.

The Power BI Desktop Client
Desktop Client, query editor, report view

Practice: Familiarize yourself with the Power BI Desktop Client, utilizing the Query Editor to load and transform data and the report view to design your first report.

From zero to hero: Build an initial Power BI report
Power BI report, visuals, insights

Practice: Develop an initial Power BI report by adding visuals, formatting them, and gaining insights.

Importing data into Power BI
Data import, connectors, DirectQuery

Practice: Import data from various sources into Power BI.

Processing the imported data
Data transformation, pivoting and unpivoting, merging queries, date columns

Practice: Transform imported data to enhance your datasets for deeper analysis in Power BI.

Establishing relationships between datasets
Relationships, 1:n, 1:1, n:m, cardinalities

Practice: Define and establish relationships between datasets in Power BI, configuring 1:n, 1:1, and n:m cardinalities to enable effective data modeling.

Visualizations – part 1
Visuals, charts, graphs, insights, filtering data

Practice: Create and customize various visuals, charts, and graphs in Power BI to derive insights and represent your data effectively.

Visualizations – part 2
Advanced Power BI visualization techniques, interactive elements

Practice: Explore advanced visualization techniques in Power BI, including interactive elements such as slicers, to enhance user engagement with your reports.

Building dashboards
Dashboards, KPIs, decision-making

Practice: Design and build interactive dashboards in Power BI, incorporating key performance indicators (KPIs) to facilitate informed decision-making.

Introduction: More Power BI
Introduction, selected advanced topics

Custom visuals
Custom visuals, enhanced data presentations, reporting requirements

Practice: Create and implement custom visuals in Power BI to enhance data presentations and meet specific reporting requirements.

Advanced reporting concepts – part 1
Calculated fields, calculated measures, DAX optimization

Practice: Use drilldown and drillthrough to improve reporting efficiency and user experience.

Advanced reporting concepts – part 2
Bookmarks, tooltips

Practice: Utilize bookmarks and tooltips in Power BI for advanced reporting.

An introduction into DAX
Explicit and implicit measures, calculated columns, calculated tables

Practice: Explore explicit measures to perform calculations and data manipulation in your Power BI reports.

More on DAX
DAX functions, filter context, time intelligence, quick measures

Practice: Deepen your understanding of DAX by applying various functions to create complex calculations that enhance your data analysis capabilities.

An introduction into M
M language, Power Query

Practice: Learn the M language in Power Query to prepare data for analysis in Power BI.

Introduction
Introduction, MS PL-300 exam preparation

Design a data model
Relationships, cardinalities, common date table, cross-filter direction

Practice: Create a data model that incorporates data normalization and establishes relationships.

Develop a data model
Hierarchies, Q&A feature, calculated tables, calculated columns

Practice: Develop an effective data model by adding hierarchies, calculated tables and columns.

Create model calculations by using DAX – part 1
DAX fundamentals, CALCULATE, time intelligence

Practice: Implement DAX fundamentals by creating explicit measures to derive meaningful insights from your model.

Create model calculations by using DAX – part 2
Advanced DAX functions, quick measures, semi-additive measures

Practice: Apply advanced DAX functions to create measures that consider row context and filter context for accurate data calculations.

Create reports – part 1
Data-driven insights, report interactivity

Practice: Build interactive reports that enable users to derive data-driven insights from visualizations.

Create reports – part 2
Conditional formatting, slicers, filters

Practice: Utilize features like slicers and filters to facilitate storytelling and user experience through effective report design.

Create dashboards
Interactive dashboards, quick insights feature, themes, dashboard design

Practice: Design interactive dashboards to support data-driven decision-making.

Enhance reports for usability and storytelling
Data-driven storytelling, insights, visual cues, annotations, contextual information

Practice: Enhance reports by integrating visual cues, annotations, and contextual information to facilitate data-driven storytelling and insights.

Identify pattern and trends
Significant patterns and trends, anomalies, correlations, dynamic visualizations

Practice: Analyze data to identify significant patterns, trends, anomalies, and correlations using dynamic visualizations for effective communication.

Introduction to Tableau: Data visualization and Business Intelligence
Introduction, role and importance of Tableau in BI

Getting started with Tableau Desktop
Tableau workbook, data connection, data cleaning, data pane, split, pivot, relationships, joins, performance optimization

Practice: Set up a new Tableau workbook, connect to a data source, clean and organize the data using the data pane, apply a split or pivot, and establish appropriate relationships or joins between tables based on performance needs.

Foundations of Chart visualizations
Continuous fields, discrete fields, dimensions, measures, chart appearance, axis, labels, chart types, data fields, visualization

Practice: Create different types of charts in Tableau by using dimensions and measures, adjust chart appearance with axis and label options, and arrange data fields to explore different visualization formats.

Sorting, grouping, and calculations
Sorting, grouping, filtering, alias, calculated fields, functions, sets, table calculations, data transformation

Practice: Use Tableau filters, sorting, and aliases to organize data, create new data fields with groups and sets, and apply calculated fields and table calculations to transform and analyze the data.

Working with maps and adding customizations to your view
Maps, spatial data, map types, Tableau Marks card, workbook customization, Trend lines, Reference lines, formatting

Practice: Create and customize map visualizations in Tableau, applying different map types and customizing the view with the Marks card.

Dashboards – Combining Views
Tableau dashboard, combining views, worksheets, Tableau story

Practice: Combine multiple views to create a Tableau dashboard and a story.

In a capstone project, you will apply the skills you learned in the course and demonstrate them through a presentation to your mentor. You will develop your own project based on the acquired knowledge and apply the techniques learned in practice.