Sql-server – SSAS Calculated Field – rolling sum, for a year of days

computed-columndata-warehousemdxsql serverssas

I have a fact table storing employee occurrences incurred day over day.
I need to have a running sum over the past year of an employee's life. I had, on first pass, done this on ETL, making this "Acitve Occurrences" Measure's AggregateFunction set to LastChild.
The problem happens as there can be more then one "entry" in a day. "Entry" is also a measure with its AggregateFunction set to LastChild. The result is that the occurrences still sum in instances with multiple "entry".

All of this feels terribly wrong. It screams to me that the "Active Occurrences" (the 365 day rolling sum) should be a calculation instead of done in the ETL. The problem is my lack of knowledge with MDX, at this point.

What would a calculation for this look like?

The reason why I would need the individual entries in the day, is that they have a reference to a type/reason dimension. Flattening the figures to the single day would lose those individual references.

Thank you in advance.

Best Answer

So the calculated member I came up with is:

sum(
    {ParallelPeriod([Calendar].[Fiscal Calendar].[Date],365,[Calendar].[Fiscal Calendar].CurrentMember):[Calendar].[Fiscal Calendar].CurrentMember},
    [Measures].[Occurrences Received]
)

simple enough, I guess. It could have been the ParallelPeriod for the Year level and 1 though I guess as this is a Fiscal Calendar 365 members at the day level needs to be used.

Related Solutions

Where should one put indexes in a time dimension table

You likely won't run into the issues of write problems, as I assume this would be something created once (or once per year), and then not touched.

But using an index will likely be a hinderance if you're searching by week ... The problem is, if the index is used, it might scan that first, and then grab each record out of the table individually, which when you're pulling out more than about 5-20% of the records, it's typically faster to do a full table scan, and then drop the records you don't care about.

I don't know of any major RDBMSes that don't optimize for this when it's well-distributed data. If it's not well distributed (eg, one of the values in a column occurs 95% of the time, but there's also other possible values), you may have to compute histograms on the table and not use a placeholder for the value when searching, so that the query optimizer has the value being searched for when generating the execution plan.

I'd likely not index day of week. I'd check my database's documentation to see what their tradeoff is for indexed reads vs. full table scans to see if I'd index the day of the month or month of the year. I'd likely index DOY/day of year if present (which sounds like it's your unique index, anyway)

Sql-server – MDX – Sum values greater than then a median calculated measure

You need first to define a measure that will compte the median price across the siblings:

[Measures].[Siblings_median_price] 
AS 
MEDIAN([DimItems].[Dim Items Id].CurrentMember.Siblings, [Measures].[Price])

The following measure should return what you want:

[Measures].[Sum_below_median] 
AS 
SUM(
     [DimItems].[Dim Items Id].CurrentMember.Children
   , IIF([Measures].[Price] < [Measures].[Siblings_median_price]
   , [Measures].[Quantity], Null)
)

Best Answer

Related Solutions

Where should one put indexes in a time dimension table

Sql-server – MDX – Sum values greater than then a median calculated measure

Related Question