Course Description

This course is designed for beginners or non-programmers who are interested in building their career in Data Analytics. The field of data analysis, as the name implies, analyses data to discover trends. It has tremendous uses in the fields like Banks & Finance Industry,Telecommunication, Airlines, Oil and Gas, Manufacturing, Insurance, Retail, Healthcare, Government, Aerospace & Defense, Fast Moving Consumer Goods (FMCG), Hi-Tech, Pharmaceutical, Hotels & Hospitality, Travel and Tourism, Media, Real Estate, Engineering, Sports, E-commerce, Energy and Utility, Automotive, Non- Profit, Chemicals, Advertising and Public Relations etc.. who rely on quality data analysis to arrive at strategic business decisions. Fresh graduates / working professionals can definitely improve their resume and their job prospects by achieving a certificate in data analytics together with a globally recognized SAS Base Programmer Certification.

Course Objective

- This course is to help you to become a successful Data Analyst. It will teach you how to make powerful reports, creating dashboards, working with MS Access and SQL databases, along with a strong focus on case studies to ensure hands-on learning.

- In this course, you will also learn the powerful Data visualization tool - Tableau, one of the most popular data visualization and rapid BI tool to bring more interactive features to your analysis.

- This course is in line with the SAS Base Certification program and helps you clear the certification exam with ease and get the best jobs in the industry. The course explores the SAS tool and different techniques to help you access and manage data, create data structures, generate reports, and handle errors.

Who should do this course?

- Students from various quantitative backgrounds, like Engineering, Finance, Maths, Statistics, Economics, Business Management - Professionals working with data in any industry- Analytics consultants- IT/Software Professionals- Anyone who aspires to spearhead their career in Data Analytics.

Prerequisites

Computer literacy and knowledge of working with basic MicrosoftExcel is necessary.

Who are the trainers?

Our trainers are highly qualified industry experts and certified instructors with years of global analytical experience.

Projects – Case Studies

- Case Study on Gantt Charting using Conditional Formatting and Logical Function- Exercise workbooks on Basic & Advanced Excel Functions, Pivot tables etc- Sample Sales Dashboard- 2 Case studies on consolidating data from different workbooks into Excel

Award of Certification

Upon successful completion of the course, the certification shall we awarded

Course Outcome

After completion of this course, you will have clear understanding of Data Analytics and how to manage & visualize the data. All the modules structured using real time, industry relevant data sets to enhance your analytics capabilities and problem solving abilities. This course will encompass all to help you emerge as an Industry ready professional in the field of analytics.

CERTIFIED DATA ANALYST(Excel-SQL-Tableau+ Base SAS Programming)

Total Duration: 64 Hours (8 Days)

Databyte Academy Sdn Bhd (1176678-V)No. 18-4, Jalan 13/48A

Sentul Boulevard Shop OfficeJalan Sentul, 51000 Kuala Lumpur, Malaysia

info@databyte.com.my

www.databyte.com.my

+603-4045 5000

+603-4045 6000

CERTIFIED DATA ANALYST(Excel-SQL-Tableau+ Base SAS Programming)

Total Duration: 64 Hours (8 Days)

Course Content: Excel

• Navigating Worksheets • Formatting and Editing Worksheet Data • Grouping Data, Subtotals and Data Validation

Exploratory Data Analysis Using Excel

Introduction to Excel

• Basic statistics; descriptive and summary • Frequency, crosstabs • Best in class evaluation • Performance benchmarking • Correlation analysis • Key drivers analysis • Association analysis • Rules based Segmentation • Interpretation of results

Data Analytics Using Excel

• A review of essential functions • Logical functions • IF, AND, OR, CHOOSE • Error checking • Statistical functions(aggregation) • Averate/IFs, Sum/Ifs, Count/IFs • Rank, quartile, decile, percentile • Standard deviation, correlation, mean, median • Date functions • TODAY, DATE, EDATE • Text functions • CLEAN, TRIM, LEFT, MID, RIGHT • Importance of range names and Name Manager • Referencing & Ranges • Data validation • Data integrity issues • Setting validation • Cleaning invalid data

Essentials, preparing data for the dashboard

• Consolidating Worksheets (VLOOKUP/HLOOKUP, INDEX, MATCH, etc) • Using Pivot Tables

Analyzing Data with Excel

• Need for data preparation • Outlier treatment • Flat-liners treatment • Missing values treatment

Data Prep & Reduction techniques

• Referencing Functions • Filter, advance filter • Conditional formatting

Working with Formulas and Functions

• Simple charts • Pie charts

Creating Charts and Graphics

Visual Analytics (Reporting & Dash boarding) Using Excel

•What is dash board & Excel dash board. •Principles of great dashboard design •Common mistakes dashboard design •Selecting the correct chart to display data •Adding icons and images to dashboards •Effective use of colour and logos •Making dashboards dynamic

Overview of Dash boarding

•A review of essential functions •Logical functions •IF, AND, OR, CHOOSE •Error checking

Essentials, preparing data forthe dashboard

•Date functions •TODAY, DATE, EDATE •Text functions •CLEAN, TRIM, LEFT, MID, RIGHT •Importance of range names and Name Manager •Referencing & Ranges •Data validation •Data integrity issues •Setting validation •Cleaning invalid data

Essentials, preparing data forthe dashboard

•Deciding On Dashboards •Reports, Dashboards And Scorecards •Selecting Measures And Metrics

Deciding On Dashboards

•AVERAGE/IFs, SUM/Ifs, COUNT/IFs •MEDIAN, TREND, FORECAST •Look up functions •VLOOKUP, INDEX, MATCH, OFFSET

Statistical functions(aggregation)

•Pivot Charts & Visual Dash boarding Techniques •How to select the Right Charts for your Data •Overview of chart types and their •Saving and using chart templates •Interactive charts •Charts tips and tricks •Creating and customizing Charts •New Features of Excel – sparklines etc. •Design principles for including charts in dashboards

Charting (Pivot Charts & Normal Charts)

•Thermometer Chart •Doughnut Chart •Pareto chart •Panel Chart •Step Chart

Advanced Power Charting Techniques

•Conditional formatting •Conditional formatting basics •Conditional format manager •Tips and tricks •Excel Tables •Pivot tables – Advanced features •Comprehensive overview •Grouping fields •Calculated fields •Using slicers

Understanding which Excel tools areessential for dashboards

Visual Analytics (Reports & Dashboards) Using Excel

•Why use ActiveX controls •Linking charts to Form controls •Add dashboard interactivity via controls •Dropdown lists •Check boxes •Spin buttons

Adding ActiveX controls•Calculating a trend •Inserting trend lines on charts •Creating scenarios •Options for choosing different scenarios

Trends & Scenarios•Calculating a trend •Inserting trend lines on charts •Creating scenarios •Options for choosing different scenarios

Macros

•Protect dashboards by locking cells •Password Protection •Restricting incorrect data entry with data validations •Using worksheet protection to prevent entry •Protecting your dashboard files using “Read Only” and password protection

Bullet-proofing your Dashboards

•Complete Management Dashboard for Sales & Services •Creating a Sales Dashboard •Creating a Services Dashboard •Creating a HR Dashboard •Best Practices in Dashboard Design

Practical Dashboard Creation: Hands-on Dashboard Creation

•Dashboard Do’s and Don’t’s •Data Layout Creating Dynamic Dashboards •Merging and Consolidating Data Using Shapes to make Charts more attractiv suitability to dashboards •Bar, Line, Bubble charts•Advance charting •Combining different chart types within one plot area•Using Alerts to draw attention to dashboards•Creating user defined charts

Creating your Excel Dashboards

•Why use ActiveX controls •Linking charts to Form controls •Add dashboard interactivity via controls •Dropdown lists •Check boxes •Spin buttons

Adding ActiveX controls

CERTIFIED DATA ANALYST(Excel-SQL-Tableau+ Base SAS Programming)

Total Duration: 64 Hours (8 Days)

Course Content: Excel

•What are Databases •Create and Modify Tables •Working with Tables •Form Designing •Report Designing

RBMS/RDBMS using SQL

Introduction to MS Access

•SQL Management Studio •Utilizing the Object Explorer

Getting started

•Schema –Meta Data –ER Diagram •Looking at an example Database design •Data Integrity Constraints & types of Relationships •Basic concepts – Queries, Data types & NULL Values, Operators and Comments in SQL •Rest of the story – Joins, Indexes, Functions & Views

Understanding basic RDBMS concepts

•Basic Select statement •Additional components – Where, Group By, Order by & Having clauses

Data manipulation – Reading & Manipulating a Single Table

•Creating, Modifying & Deleting Tables •Create Table & Create Index statements •Insert, Update & Delete statements •Drop & Truncate statements – Uses & Differences •Alter Table & alter Column statements

Data based objects creation(DDL Commands)

•Sub-queries vs. Temp Tables vs. Joins •Stored Procedures •Optimizing for Composite keys & Non-numeric Primary keys

Optimizing your work

•Creating a 360 Deg. Customer view Data manipulation – Case Study-1

•Deciling using the n-tile function •Row number & Partition by clause

Data manipulation - Case Study -2

What is SQL – A Quick Introduction

•Introduction to Tableau & Architecture•Symbol Map & Filled Map •Custom Geo Coding

Getting started

• Analytics World• Introduction to Analytics• ETL concept and role of SAS in ETL• SAS in advanced analytics• SAS Certification: Induction and walk through

Introduction To Data Science – SAS

• Use formatted and list input to read raw data files.• Use infile statement options to control processing when reading raw data files• Use various components of an input statement to process raw data files including column and line pointer controls, and trailing @ controls.• Combine SAS data sets.• Access an Excel workbook.

Accessing Data

• Create temporary and permanent SAS data sets.• Create and manipulate SAS date values.• Export data to create standard and Comma delimited raw data files.• Control which observations and variables in a SAS data set are processed & output.

Creating Data Structures

• Identify and resolve programming logic errors.• Recognize and correct syntax errors.• Examine and resolve data errors.

Handling Errors

• Generate list reports using the print procedure.• Generate summary reports and frequency tables using base SAS procedures.• Enhance reports through the use of user-define formats, titles, footnotes and SAS System reporting.• Generate reports using ODS statements.

Generating Reports

• Investigate SAS data libraries using base SAS Utility procedures.• Sort observations in a SAS data set.• Conditionally execute SAS statements.• Use assignment statements in the data step.• Modify variable attributes using options and Statements in the data step.• Accumulate sub-totals and totals using data step statements.• Use SAS functions to manipulate character data, numeric data, and SAS date values.• Use SAS functions to convert character data to numeric and vice versa.• Process data using do loops.• Process data using one-dimensional SAS arrays.• Validate and clean data.

Managing Data

•Working with aggregate versus disaggregate•My Tableau Repository •Connecting to Data sources •Understanding the Tableau workspace •Dimensions and Measures •Data Types & Default Properties •Tour of Shelves & Marks Card •Building basic views •Saving and Sharing your work-overview

Calculated Fields

•Explain latitude and longitude •Default location/Edit locations data •Explain - #Number of Rows •Basic Functions (String, Date, Numbers) •Usage of Logical conditions •Explain scope and direction of table calculations •Percent of Total, Running / Cumulative calculations

Maps

• Multiple Table Join • Data Blending • Difference between joining and blending data, and when we should do each

Working with Data

•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, Dual Axes & Combination chart •Parts of Views

Basic Data Analysis•Create What-If analysis •Using Parameters in •Calculated fields •Bins •Reference Lines •Filters/Sets •Display Options (Dimension/Measure Selection

Parameters

• Combining multiple visualizations into a dashboard (overview) • Making your worksheet interactive by using actions Filter/URL /Highlight • Options in Formatting your Visualization • Working with Labels and Annotations • Effective Use of Titles and Caption

Building & customizing Dashboards

Visual Analytics using Tableau

BASE SAS

•Sorting •Trend lines •Reference Lines •Forecasting •Filters •Context filters •Sets

Data Manipulation•In/Out Sets •Combined Sets •Grouping •Bins/Histograms •Drilling up/down – drill through •Hierarchies/View data •Actions (across sheets)

• Working with the Data Engine / Extracts • Working with Custom SQL • Toggle between to Direct Connection and Extracts