The most recent version of Dodeca brought several exciting enhancements for MDX-related functionality. One of these is a new selector list based on a reusable MDX script object. Although MDX queries are probably most often associated with queries that return numerical data from a cube, they also have incredibly useful metadata capabilities that can be employed for various purposes. In Dodeca, it’s common to use a report script or member query specification to return members from an outline. For example, you might want to provide your users with a selector such that they can choose a particular product (or products) from your Product dimension in order to customize a report that they will build dynamically.
I see MDX scripts as being a natural, clean, and flexible way to populate these selectors, and moving forward I will recommend them whenever possible over the more arcane report scripts that have been around for years.
That all said, what I want to show today is the following: I’m going to edit an existing Dodeca view so as to replace one of its existing selector lists with a new list based on an MDX query.
Consider the following view for an Income Statement:
Our existing view uses a typical selector for the Market dimension selection
As you can see, the user is able to choose a particular Market, Product, and Scenario and then build the view. The available members shown for the Market and Product selector lists are, of course, generated dynamically based on the outline of the Essbase connection associated with the view. But let’s say that I want to provide the user a list of very particular items from the Market dimension. In this case, I want to make it so the user only sees and has to choose from members from the Market dimension that are both at level 1 and have a certain UDA (“Major Market”).
Create the Essbase MDX Script
Let’s navigate over to the new Essbase Scripts tab (Admin > Essbase Scripts). At the moment, I don’t have any scripts (this is actually a copy of Dodeca running out of a Docker container and I’m not populating any default Essbase Scripts yet!):
Pull up the Essbase Scripts management tab by selecting it from the Admin menu
I’ll go ahead and click on New and then choose MDX script. In this case I’ll just call it MajorMarkets_MDX. I’ll then have the following new script properties showing:
Editing a new MDX Essbase script
The first thing to do is to define the Query property and populate an MDX script. Editing that property will bring up the following dialog where I can enter the query:
Edit the MDX script
For reference (or for copying and pasting with your own copy of Dodeca and Sample/Basic), here’s the MDX script:
SELECT
{} ON 0,
{Intersect(Uda([Market], "Major Market"), [Market].Levels(1).Members)} ON 1
Note a couple of things about this script:
- I can specify an empty tuple for the first axis (axis 0). A lot of examples of MDX scripts will “anchor” to some default member in another dimension, usually Actual from the Scenario dimension, but in this case it’s not necessary and in fact will keep the script cleaner and reusable
- I’m not specifying
ON PAGES, ON ROWS, ON COLUMNS, or similar. The equivalent to these are AXIS(0), AXIS(1), and AXIS(2), respectively (also note that there are additional axes although in practice it seems uncommon to see more than three, in favor of using CrossJoin). As I also recently discovered, you don’t even need to write out AXIS – using the axis index itself is sufficient
- There’s no FROM [Sample].[Basic] at the end of the query. It can be included if we want, but when an Essbase connection is specified for the query (as we”ll configure in a moment), the cube is implied and we can save some keystrokes (but perhaps more importantly, we can make our script even more reusable)
With the query now specified, let’s set the Essbase connection (this is a simple dropdown where we choose an already configured connection, in this case, Sample.Basic), and lastly, set the Axis property. We can use Rows here because in this case we only want to return the data as a simple list of rows, because it’s being populated into a simple selector list.
Set the EssbaseConnectionID and Axis on the Essbase script property editor
With the configuration out of the way, we can now test the query to make sure it brings back the proper data. In the following screenshot, I’ve selected the Show as Member List option, which has the effect of showing the script output as a simple text list (as opposed to inside of a grid). I find that it’s my preferred viewing format for simple selectors like this. Just press Test Query and check things out:
Test the MDX script to verify its output
As expected (or as hoped, perhaps), the exact members I wanted were returned: just those members from the Market dimension that are at level one and that are also tagged with the UDA “Major Market”.
Create the Essbase MDX Selector List
So far we’ve defined an Essbase MDX script inside of Dodeca, using Dodeca’s brand new MDX script editor (that sports syntax highlighting and autocomplete, to boot!). The Essbase Script object itself is just half of the equation. We can reuse this object anywhere in Dodeca that we can reference an MDX script. As it turns out, we want to use this in a new selector list, which we will now configure. Let’s create the new Selector List:
Create a new MDX-based Selector List
First thing’s first, we give the selector list a unique ID, as we would with any other object. Then, as with any other selector list, we choose the selector that it will be associated with. In this case the selector represents the Market dimension from our cube(s). Next, the selector list type is the new EssbaseMdxQuery selector list object type. Lastly, we choose what type of control (GUI) will be used to show the selector list. In this case I just want a simple flat list to show on the side of the view.
Now we configure the properties for the new selector list. Most of the defaults are fine here, although I’m going to focus on setting two things here: one, I want AutoOpenListBox set to True as a nicety for the user so that the list pops open when the view is opened, and second (and more importantly), I need to select my MDX script object for the MdxScriptID property.
Set basic properties for the new MDX selector list
Update the View
At this point, the Essbase script and the new selector list are ready to go. To finish things out for this example, though, I’m going to update an existing view to use the new selector instead of the one it was previously configured with. So there’s nothing unique about the following steps with respect to MDX selector lists. Let’s go open the view (Income Statement) to edit the selector configuration:
Open view properties to edit the Selector Configuration
A given selector in Dodeca will often have a default Selector List, but in this case we are going to set a specific selector list to use no matter what the default is. In the following screenshot, I have updated the Selector List property of the 4_Market selector so that it specifically uses the new Sample_Basic_Market_Regions selector list I configured a moment ago.
Update the specific Selector List for the Market selector on our view
After that’s set, I just save the selector configuration, update the view, and am now ready to launch the view:
Viewing and building our updated view, showing the new MDX selector in place
As you can see, the old selector that provided a hierarchy of Markets is now gone, and in its place is the new selector being driven by the MDX query we defined, and only showing me the exact selections that I wanted to see.
Wrapping Up
I hope you found this example useful. As I stated earlier, I am very excited about the newfound energy that is being put into MDX these days – both by Oracle and into Dodeca. It is an incredibly powerful language and numerous use cases abound where it can help provide users with exactly the data they want to see, implemented in a way that will be even more clear and maintainable than previous techniques such as report scripts or member queries. I put together a few other MDX query examples for pulling members that might be useful as well.
