Data Analytics Ամբողջական Դասընթաց

• Course Introduction
• Data Analytics in Different Sectors

Calculation and Tables, Home Tab

Page Layout, Review, View

Most frequently used functions

• Spreadsheet analytical techniques: Overview
• The Fill Handle & Autocorrect
• Subtotals
• Data Validation
• Conditional Formatting

• Filtering Tools and Removing Duplicates
• Consolidating Data
• Circular References: Iterations

• What-If Controls: The Goal Seek Command & Solver: Introduction
• What-If Controls: One & Two-Dimensional Data Tables
• What-If Controls: Using the Scenario Manager for Sensitivity Analysis

• Pivot Table & Pivot Chart
• Un-pivot Data, Slicers & Timelines
• Calculated Field, Grouping & External connections (i.e. SQL Statements)
• Charts & Graphics: Plotting Data
• Combining Chart Types: Falling ball speed

• Descriptive Statistics, working with Trends
• Measures of Central Tendency, Asymmetry, and Variability
• Correlation & Predictability

• Regression and Curve Fitting
• Multiple Regression: Linear Estimates
• Nonlinear Regression, Dealing with Categorical Data
• Curve Fitting: Sigmoid Curves
• Confidence Intervals & Hypothesis Testing

• Linear algebra: Reiterations and Matrixes
• Linear algebra: Solving Equations
• Types of Distributions: Sampling distribution
• Sampling Techniques
• Decision trees: Option Pricing
• Monte Carlo Simulations

• Formulas for Financial Applications and Modeling: Profitability Analysis
• Time Series Analysis and Forecasting
• Marketing strategy analysis: Demand curve & Pricing
• Solving LP Problems in Excel with Solver for business modeling (with sensitivity analysis)

• Value at Risk: Non Parametric (Historical) VaR
• Variance-Covariance, Risk modeling
• Probabilities in action: Calculating an Optimal Bid
• The Second Exam

• Relational Databases and SQL  
• Numeric  
• Character  
• Boolean  
• Date time 

• The SELECT Query 
• Basic Anatomy and Working of a SELECT Query 
• Basic Keywords in a SELECT Query
• Filtering, Ordering, Alias
• Views and Index
• String Functions
• Mathematical Functions
• Date – Time Functions

Creating Tables 

• Creating Blank Tables  
• Creating Tables with SELECT

• The COPY Command  
• Import data from Excel
• Exporting Data to a File for Further Processing in Excel

Updating Tables  

• Adding and Removing Columns 
• Adding New Data  
• Updating Existing Rows  

Deleting Data and Tables 

• Deleting Values from a Row  
• Deleting Rows from a Table  
• Deleting Tables 

• Types of Joins  
• Subqueries  
• Unions  
• Using the CASE WHEN Function

• Aggregate Functions  
• Aggregate Functions with GROUP BY  
• GROUP BY  
• Multiple Column GROUP BY  
• The HAVING Clause 

• Window Functions  
• The Basics of Window Functions  
• Statistics with Window Functions

• Introduction
• Downloading Power BI & adjusting settings
• Exploring the Power BI Desktop interface & workflow
• Helpful Power BI resources
  Connecting and Shaping Data with Power BI Desktop
• Connect to data sources
• Types of data connectors in Power BI Desktop
• Change the data source settings
• The Power BI Query editor (aka Power Query)
• Basic table transformations in Power BI
• Working with text tools
• Working with numerical values
• Working with date & time tools

• Shape and transform tables
• User-friendly naming conventions
• Resolving inconsistencies & nulls
• Evaluate and transform column data types
• Append queries
• Merge queries and expand table
• Merge queries with group by, and different types of joins

• Pivot & Unpivot
• Conditional columns
• Columns from examples
• Transpose
• Data profiling
• Resolve data import errors
• Files from folders
• Data loading with parameters

• What is a “Data Model”?
• Principles of data normalization
• Understanding data tables vs. lookup tables
• Creating table relationships in Power BI Desktop
• Design a data model that uses a “Star” schema
• “”Snowflake”” schemas in Power BI
• Managing & editing table relationships in Power BI Desktop
• Managing active vs. inactive relationships
• Understanding relationship cardinality
• Understanding filter flow
• Two-way filters in Power BI Desktop (USE WITH CAUTION!)
• Hiding fields from the Power BI report view
• Table and column properties
• Common date table (using DAX and Power Query)
• Hierarchies and data granularity
• Role playing dimensions
• Power BI data model best practices

• Intro to Data Analysis Expressions (DAX)
• Intro to DAX calculated columns
• Basic operators
• IF, BLANK and ISBLANK
• AND, OR and NOT
• SWITCH
• Measures -an introduction, with standard aggregations including COUNTBLANK
• Aggregation of calculations
• Other statistical functions
• Rounding functions
• Division functions – MOD and QUOTIENT
• SIGN (and use with SWITCH) and ABS
• COUNT functions (COUNTA, DISTINCTCOUNT, COUNTROWS), exponential function
• Text searching
• Text extraction and substitution
• Text conversion
• Other text functions

• ISERROR and LOOKUPVALUE
• Other information functions
• RELATED – Flatten out a parent-child hierarchy
• RELATEDTABLE and COUNTROWS
• ALL, FILTER, CALCULATE, ALLEXCEPT, ALLSELECTED
• Date and time functions, FIRSTDATE, LASTDATE
• Start of… and End of…, Previous… and Next…
• DATESINPERIOD, DATESMTD, DATESQTD, DATESYTD, TOTALMTD, TOTALQTD, TOTALYTD
• Opening Balance and Closing Balance
• Semi-additive measures
• SAMEPERIODLASTYEAR and PARALLELPERIOD
• Other Time Intelligence Functions
• Quick measures

• Exploring the “Report” view in Power BI Desktop
• Table and matrix
• Column charts, line charts and area charts
• Combo charts and treemaps
• Card, multi-row card, and gauge visual
• Pie chart, donut chart and funnel
• Scatter chart, waterfall chart and maps
• Customize and configure visuals
• Custom tool tips
• Configure report page
• Conditional formatting
• Applying slicing and filtering
• Sorting
• Synch slicers
• Using themes
• Bookmarks
• Page navigation
• Edit interactions
• Analyze feature, Identify outliers
• Create groups, bins and clusters
• Analytics pane, Forecast feature

• Logging into Power BI Service and a quick look around
• Power BI terminology
• Datasets and Reports in Power BI Service
• Publish reports and export data
• Create a workplace
• Manage tiles in a dashboard
• Analyze in Excel feature

Introduction to Tableau. Understanding Tableau Concepts

• Connect to data sources
• Prepare data for analysis
• Customize fields
• Understand dimensions and measures
• Understand discrete and continuous fields
• Understand aggregations
• Create basic charts

• Create and use filters
• Create parameters to enable interactivity
• Create calculated fields
• Create table calculations
• Structure the data
• Map data geographically
• Create dashboards and stories
• Summarize, model, and customize data by using the Analytics feature

• Intro to programming, Python Interpreters
• Syntax
• Variables & Data Types (Strings, Numbers)
• Getting Input from Users

• If Statement
• Lists, Tuples, Sets, Dictionaries

• Loops
• Functions, Lambda

• Error handling (Try / Except)
• Reading and Writing Files
• Object oriented programming (Classes & Objects, Inheritance) Part1

Built-in packages (random, os, request, math)

• NumPy Arrays & Array Properties
• Array Creation
• Random Number Generation
• Indexing & Slicing Arrays
• Array Operations
• Filtering Arrays & Modifying Array Values
• The Where Function

• Array Aggregation
• Array Functions
• Sorting Arrays
• Vectorization
• Broadcasting

• Data frames, reading data from csv, html, json
• Filtering, and sorting
• Adding/removing columns

• Updating Rows and Columns
• Conditional changes
• Join, merge, append

• Indexing
• Aggregation
• Cleaning data
• Casting datatypes
• Working with datetime

• Line plot
• Bar chart
• Pie chart

• Histogram
• Time series
• Subplots