Expert Cube Development with SSAS Multidimensional Models (2014)

Preface

Microsoft SQL Server Analysis Services ("Analysis Services" from here on) is now 15 years old, and a mature product proven in thousands of enterprise-level deployments around the world. Starting from a point where few people knew that Analysis Services existed and those who knew about it 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 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, online forums, and books galore on the subject. So, why should we 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 growing to its maturity and at the same time seen more and more people moving 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 of them addressed the practical problems that 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 of the 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 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 for many years.

A very important point to make is that this book only covers Analysis Services 2012 Multidimensional Models. As you may know, as of SQL Server 2012, there are two versions of Analysis Services: Multidimensional and Tabular. Although both of them are called Analysis Services and can be used for much the same purposes, the development experience for the two is completely different. If you are working on a Microsoft Business Intelligence project, it is very likely that you will be using either one or the other, not both, so it makes no sense to try and cover both Multidimensional and Tabular in the same book.

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.

Another 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 that we say. We're not going to pretend that all of the 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 or her 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 percent of what you learn is new, it might be the crucial piece of knowledge that makes the difference between the success and failure of 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 to avoid having an "If only I'd known about this earlier!" moment yourself, by passing on the knowledge that we've learned the hard way. We also hope that you will enjoy reading it and that you're successful in whatever you're trying to achieve with Analysis Services.

What this book covers

Chapter 1, Designing the Data Warehouse for Analysis Services, shows you how to design a relational data mart to act as a source for Analysis Services.

Chapter 2, Building Basic Dimensions and Cubes, covers setting up a new project in SQL Server Data Tools and building simple dimensions and cubes.

Chapter 3, Designing More Complex Dimensions, discusses more complex dimension design problems such as slowly changing dimensions and ragged hierarchies.

Chapter 4, Measures and Measure Groups, looks at the measures and measure groups, how to control the measures that aggregate up, and how dimensions can be related to the measure groups.

Chapter 5, Handling Transactional-Level Data, looks at issues such as drillthrough, fact dimensions, and many-to-many relationships.

Chapter 6, Adding Calculations to the Cube, shows how to add calculations to a cube, and gives some examples of how to implement common calculations in MDX.

Chapter 7, Adding Currency Conversion, deals with the various ways in which we can implement currency conversion in a cube.

Chapter 8, Query Performance Tuning, covers query performance tuning, including how to design aggregations and partitions and how to write efficient MDX.

Chapter 9, Securing the Cube, looks at the various ways in which we can implement security, including cell security and dimension security, as well as dynamic security.

Chapter 10, Going in Production, looks at some of the common issues that we'll face when a cube is in production, including how to deploy changes and how to automate partition management and processing.

Chapter 11, Monitoring Cube Performance and Usage, discusses how we can monitor query performance, processing performance, and usage once the cube has gone into production.

Appendix, DAX Query Support, discusses support for DAX queries and Power View against Multidimensional Models.

What you need for this book

To follow the examples used in this book, we recommend that you have a PC with the following software installed on it:

· Microsoft Windows Vista SP2 or greater for desktops

· Microsoft Windows Server 2008 SP2 or greater for servers

· Microsoft SQL Server Analysis Services 2012 Multidimensional

· Microsoft SQL Server 2012 (the relational engine)

· Microsoft Visual Studio 2012 and SQL Server Data Tools

· SQL Server Management Studio

· Excel 2007, 2010, or 2013 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 used in this book. We'll discuss the differences between Developer Edition, Standard Edition, BI Edition, and Enterprise Edition in Chapter 2, Building Basic Dimensions and Cubes; some of the functionalities that we'll cover are not available in the Standard Edition and we'll mention that fact whenever it's relevant.

Who this book is for

This book is aimed at Business Intelligence consultants and developers who work with Analysis Services Multidimensional Models on a daily basis, who already know the basics of building a cube, 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 SQL Server Data Tools. 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 modeling scenarios that you're never likely to encounter. We're not going to cover all of 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 that you can copy and adapt yourselves, but not try to explain how the language works.

Conventions

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: "DimGeography is not used to create a new dimension, but is being used to add geographic attributes to the Customer dimension."

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

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: "You can set project properties by right-clicking on the Project node in the Solution Explorer pane and selecting Properties."

Note

Warnings or important notes appear in a box like this.

Tip

Tips and tricks appear like this.