Foundations of SQL Server 2005 Business Intelligence – Lynn Langit – 1st Edition

About the Technical Reviewer
Acknowledgments

1. What Is Business Intelligence?
Just What Is BI?
Defining BI Using Microsoft's Tools
What Microsoft Products Are Involved?
BI Languages
Understanding BI from an End User's Perspective
Demonstrating the Power of BI Using Excel 2003 Pivot Tables
Understanding BI Through the Sample
Understanding the Business Problems that BI Addresses
Reasons to Switch to Microsoft's BI Tools
Summary

2. OLAP Modeling
Modeling OLAP Source SchemasStars
Understanding the Star Schema
Understanding a Dimension Table
Why Create Star Schemas?
Effectively Creating Star Schema Models Using Grain Statements
Tools for Creating Your OLAP Model
Modeling Source SchemasSnowflakes and Other Variations
Understanding the Snowflake Schema
Knowing When to Use Snowflakes
Considering Other Possible Variations
Choosing Whether to Use Views Against the Relational Data Sources
Understanding Dimensional Modeling (UDM)
Using the UDM
The Slowly Changing Dimension (SCD)
The Rapidly Changing Dimension (RCD)
Writeback Dimension
Understanding Fact (Measure) Modeling
Calculated Measure vs. Derived Measure
Other Types of Modeling
Data Mining
KPIs (Key Performance Indicators)
Actions, Perspectives, Translations
Source Control and Other Documentation Standards
Summary

3. Introducing SSIS
Understanding ETL
Data Maps
Staging Servers
ETL Tools for BI/SSIS Packages
Basic SSIS Packages Using BIDS
Developing SSIS Packages
Designing SSIS Packages
Adding Transformations to the Data Flow
Summary

4. Using SSAS
Using BIDS to Build a Cube
Building Your First Cube
Refining Your Cube
Reviewing Measures
Reviewing Dimensions: Attributes
Reviewing Dimensions: Hierarchies
Reviewing Dimensions: Member Properties
Summary

5. Intermediate OLAP Modeling
Adding Key Performance Indicators (KPIs)
Implementing KPIs in SSAS
Considering Other KPI Issues
Using Perspectives and Translations
Perspectives
Translations
Localizing Measure Values
Using Actions
Other Types of Modeling
Summary

6. Advanced OLAP Modeling
Multiple Fact Tables in a Single Cube
Considering Nulls
Modeling Nonstar Dimensions
Snowflake Dimensions
Degenerate Dimensions
Parent-Child Dimensions
Many-to-Many Dimensions
Role-Playing Dimensions
Writeback Dimensions
Modeling Changing Dimensions and More
Error Handling for Dimension Attribute Loads
Using the Business Intelligence Wizard
What's Next?
Summary

7. Cube Storage and Aggregation
Using the Default Storage: MOLAP
XMLA (XML for Analysis)
Aggregations
MOLAP as Default in SSAS
Adding Aggregations
Advanced Storage: MOLAP, HOLAP, or ROLAP
Considering Other Types of Storage
ROLAP Dimensions
Huge Dimensions
Summarizing OLAP Storage Options
Using Proactive Caching
Notification Settings for Proactive Caching
Fine-Tuning Proactive Caching
Deciding Among OLTP Partitioning, OLAP Partitioning, or Both
Relational Table Partitioning in SQL Server 2005
Other OLAP Partition Configurations
Cube and Dimension Processing Options
What's Next?
Summary

8. Intermediate SSIS
General ETL Package-Design Best Practices
Creating the SSIS Package from Scratch
Configuring Connections
Using Data Source Views (DSVs)
Reviewing the Included Samples Packages
Adding Control Flow Tasks
Container Tasks
SQL Tasks
File System Tasks
Operating System Tasks
Script Tasks
Remote Tasks
SSAS Tasks
Precedence Constraints
Using Expressions with Precedence Constraints
Understanding Data Flow Transformations
Understanding Data Sources and Destinations
Adding Transformations to the Data Flow
Adding Data Transformations
Split Data Transformations
Translate Data Transformations
SSAS Data Transformations
Slowly Changing Dimension Transformation
Sample Data Transformations
Run Command Data Transformations
Enterprise Edition Only Data Transformations
Using the Dynamic Package Configuration Wizard
SSIS Expressions
Summary

9. Advanced SSIS
Understanding Package Execution
Data Viewers
Debugging SSIS Packages
Logging Execution Results
Error Handling
Event Handlers
Deploying the Package and Configuring Runtime Settings
SSIS Package Deployment Options
SSIS Package Execution Options
SSIS Package Security
Placing Checkpoints
Using Transactions in SSIS Packages
Summary

10. Introduction to MDX
Understanding Basic MDX Query Syntax
Writing Your First MDX Query
Members, Tuples, and Sets
Adding Calculated Members, Named Sets, and Script Commands
Using Calculated Measures
Named Sets
Script Commands
Understanding Common MDX Functions
New or Updated MDX Functions
Adding .NET Assemblies to Your SSAS Project
Configuring Assemblies
Summary

11. Introduction to Data Mining
Defining SSAS Data Mining
More Data Mining Concepts
Architectural Considerations
Reviewing Data Mining Structures
Mining Structure Viewers
Mining Accuracy Charts
Mining Prediction Viewers
Understanding the Nine Included Data Mining Algorithms
Using the Mining Structure Wizard
Content and Data Types
Processing Mining Models
SSIS and Data Mining
Working with the DMX Language
A Simple DMX Query
Data Mining Clients
Summary

12. Reporting Tools
Using Excel 2003: Pivot Charts and More
Limitations of Excel 2003 as an SSAS Client
Using SQL Server Reporting Services (SSRS)
Producing Reports with Report Builder
Working with .NET 2.0 Report Viewer Controls
Understanding SharePoint 2003 Web Parts
Examining Business Scorecard Manager (BSM) 2005
Considering ProClarity and Data Mining Clients
ProClarity
Data Mining Clients
Summary

13. SSAS Administration
Understanding Offline vs. Online Mode in BIDS
Reviewing SSMS/SSAS Administration
XML for Analysis (XMLA)
SSAS Deployment Wizard
Server Synchronization
Thinking About Disaster Recovery
Considering Security
Connection Strings
Security Roles
Other Security Planning Issues
Understanding Performance Tuning
Applying Scalability
Using High Availability Clustering
Summary

14. Integration with Office 2007
SQL Server 2005 SP2
Exploring Excel 2007
KPI Support
Configuring Excel 2007 as a Data Mining Client
Using Excel 2007 as a Data Mining Client
Using the Excel 2007 Data Preparation Group
Using the Excel 2007 Data Modeling Group
Using the Excel 2007 Accuracy and Validation Group
Additions to the Final Release
Integrating Microsoft Office SharePoint Server 2007 (MOSS)
Using Excel 2007 on the Web (Excel Services)
MOSS Data Connection Libraries
MOSS KPIs (Key Performance Indicators)
Using the SSRS Report Center and Reporting Web Parts
MOSS Business Data Catalog (BDC)
Exploring Performance Point Server (PPS) 2007
Summary
Conclusion