[Mondrian] Dinamically build members in a MDX query?

[Julian Hyde]

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
> 
>