Data analyst – working with BI Excel and SQL Server

Training process

Training needs analysis

If you have specific requirements regarding the training programme, we will carry out a training needs analysis for you. This will guide us on which aspects of the programme should receive greater emphasis, so that the training programme meets your specific needs.

What will you gain?

Faster analysis in Excel - You will organize the way you work in Excel and build reliable spreadsheets more efficiently. With named ranges, references and better formulas, you will reduce errors in daily analysis.

More reliable pivot reporting - You will learn how to create and modify pivot tables and pivot charts to summarize large datasets faster, filter results with confidence and present findings in a clear, useful format.

Combining multiple data sources - After the course, you will be able to connect data from Excel files, CSV, TXT and databases on your own. This will help you merge scattered information into one consistent source for analysis.

Data modeling with Power Pivot - You will build a relationship-based data model and work with larger datasets than a regular worksheet can handle. This will let you create analyses that are more robust and easier to expand.

Practical DAX skills - You will master calculated columns, measures and DAX functions, including CALCULATE and time intelligence. This will help you build KPI logic and period comparisons without manual workarounds.

Clearer reports in Power BI - You will learn how to choose the right visuals, design interactions and publish reports in Power BI. As a result, you will present data in a form that stakeholders understand and use faster.

Stronger SQL Server querying - You will write SQL queries on your own using SELECT, WHERE, JOIN, GROUP BY and HAVING. This gives you more independence when retrieving data and verifying information quality at the source.

A broader BI perspective - You will understand how to connect Excel, Power Query, Power Pivot, Power BI and SQL Server into one analytical workflow. This will help you move from raw data to final reporting more smoothly.

Training programme

1. Excel in data analysis

logic of working with the program and program architecture,
names, references and relationships in cells,
optimization of formulas and functions in Excel,
discussion of mathematical, statistical, lookup and reference, date and time functions,
nesting functions,
combining data, cooperation between worksheets,
Excel as a database,
Excel cooperation with external and internal databases,
operations on data: sorting, filtering,
data consolidation in worksheets,
data analysis tool: pivot tables – creation, modification, pivot table tools.

2. Introduction to multidimensional analysis

benefits of using PowerPivot for data analysis in Excel,
multidimensional analysis and PowerPivot,
introduction to tools: Power View, Power Query, Power BI,
creating KPI views.

3. Data models

import of data into the model from the active workbook, from other workbooks, from txt files, csv, from databases.

4. Power Query

terminology and user interface,
connecting to various data sources,
data transformations in Power Query,
places of data placement.

5. PowerPivot

loading data into PowerPivot,
refreshing connections,
calculated columns,
calculated fields (measures),
data analysis expressions (DAX),
examples of using the CALCULATE function,
time analysis,
hierarchies and hiding columns.

6. Models using the DAX language

calculated columns, explicit and implicit calculated fields,
calculated measures and model corrections,
dimension and hierarchy elements.

7. Reports obtained thanks to models from PowerPivot

pivot tables and pivot charts,
discussion of standard pivot tables and pivot tables based on PowerPivot,
filters and slicers,
timeline.

8. Export of tables from the PowerPivot model

9. Creating Key Performance Indicators (KPIs)

10. Creating reports in Power View

optimization of data models for use in Power View,
creating a basic report – a table,
presenting a table in matrix form,
visualization in the form of cards,
filtering reports,
creating charts.

11. Introduction to business intelligence (BI) solutions

introduction to BI business intelligence,
principles of data visualization,
data analysis on a map, table with data.

12. Getting data in Query Editor. Working with data

using MS Excel files as a Power BI data source,
using databases as a Power BI data source,
Power BI service.

13. Interactive data visualization

selection of the appropriate chart in Power BI,
interactions,
custom visualizations,
creating dashboards,
creating and publishing reports in Power BI,
managing Power BI solutions.

14. SQL Server and its tools

logic of operation and architecture of MS SQL Server,
database design by example: database design process, schema notations, patterns,
introduction to relational databases: database creation process, normalization – advantages and disadvantages, denormalization – advantages and disadvantages,
environment: MS SQL Server Management Studio – its structure and application,
SQL language: SELECT clause, functions and operators, WHERE clause, ORDER BY clause, TOP Clause,
joining multiple tables – inner and outer joins, grouping rows, aggregate functions,
selecting groups of rows – HAVING clause, subqueries, operations on functions and stored procedures
creating the database structure, creating tables – CREATE TABLE, creating keys, Insert, Update, Delete.

What are the prerequisites for participating in the training?

Basic Excel skills - You should move around worksheets comfortably, edit cells, use simple formulas and save files, so you can focus on analysis instead of learning the basics of the application.

Working with tables and data - You should be able to read tables, recognize rows and columns, and perform simple data operations, because the course develops these skills at a more advanced level.

Basic analytical logic - You should understand relationships between data, filtering and grouping results, so it is easier for you to move into data models, measures, KPI logic and reporting.

Readiness for SQL work - You should be ready to work with technical query syntax and data structures, even if you do not know SQL yet, because the course includes hands-on database operations.