Microsoft Data Science Online Training

Microsoft consulted data scientists and the companies that employ them to identify the core skills they need to be successful. This informed the curriculum used to teach key functional and technical skills, combining highly rated online courses with hands-on labs, concluding in a final capstone project.

Microsoft Data Science Plans :

  PLAN A PLAN B
Datascience Basics Check-Symbol-for-Yes Check-Symbol-for-Yes
SQL Server & T-SQL Check-Symbol-for-Yes Check-Symbol-for-Yes
Power BI & Excel Check-Symbol-for-Yes Check-Symbol-for-Yes
Junior DataScientist Check-Symbol-for-Yes Check-Symbol-for-Yes
Microsoft Business Analyst Check-Symbol-for-Yes Check-Symbol-for-Yes
Azure, Machine Learning, Hadoop Croos-symbol-for-No Check-Symbol-for-Yes
HD Insight & Spark Croos-symbol-for-No Croos-symbol-for-Yes
Total Course Fee INR 15,000/-
USD 250
INR 45,000/-
USD 700

Register Today :

 

Data Science Training Course Contents:

Module I: SQL Server & Design, Queries, Joins

Module II: T-SQL Queries, Tuning & Programming

CHAPTER 1: SQL SERVER INSTALLATION

  • What is Data? What is Database? File Store Limitations?
  • Why Microsoft SQL Server? Advantages (Technical/Usage)
  • SQL Server - Career Options, Certifications, Projects
  • What is SQL? What is T-SQL? Differences. Why T-SQL?
  • MSBI Components and Real-time Activities
  • Versions and Editions of SQL Server - Overview
  • Session Wise Plan, Material and Real-time Project Details
  • LAB PLAN - 24x7 LIVE Server (Online Lab)
  • SQL Server Software - Server Installation Steps
  • SQL Server Tools Installation and Verification
  • SSMS in Windows OS, SOS Tool in Mac & LINUX OS
  • H/W & S/W Requirements. Server Configuration Options
  • Instance Types : Default and Named Instances. Instance IDs
  • SQL Server Tools - SQL Server Management Studio (SSMS)

CHAPTER 7: STORED PROCEDURES - LEVEL 1

  • Stored Procedures - Purpose, Syntax, Properties and Types
  • Compilation, Precompilation and Query Optimization (QO)
  • Variables - Usage and Data Types in Stored Procedures
  • Parameters - Usage and Data Types in Stored Procedures
  • Stored Procedure Executions - Syntax, Alternate Options
  • Stored Procedures for Data Validations & Missing Identity
  • Stored Procedures for Dynamic SQL Queries. Views & SPs
  • Stored Procedures for Data Reporting. Advantanges, Tuning
  • Important System Procedures For Metadata Access. Usage
  • Important Extended Procedures For Application Operations
  • IF.. ELSE, IF .. ELSE IF, IIF Conditions. PRINT statements
  • Error Handling Techniques in T-SQL: TRY, CATCH, THROW
  • Dynamic Parameters and Variables. Examples with Views
  • Default Parameter Values, Data Types and NULL Values
  • Batch Executions with Stored Procedures. Variants

CHAPTER 2: SQL BASICS - DDL, DML, SELECT

  • Basic SQL for Beginners. Introducing Databases, Tables
  • What is SQL? Why T-SQL? Basic SQL Queries in SSMS
  • DDL and DML Statements - Creating & Using Databases
  • Table Creation (Basic Level) - Columns and Data Types
  • Issues with Digital Data into Characters. Missing Values
  • INSERT / Store Data into SQL Server Tables - Options
  • Single Row and Multiple Row Inserts with NULL Values
  • SELECT Queries and Basic Operators : IN, BETWEEN
  • IS, UNION, UNION ALL, Other Basic SQL Operators
  • UPDATE Statements with / without Conditions. SET
  • DELETE Statements with Conditions. Logging Options
  • TRUNCATE Statement - DELETE Comparisons, Logging
  • SYSTEM DATABASES - Purpose and Importance. Resource
  • CLIENT - SERVER Architecture (TDS) & Client Statistics

CHAPTER 8: STORED PROCEDURES - LEVEL 2

  • Stored Procedures for Sub Queries, Dynamic Sub Queries
  • Stored Procedures for Recursive and Nested Queries
  • OUTPUT Parameters in Stored Procedures. Usage Options
  • Cursors - Benefits, Syntax. Using SProcs with Cursors
  • FORWARD_ONLY and SCROLL Cursors Types. Limitations
  • STATIC and DYNAMIC Cursors Types. ABSOLUTE Fetch
  • LOCAL and GLOBAL Cursor Types & Scope, Reusability
  • KEYSET DRIVEN Cursor Types & Performance Options
  • Embedding Cursors in Procedures and User Functions
  • SPs with Cursors @ Dynamic Data Loads, Data Formatting
  • Memory Limitations with Cursors with SP Recompilations
  • Using Extended Stored Procedures with User Procedures
  • Stored Procedures for Dynamic Values, Calendar Data
  • Unicode Data and Dynamic SQL Queries. sysname Data

CHAPTER 3: SQL SERVER DATABASE DESIGN

  • SQL Database Architecture - Logical and Physical View
  • Database Properties - Files - Types - Storage Options
  • Data Files : Purpose and Sizing. Detailed Architecture
  • Filegroups : Purpose and Grouping Options. Properties
  • Log files : Sizing, Placement & Detailed Architecture
  • Pages, Extents (Uniform, Mixed). Data Allocation Process
  • Write Ahead Log (WAL) and Log Sequence Number (LSN)
  • Virtual Log File (VLF) and MINI LSN. Operation Audits
  • Database Creation using GUI - Adding Files, Filegroups
  • Database File and Filegroup Options. GUI Limitations
  • Database Creation using T-SQL Scripts. SYNTAX Rules
  • Database with Filegrowth, Autogrowth, MAXSIZE Options
  • mdf, ndf, ldf and Custom Extensions. Dynamic Extensions
  • Planning and Designing Very Large Databases (VLDB)
  • CHAR versus VARCHAR Differences - Type, Size Allocations

CHAPTER 9: VIEWS - FUNCTIONS & QUERIES

  • Views on Tables - Stored SELECT Statements, Data Access
  • SCHEMABINDING and ENCRYPTION Options - Advantages
  • Cascaded Views and WITH CHECK OPTION, Advantages
  • Orphan Views - Scenarios and Realworld Solutions
  • Common System Views For Metadata Access, Object IDs
  • Functions: Types, Purpose and Usage. Return Values
  • Scalar Value and Inline Table Value Functions
  • Multi-Line Table Value Returning Functions - Usage
  • Table Variables and Parameters in SQL Server. Usage
  • ROLLUP and CUBE - Sub Totals, Grand Totals, Aggregates
  • ROLLUP of Table Data. Column Aggregations. ORDER BY
  • CUBE on Table Data - Purpose & Usage. Permutations
  • Queries with GROUPING() Option in SELECT, Using HAVING
  • HAVING versus WHERE Conditions - Usage Differences

CHAPTER 4: TABLE DESIGN & QUERIES

  • Table Design - Creation. Columns - Data Types, Length
  • Routing Tables to Database File Groups, Advantages
  • Schemas - Purpose, Creation and Usage with Tables
  • Table Design using T-SQL Scripts - Syntax, Examples
  • Data Types, Length, NULLs and Naming Conventions
  • UNION, UNION ALL Operators. Differences, Row Order
  • CREATE, ALTER, DROP -- INSERT, UPDATE, DELETE
  • SELECT Queries with Schema on Tables, Column Aliases
  • T-SQL Data Types and NULL Values. Computed Columns
  • Comparing DELETE and TRUNCATE - TLog Files
  • T-SQL Operators: IN, BETWEEN, IS, AND, OR, EXISTS
  • Default Schema and Default Filegroup for Table Design
  • Basic Sub Queries - SELECT, MIN/ MAX. Column Aliases
  • Temporary Tables : Purpose and Types. Local and Global
  • Synonyms : Purpose. Alternate Object Reference, Queries

CHAPTER 10: TRANSACTIONS & TRIGGERS

  • Need for Transactions, Transaction Scenarios
  • ACID Properties and Transaction Types. Atomic Property
  • EXPLICIT, IMPLICIT Transactions - Query Blocking
  • IMPLICIT Transactions - Usage, Database Settings
  • AUTOCOMMIT Transactions - Advantages, Examples
  • OPEN Transactions and Audits. OPENTRAN commands
  • Nested Transactions and COMMIT / ROLLBACK Rules
  • SavePoint Options with Explicit Transactions, Rollbacks
  • LOCK HINTS : READPAST, NOLOCK, HOLDLOCK - Usage
  • Triggers - Purpose and Types. Scope Of Usage
  • DML Triggers - Events, Types and Practical Usage
  • FOR / AFTER Triggers and INSTEAD OF Triggers
  • INSERTED & DELETED Memory Tables with DML Triggers
  • Triggers for DML Operation Audits and Data Sampling
  • Database Triggers and Server Level Triggers

CHAPTER 5: CONSTRAINTS and KEYS

  • Constraints and Keys - Ensuring Table Data Integrity
  • Normal Forms - Types, Relational Database (RDB) Design
  • OLTP Database Model & BCNF - Relations with PK / UQ
  • NULL, NOT NULL and Default Nullability for Columns
  • UNIQUE KEY Constraints: Importance, Uniqueness, Nulls
  • PRIMARY KEY Constraint: Properties, Priority, Limitations
  • FOREIGN KEY Constraint: References, Relations & Usage
  • CHECK Constraints: Properties, Conditions and Usage
  • DEFAULT Constraints: Properties, Usage and Limitations
  • Relations with Tables across Multiple Schemas, Usage
  • Identity Property with / without PRIMARY KEY, Usage
  • Naming Conventions For Constraints, Columns and Tables
  • Normal Forms - Types, Purpose and Usage. With Examples
  • BCNF: Boycee-Codd Normal Form and Practical Usage

CHAPTER 11: INDEXES and QUERY TUNING OPTIONS

  • Indexes: Architecture (Page Level), Purpose and Types
  • Clustered Indexes - Architecture, Fragmentation Issues
  • Non Clustered Indexes - Architecture, Column References
  • Execution Plans and Query Optimization (QO) Techniques
  • Execution Plan - Table Scan, Index Scan and Index Seek
  • INCLUDED INDEXES - Index Seeks, Query Tuning
  • COLUMNSTORE Indexes - Advantages, Usage Examples
  • FILTERED Indexes & Online Indexes
  • Materialized Views / Indexed Views - Tuning Options
  • Query Optimizer (QO) Options for Index Pages, Data Pages
  • Limitations of Indexes - Impact on DML and SELECT
  • Primary Key Index, Composite Indexes and Precautions
  • RID and Index Key Concepts. Index Page - Data Page Arch"
  • Real-world Considerations For Indexes (Tables, Views)

CHAPTER 6: JOINS, SUB QUERIES & NESTED QUERIES

  • JOINS - Purpose and Types, Use Case Scenarios
  • JOIN - Types, Queries and Importance of Reports
  • CROSS JOIN in detail. Examples and Conditions @ WHERE
  • INNER JOIN in detail. Examples with WHERE and ON
  • Comparing INNER JOIN with CROSS JOIN for Conditions
  • OUTER JOINS in detail. LEFT, RIGHT and FULL Joins
  • SELF JOINS with INNER / OUTER Joins. Usage Scenarios
  • Working with Self Joins on non key columns, advantages
  • JOINS with more than 2 tables. Syntax, Precedence Order
  • Query Optimization Considerations with Schema References
  • Deciding the best Join Type, Order and Query Options
  • JOIN Queries with Options and UNION, UNION ALL Operators
  • Basic Sub Queries and Joins. Alternate Syntax & Queries
  • Using ON and WHERE for Join Conditions. Working with NULLs
  • Using SubQueries for Self Joins and Outer Joins
  • Working with Nested Queries and Nested Sub Queries
  • Using Sub Queries and Nested Sub Queries with Outer Joins
  • End User Access to SQL Databases - Reporting Tools, Options
  • A Real-world Case Study understanding Joins & Queries

CHAPTER 12: SQL SERVER ARCHITECTURE

  • Client - Server Architecture of SQL Server
  • SQL Server Tools - Connection Options, TDS Packets
  • Protocols : TCP / IP, Named Pipes, Shared Memory
  • SQL Native Client (SNAC) and OLE DB Drivers / Providers
  • ISO - OSI Model of Data Connections, Encrypted Data
  • Query Processing and Query Optimizer (QO) Components
  • SQL Server Architecture For Database Engine, LCM Options
  • Architecture - Query Processor and Storage Engine
  • Architecture - Query Parser, Optimizer, Mini LSN, MDAC
  • Architecture - SQL Engine, SQL Manager and Query Buffers
  • Architecture - Write Ahead Log (WAL), Lazy Writer Threads
  • Architecture - SQLOS Threads and Task Schedulers, CLR
  • SQL Database Architecture - RAID Levels (S/W, H/W)
  • Log Sequence Numbers (LSN) and Time Mapping. Audits
  • Log File Architecture - Virtual Log Files and Usage
  • Log File Architecture - Mini LSN & Degree Of Parallelism
  • DB Catalogs, CLR Integration and MDAC Components
  • LSN Timestamps and MINILSN. Background Threads @ SQL

Module I: AZURE SQL DATABASE (Dev)

DAY 1: AZURE CLOUD INTRO

  • Introduction to Cloud. Need for Cloud, Advantages
  • Cloud Architecture Basics - Iaas, PasS and SaaS
  • Operational Advantages of Cloud, Cloud Providers
  • Advantages of Microsoft Cloud - Azure Platform
  • Service Models, Private & Public Clouds
  • SQL Databases in Microsoft Azure and Advantages
  • Azure SQL & Databases - Need, Importance
  • Azure Sources - Types, Microsoft Market Place
  • Azure SQL Database, Azure SQL Data Warehouse
  • Azure Analysis Services, BLOB and TABLE Storage
  • Azure Cosmos DB, Data Lake, DH Insight, Spark
  • Virtual Machines and Apps, Programs in Azure
  • Azure SQL Variants and Service Tiers
  • Advantages of Azure SQL Databases & Tools
  • Comparing Azure with AWS and Google Cloud
  • Microsoft Azure Price Tiers & Subscription

DAY 2: AZURE CLOUD CONFIGURATIONS

  • Azure Cloud Subscription, Azure Portal Options
  • Azure Resources, Marketplace and Dashboards
  • Azure SQL Database Architecture Components - in detail
  • Price Tiers: Basic, Standard, Premium, PremiumRS
  • Isolated Price Trier - Advantages, Performance
  • Creating SQL Servers in Azure and in Virtual Machines
  • Elastic Pools and Configuration Options - Advantages
  • DTU : Data Transaction Units : Architecture, Pools
  • eDTUs and Elastic Pool, per Database Settings
  • EDTU Cost, eDTU max/min Limits and Performance
  • Resource Groups and Resource Pools in Azure SQL
  • Azure SQL Databases : Technical Features, Benefits
  • Built-In Intelligence and Scalability, Tools For Usage
  • Advanced Security Compliance, ARM and ASM Topologies
  • Need for OSM Workspace - Operations Management Suite

DAY 3: AZURE SQL DATABASE CONFIGURATION

  • Creating Azure SQL Server Instances
  • Creating Azure SQL Databases, Price Tiers
  • SQL Database – Cloud Database as a Service
  • Subscription Options and Database Sources
  • Elastic Pools & Tier Selection - Recommendations
  • Database Name Identifiers, Naming rules & restrictions
  • Server Names - Locations, Admin Users, Passwords
  • S1/S2/S3 DTU bands and Performance, Storage
  • Add-On Storage Options. Database Provisioning
  • Firewall Rules, IP Configuration Ranges
  • Azure Dashboard - Metrics, Notification Options
  • Azure SQL Database Collation, Connection Options, Tools
  • SQL Server Management Studio (SSMS) & Visual Studio
  • SQL Server Data Explorer Tool in Azure Cloud
  • .NET, PHP, Node.js, Java, Ruby, Python
  • Creating Azure SQL Databases in SSMS Tool
  • T-SQL Scripts for Azure SQL Database

DAY 4: DEVELOP AZURE SQL DATABASE

  • Executing T-SQL Scripts in Azure
  • Creating Tables and Defining Constraints
  • Cascades, Constraint Rules and Index Rules
  • Clustered Indexes in Azure SQL Database Tables
  • Programming Objects: Stored Procedures in Cloud
  • Automated Recompilations, Complex Stored Procedures
  • Triggers and Memory Tables Architecture in Cloud
  • CTE : Common Table Expressions and Performance
  • User Defined Functions and Views for Data Reporting
  • Differences between On-Premise and Cloud SQL Databases
  • Executing T-SQL Scripts in Azure SQL Database
  • Linked Servers with On-Premise and Cloud
  • SSMS "Generate Script" Options, Advanced Options
  • Azure SQL Database JSON Features, Data Imports
  • Azure SQL Database In-Memory Tables - Advantages
  • Temporal Tables, In-Memory OLTP Tables with Azure SQL DB
  • Excel Reporting Options from Azure SQL Database
  • Data Explorer Options with Azure SQL Databases
  • XML Data Storage & Reports. BLOB Data Storage

DAY 5: AZURE SQL DATABASE MIGRATIONS

  • Database Scripting Wizard in SSMS
  • Scripting On-Premise Databases in T-SQL
  • Data Migration Assistant (DMA) Tool
  • Schema Generation and Compatability Issues
  • Generating Data Scripts, Assessment, Schema Options
  • Prepare and Deploy Fixes. Database Snapshots
  • Resolving Database Migration Compatibility Issues
  • Partially Supported and Unsupported Functions
  • non SQL Server Database Migrations : MS Access, Oracle
  • SQL Server Migration Assistant (SSMA) Tool
  • Import from a BACPAC file using Azure portal
  • Import from a BACPAC file using SQLPackage
  • Import from a BACPAC file using PowerShell
  • Migrate Stored Procedures, In-Memory Tables

DAY 6: INTEGRATING with AZURE SQL DATABASE

  • Azure SQL Database Tables, Views in Excel
  • Excel Pivot Tables and Chart Reports with Azure SQL DB
  • Azure & Excel ODC Connections. Pivot Reports
  • ADO.NET, JDBC and ODBC Connections. Data Mashups
  • Connection Drivers in Azure Cloud - Options
  • Azure Portal Email Configurations, Triggers
  • Azure SQL Database Query Batching - Advantages
  • Azure Cloud Shell - Concepts, Architecture
  • Azure Power Shell - Install and Configure
  • Installing and Scripting with Power Shell
  • PowerShellGet and Version Paths
  • Cloud Shell to run the Azure Power Shell
  • Linux Virtual Machines with Power Shell
  • Windows Virtual Machines with Power Shell

DAY 7: MCSA Certifications

Guidance and Mock Certification for 70-762 (SQL Dev)- If Required

 

Guidance and Mock Certification for 70-473 (Azure SQL) - Mandatory

CHAPTER 1 : INTRODUCTION TO POWER BI (Free Demo)

  • Introduction to Power BI - Need, Imprtance
  • Power BI - Advantages and Scalable Options
  • History - Power View, Power Query, Power Pivot
  • Power BI Data Source Library and DW Files
  • Cloud Colloboration and Usage Scope
  • Business Analyst Tools, MS Cloud Tools
  • Power BI Installation and Cloud Account
  • Power BI Cloud and Power BI Service
  • Power BI Architecture and Data Access
  • OnPremise Data Acces and Microsoft On Drive
  • Power BI Desktop - Instalation, Usage
  • Sample Reports and Visualization Controls
  • Power BI Cloud Account Configuration
  • Understanding Desktop & Mobile Editions
  • Report Rendering Options and End User Access
  • Power View and Power Map. Power BI Licenses
  • Course Plan - Power BI Online Training

CHAPTER 8 : DAX EXPRESSIONS - Level 1

  • Purpose of Data Analysis Expresssions (DAX)
  • Scope of Usage with DAX. Usabilty Options
  • DAX Context : Row Context and Filter Context
  • DAX Entities : Calculated Columns and Measures
  • DAX Data Types : Numeric, Boolean, Variant, Currency
  • Datetime Data Tye with DAX. Comparison with Excel
  • DAX Operators & Symbols. Usage. Operator Priority
  • Parenthesis, Comparison, Arthmetic, Text, Logic
  • DAX Functions and Types: Table Valued Functions
  • Filter, Aggregation and Time Intelligence Functions
  • Information Functions, Logical, Parent-Child Functions
  • Statistical and Text Functions. Formulas and Queries
  • Syntax Requirements with DAX. Differences with Excel
  • Naming Conventions and DAX Format Representation
  • Working with Special Characters in Table Names
  • Attribute / Column Scope with DAX - Examples
  • Measure / Column Scope with DAX - Examples

CHAPTER 2 : CREATING POWER BI REPORTS, AUTO FILTERS

  • Report Design with Legacy & .DAT Files
  • Report Design with Databse Tables
  • Understanding Power BI Report Designer
  • Report Canvas, Report Pages: Creation, Renames
  • Report Visuals, Fields and UI Options
  • Experimenting Visual Interactions, Advantages
  • Reports with Multiple Pages and Advantages
  • Pages with Multiple Visualizations. Data Access
  • PUBLISH Options and Report Verification in Cloud
  • "GET DATA" Options and Report Fields, Filters
  • Report View Options: Full, Fit Page, Width Scale
  • Report Design using Databases & Queries
  • Query Settings and Data Preloads
  • Navigation Options and Report Refresh
  • Stacked bar chart, Stacked column chart
  • Clustered bar chart, Clustered column chart
  • Adding Report Titles. Report Format Options
  • Focus Mode, Explore and Export Settings

CHAPTER 9 : DAX EXPRESSIONS - Level 2

  • YTD, QTD, MTD Calculations with DAX
  • DAX Calculations and Measures
  • Using TOPN, RANKX, RANK.EQ
  • Computations using STDEV & VAR
  • SAMPLE Function, COUNTALL, ISERROR
  • ISTEXT, DATEFORMAT, TIMEFORMAT
  • Time Intelligence Functions with DAX
  • Data Analysis Expressions and Functions
  • DATESYTD, DATESQTD, DATESMTD
  • ENDOFYEAR, ENDOFQUARTER,ENDOFMONTH
  • FIRSTDATE, LASTDATE, DATESBETWEEN
  • CLOSINGBALANCEYEAR,CLOSINGBALANCEQTR
  • SAMEPERIOD and PREVIOUSMONTH,QUARTER
  • KPIs with DAX. Vertipaq Queries in DAX
  • IF..ELSEIF.. Conditions with DAX
  • Slicing and Dicing Options with Columns, Measures
  • DAX for Query Extraction, Data Mashup Operations
  • Calcualted COlumns and Calculated Measures with DAX

CHAPTER 3 : REPORT VISUALIZATIONS and PROPERTIES

  • Power BI Design: Canvas, Visualizations and Fileds
  • Import Data Options with Power BI Model, Advantages
  • Direct Query Options and Real-time (LIVE) Data Access
  • Data Fields and Filters with Visualizations
  • Visualization Filters, Page Filters, Report Filters
  • Conditional Filters and Clearing. Testing Sets
  • Creating Customised Tables with Power BI Editor
  • General Properties, Sizing, Dimensions, and Positions
  • Alternate Text and Tiles. Header (Column, Row) Properties
  • Grid Properties (Vertical, Horizontal) and Styles
  • Table Styles & Alternate Row Colors - Static, Dynamic
  • Sparse, Flashy Rows, Condensed Table Reports. Focus Mode
  • Totals Computations, Background. Boders Properties
  • Column Headers, Column Formatting, Value Properties
  • Conditional Formatting Options - Color Scale
  • Page Level Filters and Report Level Filters
  • Visual-Level Filters and Format Options
  • Report Fields, Formats and Analytics
  • Page-Level Filters and Column Formatting, Filters
  • Background Properties, Borders and Lock Aspect

CHAPTER 10 : POWERBI DEPLOYMENT & CLOUD

  • PowerBI Report Validation and Publish
  • Understanding PowerBI Cloud Architecture
  • PowerBI Cloud Account and Workspace
  • Reports and DataSet Items Validation
  • Dashboards and Pins - Real-time Usage
  • Dynamic Data Sources and Encryptions
  • Personal and Organizational Content Packs
  • Gateways, Subscriptions, Mobile Reports
  • Data Refresh with Power BI Architecture
  • PBIX and PBIT Files with Power BI - Usage
  • Visual Data Imprts and Visual Schemas
  • Cloud and On-Premise Data Sources
  • How PowerBI Supports Data Model?
  • Relation between Dashbaords to Reports
  • Relation between Datasets to Reports
  • Relation between Datasets to Dashbaords
  • Page to Report - Mapping Options
  • Publish Options and Data Import Options
  • Need for PINS @ Visuals and PINS @ Reports
  • Need for Data Streams and Cloud Intergration

CHAPTER 4 : CHART AND MAP REPORT PROPERTIES

  • CHART Report Types and Properties
  • STACKED BAR CHART, STACKED COLUMN CHART
  • CLUSTERED BAR CHART, CLUSTERED COLUMN CHART
  • 100% STACKED BAR CHART, 100% STACKED COLUMN CHART
  • LINE CHARTS, AREA CHARTS, STACKED AREA CHARTS
  • LINE AND STACKED ROW CHARTS
  • LINE AND STACKED COLUMN CHARTS
  • WATERFALL CHART, SCATTER CHART, PIE CHART
  • Field Properties: Axis, Legend, Value, Tooltip
  • Field Properties: Color Saturation, Filters Types
  • Formats: Legend, Axis, Data Labels, Plot Area
  • Data Labels: Visibility, Color and Display Units
  • Data Labels: Precision, Position, Text Options
  • Analytics: Constant Line, Position, Labels
  • Working with Waterfall Charts and Default Values
  • Modifying Legends and Visual Filters - Options
  • Map Reports: Working with Map Reports
  • Hierarchies: Grouping Multiple Report Fields
  • Hierarchy Levels and Usages in Visualizations
  • Preordered Attribute Collection - Advantages
  • Using Field Hierarchies with Chart Reports
  • Advanced Query Mode @ Connection Settings - Options
  • Direct Import and In-memory Loads, Advantages

CHAPTER 11 : POWER BI CLOUD OPERATIONS

  • Report Publish Options and Verifications
  • Working with Power BI Cloud Interface & Options
  • Navigation Paths with "My Workspace" Screens
  • FILE, VIEW, EDIT REPORTS, ACCESS, DRILLDOWN
  • Saving Reports into pdf, pptx, etc. Report Embed
  • Report Rendering and EDIT, SAVE, Print Options
  • Report PIN and individual Visual PIN Options
  • Create and Use Dashboards. Menu Options
  • Goto Dashboard and Goto LIVE Page Options
  • Operations on Pinned Reports and Visuals
  • TITLE, MEDIA, USAGE METRICS & FAVOURITES
  • SUBSCRIPTION Options and Reports with Mobile View
  • Options with Report Page : Print and Subscribe
  • Report Actions: USAGE METRICS, ANALYSE IN EXCEL
  • Report Actions: RELATED ITEMS, RENAME, DELETE
  • Dashboard Actions: METRICS, RELATED ITEMS
  • Dashboard Actions: SETTINGS FOR Q & A, DELETE
  • PIN Actions: METRICS, SHARE, RELATED ITEMS
  • PIN Actions: SETTINGS FOR Q & A, DELETE
  • EDIT DASHBOARD (CLOUD), On-The-Fly Reports
  • Dataset Actions: CREATE REPORT, REFRESH
  • SCHEDULED REFRESH & RELATED ITEMS
  • Dashboard Integration with Apps in Power BI

CHAPTER 5 : HIERARCHIES and DRILLDOWN REPORTS

  • Hierarchies and Drilldown Options
  • Hierarchy Levels and Drill Modes - Usage
  • Drill-thru Options with Tree Map and Pie Chart
  • Higher Levels and Next Level Navigation Options
  • Aggregates with Bottom/Up Navigations. Rules
  • Multi Field Aggregations and Hierarchies in Power BI
  • DRILLDOWN, SHOWNEXTLEVEL, EXPANDTONEXTLEVEL
  • SEE DATA and SEE RECORDS Options. Differences
  • Toggle Options with Tabular Data. Filters
  • Drilldown Buttons and Mouse Hover Options @ Visuals
  • Dependant Aggregations, Independant Aggregations
  • Automated Records Selection with Tabular Data
  • Report Parameters : Creation and Data Type
  • Available Values and Default values. Member Values
  • Parameters for Column Data and Table / Query Filters
  • Parameters Creation - Query Mode, UI Option
  • Linking Parameters to Query Columns - Options
  • Edit Query Options and Parameter Manage Entries
  • Connection Parameters and Dynamic Data Sources
  • Synonyms - Creation and Usage Options

CHAPTER 12 : IMPROVING POWER BI REPORTS

  • Publish PowerBI Report Templates
  • Import and Export Options with Power BI
  • Dataset Navigations and Report Navigations
  • Quick Navigation Options with "My Workspace"
  • Dashboards, Workbooks, Reports, Datasets
  • Working with MY WORK SPACE group
  • Installing the Power BI Personal Gateway
  • Automatic Refresh - Possible Issues
  • Adding images to the dashboards
  • Reading & Editing Power BI Views
  • Power BI Templates (pbit)- Creation, Usage
  • Managing report in Power BI Services
  • PowerBI Gateway - Download and Installation
  • Personal and Enterprise Gateway Features
  • PowerBI Settings : Dataset - Gateway Integration
  • Configuring Dataset for Manual Refresh of Data
  • Configuring Automatic Refresh and Schdules
  • Workbooks and Alerts with Power BI
  • Dataset Actions and Refresh Settings with Gateway
  • Using natural Language Q&A to data - Cortana

CHAPTER 6 : POWER QUERY & M LANGUAGE - Part 1

  • Understanding Power Query Editor - Options
  • Power BI Interface and Query / Dataset Edits
  • Working with Empty Tables and Load / Edits
  • Empty Table Names and Header Row Promotions
  • Undo Headers Options. Blank Columns Detection
  • Data Imports and Query Marking in Query Editor
  • JSON Files & Binary Formats with Power Query
  • JavaScript Object Notation - Usage with M Lang.
  • Applied Steps and Usage Options. Revert Options
  • creating Query Groups and Query References. Usage
  • Query Rename, Load Enable and Data Refresh Options
  • Combine Queries - Merge Join and Anti-Join Options
  • Combine Queries - Union and Union All as New Dataset
  • M Language : NestedJoin and JoinKind Functions
  • REPLACE, REMOVE ROWS, REMOVE COL, BLANK - M Lang
  • Column Splits and FilledUp / FilledDown Options
  • Query Hide and Change Type Options. Code Generation

CHAPTER 13 : INSIGHTS AND SUBSCRIPTIONS

  • Data Navigation Paths and Data Splits
  • Getting data from existing systems
  • Data Refresh and LIVE Connections
  • pbit and pbix : differences. Usage Options
  • Quick Insights For Power BI Reports
  • Quick Insights For PowerBI Dashboads
  • Generating Insights with Cloud Datasets
  • Generarting Reports with Cloud Datasets
  • Using relational databases on-premises
  • Using relational databases in the cloud
  • Consuming a service content pack
  • Creating a custom data set from a service
  • Creating a content pack for your organization
  • Consuming an organizational content pack
  • Updating an organizational content pack
  • Adding Tiles : Images, Videos, DataStreams
  • Creating New Reports from Cortana, Advantages

CHAPTER 7 : POWER QUERY & M LANGUAGE - Part 2

  • Invoke Function and Freezing Columns
  • Creating Reference Tables and Queries
  • Detection and Removal of Query Datasets
  • Custom Columns with Power Query
  • Power Query Expressions and Usage
  • Blank Queries and Enumuration Value Generation
  • M Language Sematics and Syntax. Tranform Types
  • IF..ELSE Conditions, TransformColumn() Types
  • RemoveColumns(), SplitColumns(),ReplaceValue()
  • Table.Distinct Options and GROUP BY Options
  • Table.Group(), Table.Sort() with Type Conversions
  • PIVOT Operation and Table.Pivot(). List Functions
  • Using Parameters with M Language (Power Query Editor)
  • Advanced Query Editor and Parameter Scripts
  • List Generation and Table Conversion Options
  • Aggregations using PowerQuery & Usage in Reports
  • Report Generation using Web Pages & HTML Tables
  • Reports from Page collection with Power Query
  • Aggregate and Evaluate Options with M Language
  • Creating high-density reports, ArcGIS Maps, ESRI Files
  • Generating QR Codes for Reports
  • Table Bars and Drill Thru Filters

CHAPTER 14 : POWERBI INTEGRATION ELEMENTS

  • SSRS Integration with Power BI
  • SSRS Report Portal URL to Power BI Cloud
  • Power BI KPI Reports Vs SSRS KPI Reports
  • Convering and Working with Mobile Reports
  • Report Buidler Reports to Powert BI
  • Generating QR Codes and Report Security
  • Reporting JSON Files, Bulk Data Loads
  • Creating high-density Reports in Power BI
  • OLAP DataSources in Power BI
  • Using MDX Queries with PowerBI Queries
  • MDX SELECT and Perspective Access
  • KPIs and MDX Expressions with Power BI
  • MDX Queries and Filters with Power BI
  • Linked Servers and T-SQL SPROCs with MDX
  • YTD, PARALLELPERIOD,SCOPE, ALLMEMBERS
  • WHERE, EXCEPT, RANGE, NONEMPTY
  • CURRENT & EMPTY, AND / OR, LEFT / RIGHT
  • Implementing Row Level Security (RLS)
  • Security Roles and Role Members. Tests
  • Using R for Power BI, Streaming DataSets
  • Azure Connections with PowerBI Desktop
  • PowerBI Reports using SQL Azure DBs

Module I

Module II

CHAPTER 1 : INTRODUCTION

  • What is Cloud Computing
  • What is Grid Computing
  • What is Virtualization
  • How above three are inter-related to each other
  • What is Big Data
  • Introduction to Analytics and the need for big data analytics
  • Hadoop Solutions - Big Picture
  • Hadoop distributions
  • Comparing Hadoop Vs. Traditional systems
  • Volunteer Computing
  • Data Retrieval - Radom Access Vs. Sequential Access
  • NoSQL Databases

CHAPTER 2 : THE MOTIVATION FOR HADOOP

  • Problems with traditional large-scale systems
  • Data Storage literature survey
  • Data Processing literature Survey
  • Network Constraints
  • Requirements for a new approach

CHAPTER 3 : HADOOP BASIC CONCEPTS

  • What is Hadoop?
  • The Hadoop Distributed File System
  • How MapReduce Works
  • Anatomy of a Hadoop Cluster

CHAPTER 4 : HADOOP DEMONS

  • Master Daemons
  • Name node
  • Job Tracker
  • Secondary name node
  • Slave Daemons
  • Job tracker
  • Task tracker

CHAPTER 5 : HDFS (HADOOP DISTRIBUTION FILE SYSTEM)

  • Blocks and Splits
  • Input Splits
  • HDFS Splits
  • Data Replication
  • Hadoop Rack Aware
  • Data high availability
  • Data Integrity
  • Cluster architecture and block placement
  • Accessing HDFS
  • JAVA Approach
  • CLI Approach

CHAPTER 6 : PROGRAMMING PRACTICES & PERFORMING TUNING

  • Developing MapReduce Programs in
  • Local Mode
  • Running without HDFS and Mapreduce
  • Pseudo-distributed Mode
  • Running all daemons in a single node
  • Fully distributed mode
  • Running daemons on dedicated nodes

CHAPTER 7: HADOOP ADMINISTATIVE TASKS - Setup Hadoop cluster of Apache, Cloudera and HortonWorks

  • Install and configure Apache Hadoop
  • Make a fully distributed Hadoop cluster on a single laptop/desktop (Psuedo Mode)
  • Install and configure Cloudera Hadoop distribution in fully distributed mode
  • Install and configure HortonWorks Hadoop distribution in fully distributed mode
  • Monitoring the cluster
  • Getting used to management console of Cloudera and Horton Works
  • Name Node in Safe mode
  • Meta Data Backup
  • Integrating Kerberos security in Hadoop
  • Ganglia and Nagios Cluster monitoring
  • Benchmarking the Cluster
  • Commissioning/Decommissioning Nodes.

CHAPTER 8 : HAOOP DEVELOPER TASKS-Writing a Map Reduce Program

  • Examining a Sample Map Reduce Program
  • With Several Examples
  • Basic API Concepts
  • The Driver Code
  • The Mapper
  • The Reducer
  • Hadoop's Streaming API

CHAPTER 9 : Performing several Hadoop Jobs

  • The configure and close Methods
  • Sequence Files
  • Record Reader
  • Record Writer
  • Role of Reporter
  • Output Collector
  • Processing video files and audio files
  • Processing image files
  • Processing XML files
  • Processing Zip files
  • Counters
  • Directly Accessing HDFS
  • Tool Runner
  • Using The Distributed Cache.

CHAPTER 10 : Common Map Reduce Algorithms

  • Sorting and Searching
  • Indexing
  • Classification/Machine Learning
  • Term Frequency - Inverse Document Frequency
  • Word Co-Occurrence
  • Hands-On Exercise: Creating an Inverted Index
  • Identify Mapper
  • Identify Reducer
  • Exploring well known problems using
  • Map Reduce applications.

CHAPTER 11 : Debugging Map Reduce Programs

  • Testing with MR Unit
  • Logging
  • Other Debugging Strategies.

CHAPTER 12 : Advanced Map Reduce Programming

  • A Recap of the Map Reduce Flow
  • Custom Writables and Writable Comparables
  • The Secondary Sort
  • Creating Input Formats and Output Formats
  • Pipelining Jobs With Oozie
  • Map-Side Joins
  • Reduce-Side Joins.

CHAPTER 13 : Monitoring and debugging on a Production Cluster

  • Counters
  • Skipping Bad Records
  • Rerunning failed tasks with Isolation Runner

CHAPTER 14 : Tuning for Performance

  • Reducing network traffic with combiner
  • Reducing the amount of input data
  • Using Compression
  • Running with speculative execution
  • Refactoring code and rewriting algorithms Parameters affecting Performance
  • Other Performance Aspects

CHAPTER 15 : Hadoop Ecosystem- Hive

  • Hive concepts
  • Hive architecture
  • Install and configure hive on cluster
  • Create database, access it console
  • Buckets,Partitions
  • Joins in Hive
  • Inner joins
  • Outer joins
  • Hive UDF
  • Hive UDAF
  • Hive UDTF
  • Develop and run sample applications in Java to access hive
  • Load Data into Hive and process it using Hive

CHAPTER 16 : PIG

  • Pig basics
  • Install and configure PIG on a cluster
  • PIG Vs MapReduce and SQL
  • PIG Vs Hive
  • Write sample Pig Latin scripts
  • Modes of running PIG
  • Running in Grunt shell
  • Programming in Eclipse
  • Running as Java program
  • PIG UDFs
  • PIG Macros
  • Load data into Pig and process it using Pig

CHAPTER 17 : SQOOP

  • Install and configure Sqoop on cluster
  • Connecting to RDBMS
  • Installing Mysql
  • Import data from Oracle/Mysql to hive
  • Export data to Oracle/Mysql
  • Internal mechanism of import/export
  • Import millions of records into HDFS from RDBMS using Sqoop

Chapter 18 : HBASE

  • HBase concepts
  • HBase architecture
  • Region server architecture
  • File storage architecture
  • HBase basics
  • Cloumn access
  • Scans
  • HBase Use Cases
  • Install and configure HBase on cluster
  • Create database, Develop and run sample applications
  • Access data stored in HBase using clients like Java
  • Map Resuce client to access the HBase data
  • HBase and Hive Integration
  • HBase admin tasks
  • Defining Schema and basic operation

CHAPTER 19 : CASSANDRA

  • Cassandra core concepts
  • Install and configure Cassandra on cluster
  • Create database, tables and access it console
  • Developing applications to access data in Cassandra through Java
  • Install and Configure OpsCenter to access Cassandra data using browser

CHAPTER 20 : OOZIE

  • Oozie architecture
  • XML file specifications
  • Install and configure Oozie on cluster
  • Specifying Work flow
  • Action nodes
  • Control nodes
  • Oozie job coordinator
  • Accessing Oozie jobs command line and using web console
  • Create a sample workflows in oozie and run them on cluster

CHAPTER 21 : Zookeeper, Flume, Chukwa, Avro, Scribe,Thrift, HCatalog

  • Flume and Chukwa Concepts
  • Use cases of Thrift ,Avro and scribe
  • Install and Configure flume on cluster
  • Create a sample application to capture logs from Apache using flume

CHAPTER 22 : ANALYTICS BASIC

  • Analytics and big data analytics
  • Commonly used analytics algorithms
  • Analytics tools like R and Weka
  • R language basics
  • Mahout

CHAPTER 23 : CDH4 ENHANCEMENTS

  • Name Node High – Availability
  • Name Node federation
  • Fencing
  • YARn

Getting Started R

  • R Basics
  • Variables and Class
  • Vectors, List, Factors, Matrix
  • Data Frames
  • Missing Values
  • Data Reading and Writing data
  • Data Visualization using GGPLOT
  • If-Else Conditions
  • Function
  • Loops
  • Data manipulation
  • Python

  • Python Basics

  • Python Lists

  • Functions and Packages

  • Numpy

  • Control flow and Pandas

Probability

  • Counting Combinations, Generating Combinations
  • Generating Random Numbers
  • Generating Reproducible Random Numbers
  • Generating a Random Sample
  • Generating Random Sequences
  • Randomly Permuting a Vector
  • Probabilities for Discrete Distributions
  • Probabilities for Continuous Distributions, Converting
  • Probabilities to Quantiles, Plotting a Density Function

Graphics

  • Edges
  • Vertices
  • Graphs
  • Programs

Machine Learning

  • Introduction to Machine Learning
  • Types Of Machine Learning
  • Real time use cases in Machine Learning
  • Types of Algorithms Types of Problems –
    • Regression
    • Classification
    • Clustering
    • Collaborative Filtering
    • Optimization
    • Prediction
  • Regression –
    • Linear Regression
    • Logistic Regression
  • Classification –
    • Logistic Regression
    • Decision Tree,Random Forest
    • KNN,SVM
    • Naive ayes
  • Clustering –
    • K-means Clustering

DESCRIPTIVE STATISTICS

  • Introduction to Advanced Data Analytics
  • Statistical descriptive and inferences for various Business problems
  • Types of Variables
  • Measures of central tendency
  • Dispersion
  • Variable Distributions
  • Probability Distributions
  • Normal Distribution and Properties
  • Skewness and Kurtosis
  • Five number Summary Analysis

INFERENTIAL STATICS

  • Null/Alternative Hypothesis formulation
  • Type I and Type II errors
  • One Sample T-TEST
  • Independent Sample T-TEST
  • Analysis of Variance ( ANOVA)
  • MANOVA
  • Chi Square Test (Non Parametric Tests)

Data quality and outlier treatment

  • Outlier treatment with robust measurements
  • Outlier treatment with central tendency Mean
  • Outlier with Min Max methods
  • Imputation with series means or median values
  • Z score Calculation
  • Sampling and estimation

Data Visualization

  • Stem and leaf
  • Dot Plot
  • Histogram
  • Density Plot
  • Frequency Plot and

Spark GraphX

  • Edges
  • Vertices
  • Types of Graphs
  • Usages
  • Simple Program

Cumulative Frequency plots

  • Box and Whisker Plot
  • Scatter Plot
  • Line Graph
  • Bar Graph
  • Pie Chart
  • Tree Map
  • Cross Tabulation
  • Case Study for Visualization

Data Quality checking

  • Z score Calculation
  • Measure of position (percentile and Quartiles)
  • Measure of asymmetry --Skewness
  • Measure of Peaked-ness --Kurtosis
  • Q-Q probability plots
  • Kolmogorov Smirnov test
  • Shapiro Wilks test
  • Data Normalization
  • Handling missing Values
  • Case Studies for Data Quality Checking
Complete Practical Training with Real-time Databases. Course includes Real-time Case Studies. Register Today
All Classes are Instructor-Led & LIVE. Completely Practical and Real-time with Study Material, Session Notes, Tasks and 24x7 Support.
 
Register Today  Other Popular Courses: SQL DBA Training, MSBI Training, SSIS Training, SSAS Training, SSRS Training [+] More Courses

Job-Oriented Real-time Training @ SQL School Training Institute - Trainer: Mr. Sai Phanindra T