In this course, you will learn how to use Microsoft SQL Server 2005 Analysis Services (SSAS) to design and implement OnLine Analytical Processing (OLAP) cubes and data mining models to support Business Intelligence (BI) solutions. This course includes concepts, procedures and practices based on real-world experience giving both the novice and experienced SQL Server 2005 developer the tools to build data warehousing and decision support system solutions. This course also provides information on end-user tools including Microsoft Excel 2003/2007 and Microsoft SQL Server 2005 Reporting Services. Implementing SSAS solutions have shown to boost data retrieval and report generation from SQL Server by up to 1000%.
SQL Training Audience:
SQL Server Administrators
SQL Training Prerequisites
It is assumed that students have working experience with SQL Server 2000 or 2005, data transformation services, Excel 2003, and SharePoint Server 2003.
SQL Training Course duration
3 days
SQL Training Course outline
What is Microsoft Business Intelligence?
- Defining Microsoft Business Intellignece
- Why Use OLAP?
- Understanding the UDM
- Dimensions and Measures
- OLAP Schemas
- Building and Viewing a Sample Cube
- The AdventureWorksDW Source Database
- Building a Cube with BIDS
- Viewing the Results in BIDS
- Viewing the Results Using Excel
- Cubes in Reporting Services
OLAP Modeling
- Selecting a Modeling Tool
- Understanding OLAP Modeling
- Understanding Dimensional Modeling
- Dimension Types
- Understanding Cube Modeling
- Modeling with BIDS
- Understanding the BIDS Interface
- Two Ways to Build a Model
- A Note about ETL
Using SSAS in BIDS
- Understanding BIDS
- Offline vs. Online Mode
- Creating Data Sources
- Creating Data Source Views
- Creating a Cube Using the Wizard
- Refining Dimensions and Measures
- Working with Your Cube
Intermediate SSAS
- Creating KPIs
- How KPIs are Implemented in SSAS
- Customizing the KPI Templates
- Other KPI Considerations
- Creating Perspectives
- Creating Translations
- Localizing Measure Values
- Currency Localization
- Creating Actions
Advanced SSAS
- Working with Multiple Fact Tables
- Linked Objects
- Dimension Usage Configurations
- Using Advanced Dimension Types
- Snowflake Dimensions
- Degenerate Dimensions
- Parent-Chile Dimensions
- Many-to-Many Dimensions
- Role Playing Dimensions
- Writeback Dimensions
- Working with Changing Dimensions
- More about Error Handling for Dimension Attribute Loads
- Using the Business Intelligence Wizard
Cube Storage and Aggregation
- Basic Storage: MOLAP
- About XMLA
- Three Storage Modes
- About Aggregations
- Customizing Aggregations
- Using Profiler
- Advanced Storage: MOLAP, HOLAP, or ROLAP
- Using Partitions with Advanced Storage Options
- ROLAP Dimensions
- Implementing Proactive Caching
- Notification Settings for Proactive Caching
- Fine Tuning Proactive Caching
- Using Partitions: Relational or SSAS
- Relational Table Partitioning in SQL Server 2005
- How to Implementing OLTP Partitioning
- Other Capabilities of OLAP Partitions
- Cube and Dimension Processing Options
Beginning MDX
- Understanding MDX
- MDX Structure Names
- MDX Syntax Rules
- Writing your First MDX Query
- About Members, Tuples, and Sets
- Common MDX Functions Explained
- New or Updated MDX Functions and Keywords
Intermediate MDX
- Understanding the Calculations Subtab
- Adding Calculated Members
- Examine a Calculated Measure
- Why Use Calculated Members?
- Adding MDX Scripts
- Adding Named Sets
- Adding .NET Assemblies
- Why Use External Assemblies?
SSAS Administration
- Implementing SSAS Security
- Reducing the Attack Surface
- Data Source Connection Methods
- Implementing Cube Roles
- Other Security Considerations
- Implementing XMLA Scripts
- SSAS Database Synchronization
- Understanding SSAS Backup and Restore
- Understanding Clustering
- Improving Scalability
- Understanding Performance Optimization
Introduction to Data Mining
- Understanding Data Mining Concepts
- About Algorithms
- More Data Mining Concepts
- Implementing Algorithms
- Reviewing Data Mining Structures
- Mining Structure Subtab
- Mining Model Properties
- Decision Tree Viewer
- Dependency Network Viewer
- Naïve Bayes Viewer
- Mining Accuracy Charts
- Mining Prediction Viewers
- Understanding Data Mining Algorithms
- Content and Data Types
- Other Model Properties
- Considering Data Mining Clients
- Understanding Mining Structure Processing
- Using SSIS to Process Mining Models
- SSIS and Data Mining
- Working with the DMX (Data Mining Extensions) Language
- A Simple DMXQuery
Introduction to SSAS Clients
- Using Excel 2003 Pivot Tables
- Pivot Charts
- The Limits of Excel 2003 as a BI Client
- Using SQL Server Reporting Services
- Build and SSRS Report
- Write and MDX Query
- View the SSRS Report
- Design the SSRS Report
- Deploy the SSRS Report
- Report Builder
- Using .NET 2.0 Report Viewer Controls
- Using SharePoint 2003 Web Parts
- Business Scorecard Manager 2005
- Data Mining Clients
Office 2007 Integration with SSAS
- Implementing SQL Server SP2
- SSAS SP2
- Implementing Excel 2007 Pivot Tables
- Excel 2007 Pivot Charts
- Implementing Excel 2007 as a Data Mining Client
- Configuring the Add-In
- Using Excel 2007 as a Data Mining Client
- Using the Query Function
- Using the Data Preparation Menu
- Using the Data Modeling Menu
- Using the Accuracy and Validation Menu
- Implementing MOSS BI Features
- Excel 2007 Web Services
- MOSS Data Connection Libraries
- MOSS KPIs
- Other MOSS BI Capabilities
- Performance Point Server (PPS)
