Hi.

I'd like to write an MDX query to compute a 'binning' of a measure as
a new dimension. Assume I have a measure "Profit". I'd like to create
a new dimension with the members:

All Profit
"$0K - $10K"
"$10K - $20K"
etc.

Ideally, I'd like the bins of profit to be a new dimension for the
purpose of my query. However, I can only seem to create calculated
members, not calculated dimensions.

Question 1: Am I correct that there isn't any syntax to define a new
dimension for the scope of a query?

Assume I decide to add the bins of profit as members to an existing
dimension. I'll chose to add the bins to a dimension other than
"Measures" because I'll want to be able to aggregate my other measures
(e.g. Sales, COGS) based on the profit bins.

Question 2: Will I have to define each bin explicitly? Assume I decide
to add the members to dimension X as a bin of dimension X at level Y:

CREATE MEMBER [X].[$0K - $10K] AS
' Sum(Filter( [X].[Y].Members,
[Measures].[Profit] >= 0 and [Measures].[Profit] <= 10000) '
CREATE MEMBER [X].[$10K - $20K] AS
' Sum(Filter( [X].[Y].Members,
[Measures].[Profit] >= 10000 and [Measures].[Profit] <= 20000)
'
etc.

thanks for any help.

Chris

Hi Chris,

No, you can't define a new dimension for the scope of a query unfortunately.
It would be a useful feature though. I would advise creating a new dimension
manually, following the same procedure as you would when creating a 'Time
Utility Dimension' (search for the many posts on this topic in this ng for
more info here), and then creating your buckets as calculated members hanging
underneath the All Member of the newly created dimension.

However, if you have too many buckets to define manually and you don't mind
getting your hands dirty with some complex, nasty, hacky MDX, here's a way to
define buckets dynamically (using Foodmart 2000) -

WITH
//FIRST, CHOOSE A DIMENSION LEVEL WHICH YOU AREN'T USING IN YOUR QUERIES AND
WHICH HAS MORE
//MEMBERS THAN THE NUMBER OF BUCKETS YOU WANT. THEN DEFINE A SET WITH THE
SAME NUMBER
//OF MEMBERS IN AS THE NUMBER OF BUCKETS DESIRED
SET BUCKETHACK AS 'HEAD([Promotions].[Promotion Name].MEMBERS, 10)'
//NEXT, CREATE A CALCULATED MEMBER THAT FINDS THE RANK OF THE CURRENTMEMBER
WITHIN THE SET JUST CREATED
MEMBER MEASURES.RANKING AS 'RANK([Promotions].CURRENTMEMBER, BUCKETHACK) - 1'
//NOW, USE THE UNDOCUMENTED CREATEPROPERTYSET FUNCTION TO CREATE A SET OF
CALCULATED MEMBERS WHICH REPRESENT
//THE BUCKETS. THE NAME OF EACH CALCULATED MEMBER IS DERIVED FROM THE RANK
OF EACH MEMBER OF THE BUCKETHACK SET
//I AM USING THE CUSTOMERS DIMENSION HERE, BUT YOU SHOULD USE A NEW
DIMENSION CREATED SPECIFICALLY FOR THIS PURPOSE
//WITH ONLY AN ALL MEMBER
SET MYBUCKETS AS 'CREATEPROPERTYSET([Customers].[All Customers], BUCKETHACK,
"$" + CSTR(MEASURES.RANKING) + "K - $" + CSTR(MEASURES.RANKING+1) + "K")'
//THEN CREATE A NEW CALCULATED MEASURE WHICH FINDS THE RANK OF THE CURRENT
BUCKET WITHIN THE LIST OF ALL BUCKETS
//TO MAKE THE MDX EASIER TO READ
MEMBER MEASURES.RANKING2 AS 'RANK(CUSTOMERS.CURRENTMEMBER, MYBUCKETS)-1'
//AND FINALLY CREATE A NEW CALCULCATED MEASURE WHICH SUMS UP PRODUCT
CATEGORIES DEPENDING THE CURRENTMEMBER
//ON THE BUCKET DIMENSION
MEMBER MEASURES.[UNIT SALES SUMMED] AS 'SUM(
FILTER([Product].[Product Category].MEMBERS,
(MEASURES.[UNIT SALES], [Customers].[All
Customers])>=(MEASURES.RANKING2*1000)
AND
(MEASURES.[UNIT SALES], [Customers].[All
Customers])<((MEASURES.RANKING2+1)*1000)
)
,(MEASURES.[UNIT SALES], [Customers].[All Customers])
)'
//JUST TO SHOW WHICH MEMBERS FALL INTO WHICH BUCKETS
MEMBER MEASURES.[BUCKET MEMBERS] AS 'SETTOSTR(
FILTER([Product].[Product Category].MEMBERS,
(MEASURES.[UNIT SALES], [Customers].[All
Customers])>=(MEASURES.RANKING2*1000)
AND
(MEASURES.[UNIT SALES], [Customers].[All
Customers])<((MEASURES.RANKING2+1)*1000)
)
)'
//THE DEMO QUERY
SELECT {MEASURES.RANKING2, MEASURES.[UNIT SALES SUMMED], MEASURES.[BUCKET
MEMBERS]} ON 0,
MYBUCKETS ON 1
FROM SALES


HTH,

Chris


"Chris Stolte" wrote:

I forgot that you can define a dimension for the scope of a session (although
not a query, I don't think) using the CREATE SESSION CUBE syntax, described
here:
http://www.microsoft.com/technet/pro.../anserddl.mspx

....but, as I said, you're much better off just building the dimension and
buckets yourself manually.

"Chris Webb" wrote: