Data Governance & Analytics

Description

Data is everywhere in organisations, but drawing a reliable conclusion from it takes method: asking the right question, checking data quality, choosing the right metrics, avoiding interpretation traps and presenting a result that helps decision-making.

This two-day course is the entry point of the Hélios Academy data pathway. It provides the common foundations for everyone who works with figures, before the more technical courses on SQL, Python, R and Data Factory. Exercises use a spreadsheet and Power BI Desktop.

A common thread: an international trading company

Participants discover the trading company used throughout the data pathway: sales, clients, products and payment delays across some thirty countries. They start from a question asked by sales management, then prepare, analyse and present the data, up to a well-argued recommendation.

Learning objectives

By the end of the course, participants will be able to:

• Turn a business question into an analysis question and measurable metrics
• Identify data types, sources and the main data quality issues
• Prepare and clean a dataset in a spreadsheet and in Power Query
• Describe data with basic statistics: mean, median, spread, trends
• Choose suitable visualisations and build a simple dashboard in Power BI
• Interpret results critically and present them convincingly

Teaching methods and resources

Short presentations alternate with hands-on exercises on the fictitious common-thread data. Each concept is applied to a business question, then discussed as a group. Materials provided: metrics and chart cheat sheet, data quality checklist, exercise dataset and dashboard template.

Course programme

1. Asking the right question

• Start from a decision to inform and frame the analysis question
• Define metrics and their calculation rules
• Tell outcome metrics apart from steering metrics
• Identify the data needed and its sources

Practical work: restating a request from sales management as an analysis plan with metrics.

2. Understanding and preparing data

• Recognise data types: quantitative, qualitative, dates, geographic
• Spot quality issues: missing values, duplicates, inconsistencies, formats
• Clean and transform data with Power Query
• Document sources and the processing applied

Practical work: preparing the common-thread sales file and checking its quality.

3. Describing and analysing

• Calculate mean, median, standard deviation and percentages
• Compare groups and periods, measure changes
• Use pivot tables
• Spot outliers, trends and seasonality

Practical work: analysis of sales and payment delays by country, product range and quarter.

4. Visualising data

• Choose the right chart for the message
• Avoid misleading charts
• Build a simple dashboard in Power BI
• Make a dashboard readable: hierarchy, colours, filters

Practical work: export sales dashboard in Power BI.

5. Interpreting and communicating

• Tell correlation apart from causation
• Recognise biases: sampling, survivorship, confirmation
• Build a key message and support it with figures
• Present a well-argued recommendation

Practical work: presenting the results and recommendation to a simulated sales director, followed by a debrief.

The entry point of the data pathway

After this course, participants who want to go further can take “Data: From Preparation to Visualisation” to master Power BI, “SQL and Data Analysis” to query databases directly, then “Analysing Data with Python” or “with R”, and finally “Analysing Data with Data Factory” to automate the data feeding their analyses.

Description

A useful dashboard relies on a controlled chain: data prepared reliably and reproducibly, a well-built model, correct measures and a layout that guides the reader. When one link is missing, figures differ from one report to another and trust disappears.

This three-day course covers the whole chain with Power BI: data preparation with Power Query, star schema modelling, calculations with the DAX language, report design, then publishing, sharing and refreshing in the Power BI service. It follows on from “Data Analysis Fundamentals” for those who build and maintain dashboards.

A common thread: an international trading company

Participants use the trading company from the data pathway. They combine subsidiary sales files, the product table, exchange rates and sales targets, build the data model, then deliver management’s monthly dashboard, published and refreshed automatically.

Learning objectives

By the end of the course, participants will be able to:

• Import and prepare data from multiple sources with Power Query in a reproducible way
• Build a star schema data model, with relationships and a date table
• Write DAX measures: aggregations, filters with CALCULATE, time comparisons
• Design readable, interactive reports that answer users’ questions
• Publish, share and schedule refresh in the Power BI service
• Secure data access and document the dashboard

Teaching methods and resources

Short presentations alternate with hands-on exercises in Power BI Desktop and the Power BI service, using the fictitious common-thread data. Each module builds on the same dashboard. Materials provided: Power Query and DAX cheat sheet, solved exercise files, report design guide and dashboard documentation template.

Course programme

1. Preparing data with Power Query

• Connect to Excel and CSV files, a folder and a database
• Clean and transform: types, columns, filters, splitting, pivoting
• Combine tables: append and merge
• Make preparation reproducible and parameterised

Practical work: consolidating subsidiary sales files and preparing reference tables.

2. Modelling data

• Understand the star schema: fact and dimension tables
• Create relationships and control their filter direction
• Build a date table
• Organise the model: names, formats, hiding technical columns

Practical work: building the common-thread data model and checking its consistency.

3. Calculating with DAX

• Tell calculated columns apart from measures
• Write aggregations and use CALCULATE to change the filter context
• Compare over time: year-to-date, year-on-year change
• Calculate ratios, shares and variances against targets

Practical work: measures for revenue in euros, margin, variance against target and year-on-year change.

4. Designing effective reports

• Choose visuals according to the message and the user
• Organise the page: hierarchy, alignment, colours
• Add interactivity: slicers, cross-filtering, drill-through, tooltips
• Avoid misleading and cluttered visualisations

Practical work: designing management’s monthly dashboard, reviewed by the other participants.

5. Publishing, sharing and maintaining

• Publish to the Power BI service and organise workspaces
• Share through apps and manage permissions
• Schedule refresh and use a gateway for internal data
• Set up row-level security and document the dashboard

Practical work: publishing the dashboard, configuring refresh and restricting data by country.

Why Power BI

Power BI is one of the most widely used visualisation tools in organisations and its Desktop version is free, so everyone can repeat the exercises. The principles taught, from data preparation to report design, also apply to other tools such as Tableau or Qlik. Helios Advisory is neither a partner nor a reseller of Microsoft.

Description

In a company, the data that supports decisions sits in relational databases: customers, products, orders, invoices, shipments. SQL is the language used to query them directly, without depending on extracts prepared by other teams.

This three-day course teaches participants to write reliable queries, check data quality and produce usable analysis. Exercises run on a fictitious company’s database, using PostgreSQL. The SQL taught follows the language standard and carries over to the databases most common in business, including Oracle and Microsoft SQL Server.

A common thread: an international trading company

Participants work on the database of a company that sells a range of products in some thirty countries: customers, products, orders, invoices and shipments, in several currencies. Module by module, they answer the export sales director’s questions, then build a dashboard of sales and late payments by country.

Learning objectives

By the end of the course, participants will be able to:

• Understand the structure of a relational database: tables, keys and relationships
• Write queries to select, filter, sort and aggregate data
• Combine several tables using joins
• Use subqueries, common table expressions and window functions
• Check data quality and document anomalies
• Prepare datasets and indicators for reporting

Teaching methods and resources

Short presentations alternate with hands-on exercises on a fictitious company’s database installed for the course. Each concept is applied to a business question. Materials provided: SQL cheat sheet, library of annotated queries, data dictionary for the exercise database and a query documentation template.

Course programme

1. Understanding relational databases

• Place tables, columns, primary and foreign keys
• Read a sales data model: customers, products, orders, invoices
• Write first queries: select, filter, sort
• Handle data types: text, numbers, dates

Practical work: exploring the exercise database and extracting the customers in a given country.

2. Combining and aggregating data

• Link tables with inner and outer joins
• Calculate totals, averages and counts
• Group data and filter groups
• Work with periods: months, quarters, years

Practical work: calculating revenue by country, product family and quarter.

3. Writing advanced queries

• Use subqueries and common table expressions
• Create categories with conditional expressions
• Rank, accumulate and compare with window functions
• Make queries readable and reusable

Practical work: monthly change in sales and ranking of the top twenty customers by revenue.

4. Checking data quality

• Detect duplicates, missing values and inconsistencies
• Reconcile two data sources
• Formalise reusable control rules
• Harmonise country codes, currencies and product references

Practical work: running a set of checks on the customer file and product catalogue, and producing an anomaly report.

5. Analysing and reporting

• Build indicators from documented queries
• Prepare datasets for a spreadsheet or a visualisation tool
• Create views to share an analysis
• Respect access rules and personal data protection

Practical work: building a dashboard of sales and late payments by country, presented to the export sales director.

Description

Most of an organisation’s data sits in relational database management systems: customers, orders, invoices, accounts, transactions. Designing and running them well determines application reliability, data quality, security and performance.

This three-day course goes beyond querying data. It teaches how to model a database, create it with its constraints, manage transactions and concurrent access, secure access, back up and restore, and optimise performance. Exercises use PostgreSQL; the principles carry over to Oracle, Microsoft SQL Server and MySQL.

A common thread: an international trading company’s database

Participants use the trading company from the data pathway. Starting from the needs of the sales, logistics and accounting departments, they design the data model, create the database, define access rights, simulate concurrent access, set up backups and optimise the slowest queries.

Learning objectives

By the end of the course, participants will be able to:

• Explain the relational model and the role of a database management system
• Design an entity-relationship model and normalise it
• Create tables, constraints and indexes with the data definition language
• Understand transactions, ACID properties and isolation levels
• Manage users, roles and permissions, then back up and restore a database
• Analyse an execution plan and improve performance

Teaching methods and resources

Short presentations alternate with hands-on exercises in a PostgreSQL environment provided for the course. Each module builds on the common-thread database. Materials provided: data definition and control language cheat sheet, solved data model, exercise scripts and a database operations checklist.

Course programme

1. The relational model and RDBMS

• Understand the principles of the relational model: relations, keys, integrity
• Discover RDBMS architecture: engine, storage, log, cache
• Compare PostgreSQL, Oracle, SQL Server and MySQL
• Place relational databases against NoSQL databases

Practical work: setting up the environment and exploring an existing database.

2. Designing the data model

• Gather requirements and identify entities, attributes and relationships
• Build an entity-relationship model
• Normalise up to third normal form, and know when to denormalise
• Move from the conceptual model to the physical model

Practical work: data model for the trading company’s sales, stock and invoicing.

3. Creating and evolving the database

• Create tables, types and constraints: primary and foreign keys, uniqueness, checks
• Create views, indexes and sequences
• Write simple procedures, functions and triggers
• Evolve the schema without losing data

Practical work: creating the common-thread database and loading test data.

4. Transactions, concurrency and security

• Understand transactions and ACID properties
• Master isolation levels, locks and deadlocks
• Manage users, roles and permissions on the least-privilege principle
• Protect sensitive data

Practical work: simulating concurrent access to an order and defining permissions by department.

5. Operations and performance

• Back up and restore a database, understand transaction logs
• Monitor activity and space used
• Read an execution plan and choose the right indexes
• Optimise the most expensive queries

Practical work: backup plan, restore after a simulated incident and optimisation of three slow queries.

The natural next step after the SQL course

The “SQL and Data Analysis” course teaches how to query data; this one teaches how to design, administer and optimise the database that holds it. It prepares for design and operations responsibilities, without targeting a vendor certification.

Description

Spreadsheet analyses quickly reach their limits: files that are too large, the same copy-paste every month, formulas that are hard to check. Python, with the pandas library, handles large volumes of data, documents every step and reruns an analysis in one click.

This three-day course is designed for programming beginners. Participants work in Jupyter notebooks on the data of a fictitious company. They learn to import and clean data, analyse it, produce charts and automate a recurring report.

A common thread: an international trading company

Participants use the data of the trading company from the SQL course: clients, products, orders, invoices and deliveries across some thirty countries, in several currencies. They answer questions from the export sales director and the finance department, then automate the monthly sales and late payments report.

Learning objectives

By the end of the course, participants will be able to:

• Write and run simple Python programs in a Jupyter notebook
• Import data from Excel and CSV files and from a SQL database
• Clean and prepare data: types, missing values, duplicates, currencies and dates
• Analyse data with pandas: filters, aggregations, joins, pivot tables
• Produce clear charts with Matplotlib and seaborn
• Automate a recurring analysis and export the results to Excel

Teaching methods and resources

Short presentations alternate with hands-on exercises in a ready-to-use Python environment provided for the course. Each concept is applied to a business question from the common thread. Materials provided: Python and pandas cheat sheet, solved notebooks, exercise dataset and a reusable automated report template.

Course programme

1. Getting started with Python

• Discover the environment: Jupyter, cells, execution
• Work with basic types: numbers, text, lists, dictionaries
• Write conditions, loops and simple functions
• Read an error message and fix your code

Practical work: first calculations on the common-thread sales and writing a currency conversion function.

2. Importing and preparing data

• Load Excel and CSV files into a pandas DataFrame
• Connect to a database and run a SQL query
• Handle types, dates, missing values and duplicates
• Check data quality and document corrections

Practical work: importing orders and invoices, consistency checks and building a clean dataset.

3. Analysing with pandas

• Filter, sort and select data
• Aggregate by client, product, country or period
• Join several tables and build pivot tables
• Calculate metrics: revenue, margin, payment delays, trends

Practical work: analysis of sales by country and product range, then identification of clients paying late.

4. Visualising results

• Choose the right chart for the message
• Plot lines, histograms and bar charts with Matplotlib and seaborn
• Polish titles, axes and annotations
• Spot trends, seasonality and outliers

Practical work: chart dashboard of export sales presented to the sales director.

5. Automating and sharing

• Turn a notebook into a reusable script
• Parameterise an analysis: period, country, currency
• Export formatted results to Excel
• Good practice: code organisation, comments, version control

Practical work: automating the monthly sales and late payments report, rerun on a new month of data.

Python or R?

Both languages can be used to analyse data. Python is a general-purpose language, widely used for automation and integration with information systems. R is designed for statistics and visualisation. Helios Advisory offers both courses; the choice depends on the intended uses and the tools already in place in the organisation.

Description

R is a language designed for statistical analysis and visualisation. Combined with the tidyverse, a consistent set of libraries, it lets you prepare data, produce high-quality charts and test hypotheses rigorously, while keeping a full record of every step.

This three-day course is designed for programming beginners. Participants work in RStudio on the data of a fictitious company. They learn to import and transform data, visualise it with ggplot2, carry out simple statistical analyses and produce a reproducible report with Quarto.

A common thread: an international trading company

Participants use the data of the trading company from the SQL course: clients, products, orders, invoices and deliveries across some thirty countries. They investigate what explains margin differences between countries and product ranges, test the effect of a sales campaign and deliver a study report to management.

Learning objectives

By the end of the course, participants will be able to:

• Write and run R scripts in RStudio
• Import data from Excel, CSV files and a SQL database
• Transform and prepare data with dplyr and tidyr
• Produce polished charts with ggplot2
• Carry out statistical analyses: comparison tests, correlation, linear regression
• Write a reproducible report with Quarto, from code to final document

Teaching methods and resources

Short presentations alternate with hands-on exercises in a ready-to-use R and RStudio environment provided for the course. Each concept is applied to a business question from the common thread. Materials provided: R and tidyverse cheat sheet, solved scripts, exercise dataset and a reusable Quarto report template.

Course programme

1. Getting started with R and RStudio

• Discover the RStudio interface: console, scripts, environment
• Work with basic objects: vectors, factors, data frames
• Install and load packages
• Organise work in projects

Practical work: first calculations on the common-thread sales and creating an RStudio project.

2. Importing and transforming data

• Import Excel and CSV files, query a SQL database
• Select, filter, sort and create variables with dplyr
• Aggregate, join tables and reshape with tidyr
• Handle dates, missing values and duplicates

Practical work: building an analysis table of margins by country, product range and quarter.

3. Visualising with ggplot2

• Understand the grammar of graphics
• Build scatter plots, histograms, box plots and line charts
• Compare groups with facets
• Polish themes, scales and annotations

Practical work: a series of charts on margin differences, discussed with the group.

4. Statistical analysis

• Describe a distribution and spot outliers
• Compare groups: Student’s t-test, chi-squared test
• Measure a correlation and fit a linear regression
• Interpret results without over-interpreting

Practical work: testing the effect of a sales campaign on sales and building an explanatory model of margin.

5. Producing a reproducible report

• Structure a Quarto document: text, code and results
• Render an HTML, Word or PDF report
• Parameterise the report to refresh it with new data
• Good practice: readable code, comments, version control

Practical work: complete study report delivered to management, regenerated on a new quarter of data.

R or Python?

Both languages can be used to analyse data. R is designed for statistics and visualisation and remains widely used in studies and research. Python is a general-purpose language, widely used for automation and integration with information systems. Helios Advisory offers both courses; the choice depends on the intended uses and the tools already in place in the organisation.

Description

An analysis is only as reliable as the data behind it: complete, consistent and up to date. Yet this data is often scattered across a business management system, files sent by subsidiaries and online services. Azure Data Factory, Microsoft’s data integration service, collects, transforms and loads it automatically into a database or data lake, ready for analysis.

This three-day course teaches how to design data pipelines without writing code, using the service’s visual interface. Participants build a complete chain, from connecting to sources to a Power BI dashboard, then learn to automate, monitor and secure it.

A common thread: an international trading company

Participants use the trading company from the SQL, Python and R courses. Its sales are recorded in an on-premises business system, its subsidiaries send monthly files and exchange rates come from an online service. Participants build the chain that consolidates this data every night and feeds management’s sales dashboard.

Learning objectives

By the end of the course, participants will be able to:

• Explain the architecture of a cloud data integration chain and where Data Factory fits
• Connect a range of sources: internal databases, files, online services
• Copy and transform data with the copy activity and data flows
• Orchestrate and automate parameterised pipelines, with incremental loading and triggers
• Monitor, secure and control the cost of processing
• Make data available to Power BI for analysis

Teaching methods and resources

Short presentations alternate with hands-on work in an Azure training environment, using fictitious data. Each module ends with a step in the common-thread chain. Materials provided: step-by-step guide, reference architecture diagram, reusable pipeline templates and an operations good-practice checklist.

Course programme

1. Understanding cloud data integration

• Distinguish ETL and ELT, data lakes and data warehouses
• Place Azure Data Factory and Data Factory in Microsoft Fabric
• Discover the components: pipelines, activities, linked services, datasets, integration runtimes
• Find your way around Data Factory Studio

Practical work: creating the common-thread data factory and mapping the sources to integrate.

2. Connecting and copying data

• Create linked services to a SQL database, file storage and an API
• Reach internal sources with a self-hosted integration runtime
• Configure the copy activity and choose the format: CSV, JSON, Parquet
• Organise data lake zones: raw, prepared, curated

Practical work: ingesting sales from the business system, subsidiary files and exchange rates.

3. Transforming data

• Design data flows: joins, aggregations, derived columns, filters
• Check quality: missing values, duplicates, rejected rows
• Choose between data flows and SQL processing in the database
• Document the business rules applied

Practical work: a data flow that calculates revenue in euros and margin by country and product range.

4. Orchestrating and automating

• Use parameters, variables and expressions
• Chain activities with conditions, loops and dependencies
• Set up incremental loading
• Schedule runs with schedule, tumbling window or event triggers

Practical work: main pipeline run every night, with incremental loading of new sales.

5. Monitoring, securing and analysing

• Track runs, diagnose errors and create alerts
• Protect access: managed identities, Azure Key Vault
• Version pipelines with Git and deploy them across environments
• Control costs and connect Power BI to the prepared data

Practical work: Power BI sales dashboard fed by the chain, followed by an operations review of a simulated incident.

Azure Data Factory and Data Factory in Microsoft Fabric

Microsoft offers Data Factory in two environments: the Azure Data Factory service and Data Factory built into Microsoft Fabric. The design principles are largely shared. The course is based on Azure Data Factory and sets aside time to transfer the practices to Fabric. Helios Advisory is neither a partner nor a reseller of Microsoft.