DAY 1: SSIS Introduction, Installation
- SQL Server Integration Services Intro
- Purpose of SSIS: ETL & DWH Operations
- SSIS Advantages: Data Loads, Operations
- SQL Server Data Tools & ETL Wizards
- SSIS Design & Development Tools: SSDT
- LIVE (Deployment) Environment, SSISDB
- Data Warehouse Design & ETL Process
- Data Warehouse (DWH) Design Principles
- SSIS Database Install & SSIS Catalog
- SSIS Configuration and Encryptions
- SSIS Catalog DB Creation, Verification
- SSDT Tool: SQL Server Data Tools - SSDT
- SSIS Developer Environment (SSDT)
- Basic Vocabulary: ETL, DWH, Data Flow
- SSIS Package Environment, SSDT Projects
- SSIS Solutions and Project Templates
- Version Control and SSIS Design Plan
- SSIS Video Training: Lab Plan, Resources
|
DAY 1: Introduction to SSAS & MDX
- Install & Configure SSAS 2017, 2016
- SSAS Component, Tools - Operation Modes
- Multidimensional Mode : Purpose, & Usage
- Tabular Mode : Purpose, Usage (ROLAP)
- PowerPivot Mode : Purpose and Usage
- Multidimensional Databases (OLAP)
- ROLAP & MOLAP Stroage Differences
- SSAS Service Accounts - SQL Browser
- SSDT 2017, 2015 Installation, Verification
- SSDT 2013 Installation and Verification
- SSAS Training - Lab Plan and Resources
- OLAP: Measure Groups & Measures
- Cube Design Entities & MDX Queries
- Attributes, Hierarchy and Members
- Meausure Groups and Dimensions
- SSAS Workflow in Real-world - Kimball
- SSAS Workflow in Real-world - Inmon
- Data Source Config, DB Installations
|
DAY 1: SSRS Intro - Install, Configure
- Report Engine Architecture, Databases
- SSRS Installation, Tools & Configuration
- SSRS 2016 : Configure Report DBs, URLs
- SSRS 2014 : Configure Report DBs, URLs
- Report Server URL and Test Connections
- Report Manager URL, Test Connections
- Portal URL in SSRS 2016 in Web Services
- Three-Phase Report Life Cycle in SSRS
- Report Server and TempDB Configuration
- Virtual Directories (VD) in Web Services
- Web Portal Communication URL & Usage
- SSRS Training and Lab Plan - DEV, ADM
- Report Designer & Report Builder Tools
- Report Builder Tool Installation, Usage
- Report Designer Tool Installation, Usage
- Report Dev Operations, Test Environment
- Report Deployment with Admin Accounts
- Report Database Creation, RDL Files
|
DAY 2: Data Flow Tuning Options - SSIS
- Control Flow Tasks: Architecture, Purpose
- Data Flow Tasks - Architecture and Usage
- SSIS Packages: Data Flow Operations
- Pipelines & Connections in Data Flow
- SSIS Package Design and Execution
- Data Flow Objects, Control Tasks in SSIS
- Data Extraction Drivers with Data Flow
- OLE DB & SQL Server Connections Usage
- SSIS Packages and Control Flow Items
- SSPI, Data Source and Initial Catalog
- DTSX Files for SSIS Package Executions
- Executions, Package Errors and Logs
- Data Flow Transformations, Data Splits
- Excel Connections & Memory References
- Source Assistants & Destination Assistants
- DAT File Imports, Data Flow, Annotations
- Package Errors, Solutions in Real-time
- SSIS Project Configuration & Debugging
- Build & Version Control Options for SSIS
- 64 Bit & 32 Bit Configuration Options
|
DAY 2: Basic Cube Design with SSAS
- Analysis Entities in OLAP Database
- OLAP Databases and Cubes For Analysis
- Kimball & Inmon Methods Of SSAS Design
- A Realworld Example - Sales Scenario
- Sample Analysis Requirements & Entities
- Basic SSAS Database Entities & Usage
- SQL Server Data Tools & SSAS Templates
- Data Source, Views and Cube Designer
- Dimension Wizard & Attribute Selection
- BUILD, DEPLOYMENT & PROCESS options
- OLAP Cube Process - Online Deployments
- Cube Browsing in SSMS & ADOMD Client
- Excel Connections for OLAP Cube Access
- Excel PIVOT Tables and Chart Reports
- Piechart Reports and Attribute Filters
- Common Deployment Errors & Solutions
- Server Impersonation - NT AUTHORITY
- OLAP Deployment Warnings and Solutions
- Data Source to SSDT : Issues, Solutions
- End to End Implementation of SSAS
|
DAY 2: Report Design - I
- Visual Studio - SQL Server Data Tools
- Report Templates, Usage & Reports
- Designing Basic Reports - Sources, Fields
- Report Project Wizard - Usage, Reports
- Data Source Connections and Options
- Query Designer, Query Builder & Import
- Table Reports and Matrix Reports
- Layout and Drilldown Reports, Blocks
- Stepped Reports, Multi Field Drilldown
- Report Project - Dataset and Reports
- Report Toolbox and ReportData, Textbox
- Table Headers, Formatting, Expressions
- Alternate Row Colors, Report Expressions
- Report Formatting, Expression Reusability
- IIF, Format, Ceiling, Round, Concat
- Dynamic Formatting Options, Expressions
- Textbox Properties - Date & Time, Styles
- Report Timeout and Tool Box Report Data
- Initial Catalog For Static/Dynamic Reports
- Planing for Dashboards, Shared Datasets
|
DAY 3: SSIS Entities with Data Loads
- ETL and DWH Implementations with SSIS
- Kimball Method of BI Design, Dimensions
- Bulk Loads and Data Imports in SSIS
- Historical Loads & Incremental Updates
- Debugging Controls in SSIS - Limitations
- Data Flow Debugging Data Viewer Options
- Synchronous & Asynchronous Data Loads
- Row, Partial, Full Blocking Transformation
- Avoiding Blockings and Buffer Reuse
- MERGE, MERGEJOIN and UNION ALL
- SORT, NOSORT Options, Advanced Sort
- Conversion Transformation, Expressions
- Tuning Data Flow Tasks with Fast Loads
- Fast Load Options in OLE DB Connections
- Tabular Data Stream (TDS): Packet Sizing
- SSIS Tuning with Row Size Calculations
- Rows Per Batch - Data Commit Options
- Transactions & Batch Scoped Data Loads
- Regular Loads and Fast loads in ETL / DW
|
DAY 3: Cube Design - Keys, Hierarchies
- Attribute Keys and Relations
- Entities, Computed Columns, Filters
- Database Dimensions Identifications
- Cube Dimensions: Identification, Usage
- Data Source Connection Strings, Options
- Named Queries and Calculations in DSV
- DSV : Data Explore & Verifications
- DSV Expressions & Named Calculations
- Cube as a Subject Oriented OLAP Object
- Cube Access Methods : Cube Browsers
- MDX Filters and Browser Options
- Conditional Formats and Formatting
- Cell Styles and ODC Connections
- Cube Browser - Drilldown & Drillthrough
- Hierarchies : Attribute Levels and Purpose
- Performance Warnings, Attribute Relations
- Composite Attribute Keys, Hierarchies
- Duplicate Key Errors and Member Names
- Cube Slicing and Dicing Options
|
DAY 3: Report Design - II
- Row Groups and Column Groups
- Row Groups, Parent - Child Groups
- Adding Groups to Existing Rows
- Group Headers & Footers, Sub Totals
- Group Visibility and Toggle Options
- Row Group, Header/Footer Properties
- Column Groups and Advanced Options
- Repeat-On-New-Page and Properties
- Column Groups and Data Grouping
- Drill-down Reports & Row Groups
- Drill-down Reports and Column Groups
- Table Reports Vs Matrix Reports
- Adding Images, Tooltip and Sizing
- Report Headers & Footers - Globals
- Merging Report Fields, Sub/Grand Totals
- Group Edits, Removing/Adding Headers
- Column Groups - Advance Mode - Options
- Repeating Header on Every Page
- Report Globals & Metadata Properties
|
DAY 4: Data Extraction (ETL) & DWH
- Legacy Data Imports, Data Cleansing
- Data Formatting and Warehouse Design
- Denormalization and Keys in DWH
- PIVOT, Data Reads, Connection Assistants
- Pivot - Implementation and Key Values
- Lineage ID - Purpose, Data Mappings
- Input Columns, Source Column Mapping
- Data Exchange with Pivot Key Values
- Data Viewer & Debugging for SSIS
- Fuzzy Lookup, Reference Connections
- Exact, Fuzzy, Nomatch Data Cleansing
- Threshold Values & Token Delimiters
- Type Conversions and Error Redirections
- ForEach Loop Container, DFT Variables
- Connection Iterations, Fetch & Mapping
- Connection Strings with Package Variables
- Package Audits for Dynamic Connections
- Identity_Insert and Table Locks in ETL
- Text Qualifiers and Data Loads with .dat
- SSIS Expressions, Data Cleansing Options
|
DAY 4: MDX Queries & Expressions
- MDX Syntax and Axis - ROWS, COLUMNS
- Practical Advantages of MDX Queries
- MDX Cube Reports, Cube Writebacks
- MDX for KPIs, Actions and Security
- MDX Queries with Dimension Attributes
- Members, Axis Levels and Granularity
- Hierarchies, Levels & Attribute Keys
- MEMBERS, CHILDREN, ALL MEMBERS
- Cell Value Comparison and Aggregates
- Tuple and Set Representations, JOINS
- TOPCOUNT / HEAD, BOTTOMCOUNT/ TAIL
- WHERE, EXCEPT, RANGE, Operators
- NONEMPTY. Limitations with WHERE
- MDX PARENT & CHILDREN. Tuple Inverse
- FILTERS - CURRENT & EMPTY MEMBERS
- FILTERS - AND / OR, LEFT / RIGHT Range
- MDX Query Batches - GO. Sub Queries
- ADOMD Client: MDX Query Processing
- MDX for Formatting. Member Binding
- MDX in Cube Browser - Null, Empty Axis
|
DAY 4: Report Design - III
- Report Parameters - Purpose, Usage
- Parameters using Dataset Queries
- Dynamic Parameters using Report Data
- Single Value, Multi Value Parameters
- Dynamic Parameters, Dependency Queries
- Dataset Parameters & Dynamic Conditions
- Dataset Link to Parameters, Value Options
- Multi-Value Parameters Data Types, NULLs
- Reports @ Multiple Data Sets. Performance
- Advanced Options : Auto / Manual Refresh
- Dataset, Toolbox Filters: Purpose & Usage
- Filters Versus Parameters - Differences
- Filter Conditions at Dataset / Toolbox
- Type Conversions with Filters, Expressions
- Deciding Parameters & Filters - Usage
- Expressions & Globals For Parameters
- JOIN, FIELDS, LABEL and Format Options
- Report Parameters Link to Dataset Filters
- Reports with txt and Excel Spreedsheets
- Paginated Reports & Content with SSRS
|
DAY 5: SSIS with ETL, Warehouse (DWH)
- OLTP Database to Historical Data Loads
- Data Warehouse (DWH) - Purpose, Usage
- Dimensions, Attributes, Members Types
- Dimension Tables, Fact Tables Design
- TYPE1 and TYPE2 ETL Implementation
- SCD Type1, Type 2 for DWH in Sales
- Dimensional Table Design - Type 1
- Dimensional Table Design - Type 2
- Inferred Members and Legacy Loads
- Initial Data Loads with Data Marts
- Business Keys & non Identity Columns
- Surrogate Keys, Alternate Business Keys
- Cascading OLTP / Stage to DWH Rows
- Fixed Attributes, Changing Attributes
- Historical Attributes. Inferred Updates
- ETL Date, Row Status Transformations
- Attribute Key Types in SCD, Limitations
- Historical Attributes and Data Delta
- SSIS Connection Assistants - Reuse
- SCD Transformations in Real-time
|
DAY 5: MDX Calculations
- Calculations with MDX Scripts. Syntax
- Calculations and Folders: Browser Usage
- Relating and Reusing Calculations
- MDX Calculations in Search Patterns
- Conditional Expressions @ MS Excel
- TIME DIMENSIONS - Usage Options
- Time Keys and Attributes - Usage
- Adding Hierarchies to Time Dimensions
- Dimension Enhancements - Advantages
- Cube Enhancements: Purpose, Usage
- Time Calculations - MDX Expressions
- SCOPE, THIS, FREEZE & MDX Formats
- Hierarchies For Time Enhancements
- Time Calculations For Hierarchies
- MDX Scripting for Time Calculations
- Browsing Time Calculations in MDX
- Used Defined Time Calculations with MDX
- Measure Level Calculations and Granularity
- Associated Measures & Pie-Charts
- MDX Query Tuning with MOLAP/ROLAP
|
DAY 5: Report Design - IV
- Chart Reports - Design Options, Properties
- Series and Series Category Groups - Usage
- Data Visualization: Trend & Discrete Charts
- Clustered/NonClustered Attribute Positions
- Series Labels : Properties, Number Formats
- Series Actions : Multi - Valued Parameters
- Report Actions : URL / Reports, Filters
- Issues - Multiple Axis Items, Solutions
- Dashboard Creations, Multiple Chart Areas
- Dashboard Exports with Dynamic Filters
- Series Properties, Markers & Grid Values
- Chart Areas, Properties & Limitations
- 3-Dimensional Report - Properties, Visibility
- Range, Pie Charts, Line Reports, Data Bars
- Report Actions with Parameters, Filter Joins
- Exploring Toolbox Filters with Conversions
- Common Errors with Filters and Solutions
- Shared DataSources, DataSets - Purpose
- Data Bar and Sparkline Stacked Reports
- Multiseries, Dynamic Charts & Axis Display
|
DAY 6: SSIS Checkpoint, Transactions
- Scripts for Control Flow, Error Redirections
- Execute SQL Task and OLE DB Queries
- SSIS Transactions and Manual Rollbacks
- Precedence Constraints: Static/Dynamic
- Parallel & Sequential Task Executions
- SSIS Checkpoints with Data Flow Tasks
- Checkpoint Files, SSIS Logging Options
- Transactions with Checkpoints in SSIS
- Checkpoint Options - Advantages, Usage
- SSIS Variables in ETL Data Flow Scripts
- Static & Dynamic Variables in Containers
- SSIS Events and Event Handling Options
- Package Level & Project Level Parameters
- Parameter Expression, Data Types, Binding
- Dynamic Precedence Constraints, Types
- FailPackageOnFailure and Checkpoints
- REQUIRED / SUPPORT, NOTSUPPORTED
- Transaction Property Versus CHECKPOINTs
- Bypass Prepare Options & Event Handling
|
DAY 6: Partitions & Storage Modes
- Partitions : Purpose and Architecture
- Cube Partitions : Storage, Slice Options
- Query Binding and Table Binding Options
- Default Partition Location Storage
- Role of Measure Partitions in Cubes
- Aggregations - Predefined Calculations
- Full, Default, None and Unrestricted
- Aggregations with Optimization Levels
- Measure Properties, Default Aggregations
- Aggregations on Measures and Dimensions
- Cube Slicing and Reusability Options
- Additive and Semi-Additive Measures
- Estimated Row Count and Tuning
- Storage Modes : Multidimensional
- Aggregation & Measure Group Storage
- Automatic, Scheduled, Medium Latency
- Low Latency and Custom Scheduling
- Proactive Caching and Silence Override
- Cache Rebuild & Perspectives
|
DAY 6: Report Design - V
- Gauge Reports: Purpose, End User Access
- Report Types - Radial and Linear Gauges
- Adding Data to Gauge Report - Indicators
- Gauge Data Versus Chart Data - Pointers
- Maximum, Minimum Scale Range Values
- Browser Compatibility and Offline Reports
- Gauge, Gauge Panel Properties and Filters
- Master and Detailed Reports - Sub Reports
- Scale - Properties, Values, Label Options
- Pointers, Scale Limits and Format Options
- Ranges & Labels, Items, Needle Options
- Adding Multiple Gauges - Dashboard Design
- Parameterized Gauge Reports and Datasets
- Indicators : Value and State Expressions
- Indicators and Gauge Panel with Filters
- Report Design with ODBC, DSN Creations
- ODBC DataSource Administrator Options
- 3D PieCharts and Aggregations with Excel
- ODBC Data Sources, Dynamic Filters
|
DAY 7: CDC Tranformations for DWH
- DML Audits with Change Data Capture
- CDC Tables with SQL Server Connections
- CDC Connections & ADO.NET Integration
- CDC Control Flow and CDC State Values
- INITIAL LOADS & PRCESSING RANGE
- State Variables, Net Changes, Logging
- Initial Data Loads for Dimensions
- Incremental Data Loads for Dimensions
- Dynamic CDC Control, OLEDB Command
- Internal Parameters and Usage Options
- Parameter Mapping For ETL Type1, Type2
- Integrating Control Flow for CDC @ ETL
- CDC Splitter - Row Inserts, Updates
- CDC Precautions, Input & Output Range
- Derived Column Transformations with CDC
- Limitations of ADO.NET Connections
- Master Child Packages,Prameter Binding
- Package Passwords, Project Parameters
- Project Configuration Options(32,64 bit)
|
DAY 7: Key Performance Indicators
- KPI - Purpose, Design Options
- GOAL, VALUE, STATUS, TREND, WEIGHT
- Data Driven Goals - MDX Expressions
- Variance and Value Computations
- KPI Organizer & Metadata Refresh
- Associated Measure Groups For KPIs
- KPI Design, Time Based Analysis Options
- Parent - Child KPIs : Design Options
- KPI Calculations and MDX Functions
- FORMAT_STRING and MDX Operators
- PARALLELPERIOD & CLOSINGPERIOD
- SESSIONSCOPE, MEMBER, SOLVE_ORDER
- Parent KPIs with Member, KPI Browser
- Excel Pivot Reports with KPI - Variance
- Drill-Up & Drill-Down - Pivot Tables
- Perspectives with Calculations & KPIs
- Role Playing, Fact, Factless Dimensions
- Referenced & Conformed Dimensions
- Degenerate, Junk, Hierarchical Dimensions
|
DAY 7: Report Design - VI
- Report Builder - 2016, 2014, 2008 R2
- DataSource, DataSet with Report Builder
- Local Report and RDL File Creations
- Entity Browsing Options & Query Options
- Dataset Design with Parameters and Filters
- Query Design with Report Builder - Entities
- Column Aggregates and Auto Group BY
- Query Edit Options with Report Builder
- Sub Totals, Drill-downs, Column Groups
- Chart and Line Reports - Report Builder
- Parameters and Filters. Adhoc Queries
- Line Reports and XValue in Report Builder
- Trend and Continuous Data - OLTP / DWH
- Map Reports - Layers, Report Item Options
- Map Gallery, ESRI Share Files, Spatial data
- Geo Spatial Data for Business Dashboards
- Working with Polygon, Line, Map Layers
- Map Visualization and Bubble Map Reports
- Data Fields, Display Labels & Visualization
|
DAY 8: Cheksum & DWH Design
- Checksum Transformation in ETL Loads
- Configuring Checksum: SSIS 2017, 2016
- Transformation Logic, Parity Checks CRC
- Checksum For Type I, Type II ETL DWH
- DWH Dimension Tables With Checksum
- Lookup Transformation, Row Redirection
- OLE DB Command and Input Parameters
- Parameter Mapping, Dynamic Updates
- Cache Transformation CAW Memory Files
- Memory Connection Lookup with Cache
- Tuning Lookup: Caching, Index Options
- Pre-ETL Activities, DB Recovery Models
- FULLCACHE, NOCACHE, PARTIAL CACHE
- Performance Tuning and Pre-ETL Loads
- Dependent Data Flow Tasks, Post ETL
- Internal Parameters and Query Updates
- OLEDB Command & Conditional Splits
- Cache Allocation Options with ETL DWH
|
DAY 8: SSAS Project Deployment
- SSAS Deployment Tools. BUILD Options
- Configuration Files - Contents & Targets
- Deployment Options, Transaction Settings
- SSAS Deployment Wizard : Impersonation
- Deployment Accounts, Lazy Aggregations
- Key Error Logs, Locations. XMLA Scripts
- OLAP Processing - Full, Default, None
- Scripting OLAP Databases with XMLA
- Cloning OLAP Database, Process Scripts
- Partition Management - SPLIT, MERGE
- Aggregation Management, Cube Slices
- Force Commits and Cube Slices, Visibility
- Database Level and Cell Level Security
- MDX Expressions & Dynamic Security
- Read Contingent Permissions. Writebacks
- SQL Agent Jobs with XMLA. Server Audits
- OLAP Job Audits and Event Messages
- Processing Jobs and DB Synchronizations
|
DAY 8: Deployments & Subscriptions
- Report Deployment - Builds, Config Files
- Data Source, Data Set Folders, Report URL
- Report Deployment Command Line. RSKey
- Report Server, Report Manager Portal URL
- Report Site Settings & Report Settings
- Data Sources: Security, Access Options
- DataSets: Properties, Dependent Items
- Reports: Options, Edits, Downloads
- Security: Content Manager and Builder
- MyReports and Report Publisher Roles
- Report Links, Default/Hidden Parameters
- Report Tuning: Caching, Cache Refresh
- Report Tuning: Snapshot and Schedules
- Report Tuning: Linked Report, Schedules
- Report Parts and Paginated Reports
- Report Delivery, File Share Subscriptions
- Email Subscriptions, Execution Accounts
- Encryption Keys & RS Config Files,
|
DAY 9: SSIS Customizations with .NET
- Script Task in SSIS Control Flow
- Script Task with VB.NET Programs
- Script Task with C#.NET Programs
- Variables and Parameters with Script Task
- ReadOnly, ReadWrite Variable Expressions
- Namespace, System.IO, System.DTS
- Data Flow Limitations with .NET Scripting
- SSIS Expressions, Debugging, BreakPoints
- Variable - Parameter Mapping Expressions
- Control Flow Sequence Containers, Usage
- Control Flow File System Limitations
- .Net Scripting for SQL Reads/Writes
- SQLDataAdapters, System.Data.SQLClient
- DTS Packages to SSIS Data Tools Project
- Package Upgrade & Versioining Options
- SSIS Configurations, Dynamic Connections
- Variables and Parameters for Script Task
- SSIS Package/Project Conversion Process
- SSIS Package Upgrades, Package Backups
|
DAY 9: Tabular Mode : Cube Design, MDX
- Tabular Mode: Importance, SSDT Templates
- Workspace Server, Configuration Settings
- Entity Identification, ODBC Connections
- ODC, ROW/Column Filters. Named Queries
- Hierarchies, Attributes and Members
- Time & Non-Time Hierarchies @ Tabular
- Aggregated Measures and KPI Generation
- Static and Dynamic Goals. Status, Trend
- Partitions - Entity Level & Cell Reuse
- Time Based Hierarchies in Tabular Mode
- BUILD and Deployment - XMLA Scripts
- InMemory and Direct Query Modes
- In-Memory with Direct Query - Uses
- Direct Query with In-Memory - Uses
- Virtual Cube Browsing Options. MDX
- Filters - Semantic Definition Language
- Cube Design with STAR,SNOWLFLAKE
- FILTER(), MDX Joins, SCOPE, TOPPERCENT
- xVelocity and Direct Query with MDX / DAX
|
DAY 9: Cubes, Power BI Integration
- Scale-Out Deployments, Remote Data
- Scale-Out Joins and Configuration Keys
- Report Designer Versus Report Builder
- Cube Reports with SSRS - MDX Queries
- MDX Parameters, Filters, Expressions
- MDX YTD, QTD Calculation with SSRS
- DAX Calculations with Tabular Reports
- DAX Reports with Tabular Cubes in SSRS
- SSAS KPIs & Cube Aggregations - MOLAP
- Cube Reports with Tabular Hierarchies
- Sub Reports, Dashboards, Action Reports
- Drill-Up, Drill-Down, Drill Through
- Power BI Reports - Desktop / Mobile
- Power BI PBX Reports with RDL Sources
- Power BI Reports with SSRS and SSAS
- Power View and Power Pivot, Usage
- SSRS Tuning Features in 2016, Migrations
- Publishing SSRS Reports to Power BI
- Scale-Out Deployment with Power BI
- Mobile Reports, Pagination Concept
- PowerBI with SSRS 2016 - Desktop
- PowerBI with SSRS 2016 - Mobile
- Using Report Portal 2016 URL Links
- New SSRS Tuning Features in 2016
- Migrating Reports from 2014 to 2016
- Comparing SSRS and Power BI Tools
- RS Key Configurations, RDLX Security
|
DAY 10: XML Connections, ODBC, LOOPS
- System.Data.SQLClient Providers: Usage
- Dynamic Connections, Loop Expressions
- Expressions with File Specifications
- For Each File - Dynamic Directories
- Variables with Indexed Connections
- XML Connections & Operating Data
- ODS Connections, Dynamic Reusability
- Advanced Connections: XML, DT_NTEXT
- XML Queries @ Sub Queries. XML Imports
- RAW - DTSPipeline Wrapper, RAW Adapters
- Row Sampling and Percentage Sampling
- SSIS Logging & sysssislog Audit Tables
- Package Logging and Container Events
- SSIS Data Profiler Tool, ADO.NET and XML
- SQL Data Profile Viewer Tool with XML
- Nullability Checks, Fast Load @ Row Size
- ADO.NET for SQL Profiler Tool, Options
- Time-Out for Quick Profile, Candidate Keys
- Value Length Distribution, Surrogate Keys
- XML Imports for Data Profiler Viewer Tool
|
DAY 10: Tabular Mode : Dax Expressions
- DAX: Query Syntax. ROW & CONTAINS
- Measures inside DAX Query. ROLAP Cube
- DAX Measures in MDX Queries, Drillthrough
- CALCULATETABLE, FILTER, ADDCOLUMN
- CROSSJOIN, GENERATE, GENERATEALL
- FILTER in CALCULATE, CALCULATETABLE
- TOPRANK, RANKX, RANK.EQ, DAX Filters
- LOOKUPVALUE, SUMMARIZE & Vertipaq
- MDX - Sub Queries, Nested Sub Queries
- Direct Query & Data Modelling in Tabular
- Optimizing Performance, MS Excel ODC
- Row and Column Oriented Databases
- Security Management with DAX. Roles
- Entity Roles and Membership. DAX Filters
- DAX Functions for Dynamic Security
- Tabular Mode Cube Design with DAT, HTML
- IIF, BLANK(), Other DAX Expressions
- Tabular Mode Imports and Workspace
- Multidimensional Versus Tabular Mode
- Zero Aggregations, Delivery Advantages
|
DAY 10: One Realtime Project for SSRS
|
DAY 11: Fact Table Design, DWH Loads
- Fact Table - Design and ER Model
- Fact Table - STAR, SNOWFLAKE Schemas
- Time Dimensions and ETL Date / Time
- Link Time Dimension to Facts, Lookups
- Parent-Child Packages for Fact Loads
- Master Package: Dimension, Fact Loads
- Inferred Members for NULL Dimensions
- SCD Wizard for DWH Fact Table Design
- Parameter Mapping for Incr Updates
- ETL Load IDs - Dimension Attributes
- Error Handling, Event Handling in SSIS
- Text Qualifiers with Flat File Sources
- Connections, Parameters in Master-Child
- Key Based Dimension LOOKUPs, Indexes
- SCD Implementation with Legacy Data
- Fact Load Design for Initial, Incr Loads
- End-to-End DWH Design Implementation
- Direct Data Loads and Staging Tables
- Fact Table Staging and Incr Updates
- Advantages of SSIS ETL/DWH Concepts
- STAR & SNOWFLAKE Schemas in SSIS
|
DAY 11: OLAP Management, Data Mining
- Cube Audits - MDX Query Logs
- OLAP Audits: Usage Based Optimization
- Usage Based Optimization Options (MOLAP)
- Flight Recorder Settings, Log Providers
- Data Sampling and MDX Query Tuning
- 100% Aggregations, CPU and Subcubes
- Lazy Aggregations, UBO in Partitions
- Cube Writebacks - Purpose and Usage
- Writeback Partitions: MDX Expressions
- MDX Transactions COMMIT / ROLLBACK
- DESCANDANTS, PARENT & MEMBERS
- Mining Structures & Mining Models
- CLUSTERING and DECISION TREES
- Naive Bayes and NEURAL NETWORK
- Case and Nested Table For Relations
- DESCRETE and CONTINOUS. Drilldowns
- Training, Testing for Mining Accuracy
- Dependency Rules and Lift Charts
- Mining Prediction Analysis Functions
- DMX - Forecasts For Prediction Functions
- DMX Filters - Aggregations and Aliases
|
For MSBI Developers
REAL-TIME PROJECT: SSRS, SSAS
REAL-TIME PROJECT: SSRS, SSIS
REAL-TIME PROJECT: SSIS,SSAS,SSRS
MSBI Training Course Highlights:
1. Completely Practical
2. Basic to Advanced, Highly Technical
3. Interactive, Real-time
4. Daily Tasks, Mock Interviews
5. Resume, Certification Guidance
6. End-to-End Realtime Projects
7. Job Support Services
|
DAY 12: SSIS Deployments, upgrades
- SSISDB Catalog Deployments, ISPAC File
- SSIS Builds, Verification Techniques
- Project Deployment Targets, Logging
- SSIS DB Catalog Creation, Usage
- SSIS Package Executions Reports
- Package Validation (32/64bit) Reports
- SSIS Package Configuration Scripts
- SSIS Package Jobs from SQL Agent
- SSIS Job Schedules and Event Logs
- SQLISPackage130 Event Error Logs
- Security Management - SSISDB Logins
- ssis_admin, failover_monitoring_agent
- Folder and Project Level Security
- Execute/Write, Content Management
- Business Intelligence Dev Studio
- SMO Connection with BIDS. Builds
- Adding DTS, SSIS Packages, UPGRADES
- Project Migration and Passwords
- Importing ISPAC Files into Projects
- Command-Line (CLI) Deployments
- SSIS ETL/DW Procedures and Data Audits
|
DAY 12: Realtime Project for SSAS
- OLAP Database Backups & Restores
- OLAP Database Detach & Attach
- DB Synchronize, OLAP Jobs with XMLA
- XEvents support through SSMS
- DAX performance through Super DAX
- Partition Slicing with Tabular Mode
- No Latency Scenarios with ROLAP
- DAX Calculations and Cube Slices
- Cube Space and Dicing with DAX
- AUTOEXISTS and FREE Statements
- Recursions, NONEMPTYCROSSJOIN
- Analysis Management Objects
- Resource and Activity Moniotoring
- DBCC Commands & MDX for SSAS - 2016
- SSAS 2012, SSAS 2014 and SSAS 2016
- Importing Multidimensional Projects
- Importing Tabular Projects and Cube
- PowerPivot Imports and ACE Driver Options
|
|
