Big Data analysis

Programme

Structured data collection and processing using various tools (SQL, Excel, Power BI)

Data processing in EXCEL

1. Working with formulas and functions
— The logic of building formulas for quickly collecting and comparing information

2. Main formulas for data processing
— AGGREGATE, SUBTOTAL, SUMIF, SUMIFS, COUNT, COUNTA, COUNTIF, COUNTIFS, LEFT, RIGHT
— Practical examples of using text functions

3. Date and time functions
—  Specifics of how Excel handles dates and times
—  Calculating calendar dates without functions
—  NOW, TODAY, YEAR, EDATE, WEEKDAY, WEEKNUM, NETWORKDAYS, WORKDAY, WORKDAY.INTL

4. Logical functions and expressions
—  Logical operators in Excel, logical values
—  IF, AND, OR, IFERROR, IFS

5. Lookup and array functions
— MATCH, INDEX, FILTER

6. Data validation functions
—  Creating drop-down lists
—  Using references to cells on other sheets and in other workbooks in formulas
—  Using references effectively
—  Creating a formula that uses values from another sheet or workbook
—  Syntax of a reference to a cell on another sheet in the same workbook
—  Syntax of a reference to a cell in another workbook
— Working with external links
— Changing the values of formulas with external links. Checking link status

7. Working with tables
— Working with the Table object
— General provisions
— Creating a table
— Using the Quick Analysis tool to create tables
—  Table capabilities
—  Filter drop-down lists
—  Using a slicer to filter data
—  The insert row
—  The total row
—  Editing a table
—  Creating a calculated table column

8. Visual data analysis
— Conditional Formatting
—  The concept of conditional formatting
— The general approach to creating conditional formatting
— Types of conditional formatting rules
—  Using a formula as a formatting criterion
— The Conditional Formatting Rules Manager
—  Processing priority of conditional formatting rules
—  Conditional and «unconditional» formatting

Data processing in Power BI

1. Working with sources

2. Working with Power Query
— Overview of the functional language M on which queries are processed in the Power Query Editor
— Preparing data from tables. Removing unnecessary columns, advanced data filtering, replacing values, splitting columns by delimiter. Adding conditional columns (columns whose values depend on other columns). Various ways of combining several tables: appending several similar tables, adding missing data from other tables (the analogue of VLOOKUP in Excel). Setting the data format. Using Unpivot

3. The data model
— Introduction to relational database theory. Types of tables. Types of relationships between tables (One to Many, Many to Many). Main varieties of relational database
— Building the data model. Detailed study of setting up relationships. Configuring the data model. Formatting data

4. Creating model calculations using DAX
— Overview of the functional language DAX. Creating simple measures with DAX functions (SUM, AVERAGE, COUNT, DISTINCTCOUNT, What IF). Adding calculated columns. Creating calculated tables. Creating the calendar needed for the Time Intelligence functions to work (PREVIOUSMONTH, PREVIOUSQUARTER, PREVIOUSYEAR, SAMEPERIODLASTYEAR, DATEYTD, STARTOFMONTH, ENDOFMONTH and many others): based on the CALENDARAUTO and CALENDAR functions (for finer calendar configuration)
— Using measures and calculated columns in analytical reports. Recommendations on using measures. Building complex DAX formulas using variables (CALCULATE, FILTER, SUMX, AVERAGEX, ALL, ALLEXCEPT, SWITCH, etc.)

5. Defining and implementing appropriate visualisations
— Overview of the various types of visualisations in Power BI: table, matrix, line chart, bar chart, pie chart, etc. Adding visualisations from the Power BI app store. Slicers

6. Processing information and building a DASHBOARD in practice for rapid management decision-making

7. Theory of connecting to databases

8. Creating structured queries for database analysis

Data processing in SQL

1. The concept of a relational DBMS
— Popular services for working with SQL
— Types of SQL queries
— Structure of SQL queries
— Principles of creating a database
— The SQL query language

2. Working with tables and databases in SQL
— Creating and editing tables. Data types
— Databases. Data types. Aggregate functions
— Grouping data. Aggregate formulas
— Working with data — filtering, selecting, editing, etc.
— Reading data — populating table rows
— The processing form. The «Connection, service» tab

3. SQL query syntax
— The concept of a query and analysis of its composition
— SQL queries (ALTER TABLE, DROP TABLE, DELETE, UPDATE, SELECT, INSERT, CREATE TABLE, SELECT ALL/DISTINCT, FROM, WHERE, GROUP BY, HAVING, LIKE, BETWEEN, IN, NOT IN, sum, min, max, count, etc.)

4. Single-row functions
— Types of functions and working with the database (LOWER, UPPER, INITCAP, CONCAT, LENGTH, LPAD and RPAD, TRIM)
— Introduction to CONVERSION functions

JOIN functions