SSAS/MDX averages over multiple time periods

Question

How do I get averages for multiple time frames in the same result row in a SSAS/MDX query?

Requirements have changed and a report I wrote as a SSRS/SQL procedure based on a transactional DB has now moved to a data warehouse with SSAS cube. SSAS is new ground for me. I have spent several days trying to work my way through MDX after quickly realizing the drag and drop approach wasn't going to get me what I needed.

The scenario: logging performance time (specifically duration in milliseconds) of key pieces and processes of software in a large system. The idea of the report is to show worst performers based on historical precedence with an eye towards finding problematic areas when users say "the system is running slow".

The report columns:

Function (actually several columns here but I'm sure I can get this working with them once I get one working)
avg duration over last year
avg duration over 1 week starting one year ago
avg duration over last 6 months
avg duration over 1 week starting 6 months ago
avg duration over last month
avg duration over 1 week starting one month ago
avg duration over last week
avg duration over last day
avg duration selected day
% decrease -> this is calculated in SSRS as the worst difference across all averages

The simplified cube - trying to follow recommendations from MS articles

Activities.Duration MS
Activities.Activities Count
Functions.Fuction Key
Functions.Function Desc
Dates.Date Key (note that the Date dimension has no hierarchy at this point)
Dates.Date Date

I got as far as the following and then started playing with the AVG() function and had little success.

WITH MEMBER [Measures].[Duration MS Avg] AS [Measures].[Duration Ms]/[Measures].[Activities Count]
SELECT
                     { [Measures].[Duration Ms], [Measures].[Activities Count], [Measures].[Duration MS Avg] } ON COLUMNS, 
                     { ([Functions].[Function Desc].[Function Desc].ALLMEMBERS )} ON ROWS 
FROM ( SELECT ( {[Dates].[Date Date].&[2016-05-24T00:00:00]} ) ON COLUMNS 
FROM [DW]) 
WHERE ( [Dates].[Date Date].&[2016-05-24T00:00:00] )

Is this type of query even possible? Am I abusing SSAS?

You need a Hierarchy in your time dimension to achieve this more easly.More than one actually, one for Year->Month->Date and one for Year->Week->Date — mxix
So SSAS newbie question: In the MS whitepaper it described star vs snowflake and the snowflake showed 2 potential hierarchies of the Date dimension in the basis database (in their case actual date vs fiscal date). I also found somewhere that the Date dimension can just be marked as a Time dimension when creating the cube. Is one way better than the other? We have flexibility right now to get it right. Maybe a favourite reference link? I haven't found a really good one yet. — SMM
I agree with @mxix's idea. Defining a proper date hierarchy is the way to go. After you do that, here's a very good article to get you started - sqlmag.com/sql-server-analysis-services/… — SouravA
Thanks. I'll have a look at this once I get some "lower priority" time. — SMM

mxix mxix · Accepted Answer · 2016-06-22T16:48:22

With the time hierarchies it's harder to "travel" along the dates.

But here is a simple example for 2 of the measures you want as an example.

WITH 
MEMBER [Measures].[Duration MS Avg] 
AS 
DIVIDE(
    [Measures].[Duration Ms]
    ,[Measures].[Activities Count]
)
MEMBER [Measures].[Duration MS Avg Over Last Day]
AS
DIVIDE(
    (
        [Dates].[Date Date].CURRENTMEMBER.LAG(1)
        ,[Measures].[Duration Ms]
    )
    ,(
        [Dates].[Date Date].CURRENTMEMBER.LAG(1)
        ,[Measures].[Activities Count]
    )
)
MEMBER [Measures].[Duration MS Avg Over Last Week]--Last 7 days
AS
DIVIDE(
    AGGREGATE(
        [Dates].[Date Date].CURRENTMEMBER.LAG(7):[Dates].[Date Date].CURRENTMEMBER
        ,[Measures].[Duration Ms]
    )
    ,AGGREGATE(
        [Dates].[Date Date].CURRENTMEMBER.LAG(7):[Dates].[Date Date].CURRENTMEMBER
        ,[Measures].[Activities Count]
    )
)
SELECT
{ 
    [Measures].[Duration Ms], 
    [Measures].[Activities Count], 
    [Measures].[Duration MS Avg],
    [Measures].[Duration MS Avg Over Last Week]
} ON COLUMNS, 
{ 
    (
        [Functions].[Function Desc].[Function Desc].ALLMEMBERS 
    )
} ON ROWS 
FROM ( 
    SELECT 
        ( {[Dates].[Date Date].&[2016-05-24T00:00:00]} ) ON COLUMNS 
    FROM [DW]
) 
WHERE 
    ( [Dates].[Date Date].&[2016-05-24T00:00:00] )

SSAS/MDX averages over multiple time periods

1 Answers