Objective
You work with diverse data sets on a daily basis. You usually use a spreadsheet, enter or paste data, create formulas, do some visual formatting and send it on. Certain actions are frequently repeated, which means that you use macros and VBA. You are a proficient and experienced user of Microsoft Excel. However, you know that you need something more.
The amount of data increases more and more, your co-workers need the reports and analysis faster and faster, simple charts and tables are not enough anymore, you have less and less time to do the same things. You need new quality; you need better tools. You need the knowledge and practical solutions that you can find in a Business analytics study programmes.
You will learn the tools that will allow you to work effectively and efficiently with large data sets (hundreds of millions of rows are not yet considered as big data, but it is something that you can already encounter in your organisation today). You will learn how to design a multidimensional model that you will implement in the good old Excel (but on a columnar, RAM-based analytics engine) or on a modern Power BI/Analysis Services platform. You will learn to create advanced calculations in the DAX language. You will learn to create captivating visualisations that will help you tell the story behind your data. You will be able to use the R language and machine learning algorithms to extract the relationships deeply hidden in the thicket of numbers. You will publish the results of your work as an attractive, interactive report accessible online on mobile devices.
The topics covered in this programme will allow you to familiarise yourself with a range of modern tools for capturing, storing, processing, and sharing data in various forms.
During the course of these studies, the students will complete projects that enable them to use the tools and the functionalities that they learn in class. The tasks will be based on real data sets available online and on public data resources (Central Statistical Office of Poland, ministries and state agencies).
The classes are taught by experienced practitioners who have completed hundreds of projects for many organisations and who have encountered thousands of challenges, large and small, in the process. They have lived through dozens of failures in the process, but remained undeterred by the setbacks and persistently pursued their goals. They will tell you about many potential troubles, so that you can avoid them.
Who is the programme addressed to?
- People who deal with data analysis on a daily basis, preparing data for reporting and decision-making purposes.
- Financial controllers, analysis and accounting departments employees.
- Administrators of servers, services, databases who want to quickly and efficiently analyse records of hardware and software operation (logs).
- Developers of analytics and reporting solutions on Microsoft Office 365, Power BI or SQL Server platforms.
- Individuals wishing to acquire highly sought-after competences of data engineer.
Prerequisite requirements
- Being able to operate a computer and Windows operating system at a level that allows you to freely use the file system and the Internet.
- Willingness to learn new tools and technologies.
- Free time and motivation – there will be quite a few tasks to do on your own
Class topics
- Building analytical models
60 hours
Working with larger data sets requires the use of appropriate storage and processing software. On the other hand, the analysis requires preparing a special database to be used in data mining projects. Which form of data storage should I choose? Which tool should I choose to define the calculation? How to make the model I am building available to analytical applications and other Users?
In this course, you will learn about the principles of design and building of multidimensional models. You will build sample implementations in SQL database and using dedicated analytics server used in Excel, Power BI and Analysis Services. You will learn two languages used for working with a model: SQL and DAX. You will work with small sets of tens of thousands of rows, but you will also be challenged by a larger collection of over 120 million rows.
- The organisation and flow of the data analysis process. CRISP-DM methodology
- Forms of data storage – advantages, disadvantages
- Relational databases. SQL Language. Designing analytical databases using SQL. Analytical functions in SQL. Creating stored procedures and functions. Automating the activities using the SQL Server Agent service.
- Creating multidimensional analytical models in Excel, Power BI, Analysis Services Tabular. Forms of relationships between tables. Filter propagation. Hierarchies. The context of performing calculations.
- Creating calculations and queries in DAX language. Measures. Calculated Columns. Calculated Tables. Modifying the calculation context.
- Implementation of typical business situations: budgeting, planning, monitoring the plan execution, analysing inventory and balance sheet data. Time dimensions (YTD, MTD, year-over-year, same period last year, etc.)
- Loading and Transforming Data
30 hours
In the case of most trainings and courses, the source data is ready to use. All you have to do is issue a command to load the data and you can already create advanced analytical reports. It is not the case with us. Here, you will face real-life, natural datasets. Dodgy formats, missing data, unknown codes, incomplete sets, different layouts and code pages – these are everyday life issues of an analyst who first has to integrate data from different sources.
You will learn two tools used in the Microsoft platform to take data from different sources and transform it comprehensively. Power Query is built into Excel, Power BI and Analysis Services Tabular. This tool has recently gained a large group of supporters – it is simple, yet powerful. It is equipped with an equally broad range of Integration Services features and allows you to create automated processes for loading data into models.
- Data quality issues.
- Power Query. Data sources. Data transformations. Query Parameterisation. Advanced Features.
- SQL Server Integration Services. Creating and automating analytical model feed processes.
- Data visualization and reporting
50 hours
The complete analytical model constitutes a source of data visualisation. They allow you to show correlations, draw user’s attention to certain things. An extremely important feature of these tools that you will use is the possibility of creating interactive presentations that tell a story that you have drafted. Each tool has its own special features, functionalities that determine how the information is shown. You have a wide range of possibilities, but do you know how to you them? You will learn what are the characteristics of a good visualisation and how to avoid problems when creating a presentation.
- The use of pivot tables and pivot charts in Excel. Slicers. OLAP functions in Excel.
- Data visualization in Power BI
- Creating and publishing reports using SQL Server Reporting Services
- Data analysis using R language
20 hours
The R language allows you to greatly expand the capabilities of your analytical tools. R is not just another typical programming language. It was created by statisticians for statisticians, hence many complex data operations or complex visualisations that are created by single instructions of this language. The availability of a large number of libraries and extensions makes it extremely versatile. The vast capabilities of Excel or Power BI re still limited. R will allow you to surpass those limits.
- Basic constructs of R language. Used data types: vectors, lists, matrices, arrays, factors, data frames. Activity vectorisation.
- Obtaining data from various sources: databases, text and Excel files, websites.
- Foundations of descriptive statistics using the R language.
- Transforming data using the dplyr library.
- Data visualization – ggplot2 library.
- Publication and presentation of analysis results: knitr and Shiny.
- The use of R language in Excel (BIRT), Power BI, SQL Server.
- Using machine learning models in data analysis
20 hours
Machine learning will allow you to uncover relationships and rules governing your dataset that are buried somewhere deep. Do you want to know if the hypothesis about the correlation of given variables is true and which product or customer characteristics have the greatest impact on sales volume? Do you want to fill in the missing data based on other similar cases? Do you want to eliminate atypical observations distorting the bigger picture? These are the applications of machine learning algorithms. The model created based on your database will allow you to predict future values of the variables that you are interested in.
We’re not going to create complex neural networks that tell you whether the customer in the photo is smiling or not. We will not build a model that automatically drives your car. However, we will use very similar algorithms to solve less complex problems within your data – forecasting sales, costs, dependence of sales on the amount of marketing expenditures, forecasting price formation and so on.
- Introduction to machine learning. Variable selection and reduction. Data transformations required by machine learning procedures.
- Creating machine learning models. Selection of an algorithm and its parameters. Training. Model validation. Prediction using a model.
- Classification problems. Use of decision trees, support-vector machines, neural networks and other classifiers.
- Clustering algorithms.
Organisational information
The tuition fee is 6000 PLN including a reservation fee of 100 PLN.
Bank account number: 61 1440 1127 0000 0000 0193 3148
On-campus sessions: Saturday –Sunday (including sessions in July and September, depending on the organisation of the academic year)