help! with averaging within a cube

MS SQL SERVER

    Sponsored Links

    Next

  • 1. DTS - updating Dimensions.
    Hi All, I am creating DTS for my OLAP cube and want to know how u guys do updates to the existing dimensions and fact tables. In my case , I am using a running number as the id for all dimension tables so deleting and inserting new row is not possible. But I have to check and update dimension properties (say contact no in case of customer dimension), there may b very few or almost no changes but I have to check for it. Just wondering how u people do it. and what about the facts .... if ne of the amount is appended or changed ? plz guide me on this . thanks in advance. kshitij.
  • 2. OLAP cube build failed - Project Server 2002
    I have received the following error message when trying to build an OLAP cube in MS Project Server 2002: The cube scheduled to be built on 04/04/2005 at 02:12 failed. (-2147221384) Detected two sibling members with the same name: (Level 02). Can anybody shed any light on what this error means. Thanks

help! with averaging within a cube

Postby Neil Evans-Mudie » Sun, 24 Jul 2005 00:09:15 GMT

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:  XXXX@XXXXX.COM  address is a spam sink
If you wish to email me, try neilevans underscore mudie at hotmail dot com
w:  http://www.**--****.com/ 



Re: help! with averaging within a cube

Postby Andrew A via SQLMonster.com » Sun, 24 Jul 2005 00:37:16 GMT

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.**--****.com/ 

Similar Threads:

1.Help with Calculating Average in Cube

I'm new at OLAP. There is a cube already deisgned with following time/
date dimensions.
[Date] Dimension:
*Year
**Quarter
***Month
****Date

[Time] Dimesion:
*Hour

there is a measure which counts total number calls [Calls].

What I need to do is that depending what date that user chooses(could
be multiple days or single month),
calculate average calls for each hour.

What is the best way to achieve this goal?
Or since I have already had total number of calls working, is it
possible to create a measeure which gets number of days from [Date]
Dimension?


Thanks in advance



2.Help with Calculating Moving Average in Cube

Sorry if this is a simple question, but I'm very new at OLAP.

In SQL 2005 I am trying to calculate a 12-month moving average for a measure 
in my cube and I'm having a little trouble.  The average seems to reset 
itself as it crosses boundaries in the time hierarchy.

I'm using the following calculation in the cube to get my 12-month rolling 
average:

Avg ([Time].[CalendarMonth].CurrentMember.Lag(12) :
[Time].[CalendarMonth].CurrentMember,
[Measures].[Revenue Actual])

My cube looks like this:

Time Dimension:
*CalendarYear
**CalendarQuarter
***CalendarMonth

Fact_Revenue
Fulldate, Program_Name, Revenue_Actual, Revenue_Plan


When calculating the averages, it resets itself as soon as it passes a YEAR 
or QUARTER boundary.  For example, if I look at (2004, Q1, Month 3) it will 
show a correct average for only the 2004 Q1 Months 1,2 and 3.  It is not 
able to figure in the averages of values that fall in a different quarter or 
year.

I'm not sure if this is an error with my MDX expression, my Time dimension, 
or what.  Does anyone have any suggestions on how to do this?

Thanks,

-s




3.switch cubes within virtual cube

i want to be able to switch the physical cubes that a virtual cube is
looking at (same structure etc) , via DSO ... has anbody done this ?

the mdstores objects are read only , how does analysis manager do it  does
it recreate the cube on the fly when you change structure ?

any ideas !!

mark


4.SQL Average Hourly Value within Time Period

I would like to modify the SQL statement listed below to 
output the average value for FullScan_Sec over the 
date_time interval BETWEEN '20040711' and '20040801'
with a single value for each hour. Each average hourly 
value over the 3 week interval. 

Thanks,

Mark

Output Wanted:

Date_Time                 FullScan_Sec
'2004-08-01 01:00:000'    234.7 
'2004-08-01 02:00:000'    246.4
'2004-08-01 03:00:000'    345.2 
.
.
'2004-07-11 23:00:000'    312.6


select dateadd(hour,datediff(hour,'20040801',
[date_Time]),'20040801') as HourStart,
  
avg([FullScan_Sec]) as FullScan
from KLRP_New

where date_time BETWEEN '20040711' and '20040801'
group by dateadd(hour,datediff(hour,'20040801',
[date_Time]),'20040801')

order by dateadd(hour,datediff(hour,'20040801',
[date_Time]),'20040801')

5.how to calculate average within a year before grouping by year

I have two tables, one listing events, and another listing documents 
published about the event. I would like to calculate the average lag 
between event and publication for each year, and then present the data 
grouped by year. I have got to this point, but the averages are coming 
out way too large, so I assume that the average function isn't working:

SELECT AVG(tbldocuments.datDate - tblevents.datDate) AS timelag,
YEAR(tblevents.datDate) AS year
FROM tblevents
INNER JOIN tbldocuments
ON tblevents.eventID = tbldocuments.eventID
GROUP BY year
ORDER BY year ASC

Can anyone help me with this?

6. Calculating averages in a cube

7. Calculate average in a multidimensional cube

8. The Average in SSAS Cubes



Return to MS SQL SERVER

 

Who is online

Users browsing this forum: No registered users and 43 guest