Regular expression for extracting element from MDX Query

6
votes

I would like to extract information, or the query axis element from a given MDX query. Suppose I have this query :

SELECT NON EMPTY {
    Hierarchize({[Product].[Product Family].Members})
} ON COLUMNS, 
NON EMPTY Hierarchize ( 
    Union ( 
        CrossJoin ( {[Time].[1997].[Q1]}, 
            CrossJoin ([Store].[Store Name].Members, 
                [Store Type].[Store Type].Members
            )
        ), 
        CrossJoin({[Time].[1997].[Q2]}, 
            CrossJoin([Store].[Store Name].Members, 
                [Store Type].[Store Type].Members
            )
        )
    )
) ON ROWS FROM [Sales] 
WHERE {
    Hierarchize({[Measures].[Unit Sales]})
}

The substring I would like to extract has pattern like this : [...](.[...]) or [...].[...].Members

Note that, ... means any alphanumeric, and what inside bracket means can be recurred. Thus, the results I expect are :

[Product].[Product Family].Members; [Time].[1997].[Q1]; [Time].[1997].[Q2]; [Store].[Store Name].Members; [Store Type].[Store Type].Members; [Measure].[Unit Sales]

I tried my best and finally figured this regex : 

\[.*?[A-Za-z\s]\](.*?(\.\[.*?[A-Za-z\s]\])|(\.Members))

But the results are :

[Product].[Product Family]; [Time].[1997].[Q1]}, CrossJoin ([Store]; [Store Name].Members, [Store Type].[Store Type]; [Time].[1997].[Q2]}, CrossJoin([Store]; [Store Name].Members, [Store Type].[Store Type]; [Sales] WHERE {Hierarchize({[Measures].[Unit Sales]

Can someone give any correction with my regex? Any help would be appreciated.

2
regexmdx

2 Answers

4
votes

Hmm, could you try this:

(\[[\w ]+\]\.\[[\w ]+\](?:\.(?:Members|\[Q\d\]))?)

This seems to work for me. I tried it on Rubular.

1
votes

Interesting task...

Well please try the following
(?<result>\[[^\]]+\]\.\[[^\]]+\]\.Members)|(?<result>(\[[^\]]+\]\.){2}\[[^\]]+\])

if you read the group result in every match you will get, what you want. I used Expresso to construct the RegEx.