In this course, you will learn how to use Microsoft SQL Server 2008 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 2008 developer the tools to build data cubes and forecasting solutions. This course also provides information on end-user tools including Microsoft Excel 2007, Office Visio 2007 Professional Edition, SQL Server Business Intelligence Development Studio (BIDS) Microsoft SQL Server 2005 Integrated Services and Reporting Services.
SQL Training Audience:
SQL Server Administrators
SQL Training Prerequisites
This course does not require any prior experience with Analysis Server 2008. It is assumed that students have working experience with SQL Server 2005 or 2008, basic relational database concepts (e.g., tables, queries, and indexing), data transformation services, Excel 2007, and SharePoint Server 2007.
SQL Training Course duration
3 days
SQL Training Course outline
1. What is Microsoft Business Intelligence?
- Defining Microsoft Business Intelligence
- Why Use OLAP?
- Understanding the Cube Structure
- Dimensions and Measures
- OLAP Schemas
- Building and Viewing a Sample Cube
- The AdventureWorksDW2008 Source Database
- Deploying a Cube with BIDS
- Viewing the Results in BIDS
- Viewing a Cube Using Excel
- Cubes in Reporting Services
2. OLAP Modeling
- Selecting a Modeling Tool
- Understanding OLAP Modeling
- Putting it all Together
- Understanding Dimensional Modeling
- Dimension Types
- Understanding Cube Modeling
- Modeling with BIDS
- Understanding the BIDS Interface
- Cube Wizard Modeling Options
- A Note about ETL
3. 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
4. Intermediate SSAS
- Refining Attribute Relationships
- 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
5. Advanced SSAS
- Working with Multiple Fact Tables
- Linked Objects
- Dimension Usage Configurations
- Using Advanced Dimension Types
- Snowflake Dimensions
- Degenerate Dimensions
- Parent-Child Dimensions
- Many-to-Many Dimensions
- Role Playing Dimensions
- Writeback Dimensions
- Working with Changing Dimensions
- More about Error Handling for Dimension Attribute Loads
- Change Data Capture
- Using the Business Intelligence Wizard
- Managing Properties
- Dimension Properties
- Hierarchy Properties
- Attribute Properties
- Cube Properties
- Measure Properties
6. Cube Storage and Aggregation
- Basic Storage: MOLAP
- About XMLA
- Three Storage Modes
- About Aggregations
- Viewing Aggregation Designs
- Customizing Aggregations
- The Aggregation Design Wizard
- The Usage-Based Optimization Wizard
- Using Profiler
- Advanced Storage: MOLAP, HOLAP, or ROLAP
- Using Partitions with Advanced Storage Options
- ROLAP Dimensions
- Implementing Proactive Caching
- Notification Settings for Proactive Caching
- Using Partitions: Relational or SSAS
- Relational Table Partitioning in SQL Server 2008
- How to Implement OLTP Partitioning
- Other Capabilities of OLAP Partitions
- Cube and Dimension Processing Options
7. Introduction to MDX Queries
- Understanding MDX
- MDX Structure Names
- MDX Syntax Rules
- Writing your First MDX Query
- About Members, Tuples, and Sets
- Working with MDX Functions
- Common MDX Functions Explained
- MDX Functions or Keywords Added or Revised in SSAS 2005
8. MDX Expressions
- Working with 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?
9. Introduction to Data Mining
- Understanding Data Mining Concepts
- Data Mining Terminology
- Understanding Your Business Questions
- Data Mining Algorithm Classifications
- Implementing Data Mining
- Understanding Data Mining Algorithms
- Data Mining Algorithm Features
- Mining Model Algorithms
- Creating Data Mining Structures
- Content and Data Types
- Separating Test and Training Data
- Other Model Properties
- Reviewing Data Mining Structures and Models
- Mining Structure Subtab
- Mining Models Subtab
- Mining Model Viewers
- Mining Accuracy Charts
- Mining Prediction Viewers
- 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
10. SSAS Administration
- Implementing SSAS Security
- Reducing the Attack Surface
- Data Source Connection Methods
- Implementing Database Roles
- © ITCourseware, LLC 3
- SQL Server 2008 Analysis Services
- Other Security Considerations
- Managing with Scripts
- Implementing XMLA Scripts
- Using Schema Rowsets
- Using Scripts to View SSAS Statistics
- Deploying and Synchronizing Databases
- Deploying by Using BIDS
- Using the Deployment Wizard
- Using the Synchronize Database Wizard
- Understanding SSAS Backup and Restore
- SSAS Database Backups
11. Advanced Administration and Optimization
- Using SSIS with Analysis Services
- Using SSIS to Process Partitions
- Using SSIS to Process Mining Models
- Other SSIS Analysis Services Tasks
- Improving Availability and Scalability
- Understanding Clustering
- Improving Scalability
- Performance Optimization
- Impacting Performance
12. Introduction to SSAS Clients
- Using Excel 2007 as an SSAS Client
- Creating Connections
- Implementing PivotTable Reports
- Excel 2007 PivotChart Reports
- Additional Excel 2007 OLAP Tools
- Implementing Excel 2007 as a Data Mining Client
- Configuring the Add-in
- Using the Data Preparation Section
- Examining the Connection Section
- Using the Data Modeling Section
- Using the Accuracy and Validation Section
- Using the Model Usage Section
- Management and Help Sections
- Using the Data Mining Templates for Visio
- Using SQL Server Reporting Services
- Build an SSRS Report
- View the SSRS Report
- Design the SSRS Report
- Deploy the SSRS Report
- Report Builder
- Implementing SharePoint Server BI Features
- Excel 2007 Web Services
- SharePoint Server Data Connection Libraries
- SharePoint Server KPIs
- Other SharePoint Server BI Capabilities
