Microsoft SQL Server Analysis Services ("Analysis Services" from here on) is now ten years old, a mature product proven in thousands of enterprise-level deployments around the world. Starting from a point where few people knew it existed and where those that did were often suspicious of it, it has grown to be the most widely deployed OLAP server and one of the keystones of Microsoft's Business Intelligence (BI) product strategy. Part of the reason for its success has been the easy availability of information about it: apart from the documentation Microsoft provides there are white papers, blogs, newsgroups, online forums, and books galore on the subject. So why write yet another book on Analysis Services? The short answer is to bring together all of the practical, real-world knowledge about Analysis Services that's out there into one place.
We, the authors of this book, are consultants who have spent the last few years of our professional lives designing and building solutions based on the Microsoft Business Intelligence platform and helping other people to do so. We've watched Analysis Services grow to maturity and at the same time seen more and more people move from being hesitant beginners on their first project to confident cube designers, but at the same time we felt that there were no books on the market aimed at this emerging group of intermediate-to-experienced users. Similarly, all of the Analysis Services books we read concerned themselves with describing its functionality and what you could potentially do with it but none addressed the practical problems we encountered day-to-day in our work—the problems of how you should go about designing cubes, what the best practices for doing so are, which areas of functionality work well and which don't, and so on. We wanted to write this book to fill these two gaps, and to allow us to share our hard-won experience. Most technical books are published to coincide with the release of a new version of a product and so are written using beta software, before the author has had a chance to use the new version in a real project. This book, on the other hand, has been written with the benefit of having used Analysis Services 2008 for almost a year and before that Analysis Services 2005 for more than three years.
The approach we've taken with this book is to follow the lifecycle of building an Analysis Services solution from start to finish. As we've said already this does not take the form of a basic tutorial, it is more of a guided tour through the process with an informed commentary telling you what to do, what not to do and what to look out for.
Chapter 1 shows how to design a relational data mart to act as a source for Analysis Services.
Chapter 2 covers setting up a new project in BI Development Studio and building simple dimensions and cubes.
Chapter 3 discusses more complex dimension design problems such as slowly changing dimensions and ragged hierarchies.
Chapter 4 looks at measures and measure groups, how to control how measures aggregate up, and how dimensions can be related to measure groups.
Chapter 5 looks at issues such as drillthrough, fact dimensions and many-to-many relationships.
Chapter 6 shows how to add calculations to a cube, and gives some examples of how to implement common calculations in MDX.
Chapter 7 deals with the various ways we can implement currency conversion in a cube.
Chapter 8 covers query performance tuning, including how to design aggregations and partitions and how to write efficient MDX.
Chapter 9 looks at the various ways we can implement security, including cell security and dimension security, as well as dynamic security.
Chapter 10 looks at some common issues we'll face when a cube is in production, including how to deploy changes, and how to automate partition management and processing.
Chapter 11 discusses how we can monitor query performance, processing performance and usage once the cube has gone into production.
To follow the examples in this book we recommend that you have a PC with the following installed on it:
Microsoft Windows Vista, Microsoft Windows XP
Microsoft Windows Server 2003 or Microsoft Windows Server 2008
Microsoft SQL Server Analysis Services 2008
Microsoft SQL Server 2008 (the relational engine)
Microsoft Visual Studio 2008 and BI Development Studio
SQL Server Management Studio
Excel 2007 is an optional bonus as an alternative method of querying the cube
We recommend that you use SQL Server Developer Edition to follow the examples in this book. We'll discuss the differences between Developer Edition, Standard Edition and Enterprise Edition in chapter 2; some of the functionality we'll cover is not available in Standard Edition and we'll mention that fact whenever it's relevant.
This book is aimed at Business Intelligence consultants and developers who work with Analysis Services on a daily basis, who know the basics of building a cube already and who want to gain a deeper practical knowledge of the product and perhaps check that they aren't doing anything badly wrong at the moment.
It's not a book for absolute beginners and we're going to assume that you understand basic Analysis Services concepts such as what a cube and a dimension is, and that you're not interested in reading yet another walkthrough of the various wizards in BI Development Studio. Equally it's not an advanced book and we're not going to try to dazzle you with our knowledge of obscure properties or complex data modelling scenarios that you're never likely to encounter. We're not going to cover all the functionality available in Analysis Services either, and in the case of MDX, where a full treatment of the subject requires a book on its own, we're going to give some examples of code you can copy and adapt yourselves, but not try to explain how the language works.
One important point must be made before we continue and it is that in this book we're going to be expressing some strong opinions. We're going to tell you how we like to design cubes based on what we've found to work for us over the years, and you may not agree with some of the things we say. We're not going to pretend that all advice that differs from our own is necessarily wrong, though: best practices are often subjective and one of the advantages of a book with multiple authors is that you not only get the benefit of more than one person's experience but also that each author's opinions have already been moderated by his co-authors.
Think of this book as a written version of the kind of discussion you might have with someone at a user group meeting or a conference, where you pick up hints and tips from your peers: some of the information may not be relevant to what you do, some of it you may dismiss, but even if only 10% of what you learn is new it might be the crucial piece of knowledge that makes the difference between success and failure on your project.
Analysis Services is very easy to use—some would say too easy. It's possible to get something up and running very quickly and as a result it's an all too common occurrence that a cube gets put into production and subsequently shows itself to have problems that can't be fixed without a complete redesign. We hope that this book helps you avoid having one of these "If only I'd known about this earlier!" moments yourself, by passing on knowledge that we've learned the hard way. We also hope that you enjoy reading it and that you're successful in whatever you're trying to achieve with Analysis Services.
In this book, you will find a number of styles of text that distinguish between different kinds of information. Here are some examples of these styles, and an explanation of their meaning.
Code words in text are shown as follows: "We can include other contexts through the use of the include directive."
A block of code will be set as follows:
CASE WHEN Weight IS NULL OR Weight<0 THEN 'N/A' WHEN Weight<10 THEN '0-10Kg' WHEN Weight<20 THEN '10-20Kg' ELSE '20Kg or more' END
When we wish to draw your attention to a particular part of a code block, the relevant lines or items will be shown in bold:
SCOPE([Measures].[Sales Amount]);
THIS = TAIL(
NONEMPTY(
{EXISTING [Date].[Date].[Date].MEMBERS}
* [Measures].[Sales Amount])
,1).ITEM(0);
END SCOPE;New terms and important words are shown in bold. Words that you see on the screen, in menus or dialog boxes for example, appear in our text like this: "clicking the Next button moves you to the next screen".
Feedback from our readers is always welcome. Let us know what you think about this book—what you liked or may have disliked. Reader feedback is important for us to develop titles that you really get the most out of.
To send us general feedback, simply drop an email to <[email protected]>, and mention the book title in the subject of your message.
If there is a book that you need and would like to see us publish, please send us a note in the SUGGEST A TITLE form on www.packtpub.com or email <[email protected]>.
If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide on www.packtpub.com/authors.
Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.
Visit http://www.packtpub.com/files/code/7221_Code.zip to directly download the example code and database.
The downloadable files contain instructions on how to use them.
All of the examples in this book use a sample database based on the Adventure Works sample that Microsoft provides, and which can be downloaded from http://tinyurl.com/SQLServerSamples. We use the same relational data source data to start but then make changes as and when required for building our cubes, and although the cube we build as the book progresses resembles the official Adventure Works cube it differs in several important respects so we encourage you to download and install it.
Although we have taken every care to ensure the accuracy of our contents, mistakes do happen. If you find a mistake in one of our books—maybe a mistake in text or code—we would be grateful if you would report this to us. By doing so, you can save other readers from frustration, and help us to improve subsequent versions of this book. If you find any errata, please report them by visiting http://www.packtpub.com/support, selecting your book, clicking on the let us know link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata added to any list of existing errata. Any existing errata can be viewed by selecting your title from http://www.packtpub.com/support.
Piracy of copyright material on the Internet is an ongoing problem across all media. At Packt, we take the protection of our copyright and licenses very seriously. If you come across any illegal copies of our works in any form on the Internet, please provide us with the location address or website name immediately so that we can pursue a remedy.
Please contact us at <[email protected]> with a link to the suspected pirated material.
We appreciate your help in protecting our authors, and our ability to bring you valuable content.
You can contact us at <[email protected]> if you are having a problem with any aspect of the book, and we will do our best to address it.