Every business has a 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.
- ComprehensiveThis book covers every aspect of Analysis Services and ancillary technologies to enable you to make the most of SQL Server.
What you’ll learn
- Understand the importance of OLAP and Analysis Services technologies.
- Build cubes and dimensions that let you get business value out of large volumes of data.
- Learn the fundamentals of MDX–the query language for advanced analysis.
- Apply ancillary aspects of SSAS: KPIs, perspectives, and calculated members.
- Apply basic data mining concepts–identify answers to questions you weren't even aware you should ask.
- Learn the various ways to consume Analysis Services data (Reporting Services, Excel, and ProClarity).
Who this book is for
Pro SQL Server 2008 Analysis Services is aimed at developers who want to take advantage of SQL Server's built-in business intelligence functionality to mine their data and produce reports that can drive business forward in right and profitable directions. The book is secondarily aimed at database administrators charged with supporting Analysis Services solutions.
Table of Contents
- Introduction to OLAP
- Cubes, Dimensions, and Measures
- SQL Server Analysis Services
- SSAS Developer and Admin Interfaces
- Creating a Data Source View
- Creating Dimensions
- Building a Cube
- Deploying and Processing
- MDX219 10: Cube Features
- Data Mining
- PowerPivot
- Administration
- User Interfaces
Author(s): Philo Janus, Guy Fouche
Series: Expert's Voice in SQL Server
Edition: 1
Publisher: Apress
Year: 2010
Language: English
Pages: 468
Prelim......Page 1
Contents at a Glance......Page 4
Contents......Page 5
About the Authors......Page 14
About the Technical Reviewers......Page 15
Acknowledgments......Page 16
Introduction......Page 17
Team rebOOk
......Page 18
From Pivot Tables to Dimensional Processing......Page 19
Data Warehousing......Page 21
Applications of OLAP......Page 22
History of OLAP......Page 24
SQL Server Analysis Services......Page 25
Data Mining......Page 30
Summary......Page 31
Cubes and Their Components......Page 32
Schemas......Page 35
Dimensions in Depth......Page 37
Slowly Changing Dimensions (SCD)......Page 38
Hierarchies......Page 40
Time Dimensions......Page 43
Measures......Page 44
Types of Aggregation......Page 48
Writeback......Page 49
Calculated Measures......Page 50
Actions......Page 51
XMLA......Page 52
Multidimensional Expressions (MDX)......Page 53
Data Warehouses......Page 54
Storage Modes......Page 55
Summary......Page 56
Hardware......Page 57
Virtualization......Page 59
Upgrading......Page 60
Standard or Enterprise Edition?......Page 61
The Unified Dimensional Model......Page 62
Logical Architecture......Page 65
Physical Architecture......Page 68
Storage......Page 70
Cube Structures in SSAS......Page 75
Data Source View......Page 77
The Cube Structure Itself......Page 79
Dimensions......Page 80
Management Data Warehouse......Page 81
Reliable Query Cancel......Page 83
Change Data Capture......Page 84
Dimension Design......Page 85
Aggregation / UBO Designer......Page 87
AMO Design Warnings......Page 88
Summary......Page 89
BIDS Is Visual Studio?......Page 90
Panes......Page 91
Solution Explorer......Page 94
Properties Pane......Page 95
Create a New Analysis Services Project......Page 97
Open an Existing SSAS Database......Page 99
Open an Existing SSAS Database as Part of a Project......Page 100
SQL Server Management Studio......Page 101
Data Sources......Page 102
Data Source Views......Page 103
Cubes......Page 104
Roles......Page 105
Executing MDX Queries......Page 106
PowerShell......Page 107
PowerShell for SQL Server......Page 108
PowerShell with SSAS......Page 109
Summary......Page 110
Cubes Need Data......Page 111
Data Sources......Page 112
Designer Tour......Page 119
Replacing a Table......Page 120
Named Calculations......Page 124
Named Queries......Page 126
Summary......Page 130
Dimensional Analysis......Page 131
Review of the Dimension Concept......Page 132
Star or Snowflake?......Page 133
The Advantage of Simplicity......Page 134
Creating a Dimension......Page 141
Analysis Management Objects (AMO) Warnings......Page 149
Dimension Properties......Page 150
Dimension Type......Page 151
Processing......Page 154
Storage......Page 155
Attributes......Page 162
Attribute Relationships......Page 164
Attribute Properties......Page 170
Parent-Child Dimensions......Page 171
The Time Dimension......Page 173
Summary......Page 180
Building a Cube......Page 181
Dimensions and Cubes......Page 183
Using Measure Group Tables......Page 184
Selecting Dimensions......Page 187
Defining Dimension Usage......Page 193
Measures......Page 196
Measure Groups......Page 198
Calculated Measures......Page 199
Summary......Page 207
Project Properties......Page 208
Using the Deployment Wizard......Page 211
Running the Wizard......Page 212
Input Files......Page 214
Synchronizing SSAS Databases......Page 215
Processing......Page 217
What Processing Does for Us......Page 218
How to Initiate Processing from BIDS......Page 221
Processing from SQL Server Management Studio......Page 227
Scheduling OLAP Maintenance......Page 228
Summary......Page 231
Why the Need?......Page 232
Tuples and Sets......Page 234
Notation......Page 235
Tuples......Page 236
MDX Queries......Page 241
SELECT......Page 242
WHERE......Page 245
MDX Functions......Page 246
Tree Functions......Page 252
Aggregate Functions......Page 254
Time Functions......Page 258
Summary......Page 262
Business Intelligence......Page 263
Time Intelligence......Page 264
Account Intelligence......Page 266
Unary Operators......Page 267
Custom Member Formulas......Page 268
Currency Conversion......Page 269
Calculations Tab......Page 270
Calculated Measures......Page 271
Other Cube Features......Page 274
Key Performance Indicators......Page 275
Actions......Page 277
Perspectives......Page 282
Translations......Page 284
Summary......Page 285
Why Mine Data?......Page 286
Microsoft Clustering......Page 287
Creating the Accessory Buyers Marketing Campaign......Page 288
Creating the Accessory Buyers Views in AdventureWorks......Page 289
Creating the Accessory Campaign Data Source View......Page 292
Finding Accessory Buyers by Using the AdventureWorks EDW......Page 293
Using the Data Mining Model Designer......Page 300
The Mining Structure View......Page 301
The Mining Models View......Page 302
Exploring the Decision Tree......Page 303
Using Drill-Through to View a Model’s Training Cases......Page 306
Using the Dependency Network......Page 307
The Mining Accuracy Chart View......Page 308
The Mining Model Prediction View......Page 310
Use the DMX Development Environment......Page 314
Create the Accessory Buyers Mining Structure......Page 315
Process the Accessory Buyers Campaign......Page 316
View the Accessory Buyers Mining Model......Page 317
Predict Our Accessory Buyers......Page 319
Summary......Page 321
Master Data Services......Page 322
Excel Writeback......Page 324
PowerPivot from Excel......Page 331
PowerPivot with SharePoint Server 2010......Page 337
Summary......Page 342
Processing a Cube......Page 343
Processing Options......Page 345
Processing Architecture......Page 346
Performance Monitor......Page 347
Automation......Page 349
Analysis Management Objects......Page 350
Scheduling......Page 353
SQL Server Agent......Page 354
SQL Server Integration Services......Page 355
Authentication......Page 358
Authorization......Page 359
Design......Page 362
Attribute Design......Page 363
Partitions......Page 366
Understanding Aggregations......Page 369
Creating an Aggregation......Page 370
Preparing an Aggregation for Use......Page 375
Large Number of Users......Page 377
SharePoint Server 2010......Page 379
Summary......Page 381
Excel 2007......Page 382
Data Source Connections......Page 383
Pivot Tables......Page 388
Pivot Table Task Pane......Page 389
Pivot Table Ribbons......Page 390
Formatting a Pivot Table......Page 392
Formulas Using Pivot Table Values......Page 394
Pivot Charts......Page 395
Visio 2007......Page 408
Reports......Page 410
Tablix......Page 411
Charts......Page 419
Report Builder 2.0......Page 427
MOSS 2007......Page 429
KPI Lists......Page 430
PerformancePoint......Page 432
Summary......Page 438
Setting Up Adventure Works......Page 439
Web Sites......Page 443
A......Page 444
B......Page 446
C......Page 447
D......Page 449
E......Page 453
F......Page 454
J......Page 455
L......Page 456
N......Page 458
O......Page 459
P......Page 460
R......Page 462
S......Page 463
U......Page 467
W Y......Page 468
