MDX-related books often dedicate a significant part of their content to explaining the concepts of multidimensional cubes, the MDX language and its functions, and other specifics related to working with Analysis Services. And that's perfectly fine, there should be books like that, the tutorials that teach the concepts. However, that also means that when it comes to examples, there's usually not enough space to provide all the details about them and their variations, otherwise the book would become huge or oftentimes lose its focus. The result of that is that making a step further from the provided calculations and queries might not be an easy task for an average reader. The other problem with tutorials is that the solution to a particular problem might be scattered throughout the book, which is where the cookbook style of books like this one come into play. Similar to data warehouses where we consolidate many tables of the relational database into a few and then organize those dimension tables in a topic-based star schema, in cookbooks we aggregate the information about a particular problem in form of one or more recipes and present that topic-based knowledge in full detail. Both the relational databases and the data warehouses have their purpose; it's not uncommon to see them together in one place. The same is true about books and their approaches. What we also know is that there are far too few data warehouses than relational databases. Again, the same is with MDX-related cookbooks in respect to MDX tutorials, particularly those dealing with advanced topics. As a writer, I hope you recognize my intention and the value this book could bring you. As a reader, we rarely have enough time to start reading a book, not to mention f nish it. This is another advantage of the cookbook format. You can browse through the contents and look for the solution to a particular problem. As the recipes are relatively short and organized in chapters, that task won't take much of your time. All the information will be in one place. In addition to that, you'll see which recipes are related to that one, so that you can learn even more.
MDX with Microsoft SQL Server 2008 R2 Analysis Services C...
and over one million other books are available for Amazon Kindle. Learn more
| |||||||||||||||
New! Introducing the tech.book(store), a hub for Software Developers and Architects, Networking Administrators, TPMs, and other technology professionals to find highly-rated and highly-relevant career resources. Shop books on programming and big data, or read this week's blog posts by authors and thought-leaders in the tech industry. > Shop now |
Book Description
Frequently Bought Together
Editorial Reviews
About the Author
Tomislav Piasevoli
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.
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.
Product Details
Would you like to update product info or give feedback on images? |
More About the Author
Tomislav Piasevoli is a Business Intelligence Specialist with years of experience with SQL Server Analysis Services. He 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 Microsoft's MVP award three times for his contribution to the SSAS community. Besides solving MDX, cube design and dimensional modeling puzzles, he occasionally presents at conferences, writes articles for magazines and maintains his blog at http://tomislav.piasevoli.com. His recent experience includes writing a book named "MDX with SQL Server 2008 R2 Analysis Services: Cookbook" published by Packt Publishing Ltd.
Customer Reviews
Most Helpful Customer Reviews
5 of 5 people found the following review helpful
Format:Paperback
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. Read more ›
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. Read more ›
2 of 2 people found the following review helpful
Format:Paperback
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.
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.
2 of 2 people found the following review helpful
By tlachev
Format:Paperback
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!
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!
Most Recent Customer Reviews
3.0 out of 5 stars
Not bad...
The book is quite simple, not so deep as I expected.
For a beginner I believe it is a excelente option in order to get the first look at MDX.
For a beginner I believe it is a excelente option in order to get the first look at MDX.
Published 2 months ago by Ronaldo Braghittoni
5.0 out of 5 stars
a practical, must-have book for MDX developer
I bought a copy right after it was released. At that time I just started to learn MDX, for many examples, I have to review many times to understand them. Read more
Published 12 months ago by hxy0135 NJ
5.0 out of 5 stars
Definitely Worth Buying
Firstly, it is a cookbook - Tomislav has written recipes for a large amount of real-world scenarios. Read more
Published 19 months ago by Boyan Penev
5.0 out of 5 stars
A very practical book for the intermediate MDX user
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. Read more
Published 19 months ago by Mr Darren Gosbell
5.0 out of 5 stars
A must have recipe book for using MDX
This is by no means an introduction (101) book to MDX, but it is an MDX reference book that anyone using Analysis Services should have on their desk (or device - available in eBook... Read more
Published 20 months ago by Dan English
5.0 out of 5 stars
Must have book for MDX developers
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. Read more
Published 20 months ago by Jason T. Thomas
5.0 out of 5 stars
If you know beginning or intermediate MDX you will LOVE this book!
In the "Who this book is for" section the author states that, "if you are a SQL Server analysis services developer who wants to take your high-performance cubes further using MDX,... Read more
Published 20 months ago by Ron Davis
4.0 out of 5 stars
Initial Amazon Review: 'MDX with Microsoft SQL Server 2008 R2 Analysis...
I'm just beginning to consume this book, so at this stage, I can't yet honestly give it 5 Stars. However, I'm already benefitting from...
(A) p. Read more
(A) p. Read more
Published 20 months ago by Daniel C. Upton
