MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook
Use the Amazon App to scan ISBNs and compare prices.
Enter your mobile number or email address below and we'll send you a link to download the free Kindle App. Then you can start reading Kindle books on your smartphone, tablet, or computer - no Kindle device required.
To get the free app, enter your mobile phone number.
About the Author
Tomislav Piasevoli is a Business Intelligence Specialist with years of experience with SQL Server Analysis Services. He lives in Croatia where he has successfully implemented many still-in-use BI solutions and now specializes in dimensional modeling, cube design and MDX consulting worldwide. Tomislav has been honored with the Microsoft's MVP award three times for his contribution to the SSAS community. Besides solving MDX, cube design and dimensional modeling puzzles, he presents at conferences, writes articles for magazines, and maintains his blog at tomislav.piasevoli.com.
- Publisher : Packt Publishing (August 9, 2011)
- Language : English
- Paperback : 480 pages
- ISBN-10 : 1849681309
- ISBN-13 : 978-1849681308
- Item Weight : 1.8 pounds
- Dimensions : 7.5 x 1.09 x 9.25 inches
- Best Sellers Rank: #5,322,352 in Books (See Top 100 in Books)
- Customer Reviews:
Top reviews from the United States
There was a problem filtering reviews right now. Please try again later.
The book helps me advanced my MDX skill a lot in a short time. Highly recommend to all MDX developers.
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.
(A) p. 100, "Calculating the difference between two dates"(using DateDiff)
(B) p. 134, "Combining Two Hierarchies into One".
(C) p. 366, "Performing Complex Sorts". Neat and well-described!
Whereas the industry-standard book 'MDX Solutions, 2nd Ed' text covers some similar concepts, this new book will earn it's place on my desk, wide-open, full of highlights and scribbles, due to it's intuitive 'cookbook' approach with each section being a plain-english business problem and complete code solution format. I appreciate the author's choice of an unmodified 'AdventureWorks DW 2008 R2' for his samples. It's good to have an advanced MDX text updated for Analysis Services 2008 R2, and the list of Tier 1 Analysis Services people (Galloway, Gosbell, Puri, Russo, Webb) as reviewers adds to the confidence.
I plan to revisit this review and add more detail as I go through the book, but my initial impression is that it can help many SSAS developers and/or cube-based report developers distinghish themselves by making substantial advances with MDX and thereby taking their OLAP solutions to new heights. As such, this book is overdue. Good reading!