Introduction of Data Warehousing

• What Is Data Warehouse?
• Data Warehouse Architecture
• Explain OLTP, OLAP
• Explain Schemas

Installation

• Pre-build VMware setup
• Informatica Client-Server setup guide on personal system

First Steps

• What Is Etl?
• What Is Informatica?
• Informatica Architecture Overview
• Explain Informatica PowerCenter Components 1] Designer 2] Repository Manager 3] Workflow Manager 4] Workflow Monitor 5] Power Center Admin Console

Sources

• Relational Sources(Data Bases)
• Files Sources(Flat File, Xml, Cobol)

Targets

• Relational Targets(Data Bases)
• Files Targets

Transformations

• What Is Transformation
• Type Of Transformation 1] Active Transformations 2] Passive Transformations
• Ports in informatica
• Connection setup in designer and workflow manager
• Expression Transformation
• Filter Transformation
• Router Transformation
• Sorter Transformation
• Rank Transformation
• Source Qualifier Transformation
• Aggregator Transformation
• Joiner Transformation
• Sequence Generator Transformation
• SQL Transformation
• Stored Procedure Transformation
• Lookup(Different Type Of Lookup) Transformation
• Union Transformation
• Normalizer Transformation
• Update Strategy Transformation
• Slowly Changing Dimension
• SCD Type-1
• SCD Type-2(Date, Flag And Version)
• SCD Type-3

Workflow Manager

• Create Reusable Tasks
• Workflows, Sessions And Worklets
• Task
• Session
• Decision Task
• Control Task
• Event Wait Task
• Timer Task
• Monitoring Workflows And Debugging Errors

Advanced Topics

• Indirect Loading
• Constraint Based Load Ordering
• Target Load Plan
• Worklet, Mapplet
• Session Properties
• Reusable Transformation
• Migration
• Scheduling Workflow
• Parameter and Variables
• Performance Tuning
• Partition

Additional Topics

• SQL Beginner Course
• Unix Basis Commands

Data Warehousing Concepts

• DBMS And RDBMS
• What Is Data Warehousing And Necessity Of It?
• Data Warehousing Architecture
• OLAP,ROLAP, MOLAP
• Data Mining
• Meta Data
• Tables, Views
• Constraints
• Fact And Dimensional Tables
• Partitioning Of Tables
• Concepts Of Referential Integrity
• Concepts Of Modeling
• Database Schema
• Concepts Of Normalization
• Concepts Of Slowly Changing Dimensions
• Concepts Of Data Mart
• Full And Incremental Extraction Process
• Change Data Capture

SQL Concepts

• DDL, DML Commands
• All The Basic Functions (Character Functions, Conversion Functions, Advanced Functions, Mathematical Functions And Date Functions)
• Joins
• Practice Sqls For Some Sample Test Scenarios

ETL Testing

• What Is ETL And Importance Of ETL Testing?
• Creating, Running And Analyzing Sessions/Workflows In Informatica
• Preparation Of Test Strategy, Test Plan And Estimations
• Testing Scenarios, Creation Of Test Cases And Scripts
• Test Case Execution And Defect Tracking And Reporting

Report Testing

• Validation Of The Report Data With DB And UI Testing
• Preparation Of Test Data

Levels of Testing

• Unit Testing
• Integration Testing
• Module Testing
• System Testing

Qlik Sense Introduction & Installation

• What is QlikSense?
• Comparing Qlik Sense Desktop with Qlik Sense
• Installing Qlik Sense Desktop
• How Qlik Sense varies from QlikView?
• Starting with QlikSense Desktop
• Opening an app with QlikSense
• QlikSense Architecture

Starting QlikSense

• Navigating in the user interface
• Data Manager
• Data Load Editor
• Data Model Viewer
• Interacting with the user interface
• Creating apps and Visualizations
• Structuring an app with Sheets

Designing Visualizations

• Different type of Visualizations
• Dimensions and Measures in a CHART
• When to use and What Chart to Use
• Selections in a Visualization
• Settings and Properties of each Visualization

Using Expressions in Visualization

• Expressions , Functions and Variables
• Working with Expression Editor
• Using Functions in Charts
• Using Variables in Expressions
• Set Analysis and Set Expressions
• Working with Master Items

Manage Data

• Adding data from files and databases
• ILoading data with the data load script
• Data Connections
• Various types of Data loads
• Profile Data with QlikSense
• Troubleshooting

Creating a Data Model

• Qlik Sense Data Architecture
• Creating Star and Snowflake schemas
• Use of Joins, Keeps, Concatenate etc..
• Understanding QVD layer
• Working on synthetic keys and circular references.

Advanced Data Modelling

• Qlik Sense Star Schema
• Link Table
• Dimensions Table
• Master Calendar
• Optimizing data models
• Best Practices of Data Modelling

Collaborate in Qlik Sense

• Data Storytelling
• Publishing
• Exporting and Printing
• Advanced Scripting and Chart Functions
• Qlik Tips and Techniques

Qlik Sense Security

• Learning about Qlik Sense App and its security features
• Providing Qlik Sense access to user sets
• Qlik Sense Security rule code

Data Warehouse Concepts

• Introduction of Data Warehouse
• Need arise of DWH
• OLTP V/S DWH
• OLTP V/s OLAP
• Data Warehouse /Data Marts Architecture
• Staging/without Staging
• Dimensions (Simple and Confirm)
• Fact (Simple, fact less, coverage, confirm)
• Warehouse Implementation Methods
• STAR SCHEMA
• SNOW FLAKE SCHEMA
• Difference between STAR and SNOW FLAKE Schema
• Other terminologies Cleansing, Purging etc
• Database Modeling
• Designing Methods
• Logical Designing
• Physical Designing
• Introduction of Dimension Modeling
• Difference between ER-Modeling and Dimension Modeling
• Implementation of Dimension Model
• Forward Engineering and Reverse Engineering
• Introduction of various Database Components
• OLAP ?OLAP/MOLAP/HOLAP

QlikView data structures

• Data structures in QlikView (AQL) • Sample Application walkthrough of QlikView
• Architecture and components of QV
• In-memory BI /OLAP
• Loading data from multiple datasources
• Script editing
• Menu Commands and Syntax
• Handling DATA SOURCE FILES
• The QWT (Access File example) primary data source
• The QWT (Access File example with QVD Conversion) secondary data files
• Script generation
• Creating a connection to the database
• Reading tables into QlikView
• Big data Analytics
• Connecting of Qlikview with Bigdata hadoop

Advanced Scripting

• Condition on a field in a table
• Aggregating Data in Script (Resident Load and Group Function, Order By etc)
• Joining tables (Left, Right, Inner, Outer Joins)
• Concatenation
• Sheet Basics
• Sheet Objects
• Text,list,chart,graph
• Multidimensional,gauges,multibox
• Calculation and expressions
• Reporting formats
• Server setup
• Publisher
• Alerts
• Bookmark

Introduction MS SQL Server

SQL Server

• Introduction to SQL server
• Introduction to T-SQL
• Constraints
• Joins
• Functions
• Views
• Indexes
• Stored Procedures
• Cursors
• Triggers

SQL Server Intergration Services (SSIS)

Introduction (SSIS)

• Over view of Integration Services Solutions
• Integration Services Tools

Developing Integration Services Solution

• Creating an integration services solution
• Using Variables
• Building and Running a Solution

Implementing Control Flow

• Control flow Tasks
• Control Flow Precedence Constraints
• Control Flow Containers

Implementing Logging Debugging and Error Handling

• Overview of Integration Services Logging
• Implementing Logging
• Debugging a package
• Implementing Error Handling

Implementing Checkpoints & Transactions

• Implementing Checkpoints
• Implementing Transactions

Deploying Packages

• Package Configurations
• Deploying Packages

Managing and Securing Packages

• Implementing key Performance Indicator
• Implementing Actions
• Implementing Perspectives
• Implementing Translations

Deploying and Securing an Analysis Services Database

• Deploying an Analysis Services Database
• Securing an Analysis Services Database

Maintaining a Multidimensional Solution

• Configuring Processing Settings
• Logging ,Monitoring and Optimizing an Analysis Services Solution
• Backing Up and Restoring an Analysis
• Services Database

SQL Server Reporting Services(SSRS)

Introducation

• Managing Packages
• Securing Packages
• Scheduling Jobs

Authoring Basic Report

• Creating A Basic Table Report
• Formatting Report Pages
• Calculating Values

Enhancing Basic Reports

• Interactive Navigation
• Displaying Data

Manipulating Data Sets

• Defining Report Data
• Using Parameters and Filters
• Using parameter lists

Using report models

• Creating report models
• Using report builder

Publishing and executing reports

• Publishing report
• Executing reports
• Creating cached instances
• Creating snapshots and report history

Using subscriptions to distribute reports

• Introduction to report subscriptions
• Creating report subscriptions
• Managing report subscriptions

Administering reporting services

• Server Administration
• Performance and reliability monitoring
• Administering report server databases
• Security administration

Programming reporting services

• Querying for server information using a web service
• Automating report management
• Rendering reports
• Creating custom code

SQl Server Analysis Servers (SSAS)

Introducation

• Overview of Data Anyaysis Solutions
• Overview of SQL Server 205 Analysis Services

Creating Multidimensional Analysis Solutions

• Developing Anaysis Services Solutions
• Data Sources And Data Source Views
• Creating a Cube

Working With Dimensions

• Configuring Dimensions
• Define Hierarchies
• Sorting And Grouping Attributes
• Working With Measures
• Working With Measures Groups

Analytical MDX

• Analytical MDX Introducation
• MDX queries MDX vs. SQL
• MDX Stored Procedures
• Adding Calculations to a Cube

Customizing Cube Functionality

• Managing Packages
• Securing Packages
• Scheduling Jobs

Data Warehousing Fundamentals

• Introduction of Data warehousing
• Purpose of Data warehouse
• DW Architecture
• Data warehouse vs. OLTP Applications
• Data Mart

Data Modeling

• Introduction of Data Modeling
• Entity Relationship(ER) Model
• Fact and Dimension Tables
• Logical Modeling
• Physical Modeling
• Schemas – Star Schema & Snowflake Schemas
• Fact less Fact Tables

Process – ETL (Extraction, Transaction & Load)

• Introduction of Extraction, Transformation and Loading (ETL)
• Types of available ETL Tools
• Key tools in the ETL market

Installation Process (Datastage)

• Windows server
• Oracle
• .NET
• Datastage ✔5X2 & 8x

C Difference

• Server jobs & Parallel jobs

Components in Datastage

• Administrator client
• Designer client
• Director client
• Import/export manager
• Multi-client manager
• Console for IBM Information server
• Web console for IBM information server

Introduction to IBM web sphere Datastage and quality stage 8x

• Introduction to Datastage
• IBM Information server Architecture
• IBM Data Quality Architecture
• Enterprise Information Integration
• Web Sphere Data stage Components

Datastage Designer

• About Web Sphere Data stage Designer
• Partitioning Methods
• Partitioning Techniques
• Designer Canvas
• Central Storage
• Job Designing
• Creating the Jobs
• Compiling and Run the Jobs
• Exporting and importing the jobs
• Parameter passing
• System (SMP) & Cluster system (MPP)
• Importing Methods (Flat file, Txt, Xls and Database files)
• OSH Importing Method

Parallel Palette

• Databases Stages
• Oracle Database
• Dynamic RDBMS
• ODBC
• SQL Server
• Teradata

File Stages

• Sequential File
• Dataset
• Lookup File set

Dev / Debug Stages

• Peek
• Head
• Tail
• Row Generator
• Column Generator

Processing Stage

• Slowly changing dimension stage
• Slowly changing dimensions implementation
• Aggregator
• Copy
• Compress
• Expand
• Filter
• Modify
• Sort
• Switch
• Lookup
• Join
• Marge
• Change Capture
• Change Apply
• Compare
• Difference
• Funnel
• Remove Duplicate
• Surrogate Key Generator
• Pivot stage
• Transformer

Containers

• Shared Containers
• Local Containers

Ab initio Architecture

• Understanding GDE
• Understanding Co-op System
• Understanding EME/Application hub

Understanding Host connection settings

• Configure host settings

Understanding Abinitio Jobs/ Graphs

• Record formats, DML, Transform functions, Key specifiers
• Dataset components/ Table components/Program components
• Layout
• Parametersy
• Validating the graphs and understanding the error messages
• Component Overview-Editors
• Record Format Editors
• Expression Editors
• Transform Editors
• Key Specifier Editors
• Package Editors
• Variable Editors

Component Overview-Dataset components

• Input file
• Output File
• Intermediate file
• Lookup files and Lookup Functions

Component Overview-Dataset components

• Filter by expression
• Dedup sorted
• Reformat
• Aggregate
• Scan
• Rollup
• Join
• Match sorted
• Normalize
• Denormalize Sorted

Understanding partitioning Concepts and mutifiles

• Understanding Multifiles
• Understanding Parallelism
• Data Parallelism
• Component Parallelism
• Pipeline parallelism

Component Overview-Partition components

• Broadcast
• Partition by key
• Partition by expression
• Partition by round robins
• Partition by range
• Partition by load balance

Component Overview-Departition components

• Gather
• Concatenate
• Interleave
• Merge

Component Overview-Database Components, dbc file configuration

• Understanding Database Configuration
• Input Table
• Output Table
• Run SQL
• Truncate Table
• Update Table
• Join with DB

Component Overview- Miscellaneous Components

• Redefine Format
• Gather Logs
• Replicate
• Run Program
• Trash

Component Overview- Validate Components

• Check Order
• Compare Records
• Validate Records
• Generate Record
• Create Data
• Compute Checksum

Understanding Performance Tuning

• Component Folding

Additional Graph concepts

• Tagging
• Branching
• Checkin-Checkout
• Locking
• Common projects
• Dependency analysis/Impact analysis
• Generic Graphs/Reusable Graphs
• Conditional components
• Concept of Phases and check point

Understanding various command line options/ configuration Variables

• M Commands
• Air Commands
• Configuration Variables
• Environment Variables

Introduction and Overview

• Why Tableau? Why Visualization?
• Level Setting & Terminology
• Getting Started & creating some powerful visualizations quickly
• The Tableau Product Line
• Things you should know about Tableau

Getting Started

• Connecting to Data and introduction to data source concept
• Working with data files versus database server
• Understanding the Tableau workspace
• Dimensions and Measures
• Using Show Me!
• Tour of Shelves (How shelves and marks work)
• Building Basic Views
• Help Menu and Samples
• Saving and sharing your work

Creating Views

• Marks
• Size and Transparency
• Highlighting
• Working with Dates
• Date aggregations and date parts
• Discrete versus Continuous
• Dual Axis / Multiple Measures
• Combo Charts with different mark types
• Geographic Map Page Trails
• Heat Map
• Density Chart
• Scatter Plots
• Pie Charts and Bar Charts
• Small Multiples
• Working with aggregate versus disaggregate data
• Analyzing
• Sorting & Grouping
• Aliases
• Filtering and Quick Filters
• Cross-Tabs (Pivot Tables)
• Totals and Subtotals Drilling and Drill Through
• Aggregation and Disaggregation
• Percent of Total
• Working with Statistics and Trend lines

Getting Started with Calculated Fields

• Working with String Functions
• Basic Arithmetic Calculations
• Date Math
• Working with Totals
• Custom Aggregations
• Logic Statements

Formatting

• Options in Formatting your Visualization
• Working with Labels and Annotations
• Effective Use of Titles and Captions
• Introduction to Visual Best Practices

Building Interactive Dashboard

• Combining multiple visualizations into a dashboard
• Making your worksheet interactive by using actions and filters
• An Introduction to Best Practices in Visualization

Sharing Workbooks

• Publish to Reader
• Packaged Workbooks
• Publish to Office
• Publish to PDF
• Publish to Tableau Server and Sharing over the Web

Putting it all together

• Scenario-based Review Exercises
• Best Practices

Teradata Basic Contents

• Teradata Basics and Data Warehouse Concepts
• The Primary Index
• Hashing of the Primary Index
• Partition Primary Index (PPI) Tables
• Secondary Indexes
• Columnar Tables
• Space
• The User Environment
• Join Indexes
• Locking
• View Functions
• Data Modeling Techniques
• Things to Consider
• A DBA’s Best Friend – The Data Dictionary
• Compression
• Protection Features
• Viewpoint
• TASM
• Teradata Load Utilities Introduction
• Teradata Tools and Everything Active

Tera Data Architecture

• Parsing Engine(PE)
• Request and Response Parcel
• Access Module Processors (AMPs)
• Bynet
• Data access Handling
• TD Config Utilities
• Config and Reconfig

Tera Data Database Space

• Permanent Space
• Spool Space
• Temp Space
• Indexes

DBC Dictionary Tables

• Data dictionary
• Parsing Engine
• System Views
• Database Views
• DBC Users View
• Indices View
• All Temp Tables View
• Using DBC AMP Usage
• Using DBC Table Size
• Logon and Logoff Tracking

Access Rights, Roles, Profiles

• Access rights (Auto,implicit,Explicit Rights)
• Roles and Profiles
• Creating Roles and Profiles
• DBC.RoleInfo and DBC.Profilev Info

Query analysis and Tools

• Database Query Log (DBQL)
• DBQL Tables and Views
• Access Logging

System Access Control Level

• TD Password Encryption
• Password Security Features
• Host Logon Processing
• GRANT/REVOKE LOGON
• Session Related Views
• DBC.sessionInfo Info View
• Data Access Info Views
• Access LogRules Views
• Access Log Views

TD Manager

• TD Manager Apps
• TD Dashboard
• Priority Scheduler Admin
• TD Manager Service
• Starting TD Manager

Monitoring Tools

• Performance Monitor
• PMON Main window
• PMON sessions Screen
• TD Administrator
• TD manager Dynamic Utilization Charting
• Locking Logger

Monitoring Tools

• Performance Monitor
• PMON Main window
• PMON sessions Screen
• TD Administrator
• TD manager Dynamic Utilization Charting
• Locking Logger

TD Remote Console Utilities

• Starting DB Window
• QRYCONFIG
• QRYSESSN
• RCVMANAGER
• SHOWLOCKS
• VPROCMANAGER
• SHOWSPACE
• SHOWBLOCKS
• PACKDISK
• SCANDISK

Archiving Data

• Archive and Recovery Statements
• Recovery vs. FastLoad
• Invoking Archive
• Restart Log
• ANALYZE Statement
• Archive
• Database DBC Archive
• Indexes option
• Database DBC Archive
• Archive and Recovery (ARC) Examples

Restoring Data

• Restore
• Copy
• Copying Tables
• Build

1. Introduction to Power BI
2. Get data into Power BI Desktop
   • Excel
   • Web
   • SQL
   • Folder Option
3. Data Transformation / Power Query Editor
   • Changing data type of column
   • How to Promote Header
   • How to eliminate blank rows
   • Split column
   • Transpose data
   • Unpivot Columns
   • Fill Down
   • Column from example
   • Conditional Column
   • Group by
   • Append Queries
   • What is applied steps?
   • What is data source settings?
4. Data Visualisation
   • Clustered Bar/Column Chart
   • Stacked Bar/Column Chart
   • 100% Stacked Bar/Column Chart
   • Line Chart
   • Area Chart
   • Combo Chart
   • Pie/Donut Chart
   • Gauge
   • Cards
   • Maps
   • Tables
   • Matrix
   • KPI Chart
   • Funnel Chart
5. Filters
   • Filters on all pages
   • Filters on this page
   • Filters on this visual
   • Drill Through
6. DAX(Data Analysis Expression)
   • Calculated Column vs Measure
   • Calculate ()
   • Filter ()
   • Sum ()
   • Average ()
   • Count ()/ CountA ()
   • Distinct Count ()
   • Max ()
   • Min ()
   • SumX ()
   • AverageX ()
   • MaxX ()
   • ALL ()
   • ALLSelected ()
   • Time intelligence Function
     • MTD ()
     • QTD ()
     • YTD ()
     • SAMEPERIODLASTYEAR ()
     • DATEADD ()
     • DATESINPERIOD ()
   • Conditional/Logical Operator
     • IF statement
     • And (&&)
     • OR (||)
     • IN
7. Bookmark & Selection
8. Edit Interactions
9. Publish a Report to Power BI Service account
10. RLS
11. Create Dashboard on Power BI Service account
12. Schedule a report on Power BI Service account