Diploma in Data Analytics & Visualisation – DDAV

Course Overview

Diploma in Data Analytics & Visualisation course is designed to help you use Reports and Analytics to find answers to your business questions. You will learn how the reporting interface and navigation work, how to run reports to get the answers you are looking for and how to configure reports for effective decision-making.

Prerequisites:

• Basic Knowledge of Computers
• Advance Excel

After completion of the course, students can perform the following task :

• Create Excel templates
• Create Dynamic Reports
• Automate Complex Reports
• Create Interactive Dashboards
• Design a database to support all Reporting requirements
• Create Reports using BI tools like Tableau and Power BI
• Data Manipulation
• Data Modelling
• Data Analysis with Statistics
• Data Communication with Information Visualization
• Data at Scale

Content & Syllabus

• Advanced File Tasks
• Working with Functions and Formulas
• Managing Tables in Microsoft Excel
• Data Tools in Excel
• Prerequisites: Basic Knowledge of Computers
• Advance Excel
• Sparkline
• Grouping & Outlining and Subtotals
• What-If Analysis
• Advanced PivotTables & Charts, slicers
• Creating HLOOKUP and VLOOKUP Functions
• Advanced Conditional Formatting
• Advanced Lookup and Reference Functions
• Advanced Functions in Excel
• Power Pivot

Dashboard Designing

• Introduction to Excel Dashboards
• Excel Table in Dashboards
• Using Custom Number Formatting
• Using Symbols in Excel Dashboards
• Conditional Formatting for Dashboards
• Sparklines in Excel Dashboards
• Data ready for Excel Dashboards
• Excel Dashboard formulas
• Excel Charts for Dashboards (Advance Concept)

Reporting Techniques

• Understanding Raw Data
• Understanding Reporting
• Creating Calculation Sheets
• Working With Charts and Tables
• Reports using Advanced Charting Techniques
• Reports Using Pivot Table, Pivot Charts

Macros With VBA

• Macro recording
• Visual Basic Editor
• Understanding Objects, Methods, Properties and Variables
• Control Structures-Decision
• Looping Structures
• User Forms and GUI
• Worksheet / Workbook Operations
• Error Handling

MS-Access

• Getting Started with Access
• Working with Table Data
• Querying a Database
• Using Forms
• Generating Reports
• Sharing Data Across Applications
• Designing a Relational Database

Power BI

Introduction to Power BI

• What is Power BI
• Installing Power BI Desktop
• Exploring the Power BI Workflow

Transforming Data with Power BI Desktop

• Connecting to different sources
• Different connecting Options
• Shaping and transforming data with Power Query
• Editing, Merging, Appending queries etc.

Modeling with Power BI

• Introduction to Modeling
• Building Relational Models
• Creating table relationships
• Understanding the filter flow
• Cardinality and Cross filtering

Introduction to DAX

• Understanding DAX Syntax
• DAX calculation types
• DAX functions
• Using variables in DAX expressions
• Table relationships and DAX
• DAX tables and filtering

Tableau

Introduction to Tableau

• Understanding Working
• Architecture & Data Model
• Data Connection & Joins
• Working with multiple data sources

Tableau Interface

• Measures and dimension
• Shelves
• Show Me
• Data Types
• Default Properties
• Marks Card
• Page Shelf
• Difference between .twb and .twbx
• Discrete vs Continuous
• Date Aggregations and Date parts
• Cross tab
• Totals & Subtotals

Chart Types

• Bar Charts & Stacked Bars
• Pie Charts
• Line Graph with Date
• Line Graph without Date
• Area charts
• Tree Map
• Word Cloud
• Packed Bubble
• Box & Whiskers Plot
• Scatter Plots
• Highlight tables
• Heat maps

Data Visualization

• Geographical fields
• Map options

Run Time Columns

• Calculated Fields
• String Calculation
• Data Calculation
• Logical Calculation
• Numeric Calculation
• Parameters

Data Formatting

• Legends
• Highlights
• Labeling
• Number Formats

Advanced Function

• Sorting
• Groups
• Sets
• Hierarchy
• Reference and Trend Lines
• Edit Axi
• Bins

Filters

• Dimension Filter
• Measure Filter
• Filter – Display Option
• Context Filters
• Relevant Filters
• Sets in filters
• Conditional Filters

Advanced Charts

• Waterfall
• Funnel

Dashboards

• Dashboards Objects
• Dashboard Actions
• URL
• Highlight
• Filter
• Publish to Web

Storyline In Tableau

Publish Reports

SQL

Introduction to Databases

• Databases
• Introduction to DBMS
• Popular DBMS Software
• Concepts of RDBMS
  • Tables
  • Tuples
  • Attributes
• Normalization
  • First Normal Form
  • Second Normal Form
  • Third Normal Form
• NOSQL Databases
  • Types of NOSQL
• Comparison

SQL Commands

• Types of SQL Commands
• Data Definition Language
  • Create, Drop, Truncate, Alter and Rename Objects
• Data Query Language
  • Select Statements
• Data Manipulation Language
• DCL and TCL
  • Grant, Revoke and transaction statements
• SQL Data Types
  • Numeric, Date and Time, LOB Types
• DML Commands
  • Insert, Update and Delete Statements
• DDL Commands
  • Create and Drop Databases

Database Constraints

• Types of Constraints
  1. Relational Integrity Constraints
  2. Key Constraints
  3. Domain Constraints
  4. Referential Integrity
• Types Of Constraints
  1. Primary and Foreign Keys
  2. Application of Indexes
  3. Checking Constraints

SQL Transactions

• SQL Transactions
  • Examples
• ACID Properties
• TCL Statements
  • Start, Commit and Rollback Statements
• Auto Commit
• Save Points
  • Identifier
  • Rollback and Release

Database Objects

• Tables
  • Creating, Altering and dropping tables
• Sequences
  • Auto Increments
  • Re-Sequencing
• Views
  • Advantages
  • Creating and Dropping Views
• Indexes
  • Types of Indexes
  • B-Tree and Hash Indexes
  • Creating and dropping Indexes Top of Form