Janus / Fouche Pro SQL Server 2008 Analysis Services

1. ed
ISBN: 978-1-4302-1996-5
Verlag: Apress
Format: PDF
Kopierschutz: 1 - PDF Watermark

E-Book, Englisch, 480 Seiten

Every business has reams of business data locked away in databases, business systems, and spreadsheets. While you may be able to build some reports by pulling a few of these repositories together, actually performing any kind of analysis on the data that runs your business can range from problematic to impossible. Pro SQL Server 2008 Analysis Services will show you how to pull that data together and present it for reporting and analysis in a way that makes the data accessible to business users, instead of needing to rely on the IT department every time someone needs a different report. Accessible-With a single author's voice, this book conducts a guided tour through the technology that makes it easy to dive into. Solution-oriented-While technically deep, the goal is to focus on practical application of the technologies instead of acting as a technical manual. Comprehensive-This book covers every aspect of analysis services and ancillary technologies to enable you to make the most of SQL Server.

Philo Janus is a senior technology specialist with Microsoft. Over the years, he has presented InfoPath to thousands of users and developers, and assisted with enterprise implementations of InfoPath solutions. With that background, he is particularly sensitive to the difficulties users and developers have had with InfoPath. He graduated from the U.S. Naval Academy with a bachelor of science degree in electrical engineering in 1989 to face a challenging career in the U.S. Navy. After driving an aircraft carrier around the Pacific Ocean and a guided missile frigate through both the Suez and Panama canals, and serving in the U.S. Embassy in Cairo, a small altercation between his bicycle and an auto indicated a change of career (some would say that landing on his head in that accident would explain many things). Philo's software development career started with building a training and budgeting application in Access 2.0 in 1995. Since then, he's worked with Oracle, Visual Basic, SQL Server, and .NET building applications for federal agencies, commercial firms, and conglomerates. In 2003, he joined Microsoft as a technology specialist evangelizing Office as a development platform.

Autoren/Hrsg.

Janus, Philo

Fouche, Guy

Inhaltsverzeichnis

1;Title Page;1
2;Copyright Page;2
3;Contents at a Glance;4
4;Table of Contents;5
5;About the Authors;14
6;About the Technical Reviewers;15
7;Acknowledgments;16
8;Introduction;17
9;CHAPTER 1 Introduction to OLAP;18
9.1;From Pivot Tables to Dimensional Processing;19
9.2;Data Warehousing;21
9.3;Applications of OLAP;22
9.4;History of OLAP;24
9.5;SQL Server Analysis Services;25
9.6;Data Mining;30
9.7;Summary;31
10;CHAPTER 2 Cubes, Dimensions, and Measures;32
10.1;Cubes and Their Components;32
10.2;Defining Measures and Dimensions;35
10.2.1;Schemas;35
10.2.2;Dimensions in Depth;37
10.2.2.1;Attributes;38
10.2.2.2;Slowly Changing Dimensions (SCD);38
10.2.2.3;Hierarchies;40
10.2.2.4;Time Dimensions;43
10.2.3;Measures;44
10.2.4;Types of Aggregation;48
10.2.4.1;Additive;49
10.2.4.2;Semiadditive;49
10.2.4.3;Nonadditive;49
10.2.5;Writeback;49
10.2.6;Calculated Measures;50
10.3;Actions;51
10.4;XMLA;52
10.5;Multidimensional Expressions (MDX);53
10.6;Data Warehouses;54
10.7;Storage;55
10.7.1;Staging Databases;55
10.7.2;Storage Modes;55
10.7.2.1;MOLAP;56
10.7.2.2;ROLAP;56
10.7.2.3;HOLAP;56
10.8;Summary;56
11;CHAPTER 3 SQL Server Analysis Services;57
11.1;Requirements;57
11.1.1;Hardware;57
11.1.2;Virtualization;59
11.1.3;Software;60
11.1.4;Upgrading;60
11.1.5;Standard or Enterprise Edition?;61
11.2;Architecture;62
11.2.1;The Unified Dimensional Model;62
11.2.2;Logical Architecture;65
11.2.3;Physical Architecture;68
11.2.4;Storage;70
11.3;Cube Structures in SSAS;75
11.3.1;Data Sources;77
11.3.2;Data Source View;77
11.3.3;The Cube Structure Itself;79
11.3.4;Dimensions;80
11.3.5;Mining Structures;81
11.4;What’s New in SQL Server 2008;81
11.4.1;Performance;81
11.4.1.1;Management Data Warehouse;81
11.4.1.2;Reliable Query Cancel;83
11.4.1.3;Dynamic Management Views;84
11.4.1.4;Block Computing—Optimization;84
11.4.1.5;Writeback Performance Improvements;84
11.4.1.6;Change Data Capture;84
11.4.2;Tools;85
11.4.2.1;Dimension Design;85
11.4.2.2;Aggregation / UBO Designer;87
11.4.2.3;AMO Design Warnings;88
11.5;Summary;89
12;CHAPTER 4 SSAS Developer and Admin Interfaces;90
12.1;Business Intelligence Development Studio;90
12.1.1;BIDS Is Visual Studio?;90
12.1.2;Panes;91
12.1.3;Solution Explorer;94
12.1.4;Properties Pane;95
12.1.5;Creating or Editing a Database Solution;97
12.1.5.1;Create a New Analysis Services Project;97
12.1.5.2;Open an Existing SSAS Database;99
12.1.5.3;Open an Existing SSAS Database as Part of a Project;100
12.2;SQL Server Management Studio;101
12.2.1;Managing Analysis Services;102
12.2.1.1;Data Sources;102
12.2.1.2;Data Source Views;103
12.2.1.3;Cubes;104
12.2.1.4;Dimensions;105
12.2.1.5;Mining Structures;105
12.2.1.6;Roles;105
12.2.2;Executing MDX Queries;106
12.3;PowerShell;107
12.3.1;A Convincing Example;108
12.3.2;PowerShell for SQL Server;108
12.3.3;PowerShell with SSAS;109
12.4;Summary;110
13;CHAPTER 5 Creating a Data Source View;111
13.1;Cubes Need Data;111
13.2;Data Sources;112
13.3;Data Source Views;119
13.3.1;Designer Tour;119
13.3.1.1;Finding Tables;120
13.3.1.2;Replacing a Table;120
13.3.2;Named Calculations and Queries;124
13.3.2.1;Named Calculations;124
13.3.2.2;Named Queries;126
13.4;Summary;130
14;CHAPTER 6 Creating Dimensions;131
14.1;Dimensional Analysis;131
14.1.1;Review of the Dimension Concept;132
14.1.2;Star or Snowflake?;133
14.1.2.1;The Advantage of Simplicity;134
14.2;Dimensions in SSAS;141
14.2.1;Creating a Dimension;141
14.2.2;Analysis Management Objects (AMO) Warnings;149
14.2.3;Dimension Properties;150
14.2.3.1;Dimension Type;151
14.2.3.2;ErrorConfiguration;154
14.2.3.3;MdxMissingMemberMode;154
14.2.3.4;Processing;154
14.2.3.5;Storage;155
14.2.3.6;UnknownMember;162
14.2.3.7;WriteEnabled;162
14.3;Attributes;162
14.3.1;Attribute Relationships;164
14.3.2;Attribute Properties;170
14.4;Parent-Child Dimensions;171
14.5;The Time Dimension;173
14.6;Summary;180
15;CHAPTER 7 Building a Cube;181
15.1;Dimensions and Cubes;183
15.1.1;Creating Cubes;184
15.1.2;Using Measure Group Tables;184
15.1.3;Selecting Dimensions;187
15.1.4;Defining Dimension Usage;193
15.2;Measures and Measure Groups;196
15.2.1;Measures;196
15.2.2;Measure Groups;198
15.2.3;Calculated Measures;199
15.3;Summary;207
16;CHAPTER 8 Deploying and Processing;208
16.1;Deploying a Project;208
16.1.1;Project Properties;208
16.1.2;Deployment Methods;211
16.2;Using the Deployment Wizard;211
16.2.1;Running the Wizard;212
16.2.2;Input Files;214
16.2.3;Deployment Scripts;215
16.3;Synchronizing SSAS Databases;215
16.4;Processing;217
16.4.1;What Processing Does for Us;218
16.4.2;How to Initiate Processing from BIDS;221
16.4.3;Processing from SQL Server Management Studio;227
16.4.4;Processing via XMLA;228
16.4.5;Processing with Analysis Management Objects (AMO);228
16.5;Scheduling OLAP Maintenance;228
16.6;Summary;231
17;CHAPTER 9 MDX;232
17.1;Why the Need?;232
17.2;Tuples and Sets;234
17.2.1;Notation;235
17.2.2;Tuples;236
17.2.3;Sets;241
17.3;MDX Queries;241
17.3.1;SELECT;242
17.3.2;WHERE;245
17.3.3;MDX Functions;246
17.3.4;Categories of Functions;252
17.3.4.1;Tree Functions;252
17.3.4.2;Aggregate Functions;254
17.3.4.3;Time Functions;258
17.4;Summary;262
18;CHAPTER 10 Cube Features;263
18.1;Business Intelligence;263
18.1.1;Time Intelligence;264
18.1.2;Account Intelligence;266
18.1.3;Dimension Intelligence;267
18.2;Operators, Functions, and More;267
18.2.1;Unary Operators;267
18.2.2;Custom Member Formulas;268
18.2.3;Attribute Ordering;269
18.2.4;Currency Conversion;269
18.3;Calculations Tab;270
18.3.1;Calculated Measures;271
18.3.2;Named Sets;274
18.4;Other Cube Features;274
18.4.1;Key Performance Indicators;275
18.4.2;Actions;277
18.4.3;Perspectives;282
18.4.4;Translations;284
18.5;Summary;285
19;CHAPTER 11 Data Mining;286
19.1;Why Mine Data?;286
19.2;Using Data-Mining Algorithms;287
19.2.1;Microsoft Naïve Bayes;287
19.2.2;Microsoft Clustering;287
19.2.3;Microsoft Decision Trees;288
19.3;Creating the Accessory Buyers Marketing Campaign;288
19.4;Preparing the Data Warehouse;289
19.4.1;Creating the Accessory Buyers Views in AdventureWorks;289
19.4.2;Creating the Accessory Campaign Data Source View;292
19.5;Finding Accessory Buyers by Using the AdventureWorks EDW;293
19.6;Using the Data Mining Model Designer;300
19.6.1;The Mining Structure View;301
19.6.2;The Mining Models View;302
19.6.3;The Mining Model Viewer View;303
19.6.3.1;Exploring the Decision Tree;303
19.6.3.2;Using Drill-Through to View a Model’s Training Cases;306
19.6.3.3;Using the Dependency Network;307
19.6.4;The Mining Accuracy Chart View;308
19.6.5;The Mining Model Prediction View;310
19.7;Finding Accessory Buyers by Using Data Mining Extensions (DMX);314
19.7.1;Use the DMX Development Environment;314
19.7.2;Create the Accessory Buyers Mining Structure;315
19.7.3;Add a Naïve Bayes Mining Model to the Accessory Buyers Campaign;316
19.7.4;Process the Accessory Buyers Campaign;316
19.7.5;View the Accessory Buyers Mining Model;317
19.7.6;Predict Our Accessory Buyers;319
19.8;Summary;321
20;CHAPTER 12 PowerPivot;322
20.1;PowerPivot Support in SQL Server 2008 R2;322
20.1.1;Master Data Services;322
20.1.2;Excel Writeback;324
20.2;PowerPivot from Excel;331
20.3;PowerPivot with SharePoint Server 2010;337
20.4;Summary;342
21;CHAPTER 13 Administration;343
21.1;DBA Tasks;343
21.1.1;Processing a Cube;343
21.1.2;Processing Options;345
21.1.3;Processing Architecture;346
21.1.4;Profiler;347
21.1.5;Performance Monitor;347
21.1.6;Automation;349
21.1.7;XML for Analysis;350
21.1.8;Analysis Management Objects;350
21.1.9;PowerShell;353
21.1.10;Scheduling;353
21.1.10.1;SQL Server Agent;354
21.1.11;SQL Server Integration Services;355
21.2;Security;358
21.2.1;Authentication;358
21.2.2;Authorization;359
21.3;Performance;362
21.3.1;Design;362
21.3.1.1;Dimensions;363
21.3.1.2;Attribute Design;363
21.3.1.3;Partitions;366
21.3.2;Aggregations;369
21.3.2.1;Understanding Aggregations;369
21.3.2.2;Creating an Aggregation;370
21.3.2.3;Preparing an Aggregation for Use;375
21.3.3;Scaling;377
21.3.3.1;Large Data Size;377
21.3.3.2;Large Number of Users;377
21.3.4;Virtualization;379
21.3.5;SharePoint Server 2010;379
21.4;Summary;381
22;CHAPTER 14 User Interfaces;382
22.1;Excel 2007;382
22.1.1;Data Source Connections;383
22.1.2;Pivot Tables;388
22.1.2.1;Pivot Table Task Pane;389
22.1.2.2;Pivot Table Ribbons;390
22.1.2.3;Formatting a Pivot Table;392
22.1.2.4;Formulas Using Pivot Table Values;394
22.1.3;Pivot Charts;395
22.2;Visio 2007;408
22.3;SQL Server Reporting Services;410
22.3.1;Reports;410
22.3.2;Tablix;411
22.3.3;Charts;419
22.3.4;Report Builder 2.0;427
22.4;MOSS 2007;429
22.4.1;KPI Lists;430
22.4.2;Excel Services;432
22.5;PerformancePoint;432
22.6;Summary;438
23;APPENDIX A Setting Up Adventure Works;439
24;APPENDIX B Data-Mining Resources;443
24.1;Books;443
24.2;Web Sites;443
25;Index;444