In my last post, I started describing a way to handle multiple calendars using a many-to-many scenario. As I explained, a recent client engagement called for a solution that would allow them to add calendars (customer calendars in particular) without requiring changes to the relational or SSAS models. In most classic date dimensions, multiple calendars are handled using additional columns for the additional calendar parts that each date has. These additional columns are then added to SSAS as additional attributes in the date dimension; these attributes are then typically used to create specific user hierarchies for that particular calendar (a Gregorian calendar hierarchy, a fiscal calendar hierarchy, etc.)
The previous post laid the groundwork for creating a multi-calendar solution using a many-to-many scenario. In that post, I described a new DimCalendar dimension table that would contain a row for each date within each of the calendars needed. The table contained date-part columns (for things like year, quarter, month, etc.), but these were not specific to a given calendar. Instead, they contained the appropriate date-parts for a given date within a given calendar. One thing to note about this approach is that the DimCalendar dimension table will hold N times the number of dates that you might need for all the years you need to represent in your solution. Thus, if you need 10 years worth of dates, a standard date dimension might contain approximately 3,650 rows; with the DimCalendar table, if you need 4 calendars represented for those same 10 years, the table will contain approximately 14,600 rows. Given this is a relatively low row count for a dimension table, this shouldn't impose any performance problems with the solution.
With the DimCalendar table available, we can create a new [Calendar] dimension within SSAS using standard dimension techniques. Using the DimCalendar table design from the last post, the [Calendar] dimension within SSAS might look like this:
A few notes about the design:
- The [Calendar] attribute contains the calendar names based on the values found in the CalendarName column from the DimCalendar table; the DefaultMember property of the [Calendar] attribute is set to [Calendar].[Calendar].&[Gregorian] so that the default calendar is the Gregorian calendar; this attribute also has its IsAggregatable property set to False.
- The other attributes are built in a typical fashion, using standard KeyColumns and NameColumn values based on columns available from the DimCalendar table; in addition, standard attribute relationships exist between attributes as would be expected in a date dimension (with the additional step of relating the [Year] attribute to [Calendar]).
- The [Year-Quarter-Month] and [Year-Week] hierarchies are built in a typical fashion.
- The [Date Calculations] attribute is based on a named calculation column in the DSV (a standard approach for "hanging" date calculations on an attribute within a date dimension) with a default value of "Current Calendar Date".
With the new [Calendar] dimension in place, we can update the cube to take advantage of it. Note that in the discussion that follows, I did not remove the existing [Date] dimension from the Adventure Works cube. Ideally, however, the new [Calendar] dimension would replace it (and could be used in a role-playing fashion just like the [Date] dimension is used already).
The first step to updating the cube is to add a new measure group based on the new FactDateCalendar table. Remember that this table contains the linkage between the existing DimTime table (which is related to all the other fact tables) and the new DimCalendar table. The measure group need only contain a single, hidden measure that uses the Count aggregate function to aggregate the row count of the FactDateCalendar table based on its related dimensions. In the screen shot below, note the diagram showing the relationships between the new tables and the new measure group named "Fact Date Calendar".
With the new measure group in place, the Dimension Usage tab can be used to relate the [Calendar] and [Date] dimensions to it and create the many-to-many relationship between the new [Calendar] dimension and the other related measure groups. The following shows the a few of the relationships defined on the Dimension Usage tab.
Note that the many-to-many relationships use the new "Fact Date Calendar" measure group as the intermediate measure group. Also note that many-to-many relationships exist between the [Calendar] dimension and the other measure groups (I just chose not to show them all in the screen shot).
With the new measure group in place along with the new relationships, the solution can be deployed, processed, and then queried (using the BIDS browser, the SSMS browser, or a front-end tool like Excel). The following shows a simple Excel 2007 pivot-table that uses the [Year-Quarter-Month] hierarchy from the new [Calendar] dimension along with measures from the "Internet Sales" measure group.
Note the different calendars showing the same grand total, but with different subtotals below that based on the fact that days aggregate to fiscal years differently than they do for calendar years (at least for Adventure Works). If users want to focus on a particular calendar, they can filter the hierarchy or include the [Calendar] attribute as a page-level filter to get the same affect. Also note that if a query doesn't include the new [Calendar] hierarchy, everything works as expected given the fact that the Gregorian calendar is set as the default calendar and aggregates and totals work as expected.
Finally, to round out the solution, we can add date calculations to the new [Calendar] dimension and make them work as expected. Earlier, I mentioned that the [Date Calculations] attribute was added to the new [Calendar] dimension in order to provide a place to "hang" date calculations. This is a fairly standard approach that has been described (with its various pros and cons discussed) by many different SSAS practitioners over the last several years; thus, I won't go into a lot of discussion about the approach here. Suffice it to say, the following provides the MDX I added to the calculations script for the cube in order to create three example date calculations (which apply to different sets of calendars -- for example purposes only).
// Date calculations for the [Calendar] dimension
Create Member CurrentCube.[Calendar].[Date Calculations].[Year to Date]
As Null;
Create Member CurrentCube.[Calendar].[Date Calculations].[Year Over Year Growth %]
As Null;
Create Member CurrentCube.[Calendar].[Date Calculations].[Year Over Year Growth]
As Null;
Create Set [Date Calculation Target Measures]
As
{
[Measures].[Internet Sales Amount]
,[Measures].[Internet Order Quantity]
,[Measures].[Internet Order Count]
,[Measures].[Reseller Sales Amount]
,[Measures].[Reseller Order Quantity]
,[Measures].[Reseller Order Count]
};
// [Year to Date]
Scope
(
[Date Calculation Target Measures]
,[Calendar].[Date Calculations].[Year to Date]
);
// [Year-Quarter-Month].[Year] level
(
[Calendar].[Year-Quarter-Month].[Year].Members
) =
Aggregate
(
{[Calendar].[Date Calculations].DefaultMember} *
PeriodsToDate
(
[Calendar].[Year-Quarter-Month].[Year]
,[Calendar].[Year-Quarter-Month].CurrentMember
)
);
// [Year-Quarter-Month].[Quarter] level
(
[Calendar].[Year-Quarter-Month].[Quarter].Members
) =
Aggregate
(
{[Calendar].[Date Calculations].DefaultMember} *
PeriodsToDate
(
[Calendar].[Year-Quarter-Month].[Year]
,[Calendar].[Year-Quarter-Month].CurrentMember
)
);
// [Year-Quarter-Month].[Month] level
(
[Calendar].[Year-Quarter-Month].[Month].Members
) =
Aggregate
(
{[Calendar].[Date Calculations].DefaultMember} *
PeriodsToDate
(
[Calendar].[Year-Quarter-Month].[Year]
,[Calendar].[Year-Quarter-Month].CurrentMember
)
);
// [Year-Week].[Year] level
(
[Calendar].[Year-Week].[Year].Members
) =
Aggregate
(
{[Calendar].[Date Calculations].DefaultMember} *
PeriodsToDate
(
[Calendar].[Year-Week].[Year]
,[Calendar].[Year-Week].CurrentMember
)
);
// [Year-Week].[Week] level
(
[Calendar].[Year-Week].[Week].Members
) =
Aggregate
(
{[Calendar].[Date Calculations].DefaultMember} *
PeriodsToDate
(
[Calendar].[Year-Week].[Year]
,[Calendar].[Year-Week].CurrentMember
)
);
End Scope;
// [Year Over Year Growth %]
// Applied only to the [Gregorian] calendar
Scope
(
[Date Calculation Target Measures]
,[Calendar].[Date Calculations].[Year Over Year Growth %]
,[Calendar].[Calendar].[Gregorian]
);
// [Year] attribute
(
[Calendar].[Year].[Year].Members ( 1 ) : Null
) =
IIf
(
([Calendar].[Date Calculations].DefaultMember, [Calendar].[Year].CurrentMember.Lag(1)) = 0
,Null
,
(
([Calendar].[Date Calculations].DefaultMember) -
([Calendar].[Date Calculations].DefaultMember, [Calendar].[Year].CurrentMember.Lag(1))
)
/
([Calendar].[Date Calculations].DefaultMember, [Calendar].[Year].CurrentMember.Lag(1))
);
Format_String
(
([Calendar].[Date Calculations].[Year Over Year Growth %])
) = "Percent";
End Scope;
// [Year Over Year Growth]
// Applied only to the [Fiscal] calendar
Scope
(
[Date Calculation Target Measures]
,[Calendar].[Date Calculations].[Year Over Year Growth]
,[Calendar].[Calendar].[Fiscal]
);
// [Year] attribute
(
[Calendar].[Year].[Year].Members ( 1 ) : Null
) =
([Calendar].[Date Calculations].DefaultMember) -
([Calendar].[Date Calculations].DefaultMember, [Calendar].[Year].CurrentMember.Lag(1));
End Scope ;
With this script in place, the Excel 200 pivot table shown above can be updated to include the new date calculations (showing just one of the measure included in the set of measures the date calculations apply to).
Note that in the screen shot below, a "Reporting" calendar shows up along with the "Fiscal" and "Gregorian" calendars. The nice thing about this solution is that I didn't have to change the design at all in order to add this new calendar (that's the whole point of the design). In fact, I had completed the design work described above, deployed and processed the solution, and was browsing it with just the "Fiscal" and "Gregorian" calendars included. I then used a T-SQL script to add the new "Reporting" calendar by adding rows to the new DimCalendar and FactDateCalendar tables in the relational database. I then reprocessed the [Calendar] dimension and the "Fact Date Calendar" measure group (without reprocessing any other dimensions or measure groups in the solution) and viola! The new "Reporting" calendar was established and showed up in my pivot-table.
Hopefully, others out there who may be faced with supporting multiple calendars (beyond the standard "Fiscal" and "Gregorian" varieties) will find this approach interesting and useful. Let me know what you think...
PS. If you want to download a copy of the solution, I've made it available as a zip file here:
The SQL database should be restored as "AdventureWorksDW-M2MCalendar" and the SSAS solution should be deployed as "Adventure Works DW - M2M Calendar". I typically create copies of the base Adventure Works sample databases for creating and testing new ideas and solutions. Enjoy!
