3 of 3 people found the following review helpful:
5.0 out of 5 stars
Take your MDX skills to the next level, October 25, 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...
Read more ›
Help other customers find the most helpful reviews
Was this review helpful to you? Yes
No
1 of 1 people found the following review helpful:
5.0 out of 5 stars
Must have for all MDX developers, November 15, 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.
Help other customers find the most helpful reviews
Was this review helpful to you? Yes
No
1 of 1 people found the following review helpful:
5.0 out of 5 stars
Practical and Concise, October 20, 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!
Help other customers find the most helpful reviews
Was this review helpful to you? Yes
No
