MDX segmentation

#1

For every account I need to segment them into 3 groups (A, B and C accounts).
I need to take the total of the accounts and then figure out the value of the
top 70% of the accounts. Then I need to take each account and sum them until
that 70% value is met, these would be clasifed as my A accounts.
Then 25% and then the last 5%.
Example:
Last 12 months revenue for all accounts = $151,125
A Accounts = 151,125 * .70 = 105,788, I will need to list each account here
that makes up the top 70%.
B Accounts = 151,125 * .25 = 37,781
C Accounts = 151,125 * .05 = 7,556

What I did was use TopPercent 70 to get my top 70 percent, then for the 25%
I had to take the TOP 95% and remove the top 70 and the same with the
remaining 5%. This process is very memory intensive and I was wondering if
there is a better way to get these groupings.

#2

Now I cannot test this so this is just an idea.

How about using Order and Subset if you set the starting point and count?

Ohoo

"appdevtech" <appdevtech (AT) online (DOT) nospam> wrote

Quote:

For every account I need to segment them into 3 groups (A, B and C
accounts).
I need to take the total of the accounts and then figure out the value of
the
top 70% of the accounts. Then I need to take each account and sum them
until
that 70% value is met, these would be clasifed as my A accounts.
Then 25% and then the last 5%.
Example:
Last 12 months revenue for all accounts = $151,125
A Accounts = 151,125 * .70 = 105,788, I will need to list each account
here
that makes up the top 70%.
B Accounts = 151,125 * .25 = 37,781
C Accounts = 151,125 * .05 = 7,556

What I did was use TopPercent 70 to get my top 70 percent, then for the
25%
I had to take the TOP 95% and remove the top 70 and the same with the
remaining 5%. This process is very memory intensive and I was wondering
if
there is a better way to get these groupings.

#3

I think it's better define Named Set first before applying Subset.

"Ohjoo Kwon" <ojkwon (AT) olap (DOT) co.kr> wrote

Quote:

Now I cannot test this so this is just an idea.

How about using Order and Subset if you set the starting point and count?

Ohoo

"appdevtech" <appdevtech (AT) online (DOT) nospam> wrote in message
news:C3A87E3C-F999-416B-BB3C-5EF8AAE03EB5 (AT) microsoft (DOT) com...
For every account I need to segment them into 3 groups (A, B and C
accounts).
I need to take the total of the accounts and then figure out the value of
the
top 70% of the accounts. Then I need to take each account and sum them
until
that 70% value is met, these would be clasifed as my A accounts.
Then 25% and then the last 5%.
Example:
Last 12 months revenue for all accounts = $151,125
A Accounts = 151,125 * .70 = 105,788, I will need to list each account
here
that makes up the top 70%.
B Accounts = 151,125 * .25 = 37,781
C Accounts = 151,125 * .05 = 7,556

What I did was use TopPercent 70 to get my top 70 percent, then for the
25%
I had to take the TOP 95% and remove the top 70 and the same with the
remaining 5%. This process is very memory intensive and I was wondering
if
there is a better way to get these groupings.

#4

I think it's better define Named Set first before applying Subset.

"Ohjoo Kwon" <ojkwon (AT) olap (DOT) co.kr> wrote

Quote:

Now I cannot test this so this is just an idea.

How about using Order and Subset if you set the starting point and count?

Ohoo

"appdevtech" <appdevtech (AT) online (DOT) nospam> wrote in message
news:C3A87E3C-F999-416B-BB3C-5EF8AAE03EB5 (AT) microsoft (DOT) com...
For every account I need to segment them into 3 groups (A, B and C
accounts).
I need to take the total of the accounts and then figure out the value of
the
top 70% of the accounts. Then I need to take each account and sum them
until
that 70% value is met, these would be clasifed as my A accounts.
Then 25% and then the last 5%.
Example:
Last 12 months revenue for all accounts = $151,125
A Accounts = 151,125 * .70 = 105,788, I will need to list each account
here
that makes up the top 70%.
B Accounts = 151,125 * .25 = 37,781
C Accounts = 151,125 * .05 = 7,556

What I did was use TopPercent 70 to get my top 70 percent, then for the
25%
I had to take the TOP 95% and remove the top 70 and the same with the
remaining 5%. This process is very memory intensive and I was wondering
if
there is a better way to get these groupings.

#5

My idea was creating and reusing named sets as following,

CREATE SET CurrentCube.SortedSet AS Order(<TargetSet>, <NumExpr>, BDESC)
CREATE SET CurrentCube.Top70 AS TopPercent(SortedSet, 70, <NumExpr>)
CREATE SET CurrentCube.Bottom5 AS BottomPercent(SortedSet, 5, <NumExpr>)
CREATE SET CurrentCube.70to95 AS Subset(SortedSet, Top70.Count,
SortedSet.Count - Top70.Count - Bottom5.Count)

I'm not sure this shows better performance, but wish this could help you.

Ohjoo

"appdevtech" <appdevtech (AT) online (DOT) nospam> wrote

Quote:

This is an example of what I have now:

CREATE MEMBER CURRENTCUBE.[Account].[Statement Parent Account].[A
Household]
AS sum(
TOPPERCENT([Account].[Statement Parent Account].[Statement Parent
Account],
70, [Measures].[T-12 Revenue])
),
VISIBLE = 1;
CREATE MEMBER CURRENTCUBE.[Account].[Statement Parent Account].[B
Household]
AS sum(
EXCEPT(
TOPPERCENT([Account].[Statement Parent Account].[Statement Parent
Account],
95, [Measures].[T-12 Revenue])
,
TOPPERCENT([Account].[Statement Parent Account].[Statement Parent
Account],
70, [Measures].[T-12 Revenue])
,ALL
)),
VISIBLE = 1;
CREATE MEMBER CURRENTCUBE.[Account].[Statement Parent Account].[C
Household]
AS sum(
EXCEPT(
union(
TOPPERCENT([Account].[Statement Parent Account].[Statement Parent
Account],100, [Measures].[T-12 Revenue])
,
[Account].[Statement Parent Account].[Statement Parent Account]
)
,
TOPPERCENT([Account].[Statement Parent Account].[Statement Parent
Account],
95, [Measures].[T-12 Revenue])
,ALL
)),
VISIBLE = 1;

I'm looking for an easier way or less memory intensice way to accomplish
this.

BTW T-12 Revenue is also a calculated member:

CREATE MEMBER CURRENTCUBE.[MEASURES].[T-12 Revenue]
AS case
when [Date].YQM.currentmember.level.name = "Month"
then [Measures].[Rep Commission12]
when [Date].[Month].currentmember.level.name = "Month"
then [Measures].[Rep Commission12]
else 0
end
/*
case
when [Date].YQM.currentmember.level.name = "Month"
then sum(LastPeriods(12, [Date].YQM.currentmember), [Measures].[Rep
Commission])
when [Date].[Month].currentmember.level.name = "Month"
then sum(LastPeriods(12, [Date].[Month].currentmember), [Measures].[Rep
Commission])
else 0
end
*/,
FORMAT_STRING = "#,0",
VISIBLE = 1

"Ohjoo Kwon" wrote:

I think it's better define Named Set first before applying Subset.

"Ohjoo Kwon" <ojkwon (AT) olap (DOT) co.kr> wrote in message
news:Ob7cjxBGHHA.4652 (AT) TK2MSFTNGP04 (DOT) phx.gbl...
Now I cannot test this so this is just an idea.

How about using Order and Subset if you set the starting point and
count?

Ohoo

"appdevtech" <appdevtech (AT) online (DOT) nospam> wrote in message
news:C3A87E3C-F999-416B-BB3C-5EF8AAE03EB5 (AT) microsoft (DOT) com...
For every account I need to segment them into 3 groups (A, B and C
accounts).
I need to take the total of the accounts and then figure out the value
of
the
top 70% of the accounts. Then I need to take each account and sum
them
until
that 70% value is met, these would be clasifed as my A accounts.
Then 25% and then the last 5%.
Example:
Last 12 months revenue for all accounts = $151,125
A Accounts = 151,125 * .70 = 105,788, I will need to list each account
here
that makes up the top 70%.
B Accounts = 151,125 * .25 = 37,781
C Accounts = 151,125 * .05 = 7,556

What I did was use TopPercent 70 to get my top 70 percent, then for
the
25%
I had to take the TOP 95% and remove the top 70 and the same with the
remaining 5%. This process is very memory intensive and I was
wondering
if
there is a better way to get these groupings.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

MDX segmentation

microsoft.public.sqlserver.olap microsoft.public.sqlserver.olap

MDX segmentation - 12-04-2006 , 03:02 PM

Re: MDX segmentation - 12-04-2006 , 09:20 PM

Re: MDX segmentation - 12-04-2006 , 10:02 PM

Re: MDX segmentation - 12-04-2006 , 10:02 PM

Re: MDX segmentation - 12-06-2006 , 09:21 AM