Hi all,
I have weather forecast data in a my measure group
the data warehouse source schema is
ForecastKey - bigint identity
ForecastMadeCalendarKey
ForecastForCalendarKey
SpatialLocationKey
RainfallMM - decimal - this is the measure I want to avg
natural key is Forecast date, forecast for date and location
The forecast information arrives daily and contains the forecast for the next 7 days. So each location + forecast for date pair will have 7 entries. One for each forecast made date.
the AverageOfChildren aggregate function seems to be doing a weird combination of both date dims to generate the average.
What I see when i browse the cube:
ForecastFor | Avg Rainfall | ForecastCount
20130414 | 5484.27122222222| 360459
and my query & results from hitting the database directly
select ForecastForCalendarKey
, COUNT(*) as [cnt]
, SUM(rainfallmm) as [sum]
, AVG(rainfallmm) as [avg]
, SUM(rainfallmm)/COUNT(distinct ForecastMadeCalendarKey) as [???]
from dbo.factClimateForecast
where ForecastForCalendarKey = 20130414
group by ForecastForCalendarKey
ForecastForCalendarKey cnt sum avg ???
---------------------- ------ ---------- --------- -----------
20130414 360459 49358.441 0.154048 5484.271222
It looks like SSAS is doing an average over both time dimensions even when only one is dragged through in the browser. I expect it to show the 'avg' value, but it's showing the weird sum/distinct count of completely unrelated time dim key.
What do i need to do to get ssas to give me the correct avg?
Adelaide, Australia
