Power BI and SQL Database Training

Infoshare is the largest tech community in CEE and the organizer of the leading tech conference in Gdańsk. It connects startups, investors, corporations, and innovation enthusiasts. It promotes entrepreneurship, knowledge sharing, and networking. Through events, competitions, and programs, it supports the development of the tech ecosystem in Poland and the region.

This training is designed for individuals who deal with processing and analyzing large amounts of data, including analysts and decision-makers based on data contained in SQL databases. The training covers the use of SQL databases in conjunction with Microsoft Power BI. You can choose one of the databases for collaboration with Power BI: Microsoft SQL Server, Azure SQL, IBM DB2, Informix, Oracle SQL, PostgreSQL, MySQL, MariaDB, or Microsoft Access.

• For analysts, marketers, and anyone working with data.
• For individuals involved in processing and analyzing large amounts of data.
• For those wanting to learn the DAX library.

• You will learn how to effectively acquire and process data for business decision-making.
• You will get to know PowerQuery and data preparation techniques.
• You will learn the terminology and tools necessary for retrieving information from a database and then presenting it in Power BI.
• You will discover how to use the DAX library for reporting and data analysis.
• You will learn how to tell a story with data and visualization techniques.

• Module 1: Power BI Desktop
  1.1. Introduction to the program

  • Versions and types of licenses and program capabilities

  • Main applications and discussion of the user interface

  • Report, card, and visualization – main components of the program

  • Power BI cloud services – capabilities and limitations
    1.2. First Power BI model

  • Working with a ready-made data model – one table with data

  • Basic visualizations – connecting visual elements with data

  • Data types, conversion, and adjusting regional settings

  • Line, bar, and scatter plots. Customizing visualization elements

  • Card, table, matrix – formatting objects

  • Geographic data-based visualizations – map and cartogram

  • Visualization, card, and report filter
    1.3. Working with a model based on multiple tables

  • Tables, relationships, and data model in analysis

  • Automatic and manual table joining using relationships

  • Optimization and modification of data attached to the model

  • Creating calculated columns, KPI indicators

  • Introduction to modeling and data joining
    1.4. Power Query data model for Power BI Desktop

  • Types of data sources for Power Query – capabilities and limitations

  • Flat files and CSV, JSON folders – editing and converting information

  • Data from spreadsheets as data sources for visualizations

  • Combining custom data sources SQL + CSV + Web Pages
    1.5. Advanced visual elements

  • Key performance indicators KPI

  • Advanced data model hierarchy

  • Custom visualizations from the trading platform and file
    1.6. Sharing Power BI Desktop reports

  • Exporting visualization data to CSV file

  • Exporting report to PDF format

  • Sharing report in the cloud

  • Embedding report on SharePoint site and public WWW site
    1.7. Stages of data processing in Power BI

  • SQL query, Transformation (M language), DAX processing

• Module 2: SQL Databases
  2.1. Introduction to databases, software overview

  • Concepts related to SQL databases

  • Division of SQL language into DQL, DML, DDL, DCL

  • Connecting to SQL server and existing database

  • SQL code editor – familiarization with tools

  • Server objects: tables, views, and stored procedures
    2.2. Connecting Microsoft Power BI to the database

  • Direct connection

  • Connecting using ODBC

  • Retrieving data from tables and views (Table/View) to the data model

• Module 3: Data Model
  3.1. Power Query in connecting and transforming data

  • Filtering and sorting input data

  • Calculated and conditional columns

  • Creating custom calculated columns

  • Text and mathematical functions

  • Data types and their conversion

  • Merging and splitting columns
    3.2. Data model and M language

  • Joining and merging tables

  • Relationships in Excel and data model

  • Advanced M language functions

  • Removing duplicate values

  • Pivot and Unpivot

  • Aggregation and counting
    3.3. Practical aspects of the data model

  • Pivot table based on the data model

  • Multiple references to objects

  • Data update

• Module 4: DAX Language
  4.1. Introduction to DAX language

  • What is DAX language

  • Applications of DAX language

  • Prerequisites
    4.2. Work environment

  • Formula bar

  • Calculation area
    4.3. Practical aspects of the data model

  • Pivot table based on the data model

  • Multiple references to objects

  • Data update
    4.4. Working with the language

  • Syntax

  • Calculated column and its applications

  • Formula syntax

  • Measures in DAX

  • Formula appearance

  • Auto-summing

  • Functions and their types

  • Related function

  • Context

  • Row context

  • Filter context

• Module 5: Direct Queries
  5.1. Select queries – DQL (Data Query Language)

  • Query designer in applications and creating a query to the database

  • Basic data types in SQL and their applications

  • Data in SQL standard
    5.2. Retrieving data – SELECT statement

  • Syntax of the SELECT command

  • Order of execution of the SELECT statement
    5.3. Operators and criteria in queries

  • Operators in SQL =, <>, >=, <=, IN, BETWEEN AND, LIKE

  • Criteria in queries and combining multiple criteria using AND, OR, NOT

  • Concept and application of NULL and NOT NULL

  • Selecting columns in the query result

  • Column alias AS

  • Calculated columns

  • Sorting query results ORDER BY (ASC/DESC) and the NULL issue

  • Scalar functions

  • Date operations

  • Limiting record retrieval and removing duplicates DISTINCT

  • Conditional expression CASE
    5.4. SQL language functions in queries

  • String operations LEN, LEFT, RIGHT, REPLACE, SUBSTRING, UPPER, LOWER

  • Concatenating strings using CONCAT function

  • Mathematical functions ROUND and similar

  • Time and date functions and operations on them

  • Conversion functions and data type casting
    5.5. Processing aggregated data

  • Aggregating results in queries using GROUP BY

  • Functions COUNT, MIN, MAX, SUM, AVG

  • Criteria in aggregated results WHERE vs HAVING

  • Introduction to window functions OVER, PARTITION BY
    5.6. Operations on joined tables

  • Concept of relationships between database tables and the element connecting tables in the query

  • Inner join INNER JOIN

  • Right outer join RIGHT OUTER JOIN

  • Left outer join LEFT OUTER JOIN

  • Full outer join FULL OUTER JOIN

  • Cross join (Cartesian product) CROSS JOIN

  • Equality and inequality joins
    5.7. Combining query results

  • Combining tables using: UNION, UNION ALL

  • Common parts and INTERSECT, EXCEPT/MINUS

• Module 6: Advanced Queries
  6.1. Nested queries

  • Query result as a condition WHERE in the query

  • Query based on another query and query optimization

  • Table variables CTE
    6.2. Window functions in SQL

  • Ordered views OVER

  • Partitioning query result PARTITION BY

  • Row references: previous, next, first, and last
    6.3. Query optimization

  • Stored procedures

  • Generating queries using stored procedures

24 h/3 days