Course Outline
-
Introduction to Excel Environment
-
Explanation about data calculation in Excel
-
Use of Shortcuts
-
Formatting and Conditional Formatting
-
Working with Formulas - Logical and Text Functions
-
Understanding about Sorting, Filtering and Data Validation
-
Data Analysis using Pivot Tables
-
Understaing of Mathematical, Statistical Functions
-
Worksheet and Workbook Protection and Security
-
Understanding of Name Ranges
-
Introduction of Charts
-
Introduction of Form Controls
-
Understanding of Data Tools Panel
-
Basics of Macro Recording
-
Overview of Dashboards
-
Deciding on Dahsboards
-
Trends and Scenarios using charts
-
Advanced Charting Techniques using Thermometer, Doughnut, Pareto, Panel and Step Chart
-
Designing Sample Dashboard using Form Controls
-
Tips and Tricks to enhance dashboard designing
-
Working with VBE (Visual Basic Editor)
-
Introduction to Excel Object Model
-
Understanding of Sub and Function Procedures
-
Key Component of Programming Language
-
Understaing of If, Select Case, With End With Statements
-
Looping with VBA
-
User Defined Function
-
Some Commonly Used Macro Examples
-
Error Handling
-
Object and Memory Management in VBA
-
User Form Controls
-
ActiveX Controls
-
Communicating with Database MS Access through ADO - Exporting/Importing Data
-
What is SQL – A Quick Introduction
-
Getting started
Understanding basic database concepts
Introduction to MS SQL Server Management Studio
Utilizing the Object Explorer
-
Understanding basic RDBMS concepts
Schema –Meta Data –ER Diagram
Looking at an example Database design
Data Integrity Constraints & types of Relationships (Primary and foreign key)
Basic concepts – Queries, Data types & NULL Values, Operators and Comments in SQL
-
Data based objects creation (DDL Commands)
Creating, Modifying & Deleting Tables
Drop & Truncate statements – Uses & Differences
Alter Table & alter Column statements
-
Data manipulation (DML Commands)
Insert, Update & Delete statements
Select statement – Where, Group By, Order by & Having clauses
-
Working with Select statement
Union and Union All – Use & constraints
Intersect and Except statements
Joins & Aliases
Accessing data from Multiple Tables
Inline and sub-queries
SQL Functions – Number, Text, Date, etc
SQL Keywords – Top, Distinct, Null, etc
SQL Operators, Use of wildcards, etc
-
Optimizing your work
Sub-queries vs. Temp Tables vs. Joins
Optimizing for Composite keys & Non-numeric Primary keys
-
What is Tableau? What does the Tableau product suite comprise of? How Does Tableau Work?
-
Tableau Architecture
-
What is My Tableau Repository?
-
Connecting to Data & Introduction to data source concepts
-
Understanding the Tableau workspace
-
Dimensions and Measures
-
Data Types & Default Properties
-
Tour of Shelves & Marks Card
-
Using Show Me!
-
Building basic views
-
Saving and Sharing your work-overview
-
Date Aggregations and Date parts
-
Cross tab & Tabular charts
-
Totals & Subtotals
-
Bar Charts & Stacked Bars
-
Line Graphs with Date & Without Date
-
Tree maps
-
Scatter Plots
-
Individual Axes, Blended Axes, Dual Axes & Combination chart
-
Edit axis
-
Parts of Views
-
Sorting
-
Trend lines
-
Reference Lines
-
Forecasting
-
Filters
-
Context filters
-
Sets
-
In/Out Sets
-
Combined Sets
-
Grouping
-
Bins/Histograms
-
Drilling up/down – drill through
-
Hierarchies
-
View data
-
Actions (across sheets)
-
Explain latitude and longitude
-
Default location/Edit locations
-
Symbol Map & Filled Map
-
Custom Geo Coding
-
Working with aggregate versus disaggregate data
-
Explain - #Number of Rows
-
Basic Functions (String, Date, Numbers etc)
-
Usage of Logical conditions
-
Explain scope and direction
-
Percent of Total, Running / Cumulative calculations
-
Create What-If analysis
-
Using Parameters in
-
Calculated fields
-
Bins
-
Reference Lines
-
Filters/Sets
-
Display Options (Dynamic Dimension/Measure Selection)
-
Combining multiple visualizations into a dashboard (overview)
-
Making your worksheet interactive by using actions
-
Options in Formatting your Visualization
-
Working with Labels and Annotations
-
Effective Use of Titles and Captions
-
Multiple Table Join
-
Data Blending
-
Difference between joining and blending data, and when we should do each
-
Working with the Data Engine / Extracts
-
Toggle between to Direct Connection and Extracts
