As the enterprises expending their horizons the enterprise data warehouse is facing multiple challenges. Date and time is one of them. It is amazingly interesting and challenging to work with date dimension in enterprise scenario. The same obvious challenge came to us while developing data warehouse changes to one of our customer.
Approach 2: The approach works nice if we have less number of consumers to the data warehouse having different fiscal year. Adding hundreds of consumers with hundreds of fiscal year will ruin the above approach. Small modifications to the above approach helped to maintain all the fiscal years for any number of consumers. Understanding that all of the hundreds of fiscal year will always converse to twelve months the date dimension is modified to include all of the twelve hierarchies. The diagram 2 visualizes the data model for the same.
The consumer can consume the respective hierarchy based upon his fiscal year. E.g. if the fiscal year for the 'Consumer-1' starts from 01-July then they will be using the JulFiscalYear, JulFiscalQuarter, JulFiscalMonth etc. It can be extended for week and other fields but we just have to make sure that the size of the row doesn't extend the maximum size of the row.
The blog discusses the solution approaches of respective problem. Putting multiple hierarchies is just like giving different name to the date and can be done easily as below. The below solution didn't deals with the scenario having fiscal year starting from middle of the month e.g. fiscal year 2012 starting from 15-April-2011. We assume that the fiscal year starts from 1st of any of the month e.g. fiscal year 2012 starts from 01-April-2011.
Approach 1: There are several solutions to the problem and one of the most common solutions is to add multiple date hierarchies to the same date dimension one for each consumer's fiscal year. The diagram 1 visualizes the data model for the same.
Approach 2: The approach works nice if we have less number of consumers to the data warehouse having different fiscal year. Adding hundreds of consumers with hundreds of fiscal year will ruin the above approach. Small modifications to the above approach helped to maintain all the fiscal years for any number of consumers. Understanding that all of the hundreds of fiscal year will always converse to twelve months the date dimension is modified to include all of the twelve hierarchies. The diagram 2 visualizes the data model for the same.
The consumer can consume the respective hierarchy based upon his fiscal year. E.g. if the fiscal year for the 'Consumer-1' starts from 01-July then they will be using the JulFiscalYear, JulFiscalQuarter, JulFiscalMonth etc. It can be extended for week and other fields but we just have to make sure that the size of the row doesn't extend the maximum size of the row.
No comments:
Post a Comment