SSAS2008R2: AverageOfChildren with 2 date dims (role playing) issues RRS feed

  • Question

  • Hi all,

    I have weather forecast data in a my measure group

    the data warehouse source schema is

     ForecastKey - bigint identity
     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

    Thursday, June 20, 2013 5:12 AM