Hello. I wish to analyze network performance data. My source data contains
both date/time data as well as network availability data (expressed as a
percentage). For example:

date,device_name,avg_uptime
======================
01-jan-2004,device1,99.19%
02-jan-2004,device1,98.12%
etc.

If I import the data into Excel and generate a pivot table, I can summarize
my data with the following functions: min, max, count, average, sum, stdev,
etc. If I use the olap wizard, the only summary functions I can use are:
min, max, sum, and count - I do not have an average function.

Is there a way to obtain the "average" summary function in the Excel OLAP
wizard, given that my source data already contains averages? I do not wish
to import the data into an Excel workbook, as it will become large over time
(and limited in terms of the # rows a workbook can store).

TIA

If your input has the absolute Up time and Total Time then using Pivot
Calculated Formula to
calculate Average Uptime as Up Time / Total Time and presenting the Pivot
Table such as

Date on row, Average Uptime calculation in Data area, then the Grand Total
for columns is
your Average of Averages.


"Domenico Discepola" <domenico_discepola (AT) quadrachemicals (DOT) com> wrote in
message news:gfOTb.14475$Ja2.90175 (AT) nnrp1 (DOT) uunet.ca...

Thank you for your reply. Unfortunately, no absolute data exists - only
averages (see sample data in previous post).

"Dang" <mdang (AT) bigpond (DOT) com.au> wrote

Try this one :

If you know how avg_uptime is calculated, it could be like

avg_uptime = avg_uptime in seconds / (24 * 60 * 60) for the day

then calculate avg_uptime in seconds in a separate column as

avg_uptime in seconds = avg_uptime * 24 * 60 * 60 / 100

and put 24 * 60 * 60 in another column as Total Time


date device avg% avg_uptime total time
01-Jan-04 device1 99.19 85700.16 86400
02-Jan-04 device1 98.12 84775.68 86400


result is

Data
date device Sum of avg_uptime Sum of avg_of_avg_uptime
01-Jan-04 device1 85700.16 99.19%
02-Jan-04 device1 84775.68 98.12%
Grand Total 170475.84 98.66%

Eventually, you can remove the column Sum of avg_uptime

This solution only valid if
avg_uptime = avg_uptime in seconds / (24 * 60 * 60)

I forgot the Calulated Formula from Pivot Table which you must add using
Pivot/Calculated Formula Menu bar

avg_of_avg_uptime =avg_uptime/'total time'



"Dang" <mdang (AT) bigpond (DOT) com.au> wrote

Hello Domenico,

Those functions are available in the MDX language and can be used to
calculate the aggregated values of avg_uptime in the time and device
dimensions in the Analysis Manager Cube Editor in the properties via custom
rollup formulas - see the "Custom Rollup Formulas and Custom Member
Formulas" entry in the BOL.

HTH,
Helmut Knappe,
Business Intelligence Ware GmbH

"Domenico Discepola" <domenico_discepola (AT) quadrachemicals (DOT) com> schrieb im
Newsbeitrag news:gfOTb.14475$Ja2.90175 (AT) nnrp1 (DOT) uunet.ca...