DATA ANALYTICS - P1

Module 1:- Getting Started with Excel

• Introduction to Excel 2013/2016/2019/Office 365
• Application Interface and Key Components of Excel
• Navigating Through Excel Ribbon Tabs
• Exploring Important Excel Options*
• Live Session Exercise
• Splitting data of Single Column into multiple
• 10 Examples to use Auto-fill and Flash Fill
• Magic of Go-To Special
• Merge/Unmerge Cells & Wrap Text
• Extracting Unique Values & Important Ribbon, General and Data Entry Keyboard Shortcuts

Module 2:- Formatting Essentials

• Formatting Essentials Introduction
• Custom Cell Number Formats
• Custom Date/Time Formats
• Working with Comments / Notes
• Format Painter – A Quick way to copy ‘Formatting Attribute’
• Paste Special
• Table, Table Styles & Formatting
• Freeze Panes

Module 3:- Functions & Formulas

• Introduction to Excel Functions and Formulas
• Basics of Functions & Formulas
• Working with Cell References Types
• Most Used Basics & Advanced Functions & Formulas
• Working with Array Formulas
• Creating Customized Formulas Step-by-Step with Live examples
• Creating and Working with Dynamic Ranges using Function and Excel Table features
• Formulas Debugging / Formulas Auditing
• Types of Formula Errors / Error Handling Tricks
• Text Functions: – CLEAN, CONCATENATE, LEFT, RIGHT, MID, LEN, FIND, SEARCH, SUBSTITUTE, and TEXT, etc.
• Date & Time Functions: – DATE, DAYS, TIME, NOW, WEEKNUM, WORKDAY, and WORKDAY.INTL etc.
• Math & Trig Functions: – INT, MOD, ROUND, ROUNDDOWN, SUMIF, SUMIFS, SUMPRODUCT etc.
• Statistical Functions: – AVERAGE, COUNT, COUNTA, COUNTBLANK, MAX, MIN, LARGE etc.
• Logical Functions: – IF, IFS, AND, OR, and IFERROR.
• Lookup & Reference Functions: – FORMULATEXT, VLOOKUP, HLOOKUP, INDEX, MATCH, INDIRECT, and OFFSET
• Newly Introduced Functions in Recent Version of Excel*: – CONCAT, TEXTJOIN, IFS, SWITCH, DGET, UNIQUE, FILTER, etc.
• Nested Conditions/Customize Formulas*

Module 4:- Data Analysis

• Data Sorting
• Data Filtering
• Named Ranges
• 10 different ways to use Conditional Formatting
• 10 different use of Data Validation
• What-If Analysis

Module 5:- Excel Charts

• Introduction to Excel Charts
• Exploring the most commonly used Charts and Templates
• Basics of Charts
• Selecting Requirement based Charts
• Working with Basic Charts:
• Creating Customized / Advanced Charts
• Creating Dynamic Chart
• Working with Dynamic Interactive Charts in Excel using Drop Down
• Working with Chart Elements, Formatting, Chart Styles, Properties, etc.

Module 6:- Pivot Tables

• Introduction to Pivot Table
• Creating a Pivot Table
• Use of Calculated Fields/Items
• Pivot Table Formatting
• Grouping Items & Summarizing data in Pivot Tables
• Grouping and Bucketing data in Pivot Table
• Changing/Modifying Data Sources
• Working with Pivot Table Designs & Layouts
• Exploring Important Pivot Table Options & Field Settings
• Pivot Table Filters
• Changing Pivot Table Summary Calculation
• Use of Slicers in Pivot Table
• Using Source Data to Convert into Infographic Summary
• Introduction to Pivot Charts

Module 1:- Getting Started with My SQL

• An Introduction and Overview of MySQL
• Installation and GUI Tools
• An Overview

Module 2:- My SQL Fundamentals

• Introducing SELECT statement
• Introducing WHERE clause
• Sort result with ORDER BY
• Using FROM to specify the source tables
• Importance of Clause Orders
• Data Modification tricks

Module 3:- Creating Database & Tables

• Creating a database
• Creating a table
• Creating Indexes
• Controlling column behavior with constraints
• Using foreign key constraints
• Creating an ID column
• Changing a schema with ALTER
• Introducing NULL and NOT_NULL
• Introduction to MySQL Data Types
• Setting up default values
• MySQL Warnings
• Alerting a table

Module 4:- My SQL Functions & Clause

• Introduction to MySQL Functions
• String Functions – CONCAT, SUBSTRING, REPLACE, REVERSE, LENGTH, UPPER, LCASE etc.
• Aggregate Functions – COUNT, MIN, MAX, SUM, AVG, ROUND etc
• Date/Time Functions – CURDATE, CURTIME, CURRENT_DATE, LOCALTIME etc.
• Control Flow Functions – IF, IFNULL, NULLIF etc.

Module 5:- Multiple Tables & Joins

• Introduction to JOINS
• Different types of JOINS
• JOINS and Aliases
• Multiple Table Joins
• Creating a simple Subselect
• Understanding of Primary keys and Foreign keys

Module 6:- Transactions, Stored Routines & Triggers

• Transactions & Stored Routines
• Triggers

Bonus Modules

• Creating a New User Login
• Granting access to new users
• Backup and Restore databases
• Important Keyboard Shortcuts Guide
• Session Study Material
• Situational Case Studies for Best Practice and Getting Ready for Corporate World
• 6 Months Live Support via Phone/Email/Messages

Module 1:- Introduction to Power BI

• What is Data Analytics & Data Visualization
  • Introduction to Data Analysis
  • Introduction to Data Visualization
  • What is Business Intelligence?
  • Overview of Self-Service Business Intelligence (SSBI) Tools
  • Leading Self-service tools
  • Comparison of Leading Visualization Tools
• Getting Started with Power BI
  • Introduction to Microsoft Power BI
  • Why Power BI
  • Power BI Elements
  • Basic Components of Power BI
  • Building Blocks of Power BI
  • Key Benefits of Power BI
• Hands-On
  • Power BI Installation
• Skills Gained
  • Understanding of Data Analysis & Visualizations
  • Concepts of Business Intelligence
  • Installing Power BI on the System
  • Elements and Building Blocks of Power BI

Module 2:- Getting Data from Different Data Sources

• Power Bi Desktop (Getting Data)
  • What is Power BI Desktop
  • Quick Walk-through Power BI Interface
  • Views in Power BI Desktop
  • Report View
  • Data view
  • Model view
  • Understanding ETL Concepts: Extract, Transform & Load
  • Data Sources in Power BI Desktop
  • Connecting and Getting data from different sources in Power BI
  • Getting data from Excel, CSV, Access, etc
  • Saving Workfile
• Hands-On
  • Identifying and retrieving data from different data sources
  • Extracting and Loading data into Power BI
  • Preparing Data
• Skills Gained
  • Concepts of ETL tool
  • Getting data from multiple sources

Module 3:- Clean, Transform and Load the Data

• Shaping Data using Power Query
  • Loading data into Power BI Desktop
  • What is Query Editor
    • Cleaning Data with Query Editor
  • Transforming Data with Query Editor
    • Unpivoting Columns
    • Eliminating Rows / Columns
    • Changing / Modifying Data Types
    • Adding Custom Columns
    • Replacing Values / NULL
    • Promoting / Demoting Row as Header
    • Modifying and managing existing ‘Steps’
  • Extracting Date Components from Date-Time
  • Introduction to “M Query”
  • Combining Data
    • Append Merge / Joins, Transpose & Formatting Data Operations
  • Hands-On
    • Cleaning Data using Power Query Editor
    • Shaping the data
    • Transforming and cleaning the data
    • Merging Rows / Columns from multiple tables
    • Joins / Append activities
  • Skills Gained
    • Data Transformation using Query Editor
    • Loading the data
    • Understanding of “M Query”

Module 4:- Design a Data Model

• Introduction to Data Modeling
  • Understanding of Relationship
    • What is Relationship?
    • One to Many Vs One to One Relationship
  • Auto-Detect Relationship during Load
  • Dimension Table Vs Fact Table
  • Cardinality in Data Modeling
  • Creating Relationship Manually
  • Managing Data Relationship
  • Editing Relationship
  • Creating Calculated Columns
  • Creating Measures
  • Optimizing Data Models for Better Visuals
  • Cross Filter Direction
  • Defining Hierarchies

• Hands-On
  • Creating Model Relationships
  • Managing Relationships
  • Creating Measures
  • Creating hierarchies

• Skills Gained
  • Understanding the basics of data modeling
  • Defining relationships and their cardinality
  • Understand star schema and its’ importance
  • Managing relationships

Module 5:- DAX Formulas & Calculations

• Introduction to Data Analysis Expression (DAX)
  • Importance of DAX
  • Data Types in DAX
  • Defining Calculation Type
    • Calculated Column Vs Calculated Measures
    • Adding New Measures to Report
    • Creating Calculated / DAX Tables
  • DAX Syntax & Operators
  • Most Important & Common Basics & Advanced DAX Functions
    • Aggregate Functions
    • Logical Functions
    • Time-Intelligence Functions
    • Information Functions
  • DAX Variables
  • Formatting DAX Code
  • Handling Errors in DAX Expressions
• Hands-On
  • Creating Calculated Tables
  • Creating Calculated Columns and Measures
  • Writing DAX calculations to perform Data Analysis
  • Beautifying DAX
  • Creating Variables
• Skills Gained
  • Understanding DAX
  • Create Calculated Columns, Measures & Tables
  • Working with Advanced functions e.g. Time-Intelligence, Filter contexts etc.

Module 6:- Data Visualization & Creating Reports

• Introduction to Visuals in Power BI
  • Creating Visualization
  • How to use Visual
  • Exploring Visualizations’ List
  • Exploring Most Common & Important Visualizations
    • Bar, Column, Line and Area Charts
    • Pie, Donut, and Gauge Charts
    • Single Number Cards and Multi Row Cards
    • Table & Matrix Visuals
    • Combo Chart, Funnel and Treemap Charts
    • Slicer, KPI and Custom Visuals
    • Map & Filled Map Visuals
  • Modifying Color Properties of Charts and Visuals
  • Shapes, Text Box, Buttons, and Images
  • Types of Filters
  • Slice and Dice Data in Power BI
  • Drilling-Up/Down
  • Understanding Custom Visuals & How to add them
  • Customizing Canvas
    • Setting up Canvas & Must Know Global Fonts
    • Page Layout & Formatting
  • Bookmarks in Power BI
  • Reports in Power BI
  • Conditionally formatting tables/matrixes
  • Activity: Creating Sales Report
• Hands-On
  • Creating visual and charts
  • Designing a report
  • Managing visual fields and format properties
• Skills Gained
  • Creating and selecting effective visualizations
  • Designing report page layout and Visual modification
  • Adding report navigation & basic functionalities
  • Creating visually stunning reports

Module 7:- Power BI Service and Managed Workspaces

• Introduction to Power BI Service & Workspaces
• What is Power BI Service
  • Exploring Interface of Power BI Service
  • Understanding the Admin Portal settings
• What is Power BI Workspace
  • Understanding of “Workspace”
  • My Workspace Vs New Workspace
  • Creating New Workspace
  • Sharing and Managing Workspaces
• Roles in workspaces in Power BI
• Exploring Power BI Licensing
  • Power BI Free Vs Power BI Pro Vs Premium
• Hands-On
  • Publishing reports on Power BI Service
  • Creating workspaces
  • Sharing and Managing Reports
  • Moving important assets to App and Publishing app in Power BI
• Skills Gained
  • Knowledge of Power BI Service (Cloud)
  • Workspaces in Power BI
  • Creating and managing a workspace

Module 8:- Creating Dashboard in Power BI

• Dashboard in Power BI
  • Introduction to Dashboard
  • Difference between Report Vs Dashboard
  • Preparing and Creating Dashboard
    • Configuring a Dashboard
    • Pinning Visuals to Dashboard
  • Dashboard Tiles
    • Pinning Tiles
  • Dashboard Widgets
  • Introduction to Power BI Q&A
    • Asking questions about data
    • Fetching results using Q&A
    • Using Q&A to create a dashboard tile
  • Quick Insights in Power BI
    • Data Analysis using Quick Insights
  • Sharing and Collaborating Dashboard with Business Users
• Hands-On
  • Creating a Dashboard
  • Pinning visuals to Dashboard
  • Adding text/video widgets to Dashboard
  • Sharing and Moving Dashboard to distributed app
  • Use of Power BI Q&A to question your data
• Skills Gained
  • Dashboard creation from report
  • Enhancing dashboard usability
  • Importance of Q&A and Quick Insights

Module 9:- Data Gateway, Security & Schedule Refresh

• Report Security in Power BI
• Introduction to Row-level Security (RLS)
  • Setting up and Enforcing Row-level security
  • Implementing Row-level security
• Data Gateway & Report Scheduling
  • What is Data Gateway
  • Types of Gateway
    • On-premises data gateway (personal mode)
    • On-premises data gateway (standard mode)
  • Installing Data Gateway
  • Data Gateway System Requirement
  • Benefits of Data Gateway
  • Scheduled Refresh & Refreshing Data
  • Managing Data Source
    • Adding and removing a Data Source
  • Hands-On
    • Configuring & Implementing RLS
    • Managing datasets
    • Setting up On-Premise Data Gateway
    • Connecting Reports on Power BI Cloud with Local Data Files
    • Schedule dataset refresh
  • Skills Gained
    • Dashboard creation from report
    • Enhancing dashboard usability
    • Importance of Q&A and Quick Insights

Bonus Module:- In-Class Project

• In-Class Project: Building Sales Report & Dashboard from Scratch
  • Extracting Data from the Local Source
  • Transforming Data using Power Query
    • Cleaning, Shaping and Preparing the data if necessary
  • Loading Data to Power BI Desktop
  • Building Data Model
  • Visual and Charts
  • Row-level Security
  • Publishing Reports
  • Power BI Service
  • Creating Dashboard
• Hands-On
  • Analyzing Sales data
  • Derive conclusions from the patterns and trends shown in the visualization
• Skills Gained
  • Report and Dashboard creation
  • Publishing, sharing and collaborating it with Business users