This course explains how to use MDX to query an Analysis Services multidimensional database. It begins with an introduction to the language, showing how it can be used beyond developing a cube, as well as an introduction to important concepts such as tuples and sets. The course then focuses on how to structure queries and how to create sets to use on a query axis. The course then continues with an exploration of the multiple navigation functions used to traverse hierarchical data. Last, it explains how to build more complex queries by adding calculations in the form of calculated measures, calculated members, and named sets. This course was written for SQL Server 2012 Analysis Services, but most principles apply to SQL Server 2005 and later.
Stacia Misner Varga is a consultant, instructor, author, mentor, BI Partner for SQLSkills, and principal of Data Inspirations specializing in Microsoft business intelligence technologies for over 10 years. She is a frequent speaker at the PASS Summit, IT/Dev Connections
conferences, and various SQL Saturday and Microsoft-related events
Introduction to MDX Hi, I'm Stacia Misner. This is the MDX Fundamentals course, specifically Model 1: Introduction to MDX, in which we'll be exploring the usage, concepts, and terms of the MDX language. We start this module by learning what the purpose of the MDX language is, and then we look at a variety of ways that MDX is used by client applications. Then to prepare for subsequent modules, we need to learn some vocabulary, specifically we need to know the terms and concepts that are unique to the multidimensional model so that we can work with the MDX language correctly and effectively.
Basic MDX Queries Hi, I'm Stacia Misner and this is module 2 of the MDX Fundamentals course in which we begin learning the syntax of MDX. To write queries, we need to learn a lot about syntax, which is the focus of this module. We'll start by learning all the different ways that we can refer to members using identifiers. Then we'll start building queries by learning how to request a tuple from Analysis Services. We learned about tuples in module 1 and now we're ready to see how to use that information. Now asking for data tuple by tuple can be tedious, so we'll learn how to generate tuples by using axes in our queries. Most commonly these are like rows and columns that you'd find in a pivot table. Sets are another key concept in MDX. We learned a bit about them in the previous module, but in this module we'll expand what we know about them and how to work with them. Now sometimes we don't want to see members that have no data, so we have a way to eliminate those if we like. At this point, we have most of the working pieces we need in a query, so this is a good point to stop and review how Analysis Services resolves a query. Now this is a very important concept to master in order to really understand how to write MDX queries. To wrap up the module, we'll look at some bonus data that we can get with our MDX queries. Specifically, cell properties and then dimension properties.
Sets Hi, I'm Stacia Misner. This is module 3 of the MDX Fundamentals course covering Sets. We'll learn not only how to create sets, but how to manipulate sets to create new ones. In this module, we're going to learn a lot about sets. We'll start by learning how we can find and use functions that create a set and then we'll learn where in a query we can work with sets. Next, we'll learn how to override the default sort order of members in a set and then we'll learn how to manipulate sets to create smaller sets through selecting specific members of a set or through filtering. Then we'll create multidimensional sets to expand the amount of information we can obtain from a query. Then we'll learn how to use polymorphic operators in place of certain functions and we'll learn how to create a new set by iterating through an existing set and combining each member with members from a second set. We'll learn a new technique for removing empty members from our results. And last, we'll see how to extract tuples from a set to get only the partial tuple we need for reporting our analysis needs.
Hierarchies in MDX Hi, I'm Stacia Misner. This is Module 4 of the MDX Fundamentals course covering Hierarchies in which we'll learn how to navigate hierarchies. Hierarchies are an important and powerful part of Analysis Services. In this module, we'll discuss what the process of hierarchy navigation is and then we'll explore various functions that we use to navigate a hierarchy, starting with upward navigation. And these are functions that help us find members above a starting point. And we'll also explore the functions that allow us to travel from a high level to a lower level of a hierarchy. And then we'll review the functions that find members on the same level as our starting point.
Calculations Hi, I'm Stacia Misner. This is Module 5 of the MDX Fundamentals course in which we learn how to add various types of calculations to our queries. Thus far throughout this course, we've reviewed a number of queries, but we haven't developed any calculations yet so our queries have been rather on the simple side. So in this module, we're going to start with the introduction to calculations to understand where they get created and how they behave in terms of memory. Then we'll move into tuple-based calculations. And these are probably the most common types of calculations that you're going to do initially. Once you get the grasp of tuples, being able to manipulate values by using tuples is not much different than performing math or creating Excel formulas. But we also have set-based calculations and these might be a little more challenging to think about because it's not the normal way that we approach building calculations in other tools. And a very popular type of calculation that we use in Analysis Services is time series. As a business intelligence product, Analysis Services supports businesses who are trying to understand history, understanding how well they're performing over time, and so there's a lot of time-series calculations built-in to support that process. And then last we have a few miscellaneous functions just to tie up some loose ends and give us some additional capabilities. Now by the time we finish this module, we'll also have finished the course and we will not have covered every single function that's available, but we will have covered the commonly used functions and the types of problems that Analysis Services is usually used for and the types of queries that often get created for Analysis Services, so you'll have a good foundation for continuing to build your skills.