Guys,

Hi.

I have a cube defined as

Dimension = Hours: All (%root%) | HourType (Core (8; 9... 16; 17); Non-core
(0... 7 & 18... 23)) | Hour (0; 1; 2... 22; 23)
Measure = Value

My dimension comes from a table with records like: {id = 1; HourType =
Non-core; Hour = 0}{id = 9; HourType = Core; Hour = 8}{id = 24; HourType =
Non-core; Hour = 23}

My measure(s) come from the following query: SELECT datepart(hour,
(cast(LEFT(valuedatetime, 23) AS datetime))) AS [hour], avg([value]) FROM
data GROUP BY datepart(hour, (cast(LEFT(valuedatetime, 23) AS datetime)))

(See below for example records, recordsets.)

I'd like to know how to create my cube so that the aggregation from a
specific hour to its 'HourType' and then up to the root 'All' is an AVERAGE
and not a sum of the value. This is because my measure select query from my
OLTP source data creates the value as an average as it aggregates as can be
seen from the crosstab query shown above. The cube wizard seems only to
allow aggregation by sum and not average.

Any help much appreciated! Tia.

Example measure recordset:
hour value
0 48700831.013689607
15 49205704.22667513
13 49338602.579925902
11 49442512.09335E-3
9 49465806.060322247
7 49663991.581225179
22 49040337.596350469
5 49629662.183885343
20 49163333.886821E-4
3 49716064.000141233
18 49163496.148747765
1 49847182.66664435
16 49112099.019549757
14 49248461.170634501
12 49420731.2105E-5
10 49507416.92865441
8 49483370.917596027
23 48567972.204324357
6 49642994.763104424
21 49168220.371349826
4 49662512.232045554
19 49165994.604601152
2 49769833.247441E-9
17 49141496.53417518

Cheers,

Neil Evans-Mudie
-. . .. .-.. / .----. ... --- -. .. -.-. .----. / . ...- .- -.
.... -....- -- ..- -.. .. .

e: My (AT) myorg (DOT) com address is a spam sink
If you wish to email me, try neilevans underscore mudie at hotmail dot com
w: http://groups.msn.com/TheEvansMudieF...ew.msnw?&pps=k

Hi,

can try doing this:

Add another meassure called [Count HourType] with Properties/Aggregation
Function select COUNT
Your first meassure can name [Sum HourType]

next add a calculated meassure:

iif(
isleaf([Yourdimension].currentmember),
[Sum HourType],
[Measures].[Sum HourType] / [Measures].[Count HourType]
)

Hope can help!


--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums...-olap/200507/1