Foundations of SQL Server 2008 R2 Business Intelligence

✍ Scribed by Guy Fouche, Lynn Langit

• Publisher: Apress
• Year: 2011
• Tongue: English
• Leaves: 437
• Edition: 2
• Category: Library

✦ Synopsis

Foundations of SQL Server 2008 R2 Business Intelligence introduces the entire exciting gamut of business intelligence tools included with SQL Server 2008. Microsoft has designed SQL Server 2008 to be more than just a database. It’s a complete business intelligence (BI) platform. The database is at its core, and surrounding the core are tools for data mining, modeling, reporting, analyzing, charting, and integration with other enterprise-level software packages. SQL Server 2008 puts an incredible amount of BI functionality at your disposal. But how do you take advantage of it? That’s what this book is all about. Authors Guy Fouch? and Lynn Langit show how to implement end-to-end BI solutions using SQL Server Analysis Services (SSAS), SQL Server Integration Services (SSIS), SQL Server Reporting Services (SSRS), and other tools in the Microsoft business intelligence toolkit. You’ll learn about all-features such as PowerPivot and Report Builder 3.0. Also provided are clear examples of predictive analysis made possible through powerful data mining features in SQL Server. If you’re an analyst or developer working with SQL Server 2008 who is charged with delivering results that drive business success, you can’t afford to be without this book; you can’t afford to ignore the powerful business intelligence suite that Microsoft has placed at your disposal. Provides the “big picture” of Microsoft’s BI tool suite Covers PowerPivot and other game-changing technologies introduced alongside SQL Server 2008 Release 2 Gives a practical analysis of features based on real-world practices What you’ll learn Apply dimensional- and fact-based modeling to create OLAP schemas Optimize data extraction and transformation with SQL Server Integration Services Develop OLAP and data-mining solutions using SQL Server Analysis Services Create compelling reports using the new Report Builder 3.0 in SQL Server Reporting Services Deliver self-service business-intelligence via Microsoft PowerPivot Manage and maintain control over your business intelligence environment Who this book is for Foundations of SQL Server 2008 R2 Business Intelligence is for business intelligence analysts and developers wanting to employ the full stack of business intelligence tools that are part of SQL Server. The book also appeals to database administrators managing databases in support of business intelligence efforts. Table of Contents What Is Business Intelligence? OLAP Modeling Concepts Introducing OLAP Modeling with SSAS Intermediate OLAP Modeling with SSAS Advanced OLAP Modeling with SSAS Cube Storage and Aggregation Introducing SSIS Intermediate SSIS Advanced SSIS Reporting Tools Data Mining with Excel Introducing PowerPivot Introduction to MDX Introduction to Data Mining

✦ Table of Contents

Cover......Page 1
Contents at a Glance......Page 3
Contents......Page 427
About the Authors......Page 435
About the Technical Reviewer......Page 436
Acknowledgments......Page 437
Just What Is Business Intelligence?......Page 4
Defining BI Using Microsoft’s Tools......Page 7
What Microsoft Products Are Involved?......Page 8
BI Languages......Page 10
Understanding BI from an End User’s Perspective......Page 12
Deploying the Standard Edition Version of the Sample Cube......Page 13
How to Connect to the Sample Cube Using Excel......Page 20
Understanding BI Through the Sample......Page 24
Understanding the Business Problems That BI Addresses......Page 25
Reasons to Switch to Microsoft’s BI Tools......Page 26
Summary......Page 27
Modeling OLAP Source Schemas—Stars......Page 28
Understanding the Star Schema......Page 29
Understanding a Dimension Table......Page 30
Why Create Star Schemas?......Page 33
Effectively Creating Star Schema Models Using Grain Statements......Page 36
Tools for Creating Your OLAP Model......Page 37
Understanding the Snowflake Schema......Page 40
Knowing When to Use Snowflakes......Page 44
Understanding Unified Dimensional Modeling......Page 45
Using the UDM......Page 46
The Slowly Changing Dimension (SCD)......Page 48
Type 1, 2, 3 SCD Solutions......Page 49
The Rapidly Changing Dimension (RCD)......Page 50
An Example......Page 51
Data Mining......Page 53
Summary......Page 54
Using BIDS to Build a Cube......Page 55
Defining Your First Cube......Page 63
Adding Dimension Attributes......Page 66
Defining Hierarchies......Page 68
Building Your First Cube......Page 69
Reviewing Measures......Page 72
Reviewing Dimensions: Attributes......Page 75
Reviewing Dimensions: Hierarchies......Page 77
Summary......Page 83
Adding Key Performance Indicators (KPIs)......Page 84
Implementing KPIs in SSAS......Page 85
Implementing KPIs in SSMS......Page 90
Perspectives......Page 92
Translations......Page 93
Localizing Measure Values......Page 95
Creating Actions in SSAS......Page 101
Creating Actions in SSMS......Page 105
Summary......Page 106
Multiple Fact Tables in a Single Cube......Page 107
Nulls......Page 109
Snowflake Dimensions......Page 112
Degenerate Dimensions......Page 114
Parent-Child Dimensions......Page 115
Many-to-Many Dimensions......Page 116
Role-Playing Dimensions......Page 120
Writeback Dimensions......Page 121
Dimensions That Change......Page 123
Error Handling for Dimension Attribute Loads......Page 124
Using the Business Intelligence Wizard......Page 125
Summary......Page 129
XML for Analysis......Page 130
Aggregations......Page 132
The Aggregation Design Wizard......Page 134
The Usage-Based Optimization Wizard......Page 138
The SQL Server Profiler as an Aggregation Design Aid......Page 140
Understanding ROLAP......Page 141
Considering Non-MOLAP Storage......Page 142
Handling Huge Dimensions......Page 145
Summarizing OLAP Storage Options......Page 147
Using Proactive Caching......Page 148
Fine-Tuning Proactive Caching......Page 150
Setting Notifications for Proactive Caching......Page 151
Relational Table Partitioning in SQL Server......Page 152
Choosing Cube and Dimension Processing Options......Page 153
Summary......Page 158
Creating a Plan......Page 160
Creating a Data Map......Page 161
Refining a Data Map......Page 163
Adding a Staging Server......Page 164
Creating a Basic SSIS Package......Page 165
Building Basic SSIS Packages......Page 170
Developing SSIS Packages......Page 171
Designing SSIS Packages......Page 173
Adding Transformations to the Data Flow......Page 181
Summary......Page 185
Common ETL Package-Design Practices......Page 186
Creating the Package Itself......Page 187
Configuring Connections......Page 192
Using Data Source Views (DSVs)......Page 195
Reviewing the Included Samples Packages......Page 196
Adding Control Flow Tasks......Page 197
Container Tasks......Page 199
SQL Tasks......Page 200
File System Tasks......Page 203
Operating System Tasks......Page 204
Script Tasks......Page 205
SSAS Tasks......Page 206
Precedence Constraints......Page 208
Using Expressions with Precedence Constraints......Page 210
Data Sources......Page 213
Data Flow Destinations......Page 215
Transformation Types......Page 216
Adding Data Transformations......Page 218
Split Data Transformations......Page 220
Translate Data Transformations......Page 222
Slowly Changing Dimension Transformation......Page 226
Run Command Data Transformations......Page 230
Enterprise Edition–Only Data Transformations......Page 231
Using the Dynamic Package Configuration Wizard......Page 233
Summary......Page 235
Understanding Package Execution......Page 236
Data Viewers......Page 240
Debugging SSIS Packages......Page 242
Logging Execution Results......Page 244
Error Handling......Page 247
Event Handlers......Page 250
Deploying SSIS Packages......Page 251
SSIS Package Deployment Options......Page 252
SSIS Package Execution Options......Page 255
SSIS Package Security......Page 259
Placing Checkpoints......Page 260
Using Transactions in SSIS Packages......Page 261
Creating a Data Profile......Page 263
Viewing a Data Profile......Page 266
Summary......Page 269
Creating a Pivot Table......Page 270
Creating a Pivot Chart......Page 273
Publishing Your Workbook......Page 274
SSRS Components......Page 275
Building Your First SSRS Report......Page 276
Running the Report Server Project Wizard......Page 277
Designing the Query......Page 278
Previewing and Designing Your Report......Page 283
Publishing Your Report......Page 286
Creating a Report Model......Page 289
Creating a Dataset......Page 291
Creating a Report......Page 292
Summary......Page 298
The Excel Ribbon......Page 299
KPI Support in Excel......Page 304
Using Excel for Data Mining......Page 306
Configuring Excel as a Data Mining Client......Page 307
Using Excel as a Data Mining Client......Page 310
Using the Data Preparation Group......Page 316
Using the Data Modeling Group......Page 321
Using the Accuracy and Validation Group......Page 322
Summary......Page 326
The PowerPivot Ribbon......Page 327
The PowerPivot Designer......Page 330
Importing Adventure Works Data......Page 334
Enriching the Adventure Works Data......Page 336
Using PowerPivot Data in Excel......Page 343
Summary......Page 344
MDX Query Syntax......Page 345
Understanding the Core Terminology......Page 346
Learning the Basic Syntax......Page 348
Writing Your First MDX Query......Page 350
Discovering Members, Tuples, and Sets......Page 351
Calculated Members, Named Sets, and Script Commands......Page 353
Adding MDX Objects to Your Cube......Page 354
Using Calculated Measures......Page 357
Working with Named Sets......Page 359
Writing Script Commands......Page 360
Common MDX Functions......Page 362
Summary......Page 365
Defining SSAS Data Mining......Page 366
Data-Mining Concepts......Page 369
Architectural Considerations......Page 370
Mining Structure Tab......Page 371
Mining Models Tab......Page 372
Mining Model Viewer Tab......Page 375
Mining Accuracy Chart Tab......Page 378
Mining Model Prediction Tab......Page 379
Understanding and Using the Included Data Mining Algorithms......Page 380
The Nine Algorithms......Page 381
The Data Mining Wizard......Page 387
Content and Datatypes......Page 389
Processing Methods......Page 393
SSIS and Data Mining......Page 394
Working with the DMX Language......Page 396
Summary......Page 399
Creating A HIERARCHYID Table......Page 400
Adding Data to the Table......Page 401
Displaying Hierarchical Data in SSMS......Page 402
A......Page 403
B......Page 404
C......Page 405
D......Page 406
E......Page 409
F......Page 410
K......Page 411
M......Page 412
N......Page 413
O......Page 414
P......Page 415
R......Page 417
S......Page 418
T......Page 421
X......Page 422
Y.Z......Page 423