Aggregation of recursive day-level calculations

Question

I'm trying to set up a cube calculation. My current remit is that I will receive exchange rates, and these will be sent at the start of the month. Sometimes they will change during the month, and I will receive a rate to use for that currency onwards.

The initial problem is that LastNonEmpty uses the granularity questioned, which means when I query by day most days do not have exchange rates, so the calculations fail. I replaced the exchange rate measure, initially with a recursive calculation, but later with the following:

WITH  
MEMBER [Measures].[EOD]
AS 
    Aggregate({NULL:[Date].[Calendar Y M D].CurrentMember}, [Measures].[End Of Day Rate])

So, when viewed on the date dimension, it will get the most recent exchange rate. This allows a daily calculation as follows:

MEMBER [Measures].[Converted]
AS
    SUM 
    ( 
        {[Currency].[Currency].[Currency] * [Date].[Calendar Y M D].CurrentMember},
        (
            (   
                [Measures].[Sales]
            )
            / [Measures].[EOD]
        )
    )

Lastly I use this in a query:

SELECT
{
    [Measures].[Converted]
}
ON COLUMNS,
NON EMPTY
{
    [Date].[Calendar Y M D].[Calendar Day].&[2010-Q3-08-01] : 
    [Date].[Calendar Y M D].[Calendar Day].&[2010-Q3-08-31] 
}
ON ROWS
FROM [Cube]

This all works fine, but ideally I'd prefer to query by year/month, as these are month-end reports. I can sum all this in the code tiers, but I'd rather have MDX that can do it itself.

The problem is that the calculations are done almost recursively at the day-level, then I'd like them rolled up to the month (Or even year) level.

I did try the currency conversion wizard, swapping the exchange rate for the time-aggregated one above, but attempting to browse the cube in SSMS locked the server as it tried to do the aggregation for the whole calendar :(

Any suggestions on what approach is best to take?

Answer 1

If you used views or named queries in the DSV instead of tables, then you could adjust the view or named query to limit the data to days where you have loaded exchange rate data. I'm not sure if this would meet your business need, but you could eliminate the empty data issues through this approach. After all, there's not much use to loading data that doesn't contain a value or, worse, preloading a guestimated exchange rate that would have to be overwritten once the actual numbers are available.