A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.
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
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.
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
Wednesday, June 11, 2008
Put fiscal year into its own dimension.
So your fact table would be:
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..
This topic is archived. No further replies will be accepted.Other recent topics
Powered by FogBugz