[Feature] Filter

[khmm12]

Greetings!

Will be great to have filter operation 🙂. https://github.com/rsim/mondrian-olap/blob/c6f0325ef387eff238028398928fc99158eacc55/spec/query_spec.rb#L280

What do you think?

[jazzido]

Absolutely, shouldn't be too hard to implement.

Just curious, are you using mondrian-rest?

[khmm12]

Yep, I'm going to use as an abstraction on mondrian engine in my master's work. I think it exactly what I need to go away from Java ecosystem and use familiar tools like as Ruby, JavaScript and REST :-). It looks like ray of light in the dark after many days in researching how to run and deploy mondrian.

P.S. Thank you!

[jazzido]

That's awesome! You are the first "external" user that I know of. So far, I've been using mondrian-rest for my own projects.

BTW, take a look at mondrian-rest-ui, it might be helpful for interacting with the server.

[jazzido]

Hi @khmm12,

I've been thinking about how to implement this measure. If you used mondrian-rest at all, you might have noticed that it translates a query string such as:

drilldown[]=[Dimension 1].[Level 1]&drilldown[]=[Dimension 2].[Level 2]&measures[]=Measure 1

to an MDX query like:

SELECT {[Measures].[Measure 1]} ON 0,
       [Dimension 1].[Level 1].Members ON 1, 
       [Dimension 2].[Level 2].Members ON 2
FROM <cube>

On queries of that form, the Filter() should be applied to the set(s) bound to axes > 0 (that is, the levels you're drilling down into). Suppose that we want to apply a filter to axis 2, the generated MDX should be something like:

SELECT {[Measures].[Measure 1]} ON 0,
       [Dimension 1].[Level 1].Members ON 1, 
       Filter([Dimension 2].[Level 2].Members, [Measures].[Measure 1] > 42) ON 2
FROM <cube>

Best I could come up with is something like:

drilldown[]=[Dimension 1].[Level 1]&drilldown[]=[Dimension 2].[Level 2]&measures[]=Measure 1&filter[1]=[Measures].[Measure 1]>42

That is, the query string is telling mondrian-rest that it should apply a filter to the second drilldown. I'm not crazy about it, so I'm open to suggestions.

How do you imagine expressing the filter condition ([Measures].[Measure 1] > 42) in a query string?

BTW, mondrian-rest was also a part of my own master's work. Check it out here if you're interested: https://dspace.mit.edu/handle/1721.1/106058

[khmm12]

Hi, @jazzido.

I've been thinking and came up to same solution - using sparse array. And mostly probably it's a single right way using in REST case, also it doesn't bring breaking changes.

Another solution, as you know, is adding POST endpoint with flexible JSON body schema or GraphQL. But it's a little bit radical solution.

P.S. Thank you! I got acquainted with your work, this is great and very helpful for me! I think I'll leave references to you work.

[jazzido]

Another solution, as you know, is adding POST endpoint with flexible JSON body schema or GraphQL. But it's a little bit radical solution.

Yes, a GraphQL endpoint is in the roadmap (#7). BTW, I'm totally open to suggestions about how that would look like.

P.S. Thank you! I got acquainted with your work, this is great and very helpful for me! I think I'll leave references to you work.

Awesome! I'd be also interested in knowing more about the work that you're doing.

[jspeis]

Also just going to give this one a bump! Will be awesome to have this integrated.