US and Worldwide: +1 (866) 660-7555
Results 1 to 10 of 10

Thread: Average of Last 90 days data, but for each hour samples in a day

  1. #1

    Default Average of Last 90 days data, but for each hour samples in a day

    I have a measure as 'power' rollup as SUM, Time dimension as Year->Month->Day->Hour, and Location Dimension.
    We are storing at hour level of power data.
    Now we require for a particular location and date, last 90 days average power for each hour. It should display 24 columns with average last 90 days power_total for each hour.
    The following query gives only that date's power for each hour for a location1. But I need last 90days average power for each hour in a date and location. How to do this requirement?
    SELECT
    {Hierarchize({[Time.Monthly].[2013].[9].[20].Children})} ON COLUMNS,
    NON EMPTY {CROSSJOIN({Hierarchize({[Location].[Location1]})},{[Measures].[power]})} ON ROWS
    FROM [Power_Trend]

    Location MeasuresLevel 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
    Location1 Power 60.00 60.00 60.00 60.00 60.00 60.00 60.00 60.00 60.00 60.00 70.17 77.17 76.50 76.17 71.33 72.67 72.00 72.83 72.00 72.00 60.33 50.00 50.00 50.00
    I am pretty new to MDX.
    Please help me out.
    -Prakash

  2. #2
    Join Date
    Jan 2013
    Posts
    448

    Default

    Just to make sure I understand, you're looking to get the average over the past 90 days by hour, right? I.e. the average power consumption at 8:00 over the the last 90 days. Is that it?

    That could be tricky to express in MDX. The ParallelPeriod() function works well to bring back the corresponding time member from a different part of the hierarchy, but that's just *one* member--it doesn't return a set. It seems like you really need a (plural) ParallelPeriods() function that can return the set going back 90 days, then average that set up, but unfortunately that doesn't exist.

    If you can make changes to your schema, one option could be to add a new "Hours" dimension, along with an calculated measure that averages the preceding 90 days. You could then get the average for the 90 days broken down by individual hour. E.g.

    with member measures.[avgPowerPast90Days] 'avg(tail(periodstodate([Time.Monthly].[Year], [Time.Montly].[2013].[9].[20]),90), measures.[power])'
    select non empty [Hour].[Hour].members on 0, measures.[avgPowerPast90Days] on 1 from [Power_Trend]

    HTH

  3. #3

    Default

    Just to make sure I understand, you're looking to get the average over the past 90 days by hour, right? I.e. the average power consumption at 8:00 over the the last 90 days. Is that it? ---> That is correct.

    I have a option to change schema. But 90 days is a variable. It can change to any number any time. So I can not calculate average before hand. That is where we have trouble.

  4. #4
    Join Date
    Jan 2013
    Posts
    448

    Default

    I think you could parameterize an average calculation like the one below to allow a variable number of days

    avg(tail(periodstodate([Time.Monthly].[Year], [Time.Montly].[2013].[9].[20]),90), measures.[power])

    The trick would still be to breakout [Hour] as a separate dimension, to allow placing it on opposite axis, or crossjoining.

  5. #5

    Default

    It worked. Thanks very much. I have created new hierarchy with only [Hour] level and executed. It worked. Thanks very much.

    WITH MEMBER [Measures].[avgPowerPast90Days] AS 'avg(tail(PeriodsToDate([Time.Monthly].[Year], [Time.Monthly].[2013].[9].[20]),90),[Measures].[Power])'
    SELECT NON EMPTY [Time.Hourly].[Hour].members ON COLUMNS , [Measures].[avgPowerPast90Days] ON ROWS from [Power_Trend]

    Thanks very much.

  6. #6
    Join Date
    Jan 2013
    Posts
    448

    Default

    Awesome. Glad to help.

  7. #7

    Default

    I did some research on PeriodsToDate function. It has some flaws; as the 1st parameter is year, it will find set in the same year. So before March 31, it wont give correct result for 90 days. So after taking your hint on separate Hours dimension, I am able to find the average using range query as below. It works fine and gave the correct result.
    But I have an issue for range query.
    If there is no data for lagged days, then no result, even if data exists for 88 lagged days. This is very generic issue for range queries. Is there any solution?


    WITH MEMBER [Measures].[avgPowerPast90Days] AS 'avg({[Time.Monthly].[2013].[10].[3].Lag(89):[Time.Monthly].[2013].[10].[3]})',FORMAT_STRING = "###.00"
    SELECT NON EMPTY [Time.Hourly].[Hour].members ON COLUMNS ,
    NON EMPTY {CROSSJOIN({Hierarchize({[BuildingHierarchy].[11111]})},{[Measures].[avgPowerPast90Days]})}
    ON ROWS from [Power_Trend]

  8. #8
    Join Date
    Jan 2013
    Posts
    448

    Default

    Are you talking about the case where there is no dimension member on one side of the range? I.e. if the first month in your time dimension is [Feb 2007], and you do a [Mar 2007].Lag(2)?

  9. #9

    Default

    Are you talking about the case where there is no dimension member on one side of the range? - Yes. I am talking about that

  10. #10
    Join Date
    Jan 2013
    Posts
    448

    Default

    You can wrap your expression in a conditional to get either than last 90 days, or the last 90-N days if there aren't a full 90 days. Something like

    iif([Time.Monthly].[2013].[10].[3].Lag(90) IS NULL,
    avg({[Time.Monthly].[2013].[10].[3].FirstSibling:[Time.Monthly].[2013].[10].[3]}),
    avg({[Time.Monthly].[2013].[10].[3].Lag(90):[Time.Monthly].[2013].[10].[3]})
    )

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •