SQL Server Analysis & Int Services training course

SQL Server Analysis Services

Overview

• Business Intelligence overview
• Analysis Services (SSAS) Multidimensional and Tabular mode
• Review of third normal form and relational OLTP databases
• Introduction to relational star schemas
• Introduction to multidimensional cubes
• Introduction to SSAS tabular models
• Building dimension tables
• Building fact tables
• Building cubes in BIDS/SSDT
• Developing an Integration Services ETL package

Dimension design

• Attribute hierarchies
• User hierarchies and drill-down
• Attribute relationships
• Key collections
• Parent-child dimensions

Cube design

• Calculations and named sets (using MDX expressions)
• KPIs (using MDX)
• Actions (drill-through and reporting)
• Partitions and aggregation design
• Perspectives

Visualisation

• MDX queries
• SSRS reports on multidimensional and tabular cubes
• Excel pivot tables
• PowerPivot and DAX
   

Administration

• Backing up and restoring star schemas
• Backing up and restoring cubes
• Slowly-changing dimensions
• Incremental updates
• Security

SQL Server Integration Services

Overview

• Integration services (SSIS)
• Workflow
• Control Flow toolbox overview
• Data Flow toolbox (ETL) overview

Control Flow

• Package variables
• Script Task
• Execute SQL Task
• Send Mail Task
• WMI Event Watcher Task
• Sequence Container
• Foreach Loop Container

Data Flow

• Sources, destinations, and transformations
• Data Viewers
• Conditional Split transformation
• Derived Column transformation
• Lookup transformation (with caching, Sort and Merge transformations)
• Fuzzy Grouping transformation

Administration

• Deploying packages
• Executing packages with dtexec and stored procedures
• Scheduling packages with SQL Server Agent
• Configuring packages
• Logging packages
• Security