CERTIFICATION OF ADVACNED STUDIES

DATA ANALYSIS / BUSINESS ANALYSIS

Accredited with
ALUMNUS STATUS
ExcelR’s
Certificate of
Advanced
Studies – Data
Analysis /
Business
Analysis

Intro Data Analysis

Data Analytics provides a subtle way to analyze the data in a qualitative and
quantitative manner to draw logical conclusions. Gone are the days where one has
to think about gathering the data and saving the data to form the clusters. For the
next few years, it’s all about Data Analytics and it’s techniques to boost the modern
era technologies such as Machine learning and Artificial Intelligence.

ExcelR’s Certificate of Advanced Studies – Data Analysis / Business Analysis

program curriculum provides extensive knowledge of Data Collection, Extraction,
Cleansing, Exploration, and Transformation. Alongside the Data Mining, Data
Integration is done with feature Engineering to build Prediction models for Data
Visualization and deploying the solution. You name the skillset and our trainers are
always there to handle the new generation tools with latest versions. As a part of
the Data Analytics training, the range of skills and tools that are emphasized in the
course include Statistical Analysis, Text Mining, Regression Modelling, Hypothesis
Testing, Predictive Analytics, Machine Learning, Deep Learning, Neural Networks,
Natural Language Processing, Predictive Modelling, R Studio, Tableau, Spark,
Hadoop, programming languages like R and Python.

PRE-REQUISITES
Graduate in commerce, science and engineering any stream, working
professional from any domain with logical, analytical skills.

Aspirant with good analytical abilities / background - Finance and accounting,

management domain
“Steinbeis University, Berlin Accreditation, “It is continuing faithfulness in the
founding and development of this institution and outstanding competence,
and excellence in deliveries”.

Steinbeis University Berlin works as a knowledge and technology transfer

partner to companies in trade and industry. Its core services encompass:
research and development, consulting, and – as a basis for all of this –
education. Founded in 1998, the Steinbeis University Berlin (German
abbreviation: SHB) offers executive degrees and employee training and
development programs matched to the needs of ‘knowledge and technology
transfer’ – that is ideally suited to the requirements of modern, knowledge-
based society.

Steinbeis University aims to increase competitiveness among the students

and the companies beyond the pure knowledge transfer and application.

Steinbeis accreditation recognizes ExcelR’s excellence in areas such as

curriculum, faculty qualifications, support services, institutional effectiveness,
planning and learning resources and student learning outcomes.
 Topics What We
Cover:

Introduction/basics • Nested V-lookup with Exact Match

• Excel: Basics to Advanced • V-lookup with Tables, Dynamic Ranges
• SQL • Nested V-V-lookup with Exact Match
• Tableau • Using V-lookup to consolidate Data from
• Power BI Multiple Sheet
• SAS • Mathematical Functions
• R Basic • SumIf, CountIf, AverageIf etc
• Python Basics • Date & Time Function

Advanced Excel –Basic Advanced Excel -Pivot Tables

• Customizing the Ribbon • Creating Simple Pivot Tables
• Worksheets • Basic and Advanced value field setting
• Format Cells • Grouping Based on number and Date
• Various selection techniques • Calculated field and Calculated items
• Shortcuts Keys
• Protecting and un-protecting worksheets Advanced Excel –Charts & Slicers
• Using Charts; Formatting Charts
Advanced Excel -Sorting and Filtering Data • Using 3D Graphs
• Sorting tables • Using Bar and Line Chart together
• Using multiple-level sorting • Using Secondary Axis in Graph
• Using custom sorting • Sharing Charts with PowerPoint / MS Word,
• Filtering data for selected view (AutoFilter) Dynamically
• Using advanced filter options
Advanced Excel -Working with Templates
Advanced Excel -Data Validations • Designing the structure of a template
• Specifying a valid range of values for a cell • Using templates for standardization of
• Specifying a list of valid values for a cell worksheets
• Specifying custom validations based on
formula for a cell Advanced Excel -VBA-Macro
• Introduction to VBA; What is VBA ?
Advanced Excel -Text Function • What can you do with VBA ?
• Upper, Lower, Proper; Left, Mid, Right; • What can you do with VBA ?
• Trim, Len, Exact • Procedures and Function in VBA
• Concatenate
Advanced Excel -Variable in VBA
Advanced Excel -Function & Formula • What is Variables ?
• Basic Function –Sum, Average, Max, Min, • Using Non-declared variables; Variable Data
Count, Count A Types
• Conditional Formatting
• Logical functions (AND, OR, NOT) Advanced Excel -Message-Box and Input-box
• Lookup and reference functions (VLOOKUP, functions
HLOOKUP, MATCH, INDEX) • Customize Message-Box and Input-box
• V-lookup with Exact Match, Approximate • Reading cell values into messages
Match • Various button groups in VBA
 Topics What We
Cover:

▪ Key Constraints
VBA Coding Advanced function ▪ Domain Constraints
• If and Select statement ▪ Referential Integrity
• Looping in VBA ▪ Types Of Constraints
• Mail Function –send automated email ▪ Primary and Foreign Keys
• Automated report will be shown ▪ Application of Indexes
▪ Checking Constraints
SQL ▪ Alter Tables
• Introduction to Databases
• Databases; Introduction to DBMS SQL Transactions
• Popular DBMS Software • Examples
• Concepts of RDBMS • ACID Properties
• Tables • TCL Statements
• Tuples • Start, Commit and Rollback Statements
• Attributes • Auto Commit
• Normalization • Save Points
• First Normal Form • Identifier
• Second Normal Form • Rollback and Release
• Third Normal Form
• NoSQL Database Database Objects
• Types of NOSQL • Tables
• Comparison • Creating, Altering and dropping tables
• Sequences
SQL Commands • Auto Increments
• Types of SQL Commands • Re-Sequencing
• Data Definition Language • Views
• Create, Drop, Truncate, Alter and Rename • Advantages
Objects • Creating and Dropping Views
• Data Query Language • Indexes
• Select Statements • Types of Indexes
• Data Manipulation Language • B-Tree and Hash Indexes
• DCL and TCL • Creating and dropping Indexes
• Grant, Revoke and transaction statement
• SQL Data Types; Numeric, Date and Time, Stored Procedures and Functions
LOB Types • Stored Objects
• Types of Stored Objects
DML Commands • Stored Procedures
• Insert, Update and Delete Statements • Create, call and drop stored procedures
• DDL Commands • Using Variables
• Create and Drop Databases • Handling Exceptions
• Named Errors and Re-signals
Database Constraints • Programming
▪ Types of Constraints • If-then-Else and Case Statements
▪ Relational Integrity Constraints • Loops
 Topics What We
Cover:

• Repeat and Leave Statements Tableau –Data Visualization Tool

• Cursors • Introduction to Tableau
• Operators and Functions • What is Tableau
• Joining Tables • Overview Of Tableau Tool(Servers , data ,
• Inner Join, Left Join, Right join visualizations)
• Advantages of Procedures • Tableau Architecture
• Advantages & Disadvantages
Database Triggers Accessing Database
From R and Python • Different Products of Tableau
• Triggers • Tableau Desktop
• Database Triggers • Tableau Public
• Data Definition Language (DDL) Triggers • Tableau Prep
• Data Manipulation Language (DML) • Tableau Online
Triggers • Tableau Server
• CLR Triggers • Tableau Analytics
• Logon Triggers
• Triggers v/s Stored Procedures Extensions in Tableau
• Accessing Database from R • Tableau Workbook
• Install R Packages • Tableau Data Source
• Configuration Information • Tableau Data Extract
• Python Database Access • Tableau Packaged Workbook (TWBX)
• Databases Supported • Tableau Packaged Data Source (TDSX)
• Libraries • Tableau Book Mark
• Read Operations • Tableau Map Source
• Insert, Update and Delete • Tableau Preferences
• Performing Transactions
• Handling Errors
Features of Tableau Desktop
Tableau • Connecting to Data from servers
• What is Data Visualization • Connecting Data from ODBC
• Advantages & Disadvantages of • Connecting data from local repositories
visualizations
Tableau-Joins and Data Pane
Age of Big data ▪ What are Joins in Tableau
• Why Data visualization Important ▪ Types Of Joins in Tableau
• Understanding data ▪ Inner Join
• Examples of Data visualizations in Action ▪ Left Join
• Different data visualizations ▪ Right Join
▪ Full Outer Join
Principles of Visualizations ▪ Union
• Design Principles ▪ Creating Joins Using Data
• Best Practices
• Data Viz Inspiration
 Topics What We
Cover:

Data Interpretation
Tableau Data Pane
• Understanding of data types, discussing
• Dimensions
about dimensions and measures etc.
• Measures
• Creating Calculated Fields
• Parameters
• Attribute functions
• Sets
• Quick table calculations
Pivot Table and Split Tables in Tableau
Creation of calculated fields
• In built Charts in Tableau
• Aggregate and disaggregate functions etc.
• Basic Charts
• Text Tables
Logical Functions
• Highlight Tables
• Understanding If-else statements,
• Bar charts
applications of if-else statements (eg: high
• Stacked Bar
profit , low profit etc.)
• Line Graphs
• Dual axis
Case-If Function
• Pie chart etc.
• Understanding Case Statements with
examples
Maps in Tableau
• Applications of case statements
• Symbol Maps
• Filled maps
ZN Function
• Combined maps
• Creation of ZN functions
• Map layers
• Application of ZN functions
• WMS
• Dealing with calculated fields etc.
• Polygon Maps
• Custom coding etc.
• How to Interpret Bullet Graphs
Ad-Hoc Calculations
• Actual Profit vs Budget Profit Analysis
• Calculations using parameters
• Market wide Analysis etc
• Sets
• Filters etc and applications
How to Interpret Scatter Plot
• Correlation Analysis
Manipulating Text-Left & Right Functions
• Direction of Relationship
• Understanding different string functions etc.
• Strength of Relationship etc.
Pre-Defined Analytics
How to Interpret Histogram
• Forecasting ,LOD Expressions , Functions,
• Distributive Analysis, Bin Sizes, Custom bin
Groups, Filters, etc
sizing etc.
Dashboards Hands-On in Tableau
How to Interpret Box Plot chart
• Understanding concept of Dashboards
• Distributive Analysis, Quartile Analysis, 5
• Building Interactive dashboards
Point Chart Analysis
• Dashboard actions, etc.
 Topics What We
Cover:

Story Hands-On in Tableau Basics of Python

• Relevance of stories in Dashboards, • Introduction to Python & Data Science
Working with examples on stories etc. Python Installation on laptops
• Python Basics -Variables -Python Build in
Animated Visualization Hands-On functions -Modules -Python Libraries
• Animation charts, play controls, page shelf, installation using PIP
applications of animation charts • Python Basics -Python Operators
• Python Basics -Flow Control Statements -
Tools for Sharing Information If Statements -While Loops
• Understanding Tableau Reader, Tableau • Python Basics -Data & time modules in
online etc. python -Interfaces in Python -For Loops
• Publishing our Workbooks in Tableau
Server Python modules for Data Analysis
• Exploring publishing options using Tableau • Python Basics –Web scrapping-Python
• Discussing sharing of workbooks, etc. custom functions -Lambda Function -
Regular Expressions
Connecting Tableau with Tableau Server • Data science Life cycle –Numpy Module
• Overview of how to connect Tableau with • Data science Life cycle –Pandas Module
Tableau server. • Data science Life cycle –Matplolib Module

What is R? Power BI (E-Learning Module)

• R software, installation , R studio , • Introduction to Power BI Preview
Understanding basic interface of R . • Download the Training Data Files
• Introduction to Signing Up for Power
Connecting Tableau with R • Signing up for Power BI Preview
• R serve package, using functions such as • Load Data into the Power BI Service
SCRIPT_REAL etc, Understanding with Preview
examples. • Practical Activity

How to integrate Tableau with R? The Power BI Desktop

• R serve package, external connections, • Intro to Power BI Desktop Section
ports, Understanding with examples. • Introduction to the Power BI Desktop
Preview
Basics of R • Creating Reports in Power BI Desktop
• Introduction of R
• Data Types Create Reports in Power BI Desktop
• Data Structures Section
• Decision Making Statements • Creating Tables in Power BI
• Conditional Loops • Table Styles and Formatting Preview
• Flow Control Statements • Matrix Visualization 06:11
• If Statements • Tables and Metrics Practical Activity
• For loops • Answers to Tables and Metrics Practical
• While Loops Activity
• Built Functions in R : Base, datasets, dplyr
and ggplot
 Topics What We
Cover:

• Changing the Method of Aggregation

DAX Formulas
• Methods of Aggregation Challenge
• DAX Formulas Section
• Methods of Aggregation Challenge
• DAX Formulas
Completed
• Date Functions
• Cards and Multi Row Cards
• Formatting Dates
• Cards, Matrix and Multi Row Card Challenge
• Date Master Tables
• Answers to Cards Challenge
• Percentage Calculations
DAX Measures
• Filtering Data -Using Slicers
• Introduction to Measures Section
• Filtering Data -Visual Filters
• Introduction to DAX Measures
• Filtering Data -Page Filters
• DAX Measures Practical Activity
• Filtering Data –Drill Through Filter
• DAX Measures Activity Completed
• Practical Activity -Filters
• The =Calculate Formula
• Practical Activity Filters Completed
Relationships
Graphs and Visualizations
• Relationships Section
• Introduction to Visualization Section
• Creating and Managing Relationships in
• Clustered Column Graphs
Power BI
• Stacked and 100% Graphs
• Relationship Calculations
• Column Graph Challenge
• Column Graph Challenge Completed
Power BI Query Editor
• Graph Options
• Introduction to Power BI Query Editor
• Trend Analysis Graphs
• Basic Transformations -Part 1
• Area Graphs
• Basic Transformations -Part 2
• Ribbon Graphs
• Aggregating Data
• Additional Graphs
• Scatterplots and Bubble

Interactive Dashboards
Duration 160 Hours
• Creating Interactive Dashboards
• Challenge -Create an Interactive Report
• Completed Challenge -How to Create an
Interactive Report
• Publishing Reports to the Power BI Service
• Pinning Visualizations to Dashboards
• Mobile Reports
• Q and A
• App Workspaces
• Publishing an App
• Using Themes in Power BI
• Using Custom Visualizations
EXAMINATION PATTERN

Examination details
Pattern 60 Multiple Choice Questions
Topics Data Analysis 100%
Time in minutes 90
Mode Online - Computer Based(Web proctoring)
Pass Percentage 60%
Number of attempts allowed 2
When will be the examination Every 1 / 3 Sunday of a month
st rd

Criteria 80% attendance

Mock tests 2
QUADRUPLE CERTIFICATES
STEINBEIS UNIVERSITY,
BERLIN, GERMANY

Alumnus Status

COURSE COMPLETION
CERTIFICATE

INTERNSHIP CERTIFICATE
 About us
ExceIR is an initiative from alumni of
IIM & IIT with branches across India
,USA, Malaysia and spreading its
footprint across the globe. ExceIR
has been awarded as Deloitte's
fastest 50 technological companies
for the year 2018, and proud
business partner of Tata
Consultancy Services (TCS) in
Analytics space. We are a trusted
training delivery partner of 350+
corporate clients and universities
across the globe with 140,000+
professionals trained across various
courses.

www.excelr.com

Accolades

10 Leading Courses for

Block Chain 2019
Accreditations & Partners

Associations, Memberships &

Associates
Clientele
 ExcelR Solutions - USA
Visit us : www.excelr.com Regency Square, 6200 Savoy Drive
Suite 1202, Houston
Tx 77036. USA

We Are At Ph: +1 844-392-3571

ExcelR Solutions – Bengaluru - HO ExcelR Solutions - Pune

49, 1st Cross, 27th Main 102, 1st floor, Phase II

Behind Tata Motors Prachi Residency
1st Stage, BTM Layout Opp Lane to Kapil Malhar
Bengaluru Baner Road, Baner, Pune
Karnataka – 560068 Maharashtra - 411046

+91 9632156744 ExcelR Solutions – Hyderabad

1800-212-2120
532, 3rd Floor, Block “C”
ExcelR Solutions - Mumbai Uptown Cyberabad
Chandra Naik Nagar
301 Shree Padmini Building Madhapur, Hyderabad
Sanpada Society Telangana - 500080
Teli Galli Cross Road
above Star Health and Allied Insurance
Andheri East, Mumbai
Maharashtra - 400069

ExcelR Solutions - Thane

304, 3rd Floor,Pratibha Building,

Three Petrol pump, Opposite Manas
Tower, LBS Rd, Pakhdi, Thane (W)
Maharashtra - 400 602