Associate in Data Analytics

What is Data Analytic

Common Terms in Data Analytics

What is data

Classication of data

Relevance in industry and need of the hour

Types of problems and business objectives in various industries

How leading companies are harnessing the power of analytics

Critical success drivers.

Overview of Data Analytics tools & their popularity.

Data Analytics Methodology & problem-solving framework.

Creation of Excel Sheet Data

Range Name, Format Painter

Conditional Formatting, Wrap Text, Merge & Centre

Sort, Filter, Advance Filter

Different type of Chart Creations

Auditing, (Trace Precedents, Trace Dependents)Print Area

Data Validations, Consolidate, Subtotal

What if Analysis (Data Table, Goal Seek, Scenario)

Solver, Freeze Panes

Various Simple Functions in Excel(Sum, Average, Max, Min)

Real Life Assignment work

Advance Data Sorting

Multi-level sorting

Restoring data to original order after performing sorting

Sort by icons

Sort by colours

Lookup Functions

• Lookup
• VLookup
• HLookup

Subtotal, Multi-Level Subtotal

Grouping Features

• Column Wise
• Row Wise

Consolidation With Several Worksheets

Filter

• Auto Filter
• Advance Filter

Printing of Raw & Column Heading on Each Page

Workbook Protection and Worksheet Protection

Specified Range Protection in Worksheet

Excel Data Analysis

• Goal Seek
• Scenario Manager

Data Table

• Advance use of Data Tables in Excel
• Reporting and Information Representation

Pivot Table

• Pivot Chat
• Slicer with Pivot Table & Chart

Generating MIS Report In Excel

• Advance Functions of Excel
• Math & Trig Functions

Text Functions

Lookup & Reference Function

Logical Functions & Date and Time Functions

Database Functions

Statistical Functions

Financial Functions

Functions for Calculation Depreciation

SQL Server 2019 Installation

Service Accounts & Use, Authentication Modes & Usage, Instance Congurations

SQL Server Features & Purpose

Using Management Studio (SSMS)

Conguration Tools & SQLCMD

Conventions & Collation

SQL Database Architecture

Database Creation using GUI

Database Creation using T-SQL scripts

DB Design using Files and File Groups

File locations and Size parameters

Database Structure modications

SQL Server Database Tables

Table creation using T-SQL Scripts

Naming Conventions for Columns

Single Row and Multi-Row Inserts

Table Aliases

Column Aliases & Usage

Table creation using Schemas

Basic INSERT

UPDATE

DELETE

SELECT queries and Schemas

Use of WHERE, IN and BETWEEN

Variants of SELECT statement

ORDER BY

GROUPING

HAVING

ROWCOUNT and CUBE Functions

Table creation using Constraints

NULL and IDENTITY properties

UNIQUE KEY Constraint and NOT NULL

PRIMARY KEY Constraint & Usage

CHECK and DEFAULT Constraints

Naming Composite Primary Keys

Disabling Constraints & Other Options

Benets of Views in SQL Database

Views on Tables and Views

SCHEMA BINDING and ENCRYPTION

Issues with Views and ALTER TABLE

Common System Views and Metadata

Common Dynamic Management views

Working with JOINS inside views

Need for Indexes & Usage

Indexing Table & View Columns

Index SCAN and SEEK

INCLUDED Indexes & Usage

Materializing Views (storage level)

Composite Indexed Columns & Keys

Indexes and Table Constraints

Primary Keys & Non-Clustered Indexes

Why to use Stored Procedures

Types of Stored Procedures

Use of Variables and parameters

SCHEMABINDING and ENCRYPTION

INPUT and OUTPUT parameters

System level Stored Procedures

Dynamic SQL and parameterization

Scalar Valued Functions

Types of Table Valued Functions

SCHEMABINDING and ENCRYPTION

System Functions and usage

Date Functions

Time Functions

String and Operational Functions

ROW_COUNT

GROUPING Functions

Why to use Triggers

DML Triggers and Performance impact

INSERTED and DELETED memory tables

Data Audit operations & Sampling

Database Triggers and Server Triggers

Bulk Operations with Triggers

Cursor declaration and Life cycle

STATIC

DYNAMIC

SCROLL Cursors

FORWARD_ONLY and LOCAL Cursors

KEYSET Cursors with Complex SPs

ACID Properties and Scope

EXPLICIT Transaction types

IMPLICIT Transactions and options

AUTOCOMMIT Transaction and usage

Overview of BI concepts

Why we need BI

Introduction to SSBI

SSBI Tools

Why Power BI

What is Power BI

Building Blocks of Power BI

Getting started with Power BI Desktop

Get Power BI Tools

Introduction to Tools and Terminology

Dashboard in Minutes

Interacting with your Dashboards

Sharing Dashboards and Reports

Power BI Desktop

Extracting data from various sources

Workspaces in Power BI

Data Transformation

Query Editor

Connecting Power BI Desktop to our Data Sources

Editing Rows

Understanding Append Queries

Editing Columns

Replacing Values

Formatting Data

Pivoting and Unpivoting Columns

Splitting Columns

Creating a New Group for our Queries

Introducing the Star Schema

Duplicating and Referencing Queries

Creating the Dimension Tables

Entering Data Manually

Merging Queries

Finishing the Dimension Table

Introducing the another DimensionTable

Creating an Index Column

Duplicating Columns and Extracting Information

Creating Conditional Columns

Creating the FACT Table

Performing Basic Mathematical Operations

Improving Performance and Loading Data into the Data Model

Introduction to Modelling

Modelling Data

Manage Data Relationship

Optimize Data Models

Cardinality and Cross Filtering

Default Summarization & Sort by

Creating Calculated Columns

Creating Measures & Quick Measures

What is DAX

Data Types in DAX

Calculation Types

Syntax, Functions, Context Options

DAX Functions

• Date and Time
• Time Intelligence
• Information
• Logical
• Mathematical
• Statistical
• Text and Aggregate

Measures in DAX

Measures and Calculated Columns

ROW Context and Filter Context in DAX

Operators in DAX - Real-time Usage

Quick Measures in DAX - Auto validations

In-Memory Processing DAX Performance

How to use Visual in Power BI

What Are Custom Visuals

Creating Visualisations and Colour Formatting

Setting Sort Order

Scatter & Bubble Charts & Play Axis

Tooltips and Slicers, Timeline Slicers & Sync Slicers

Cross Filtering and Highlighting

Visual, Page and Report Level Filters

Drill Down/Up

Hierarchies and Reference/Constant Lines

Tables, Matrices & Conditional Formatting

KPI's, Cards & Gauges

Map Visualizations

Custom Visuals

Managing and Arranging

Drill through and Custom Report Themes

Grouping and Binning and Selection Pane, Bookmarks & Buttons

Data Binding and Power BI Report Server

Why Dashboard and Dashboard vs Reports

Creating Dashboards

Conguring a Dashboard Dashboard Tiles, Pinning Tiles

Power BI Q&A

Quick Insights in Power BI

Custom Data Gateways

Exploring live connections to data with Power BI

Connecting directly to SQL Server

Connectivity with CSV & Text Files

Excel with Power BI Connect Excel to Power BI, Power BI Publisher for Excel

Content packs

Update content packs

Introduction and Sharing Options Overview

Publish from Power BI Desktop and Publish to Web

Share Dashboard with Power BI Service

Workspaces (Power BI Pro) and Content Packs (Power BI Pro)

Print or Save as PDF and Row Level Security (Power BI Pro)

Export Data from a Visualization

Export to PowerPoint and Sharing Options Summary

Understanding Data Refresh

Personal Gateway (Power BI Pro and 64-bit Windows)

Replacing a Dataset and Troubleshooting Refreshing