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


Just as every needs to effectively employ business intelligence (BI) to stay competitive; every IT professional needs to master BI to stay employed in this fastest-growing segment of information technology. Foundations of Server 2005 Intelligence is the quickest path to understanding BI; and it is essential reading for all who work with SQL Server 2005. It is written from a practical perspective; perfect for anyone who uses the in Server 2005s extraordinarily rich BI product suite.
This book explains how best to use Services; Server Services; Server Reporting Services; and SQL Server Data Mining. It also describes best practices for implementing end-to-end BI solutions in small; medium; and large environments. And it provides important information about integrating BI with various client tools; including Excel; Business Scorecards; Proclarity; and SharePoint Portal Server. Developers; end users; and even managers will find this an enlightening guide to the power and promise of SQL Server 2005 BI.

View more

Table of Contents

About the Technical Reviewer

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

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

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

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

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

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?

7. Cube Storage and Aggregation
Using the Default Storage: MOLAP
XMLA (XML for Analysis)
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?

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

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

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

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

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
Data Mining Clients

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

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

Leave us a commentNo Comments

Inline Feedbacks
View all comments
Would love your thoughts, please comment.x