Performing MTD/YTD calcs over multiple calendars in analysis services

I have the following situation in my cube:

Shop A uses calendar Cal1. Their sales month starts Jan 5th. Shop B uses calendar Cal2. Their sales month starts Jan 10th. Shop C...etc

Shop calendars can not simply be represented as offsets of a main calendar. They have different working days, public holidays etc.

I need to produce a daily (reporting services) report with the actual calendar date as a parameter. The list of shops is also a multi select parameter. If a user selects the 15th of Jan, I need to show the combined MTD sales for all shops selected in the parameters. So that would mean the first 10 days of sales for shop A and the first 5 days of sales for shop B etc.

Any ideas how I can make this work? I'll also need to provide YTD figures in the same manner.

I am implementing multiple calendars using a bridging table between my date and calendar dimensions. It is the technique described here: http://duncansutcliffe.wordpress.com/2010/06/11/a-better-date-dimension/

I can not hard code the calendars as there is a requirement to possibly add more in the future without modifying the schema.

I am not sure I understand you sales data start days, but if I do then the solution is to make an extra dimension as a "reporting calendar" as a point of harmony between the actual calendars

Each shop has a known offset to the reporting calendar, so for shop A it's 5 days, for shop B it's 10 days etc

When you add fact data you also need to calculate a reporting date using the offset. So for Shop A 5 Jan is actually 1 Jan etc

When reporting, the user selects a date on the reporting calendar, and facts are selected based on that

e.g. if the user selected reporting calendar 15th Jan, it would only select actual dates 1 to 15 Jan and reporting calendar up to 15 and only Jan

Data selected would be Shop A 5 to 15, Shop B 10 to 15

1 to 4 Jan for Shop A and 1 to 9 for Shop B would be in Dec of the reporting calendar, and not included because of the filter of reporting calendar Jan

You have the right idea, however its critical to note that an offset can not be used. Each calendar has different working days. We can not assume that each calendar can be represented by an offset of the main annual calendar.

Alex 2010-10-29 04:34:49

Hmm, works for me. Read it again? There can be an offset for each calendar or data set. The "reporting calendar" is not representing any real dates, it just provides a reference to a "reporting period", usually a "reporting month" or "reporting year". e.g. If a fact exists in a given data range AND exists in the reporting range (month) it is selected

TFD 2010-10-29 10:07:47

Yeah I see....I'll try an implementation.

Alex 2010-10-31 14:47:09

ansaurus

tags:

views:

answers:

Performing MTD/YTD calcs over multiple calendars in analysis services

related questions