[Mondrian] 100+ invocations of getMemberChildren with just one element in contraint [Found it!]

Pedro Alves pmgalves at gmail.com
Wed Jul 18 07:29:39 EDT 2012



Ignore what I said about that variable. It has to do with the way the 
query is written!


Take 2 queries, exactly the same, except one uses a fully qualified name 
and the other uses [Dimension].[Level].<name>



with set bigset as '([Product].[Drink].[Alcoholic Beverages].[Beer and 
Wine].[Beer].[Good].[Good Light Beer] : 
[Product].[Non-Consumable].[Periodicals].[Magazines].[Sports 
Magazines].[Robust].[Robust Monthly Sports Magazine])'
select NON EMPTY {[Measures].[Unit Sales]} ON COLUMNS,
   Except([bigset], {[Product].[Drink].[Alcoholic Beverages].[Beer and 
Wine].[Beer].[Good].[Good Light Beer]}) ON ROWS
from [Sales]


Number of queries (after rolapschema is initialized): 742


with set bigset as '([Product].[Product Name].[Good Light Beer] : 
[Product].[Product Name].[Robust Monthly Sports Magazine])'
select NON EMPTY {[Measures].[Unit Sales]} ON COLUMNS,
   Except([bigset], {[Product].[Product Name].[Good Light Beer]}) ON ROWS
from [Sales]


Number of queries (after rolapschema is initialized): 41


(note that this is a "cold boot", and it takes almost 40 queries to 
initialize the rolapschema



Question: Would be interesting to try to detect if we're doing a lot of 
queries and either batch them or at a point in time get the entire 
dimension in memory. Thoughts?



-pedro



On 07/17/2012 05:30 PM, Pedro Alves wrote:
>
> Found that this happens when I have: mondrian.native.nonempty.enable=true
>
>
> Here's a sample in foodmart:
>
>
> with set bigset as '([Product].[Drink].[Alcoholic Beverages].[Beer and
> Wine].[Beer].[Good].[Good Light Beer] :
> [Product].[Non-Consumable].[Periodicals].[Magazines].[Sports
> Magazines].[Robust].[Robust Monthly Sports Magazine])'
> select NON EMPTY {[Measures].[Unit Sales]} ON COLUMNS,
>   Except([bigset], {[Product].[Drink].[Alcoholic Beverages].[Beer and
> Wine].[Beer].[Good].[Good Light Beer]}) ON ROWS
> from [Sales]
>
>
> Why I don't understand yet is why it's trying to get the children of a
> fully qualified member
>
>
>
> -pedro
>
>
>
>
>
>
>
> On Fri 13 Jul 2012 06:25:55 PM WEST, Pedro Alves wrote:
>>
>>
>> Hello.
>>
>>
>> I'm evaluating a function like: Except( set1, set2) , where set1 and
>> set 2 is an extensive list (about 50 members, something like that).
>> All the members are explicitly named: Channels.All.[release-cck-yandex]
>>
>>
>> The problem is that mondrian is doing a query for each member, and I
>> can't quite understand why:
>>
>>
>> It does this for each, resulting in a huge performance loss. What
>> exactly control this? Why is it trying to go, for each individual
>> record, fetch the children of the element with just one member at a
>> time in the contraint key?
>>
>>
>> Log:
>>
>>
>>
>> 2012-07-13 18:11:40,659 DEBUG [mondrian.olap.Util]
>> Util.lookupCompound: parent.name=BlockList Analysis, category=unknown,
>> names=Channels.All.[release-cck-yandex]
>> 2012-07-13 18:11:40,659 DEBUG [mondrian.rolap.RolapCube]
>> RolapCube.lookupChild: name=BlockList Analysis, childname=Channels,
>> status=hierUsage == not shared returning elementname=Channels
>> 2012-07-13 18:11:40,659 DEBUG [mondrian.rolap.RolapHierarchy]
>> HierarchyBase.lookupChild: name=Channels, childname=All returning
>> elementname=All
>> 2012-07-13 18:11:40,660 DEBUG [mondrian.rolap.RolapSchemaReader]
>> looking for child "[release-cck-yandex]" of [Channels].[All]
>> 2012-07-13 18:11:40,965 DEBUG [mondrian.server.monitor]
>> ExecutionStartEvent(517)
>> 2012-07-13 18:11:40,965 DEBUG [mondrian.server.monitor]
>> SqlStatementStartEvent(177)
>> 2012-07-13 18:11:42,604 DEBUG [mondrian.server.monitor]
>> SqlStatementExecuteEvent(177)
>> 2012-07-13 18:11:42,605 DEBUG [mondrian.rolap.RolapUtil]
>> SqlMemberSource.getMemberChildren: done executing sql [select
>>     "product_channels"."product_channel" as "c0"
>> from
>>     "product_channels" as "product_channels"
>> where
>>     "product_channels"."product_channel" = 'release-cck-yandex'
>> group by
>>     "product_channels"."product_channel"
>> order by
>>     CASE WHEN "product_channels"."product_channel" IS NULL THEN 1 ELSE
>> 0 END, "product_channels"."product_channel" ASC], exec+fetch 1641 ms,
>> 1 rows
>> 2012-07-13 18:11:42,605 DEBUG [mondrian.olap.Util]
>> Util.lookupCompound: found child.name=release-cck-yandex,
>> child.class=mondrian.rolap.RolapCubeMember
>> 2012-07-13 18:11:42,609 DEBUG [mondrian.server.monitor]
>> SqlStatementEndEvent(177)
>> 2012-07-13 18:11:42,612 DEBUG [mondrian.olap.Util]
>> Util.lookupCompound: parent.name=BlockList Analysis, category=unknown,
>> names=[Measures].[Total Requests]
>>



More information about the Mondrian mailing list