Data Warehousing / Informatica

  • Duaration - 3 Months

Data warehouse as ‘a subject-oriented, integrated, time-variant and non-volatile collection of data.’ Extremely useful for Data Analysts, this data helps them to take business decisions and other data-related decisions in the organization.

Requirements
  • Broad vision
  • Deep technical expertise in one or two areas
  • Communications skills
  • The ability to analyze data sources
  • An early-warning system
  • SQL database

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


Introduction Data Warehouse
  • Explain about Data Warehouse?
  • Core concept of Data Warehouse
  • Need for DWH
  • Understanding Data Warehouse Concept
  • Data Warehouse Design and Architecture
  • Explain on different types of Schema
  • OLTP vs OLAP
  • Dimensional and Fact Table design
  • Explain on Star Schema
  • Explain on Snow Flake Schema

Getting Start with Power BI
  • Introduction to Power BI
  • Architecture of Power BI
  • Component of BI
  • Understanding on Basic concept on Power BI
  • Pre-requisite to Install Product
  • Install Product on OS
  • Verify Install
  • Configure/Customize of Microsoft Power BI
  • Explain of Integration Services

Power BI Service
  • Overview of BI Services
  • Explain on Live Dashboards
  • Interactive Reports
  • Data Visualization
  • Understanding on Power BI Desktop

Working with Data and Modeling
  • Connect to Multiple Data sources
  • Live Connectivity with SQL Analysis server
  • Using Query Editor
  • Data Loading on Individual Tables
  • How to Transform data with Query Editor
  • Cleaning Formatted / Unformatted Data
  • Explain on Manage Data Relationships
  • Create Calculated Columns
  • How to Optimizing Data Models
  • Show to Create Calculated Tables
  • Explorer on Time-Based Data

Working with Visualization
  • Overview of Visualization
  • Create Simple Visualization
  • Explorer to use Map Visualizations
  • Combination Charts
  • Create and apply format Slicers
  • Use Funnel and Waterfall Charts
  • Create basic Reports and explorer on Reports
  • Apply Styles in Shapes, Text Fields, and Images on Reports
  • Style on Page Layout and Formatting
  • Interactions between Visualization
  • How to Customize Reports for summarization and Category
  • Z-order of Report Elements
  • Create Table and Matrices
  • Build Bar, Pie, Line and Scatter Charts from Loaded Data

Power BI and Excel
  • Overview of BI and Excel
  • Import data in Excel files
  • Explorer on Table Analysis Tools in Excel
  • Identifying Data Groups

Working with Power Query and Power Map
  • Introduction to Power Query and Power Map
  • Explorer on Discover and Import data
  • How to Transform, Merge and Filter Data
  • Grouping and Aggregating Data
  • Analysis with Power Map
  • How to Create Map-Based Graph
  • Create Heat and Region Maps
  • Analyzing changes over Time

Working with Publish and Sharing
  • Overview of Publish and Sharing
  • How to Publish Reports to Power BI Service
  • Import and Export Power BI
  • Manual Republish
  • How to Create Group in Power BI
  • Create/Edit Content Pack
  • Publish Report to Web from Power BI

Working with DAX
  • Overview of DAX
  • Type of DAX Calculation
  • Different kind of Functions in DAX
  • Explain on DAX Operators
  • Create Calculation in Power Pivot
  • Measures vs Attributes
  • How to Create Common Aggregates
  • Table Relationship with DAX
  • How to Create KPIs

Case Studies on Microsoft Power BI
Discussion with Interview Question and Best Practices on Microsoft Power BI

Get The Coaching Training Today!

For more information for courses contact us