
T-SQL
Module 1 – SQL / T-SQL
- Basics of Database Design
- Normalization
- Demoralization
- 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 AdventureWorks2012/2008
- Executing SQL Statements
- Identifiers
- Creating Data Types
- Operators
- Variables
- Functions
Module 2 – 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
- Sub queries and correlated sub queries
- MERGE queries
- 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
SSIS
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
- Scheduling and Executing Plans · Examining Database Maintenance Plan Packages
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
Debugging, Error Handling and Logging
- SSIS debugging overview
- Breakpoints in SSIS
- SSIS debugging windows
- Control Flow: The on Error 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
SSIS Package Deployment
- Configurations and deployment
- The deployment utility
- Deployment options
- Deployment security
- Executing packages – DTExec and DTExecUI
SSAS
- 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
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, write back 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
MDX
- Basic syntax
- Using the MDX query editor in SQL Server Management Studio
- Adding calculated members
- Adding scripts
- Adding named sets
SSAS Administration
- Best practices – health monitoring
- XMLA scripting (SQL Mgmt. Studio)
- Security – roles and permissions
- Disaster Recovery – backup / restore
Introduction to Reporting Clients
- Excel 2007/ Power pivot
- SQL RS & Report Builder
- SharePoint Performance Point Services 2010
SSRS
Introducing SQL Server Reporting Services
- Tour of Features
- Reporting Services Architecture
- Reporting Services Terminology
- Reporting Services Editions
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
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
Reach Us
Call or use the form to request a free initial consultation.
Office 1.05, 1st Floor, Building 2,Croxely Business Park, Watford, WD18 8YA
02035736917
enquiries@bsbitraining.co.uk