[Mondrian] Dinamically build members in a MDX query?

Julian Hyde jhyde at pentaho.com
Sat Sep 12 21:39:36 EDT 2009


Pedro,

I can see that we could have members which are in-memory (i.e. not in any
database table), can be created programmatically, and have a name and also a
key value (e.g. 1, 2, 3, ...) that can be used to drive a formula in another
dimension.

However, I'm not totally sure that it makes sense, is implementable, or
would be understandable/useful to people who were not mondrian gurus. So the
idea needs to be knocked into shape some more. Can you log an RFE for this?
I'd like to see if I can meld it with my idea for 'pure calculated'
dimensions.

Julian

PS We can discuss further at the meetup. Tapas for features.

> -----Original Message-----
> From: Pedro Alves [mailto:pmgalves at gmail.com] 
> Sent: Friday, September 04, 2009 5:13 PM
> To: jhyde at pentaho.com; Mondrian developer mailing list
> Subject: Re: [Mondrian] Dinamically build members in a MDX query?
> 
> 
> 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
> 




More information about the Mondrian mailing list