About course

Information provided by the training provider

Abstract

This program is designed for novice data analysts who do not have any experience in data management.The material contains information from the beginning of data analysis to the creation of automated reports.The training introduces a lot of advanced data analytics information, challenges, and examples using SQL data queries and the Power BI tool to visualize data.Here, not only a general understanding of where data analysis begins is gained, but also real solutions for data analytics are developed.The requirements of employers and the real tasks set for data analysts in the recruitment selections are introduced and demonstrated lively.

Important information

Way of learning

Place

Language

Aukštos pridėtinės vertės programa

Yes

Minimum requirements for the participant

Education

Vidurinis išsilavinimas

Acquired and improved competencies

Ordinary:

Mathematical competence and competence in science, technology and engineering

Digital competence

Professional competencies:

Program server data requests.

Automate data rendering graphs and visualizations

Design typical relational and non-linear (NoSQL) databases

Content of the learning program

Topic name	Brief description of the topic
Topic name Create structured data repositories	Brief description of the topic • Creating a MySQL server: • Installing a MySQL server on a computer; • CREATING SQL Server users; • Setting up a convenient connection to SQL Server; • Setting consumer protection parameters; • Familiarization with the mySQL server infrastructure: • Using MySQL Workbench; • Database structure on a MySQL server; • Principles of creating new databases on MySQL servery; • Differences between active and inactive databases[ • Importing and viewing database models on a MySQL server; • Creating and updating tables on a MySQL server without using code; • Introduction to viewsViews) and their conception; • Introduction to MySQL procedures; • Introduction to MySQL functions; • Uploading data to a MySQL server: • Uploading data tables to a MySQL server; • Filling data tables on a MySQL server; • Creating a data model on a MySQL server.
Topic name Create interactive features for an automated report for data analysis	Brief description of the topic • Uploading graphs to the report; • Using slicer-type visualizations for convenient filtering; • Using card-type graphs to display KPI indicators; • Use of map-type graphs to display data on the map; • Using matrix graphs to display data in a table, on the principle of Excel Pivot; • Topics of reports; • Reporting templates; • Create individual pages in the report.Subject.Report visualization navigation: • Transition from one graph level to anotherDrill Down/Up); • Opening the visualization of the report in a separate window Focus Mode); • Visualization check who filters the data; • Sorting visualization elements by the specified column; • Sort a table column by another column in the same table; • Options for disabling the interactivity of visualizations; • Role creation in RLSRow Level Security) in a Power BI Desktop environment by using formulas; • Hide tables, columns, and formulas.
Topic name Create data queries	Brief description of the topic • Basic sql server data types; • Data formats; • SQL queries: • SQL code syntax; • STRUCTURE OF SQL queries; • Selection of columns with the help of SELECT queries; • Selection of tables using FROM; • Renaming columns and tables using AS; • Sorting the query result using ORDER BY; • Data aggregation using SQL queries; • SQL functions: • Standard functionsbuilt-in) SQL functions; • Text transformation; • Management of numeric data using SQL functions; • Arithmetic operators of SQL queries; • Comparative operators of SQL queries; • Data filtering using WHERE syntax; • Multifunctional filtering using logical operators; • SQL syntax for data grouping; • Changing if logic to a more convenient CASE syntax; Combining tables: • Principles of data queries using multiple tables; • Vertical connection of tables using UNION syntax; • Horizontal connection of tables using the syntax JOIN.
Topic name Creating mathematical data management models with the help of formulas	Brief description of the topic • Power BI table relationship modeling capabilities; • Manual creation of table relationships; • Creating layouts for table relationships; • Types of table relationships; • Active / inactive table relationships, their differences and principles of use; • Directions of cross-filtering, their differences and principles of use; • Hiding tables and columns; • Columns and FormulasMeasures) grouping into folders; • Formulas Measures) table creation; • Setting the main date table; • DAXData Analysis Expressions) formula writing: • DAX syntax; • Commenting on DAX formulas; • Simple aggregation formulas SUM, COUNT, AVERAGE, MIN, MAX; • Specific DAX division using DIVIDE; • Filtering tables using FILTER syntax; • Application of artificial intelligence integrations in Power BI Desktop: • Application of data forecasting function; • Application of the cognitive function of speech recognition; • Application of the cognitive function of extracting basic phrases; • Azure Machine Learning functionality.
Topic name Merging and preparing different data formats for analysis	Brief description of the topic • Power BI Desktop Components: • Power BI Desktop Configuration; • Features of data visualization; • Possibilities of creating relationships between tables; • Connecting to data with Power BI Desktop: • Variants of standard connection to different data sources; • Reading data from WEB pagesWeb Scraping); • Reading data from PDF files; • Connection methods to sql server data; • Connection to mySQL server data; • Importing data into Power BI in "Import" mode; • KPI for measurement indicatorsKey Performance Indicators) identification; • Preparation of SQL queries; • Connection to the MySQL database; • Application of ready-made SQL queries; • Storage of the data model in .pbix format; • Meta data concept; • Publishing a Power BI Desktop report to the Power BI Service; • ETAExtract Transform Load) concept; • ETL realization with Power Query; • Writing Power Query formulas using M-Language.
Topic name Understanding data infrastructure	Brief description of the topic Data transformation: • Automatic data collection from data sources; • ETA Process (ETA Process)Extract Transform Load); • Data cleaning and preparation for analysis; • Database structures: • Structured data tables; • Representation of relational data tables; • SQL Server ViewsViews); • Using SQL data queries to extract data from the server; • Data aggregation: • Data modeling using SQL queries; • SQL functions; • Application of mathematical models using SQL code; • Creating new tables and columns by pulling data from the server; • Table relationships: • Types of relational table relationships; •Advantages and disadvantages of a one-to-one table relationship; • Advantages and disadvantages of the "one-to-many" table connection; • Advantages and disadvantages of the "many-to-many" table connection; • Types of table connection: • Table keys: • Principles of combining structural data; • Primary keys for connecting structural tables; • Foreign keys.
Topic name Create and update structured databases	Brief description of the topic • SQL query syntax C.R.U.D.Create Read Update Delete) concept; • Automatic creation of databases using SQL code; • Automatic creation of tables with different data types using SQL code; • Creating tables with keys from several columns; • Creation of temporary tables on SQL servers; • Features of temporary tables and practice of use; • ViewsView) creation using SQL code; • Principles and limitations of deleting data on SQL servers; • Deleting columns in relational tables using SQL code; • Deleting relational tables using SQL code; • Restrictions on deleting relational tables when using relationships; • Deleting SQL Server databases using SQL code; • Automation of data recording to SQL Server using INSERT INTO syntax; • Automatic data refresh in SQL Server using update set where syntax; • Data refresh restrictions on SQL Servers; • Editing columns in a database table using ALTER TABLE.
Topic name Identify data sources	Brief description of the topic Data sources: • SQL databases; • Excel files; • CSV files; • JSON files; • XML files; • Text files.• Descriptive analysis; • Diagnostic analysis; • Predictive analysis; • Prescriptive analysis.• Structured data; • Unstructured data; • Data flows; • Big data and its formats; • Cloud data technologies; • On-premises servers; • SQL Servers: • Relational data tables; • Automatic data update; • NoSQL technologies; • Data warehousesDate Warehouse); • Data Lakes Date Lake); • Artificial intelligence services; • Data modeling; • Data cleaning; • Data integration.
Topic name Create automatic data mapping graphs and visualizations	Brief description of the topic • Business Intelligence Business Intelligence concept:• Data processing stages; • Data sorting; • Data aggregation using automatic tools; • Features of interactive display of data; • Creating a "narrative" of data; • Overview of the MS Power Platform system; • Common tools that make up the system – Dataverse, data connections and AI integrations; • Power BI business analysis tool in the context of other tools; • Types of data tables: • WidthWide) type of data tables; • Length (SSA) (Long) type data tables; • Power BI updates, frequency and scope of changes; • Advantages and disadvantages of Power BI compared to competitors; • Power BI architecture components: • Features of connecting to data in local sources; • Data sources; • User's environment; • Big Data (BIG DATA)BIG DATA) management architectures.• Power BI predecessor technology for OLAP cubes; • Advanced data processing architectures; • Data processing with Azure Synapse.