MDX Drill-Down Query Generation

Question

I'm writing some client code to integrate with ActivePivot. As part of this, I need to be able to handle drill-down requests and generate the appropriately modified MDX query before submitting it to the server.

Here's a simple example using the ActivePivot sandbox cube. Suppose the base query is:

SELECT
{
    Hierarchize
    (
        {
            [Bookings].[DEFAULT_HIERARCHY].[ALL].[AllMember],
            [Bookings].[DEFAULT_HIERARCHY].[ALL].[AllMember].Children
        }
    )
} ON COLUMNS,
{
    Hierarchize
    (
        {
            [TimeBucket].[DEFAULT_HIERARCHY].[ALL].[AllMember],
            [TimeBucket].[DEFAULT_HIERARCHY].[ALL].[AllMember].Children
        }
    )
} ON ROWS
FROM [EquityDerivativesCube]

When the user drills down on the "2M" tenor, I would need to generate:

SELECT
{
    Hierarchize
    (
        {
            [Bookings].[DEFAULT_HIERARCHY].[ALL].[AllMember],
            [Bookings].[DEFAULT_HIERARCHY].[ALL].[AllMember].Children
        }
    )
} ON COLUMNS,
{
    Hierarchize
    (
        {
            [TimeBucket].[DEFAULT_HIERARCHY].[ALL].[AllMember],
            [TimeBucket].[DEFAULT_HIERARCHY].[ALL].[AllMember].Children,
            [TimeBucket].[DEFAULT_HIERARCHY].[ALL].[AllMember].[2M].Children
        }
    )
} ON ROWS
FROM [EquityDerivativesCube]

Now, for simpler queries such as those above I can just define a substitution point in a base query and inject drilled down paths into it. For example, the base query for the above would be:

SELECT
{
    Hierarchize
    (
        {
            {0}
        }
    )
} ON COLUMNS,
{
    Hierarchize
    (
        {1}
    )
}
ON ROWS FROM [EquityDerivativesCube]

(obviously the substituion points would need their curly braces escaped, but you get the idea)

But with more complicated queries, such as those with multiple members on an axis, this approach quickly breaks down. For example, here is a query with two members on the column dimension:

SELECT
Hierarchize(
    Union(
        CrossJoin(
            {[Bookings].[DEFAULT_HIERARCHY].[ALL].[AllMember]},
             {[Underlyings].[DEFAULT_HIERARCHY].[ALL].[AllMember]}
        ),
         Union(
            CrossJoin(
                {[Bookings].[DEFAULT_HIERARCHY].[ALL].[AllMember]},
                 [Underlyings].[DEFAULT_HIERARCHY].[ALL].[AllMember].Children
            ),
             Union(
                CrossJoin(
                    [Bookings].[DEFAULT_HIERARCHY].[ALL].[AllMember].Children,
                     {[Underlyings].[DEFAULT_HIERARCHY].[ALL].[AllMember]}
                ),
                 CrossJoin(
                    [Bookings].[DEFAULT_HIERARCHY].[ALL].[AllMember].Children,
                     [Underlyings].[DEFAULT_HIERARCHY].[ALL].[AllMember].Children
                )
            )
        )
    )) 
ON COLUMNS,
{Hierarchize(
    {[TimeBucket].[DEFAULT_HIERARCHY].[ALL].[AllMember],
     [TimeBucket].[DEFAULT_HIERARCHY].[ALL].[AllMember].Children})} 
ON ROWS
FROM [EquityDerivativesCube]

I need to be able to detect drill-down requests against any drillable members and modify the query accordingly before re-submitting it.

So what I'm wondering is how to go about doing this? My initial hope was that MDX itself defined some standard mechanism to provide the drill-down query based on some parameters you pass it. However, I've not been able to find anything like this.

Thus, I fear that my only recourse is to parse the query myself and inject the drill-down information dynamically into an AST, then generate the new query. Can anyone confirm this, or offer an alternative approach?

score 4 · Answer 1 · answered Sep 10 '13 at 16:28

4

You can use the DrilldownMember function with the RECURSIVE flag.

For instance:

DrilldownMember({ [TimeBucket].[DEFAULT_HIERARCHY].[ALL].[AllMember]}, { [TimeBucket].[DEFAULT_HIERARCHY].[ALL].[AllMember], [TimeBucket].[DEFAULT_HIERARCHY].[ALL].[AllMember].[2M]}, RECURSIVE)

It will return the same members than

{
    {[TimeBucket].[DEFAULT_HIERARCHY].[ALL].[AllMember]},
    [TimeBucket].[DEFAULT_HIERARCHY].[ALL].[AllMember].Children,
    [TimeBucket].[DEFAULT_HIERARCHY].[ALL].[AllMember].[2M].Children
}

answered Sep 10 '13 at 16:28

Benoit

1,995
1
13
18

Thanks - I can see how this helps with simple queries, but I don't see how it can be applied to more complicated queries. I've updated my question to include an example. Could you please take a look and comment/update your answer? – Kent Boogaart Sep 16 '13 at 05:52
After experimenting a bit more, I *think* what I need to do is use `CrossJoin` (no need for `Union` - that was just generated by the tool I was using) with one `DrilldownMember` call for each member of the dimension. So I would maintain a set of members to drill down per dimension member. As the user drills into members, I would add to the approriate set and generate the query accordingly. When they drill up, I would remove from the appropriate set. Would love to hear your thoughts on this approach. – Kent Boogaart Sep 16 '13 at 07:00

MDX Drill-Down Query Generation

1 Answers1