[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