DATA ANALYTICS TRAINING

DATA ANALYTICS

Data analytics is the process of examining data sets in order to draw conclusions about the information they contain, increasingly with the aid of specialized systems and software. Data Analytics allows you to view statistical information about unstructured data, such as files and emails. With this information, you can quickly assess the state of your big data sources, take actionable steps to retrieve valuable storage space, and mitigate the risk of compliance-related issues.

ADVANCED EXCEL

Advanced Excel Course – Overview of the Basics of Excel

  • Customizing common options in Excel
  • Absolute and relative cells
  • Protecting and un-protecting worksheets and cells

Advanced Excel Course – Working with Functions

  • Writing conditional expressions (using IF)
  • Using logical functions (AND, OR, NOT)
  • Using lookup and reference functions (VLOOKUP, HLOOKUP, MATCH, INDEX)
  • VlookUP with Exact Match, Approximate Match
  • Nested VlookUP with Exact Match
  • VlookUP with Tables, Dynamic Ranges
  • Nested VlookUP with Exact Match
  • Using VLookUP to consolidate Data from Multiple Sheets

Advanced Excel Course – Data Validations

  • Specifying a valid range of values for a cell
  • Specifying a list of valid values for a cell
  • Specifying custom validations based on formula for a cell
  • Advanced Excel Course – Working with Templates
  • Designing the structure of a template
  • Using templates for standardization of worksheets

Advanced Excel Course – Sorting and Filtering Data

  • Sorting tables
  • Using multiple-level sorting
  • Using custom sorting
  • Filtering data for selected view (AutoFilter)
  • Using advanced filter options

Advanced Excel Course – Working with Reports

  • Creating subtotals
  • Multiple-level subtotals
  • Creating Pivot tables
  • Formatting and customizing Pivot tables
  • Using advanced options of Pivot tables
  • Pivot charts
  • Consolidating data from multiple sheets and files using Pivot tables
  • Using external data sources
  • Using data consolidation feature to consolidate data
  • Show Value As ( % of Row, % of Column, Running Total, Compare with Specific Field)
  • Viewing Subtotal under Pivot
  • Creating Slicers ( Version 2010 & Above)

Advanced Excel Course – More Functions

  • Date and time functions
  • Text functions
  • Database functions
  • Power Functions (CountIf, CountIFS, SumIF, SumIfS)

Advanced Excel Course – Formatting

  • Using auto formatting option for worksheets
  • Using conditional formatting option for rows, columns and cells
  • Advanced Excel Course – Macros
  • Relative & Absolute Macros
  • Editing Macro’s

Advanced Excel Course – WhatIf Analysis

  • Goal Seek
  • Data Tables
  • Scenario Manager

Advanced Excel Course – Charts

  • Using Charts
  • Formatting Charts
  • Using 3D Graphs
  • Using Bar and Line Chart together
  • Using Secondary Axis in Graphs
  • Sharing Charts with PowerPoint / MS Word, Dynamically
  • (Data Modified in Excel, Chart would automatically get updated)

=======================================================================

VISUAL BASIC FOR APPLICATIONS (VBA MACROS)

Introductions to VBA Macro

Segregate the notes as:-

Sheet related Activities

  • Simple data
  • If statements
  • Tool box controls
  • Loops
  • Multiple loops
  • Data filtering & populating to listbox
  • Data filtering & populating to another column in the same sheet
  • Font related activities
  • Font color activities
  • Cells formatting activities
  • Cells fill color activities
  • Sheets renaming, protecting
  • Sheets sorting
  • Data sorting
  • Blank rows, columns deleting
  • Hide, unhide rows, cols
  • Workbook save, delete activities
  • Range, cells controlling

Multiple sheets data activities

Chart related activities

Msgbox related activities

Other applications related activities

Transferring data from excel to word, access, pdf files

Administrator related activities

Pivot Tables and Pivot Chart Related Programs

=======================================================================

STRUCTURED QUERY LANGUAGE (SQL)

  • Introduction to SQL
  • Retrieving Data
  • Updating Data
  • Inserting Data
  • Deleting Data
  • Sorting and Filtering Data
  • Advanced Filtering
  • Summarizing Data
  • Grouping Data
  • Using Sub queries
  • Joining Tables
  • Managing Tables
  • Using Views
  • Stored Procedures
  • Using Cursors
  • Using Transactions

====================================================================

MICROSOFT BUSINESS INTELLIGENCE (MSBI)

Introduction to SQL Server Integration Services

  • SSIS Framework/Architecture
  • SSIS Package Architecture Overview
  • Development and Management Tools
  • The Import and Export Wizard

Introduction to Data Flow

  • Data Flow Overview
  • Data Sources
  • Data Destinations
  • Data Flow Transformations
  • Data Viewers

Data Sources

  • Excel Source
  • Flat File Source
  • OLE DB Source
  • XML Source

Data Flow Transformations

  • Aggregate Transformation
  • Audit Transformation
  • Character Map Transformation
  • Conditional Split Transformation
  • Copy Column Transformation
  • Derived Column Transformation
  • Data Conversion Transformation
  • Multicast Transformation
  • OLE DB Command Transformation
  • Percentage Sampling Transformation
  • Row Count Transformation
  • Sort Transformation
  • Union All Transformation

Advanced Data Flow Transformations

  • Lookup Transformation
  • Merge Transformation
  • Merge Join Transformation
  • Slowly Changing Dimension Transformation
  • Pivot Transformation
  • Export Transformation
  • Import Transformation
  • Unpivot Transformation

Data Flow Destinations

  • Data Reader Destination
  • Excel Destination
  • Flat File Destination
  • OLE DB Destination

Introduction to Control Flow

  • Control Flow Overview
  • Precedence Constraints
  • The Execute SQL Task
  • The Bulk Insert Task
  • The File System Task
  • The FTP Task
  • The Send Mail Task
  • Bulk Insert Task
  • Data Flow Task
  • Execute DTS 2000 Package Task
  • Execute Package Task
  • Execute Process Task
  • Web Service Task
  • XML Task

Advanced Control Flow

  • For Loop Container
  • For Each Loop Container
  • Sequence Container

Variables and Configurations

  • Variables Overview
  • Variable scope
  • SSIS system variables
  • Using variables in control flow
  • Using variables in data flow
  • Using variables to pass information between packages
  • Property expressions
  • Configuration Overview
  • Configuration options
  • Configuration discipline

Debugging, Error Handling and Logging

  • SSIS debugging overview
  • Configuring Package Logging
  • Breakpoints in SSIS
  • Event handler in SSIS
  • Configure Error output in Data Flow
  • Data Viewers
  • Configure Check Points
  • Transactions in SSIS
  • SSIS Security

Extending SSIS through Custom Code – Scripting

  • Introduction to SSIS scripting
  • The SSIS script editor
  • The SSIS object model
  • Script in Control flow: The Script Task
  • Script in Data flow: The Script Component
  • Introduction to SSIS component development

SSIS Deployment

  • Create Configuration Files
  • Implement Logging
  • Different Deployment Options
  • Executing packages using utilities – DTExec and DTExecUI
  • Schedule SSIS Packages with SQL Server Agent Jobs
  • Monitoring Jobs in SQL Server
  • Call SSIS Packages in Stored Procedures
  • Call SSIS Packages in command prompt

SQL Server Reporting Services (SSRS) Architecture

  • Architecture of SSRS
  • Components in SSRS
  • Phases of Report Development Life Cycle
  • Report types and formats
  • SSRS Web Services
  • Report Server
  • Report Builder
  • Model Designer
  • Data sources: SQL Server, Oracle, OLE DB

Creating basic reports

  • Tabular
  • List
  • Matrix
  • Chart
  • Constructing data sources and Datasets
  • Inserting a data region, fields and images

Report Authoring

  • Table Reports and Matrix Reports
  • Report Designer Paging Options
  • Drill-down Reports
  • Chart Reports, Indicator, Gauge Control Reports
  • List Reports
  • Use Page Header and Page Footer
  • Drill-Through or Linked Reports
  • Sub Reports
  • Create Report Template and use it in new Reports
  • Parameter Reports
  • Single Parameter
  • Multiple Parameters
  • Cascading Parameter
  • Create Calendar in Parameters
  • Default Values and Drop Down list options
  • Expressions in SSRS
  • Understand conditional formatting
  • Adding images
  • Grouping and Sorting
  • Page Header and Page Footer
  • Aggregate Functions
  • Sub Totals and Grand Totals
  • Document Map Reports
  • Create Reports with Stored Procedures
  • Create Reports with SSAS Cube

Deploying reports to the server

  • Report Server Configuration
  • Configure Report Server details in SSRS Project
  • Report Deployment to Report Server

Creating reports with Report Builder

  • Deploying Report Builder to users
  • Dragging and dropping entities onto charts and tabular reports
  • Navigating data with infinite drill through

Report Manager and Delivery

  • Report Builder to create ad-hoc Reports
  • Create Linked Reports
  • Create Subscriptions
  • Standard Subscriptions
  • Data Driven Subscriptions
  • Cache a Report
  • Snapshot a Report
  • Report Logging

Introduction to SSAS

  • Brief Introduction to SSAS
  • Using SSAS in BIDS
  • Understanding BIDS
  • Creating Data Sources
  • Creating Data Source Views
  • Creating a Cube Using the Wizard
  • Refining Dimensions and Measures

Intermediate SSAS

  • Creating KPIs
  • Creating Perspectives
  • Creating Translations
  • Creating Actions
  • Advanced SSAS
  • Working with Multiple Fact Tables
  • Using Advanced Dimension Types
  • Working with Changing Dimensions
  • Using the Business Intelligence Wizard

Cube Designing and development

  • Different Ways to Create CUBE
  • Measures and Measure Groups
  • Diff between BUILD, DEPLOY and PROCESS
  • Calculated Columns

Hierarchies

  • Creating and Managing Hierarchies
  • Translations

Beginning MDX

  • Understanding MDX
  • About  Members, Tuples and Sets
  • Common MDX Functions Explained
  • New or Updated MDX Functions or Keywords

Intermediate MDX

  • Understanding the Calculations Sub tab
  • Adding Calculated Members
  • Adding MDX Scripts
  • Adding Named Sets

SSAS Administration

  • Implementing SSAS Security
  • Implementing XMLA Scripts
  • Understanding SSAS Backup and Restore
  • Understanding Performance Optimization

Introduction to SSAS Clients

  • Using Excel 2007 Pivot Tables
  • Using SQL Server Reporting Services

Partitions-Aggregations

  • What are Partitions and How to create Partitions?
  • On what basis a Partition are created?

 Actions, Calculations, Perspectives and KPIs

  • What are Actions and How to create IT?
  • Understand KPIs and Create KPIs
  • Understand Calculations and Create Calculations
  • Creating Perspectives

Deploying the Cube

  • Generate XMLA Script and Deploy a Cube in SQL Server
  • Create Jobs to process the cube in SQL Server
  • Different Approaches to Deployment
  • Client Interactions with Excel

=======================================================================

MICROSOFT POWER BI 

Power BI

  • Download and Install BI Desktop
  • Add, Rename, Duplicate, and Delete Pages
  • Connect to SQL Server
  • Connect to Multiple Excel Sheets
  • Get Data from Excel Files
  • Get Data from Text Files
  • Load Data from Multiple Data Sources
  • Remove Unwanted Columns from Tables

Power BI Transformations

  • Change the Data type of a Column
  • Combine Multiple Tables
  • Clusters
  • Enter data or Copy & Paste data from Clipboard
  • Format Dates
  • Groups
  • Hierarchies
  • Joins
  • Pivot Table
  • Query Groups
  • Reorder or Remove Columns
  • Rename Column Names
  • Rename Table Names
  • Split Columns
  • UnPivot Table

Power BI Charts Tutorial

  • Area Chart
  • Bar Chart
  • Card
  • Clustered Bar Chart
  • Clustered Column Chart
  • Column Chart
  • Donut Chart
  • Funnel Chart
  • Heat Map
  • Line Chart
  • Clustered Column and Line Chart
  • Line and Stacked Column Chart
  • Matrix
  • Multi-Row Card
  • Pie Chart
  • Ribbon Chart
  • Stacked Area Chart
  • Scatter Chart
  • Stacked Bar Chart
  • Stacked Column Chart
  • Table
  • Tree Map
  • Waterfall Chart
  • 100% Stacked Bar Chart
  • 100% Stacked Column Chart
  • Map
  • Filled Map
  • Format Area Chart
  • Format Bar Chart
  • Formatting Card
  • Format Clustered Bar Chart
  • Format Clustered Column Chart
  • Formatting Column Chart
  • Format Donut Chart
  • Format Funnel Chart
  • Formatting Line Chart
  • Format Line & Clustered Column
  • Format Line & Stacked Column
  • Formatting Matrix
  • Format Multi-Row Card
  • Format Pie Chart
  • Formatting Ribbon Chart
  • Format Stacked Area Chart
  • Formatting Scatter Chart
  • Format Stacked Bar Chart
  • Formatting Stacked Column Chart
  • Format Table
  • Format Tree Map
  • Formatting Waterfall Chart
  • R Script
  • Format Map
  • Format Filled Map

Power BI Filters Tutorial

  • Slicer
  • Basic Filters
  • Advanced Filters
  • Top N Filters
  • Filters on Measures
  • Page Level Filters
  • Report Level Filters
  • Drill through Filters

Power BI Tutorial on Calculated Fields

  • Calculated Columns
  • Conditional Columns
  • Calculated Measures
  • Calculated Tables
  • Custom Columns

Power BI Tutorial on Dashboards

  • Register to Pro Service
  • Dashboard Introduction
  • Connect Desktop with BI Service or Pro
  • Publish Desktop Reports
  • Create a Workspace
  • Create a Dashboard
  • Favourites

Working with Power BI Dashboards

  • Dashboard Actions
  • Add Reports to a Dashboard
  • Add Title to Dashboard
  • How to Add Image to Dashboard
  • Add Video to Dashboard
  • Add Web Content to Dashboard
  • Dashboard Settings
  • Delete a Dashboard
  • Pin Report to a Dashboard

Sharing Power BI Work

This Power Bi tutorial section covers the sharing of Work. I means, sharing reports, Dashboards, apps, Workspaces etc.

  • Share a Dashboard
  • Share a Report
  • Sharing Workspace
  • Publish App
  • View Published App

Power BI DAX Tutorial

  • Aggregate Functions
  • Date Functions
  • Logical Functions
  • Math Functions
  • String Functions
  • Trigonometric Functions

=======================================================================

TABLEAU

Introduction To Tableau

  • Defining Tableau
  • Components Of A Computing Cloud
  • Differentiating Types Of Clouds: Public, Private, Hybrid
  • Delivering Services From The Cloud
  • Categorizing Service Types
  • Comparing Vendor Cloud Products: Amazon, Google, Microsoft And Others

Adopting The Cloud

  • Key Drivers Of Tableau Solutions
  • Instantaneous Provisioning Of Computing Resources
  • Handling Varied Loads With Elasticity And Seamless Scalability
  • Tapping Into An Infinite Storage Capacity
  • Cost-Effective Pay-As-You-Use Billing Models
  • Evaluating Barriers To Tableau
  • Handling Sensitive Data
  • Aspects Of Cloud Security
  • Assessing Governance Solutions

Exploiting Software As A Service ( SaaS )

  • Characterizing SaaS
  • Minimizing The Need For Local Hardware And Software
  • Streamlining Administration With Centralized Installation And Updates
  • Optimizing Cost And Performance With The Ability To Scale On Demand
  • Comparing Service Scenarios
  • Improving Collaboration With Business Productivity Tools
  • Simplifying Business Process Creation By Integrating Existing Components
  • Inspecting SaaS Technologies
  • Deploying Web Applications
  • Implementing Web Services: SOAP, REST
  • Choosing A Development Platform

Delivering Platform As A Service (PaaS)

  • Exploring The Technical Foundation For PaaS
  • Specifying The Components Of PaaS
  • Analyzing Vendor PaaS Provisions
  • Selecting An Appropriate Implementation
  • Building Services With Solution Stacks
  • Evaluating The Architecture Of Vendor Specific Platforms
  • Becoming Familiar With Service Platform Tools
  • Leveraging The Power Of Scalable Middleware
  • Managing Cloud Storage
  • Controlling Unstructured Data In The Cloud
  • Deploying Relational Databases In The Cloud
  • Improving Data Availability
  • Employing Support Services
  • Testing In The Cloud
  • Monitoring Cloud-Based Services
  • Analyzing Portability Across Platforms

Deploying Infrastructure As A Service ( IaaS )

  • Enabling Technologies
  • Scalable Server Clusters
  • Achieving Transparency With Platform Virtualization
  • Elastic Storage Devices
  • Accessing IaaS
  • Provisioning Servers On Demand
  • Handling Dynamic And Static IP Addresses
  • Tools And Support For Management And Monitoring

Building A Business Case

  • Calculating The Financial Implications
  • Analyzing Current And Future Computing Requirements
  • Comparing In-House Facilities To The Cloud
  • Estimating Economic Factors Downstream
  • Preserving Business Continuity
  • Selecting Appropriate Service-Level Agreements
  • Safeguarding Access To Assets In The Cloud
  • Security, Availability And Disaster Recovery Strategies

Migrating To The Cloud

  • Technical Considerations
  • Rearchitecting Applications For The Cloud
  • Integrating The Cloud With Existing Applications
  • Avoiding Vendor Lock-In
  • Planning The Migration
  • Incremental Vs
  • One-Step Solution
  • Selecting A Vendor
  • Establishing Staff Skill Requirements

====================================================================

QLIKVIEW

INTRODUCTION

  • What Is Qlikview?
  • Why Qlikview?
  • Unique Features Compared To Traditional BI Tools
  • Complete Dataware Housing Basics

QLIKVIEW PRODUCTS

  • Desktop
  • Server
  • Publisher
  • AccessPoint

ROLES INVOLVED

  • Developer
  • Designer
  • Administrator

DEPLOYMENT PROCESS

  • Standalone
  • Server
  • Publisher

INTRODUCTION TO DATA AND SCRIPTING

  • Relational Databases
  • Dimensional Data Structures
  • Comparing Data Models To QlikView Data Model
  • Data Structures In QlikView
  • Data Source Files
  • The QWT Primary Data Source
  • The QWT Secondary Data Files
  • Edit Script Explanation

LOADING DATA FROM THE DATABASE

  • Script Generation
  • Comments In The Script
  • Script Debugging

STRUCTURING THE SCRIPT

  • Creating Tabs In The Script

BASIC DATA MODEL AND TABLE VIEWER

  • The Table Viewer
  • The System Table
  • Explanation Of Document Properties

BASIC DATA TRANSFORMATION

  • Load Data From Multiple Data Sources
  • Renaming A Field
  • Script Expressions
  • Loading An XML File
  • Renaming Fields Using The Qualify Statement
  • Key Fields
  • Loading Generic Tables
  • Including Images And Media Content
  • Loading A Field Into A Table Multiple Times
  • Using A Record Counter On Key Fields
  • Loading Cross Tables And Info Tables
  • Handling Null Values
  • Changing Date Formats
  • Changing Metadata
  • Complete Discussion About Concatenation
  • Resolving Synthetic Keys
  • Resolving Loops
  • Preparing QVD Files
  • Performing Incremental Loads
  • Optimization Techniques At Script Level

=======================================================================

R PROGRAMMING

Essential to R Programming

  • An Introduction To R
  • Introduction To The R Language
  • Programming Statistical Graphics
  • Programming With R
  • Simulation
  • Computational Linear Algebra
  • Numerical Optimization

Data Manipulation Techniques Using R Programming

  • Data In R
  • Reading And Writing Data
  • R And Databases
  • Dates
  • Factors
  • Subscribing
  • Character Manipulation
  • Data Aggregation
  • Reshaping Data

Statistical Applications Using R Programming

  • Basics
  • The R Environment
  • Probability And Distributions
  • Descriptive Statistics And Graphics
  • One- And Two-Sample Tests
  • Regression And Correlation
  • Analysis Of Variance And The Kruskal–Wallis Test
  • Tabular Data
  • Power And The Computation Of Sample Size
  • Advanced Data Handling
  • Multiple Regression
  • Linear Models
  • Logistic Regression
  • Survival Analysis
  • Rates And Poisson Regression
  • Nonlinear Curve Fitting

=======================================================================

STATISTICAL ANALYSIS SYSTEM (SAS)

  • Introduction To SAS System & Architecture
  • History And Various Modules
  • Features
  • Variables & SAS Syntax Rules
  • SAS Data Sets
  • Data Set Options
  • Operators
  • Reading Raw Data
  • Infile Statement With Options
  • Working With External Files & Options
  • Multiple Observations
  • Input Styles
  • Select Statement
  • Leave and Continue Statements
  • Creating & Redefining Variables
  • Where Statement
  • If – Then Else Statement
  • Goto, Stop And Error Statements
  • Output Statement, Put Statement
  • Do Loops
  • Modifying And Combining Data Sets
  • Updating Master Data Set
  • Display Manager Commands
  • SAS Functions
  • An Introduction To Arrays And Array Processing
  • Overview Of Methods For Combining SAS Data Sets

BASE SAS PROCEDURES

  • Proc sort
  • Proc Print
  • Proc Means
  • Proc Freq
  • Proc Plot
  • Proc Chart
  • Proc Copy
  • Proc Summary
  • Proc Append
  • Proc Datasets
  • Proc Contents
  • Proc Delete
  • Proc Format
  • Proc Import
  • Proc Export

=======================================================================

STATISTICAL ANALYSIS SYSTEM (ADVANCED SAS)

SAS/SQL

  • Introduction To SAS/ SQL
  • Features
  • Uses
  • Terminology
  • Data Types, Key Words, & Operators
  • Functions, Predicates
  • Formatting Output
  • Group By Clause
  • Order By Clause
  • Having Clause
  • Case Expression And Conditional Logic.
  • Creating ,Populating & Deleting Tables
  • Alter Table Statement
  • Changing Column’s Length
  • Joins
  • Constraints
  • Renaming A Table & Columns
  • Views

SAS/MACROS

  • Macro Concepts
  • Macros And Macro Variables
  • Creating Macro Variables
  • Using Macro Variables
  • Creating Modular Code With Macros
  • Invoking A Macro
  • Adding Parameters To Macros
  • Macros With Conditional Logic
  • Using Various Procedures In Macros
  • Automatic Variables
  • Macro Functions
  • Including External Macros

SAS/ODS

  • Creating RTF File
  • Creating Html File
  • Creating Pdf File

SAS/GRAPH

  • Plot Procedure
  • Multiple Plots & Overlay
  • Symbol Statement
  • Title And Footnote Statements

SAS/REPORTS

  • Frequency Report
  • One-Way Frequency Report
  • Cross Tabular Frequency Report
  • Summary Statistics
  • Creating A List Report
  • Define Statement
  • Order Usage and Group
  • Printing Grand Totals
  • Rbreak Statement
  • Tabulate Procedure
  • One-Dimensional Tables
  • Two-Dimensional Tables
  • Obtaining A Total
  • Analysis Variables with options
  • Summary Statistics

SAS/STAT

  • Proc Univariate
  • Proc Corr
  • Proc Reg
  • Proc Anova

Leave a Reply

Your email address will not be published. Required fields are marked *

Enquiry

Give us a call or fill in the form below and we will contact you. We endeavor to answer all inquiries within 24 hours on business days.