MDX Series
This is post number two in a six post series. A brief overview of the series:
- Post #1: Connect to Sample.Basic and execute two basic queries
- Post #2: Introduce functions using a single dimension
- Post #3: Introduce multiple dimensions and CrossJoin
- Post #4: Define sets and tuples to add a deeper understanding of how MDX works
- Post #5: Discuss how to avoid almost all frustration using MDX in Smart View
- Post #6: Provide a strategy for refreshable MDX using a macro
View Our Kscope MDX Presentation
Whither Art Thou, My Children?
In the introductory post to this series, we only used members, since that is the simplest way to create a query. But if that was all that MDX offered, then it would be so limited as to be almost useless. Fortunately, that is nowhere near all that it offers.
Functions allow us to create much more complicated views, with only the slightest increase in complexity to the query. Actually, compared to listing all members in a large dimension, functions provide a great simplification. However, it does require a little knowledge about what functions are available, and how to use them.
We will take a look at two relational functions in this section: Descendants and Members. By way of these examples, a lot of the power and flexibility of functions will be illustrated.
At the end, we will give a quick overview of the range of functions available and provide with a link to the Essbase Technical Reference, where you can find much more detail. As you come across different needs while building your queries, the Technical Reference will be an invaluable resource.
Let’s Add a Function
Adding a function to a query is easy. Every function follows one of these patterns:
(click to enlarge)
What is meant by this? Essentially it just means that there is a function keyword, and then one or more arguments, which is just information that tells the function how to behave. Arguments are always linked to something the function is supposed to do, whether that means giving it the starting point for what it needs to do, or telling it how it proceeds from the starting point.
Let’s take a concrete example. The DESCENDANTS function is used to retrieve members below a certain member (also, optionally, it may include the starting member). Here is the simplest use of the Descendants member:
(click to enlarge)
In Sample.Basic, this will retrieve:
(click to enlarge)
We can include this function in our query from the previous blog post.
Note: all blog posts in this series are connected to Sample.Basic using an Essbase connection. To execute an MDX statement, use the Essbase ribbon button for Query > Execute Query. If you need details on how to do this, the first post in this series walks through it step-by-step.
Here’s the query:
(click to enlarge)
And the results:
(click to enlarge)
Not much to it!
That’s not all the DESCENDANTS function can do though. There are also optional parameters. The first optional parameter can limit how deep in the hierarchy it looks for descendants. The second optional parameter uses keywords to further modify the first parameter (or, if the first parameter is not present, then simply to modify the default behavior of DESCENDANTS). One of the most common uses of this is to return only base-level members:
(click to enlarge)
Note: the use of 20 there is a bit arbitrary. Basically, unless you have a hierarchy that is more than 20 levels deep, then using “20, LEAVES” is equivalent to saying base-level members. If more than 20 levels were present, then the number 20 could be increased.
In Sample.Basic, this will retrieve:
(click to enlarge)
The MEMBERS Function
We’ll take a look at one more function here. Actually, we’ll take be using two functions, while only examining one.
The MEMBERS function works a little different than the DESCENDANTS function. The DESCENDANTS function took between one and three arguments, with the first argument being a member. (The second and third arguments were a number and a keyword.)
The MEMBERS function, instead of a member, takes either a layer or a dimension as a parameter. A dimension is pretty clear, but a layer requires a brief explanation. Basically, a layer refers to either a level or a generation, which are the two ways of looking at where members exist in the hierarchy. Generation counts down from the root dimension member to the base-level members, and levels counts up from the base-level members up to the root.
We aren’t going to fully explain the LEVELS function here, but we will use it, in part to demonstrate by example how the results of one function can form an argument for the second.
(click to enlarge)
Results:
(click to enlarge)
Whoops! Something odd happened there. Although we said to use LEVELS for Qtr1 at level 0, we actually are seeing base-level members for the full dimension. On further inspection, this is the correct result.
What is happening is that the LEVELS function always returns a full level, not a level restricted by the member that was used for input. This is also the reason that we have to use the MEMBERS and LEVELS function together: because what ON COLUMNS expects is a set, which is a collection of members (or member combinations, which we’ll explore in the next blog post). ON COLUMNS cannot take a level as input, since a level is a distinct concept in MDX, and can only be translated to the equivalent set of members by the MEMBERS function.
The takeaway here is that it is important to know three questions to think about when it comes to data types:
- What data types exist in MDX?
- What data type does a function given return?
- What transformation options are available to us?
Types of Functions
This section concludes with a brief rundown of datatypes.
Data types that can directly build MDX statements:
- Functions that return a member
- Functions that return a tuple
- Functions that return a set
We’ll explain what a tuple is in the next two blog posts. (We’ll also come to understand sets more fully.)
Data types that can be used as arguments in other functions:
- Functions that return a number
- Functions that return a dimension
- Functions that return a layer
- Functions that return a Boolean
- Functions that return a date
- Functions that return a string
More detail can be found in the Essbase Technical Reference. To avoid version issues and changing URLs, the recommended way to find the appropriate Technical Reference is to use your favorite search engine and search for “[your version] Essbase”, then open the Technical Reference from the Essbase page of the documentation.
(click to enlarge)
Up Next
The next post will introduce tuples, which are able to refer at once to multiple dimensions (as we see regularly in the POV), explain how to use tuples in sets, and use the CrossJoin function to combine sets in powerful ways.
