Modifying Data

• inserting Data: INSERT INTO
• updating Data: UPDATE SET
• deleting Data: DELETE FROM
• Working with NULL Values and IS NULL
• Real-life Example: Updating Inventory Levels in
• a Warehouse Management System

Data Normalization and Database Design

• understanding Data Normalization
• normal Forms (1NF, 2NF, 3NF, BCNF)
• designing Efficient Database Schemas
• case Study: Normalizing a Customer Relationship
• Management (CRM) Database

Advanced-Data Manipulation with Window Functions

• introduction to Window Functions
• ROW_NUMBER(), RANK(), DENSE_RANK()
• aggregate Window Functions: SUM(), AVG(), MIN(), MAX()
• practical Use Cases of Window Functions
• Real-life Example: Calculating Running Totals and Moving Averages in Financial Data

Mastering Subqueries and Correlated Subqueries

• Deep dive into subqueries and correlated subqueries.
• Optimizing subqueries for performance.
• performance Considerations and Best Practices
• Real-life example: Using complex subqueries for dynamic reporting.

Indexes and Performance Optimization

• Understanding Indexes and Their Types
• Creating and Managing Indexes
• Query Tuning and Optimization Techniques
• analyzing Query Performance with EXPLAIN
• case Study: Optimizing Queries for a High-traffic Ecommerce Platform

Stored Procedures, Functions, and Triggers

• Writing stored Procedures and User-Defined Functions
• benefits and Use Cases of Stored Procedures
• Implementing triggers for Automated Tasks
• Real-life Example: Automating Inventory Updates with
• Triggers

Working with Complex Data Types

• Handling JSON and XML Data
• Extracting and Manipulating JSON Data
• Working with Geospatial Data in MySQL
• Real-life Example: Storing and Querying User
• Preferences in JSON Format

Handling Time Series and Temporal Data

• Working with Date and Time Functions
• Analyzing Time Series Data
• Temporal Data Types and Functions
• Case Study: Time Series Analysis for Stock Market Data

Advanced Analytical Techniques

• Recursive queries and common table expressions (CTEs).
• Pivoting and unpivoting data.
• Advanced pattern matching with regular expressions.
• Real-life example: Using recursive queries for hierarchical data analysis.

Final Project: Solving a Complex Data Problem

• Project Overview and Requirements
• Data Exploration and Preprocessing
• Advanced-Data Analysis and Reporting
• Presenting Your Findings and Insights
• Project Application: Building a Comprehensive Sales Dashboard

• Installation Guidelines and Procedures
• Software Overview
• Power BI Account Registration
• Introduction to Tools and Terminology
• Updating Data in Power BI Service
• Defining a Dashboard
• Defining a Report
• Understanding the Interface
• Navigation Pane and Ribbon
• Working with Visualizations
• Saving and Publishing Reports

• Connecting to Data Sources
• Uploading Local CSV Files
• Connecting to Excel Data
• Utilizing the Query Editor
• Comparing Import Data vs. Direct Query
• Data Cleaning and Transformation Techniques
• Merging and Appending Data
• Introduction to ETL Concepts
• Extracting Data from Various Sources
• Transforming Data for Analysis
• Loading Data into Power BI

Hands-on: Datasets will be provided for practice in loading, cleaning, transforming data, and applying ETL concepts.

• Data Modeling Techniques
• Managing Data Relationships
• Understanding Cardinality and Cross Filtering
• Default Summarization and Sorting Options
• Creating Calculated Columns
• Developing Quick Measures and Measures
• Understanding Star Schema and Snowflake Schema
• Using Date Tables and Hierarchies

Hands-on:

• Participants will work with datasets to create
• calculated columns and manage relationships

• Introduction to DAX
• What is DAX?
• The role of DAX in Power BI
• Importance of DAX
• Why DAX is essential for data modeling and analysis
• Scenarios where DAX is used in Power BI
• DAX Syntax
• Basic syntax rules
• How to write DAX formulas
• Data Types in DAX
• Understanding different data types
• Implicit vs. explicit data type conversion
• Functions in DAX
• Overview of DAX functions
• Commonly used function
• Aggregation Functions: SUM, AVERAGE, MIN, MAX, COUNT
• Date and Time Functions: TODAY, NOW,
• DATEDIFF, YEAR, MONTH, DAY
• Logical Functions: IF, AND, OR, SWITCH
• Text Functions: CONCATENATE, LEFT, RIGHT,MID, SUBSTITUTE
• Mathematical Functions: ABS, CEILING, FLOOR, ROUND
• Statistical Functions: MEDIAN, PERCENTILE, STDEV
• Information Functions: ISBLANK, ISNUMBER, ISERROR
• Creating Measures with DAX
• Difference between calculated columns and measures
• How to create and use measures DAX Operators
• Arithmetic Operators: +, -, *, /, ^
• Comparison Operators: =, <>, <, <=, >, >=
• Text Concatenation Operator: &
• Logical Operators: &&, ||, IN
• Tables and Filtering in DAX
• Working with tables in DAX
• Filter functions: CALCULATE, FILTER, ALL, ALLEXCEPT, REMOVEFILTERS
• Context transition and its implications
• Time Intelligence Functions
• Overview of time intelligence in DAX
• Common time intelligence functions: TOTALYTD,
• SAMEPERIODLASTYEAR, PARALLELPERIOD, DATESBETWEEN
• Creating and using date tables
• Working with Relationships in DAX
• Understanding relationships and their impact on DAX formulas
• Using RELATED and RELATEDTABLE functions
• Advanced DAX Concepts
• Calculated Tables
• Row context vs. filter context
• Context transition
• Iterators: SUMX, AVERAGEX, MAXX, MINX

Hands-on:

• Participants will practice using various DAX functions

• Creating Visualizations
• Color Formatting Techniques
• Setting Sort Order
• Developing Scatter and Bubble Charts
• Using Tooltips
• Implementing Slicers
• Cross Filtering and Highlighting
• Applying Report-Level Filters
• Drill Down and Drill Up Functionality
• Creating Hierarchies
• Conditional Formatting for Tables and Matrices
• Utilizing KPIs
• Using Cards and Gauges
• Map Visualizations
• Custom Visuals
• Grouping and Binning Data
• Using Selection Pane, Bookmarks & Buttons
• Managing Z-Order
• Best Practices for Effective Visualizations
• Storytelling with Data

Hands-on:

• Participants will practice creating visualizations, applying color formatting, using slicers, and other visualization techniques.

• Modifying and Printing Reports
• Renaming and Deleting Report Pages
• Adding Filters to Pages or Reports
• Setting Visualization Interactions
• Printing Report Pages
• Exporting Reports to PowerPoint
• Creating Dashboards
• Managing Dashboards
• Pinning Report Tiles to Dashboards
• Pinning Live Report Pages to Dashboards
• Pinning Report Tiles to Dashboards
• Pinning Live Report Pages to Dashboards
• Pinning Tiles from Other Dashboards
• Pinning Excel Elements to Dashboards
• Managing Pinned Elements in Excel
• Adding Tiles to Dashboards
• Building Dashboards with Quick Insights
• Setting a Featured Dashboard
• Asking Questions about Your Data with Power BI Q&A
• Tweaking Datasets for Q&A
• Enabling Cortana Integration for Power BI
• Best Practices for Dashboard Design

Hands-on:

• Participants will practice adding filters to reports, setting interactions, and creating dashboards.

• Overview of Sharing Options
• Publishing Reports from Power BI Desktop
• Publishing Reports to the Web
• Sharing Dashboards Using Power BI Service
• Understanding Content Packs
• Saving Reports as PDF
• Implementing Row-Level Security
• Exporting Data from Visualizations
• Setting Permissions and Access Controls
• Collaborative Workspaces
• Best Practices for Report Distribution

Hands-on:

• Participants will practice sharing dashboards using Power BI Service, saving reports as PDFs, and exporting data from visualizations.

• Function
• Nested IF
• AND Function
• SUMIF
• COUNTIF
• IFERROR

Hands-on:

• Implement conditional functions in Excel formulas.
• Use nested IF statements for complex conditions.

• Creating Simple Charts
• Modifying Charts
• Changing Chart Types
• Chart Formatting
• Formatting Chart Items
• Formatting Plot Area
• Formatting Data Markers

Hands-on:

• Create various types of charts and customize them.
• Format charts to enhance visual clarity

• Converting Dates to Different Formats
• Obtaining Today’s Date
• Finding Workdays After Specified Days
• Extracting Year, Month, Day from Date
• Extracting Day of the Week from Date
• Calculating Differences Between Dates

Hands-on:

• Convert date formats using Excel functions.
• Perform date calculations based on given scenarios

• Highlight Cells Rules
• Top / Bottom Rules
• Data Bars
• Color Scales
• Icon Sets
• New Rule
• Clearing and Managing Rules

Hands-on:

• Apply various conditional formatting rules to Excel data.
• Manage and clear conditional formatting rules as needed.

• Sort by Text, Numbers, Dates
• Sort by Cell Color, Font Color, Cell Icon
• Sort by Custom List
• Multi-column or Multi-row Sorting

Hands-on:

• Sort Excel data using different sorting methods.
• Perform multi-column sorting operations.

• Filter by Selected Values, Text, Date, Numbers
• Filter by Cell Color
• Clear Filters
• Advanced Filtering Techniques
• Using Slicers for Filtering

Hands-on:

• Apply filters and Advance filterings to Excel data based on various criteria.
• Use slicers to interactively filter data.

• VLOOKUP and HLOOKUP Functions
• INDEX and MATCH Functions
• Range Lookup Techniques

Hands-on:

• Perform lookup operations using VLOOKUP, HLOOKUP, INDEX, and MATCH functions.
• Apply range lookup techniques in Excel formulas.

• Creating PivotTables
• Recommended PivotTables
• PivotTable Fields and Areas
• PivotTable Nesting and Filtering
• Summarizing Values by Calculations
• PivotTable Tools and Customizations
• Creating PivotCharts
• Using Pictures in Charts
• Advanced Chart Types: Band, Thermometer, Gantt,
• Waterfall
• Sparklines
• Working with Multiple Sheets
• Creating a formula across multiple worksheets
• Summarizing data in multiple worksheets
• Merging two tables using Append Queries

Hands-on:

• Create PivotTables and PivotCharts to summarize and visualize data.
• Customize PivotTables and PivotCharts for multiple worksheets

• Introduction to Macros
• Recording and Running Macros
• Introduction to VBA (Visual Basic for Applications)
• Basic VBA Syntax

Hands-on:

• Record and run macros to automate repetitive tasks.
• Write basic VBA scripts for Excel operations.

Connecting to a Data File - CSV File

• Importing CSV Files
• Basic Data Preparation

Advanced-Data Preparation

• Using Data Interpreter
• Pivoting and Splitting Columns
• Fixing Geographical Data Errors

Hands-on Exercise:

• Connect Tableau to a CSV file and perform data preparation tasks like splitting columns and fixing data errors.

Basic Visualization Techniques

• Creating Bar Charts, Line Charts, and Pie Charts
• Adding Colors and Labels
• Formatting Visualizations

Exporting Your Worksheet

• Exporting Visualizations to Various Formats

Hands-on:

• Create bar, line, and pie charts using sample data. Add colors and labels, and format the visualizations.

Time Series, Aggregation, and Filters

• Working with Time Series Data
• Understanding Aggregation and Granularity
• Adding Filters and Quick Filters

Creating Area Charts and Highlighting

• Building Area Charts
• Using Highlighting Techniques

Hands-on:

• Create a time series chart with filters and an area chart with highlighting.

Working with Data Extracts in Tableau

• Creating and Using Data Extracts
• Performance Optimization

Creating Calculated Fields

• Basic Calculated Fields
• Advanced Calculations

Hands-on Exercise:

• Create and use data extracts, and build calculated fields for data analysis.

Joining Data

• Understanding LEFT, RIGHT, INNER, and OUTER Joins
• Handling Joins with Duplicate Values
• Joining on Multiple Fields

Data Blending

• When to Use Data Blending
• Creating Calculated Fields in a Blend (Advanced Topic)

Hands-on Exercise:

• Perform different types of joins and data blending using provided data sets.

Dual Axis Charts

• Creating Dual Axis Charts
• Synchronizing Axes

Mapping

• Setting Geographical Roles
• Fixing Geographical Data Errors
• Creating Maps

Hands-on:

• Create dual-axis charts and maps, and correct geographical data errors.

Dashboard Design

• Creating Your First Dashboard
• Adding Interactive Actions (Filters and Highlighting)

Advanced Dashboard Interactivity

• Leveraging the Power of Parameters
• Creating a Customer Segmentation Dashboard

Hands-on Exercise:

• Build an interactive dashboard with filters and highlighting actions.

Advanced-Data Preparation

• Connecting to PDF and Spatial Files
• Joining Spatial Files

Complex Visualizations

• Step and Jump Line Charts
• Viz in Tooltip

Hands-on:

• Connect to PDF and spatial files, and create step line charts with viz in tooltip.

Creating a Storyline

• Designing a Storyline
• Adding Visualizations to a Story

Final Project: Customer Segmentation Dashboard

• Analyzing Customer Data
• Creating and Presenting a Comprehensive Dashboard

Hands-on:

• Create a complete storyline and present a customer segmentation dashboard as a final project.

Table Calculations

• Understanding Table Calculations
• Creating and Using Table Calculations for Analysis

Advanced Dashboard Techniques

• Creating Bins and Distributions
• Leveraging Parameters for Dynamic Dashboards

Advanced Data Preparation

• Working with Data From PDF Files
• Data Modeling and Relationships

Hands-on Exercise:

• Create and apply table calculations for analysis.
• Build a dynamic dashboard using parameters.
• Prepare data from PDF files for analysis in Tableau.

Mapping and Geospatial Data

• Mapping Basics and Geographical Roles
• Fixing Geographical Data Errors

Advanced Geospatial Analysis

• Connecting and Joining to Spatial Files
• Creating and Customizing Maps

Hands-on:

• Create maps with geographical data and fix any errors.
• Join spatial files and create advanced geospatial visualizations..

Publishing to Tableau Server and Tableau Online

• Online Publishing Dashboards and Reports**
• Managing Permissions and Data Sources

Collaboration and Sharing

• Creating Projects and Organizing Content
• Using Tableau Online for Collaboration

Hands-on Exercise:

• Publish a dashboard to Tableau Server and set up permissions.
• Collaborate with others using Tableau Online and manage shared projects.

Project Planning and Data Collection

• Defining the Project Scope
• Collecting and Preparing Data

Building and Presenting the Final Dashboard

• Creating Visualizations and Dashboards
• Presenting Findings and Insights

Hands-on:

• Define a project scope and collect relevant data.
• Build a comprehensive dashboard and present the findings as a capstone project.