Contents

Introduction

Part I: Introduction

Chapter 1: Introduction to Data Warehousing and SQL Server 2008 Analysis Services

A Closer Look at Data Warehousing

Key Elements of a Data Warehouse

Inmon Versus Kimball — Different Approaches

Business Intelligence Is Data Analysis

Microsoft Business Intelligence Capabilities

SQL Server Analysis Services 2008

The Unified Dimensional Model

Summary

Chapter 2: First Look at Analysis Services 2008

Differences between Analysis Services 2000, Analysis Services 2005, and Analysis Services 2008

Upgrading to Analysis Services 2008

Using Business Intelligence Development Studio

Using SQL Server Management Studio

Querying Using the MDX Query Editor

Summary

Chapter 3: Introduction to MDX

What Is MDX?

Fundamental Concepts

MDX Queries

MDX Expressions

Operators

MDX Functions

Summary

Chapter 4: Working with Data Sources and Data Source Views

Data Sources

Data Source Views

Data Source Views in Depth

Multiple Data Sources within a DSV

Summary

Chapter 5: Dimension Design

Working with the Dimension Wizard

Working with the Dimension Designer

Browsing the Dimension

Sorting Members of a Level

Optimizing Attributes

Defining Translations in Dimensions

Creating a Snowflake Dimension

Creating a Time Dimension

Creating a Parent-Child Hierarchy

Summary

Chapter 6: Cube Design

The Unified Dimensional Model

Creating a Cube Using the Cube Wizard

Cube Dimensions

Measures and Measure Groups

Calculated Members

Creating Perspectives

Creating Translations

Browsing Perspectives and Translations

Summary

Chapter 7: Administering Analysis Services

Administration Using SQL Server 2008 Tools

Online Mode

Summary

Part II: Advanced Topics

Chapter 8: Advanced Dimension Design

Custom Rollups

Enhancements to Parent-Child Hierarchies

Using Properties to Customize Dimensions

Dimension Intelligence Using the Business Intelligence Wizard

Server Time Dimension

Dimension Writeback

Summary

Chapter 9: Advanced Cube Design

Measure Groups and Measures

Adding and Enhancing Dimensions

Adding Calculations to Your Cube

Key Performance Indicators (KPIs)

Drillthrough

Actions

Adding Intelligence to the Cube

Working with Partitions

Defining Security

AMO Warnings

Summary

Chapter 10: Advanced Topics in MDX

Calculation Fundamentals

Restricting Cube Space/Slicing Cube Data

Removing Empty Cells

Filtering Members on Axes

Ranking and Sorting

Parameterize Your Queries

MDX Functions

Summary

Chapter 11: Extending MDX Using External Functions

Built-In UDFs

.NET User-Defined Functions (Stored Procedures)

COM User-Defined Functions

COM UDFs versus .NET Stored Procedures

Summary

Chapter 12: Data Writeback

Dimension Writeback

Cell Writeback

Summary

Part III: Advanced Administration and Performance Optimization

Chapter 13: Programmatic and Advanced Administration

Analysis Management Objects (AMO)

Resource and Activity Monitoring

HTTP Connectivity to Analysis Services

Analysis Services and Fail-Over Clustering

Summary

Chapter 14: Designing for Performance

Optimizing UDM Design

Optimizing for Processing

Designing Aggregations

Managing Aggregation Designs

Scalability Optimizations

Summary

Chapter 15: Analyzing and Optimizing Query Performance

The Calculation Model

Query Execution Architecture

Performance Analysis and Tuning Tools

Analyzing Query Performance Issues

Query Optimization Techniques

Scale Out with Read-Only Database

Writeback Query Performance

Summary

Part IV: Integration with Microsoft Products

Chapter 16: Data Mining

The Data Mining Process

Real-World Applications

Data Mining Algorithms in SQL Server Analysis Services 2008

Working with Mining Models

Summary

Chapter 17: Analyzing Cubes Using Microsoft Office Components

Analyzing Data in Excel 2007

ProClarity

Microsoft Performance Point Server 2007

Summary

Chapter 18: Using Data Mining with Office 2007

Configuring Your SSAS

Table Analytics

Data Mining Tools

Visio Add-In

Summary

Chapter 19: Integration Services

Creating an Integration Services Project

Creating Integration Services Packages for Analysis Services Operations

Automating Execution of SSIS Packages

Summary

Chapter 20: Reporting Services

Report Designer

Report Server

Creating a Report on a Relational Database

Creating Reports Based on a UDM

Managing Your Analysis Services Reports

Ad-Hoc Reports Using Report Builder

Summary

Part V: Scenarios

Chapter 21: Designing Real-Time Cubes

Proactive Caching

Proactive Caching at Work

Long Latency Scenario

Average Latency Scenario

No Latency Scenario

Summary

Chapter 22: Securing Your Data in Analysis Services

Securing Your Source Data

Securing Your Dimension Data

Securing Your Cube Data

Summary

Chapter 23: Inventory Scenarios

Inventory Control and Orders

Forecasting

Understanding Inventory

Summary

Chapter 24: Financial Scenarios

Presenting Budget Information

Currency Conversion Scenario (m:n)

Precision Considerations

Employee Scenario (P/C)

Custom Rollup Scenarios

Summary

Chapter 25: Web Analytics

What Is Web Analytics?

Collecting Data

Transforming Web Log Data

Step-by-Step Guide

Summary

Appendix A: MDX Functions

Index

Advertisement

Professional Microsoft® SQL Server® Analysis Services 2008 with MDX

Published by

Wiley Publishing, Inc.

10475 Crosspoint Boulevard

Indianapolis, IN 46256

www.wiley.com

Copyright © 2009 by Wiley Publishing, Inc., Indianapolis, Indiana

Published simultaneously in Canada

ISBN: 978-0-470-24798-3

Library of Congress Cataloging-in-Publication Data is available from the publisher.

No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 646-8600. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, (201) 748-6011, fax (201) 748-6008, or online at http://www.wiley.com/go/permissions.

Limit of Liability/Disclaimer of Warranty: The publisher and the author make no representations or warranties with respect to the accuracy or completeness of the contents of this work and specifically disclaim all warranties, including without limitation warranties of fitness for a particular purpose. No warranty may be created or extended by sales or promotional materials. The advice and strategies contained herein may not be suitable for every situation. This work is sold with the understanding that the publisher is not engaged in rendering legal, accounting, or other professional services. If professional assistance is required, the services of a competent professional person should be sought. Neither the publisher nor the author shall be liable for damages arising herefrom. The fact that an organization or Web site is referred to in this work as a citation and/or a potential source of further information does not mean that the author or the publisher endorses the information the organization or Web site may provide or recommendations it may make. Further, readers should be aware that Internet Web sites listed in this work may have changed or disappeared between when this work was written and when it is read.

For general information on our other products and services please contact our Customer Care Department within the United States at (877) 762-2974, outside the United States at (317) 572-3993 or fax (317) 572-4002.

Trademarks: Wiley, the Wiley logo, Wrox, the Wrox logo, Programmer to Programmer, and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates, in the United States and other countries, and may not be used without written permission. Microsoft and SQL Server are registered trademarks of Microsoft Corporation in the United States and/or other countries. All other trademarks are the property of their respective owners. Wiley Publishing, Inc. is not associated with any product or vendor mentioned in this book.

Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in electronic books.

I dedicate this book in the grandest possible manner to my dear wife, Shreepriya, who has been fully supportive and patient with me for all the late nights I worked on this book. It is also dedicated to my twins, Praveen and Divya, who have seen me work long hours on this book. I dedicate this book in memory of my father, Harinath Govindarajalu, who passed away in 1999 and who I am sure would have been proud of this great achievement, and to my mother, Sundar Bai, and my sister, Geetha Harinath. Finally, I dedicate this book in memory of my uncle, Jayakrishnan Govindarajalu, who passed away in 2007 and who was very proud of me co-authoring the first edition of this book, and was eagerly looking forward to seeing this book.

—Siva Harinath

Thanks to my wife, Wendy, for her love and patience. Love and hope to Lawrence, Loralei, and Joshua.

—Matt Carroll

To my Parents, Uncle & Aunt, Guru(s), and the Lord Almighty for molding me into who I am today.

—Sethu Meenakshisundaram

To the patience and love from Isabella and Hua-Ping.

—Denny Lee

About the Authors

Sivakumar Harinath was born in Chennai, India. Siva has a Ph.D. in Computer Science from the University of Illinois at Chicago. His thesis title was “Data Management Support for Distributed Data Mining of Large Datasets over High Speed Wide Area Networks.” Siva has worked for Newgen Software Technologies (P) Ltd., IBM Toronto Labs, Canada; National Center for Data Mining, University of Illinois at Chicago; and has been at Microsoft since February of 2002. Siva started as a Software Design Engineer in Test (SDET) in the Analysis Services Performance Team and currently is a Senior Test Lead in the Analysis Services team. Siva’s other interests include high-performance computing, distributed systems, and high-speed networking. Siva is married to Shreepriya and has twins, Praveen and Divya. His personal interests include travel, games, and sports (in particular carrom, chess, racquet ball, and board games). You can reach Siva at Sivakumar.harinath@microsoft.com.

Matt Carroll is currently a Senior Development Lead on the SQL Server Integration Services team at Microsoft. Prior to this, he spent 10 years working on the SQL Server Analysis Services team as a developer and then development lead. He’s presented on Analysis Services at VSLive and compiled and edited the whitepaper “OLAP Design Best Practices for Analysis Services 2005.”

Sethu Meenakshisundaram has more than 20 years of Enterprise System Software Development experience. Sethu spent a good portion of his career at Sybase Inc. in architecture, development, and management building world class OLTP and OLAP Database Systems. Sethu was instrumental in developing and leading highly complex clustered systems of Adaptive Server Enterprise. Early in the ‘90s, Sethu developed a version of Sybase Adaptive Server running on the Windows platform. Most recently he was an Architect in the SQL Server BI team driving technology and partner strategy. Prior to Microsoft, Sethu managed all of Server development as Senior Director at Sybase including building teams in the U.S., India, and China. He is currently a Vice President in charge of Technology Strategy at SAP Labs, USA.

Rob Zare is a program manager on the SQL Server development team. He’s worked on the product since shortly before the first service pack of SQL Server 2000. During that time, he’s focused primarily on Analysis Services, though for the next major release of SQL Server he’ll be focused on Integration Services. He is the co-author of Fast Track to MDX and regularly speaks at major technical conferences around the world.

Denny Lee is a Senior Program Manager based out of Redmond, WA in the SQLCAT Best Practices Team. He has more than 12 years experience as a developer and consultant implementing software solutions to complex OLTP and data warehousing problems. His industry experience includes accounting, human resources, automotive, retail, web analytics, telecommunications, and healthcare. He had helped create the first OLAP Services reporting application in production at Microsoft and is a co-author of “SQL Server 2000 Data Warehousing with Analysis Services” and “Transforming Healthcare through Information [Ed. Joan Ash] (2008)”. In addition to contributing to the SQLCAT Blog, SQL Server Best Practices, and SQLCAT.com, you can also review Denny’s Space (http://denster.spaces.live.com). Denny specializes in developing solutions for Enterprise Data Warehousing, Analysis Services, and Data Mining; he also has focuses in the areas of Privacy and Healthcare.

Credits

Contributors

Akshai Mirchandani

Wayne Robertson

Leah Etienne

Grant Paisley

Executive Editor

Robert Elliott

Development Editor

Kelly Talbot

Technical Editor

Ron Pihlgren

Prashant Dhingra

Production Editor

Daniel Scribner

Copy Editor

Kim Cofer

Editorial Manager

Mary Beth Wakefield

Production Manager

Tim Tate

Vice President and Executive Group Publisher

Richard Swadley

Vice President and Executive Publisher

Barry Pruett

Associate Publisher

Jim Minatel

Project Coordinator, Cover

Lynsey Stanford

Proofreader

Nancy Carrasco

Indexer

Ron Strauss

Acknowledgments

Wow!!! It has been an amazing 15 months from when we decided to partner in writing this book. The first edition of this book started when Siva jokingly mentioned to his wife the idea of writing a book on SQL Server Analysis Services 2005. She took it seriously and motivated him to start working on the idea in October 2003. Because the first edition was well received, Siva identified co-authors for the new edition. All the co-authors of this book were part of the SQL Server team when they started writing this book. As always, there are so many people who deserve mentioning that we are afraid we will miss someone. If you are among those missed, please accept our humblest apologies. We first need to thank the managers of each co-author and Kamal Hathi, Product Unit Manager of the Analysis Services team for permission to moonlight. Siva specifically thanks his manager Lon Fisher for his constant encouragement and support to help Analysis Services customers. We thank our editors, Bob Elliott and Kelly Talbot, who supported us right from the beginning but also prodded us along, which was necessary to make sure the book was published on time.

We would like to thank our technical reviewers, Ron Pihlgren and Prashant Dhingra, who graciously offered us their assistance and significantly helped in improving the content and samples in the book. We thank Akshai Mirchandani, Wayne Robertson, Leah Etienne, and Grant Paisley for their contributions in the book for Chapters 5, 6, 14, 17, and 18. We thank all our colleagues in the Analysis Services product team (including Developers, Program Managers, and Testers) who helped us in accomplishing the immense feat of writing the book on a development product. To the Analysis Services team, special thanks go to Akshai Mirchandani, T. K. Anand, Cristian Petculescu, Bogdan Crivat, Dana Cristofor, Marius Dumitru, Andrew Garbuzov, Bo Simmons, and Richard Tkachuk from the SQL Server Customer Advisory team for patiently answering our questions or providing feedback to enhance the content of the book.

Most importantly, we owe our deepest thanks to our wonderful families. Without their support and sacrifice, this book would have become one of those many projects that begins and never finishes. Our families were the ones who truly took the brunt of it and sacrificed shared leisure time, all in support of our literary pursuit. We especially want to thank them for their patience with us, and the grace it took to not kill us during some of the longer work binges.

Introduction

Analysis Services 2005 was a significant leap from Analysis Services 2000 in building your multidimensional databases right from the concept of building your cubes in Business Intelligence Development Studio to the concept of the Unified Dimensional Model with attribute and user hierarchies. The first edition of this book, Professional SQL Server Analysis Services 2005 with MDX, was aimed at novice to advanced users and was very well received by the readers. Analysis Services 2005 is a large and complex product that needed a lot of fine-tuning to get the best performance.

Analysis Services 2008 added enhancements to the Analysis Services 2005 tools that make it easy to use and build your databases right for efficient performance to significant enhancements on the server to provide improved performance. Hence, we decided to write this book to provide insight into the enhancements in Analysis Services 2008 and help you understand how to utilize them effectively for your business needs. If you have read the first edition of the book, you will find several chapters’ titles to be the same. Because Analysis Services 2008 is an incremental release, we have made enhancements to each chapter appropriately. We have enhanced the performance chapters and added a few additional scenarios that we believe will help you to understand and build multidimensional databases efficiently. This book still is targeted at novice to advanced users. If you are not familiar with SQL Server Analysis Services 2005, we highly recommend you go through the chapters in sequence to understand and use Analysis Services 2008 effectively to build, process, and deploy top-of-the-line business intelligence applications.

We are not shy about admitting to the apparent complexity of the product when faced with the user interface, which happens to be embedded in the Microsoft Visual Studio shell. This is great for you, especially if you are already familiar with the Visual Studio development environment. With this book, we want to show that not only will you overcome any possible initial shock regarding the user interface, but you will come to see it as your friend. It turns out there are many wizards to accomplish common tasks, or you can design the analytic infrastructure from the ground up — it is up to you.

This formidable yet user-friendly interface will empower you to implement business analytics of a caliber formerly reserved for academicians writing up government grant proposals or Ph.D. dissertations. More importantly, this power to turn data into information, and we mean real, usable, business-related decision-making information, can impact the bottom line of your company in terms of dollars earned and dollars saved. And that is what data warehousing, ultimately, is all about. Put another way, the purpose of all this data warehousing is simple; it is about generating actionable information from the data stores created by a company’s sales, inventory, and other data sources. In sum, it is all about decision support.

Who This Book Is For

What was the impetus for you to pick up this book? Perhaps you are passionate about extracting information from reams of raw data; or perhaps you have some very specific challenges on the job right now that you think might be amenable to a business analysis–based solution; or perhaps you have used Analysis Services 2005 and want to learn about Analysis Services 2008. Then, there is always the lure of fame and fortune. Please be aware that attaining expert status in data warehousing can lead to lucrative consulting and salaried opportunities. However, it won’t likely make you as rich as becoming a purveyor of nothing-down real estate courses. If your desire is to leave the infomercial career path to others and get really serious about data warehousing in general and business intelligence in particular, you have just the book in your hands to start or continue on your path to subject mastery.

The obvious question now is what are the prerequisites for reading and understanding the content of this book? You certainly do not have to already know the intricacies of data warehousing, you will learn that here as you go. If you have only the foggiest notion of what a relational database is; well, this book is going to challenge you at best and bury you at worst. If you are not intimidated by what you just read, this book is for you. If you have worked on data warehouses using non-Microsoft products and want to learn how Microsoft can do it better, this book is for you. If you are a database administrator, MIS Professional, or application developer interested in exploiting the power of business intelligence, this book is definitely for you!

What This Book Covers

Analysis Services 2008 is the premier multidimensional database product from Microsoft. This is the most recent of four releases from Microsoft to date. In this release, the tools and server provided have been designed for use as an enterprise-class Business Intelligence Server and we think Microsoft has been successful. Analysis Services 2008 extends on top of Analysis Services 2005 and provides you with powerful tools to design, build, test, and deploy your multidimensional databases. By integrating the tools within Visual Studio you really get the feel of building a Business Intelligence (BI) project. Similar to any application you build within VS, you build your BI projects and deploy them to an Analysis Services instance. Due to the product design that is integrated with the Visual Studio shell and enhanced features you definitely have to know how to create cubes, dimensions, and many other objects, maintain them, and support your BI users. Similar to its well-liked predecessors, Analysis Services 2008 supports the MDX language, by which you can query data. MDX is for querying multidimensional databases much like SQL is for querying relational databases. The MDX language is a component of the OLE DB for OLAP specification and is supported by other BI vendors. Microsoft’s Analysis Services 2008 provides certain extensions to the MDX supported by Analysis Services 2005 that help you to achieve best performance from your multidimensional databases.

This book walks you through the entire product and the important features of the product with the help of step-by-step instructions on building multidimensional databases. Within each chapter you will not only learn how to use the features, but also learn more about the features at a user level and what happens behind the scenes to make things work. We believe this will provide you with additional insight into how features really work and hence provide insight into how they are best exploited. It will also enhance your ability to debug problems that you might not have been able to otherwise. This behind-the-scenes view is often surfaced through exposure of XML for Analysis (XMLA), created by the product based on user interface settings. It works like this: Analysis Services 2008 uses the XMLA specification to communicate between client and server, and the Analysis Services 2008 tools communicate to the server using XMLA. Once you have designed your multidimensional database using the tools, you need to send the definition to the server. At that time the tools use XMLA to send the definitions. You will learn these definitions so that you have the ability to design a custom application that interacts with an Analysis Services instance.

MDX is the language used for data retrieval from Analysis Services. You will get an introduction to the MDX language with basic concepts and the various MDX functions in this book. When you are browsing data using Analysis Services tools, those tools send appropriate MDX to the instance of Analysis Services that contains the target data. By learning the MDX sent to the server for the various desired operations, you will begin to understand the intricacies of MDX and thereby improve your own MDX coding skills by extension. Finally, you will learn to optimize your MDX queries to get the best performance from your Analysis Services.

One of the key value-adds found in this book, which we think is worth the price of admission by itself, is that through the chapters you will begin to understand what design trade-offs are involved in BI application development. Further, the book will help you to do better BI design for your company in the face of those trade-off decisions — especially with the help of a few scenarios — and there are many scenarios discussed in this book. The scenarios are geared toward some of the common business problems that are currently faced by existing Analysis Services customers. Although there is no pretension that this book will teach you business per se, it is a book on BI and we did take the liberty of explaining certain business concepts that you are sure to run into eventually. For example, the often misunderstood concept of depreciation is explained in some detail. Again, this aspect of the book is shallow, but we hope what pure business concepts are covered will provide you with a more informed basis from which to work. If you know the concepts already, well, why not read about the ideas again? There might be some new information in there for you.

Finally, this book covers integration of Analysis Services with other SQL Server 2008 components: Data Mining, Integrations Services and Reporting Services, as well as Microsoft Office products. These chapters will help you go beyond just a passing level of understanding of Analysis Services 2008; it is really integration of these disparate components that ship in the box with SQL Server that allow you to build start to finish BI solutions that are scalable, maintainable, have good performance characteristics, and highlight the right information. Do not skip the chapters that do not at first seem crucial to understanding Analysis Services 2008 itself; it is the whole picture that brings the real value. Get that whole picture for stellar success and return on your investment of time and energy.

How This Book Is Structured

The authors of books in the Wrox Professional series attempt to make each chapter as stand-alone as possible. This book is no exception. However, owing to the sophistication of the subject matter and the manner in which certain concepts are necessarily tied to others has somewhat undermined this most noble intention. In fact, unless you are a seasoned data warehousing professional, or otherwise have experience with earlier versions of Analysis Services, it is advised you take a serial approach to reading chapters. Work through the first seven chapters in order because they will collectively provide you with some architectural context, a good first look at the product, as well as how to effectively design your cubes, an introduction to MDX, and an introduction to managing your Analysis Services server. Just to remind you, in the simplest terms, MDX is to Analysis Services what SQL is to SQL Server. Ok, that was just too simple an analogy; but let’s not get ahead of ourselves! As for the actual layout of the book, we have divided the book into roughly four major sections.

In Part I we introduce the basic concepts and then get you kick-started using Analysis Services with most of the common operations that you need to design your databases. You will become familiarized with the product if you aren’t already, and hopefully it will provide you some sense of achievement, which will certainly help motivate you to go beyond the simple stuff and move to the advanced.

Part II contains chapters that prepare you for the more advanced topics concerning the creation of multidimensional databases such as multiple measure groups, Business Intelligence wizards, Key Performance Indicators, and Actions. You will learn about the calculation model in Analysis Services 2008 and enhance your dimensions and cube designs using Business Intelligence Development Studio. Further, you will learn more about extending MDX via external functions, as well as how to effectively do data writeback in your cube.

In Part III of the book, you will learn how to administer your Analysis Services programmatically as well as designing and optimizing your cube for best performance.

In Part IV, we cover the integration of Analysis Services with other SQL Server 2008 components and Microsoft Office products that help you build solutions and provide the best support possible to your administrators and BI users. This is also the section where you will discover Data Mining and how Data Mining along with Microsoft Office 2007 makes it easy to use and effective to perform analysis on data.

Finally in Part V, we provide various scenarios from securing your data, to budgeting, to analyzing Web traffic analysis. These scenarios will help you to understand and model similar business requirements.

Together, these five sections, that is to say, this book, will provide you a full-blown BI learning experience. Because BI and BI applications constitute such an incredibly complex and massive field of endeavor, no one book can possibly cover it all. In terms of BI though the eyes of SQL Server Analysis Services 2008, we hope this book has got it covered!

We also encourage you to download and take a look at Appendix A; it is the complete MDX Reference. We thank Microsoft for providing the content for Appendix A. In the first edition of the book, Appendix A was included along with the book. Due to the Analysis Services 2008 features we have covered in this book and the additional scenarios, we have made Appendix A available for download so that the book doesn’t become too large. You can find it on this book’s page on www.wrox.com.

What You Need to Use This Book

You need a computer running some version of the Windows operating system, like Windows Vista Professional, for example, and a copy of SQL Server 2008 installed on that system. In addition you also need the SQL Server 2008 Business Intelligence samples that can be downloaded from www.codeplex.com. Please see the appropriate documentation from Microsoft for the hardware requirements needed to support the particular version of Windows you own.

Conventions

To help you get the most from the text and keep track of what’s happening, we’ve used a number of conventions throughout the book.

Boxes like this one hold important, not-to-be forgotten information that is directly relevant to the surrounding text.

Tips, hints, tricks, and asides to the current discussion are offset and placed in italics like this.

As for styles in the text:

We highlight new terms and important words when we introduce them.

We show keyboard strokes like this: Ctrl+A.

We show URLs and code within the text like so: persistence.properties.

We present code in two different ways:

In code examples we highlight new and important code with a gray background.

The gray highlighting is not used for code that's less important in the present context, or has been shown before.

Source Code

As you work through the examples in this book, you may choose either to type in all the code manually or to use the source code files that accompany the book. All of the source code used in this book is available for download at www.wrox.com. Once at the site, simply locate the book’s title (either by using the Search box or by using one of the title lists) and click the Download Code link on the book’s detail page to obtain all the source code for the book.

You’ll also want to have the databases from the SQL2008.AdventureWorks_OLTP_DB_v2008.zip and SQL2008. AdventureWorks_DW_BI_v2008.zip files installed. These databases are not installed with SQL Server 2008 by default. The AdventureWorks DW files (along with the other SQL Server database files) can be downloaded from www.wrox.com/go/SQLServer2008RTMDataSets. Download and install the SQL Server 2008 Adventure Works DW 2008 sample database for your machine’s architecture. For example, if you have an x64 machine, the sample database to install is: SQL2008.AdventureWorks_DW_BI_v2008.x64.msi.

Because many books have similar titles, you may find it easiest to search by ISBN; for this book the ISBN is 978-0-470-24798-3.

Once you download the code, just decompress it with your favorite compression tool. Alternatively, you can go to the main Wrox code download page at www.wrox.com/dynamic/books/download.aspx to see the code available for this book and all other Wrox books.

Errata

We make every effort to ensure that there are no errors in the text or in the code. However, no one is perfect, and mistakes do occur. If you find an error in one of our books, like a spelling mistake or faulty piece of code, we would be very grateful for your feedback. By sending in errata you may save another reader hours of frustration and at the same time you will be helping us provide even higher quality information.

To find the errata page for this book, go to www.wrox.com and locate the title using the Search box or one of the title lists. Then, on the book details page, click the Book Errata link. On this page you can view all errata that has been submitted for this book and posted by Wrox editors. A complete book list including links to each book’s errata is also available at www.wrox.com/misc-pages/booklist.shtml.

If you don’t spot “your” error on the Book Errata page, go to www.wrox.com/contact/techsupport.shtml and complete the form there to send us the error you have found. We’ll check the information and, if appropriate, post a message to the book’s errata page and fix the problem in subsequent editions of the book.

p2p.wrox.com

For author and peer discussion, join the P2P forums at p2p.wrox.com. The forums are a Web-based system for you to post messages relating to Wrox books and related technologies and interact with other readers and technology users. The forums offer a subscription feature to e-mail you topics of interest of your choosing when new posts are made to the forums. Wrox authors, editors, other industry experts, and your fellow readers are present on these forums.

At http://p2p.wrox.com you will find a number of different forums that will help you not only as you read this book, but also as you develop your own applications. To join the forums, just follow these steps:

1. Go to p2p.wrox.com and click the Register link.

2. Read the terms of use and click Agree.

3. Complete the required information to join as well as any optional information you wish to provide and click Submit.

4. You will receive an e-mail with information describing how to verify your account and complete the joining process.

You can read messages in the forums without joining P2P but in order to post your own messages, you must join.

Once you join, you can post new messages and respond to messages other users post. You can read messages at any time on the Web. If you would like to have new messages from a particular forum e-mailed to you, click the Subscribe to this Forum icon by the forum name in the forum listing.

For more information about how to use the Wrox P2P, be sure to read the P2P FAQs for answers to questions about how the forum software works as well as many common questions specific to P2P and Wrox books. To read the FAQs, click the FAQ link on any P2P page.

Part I

Introduction

Chapter 1: Introduction to Data Warehousing and SQL Server 2008 Analysis Services

Chapter 2: First Look at Analysis Services 2008

Chapter 3: Introduction to MDX

Chapter 4: Working with Data Sources and Data Source Views

Chapter 5: Dimension Design

Chapter 6: Cube Design

Chapter 7: Administering Analysis Services

1

Introduction to Data Warehousing and SQL Server 2008 Analysis Services

Business intelligence (BI) helps enterprises to gain insight from historical data and formulate strategic initiatives for the future. The historical data are stored as an electronic repository, which is called a data warehouse. A data warehouse is a system of records (a business intelligence gathering system) that takes data from a company’s operational databases and other data sources and transforms it into a structure conducive to business analysis. Business calculations are often performed on the organized data to further its usefulness for making business decisions. Finally, the data is made available to the end user for querying, reporting, and analysis. A data warehouse system that is cleansed, is organized, and has optimized storage of historical records gives the business an intelligence gathering system to understand the business dynamics. Business analysis can be done in reactive mode or predictive mode. Reactive mode business analysis (also known as business analytics) is a function where information workers, business analysts, and other business users investigate the system of records and identify patterns and trends, and make business decisions to improve their business processes. Predictive mode analysis (also known as predictive analytics or data mining) is done using mathematical models to predict future trends on the system of records. The general approach to storing business data in a dimensional model and providing quick answers by slicing and dicing the business data is known as On Line Analytical Processing (OLAP). OLAP systems are architected in different ways. The most common types are MOLAP (Multidimensional OLAP), ROLAP (Relational OLAP), and HOLAP (Hybrid OLAP). SQL Server 2008 is a business intelligence platform that provides a scalable infrastructure with server (Analysis Services and Reporting Services) and tools (Integration Services and Reporting Services) to extract, transform, load, build, query, and report data warehouse solutions. Now that you have the big picture of data warehousing, take a look at what you learn in this chapter.

In this chapter you learn what data warehousing really is and how it relates to business intelligence. This information comes wrapped in a whole load of new concepts, and you get a look at the best known approaches to warehousing with the introduction of those concepts. We explain data warehousing in several different ways and we are sure you will understand it. You will finally see how SQL Server 2008 Analysis Services (SSAS 2008) puts it all together in terms of architecture — at both client and server levels.

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!