MDX count occurrences per hour over a time

Question

Looking to get an MDX query for SSAS to get the average Report Executions per hour over any time frame. I"m working with a cube that has SSRS metadata.... and i want to get average Executions per hour.
I figured i have to count how many hours are involved.... as many reports are only run until 5 PM.... so i can't do a straight 24 hour timeframe.

i got something like this so far.... but it's CONSISTENTLY dividing by all hours.... Also i'm already tracking the total counts of executions....

here's the mdx i have... the calc is working, but it keeps dividing everything by 16 for a specific day ... ie.... for 12PM.... it divides by the total transactions by 16 hours.... but the transaction only occurred twice....between 12-1PM. so it should divide by 2.....

   WITH MEMBER [Measures].[Hour Count] AS
        sum(
                [Execution End Time].[24 Hour Time].[Hour 24].MEMBERS,
                IIF([Measures].[Report Execution Count] >= 1,1,0))
        MEMBER [Measures].[Average Exec Per Hour] AS
                ([Measures].[Report Execution Count]/[Measures].[Hour Count])

select
  {[Measures].[Hour Count]
, [Measures].[Report Execution Count] as ['Transaction Count']
, [Measures].[Average Exec Per Hour]}
on Columns
        ,[Execution End Time].[24 Hour Time].[Hour 24]  on Rows
from [BICC DM]
where  ([Execution End Date].[Calendar Dates].[Date Key].&[20160707])

Data extract

an hour occurs once per hour so how would [Measures].[Hour Count] ever be more than 1 in that Data Extract you provided? — whytheq
the measure keeps a count of how many hours are involved in a particular transaction occurring. example. transaction occurs at 12 on monday... and 3pm on tuesday..... 2 hours are involved... so for that report there a 2 will be placed in that measure. — AlenKnight
but you have hours on the rows of your results - so for each hour there is by definition only one hour involved! — whytheq

SouravA SouravA · Accepted Answer · 2016-07-11T11:10:24

Change the definition of the first measure to below -

WITH MEMBER [Measures].[Hour Count] AS
IIF(
    ([Execution End Time].[24 Hour Time].CurrentMember,[Measures].[Report Execution Count])  >= 1,
    1,
    0
   )

The issue with your existing measure is the it is evaluating the value for a static set (i.e. [Execution End Time].[24 Hour Time].[Hour 24].MEMBERS). I guess what you want to do is evaluate it for the current hour instead.

MDX count occurrences per hour over a time

1 Answers