[Mondrian] Dinamically build members in a MDX query?

[Pedro Alves]

This is to use in a dashboard, where the users can compare different 
versions, so storing it in a table is no go (I've used this approach for 
slightly different scenarios, and worked great)


What I would like to have, as a user, was some function(s) that could 
create a dimension and define it's hierarchies from set values - using
Mdx functions like generate, name, cast, rank, etc. They give me 
powerful tools to get whatever values I want and being able to assign 
this ordered sets to "calculated dimensions" would give me a great 
liberty in terms of displaying results. On the example I gave I use 
[Locations].[<number>] just because the locations dimension wasn't used 
yet, could be anything else.


I have no idea how much this goes against the standards, probably it's 
impossible to have something like this. When I have some time I need to 
look if implementing custom functions (do we have UDF here or am I 
mixing luciddb concepts?) can help me to achieve this.



-pedro



On 09/09/04 20:55, Julian Hyde wrote:
> That's a tricky one. For ordinary members, I'd suggest that you back the
> dimension with a table, write into the table, and use the dimension
> cache-flush API (e.g. CacheControl.createAddCommand) to load those members
> into cache.
>
> However, are calculated members. This dimension is connected to the fact
> table via a foreign key, so it doesn't make sense to store it in a table.
> Such purely calculated dimensions are useful. Budget vs. Actual is the
> textbook example of a dimension that is orthogonal to the Measures
> dimension, and whose members are arbitrary placeholders for different
> calculations. (Is there a commonly accepted term for such dimensions?)
>
> But they are hard to achieve currently. In the scenarios prototype (see
> ScenarioTest.java) the Scenario system dimension uses an empty InlineTable.
> It has an arbitary connection to the fact table -- the time_id foreign key.
> If it was ever joined to the fact table, it would make all cells come back
> zero. But the trick is to ensure that all calculated members of this
> dimension always boil down to the 'all' member, which doesn't do any
> filtering.
>
> You could try something similar. Then use Cube.createCalculatedMember to add
> the extra calculated members programmatically.
>
> Please let me know how this turns out. I'd like to make pure calculated
> dimensions (or whatever they're called) a feature.
>
> Julian
>
>> -----Original Message-----
>> From: mondrian-bounces at pentaho.org
>> [mailto:mondrian-bounces at pentaho.org] On Behalf Of Pedro Alves
>> Sent: Friday, September 04, 2009 12:11 PM
>> To: Mondrian developer mailing list
>> Subject: [Mondrian] Dinamically build members in a MDX query?
>>
>>
>>
>> Hello.
>>
>>
>> I need to do a mdx query that outputs in rows the elapsed days from a
>> certain date; (I want to study the uptake of firefox versions since
>> their release)
>>
>>
>> For this I only have the date dimension; I can do what I want if I
>> specify the members one by one, but this is really a major hack; Is
>> there any alternative approach? Can I define a range of calculated
>> members dinamically ?
>>
>>
>> Here's the query (/me is ashamed):
>>
>>
>>
>> with set [versions] as '{[Products].[All].[Firefox].[3.5].[3.5.1],
>> [Products].[All].[Firefox].[3.5].[3.5.2]}'
>>     member [Measures].[Diff] as '([versions].Item(1.0) -
>> [versions].Item(0.0))'
>>     set [dates1] as '([Date].[All].[2009].[7].[15].[2009-07-15] :
>> [Date].[All].[2009].[7].[15].[2009-07-15].Lag((- 24.0)))'
>>     set [dates2] as '([Date].[All].[2009].[7].[31].[2009-07-31] :
>> [Date].[All].[2009].[7].[31].[2009-07-31].Lag((- 24.0)))'
>>     member [Locations].[-4] as 'IIf(([Products].CurrentMember.Name =
>> [versions].Item(0.0).Name), ([dates1].Item(0.0),
>> [Measures].[Uptake]),
>> ([dates2].Item(0.0), [Measures].[Uptake]))'
>>     member [Locations].[-3] as 'IIf(([Products].CurrentMember.Name =
>> [versions].Item(0.0).Name), ([dates1].Item(1.0),
>> [Measures].[Uptake]),
>> ([dates2].Item(1.0), [Measures].[Uptake]))'
>>     member [Locations].[-2] as 'IIf(([Products].CurrentMember.Name =
>> [versions].Item(0.0).Name), ([dates1].Item(2.0),
>> [Measures].[Uptake]),
>> ([dates2].Item(2.0), [Measures].[Uptake]))'
>>     member [Locations].[-1] as 'IIf(([Products].CurrentMember.Name =
>> [versions].Item(0.0).Name), ([dates1].Item(3.0),
>> [Measures].[Uptake]),
>> ([dates2].Item(3.0), [Measures].[Uptake]))'
>>     member [Locations].[0] as 'IIf(([Products].CurrentMember.Name =
>> [versions].Item(0.0).Name), ([dates1].Item(4.0),
>> [Measures].[Uptake]),
>> ([dates2].Item(4.0), [Measures].[Uptake]))'
>>     member [Locations].[1] as 'IIf(([Products].CurrentMember.Name =
>> [versions].Item(0.0).Name), ([dates1].Item(5.0),
>> [Measures].[Uptake]),
>> ([dates2].Item(5.0), [Measures].[Uptake]))'
>>     member [Locations].[2] as 'IIf(([Products].CurrentMember.Name =
>> [versions].Item(0.0).Name), ([dates1].Item(6.0),
>> [Measures].[Uptake]),
>> ([dates2].Item(6.0), [Measures].[Uptake]))'
>>     member [Locations].[3] as 'IIf(([Products].CurrentMember.Name =
>> [versions].Item(0.0).Name), ([dates1].Item(7.0),
>> [Measures].[Uptake]),
>> ([dates2].Item(7.0), [Measures].[Uptake]))'
>>     member [Locations].[4] as 'IIf(([Products].CurrentMember.Name =
>> [versions].Item(0.0).Name), ([dates1].Item(8.0),
>> [Measures].[Uptake]),
>> ([dates2].Item(8.0), [Measures].[Uptake]))'
>> [Measures].[Uptake]), ([dates2].Item(24.0), [Measures].[Uptake]))'
>>     member [Measures].[Uptake] as '((100.0 *
>> ([Products].CurrentMember,
>> [Measures].[Total Requests])) / ([Products].Parent, [Measures].[Total
>> Requests]))'
>> select NON EMPTY [versions] ON COLUMNS,
>>     NON EMPTY {[Locations].[-4], [Locations].[-3], [Locations].[-2],
>> [Locations].[-1], [Locations].[0], [Locations].[1], [Locations].[2],
>> [Locations].[3], [Locations].[4], [Locations].[5], [Locations].[6],
>> [Locations].[7], [Locations].[8], [Locations].[9], [Locations].[10],
>> [Locations].[11], [Locations].[12], [Locations].[13],
>> [Locations].[14],
>> [Locations].[15], [Locations].[16], [Locations].[17],
>> [Locations].[18],
>> [Locations].[19], [Locations].[20]} ON ROWS
>> from [BlockList Analysis]
>> _______________________________________________
>> Mondrian mailing list
>> Mondrian at pentaho.org
>> http://lists.pentaho.org/mailman/listinfo/mondrian
>>
>>
>
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian