SSAS How to "infill" snapshot facts by repeating last non-empty value

Question

I have a cube with a typical snapshot structure and daily granularity (like inventory quantities). I would like to be able to remove some of the granular data from this cube, because we have something like 270,000,000 rows of source data, cube processing is slow, and there isn't a meaningful difference from one data point to the next, at the day level.

However, users want a graduated level of detail - daily detail for the recent past, then monthly or quarterly for older periods. Doing that would help the situation BUT - they also want charts that "appear" to show data for each data point and not have "holes" between one data point and the next.

So here's the question: if I have a cube with a snapshot fact table, and the table has daily values for the most recent 30 days, then monthly values for 6 months, then quarterly values for two years prior, is there any sane way to make output from the cube "spoof" the gaps, by repeating the last snapshot value for each "empty" day? In other words, if I deliver a chart over the whole time period, I want it to have plateaus that repeat the last non empty value across each gap in the data, but without incurring the storage penalty of keeping all those values.

Answer 1

You could in the cube use a MDX calculated measure for day level, which looks up the last available data point.

Not sure if that idea helps, but that's where I would start looking.

Answer 2

I am close on this. Came up with the following type of recursive expression, which seems to work (mostly). Tried to substitute in the ClosingPeriod() function to tidy it up, but that bit doesn't work:

/* Works! */
with member Measures.lastEstatementsCount as 
iif( 
    isleaf( [Date].[Calendar].currentmember ),

    iif(
        isempty([Measures].[_Add E Statements Count]),
        ( [Date].[Calendar].prevmember, Measures.[lastEstatementsCount] ),
        Measures.[_Add E Statements Count]
    ),

    ( 
        ( tail( descendants( [Date].[Calendar].currentmember ) ) ).item(0), 
            Measures.[lastEstatementsCount] 
    )
)
select 
    Measures.lastEstatementsCount on columns,
    [Date].[Calendar].[Month Name] on rows
from [EngagedMember];


/* Substituting ClosingPeriod() in the recursion doesn't for some reason */
with member Measures.lastEstatementsCount as 
iif( 
    isleaf( [Date].[Calendar].currentmember ),

    iif(
        isempty([Measures].[_Add E Statements Count]),
        ( [Date].[Calendar].prevmember, Measures.[lastEstatementsCount] ),
        Measures.[_Add E Statements Count]
    ),

    ( 
        ClosingPeriod( [Date].[Calendar].[Date], [Date].[Calendar].currentmember ), 
            Measures.[lastEstatementsCount] 
    )
)
select 
    Measures.lastEstatementsCount on columns,
    [Date].[Calendar].[Month Name] on rows
from [EngagedMember];