Applied Microsoft Analysis Services 2005: And Microsoft Business Intelligence Platform

Contents......Page 4
1.1 What is Analysis Services 2005?......Page 16
Preface......Page 12
Acknowledgements......Page 13
About the book......Page 14
About the author......Page 15
1.1.1 Introducing OLAP......Page 17
1.1.2 Introducing Data Mining......Page 19
1.1.3 Overview of SSAS......Page 20
1.1.4 SSAS and Microsoft Business Intelligence Platform......Page 21
1.2 Understanding OLAP......Page 25
1.2.2 Dimensional Model......Page 26
1.3 Understanding The Unified Dimensional Model......Page 31
1.3.1 Relational Model Features......Page 32
1.3.2 Dimensional Model Features......Page 34
1.3.3 UDM Components......Page 36
1.4 Analysis Services Architecture......Page 38
1.4.2 XML for Analysis (XMLA) Specification......Page 39
1.4.3 SSAS Clients......Page 41
1.5.1 Introducing Adventure Works Sales OLAP System (SOS OLAP)......Page 42
1.5.2 Your First OLAP Report......Page 44
1.6 Summary......Page 49
1.7 Resources......Page 50
2.1.1 SSAS Databases......Page 54
2.1.2 Data Sources......Page 56
2.2 Designing Databases......Page 59
2.2.1 Project Mode......Page 60
2.2.2 Online Mode......Page 61
2.2.3 Other Management and Design Options......Page 62
2.3 The Four-Step Design Process......Page 63
2.3.2 Step 2: Declare Grain......Page 64
2.3.4 Step 4: Identify Facts......Page 65
2.4.1  Working with Analysis Services Projects......Page 66
2.4.2 Connecting to Data Sources......Page 68
2.4.3 Choosing Data Source Authentication Mode......Page 70
2.4.4 Constructing the Real-time Data Source View......Page 73
2.5.1 Designing the Data Warehouse Data Schema......Page 81
2.5.2 Constructing the Historical Data Source View......Page 84
2.7 Resources......Page 90
3.1.1 Attributes......Page 91
3.1.2 Hierarchies......Page 95
3.1.3 Member properties......Page 97
3.1.5 Dimension Storage......Page 101
3.2.1 Measure Bindings......Page 102
3.2.3  The Cube Object Schema......Page 103
3.3.1 Building a Cube using the Cube Wizard......Page 104
3.3.2 Introducing the Dimension Wizard......Page 110
3.3.3 Introducing the Dimension Designer......Page 115
3.3.4 Introducing the Cube Designer......Page 120
3.5 Resources......Page 127
4.1 Working with Standard Dimensions......Page 128
4.1.2 Configuring Attributes......Page 129
4.1.3 Configuring Attribute Hierarchies......Page 130
4.1.4 Creating a Multilevel Hierarchy......Page 133
4.1.5 Defining the Hierarchy Top Member......Page 134
4.1.6 Sorting Attribute Members......Page 135
4.2.1 Creating Ragged Hierarchies......Page 138
4.2.2  Implementing Multiple Hierarchies......Page 139
4.2.3 Working with Parent-child Hierarchies......Page 143
4.2.4 Grouping Attribute Members......Page 148
4.3.1 Standard Time Dimensions......Page 151
4.3.2 Server Time Dimensions......Page 154
4.4.1 Formatting Measures......Page 159
4.4.2 Understanding Measure Additivity......Page 160
4.4.3 Working with Additive Measures......Page 162
4.4.4 Creating New Measure Groups......Page 165
4.4.5 Working with Measure Expressions......Page 167
4.6 Resources......Page 170
5.1 Working with Dimension Relationships......Page 171
5.1.1 Understanding Dimension Usage......Page 172
5.1.2 Multi-Grain Relationships......Page 174
5.1.3 Role Playing Relationships......Page 177
5.1.4 Fact Relationships......Page 178
5.1.5 Reference Relationships......Page 182
5.1.6 Many-to-Many Relationships......Page 184
5.1.7 Treating Dimensions As Measure Groups......Page 186
5.2.1 Handling Semi-additive Measures......Page 188
5.2.2 Implementing Accounting Intelligence......Page 189
5.2.3 Using Custom Aggregation......Page 193
5.2.4 Dimension Writeback......Page 197
5.3 Working with External UDM Objects......Page 200
5.3.1 Linking Dimensions and Measure Groups......Page 201
5.3.2 Working with Linked Object Relationships......Page 203
5.4.1 Understanding Error Configurations......Page 204
5.4.2 Enabling the Dimension Unknown Member......Page 205
5.4.3 Dealing with Fact Table Integrity Errors......Page 207
5.6 Resources......Page 209
6.1.1 Introducing the Inventory UDM......Page 212
6.1.2 Creating a Cube Template......Page 214
6.2.1 Creating a Cube without a Data Source......Page 216
6.2.2 Generating the Subject Area Database Schema......Page 219
6.3.1  Understanding SSIS projects......Page 223
6.3.2 Loading the Staging Database......Page 225
6.3.3  Loading the Subject Area Database......Page 227
6.3.4 Processing Analysis Services Objects......Page 234
6.3.5 Working with Package Configurations......Page 235
6.3.6 Testing Package Execution......Page 236
6.5 Resources......Page 238
7.1 Understanding Data Mining......Page 239
7.1.1 What is Data Mining?......Page 240
7.1.2 Data Mining Tasks and Algorithms......Page 241
7.1.3 Data Mining Architectural View......Page 242
7.1.4 Data Mining Extensions (DMX)......Page 243
7.2.1 Data Mining Models......Page 244
7.2.2 Data Mining Structures......Page 248
7.2.3 The Data Mining Design Process......Page 250
7.3.1 Defining the Mining Domain......Page 253
7.3.3 Constructing the Data Schema......Page 254
7.3.4 Building the Model......Page 255
7.3.5 Exploring the Model......Page 261
7.3.6 Evaluating the Model......Page 266
7.4 Summary......Page 271
7.5 Resources......Page 272
8.1.1 Advantages of OLAP Data Mining......Page 273
8.2 Customer Profiling......Page 274
8.2.2 Implementing an OLAP Mining Model......Page 275
8.2.4 Exploring the Model......Page 279
8.2.5 Enhancing UDM with Data Mining......Page 281
8.3.1 Working with Nested Tables......Page 282
8.3.2 Exploring the Model......Page 286
8.4 Sales Forecasting......Page 288
8.4.1 Designing the Model Schema......Page 289
8.4.2 Constructing the Mining Model......Page 290
8.4.3 Exploring the Mining Model......Page 293
8.4.4 Fine-tuning the Mining Model......Page 295
8.5.1 Building a Data Mining-aware Integration Package......Page 298
8.5.2 Working with the Data Mining Query Task......Page 299
8.7 Resources......Page 300
9.1.1 What is MDX?......Page 304
9.1.2 How is MDX Used in UDM?......Page 305
9.1.3 Tuples and Sets......Page 308
9.1.4 How Autoexists Affects The Cube Space......Page 312
9.2.1 Introducing the MDX Designer......Page 314
9.2.2 How the Cube Aggregates......Page 317
9.2.3 Troubleshooting MDX Errors......Page 318
9.2.4 Your First MDX Expression......Page 320
9.2.5 Calculating Averages......Page 323
9.3 Navigating Hierarchies Horizontally......Page 325
9.3.2 Comparing Values Between Adjacent Time Periods......Page 326
9.3.3 Comparing Values Between Parallel Time Periods......Page 330
9.3.4 Period-to-Date Aggregations......Page 332
9.4 Navigating Hierarchies Vertically......Page 333
9.4.1 Calculating Ratios......Page 334
9.4.2 Allocating Values......Page 337
9.4.3 Creating Dimension Calculated Members......Page 339
9.6 Resources......Page 341
10.1.1 Constructing Sets......Page 342
10.1.2 Using Sets in MDX Queries......Page 345
10.1.3 Crossjoining Sets......Page 346
10.1.4 Filtering Sets......Page 348
10.1.5 Named Sets......Page 351
10.2.1 Understanding Subcube Expressions......Page 356
10.2.2 Your First Subcube Expression......Page 357
10.2.3 Script Execution Revisited......Page 361
10.2.4 Implementing Targeted Allocations......Page 363
10.3.1 Applying Time Intelligence......Page 366
10.3.2 Handling Currency Conversions......Page 370
10.5 Resources......Page 375
11.1.1 Extensibility Scenarios......Page 376
11.2 Using Standard External Libraries......Page 377
11.2.2  Setting the Default Member Programmatically......Page 378
11.3.1 Understanding SSAS Stored Procedures......Page 380
11.3.2 Deploying Stored Procedures......Page 383
11.3.3 Securing Stored Procedures......Page 385
11.3.5  Debugging Stored Procedures......Page 386
11.4.1 Exploring the ADOMD.NET Object Model......Page 387
11.4.2 Working with Current Context......Page 388
11.4.3 Constructing a Set Dynamically......Page 390
11.4.4 Getting Data Mining Prediction Results......Page 392
11.6 Resources......Page 394
12.1.1 Understanding KPIs......Page 395
12.1.2 Your First KPI......Page 398
12.1.3 Implementing FOSH Metrics......Page 402
12.1.4 Retrieving the KPI Metadata......Page 405
12.2 Extending UDM With Actions......Page 407
12.2.1 Understanding Actions......Page 408
12.2.2 Requesting URL Resources With Regular Actions......Page 409
12.2.3 Displaying Detail Cell Data With Drillthrough Actions......Page 413
12.2.4 Generating Reports With Reporting Actions......Page 416
12.2.5  Changing Cell Data With Cube Writeback......Page 419
12.3.1 Understanding Perspectives......Page 425
12.3.2 Defining Perspectives......Page 426
12.4 Localizing UDM With Translations......Page 427
12.4.2 Translating Metadata......Page 428
12.4.3 Translating Data......Page 429
12.4.4 Testing Translations......Page 431
12.6 Resources......Page 432
13.1.1 The UDM Management Lifecycle......Page 436
13.1.2 Managing the Analysis Server......Page 438
13.1.3 Scripting Objects and Tasks......Page 440
13.2.1 Deploying Databases......Page 443
13.2.2 Synchronizing Databases......Page 448
13.2.4 Automating Management Tasks......Page 450
13.3.1 Tracing With SQL Server Profiler......Page 452
13.3.2 Monitoring the Server Performance with Performance Counters......Page 456
13.3.3 Capture and Replay With Flight Recorder......Page 458
13.4 Programming with AMO......Page 459
13.4.1  Understanding Management API......Page 460
13.4.2 AMO vs. ADOMD.NET......Page 461
13.4.3  Creating Local Cubes with AMO......Page 462
13.5 Summary......Page 465
13.6 Resources......Page 466
14.1 Designing Storage......Page 467
14.1.1 Understanding Storage Modes......Page 468
14.1.2 Configuring Partition Storage......Page 471
14.2 Managing Aggregations......Page 473
14.2.1 Understanding Aggregations......Page 474
14.2.2 Designing Aggregations......Page 476
14.2.3 Usage-Based Optimization......Page 481
14.2.4 Examining the Aggregation Design......Page 484
14.3.1 Architecting Large-Scale SSAS Sites......Page 485
14.3.2 Optimizing Partition Storage......Page 487
14.3.3 Creating Partitions Programmatically with AMO......Page 493
14.3.4 Generating Partitions with SSIS......Page 497
14.5 Resources......Page 499
15.1 Understanding UDM Processing......Page 500
15.1.2 Analyzing Processing Impact......Page 501
15.2 Processing Dimensions......Page 502
15.2.2 Rebuilding Dimensions......Page 503
15.2.3 Incrementally Processing Dimensions......Page 506
15.3.1 Understanding Cube Processing......Page 508
15.3.3 Refreshing the Cube Data......Page 509
15.3.4 Processing Cubes Incrementally......Page 510
15.4.1 Automating Processing With SSIS Packages......Page 512
15.4.2 Automating Processing With Scripting......Page 514
15.5.1 The Reality of Real Time OLAP......Page 515
15.5.2 Push-Mode Processing......Page 518
15.5.3 Proactive Caching......Page 522
15.7 Resources......Page 533
16.1 Understanding SSAS Role-based Security......Page 534
16.1.1 Understanding Roles......Page 535
16.1.2 Permissions and Access Rights......Page 538
16.1.3 Creating Database Administrator Role......Page 540
16.1.4 Managing Cube-level Security......Page 543
16.2.1  Understanding UDM Data Security......Page 546
16.2.2 Configuring Basic Dimension Security......Page 548
16.2.3 Configuring Advanced Dimension Security......Page 550
16.2.4 Implementing Dynamic Dimension Security......Page 554
16.3.1 Understanding Cell Security......Page 558
16.3.3  Externalizing Cell Data Security......Page 560
16.4.1 How Security Impacts Intranet Applications......Page 562
16.4.2 Internet Security Approaches......Page 563
16.6 Resources......Page 568
17.1 Understanding Integration Options......Page 572
17.1.1 Thin Clients......Page 573
17.1.2 Native Clients......Page 574
17.1.3 .NET Clients......Page 575
17.2.1 Web Service Integration......Page 576
17.2.2 Other XMLA Integration Options......Page 579
17.2.3 Tracing XMLA Traffic......Page 581
17.3.1 Introducing the Legacy Client Sample Application......Page 584
17.3.2 Retrieving the UDM Metadata......Page 585
17.3.3 Working with MDX Queries......Page 586
17.4 Implementing .NET Clients......Page 587
17.4.1 Understanding ADOMD.NET Object Hierarchy......Page 588
17.4.3 Managing Connections and Sessions......Page 589
17.4.4 Retrieving the Query Results......Page 591
17.5.1 Querying Mining Models......Page 595
17.5.2 Generating Session Mining Models......Page 600
17.5.3 Generating Local Mining Models......Page 602
17.5.4 Displaying Data Mining Results......Page 603
17.6 Summary......Page 605
17.7 Resources......Page 606
18.1 Understanding Reporting Services......Page 607
18.1.1 Reporting Services Architecture......Page 608
18.1.2 Integrating Reporting Clients......Page 610
18.2.1 Report Authoring Basics......Page 611
18.2.2 Creating Tabular Reports......Page 613
18.2.3 Implementing Parameterized Reports......Page 619
18.3 Authoring Data Mining Reports......Page 623
18.3.1 Targeted Campaigning......Page 624
18.3.2 Sales Forecasting......Page 625
18.3.3 Working with CLR Stored Procedures......Page 629
18.3.4 Implementing Drillthrough Navigation......Page 631
18.4 Delivering Reports......Page 632
18.4.1 Report-enabling Windows Reporting Clients......Page 633
18.5 Generating UDM Ad hoc Reports......Page 637
18.5.1 Understanding Ad hoc Reporting Architecture......Page 638
18.5.2  The Ad Hoc Model......Page 639
18.5.3 Authoring Ad hoc Reports......Page 640
18.6 Summary......Page 642
18.7 Resources......Page 643
19.1 Using Excel Analytics Tools......Page 644
19.1.1 Authoring PivotTable Reports......Page 645
19.1.2 Building Local Cubes......Page 651
19.1.3 Creating Structured Reports......Page 652
19.2.1 Understanding Office Web Components......Page 657
19.2.2 Programming OWC......Page 659
19.2.3 Integrating OWC with Reporting Services......Page 661
19.3.1 Understanding Business Scorecard Manager......Page 663
19.3.2 Implementing Scorecards......Page 665
19.3.3 Deploying Scorecards......Page 675
19.4 Summary......Page 677
19.5 Resources......Page 678
Appendix A......Page 679
Appendix B......Page 688
Master resource list......Page 699
Index......Page 701