Learn more Download now Shop now Shop now Shop now Shop now Shop now Shop now Shop now Shop now Learn More Shop now Learn more Shop Fire Shop Kindle Learn More Shop now Shop now Learn more

Customer reviews

5.0 out of 5 stars
MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook
Format: Paperback|Change
Price:£30.99+ Free shipping with Amazon Prime

on 26 October 2011
There are not many MDX books available, so when a new one arrives on the market, I definitely want to take a look. Now I have taught MDX classes for many years, and I think perhaps my first class was in 2002. However, it's been so long now, I don't recall exactly when that first class was. What I do know is that my understanding of MDX and my fluency with the language has grown by leaps and bounds since then through experience with all kinds of bleeding edge MDX, and I have tried to communicate some of my insights developed along the way to students in my classroom. MDX concepts can stretch your thinking (to put it mildly), and more so if you have to unlearn SQL concepts as well, which is true of the majority of my students. As a native English speaker, I can find it challenging at times to explain certain aspects of MDX. For all these reasons, I can fully appreciate Tomislav's efforts to create this book and commend him for his excellent examples and explanations using a language that is not his mother tongue.

Tomislav's book is definitely not for beginners. There is an underlying assumption that you already have some familiarity with the basics of MDX. The purpose of this book is to provide you with the tools necessary to continue building your skills. The chapters group together a series of related concepts, called recipes. You don't need to read the book sequentially from cover to cover. Instead, you search for the desired outcome, such as handling division by zero errors (described in Chapter 1) or calculating row numbers (found in Chapter 7). Ideally, you don't just read the recipe, but actually try out the steps yourself. I did some of my reading of this book when I was away from my laptop, and frequently found that I wanted to try out something that Tomislav mentioned. Even long-time MDX developers like myself can find interesting tidbits of information to round out their repertoire!

Each concept within a chapter is presented in a similar manner, beginning with "Getting ready" which are the steps you need to perform to set up your query. For example, you might need to work in Management Studio and set up a query as a starting point or open the Script View for a cube in Business Intelligence Development Studio. Then the next section is "How to do it..." which provides step-by-step instructions for working with a query or MDX script to accomplish the intended outcome. Then Tomislav continues with the "How it works..." section which provides background information on the key concepts for the current recipe. This section is the real meat of the book, but is nicely separated from the implementation steps if you want to jump straight to the technical details. Depending on the recipe, Tomislav also includes additional sections, such as "There's more..." to provide alternative solutions or to point you to helpful links on the Internet such as whitepapers and blog articles providing more insight, and "See also..." to cross-reference you to another recipe in the book that covers a closely related concept. Where applicable, he also includes information about how the techniques in the recipe behave differently in earlier versions of Analysis Services.

Chapter 1: Elementary MDX Techniques

Don't let the word Elementary fool you into thinking you can learn entry-level MDX from this chapter. As I mentioned earlier, this book is not for beginners. There are good techniques here and a few basics, such as a great explanation of the FORMAT_STRING property and troubleshooting its use. However, I would consider the majority of these techniques to be elementary only as compared to the other techniques found later in the book. For example, the WHERE clause is one of those things that I see people really get into a tangle over (because they can't forget their T-SQL), and Tomislav demonstrates using it to implement a logical OR on members from different hierarchies and a logical AND for members from the same hierarchy. One of my favorite sections in this chapter is the coverage of alternatives for the FILTER function, which can cause performance problems. Use this chapter to get grounded with some foundational concepts, then strap on your seatbelt before diving into the rest of the book!

Chapter 2: Working with Time

Time is something that every cube has. Or at least every cube that I've ever met. I can't imagine a cube without one (even if it's called Date) because often business analysis is comparing one period to another or monitoring trends over time. Tomislav starts with the basics of the YTD function but delves into variations on the theme and points out pitfalls to avoid. He moves on to parallel periods, moving averages, and finding last dates with data, among other time-related topics. A useful recipe in this chapter is the use of string functions to calculate a date, as I see this requirement a lot when working with Reporting Services reports that use Analysis Services as a source. A good case for working with a single-member named set rather than a calculated member is also made in this chapter.

Chapter 3: Concise Reporting

A report in this chapter means a pivot table used in some front-end tool for Analysis Services, and not Reporting Services exclusively. The goal of this chapter is to reduce the size of the pivot table, and thereby improve performance. I would characterize this chapter as one that helps you find the best or the worst members in a group, whether in a hierarchy, among siblings, or among descendants. Tomislav starts off the chapter with a recipe to get the top N members. In this recipe, Tomislav includes a great explanation of what can go wrong when you use the TopCount function. Well, it's not a matter of it behaving incorrectly because it's doing what you ask. The problem is that many people misunderstand how the TopCount function behaves under certain conditions and Tomislav delves deeply into the behavior here. He then builds on these ideas throughout the chapter and introduces alternatives for finding and displaying the best and the worst.

Chapter 4: Navigation

Hierarchies in a dimension are extremely useful for a number of reasons, one of which is navigation. The chapter begins with some simple queries that use Boolean logic to test the context of a current member on the row axis, and then expands to use scoping in the MDX script or use a query (using CELL CALCULATION) to determine if members are in the same branch of a hierarchy. Are you confused about when to use the Exists function and the EXISTING keyword? Tomislav covers them both in this chapter in a variety of contexts. Also, having advocated on behalf of a named set in a previous chapter, Tomislav explores the pros and cons of named sets more fully in this chapter.

Chapter 5: Business Analytics

This chapter covers several techniques that are encountered less frequently (depending on who you ask, I suppose) than those covered up to this point in the book. For example, the chapter begins with linear regression which I've never had to use in 10 years of writing MDX. But I said the same about the Correlation function once upon a time and I now use it frequently in a current project, so my feeling is that you never know when you'll need to use a seemingly obscure function. Because these analytical functions are used less commonly, the amount of information available through Books Online or elsewhere on the Internet is pretty slim. Therefore, having this chapter's working examples at your fingertips is invaluable. Also covered in this chapter is adjusting forecasts based on periodic cycles, alternative approaches to expense allocations, finding slow-moving inventory items, categorizing customers, and ABC analysis (which is an application of Pareto analysis).

Chapter 6: When MDX is Not Enough

In this chapter, Tomislav makes the case that when an MDX approach gets overly complicated, it's time to look at making changes to the dimension or cube design. For example, he says, "Every time you catch yourself using functions like Except(), Filter(), or similar too often in your calculations, you should step back and consider whether that's a repeating behavior and whether it would pay off to have an attribute to separate the data you've been separating using MDX calculations." He also explains how and why to create a placeholder measure in the cube to use with assignments in the MDX script. Utility dimensions for unit conversion or for time-based calculations are also covered in this chapter.

Chapter 7: Context-aware Calculations

Understanding context is an important aspect of MDX development. As Tomislav explains in the introduction to this chapter, context can be unpredictable based on what a user might select to place on rows and columns, or it can be partially known when you expect a particular measure or hierarchy to be used, or it can be completely known. The trick is to produce a calculation that behaves correctly regardless of context, which can be made trickier based on a combination of factors that Tomislav describes. The recipes in this chapter help you explore context from a number of, um, contexts, starting with how to know how many columns and rows will be in a query's result set, how to determine which axis contains measures, how to determine what has been placed on an axis, among other useful techniques.

Chapter 8: Advanced MDX Topics

Now frankly I considered several of the recipes up to this point to be advanced, so I had to chuckle at the title of this chapter. Let's just say these recipes are more complex! In this chapter, you'll find techniques for working with parent-child hierarchies and displaying random values for sampling purposes. Hopefully, you're avoiding the use of parent-child hierarchies and random sampling is not a common request in reports, so this section of the chapter is interesting primarily from an academic viewpoint. But then we move to complex sorts - a very useful subject indeed. Tomislav provides several examples and highlights potential problem areas. Also in this chapter is a recipe for recursively calculating cumulative values.

Chapter 9: On the Edge

Tomislav uses this chapter to collect topics that don't neatly fit into the earlier chapters. Here he covers Analysis Services stored procedures (which are nothing like T-SQL stored procedures, by the way), as well as using the OPENQUERY and OPENROWSET functions for calling MDX from a T-SQL statement. He also introduces Dynamic Management Views (DMVs) for documenting and monitoring cubes, and shows how to use SQL Server Profiler to capture MDX queries. Last, he shows how to use the DRILLTHROUGH command.

If you're an MDX developer, whether brand new or experienced, you will find lots of good information in this book and practical examples of how and why to implement specific techniques. I definitely recommend that you add it to your library, in whatever format you prefer.
0Comment| 2 people found this helpful. Was this review helpful to you? Report abuse
on 28 October 2011
MDX is the query language for multidimensional cubes. Many BI practitioners perceive MDX to have a high learning curve probably not that much for the language itself but for the multidimensional concepts you need to master before you can get something out of it. When interacting with the community, I am frequently asked to recommend a MDX book. So far, my recommendations have been Microsoft SQL Server 2008 MDX Step by Step by Brian Smith at el for beginners and MDX Solutions by George Spofford at el for more advanced users.

I will add MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook by Tomislav Piasevoli (MVP - SQL Server) to my recommendation list. This book takes an interesting approach that will be particularly appealing to readers who have already mastered the basics. As its name suggests, the book is a collection of recipes that you can quickly refer to when you tackle a specific MDX requirement. For example, suppose you need to obtain the last date with data in the cube. You flip to Chapter 2: Working with Time and you find a section Getting Values on The Last Date With Data. In it, the author provides the necessary background to explain the concept and provides a 9-step recipe for implementing it. Besides its practical and concise style, another thing that I liked about this book is that it targets the Adventure Works cube so you don't have to spend time installing and learning other databases. You can hit the ground running by just copying and executing the query.

All in all, this is great book to add to your repertoire of Analysis Services books. The author is an industry-recognized expert who has many years of experience in developing BI solutions with Analysis Services and this becomes evident quickly. Get the recipes!
0Comment| One person found this helpful. Was this review helpful to you? Report abuse
on 15 November 2011
I am always trying to keep my MDX and Analysis Services skills up-to-date and so I am always looking for new books that cover those topics. Recently I came across Tomislav Piasevoli's new "MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook". When I read the first chapters I realized very fast, that book has great potential for all kinds SSAS/MDX developer. For beginners the examples provided are easy to follow and very well described and the fact that he focused on real world scenarios makes the entry into the multidimensional world very intuitive. Beginners will have many quick wins as they can simply copy the examples and adopt them to their cube which I think is the best way to start with MDX. Advanced developers will like the additional information he provides for all examples. He describes pros and cons of several implementations and used techniques especially in terms of performance and usability. The best practices he provides thereby are also good guidelines for beginners and will prevent them from making very common mistakes every developer had to struggle with in the beginning. Even experts that have been working with MDX and Analysis for several years now and consider themselves to have very good skills in both domains, I think will still benefit from reading this book. It is always interesting to see, how other people solve common business requirements to broaden one's mind. If you are developing BI solutions every day it is very likely that you stop thinking about your own solution in detail very fast and will be implementing the same solution over and over again even though there may be better solutions out there. Tomislav highlights many of those solutions and shares them with this book.

In general the book is very well structured. The first chapters start with basic calculations and the further you read the more advanced the topics get. Especially in the last chapters he also discusses not so common scenarios that at first sight seem very far-fetched but all of them may be relevant for your some time. Those are things like Context-aware Calculations, how to make changes to your cube to allow better querying and also SSAS's Dynamic Management Views (DMVs). Even if you may have not needed them yet, it is always good to know where to find them.

Throughout the book all topics are handled in the same way which makes it very easy to read. He starts with the business problem and describes it in detail. Afterwards he discusses several possible ways to solve it finally explains details why he has chosen a particular solution over another. In the "There's more ..."-section of each example he provides further information on the used techniques that allow you to dig into the topic very deep.

Overall I have to say that is a very good book and it is worth a buy for every one that is working with MDX and Analysis Services.
0Comment| One person found this helpful. Was this review helpful to you? Report abuse
on 3 October 2011
Completed the entire book and am really pleased with this book. I would say that it is a must have for people who have already started using MDX.
Walking back the memory lane, I always yearned for a SSAS book that had examples of common design and implementation issues and was overjoyed to find the same in Expert Cube Development with Microsoft SQL Server 2008 Analysis Services. Once I went through this book completely, I tried to find a similar one for MDX too but in vain (found one for SSRS in the process - Microsoft SQL Server Reporting Services Recipes for Designing Expert Reports). Well, I am glad to say that the search has finally ended with the advent of the MDX cookbook by Tomislav. The book follows a simple and easy to understand structure throughout the 9 chapters :

- Getting ready :- getting the pre-requisite queries ready for the task
- How to do it :- how to do the task
- How it works :- how the task works
- There's more :- alternate ways and more information on the task
- See also :- links to related information and tasks

Once I got familiar with the format, I was just reading the `Getting ready' part and trying to solve the task on my own. After that, I would compare my answers with the next 2 sections and it enriched my reading experience. Though the initial chapters were relatively easy, I was scratching my head as the chapters neared the end and I have learned a lot of new things through this book. This is not really intended to be your first MDX book but is a MUST HAVE once you have tasted a bit of MDX. All you MDX developers, you are definitely missing out on a great book if you haven't read this.
0Comment|Was this review helpful to you? Report abuse
on 10 October 2011
I think my friend Tomislav did a great job on this book and it would make a valuable addition to the bookshelf of anyone that is working with MDX.

I really enjoyed reading this and there were even a couple of interesting techniques that I have added to my toolkit. As far as I know there are not any other MDX books on the market quite like this one. It's more aimed at the intermediate level of MDX user and assumes that you have some concept of things like members, tuples and sets. You will probably get the most out of it if you have had at least a little experience writing your own MDX expressions.

The fact that it's written in a cook book style makes it very practical. You can scan down the table of contents if you want and just pick out individual recipes. Or you can read it from cover to cover as the recipes start simple and gradually increase in complexity. The individual recipes stand alone, but they also have links to other related recipes. Although the book makes frequent references to SSAS 2008 R2 the vast majority of the recipes will work just fine with the 2005 and 2008 versions (and for Denali when it's released).

Finally I was very surprised to find that the image on the front cover is one of my home town Melbourne, Australia. So if you live in Melbourne and are into MDX you definitely should buy yourself a copy of this book.
0Comment|Was this review helpful to you? Report abuse
on 11 October 2011
Firstly, it is a cookbook - Tomislav has written recipes for a large amount of real-world scenarios. The topics are very well covered and follow a pattern of: Getting Ready -> How to do it -> How it works -> There's more -> See also. First, we get an introduction, and then we prepare to get the work done. After that we get a step-by-step walkthrough for the actual solution. I liked the next section "How it works". Here we get an explanation of why we get the result letting us get some more insight rather than blindly typing code. I find it a very nice touch and I applaud the author for spending the time to include this invaluable bit of information. The "There's more" section after that expands a bit the topic, trying different options and showing what happens when we apply them. In the end of each chapter we have a section showing which other topics are related to the one we are currently reading. All in all, I think that Tomislav's cookbook is very, very, well written.
0Comment|Was this review helpful to you? Report abuse

Customers also viewed these items

Need customer service? Click here