Associate in Data Analytics

Understanding Concepts of Excel

• 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

Ms Excel Advance

• 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

SQL Server Fundamentals

• 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 Server 2019 Database Design

• 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 Tables in MS SQL Server

• 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

Data Validation and Constraints

• 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

Views and Row Data Security

• 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

Indexes and Query tuning

• 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

Stored Procedures and Benets

• 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

System functions and Usage

• 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

Triggers, cursors, memory limitations

• 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

Cursors and Memory Limitations

• Cursor declaration and Life cycle
• STATIC
• DYNAMIC
• SCROLL Cursors
• FORWARD_ONLY and LOCAL Cursors
• KEYSET Cursors with Complex SPs

Transactions Management

• ACID Properties and Scope
• EXPLICIT Transaction types
• IMPLICIT Transactions and options

Introduction to Power BI

• 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

• Power BI Desktop
• Extracting data from various sources
• Workspaces in Power BI

Power BI Data Transformation

• 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

Modelling with Power BI

• 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

Data Analysis Expressions (DAX)

• 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

Power BI Desktop Visualisations

• 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

Introduction to Power BI Dashboard and Data Insights

• Why Dashboard and Dashboard vs Reports
• Creating Dashboards
• Conguring a Dashboard Dashboard Tiles, Pinning Tiles
• Power BI Q&A
• Quick Insights in Power BI

Direct Connectivity

• 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

Publishing and Sharing

• 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

Refreshing Datasets

• Understanding Data Refresh
• Personal Gateway (Power BI Pro and 64-bit Windows)

Capstone Project