Analysis Services - Create Different Aggregation Rules for Different Measures

Analysis Services - Create Different Aggregation Rules for Different Measures

Hello.

How would I set the aggregation rules to have one measure roll-up by "rows and
columns" and a second measure in the same cube rollup JUST the rows?

It's a simple ROLAP cube, 2 dimensions based off the same relational table.  The
data is:

Cross-Sale item
Product Sold        Hat              shirt           coat            shoes
hat              xxxxxxxxxx      ( #1 ) ( #2 )   ( #1 ) ( #2 )   ( #1 ) ( #2 )
shirt            ( #1 ) ( #2 )   xxxxxxxxxxxxx   ( #1 ) ( #2 )   ( #1 ) ( #2 )
coat             ( #1 ) ( #2 )   ( #1 ) ( #2 )   xxxxxxxxxxx      (#1)   (#2)
shoes            ( #1 ) ( #2 )   ( #1 ) ( #2 )   ( #1 ) ( #2 )   xxxxxxxxxxxx

Where #1 = the number of Products sold (down the Y axis)
. . . #2 = the number of Cross-sale items sold.  ( How many people that bought a
hat ALSO bought a shirt, a coat, a pair of shoes, etc. )

To compound this, there are 3 levels of products, on both the X and the Y axes.
All products that appear on the X axis, also appear on the Y axis (that
intersection is represented by the 'xxxxxxxxxxx'.)

The problem arises that when the cube processes, it is rolling-up Measure #1
(Product Sold) on both the X and Y axes, and I would like that measure to roll on
JUST the Y axis.

Measure #2 (Cross-sale) can roll on both.

For extra credit:

How would I create Measure #3, which is (Measure#2 / Measure#1) presented as a %,
displayed at all levels of the cube?

Walt

Hello.

How would I set the aggregation rules to have one measure roll-up by "rows and
columns" and a second measure in the same cube rollup JUST the rows?

It's a simple ROLAP cube, 2 dimensions based off the same relational table.  The
data is:

Cross-Sale item
Product Sold        Hat              shirt           coat            shoes
hat              xxxxxxxxxx      ( #1 ) ( #2 )   ( #1 ) ( #2 )   ( #1 ) ( #2 )
shirt            ( #1 ) ( #2 )   xxxxxxxxxxxxx   ( #1 ) ( #2 )   ( #1 ) ( #2 )
coat             ( #1 ) ( #2 )   ( #1 ) ( #2 )   xxxxxxxxxxx      (#1)   (#2)
shoes            ( #1 ) ( #2 )   ( #1 ) ( #2 )   ( #1 ) ( #2 )   xxxxxxxxxxxx

Where #1 = the number of Products sold (down the Y axis)
. . . #2 = the number of Cross-sale items sold.  ( How many people that bought a
hat ALSO bought a shirt, a coat, a pair of shoes, etc. )

To compound this, there are 3 levels of products, on both the X and the Y axes.
All products that appear on the X axis, also appear on the Y axis (that
intersection is represented by the 'xxxxxxxxxxx'.)

The problem arises that when the cube processes, it is rolling-up Measure #1
(Product Sold) on both the X and Y axes, and I would like that measure to roll on
JUST the Y axis.

Measure #2 (Cross-sale) can roll on both.

For extra credit:

How would I create Measure #3, which is (Measure#2 / Measure#1) presented as a %,
displayed at all levels of the cube?