TERADATA DBA CONTENT, DATA WAREHOUSE & DATA MODEL FUNDAMENTALS

IT Data Fundamentals & MSBI Existence
  • Data, Data Availability in IT
  • Data, Data Availability in IT
  • IT data storage areas
  • Datawarehouse, BI definitions and layers
  • MSBI existence in IT, Product services
  • MSBI Tools and Components
  • MSBI Opportunities
Creative visualization through screen shots
MSBI and Competitiveness
  • What are BI, MSBI, and SSBI? Importance in the current Enterprise? Competitive advantages
  • MSBI vs Other BI tools
  • MSBI 2016 Vs Others (2014, 2012, 2008, 2005)
  • Hardware & Software requirements
  • Instances and installation methods
Tools & Utilities to work with MSBI
  • SSDT, BIDS, SSMS, DQS Client / Server / Installer
  • Sqlserver Profiler, Deployment Wizards
  • Command Line Utilities
      a)DTUTIL b) DTUTILEXEC c) RS RSCONFIG d) ASCMD etc…
Data warehousing fundamentals
  • Data definitions
       (Data, Information, BI, Mining, ODS, DSS, Analysis, OLTP, OLAP, OLCP)
  • Dwh Characteristics, Principles
  • Dwh approaches (Top down/Bottom up)
  • Dwh Life Cycle steps
  • Data Mart & Types
  • Data Processing Types (OLAP)
  • Granularity and various granularities
  • Diff between OLTP and OLAP
Creative visualization through screen shots
Dimensional Modeling Fundamentals
  • Dimension, Dimension Table & types
  • Fact, Fact Table & measures types
  • Fact less fact table
  • Various schemas (Star, Snow Flake, Galaxy & Hybrid )
  • Surrogate key and usages in real time
  • Model, Business, Conceptual, Logical, Physical Data Models
Real time project tables, models and tables



SQL SERVER INTEGRATION SERVICES [SSIS(Basic-Advanced):15 hours]:2016 & 2014

SSIS in real-time projects (DWH and OLTP)
  • SSIS resources in the BI and Non BI projects
  • SSIS collaboration with other DWH tools
  • SSIS strengths than contemporary ETL tools
  • SSIS installation (SSDT, SSMS) and observation
  • SSIS real time projects flow and explanation
  • SSIS Development / Support responsibilities
  • SSIS Testing, Deployment, Versioning, Scheduling, Defect Handling
  • Normalization vs demoralization in DWH
  • Dimensional Model (Facts & Dimensions)
SSIS-2016 Logical & Physical Architectures
  • What is SSIS? Detailed logical architecture and components
  • SSIS-2016 newly added features, disconnected features, enhancement and break down features
  • SSIS Practical architecture, Solution, Project and Package creations and executions
  • Connection Managers, Shared Data Sources, Parameters(2012,2014,2016)
  • Discussion on new Features in SSIS 2016
  •   Balanced Data Distributor,
  •   OData v4 data sources & and Bug Fixes
  •   Data Feed Publishing Components
Real time DWH-ETL flow and SSIS operations
SSIS resources and responsibilities
ETL operations in SSIS (Data Flow Task) (Extract, Transform and Load)
  • Objective of Dataflow and process flow
  • In memory model and performance tips
  • Aggregate, Data Conversion, Derived column, Character Map, Audit
  • Merge, Merge Join, Union All, Sort
  • Multicast, Conditional Split, Oledb command
  • Lookup, Fuzzy lookup, Copy column
  • Pivot, Un pivot, Term Extract, Term Lookup
  • CDC, Data Cleansing, Script Component
  • Checksum transformation (2014) real usage
Real time scenarios, Business Logics Custom Operations, Complex issues & situations
Implementing SCDs, CDC in DWH using restarts, transactions and data tracking
  • Using Check points to configure restarts and preserve operations
  • Implementing Transactions for historical and business evaluations
  • Implementing Slowly Changing Dimensions in Data warehouse using No, Full and Partial chches
  • No history tracking of dimension (Type1)
  • Full history track and methods (Type2)
  • Partial history track and need (Type3)
  • Manual Implementation of SCDs, Incremental (Delta) Loading
  • Change data capture in data warehouse tables for the events insert, update and delete on OLTP
Real time scenarios and important tips
Versioning and scheduling in DWH projects
  • SSIS versioning, other versioning tools
  • SSIS Scheduling, other versioning tools
  • Jobs, Proxy and Sqlserver agent account
Practical explanation on versioning and scheduling



SSIS ETL, Windows and Sql Server Admin tasks (Control Flow Tasks and Operations)
  • Looping usages (For Each loop, For loop) and various scenarios (Files, Datasets, XMLs, Excels)
  • Transactional operations and Parallel processing implementation using Sequence container
  • Using File System to move, copy, create folders
  • Execute Sql for Single, Multi SQL, Procedure, functions with Various parameters and implementing DML, DDL statements
  • Working on Master, Child Packages using Execute Package task and variables
  • Expression task for evaluations, iterations
  • Using Script task for customized operations
  • Execute process task for Zip, Exe Operations
  • Profiling and cleansing the data using Data Profiler, DQS cleansing operations
  • Controlling tasks using Precedence Constraints
Real time scenarios and many important tips
Data Loading, Fact and Dimensions load
  • Direct load and components (Truncate & Load)
  • Fast load implementation in SSIS
  • Incremental load in warehouse tables
  • Wizards, Tools, Tasks for different types of load
Practical on these with various approaches
Debugging, Logging and Event handling
  • Trouble shooting and Bottlenecks identification using DataViewer, BreakPoint, Hit count debug methods
  • Real time Logging, usages and observations
  • Log Providers and Catalog Logging
  • Implementing Basic, Verbose and Performance
  • Event vs Error Handling, real time handling
Error identification, Bottleneck resolutions
Command Line Utilities Utilization
  • Various environments operations of packages using a) DTUTIL b)DTEXECUI c) DTEXEC
Practical execution with configurations
Configurations and Parameters in DWH
  • Various ways of configuring SSIS packages (Legacy)
  • User interactions to the packages for incremental load
  • Parameters and environments in Catalog Database
Real time handling, configuring and passing values
Security, Deployment in real time projects
  • Security in various ways (Password protection & Role based security), DBA role in security
  • Package deployment using manifest file (File System, Sqlserver deployment)
  • Project deployment using Catalog database (SSDB) and explaining various features
Bottlenecks, Performance Tuning
  • Production issues in real time, other pkg issues
  • Bottleneck, various ways to identify bottlenecks
  • Tuning Source, Destination, Transformation,Packageand System level
  • Data pipeline optimization, in memory model
  • Execution trees, memory buffer optimization
Real time issues with detailed explanation
Real time errors, memory issues and system issues
SSAS in real-time projects (DWH & BI)
  • SSAS resources in the BI projects
  • SSAS collaboration with other DWH tools
  • SSAS strengths than contemporary tools
  • SSAS installation (SSDT, SSMS) and observation
  • SSAS real time projects flow and explanation
  • SSAS Development / Support responsibilities, SSAS Processing
  • SSAS Testing, Deployment, Versioning, Scheduling and Defect Handling
  • Normalization vs demoralization in DWH
  • Dimensional Model (Facts, Dim) process
BISM (Business Intelligence Semantic Model) role in analysis and data mining
  • SSAS 2016, 2014 and 2012 discussions
  • Installation of SSAS, Analysis Modes
  • Multidimensional model and real time usage
  • Tabular mode and real time usage
  • Power Pivot and real time usage
  • Over view of SSBI,Multidimensional Model, Tabular Model and Power Pivot
  • Power Pivot for Excel and Share Point
  • What is OLAP, Types of OLAP (MOLAP, ROLAP, DOLAP, HOLAP)
  • Sample project, Modeling, Designing & Solution
Detailed diagrammatic presentation
Visualizations on multi-dimensional & tabular
Multi-dimensional model (Cube Data mart) creation in real time with various settings
  • Developing Analysis Services Multi Dimension Corporate Solution with SSDT tools, Command lines.
  • Data source view creation
  • Add / Remove tables
  • Design / Diagram panes
  • Named calculations
  • Named Queries
  • Relationships
  • Identifying dimensions with types (Date, Time, Regular, Scenario etc…)
  • Creating, Deploying and Processing of cubes
Class room cube creation with practical
Real time cube creation and explanation
Kimball approach of Facts, Dimensions add / removal (configuring measures and dimensions)
  • Working with Measures, Measure Groups,Members and Dimensions in data warehouse
  • Maintaining measures in analytical format using various properties
  • Configuring Dimensions, taking all attributes, composite keys, Duplicate Attribute Keys
  • Discretization feature, Sort order
  • Hierarchies, levels, members, performance improvement features implementation
  • Linking dimensions , gathering/ collecting calculations, KPIs and actions
Complete practical on these concepts
Detailed coverage of each concept with its features (not mentioned due space issue)
Semantic operations on Cube and its functionalities in DWH
  • Writing various calculations using MDX
  • Preparing script with multiple members
  • Pros and cons of various members
  • Implement Key Performance Indicators for effective report analysis
  • Pull model analysis with Actions
  • Implementing Perspectives / Translations to restrict visibility and for multi-language translation
Effective relationships (Dim & Fact)
  • Working with Dimension Usage Wizard
  • Real time usages of various relationships (Regular, Referenced, Fact etc…)
Working on multiple tables to show practical
Parallel processing of data using Fact Partitions and various ways of processing
  • Implementing Partitions (Table and Query)
  • Usage of various storage modes
  • Automatic processing using Proactive Caching
  • Cube, Fact and Dimension table processing (Full, Clear, Data, Incremental, Add, Index, Update, Default etc…)
Practical on this with performance observations














Faster retrieval of data using Aggregates design and Usage based optimization
  • Working with Designing Aggregations
  • Normal Mode and advanced mode
  • Usage based optimization
Practical on this with performance observations
Working with MDX and components
  • What is MDX, MDX Fundamentals
  • Identifiers, Expressions & Literals
  • Data Types & Operators
  • Functions (twelve types of functions)
  • MDX language reference DDL, DML etc…
  • MDX multi-function operators
  • Sub cube expressions
  • Member, Measure, Tuple, Set features
  • Advanced Time and Date functions for cumulative preceding and following analysis
  • MDX expressions in SSDT
  • MDX Querying against OLAP Cube
Explaining with various queries and examples
Dedicated material and more practical oriented
Many functions, features explanation practically
(Due to space issue, all features are not mentioned)
Data Analysis Extensions [DAX]
  • Need of DAX in tabular models, Excel
  • Expressions in DAX
  • Identifiers, Literals and Operators
  • Functions and various types of functions
Explaining with various queries and examples
Dedicated material and more practical oriented
Many functions, features explanation practically
(Due to space issue, all features are not mentioned)
SSAS Deployment, real time methods
  • BIDS / SSDT deployment
  • SSAS deployment wizard
  • XMLA method of deployment
  • Backup and Restore
  • SSAS synchronization wizard
  • AMO objects (Analysis Services Management Objects)
SSAS Cube and MDX Performance tuning
  • Production issues while cube processing
  • Memory, Time and threads issue at MDX
  • SSAS Database and Cube Audits - Query Logs
  • Security Audits and Need for Optimizations
  • DB Audits, Usage Based Optimization
  • Aggregations, Usage Based Optimization Options (MOLAP) and Filters
  • Partitions & Query binding
  • Named Calculations, Queries and Named Sets
  • Flight Recorder Settings, Query Logs
  • Data Sampling Intervals and MDX Query Tuning Process
  • 100% Aggregations (FULL), CPU and Space Thresholds
  • Proactive cache solution for fresh update
  • Lazy Aggregations & UBO in Partitions, Performance Settings
  • Writeback option to save OLTP analysis
  • Writeback Partitions - Cube & Dim, UPDATES
  • Using MDX Expressions and Queries
  • Writeback Tables, MDX Transactions - COMMIT / ROLLBACK
  • Subcube - Creation, Advantages Usage. MDX Query Plans
  • Subcube - DESCANDANTS, PARENT & MEMBER Functions
  • SSAS threads, taking required number of threads
  • SSAS Trace Monitoring and Sql server Profiler
Explaining by taking a real time situation
Many functions, features explanation practically
(Due to space issue, all features are not mentioned)
SSAS-TABULAR MODEL CONCEPTS
  • Workspace Database, Workspace Server, Direct Query, Backup to disk
  • Installation steps, error mechanisms and error rectification process
  • Creating a tabular model and setting model properties
  • Adding data to the model, renaming tables, filtering columns
  • Rename Columns
  • Monitoring relationships, Providing relationships
  • Create Hierarchies
  • Create partitions
  • Create perspectives
  • Create Roles
  • Create KPIs
  • Deployment
  • Using in Excel Power Pivot and PowerView
Explaining by constructing a real time tabular model
Many functions, features explanation practically
(Due to space issue, all features are not mentioned)