Wednesday, June 25, 2008

Handling Multiple Calendars with a M2M Scenario (Part 2)

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:

 Calendar Dimension Design

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".

New Measure Group

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.

New Many-to-Many Relationships

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.

Simple Pivot-Table with Calendar Dimension

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).



Excel Pivot-Table with Calendar Dim and Date Calculations



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!

14 comments:

Anonymous said...

Interesting solution. I would like to test this concept against an M2M calendar problem we have and was hoping to use your sample DW and project solution. Just one problem - How can I modify and deploy the SSAS project onto an SS 2005 Std Ed instance? I see it contains items which I think are Enterprise specific. TIA, Chas.

Dave Fackler said...

Hi Chas,

None of the components of the solution I've presented require Enterprise Edition. So, you should be able to recreate the solution using an SSAS project that can be deployed to Standard Edition.

That said, I did use the Enterprise Edition version of the Adventure Works sample to create the sample solution. Thus, you are right in that the sample I provided cannot be deployed to a Standard Edition server. You should be able to opne the Standard Edition version of the Adventure Works solution and recreate the solution though...

Good luck!
Dave Fackler

Anonymous said...

Thanks Dave, that worked. Regards, Chas.

Joe said...

Hi,

I am using your solution to build multiple calendars. But when I am at the dimension usage tab and tried to define the many to many I am getting an error saying "not intermediate measre group or dimesion exists and the dropdown list is empty. I went tried it on the AdventureWork project and database I got from you. There also I am getting the same error. I would really apprecite your advice on this.

Thans

Joe

Dave Fackler said...

Joe,

I'm not exactly sure what problem you might be running into. If you look at the database/project that I included in the post, the M2M calendar dimension and dimension usage should already be set up. Have you taken a look at that completed sample?

The only reasons I can think of as to why the M2M intermediate measure group drop-down would be empty is if you haven't correctly created the measure group off of the bridge table or you don't have the relationships or dimension usage right for the dimensions that connect to the measure group based on the bridge table.

If you need additional help, please feel free to email me at dave.fackler_at_hotmail.com. Please include as much detail as you can about your solution (possibly including screen shots showing the relavent tables in the DSV, your dimension designs, the measure group designs, the dimension usage design, etc.)

Dave Fackler

Karthik said...

Hi,

In this solution, I am wondering how do we set default member of the Calendar dimension to the last day of Sales data depending on the Calendar selected. Any Ideas?

TIA,
Karthik

calendars said...

The make up of the calendars can be used to efficiently create awareness. The lay-out, design and content of the calendars can bring to light and emphasize the presence of the products you show case on it.

Anonymous said...

Hi Dave -
You still monitoring this...? We are trying to implement your M2M calendar solution on 2008 (not R2). The basic approach for works great (thanks!) but when adding in the calculations and browsing within BIDS we get ugly errors "PivotTable List error 0xE0040200" or "Visual Studio HRESULT E_FAIL from COM component call." Even your downloaded complete solution gives the same result. Any reason this wouldn't work on 2008 (or would you use a different approach these days)?
Thanks for any hints!
TMan

Sam Kane said...

Here are this and some other articles on SSAS Date Dimension

http://ssas-wiki.com/w/Articles#Date_Dimension

Anonymous said...

I have tried this and functionally it works perfectly. However, I have found what maybe a general issue with M2M in that it does not use partition slicing.
My MG is partitioned by YYYYQQ, I have kept the Date dim and added a Calendar dim and bridge. In a trace I see queries using Date access only the relevant partitions but those using Calendar access ALL partitions. Anyone else noticed this?
Harvey

Mike said...

Dave - This is great work, and as I eagerly adopt for my own use, I run into a problem. I get that we need to set the default member for the Calendar attribute where the ISAggregatable is set to false, but I also need to set a default of 'yesterday' at the date level (already have the MDX). Do you see how we can set both defaults for this dimension?

Paul Goldy said...

Hi Dave. Nice work - thanks. Did you take the solution a step further and associate the appropriate date hierarchy with each client in the model? I'm trying to link the correct date hierarchy to the members in the client hierarchy so when a user filters to that client the cube is "smart enough" to know which date hierarchy to use. Any thoughts on how to do so?

Paul Goldy

Vidya said...

Hi Dave,
Thanks for the solution, hoping you will be able to respond.
I implemented this solution. It works perfectly for our situation where we have dynamic calendar types and all will flow through well.
However, YTDs or any aggregations are extremely slow. PY, PD etc are working fine.
Not sure what is it I'm doing wrong.

Vidya said...

I was able to fix this issue by replacing aggregates with sum for the time aggregations (YTD, QTD etc). The performance went from 10min to 1 sec! Has anyone seen a similar behaviour