Re: [pentahoanalysistool-dev:290] Re: [pentahoanalysistool-dev:/] Potential solution for olap4j query model drill down

[t...@wamonline.org.uk]

Here's a quick one for Will, Paul or anyone who cares to listen,

So I got the basic drilling working, for 2 dimensions, so you can do Region*Department and you'll get a result thats very much like JPivot Drill Position, the problem I'm facing is very much a lack of understanding I think but basically to get the code working I find you have to do an include for every child member something like:

queryDimension.include(memberFetched);
                for (int i = 0; i<childmembers.size();i++){
                queryDimension.include(childmembers.get(i));
                }

because if you use a Selection like Selection.Operator.INCLUDE_CHILDREN then you can't select an individual child, and Will your samples use .include on the first dim then .createselection on the 2nd, but if that is the case how would you crossjoin and drill a 3rd or subsequent  level?

Cheers

Tom



On Fri 13/11/09 4:27 PM , Paul Stoellberger paul.sto...@aschauer-edv.at sent:

Hi Will, 

thanks for implementing the context, I think its a quite reasonable way to do drilling in olap4j.

I have to admit I'm not 100% aware what the consequences are, but it 'feels' alright

Just so i understand your implementation.. this would be the way to create a drill query:

1 )We move the Dimension "Store" and "Product" to the same axis

2) we include all the members we want from the "Product" dimension

3) we include all members we want from the Store dimension, but instead of just including it, which would happen without any connection to the other queryDimension, we create a context for these members, where they should applied. so they won't be applied for every member of the Product dimension, but to a specific one.. so the result will look like:

All Products.Food

All Products.Drink All Stores.Store1

All Products.Drink All Stores.Store2

All Products.Drink All Stores.Store3

All Products.Hardware

If thats correct. ok... but I do have some questions about it:

 

a) What happens if the context (member) we're adding to the selection to wasn't included in the query yet?

b) What happens if the first queryDimension is Store and the second is Product, what will the query then look like? 

c) What happens if we move the storeDimension to a different axis?

We're gonna need to be able to use MDX functions for selections... so e.g instead of including all children from the Store Dimension .. we would like to apply a Filter() or TopCount()

Do you think it would be an idea to do it similar:

Selection children = storeDimension.include(Selection.Operator.CHILDREN, "Store", "All Stores");

children.applyFunction(new TopCount(5);

Well I guess this doesn't make much sense since usually you need more than one Selection for a function.. so more like:

Member stores = cube.lookupMember("Store.All Stores");

Selection children = query.getSelectionFactory().createMemberSelection(stores,  Selection.Operator.CHILDREN);

Member measure =  cube.lookupMember("Measures.Actual);

Selection filterChildren = new TopCountSelection(children, 5, measure)

Since this is not a part of an existing queryDimension i can't include it like storeDimension.include(filterChildren) .... right?

I don't know if that makes any sense at all... I'm just trying to get my head around how we could use MDX functions in our queries...

Please let me know what you think

-paul

On 10 Nov 2009, at 05:07, Will Gorman wrote:

I've updated the context API code and removed the assumed ordering
issue so that push operations should work.  The API now looks like
this:

       Selection children =
storeDimension.include(Selection.Operator.CHILDREN, "Store", "All
Stores");
       children.addContext(productDimension.createSelection("Product",
"All Products", "Drink"));
       children.addContext(timeDimension.createSelection("Time",
"1997", "Q3"));

Unfortunately I haven't figured out a way with this model to support
the QueryDimension.setHierarchizeMode() API.  The MDX generated now
uses a Hierarchize at the root of the axis to sort all the unions
correctly.   Is keeping that feature a requirement?  We could move the
HierarchizeMode API into the Axis vs. the individual dimensions, or if
someone knows what the MDX would look like for unions and dimension
specific hierarchize calls, I could attempt that.

Let me know what you think!

Will

On Mon, Nov 9, 2009 at 9:24 AM, Will Gorman <wgo...@gmail.com> wrote:

Hi Tom,

That is exactly what the selection context is for, so you can specify

where in the axis you are drilling.

Will

On Mon, Nov 9, 2009 at 1:04 AM,  <t...@wamonline.org.uk> wrote:

Off to London today, so sadly I haven't got time to test it, just to clarify

though, in comparison to the closest we've got so far, if you specify 2

members in the QM, say Region*Products, if you drill Products.Drink, it is

smart enough to work out which Region you're drilling on? Thats the crazy

issue currently faced with QM drilling.

Cheers

Tom

On Mon 9/11/09 5:01 AM , Will Gorman wgo...@gmail.com sent:

Hi folks,

I was experimenting a little bit this weekend with drill down support

in the olap4j query model. The idea I introduced was a selection

context (Selection.get/setSelectionContext()). This context contains

the members of the other dimensions in the axis related to the

selection.

The unit test "testSelectionContext()" which I included in the

attached patch selects the children of 1997 within the context of

products.drink and all stores, the query also has a selection on all

store children, which is only in the context of drink and 1997 q3.

Output:

Axis #1:

{[Measures].[Store Sales]}

Axis #2:

{[Product].[All Products], [Time].[1997], [Store].[All Stores]}

{[Product].[All Products].[Drink], [Time].[1997], [Store].[All Stores]}

{[Product].[All Products].[Drink], [Time].[1997].[Q1], [Store].[All Stores]}

{[Product].[All Products].[Drink], [Time].[1997].[Q2], [Store].[All Stores]}

{[Product].[All Products].[Drink], [Time].[1997].[Q3], [Store].[All Stores]}

{[Product].[All Products].[Drink], [Time].[1997].[Q3], [Store].[All

Stores].[Canada]}

{[Product].[All Products].[Drink], [Time].[1997].[Q3], [Store].[All

Stores].[Mexico]}

{[Product].[All Products].[Drink], [Time].[1997].[Q3], [Store].[All

Stores].[USA]}

{[Product].[All Products].[Drink], [Time].[1997].[Q4], [Store].[All Stores]}

{[Product].[All Products].[Food], [Time].[1997], [Store].[All Stores]}

{[Product].[All Products].[Non-Consumable], [Time].[1997], [Store].[All

Stores]}

Row #0: 565,238.13

Row #1: 48,836.21

Row #2: 11,585.80

Row #3: 11,914.58

Row #4: 11,994.00

Row #5:

Row #6:

Row #7: 11,994.00

Row #8: 13,341.83

Row #9: 409,035.59

Row #10: 107,366.33

The code right now is a little fragile, the push* operations aren't

compatible yet because the order of dimensions in the context is

assumed, and the dimension hierarchize flag isn't supported yet.

If this approach makes sense, I'll create a drill down operation that

simplifies the API. The key is providing context to the drill down,

so you know where in the axis you are drilling.

To test the code, apply the patch to the latest olap4j svn.

If you can think of a scenario this approach doesn't address, or if

you have any problems with the patch, please let me know.

Thanks!

Will

[t...@wamonline.org.uk]

Ok so i sorta get it,

you use the 1st dimension as a master dim so you can for example use the [Region].[All Region].[Southern] as a selection and then you can add Departments for example to that selection, got that far and it works, then you could add Positions to that selection also, but I haven't yet worked out how you'd narrow down the selection on the 2nd level in this example,

So I can get PAT to generate [Region].[All Region].[Southern]*[Departments].[All Departments].children*[Positions.[All Positions].children as a rough example, but I dunno how to tell the selection to only drill on [Departments].[All Departments].[Executive Management] for example as the selection for that level is just a Selection.Operator.INCLUDE_CHILDREN

Hope that made a modicum of sense.

Cheers

Tom



On Sun 15/11/09 11:21 AM , t...@wamonline.org.uk sent:

Here's a quick one for Will, Paul or anyone who cares to listen,

So I got the basic drilling working, for 2 dimensions, so you can do Region*Department and you'll get a result thats very much like JPivot Drill Position, the problem I'm facing is very much a lack of understanding I think but basically to get the code working I find you have to do an include for every child member something like:

queryDimension.include(memberFetched);
                for (int i = 0; i

Selection children = query.getSelectionFactory().createMemberSelection(stores , Selection.Operator.CHILDREN);

[t...@wamonline.org.uk]

Also if I remove the selection clause and loop on the number of children of a member and try and add them all individually, its creates invalid mdx.



On Sun 15/11/09 5:26 PM , t...@wamonline.org.uk sent:

[Will Gorman]

Hi Tom,

I'd want to see more details on the invalid MDX being generated, could
you create a unit test similar to what I've included in the patch? In
my example unit test I show multiple drill down using three levels,
I'm not sure I understand what you mean by your earlier questions...
Here is how I could imagine the user interaction:

Select Dim A All, Dim B All, Dim C All
Drill into Dim B All Children (no context changes necessary because
Dim A and Dim C only have a single member selection, but if you
included it you'd just add Dim A.All and Dim C.All to DimB All
Chilfdren's context
Drill into DimC All next to child Dim B.Child1, this will create a new
selection for DimC.Children, with the context of Dim A.All and Dim
B.Child1.

I'm not sure that makes sense or not, basically every time a user
clicks drill down, you'd need to create an additional selection with
the necessary context. It can be slightly confusing, because you
might have Dim A.All Member + Children selected in the query, but the
context is one specific child in DimA, you don't need to create a
selection for that member, just add context.

Hope that helps a little. If you are still struggling, I recommend
demonstrating the issues through unit tests, that will help me
understand what your issues are.

Thanks,

Will

[t...@wamonline.org.uk]

Thanks Will that clarifies things a bit, I'll fiddle later on.

Ta

Tom



On Mon 16/11/09 3:11 AM , Will Gorman wgo...@gmail.com sent: