The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Date dimension with different fiscal calendars

I am building a datawarehouse that holds data for different clients, which is later loaded into OLAP cubes (1 per client). I want to create a date dimension for my data warehouse that includes Fiscal Periods.

The problem is that each client has a different fiscal calendar. Currently, my date dimension looks like the one below and I create a separate view for each client: DIM_Date_CLIENT1, DIM_Date_CLIENT2, DIM_Date_CLIENT3 etc.

Any suggestions on how I can improve the way I'm dealing with different clients' fiscal calendars?


CREATE TABLE dbo.DIM_Date(
    DateKey int NOT NULL,
    ActualDate datetime NOT NULL,
    [DayName] nvarchar(36) NULL,
    MonthFirstDay datetime NULL,
    MonthFullName nvarchar(30) NULL,
    MonthKey int NULL,
    MonthLastDay datetime NULL,
    [MonthName] nvarchar(16) NULL,
    QuarterFirstDay datetime NULL,
    QuarterKey int NULL,
    QuarterLastDay datetime NULL,
    QuarterName nvarchar(36) NULL,
    YearFirstDay datetime NULL,
    YearKey int NULL,
    YearLastDay datetime NULL,
    YearName nvarchar(4) NULL,
    WeekFirstDay datetime NULL,
    WeekKey int NULL,
    WeekLastDay datetime NULL,
    WeekName nvarchar(36) NULL,
    CLIENT1_FYYearID int NULL,
    CLIENT1_FYYearName varchar(10) NULL,
    CLIENT1_FYPeriodID int NULL,
    CLIENT1_FYPeriodName varchar(10) NULL,
    CLIENT1_FYWeekID int NULL,
    CLIENT1_FYWeekName varchar(10) NULL,
    CLIENT2_FYYearID int NULL,
    CLIENT2_FYYearName varchar(10) NULL,
    CLIENT2_FYPeriodID int NULL,
    CLIENT2_FYPeriodName varchar(10) NULL,
    CLIENT2_FYWeekID int NULL,
    CLIENT2_FYWeekName varchar(10) NULL,
    CLIENT3_FYYearID int NULL,
    CLIENT3_FYYearName varchar(10) NULL,
    CLIENT3_FYPeriodID int NULL,
    CLIENT3_FYPeriodName varchar(10) NULL,
    CLIENT3_FYWeekID int NULL,
    CLIENT3_FYWeekName varchar(10) NULL
)
Craig Send private email
Wednesday, June 11, 2008
 
 
It seems to me you have used data warehousing principals correctly in your solution, at least as far as I understand them. But it doesn't seem a viable long term approach.

Because what you are asking is quite specialised, I suspect you won't get a good answer here. I recommend finding a data warehouse forum instead.
Steve McLeod Send private email
Wednesday, June 11, 2008
 
 
Craig,

If you have different views for different customers, maybe you could move the fiscal year logic to the view definitions (assuming it can be expressed by some simple enough expressions based on the other columns of the table)?

Frank
Frank Ploessel Send private email
Wednesday, June 11, 2008
 
 
There is surely a limit to the number of different fiscal calendars possible, isn't there? Personaly I'd be providing a series of different tables for each calendar, and a synonym (or view) pointing to the appropriate table for the company
David Aldridge Send private email
Wednesday, June 11, 2008
 
 
David Aldridge, that's it!

There is a limit to the number of fiscal calendars and your solution is ideal. Thanks
Craig Send private email
Thursday, June 12, 2008
 
 
Put fiscal year into its own dimension.

So your fact table would be:

DateDimID
,FiscalYearDimID
,ClientDimID
,etc.

So you are effectively taking a snapshot of the fiscal year configuration for the client and date at the time of ETL.

Data warehouses don't need to be "normalized" in the same way that an traditional entity/transaction database design is normalized.  Just because the Client and Date determine a Fiscal Year, clients can also change their fiscal years over time (although most companies won't do it more than once during a lifetime - except for mergers), and you wouldn't want that to affect your DW which is a snapshot and it will save you the trouble of adding further information about effective dates of such a change..
Cade Roux Send private email
Thursday, June 12, 2008
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz