[Mondrian] How to dynamically group multiple range on dimension

Selina Tech swucareer99 at gmail.com
Tue Mar 15 21:23:55 EDT 2016


 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
>>
>>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20160315/cc0b33e8/attachment.html 


More information about the Mondrian mailing list