Tim Blog

Suppose a data mart contains some sales facts and a time dimension.

A time dimension is easy to implement using a Year>Month>Day hierarchy, which will contain specific values at each level (e.g “2004”, “2004-Dec”, “2004-12-25”)

Suppose further that a user needs to create a month to date (MTD) sales report.

With the time dimension described so far, a user could select data where the month equals “2004-Dec”. This is fine until she opens the report next month (January) and it still shows December’s data. She has to edit the report to get January’s data.

Ideally she could select data using a special value like “MTD” instead of a specific value like “2004-Dec”. Then the report would dynamically change and avoid the problem.

I call this a “special time period” (and make no claim to be the first to do so!)

How to implement this?

In this example, the data mart is a SQL Server database and a SQL Server Analysis Services Cube. I give users access to both using a SQL based report writer, and an OLAP report writer (in the real world... Crystal Reports and Crystal Analysis).

I would like the user to be able to write a SQL based report or an OLAP report using the special time period.

First, some data for the example (I have dispensed with the usual constraints for brevity)

CREATE TABLE [dbo].[dimn_time] (
 [time_key] [int] NOT NULL ,
 [time_date] [datetime] NOT NULL ,
 [caption_day] [varchar] (50) NOT NULL ,
 [caption_month] [varchar] (50) NOT NULL ,
 [caption_year] [varchar] (50) NOT NULL ,
 [is_special_period_mtd] [bit] NOT NULL
)
GO

CREATE TABLE [dbo].[fact_sales] (
 [time_key] [int] NOT NULL ,
 [sales_amount] [money] NOT NULL
)
GO

INSERT INTO dimn_time ([time_key],[time_date],[caption_day],[caption_month],[caption_year],[is_special_period_mtd])
VALUES(1,'25-DEC-2004','2004-12-25','2004-Dec','2004',1)
GO

INSERT INTO dimn_time ([time_key],[time_date],[caption_day],[caption_month],[caption_year],[is_special_period_mtd])
VALUES(2,'26-DEC-2004','2004-12-26','2004-Dec','2004',1)
GO

INSERT INTO dimn_time ([time_key],[time_date],[caption_day],[caption_month],[caption_year],[is_special_period_mtd])
VALUES(3,'01-JAN-2005','2005-01-01','2005-Jan','2005',0)
GO

INSERT INTO fact_sales ([time_key],[sales_amount])
VALUES(1,100.00)
GO

INSERT INTO fact_sales ([time_key],[sales_amount])
VALUES(1,120.00)
GO

INSERT INTO fact_sales ([time_key],[sales_amount])
VALUES(2,50.00)
GO

INSERT INTO fact_sales ([time_key],[sales_amount])
VALUES(3,500.00)
GO

The flag [is_special_period_mtd] must be kept current, and since the grain of the time dimension is day, I will update the flag every day. This is done using a simple stored procedure that updates [time_date] to 1 if it equals the current date and 0 otherwise. For other special time periods, the logic might be more complicated, e.g a moving average or cumulative total.

My first objective is reached. The user can now connect to the database using a SQL based report writer (like Crystal Reports for example), join the two tables on [time_key], put the [sales_amount] column on the report and create a filter selecting only records where the flag [is_special_period_mtd] is <> 0. She doesn’t have to create messy formula’s embedded in individual reports.

The SQL would be:

SELECT
 b.[caption_day], a.[sales_amount]
FROM
 [fact_sales] a join [dimn_time] b on a.[time_key] = b.[time_key]
WHERE
 b.[is_special_period_mtd] <> 0

Which returns:

caption_day                                        sales_amount         
-------------------------------------------------- ---------------------
2004-12-25                                         100.0000
2004-12-25                                         120.0000
2004-12-26                                         50.0000

(3 row(s) affected)

Voila!

Now the harder part…I want the same possibility if the users are using the cube, and an OLAP report writer, and I want to use the same flag in the database.

This technique allows the logic for including a given date in a special time period to be defined once only, in the stored procedure that updates the corresponding flag.

First, let's build a simple time dimension and cube over this data.

Figure1
http://weblogs.sqlteam.com//images/weblogs_sqlteam_com/timg/117/o_figure1.jpg

The dimension is straight forward; I created levels for Year, Month and Day. (I set the ordering for the Day level to be the [time_date] column which ensures date order no matter what format you use for date captions)

Here is the problem… I want the user to be able to select a member called “MTD”, but I don’t have this yet. If I include the [is_special_period_mtd] column as a level, then it will show members with values of “0” and “1”.

How to do it?

First, add a member property to the day level based on [is_special_period_mtd] column and mark it’s visible property as False, just so users don’t get confused with member properties defined for other purposes.

Next I created a cube… I took all the default options from the create cube wizard to get this.

Figure2
http://weblogs.sqlteam.com//images/weblogs_sqlteam_com/timg/117/o_figure2.jpg

I would like the user to see a dimension called “Special Time Periods”, so when she opens it up, she will see one member for each special time period. In this example she will see a single member called “MTD”.

To do this I did a bit of a trick to make Analysis Services think there is such a dimension, and it will only have one member that is linked to every fact table row. Then I will create a calculated member “MTD” in the new dimension.

I start with a view in the database:

CREATE VIEW dimn_special_time_periods_vw
AS
SELECT
 special_time_period_key = 0,
 caption_special_time_period = ‘None’

and I create a view of  the [sales_fact] table with the corresponding key value:

CREATE VIEW fact_sales_vw
AS
SELECT
 time_key,
special_time_period_key = 0,
 sales_amount
FROM
 fact_sales

The end result is that the new [fact_sales_vw] looks exactly like the original table, but every row is linked to a dummy special period  “record”. Now I recreate the cube, same as before but using the [fact_sales_vw] rather than the [fact_sales] table.

In addition I have added a private (non-shared)  dimension based on  [dimn_special_time_periods]. The only thing I changed from the default wizard settings is to remove the “All” level.

Figure3
http://weblogs.sqlteam.com//images/weblogs_sqlteam_com/timg/117/o_figure3.jpg

Finally I can create the calculated member, in the Special Time Periods dimension, like this:

Figure4
http://weblogs.sqlteam.com//images/weblogs_sqlteam_com/timg/117/o_figure4.jpg

The MDX (Multi Dimensional Expressions) statement for the calculated member is:

Aggregate
(
Filter
(
[Time].[Day].Members, [Time].CurrentMember.Properties("is_special_period_mtd") <> "0"
),
[Special Time Periods].&[0])
)

Now I can process the cube and I’m done.

The MTD member of the Special Time Period dimension is independent of the Time dimension itself. It always gives an answer for the current month, regardless of the year or month the user may be filtering on.

Figure5
http://weblogs.sqlteam.com//images/weblogs_sqlteam_com/timg/117/o_figure5.jpg

This has some overhead in the cube, because the MDX is executed dynamically. For me this is outweighed by the fact that I can define the special period in a single place and expose the same speical period to users of the database and to users of the cube.

I would be interested to hear any improvements. I am no MDX wiz, so I expect there is a better way to phrase the MDX query to get the same result.