MSBI Training

SQL Server 2014 Integration Services
Introduction to SQL Server
Integration Services
 Product History
 SSIS Package Architecture Overview
 Development and Management Tools
 Deploying and Managing SSIS
 Packages
 Source Control for SSIS Packages
SSIS for DBAs: Using SQL Server
Management Studio
 The Import and Export Wizard
 Importing and Exporting Data
 Working with Packages
 Database Maintenance Plans
 Creating Database Maintenance Plans using
SSMS
Business Intelligence
Development Studio
 Launching BIDS
 Project templates
 The package designer
 The Toolbox
 Solution Explorer
 The Properties window
 The Variables window
 The SSIS menu
Introduction to Control Flow
 Control Flow Overview
 Precedence Constraints
 The Execute SQL Task
 The Bulk Insert Task
 The File System Task
 The FTP Task
 The Send Mail Task
Advanced Control Flow
 Containers grouping and looping
 The Web Service Task
 The WMI tasks
 The Analysis Services tasks
 The Execute Process Task
 The Execute Package Task
Introduction to Data Flow
 Data Flow Overview
 Data Sources
 Data Destinations
 Data Transformations
 The Copy Column Transformation
 The Derived Column Transformation
 The Data Conversion Transformation
 The Conditional Split Transformation
 The Aggregate Transformation
 The Sort Transformation
 Data Viewers
Variables and Configurations
 Variables Overview
 Variable scope
 SSIS system variables
 Using variables in control flow
 Using variables in data flow
 Using variables to pass information
between packages
 Property expressions
 Configuration Overview
 Configuration options
Slowly Changing Dimensions
 About Slowly changing dimensions
 Types of Slowly changing dimensions
 Loading Slowly changing dimensions with
SSIS
 Handling inferred member in scd.
Change Data Capture
 About CDC & LSN
 Enbling CDC on database
 Enable on Table
 Implementing incremental Load with CDC
Pivoting & unpivoting in SSIS
Debugging, Error Handling and
Logging
 SSIS debugging overview
 Breakpoints in SSIS
 SSIS debugging windows
 Control Flow: The OnError event
 handler
 Data Flow: Error data flow
 Configuring Package Logging
 Built-in log providers
Advanced Data Flow
 Revisiting data sources and
 destinations
 The Lookup Transformation
 Getting Fuzzy: The Fuzzy Lookup and
Fuzzy Grouping Transformations
 The Multicast Transformation
 The Merge and Merge Join
 Transformations
 The Data Mining Query
 Transformation
 The Data Mining Model Training
Destination
 The Slowly Changing Dimension
 Transformation
Extending SSIS through Custom
Code
 Introduction to SSIS scripting
 The SSIS script editor
 The SSIS object model
 Script in Control flow: The Script Task
 Script in Data flow: The Script Component
 Introduction to SSIS component
development
SSIS Package Deployment
 Configurations and deployment
 The deployment utility
 Deployment options
 Deployment security
 Executing packages – DTExec and
DTExecUI
SSIS Package Management
 The SSIS Service
 Managing packages with DTUtil
 Managing packages with SQL Server
Management Studio
 Scheduling packages with SQL Server
Agent
SQL Server 2014 Reporting
Services
Introducing SQL Server Reporting
Services
 Tour of Features
 Reporting Services Architecture
 Reporting Services Terminology
 Reporting Services Editions
 Taking Reporting Services for a Spin
Creating Reports
 Deconstructing Reporting Services
 Shared Data Sources
 Creating Reports from Scratch
Calculations and Formatting
 Creating Expressions
 Using the Global Collections
 Formatting Items
 Conditional Formatting
Grouping and Sorting
 Creating Groups
 Calculating Totals and Percentages
 Interactive Sorting
 Creating Drill-Down Reports
Report Parameters
 Creating Report Parameters
 Creating Drop-Down Parameters
 Multi-Valued Parameters
 Debugging Parameter Issues
Creating Matrix Reports and
Charts
 Creating a Basic Matrix Report
 Matrix Subtotals
 Creating a Basic Chart
 Exploring the Charting Possibilities
Managing Reporting Services
 Deploying Reports and Data Sources
 Exporting Reports and Printing
 Using the Web-Based Report Manager
 Using SQL Server Management
 Studio-based Reporting Services Manager
Reporting Services Security
 Two Parts to Reporting Services Security
 Securing Access to Reports
 Data Source Security
Programming Reporting Services
 The Many Reporting Services APIs
 Integrating Reporting Services into
Applications using URL Access
 Using the Report Viewer Controls
Advanced Reporting Services
Programming
 Using the Reporting Services Web Service
 Working with Custom Assemblies
Snapshots and Subscriptions
 Caching and Snapshots
 Creating Standard Subscriptions
 Creating Data-Driven Subscriptions
 Managing Subscriptions
Ad-Hoc Reporting with Report
Builder
 The Report Builder Architecture
 Creating Report Model Projects
 Running Report Builder
SQL Server 2014 Analysis
Services
OLAP Modeling
 Modeling source schemas-stars and
snowflakes
 Understanding dimensional modelingDimensions (Type 1, 2, or 3) or rapidly
changing
 Understanding fact (measures) and cube
modeling
Using SSAS in BIDS
 Understanding the development
environment
 Creating Data Sources and Data Source
Views
 Creating cubes – using the UDM and the
Cube Build Wizard
 Refining Dimensions and Measures in BIDS
Intermediate SSAS
 KPIs
 Perspectives
 Translations – cube metadata and currency
localization
 Actions – regular, drill-through and
Reporting
Advanced SSAS
 Using multiple fact tables
 Modeling intermediate fact tables
 Modeling M:M dimensions, Fact
(degenerate) dimensions, Role-playing
dimensions, writeback dimensions
 Modeling changing dimensions –
Dimension Intelligence w/ Wizard
 Using the Add Business Intelligence
Wizards – write-back, semi-additive
measures, time intelligence, account
intelligence
Cube Storage and Aggregation
 Storage topics – basic aggregations, MOLAP
 Advanced Storage Design – MOLAP, ROLAP,
HOLAP
 Partitions – relational and Analysis
Services partitions
 Customizing Aggregation Design –
Processing Design
 Rapidly changing dimensions / ROLAP
dimensions
 Welcome to the Real Time – Proactive
Caching
 Cube processing options
Beginning MDX (All Basics)
 Basic syntax
 Using the MDX query editor in SQL Server
Management Studio
 Most-used Functions & Common tasks
 New MDX functions
Intermediate MDX
 Adding calculated members
 Adding scripts
 Adding named sets
SSAS Administration
 Best practices – health monitoring
 XMLA scripting (SQL Mgmt Studio)
 Other Documentation methods
 Security – roles and permissions
 Disaster Recovery – backup / restore
 Clustering – high availability
Introduction to Data Mining
 What and why?
 Examples of using each of the 9 algorithms
(MS Clustering, MS Decision Trees, Naïve
Bayes, MS Sequence Clustering, MS Time
Series, MS Association Rules, MS Neural
Network)
 Data mining dimensions
 Data mining clients
 Processing mining models
Introduction to Reporting Clients
 Excel 2003 Pivot Tables
 SQL RS & Report Builder
Difference between 2012& 2014