Business Analyst Training Course

Introduction to Excel, Basic Understanding Menu and Toolbar, Introduction to different category of functions like Basics, Mathematical and Statistical, Date and Time, Logical, Lookup and References, Text and Information.

Mathematical Functions:- Sum, Sumif, Sumifs, Count, Counta, Countblank, Countif, Countifs, Average, Averagea, Averageif, Averageifs, Subtotal, Aggregate, Rand, Randbetween, Roundup, Rounddown, Round, Sumproduct

Date & Time Function:- Date, Day, Month, Year, Edate, Eomonth, Networkdays, Workday, Weeknum, Weekday, Hour, Minute, Second, Now, Today, Time

Text Functions & Data Validation:- Char, Clean, Code, Concatenate, Find, Search, Substitute, Replace, Len, Right, Left, Mid, Lower, Upper, Proper, Text, Trim, Value, Large, Small Filters (Basic, Advanced, Conditional), Sort (Ascending, Descending, Cell/ Font Color), Conditional Formatting, Data Validation, Group & Ungroup, Data split.

Statistical Function & Other Functions:- Isna, Isblank, Iserr, Iseven, Isodd, Islogical, Isytext, Max, Min, Len, Right, Left, Mid, ,Maxa, Maxifs, Median, Minifs, Mina, Vara, Correl, Geomen

Logical Functions:- And, Or, If, Iferror, Not, Nested If

Lookup & Reference Functions:- VLookup, HLookup, Index, Match, Offset, Indirect, Address, Column, Columns, Row, Rows, Choose, Arrays Concept In Lookup Formula’s, Past Special, Past link

Lesson 1: Introduction to Business Analytics

• What is Analytics?
• Overview of different Analytics with Segment
• Customer Analytics
• Understand Business Analysis Key Concepts
• Performance Analytics
• Risk Analytics

Lesson 2:Formatting, Auto Formatting & Data Manipulation

• Conditional Formatting
• Freezing Concept
• Data Validations
• Logical Functions
• And, If, Nested IF, Not, OR, True, False
• SUMPRODUCT, IFERROR, CONCATENATE, MOD, LEN, RIGHT, LEFT
• Vlookup/Hlookup, Index, Match, Offset
• Statistical and Mathematical Functions
• Rank, Quartile, Percentile, Correl, Geomen
• Trace Precedents and Trace Dependents
• Round, Roundup, Round down, Max, Min, Large, Small
• Standard Deviation, Correlation, Mean, Median, Sum, Count, , Countifs , Sumif, Sumifs

Lesson 3: Data Analysis & Visualization – Power Pivot

• Pivot Table
• Power Pivot
• What-if Analysis
• Data Table –One Variable and Two Variables
• Data Analysis Using Statistics
• Descriptive Statistics
• ANOVA
• Moving Average
• Testing Hypothesis
• Measuring Covariance and Correlation
• Distribution
• Regression
• Graphs & Charts
• Analysis Tool Pack
• Solver
• Histogram
• Pareto
• Water Fall

Lesson 4: What is Dashboards and Excel Dashboard

• Overview of Chart types, Chart Formatting
• Active X Form Controls
• Principle of great dashboard design
• Selecting Correct Chart to display data
• Interactive Charts with Form Controls
• Combo box, Check Box, Scroll Bar and Radio Button
• Interactive Dashboard with Form Controls
• Form Controls for reports automation
• Data Models using Power Pivot

• What is MSPBI and its scope
• Learn the common work flow in MSPBI
• Building blocks of MSPBI and its relations
• Quick demo how to create a business dashboard in MSPBI
• MSPBI components
• Old vs. new technologies
• Power BI Desktop/Service/Mobile

• Get data in shape for use with MSPBI
• Combining two or more data sets (source data) for reporting
• Tackling messy data in MSPBI
• Clean and transform data

• How to connect many different data sources
• Manage data source (database) relationships
• Unique keys
• Calculated columns and more
• Custom calculations to evaluate time-based functions
• Build calculated tables based on DAX formulas and expressions
• Creating and viewing visuals easier with optimized models
• Discover hierarchal drill-down tools for date fields

• Create and customize visualization in MSPBI and its power
• Use combination charts
• Create and format slicers with it
• Map visualizations and its utilization
• Use tables and matrixes
• Long live bubbles
• scatter charts in action
• Advanced funnel and waterfall charts
• Drive fast dashboard insights with gauges and numbers
• Color your visualization world with colors
• shapes and scales
• Adding personal touch
• logo etc. to reports and dashboards
• Display and present your dashboard in a way you want with summarize data
• Control how your report elements overlap with each other
• Learn to drill into hierarchies
• Manage how levels are shared (Z-order in reports)
• How to use R visuals in MSPBI

• Quick insights in Power BI Service
• Create and configure a dashboard
• Share dashboard with your organization
• Display and edit visuals- tiles
• full screen
• Get more space on your dashboard
• Install and configure a personal gateway
• Excel and MSPBI
• Import and excel table into Power BI
• Import excel files with data models and power view sheets
• Connect One Drive for business to MSPBI
• Excel data in Power BI summary

• DAX and its basic building blocks
• Create calculated columns (fields) and measures in MSPBI using DAX formulas
• Breadth of functions available in DAX
• User variables in DAX
• Create expressions across multiple tables with relational functions;
• Filter and evaluate tables using advanced table functions

Access is a relational database management system (RDBMS) from Microsoft that combines the relational Microsoft Jet Database Engine with a graphical user interface and software-development tools. … It can also import or link directly to data stored in other applications and databases. Introduction to SQL

• SQL Course overview
• Installing the test environment
• What is SQL?
• Editors and Platforms to learn SQL

Complete SQL in a Class

• Quick-start introduction
• Using the basic SELECT statement
• Selecting Rows
• Selecting columns
• Counting rows
• Inserting data
• Updating data
• Deleting data
• Import and Export data

Fundamentals of SQL

• Databases and tables
• SQL syntax overview
• Data Definition, Data Manipulation, Data Control, Transactional Control statements
• Creating tables
• Deleting a table
• Inserting rows into a table
• Deleting rows from a table
• What is NULL?
• Controlling column behaviours with constraints
• Changing a schema with ALTER
• Filtering data with WHERE, LIKE, and IN
• Removing duplicates with SELECT DISTINCT
• Sorting with ORDER BY

How Relationships Work in SQL

• Understanding Joins
• Accessing related tables with JOIN
• Multiple related tables

Explaining SQL Strings

• About SQL strings
• Finding the length of a string
• Selecting part of a string
• Removing spaces with TRIM
• Making strings uppercase and lowercase

Numbers and SQL

• About numeric types
• Finding the type of a value
• Integer division and remainders
• Rounding numbers

SQL Functions and Clause

• The Aggregate functions MIN, MAX, AVG, SUM and COUNT, UPPER, LENGTH, LOWER
• The GROUP BY and HAVING clauses Grouping in a combination with joining
• Triggers in SQL

• Concept of Trigger
• Create Trigger for (Insert, Update, Delete)
• Alter Trigger

What are Subselects and Views in SQL

• Creating a simple subselect
• Searching within a result set
• Creating a view
• Creating a joined view

Maintaining SQL Server Database

• Backup Database
• Restore Database

SQL Server Job Creation

• How to create job in SQL Server Agent
• How to schedule job

Topics Covered as follows:

• Access environment and tools
• Database terminology and concept
• Designing database in Access
• Understanding RDBMS
• Working with the Design side of Tables
  • Create Query
  • Join Tables That Have No Common Fields
  • Work with Subdatasheets
  • Create Sub queries
• Working with the runtime of Tables
• Data Migration and Importing
• Working with the Design side of Queries
• Working with the runtime of Queries
• Working with the Design side of Forms
  • Adding Controls to Forms
  • Creating Sub forms
  • Organizing Information with Tab Pages
  • Displaying a Summary of Data in a Form
  • Applying Conditional Formatting
• Working with the runtime of Forms, Managing Switchboard
• Working with the Design side of Reports
  • Organize Report Information
  • Format Reports Include Charts in a Report
  • Add a Calculated Field to a Report
  • Add a Sub report to an Existing Report
• Working with the runtime of Reports
• Working with the Design side of Macros
  • Creating a Macro Restricting Records Using a Condition
  • Automating Data Entry Using a Macro
• Working with the runtime of Macros
• How to create a functional specification
• Build a real-world business application
• Putting altogether and deployment

• Overview Of SAS
• Working in the SAS Environment
• Working with windows
• Enhance editor window
• Log window
• Output window
• Result window explorer window program editor window
• Overviews of Libraries
• Datasets
• Data view
• Referencing files in SAS libraries
• Basic Concepts
• Creating an SAS programs
• Components of SAS programs
• Characteristics of SAS programs
• The layout of SAS programs

• Introduction to Business Analytics
• Types of Analytics
• Case study on Walmart, Signet Bank
• Data Science and its importance

• Introduction to R
• Installing R
• Installing R Studio
• Workspace Setup
• R Packages