SQL DEVELOPER/Administrator / SSIS / SSRS / SSAS Handson

Module 1 - SQL Developer ( 5 days ) Module 2 - SQL Administrator ( 5 days ) Module 3 - SSAS (3 days ) Module 4 - SSIS (3 days ) Module 5 - SSRS (3 days ) Microsoft SQL Server 2008 Training Outline Introducing SQL Server 2008 What’s New in SQL Server? Looking at the Numbers SQL Server Editions Installing SQL Server Basics of Database Design Normalization Denormalization Tools for Normalization in SQL Server Introducing SQL Server Management Studio Creating a Database Adding Tables Creating Indexes and Constraints Building Relations T-SQL Basics What is T-SQL? Syntax Conventions Introducing AdventureWorks2008 Executing SQL Statements Identifiers Creating Data Types Operators Variables Functions Using T-SQL to Build and Alter Objects SELECT queries The SELECT Clause The FROM Clause The WHERE Clause The ORDER BY Clause Using GROUP BY and HAVING Using TOP N and TOP N PERCENT UNION Queries Full-Text Search Action Queries SELECT INTO queries DELETE queries TRUNCATE TABLE queries INSERT queries UPDATE queries MERGE queries Locking and Execution Plans What is Locking? Monitoring Locks Tuning Locking Deadlocks Tuning Queries Management Data Warehouse Performance Studio Views, Stored Procedures, and Functions Using Views Using Stored Procedures Using Functions Advanced Transact-SQL Transactions Rowset Functions Ranking Functions Cursors Structured Error Handling XML and SQL Server XML Columns and Indexes Querying and Modifying XML Data Using OPENXML Using FOR XML Triggers Creating DML Triggers Altering DML Triggers Using the Inserted and Deleted Tables Checking Updated Columns Recursive and Nested Triggers Creating DDL Triggers .NET inside of SQL Server Creating CLR User-Defined Functions Creating CLR Stored Procedures Using the Sql Namespace Creating CLR Triggers Creating CLR User-Defined Types Choosing When To Use CLR Objects SMO and RMO Programming Retrieving Server Information Working With Database Objects Generating Scripts Viewing and Modifying Server Configuration Understanding RMO Data Protection/Tracking Understanding Change Data Capture Data Capture Schema and Tables Transparent Data Encryption SQL Server 2008 Analysis Services Understanding Analysis Services Introducing Business Intelligence Development Studio Creating a Data Cube Exploring a Data Cube SQL Server 2008 Integration Services The Import and Export Wizard Creating a Package Working with Connection Managers Building Control Flows Building Data Flows Creating Event Handlers Saving and Running Packages SQL Server 2008 Reporting Services The Reporting Services Architecture Using Report Designer Publishing a Report Using Report Builder Using Report Manager Service Broker Service Broker Terminology Creating a Service Broker Application Processing Messages with Stored Procedures Using Service Broker Across Servers Security and SQL Server Choosing Service Accounts Authentication Modes Roles and Users Permissions Object Ownership Conclusion ---------------------------------------------------------------- SQL Server 2005 Analysis Services (SSAS) Training Outline What is Microsoft BI? Core concept – BI is the cube or UDM Example cube as seen using Excel pivot table MS BI is comprehensive – more than Analysis Services on SQL Server Demonstration of SQL Reporting Services with cube as data source OLAP modeling Modeling source schemas—stars and snowflakes Understanding dimensional modeling—Dimensions (Type 1, 2, or 3) or rapidly changing Understanding fact (measures) and cube modeling Other types of modeling—data mining etc. 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, drillthrough 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 – writeback, 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 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 .NET Assemblies 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, Naive 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 and 2007 Pivot Tables SQL Server 2005 Reporting Services (SSRS) & Report Builder SPS RS web parts & .NET report viewer controls Business Scorecards & ProClarity Integration with Office 2007 SharePoint 2007 and AS Report Center (type of dashboard) uses KPIs, Reports, Excel Web, Filter Excel Services 2007 and AS (Web Services) Conclusion ----------------------------------------------------------------------- SQL Server 2005 Integration Services (SSIS) Training Outline A Guided Tour of SSIS Understanding SSIS Exploring and Executing an SSIS Package in BIDS Exploring and Executing a Package in BIDS Introduction to Control Flow Overview of Control Flow in SSIS Control Flow Tasks Workflow Tasks Other SQL Operations Tasks Precedence Constraints Data Processing Tasks Introduction to Data Flows Data Flow Overview Data Flow Pipeline Data Flow Sources Data Flow Destinations Data Viewers Data Flow Transformations Variables and Configurations Understanding Variables Using Variables in Control Flow Using Variables in Data Flow Understanding Property Expressions Understanding Configurations User Configurations Using Variables and Configurations between Packages Advanced Control Flow Advanced Control Flow Overview Using Containers Transactions Support in SSIS Additional Control Flow Tasks Error Logging and Handling Error Logging and Handling Overview Checkpoints Package Logging Event Handling Advanced Data Flow Synchronous Transformations Asynchronous Transformations Working with Error Outputs Using Advanced Transformations Package Deployment Overview of Deploying Packages Deployment Challenges Create a Package Deployment Utility The Package Installation Wizard Deploy a Package to the File System Deploy a Package to SQL Server Redeploying Updated Packages Package Management Overview of Package Management Managing packages with DTUtil Managing packages with SQL Server Management Studio Executing Packages Scheduling packages with SQL Server Agent SSIS Security Introduction to Scripting and Custom Components Extending SSIS Capabilities Through Code The SSIS scripting Scripting in Control Flows Scripting in Data Flows Custom SSIS Components Best Practices Best Practices for Using SSIS Best Practices for Package Design Data Flow Best Practices Beyond ETL: Other Scenarios for SSIS Using SSIS Beyond ETL Migrating SQL Servers with SSIS SQL Server Maintenance Plans Working with Analysis Services Folder and File Management Working with Windows Event Log Conclusion ---------------------------------------------------------------------- SQL Server 2005 Reporting Services (SSRS) Training Outline 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 Reporting Services Web Service 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 Conclusion