[Mondrian] How to dynamically group multiple range on dimension

Selina Tech swucareer99 at gmail.com
Wed Mar 16 19:09:37 EDT 2016


Hi, Jeff:

    Thanks a lot for your help in detail. I followed this idea and changed
the code a little bit and got the correct result.
The result looks like:

All          sum_count
It100      55
It400      55
It2000    40

     I am wondering how can I change the column name from "All" to a
customized name, such as "Range".

Sincerely,
Selina



On Wed, Mar 16, 2016 at 5:00 AM, Wright, Jeff <
jeff.s.wright at truvenhealth.com> wrote:

> Are you just missing Aggregate()? Something like
>
>
>
> With member [number_dim].[lt100] as ‘Aggregate(
>
>   [number_dim].[All number_dims].[1] : [number_dim].[All
> number_dims].[100] )’
>
> Member [number_dim].[lt400] as ‘Aggregate(
>
>   [number_dim].[All number_dims].[101] : [number_dim].[All
> number_dims].[400] )’
>
> Member [number_dim].[lt2000] as ‘Aggregate(
>
>   [number_dim].[All number_dims].[1001] : [number_dim].[All
> number_dims].[2000] )’
>
> SELECT
>
> NON EMPTY {[Measures].[sum_count]} ON COLUMNS,
>
> NON EMPTY { [number_dim].[lt100], [number_dim].[lt400],
> [number_dim].[lt2000] } ON ROWS
>
> FROM [my_cube]
>
>
>
> --jeff
>
>
>
> *From:* mondrian-bounces at pentaho.org [mailto:mondrian-bounces at pentaho.org]
> *On Behalf Of *Selina Tech
> *Sent:* Tuesday, March 15, 2016 9:24 PM
> *To:* Mondrian developer mailing list <mondrian at pentaho.org>
> *Subject:* Re: [Mondrian] How to dynamically group multiple range on
> dimension
>
>
>
>  Hi, All:
>
>  I rewrite this question as below to avoid misunderstanding.
>
>  I have a dimension called number_dim which is a random number from 0 to
> MAX integer and a measure called sum_count which is sum of all count.
>
>  I have following two MDX query and get same result listing all the
> sum_count for different number.
>
>
>
> SELECT
>
> NON EMPTY {[Measures].[sum_count]} ON COLUMNS,
>
> NON EMPTY {[number_dim].[count_hiera].[number].Members } ON ROWS
>
> FROM [my_cube]
>
>
>
>
>
> SELECT
>
> NON EMPTY {[Measures].[sum_count]} ON COLUMNS,
>
> NON EMPTY {
>
> [number_dim].[count_hiera].[number].&[1] :
> [number_dim].[count_hiera].[number].&[100],
>
> [number_dim].[count_hiera].[number].&[101] :
> [number_dim].[count_hiera].[number].&[400],
>
> [number_dim].[count_hiera].[number].&[1000] :
> [number_dim].[count_hiera].[number].&[2000]
>
>  } ON ROWS
>
> FROM [my_cube]
>
>
>
> number   sum_count
>
> 21           40
>
> 99           15
>
> 127         25
>
> 350         30
>
> 1380       20
>
> 1453       20
>
>
>
> ----------------
>
> I was asked to get table below which the sum_count can be grouped by range
> of number_dim dimension. the group number could be changed at MDX.
>
> For example, we can group it to 2, 3, 4 or any number groups.
>
>
>
> ------------------
>
> number   sum_count
>
> 1-100       55
>
> 101-400     55
>
> 1001-2000   40
>
>
>
> With my understanding, I can not add a new level on number_dim to group by
> level, because the number of groups could be changed, and how to mapping
> number to group is dynamical.
>
> I might need some code similar to
>
>
> http://www.ssas-info.com/analysis-services-faq/27-mdx/915-how-do-i-group-dimension-members-dynamically-in-mdx
>
> Your any help is highly appreciated.
>
> Sincerely,
> Selina
>
>
>
> On Tue, Mar 15, 2016 at 5:51 PM, Selina Tech <swucareer99 at gmail.com>
> wrote:
>
> Hi, Tom:
>
>          your reply was very encouraging. However I might mislead this
> question. The age dimension is just an example. I deal with a dimension
> with integer could be any number from 0 to MAX of integer. I am asked to
> group integer by 5 groups.  I need to dynamically to group them.
>
> I might need some code similar to
>
>
> http://www.ssas-info.com/analysis-services-faq/27-mdx/915-how-do-i-group-dimension-members-dynamically-in-mdx
>
>        Your any help is highly appreciated.
>
> Sincerely,
> Selina
>
>
>
> On Tue, Mar 15, 2016 at 5:27 PM, Tom Barber <tom at analytical-labs.com>
> wrote:
>
> Hi
>
> I'd normally create another "bucket" column in my dimension that is
> populated by the value you want 20-29 or whatever. Then just create a 2nd
> hierarchy in the schema that uses that column instead of the actual year.
>
> Tom
>
> On 16 Mar 2016 00:15, "Selina Tech" <swucareer99 at gmail.com> wrote:
>
> Dear All:
>
>   I have a question on "How to dynamically group multiple range on
> dimension".
>
>
>
>  I have a dimension called age_dim and a measure called sum_count which is
> sum of all count.  I have following two MDX query and get same result
> listing all the sum_count for different ages.
>
>
>
> SELECT
>
> NON EMPTY {[Measures].[sum_count]} ON COLUMNS,
>
> NON EMPTY {[age_dim].[count_hiera].[age].Members } ON ROWS
>
> FROM [my_cube]
>
>
>
>
>
> SELECT
>
> NON EMPTY {[Measures].[sum_count]} ON COLUMNS,
>
> NON EMPTY {
>
> [age_dim].[count_hiera].[age].&[20] : [age_dim].[count_hiera].[age].&[29],
>
> [age_dim].[count_hiera].[age].&[30] : [age_dim].[count_hiera].[age].&[39],
>
> [age_dim].[count_hiera].[age].&[40] : [age_dim].[count_hiera].[age].&[49]
>
>  } ON ROWS
>
> FROM [my_cube]
>
>
>
> age   sum_count
>
> 21     40
>
> 25     15
>
> 27     25
>
> 35     30
>
> 38     20
>
> 45     20
>
> 47     10
>
>
>
>
>
> ----------------
>
> However I like to get table below which the sum_count can be grouped by
> range of age dimension. Any Help would be great!
>
>
>
> ------------------
>
> age   sum_count
>
> 20-29     80
>
> 30-39     50
>
> 40-49     30
>
>
>
>
>
> _______________________________________________
> 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
>
>
>
>
>
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20160316/054e6496/attachment-0001.html 


More information about the Mondrian mailing list