This blog will help especially those who find difficulty to insert or update Fiscal Date values in Date Dimension table. Very hardly see the Data Warehouse design without Date dimension. In most cases Fiscal Date fields are very, very important to analysis the business data. Normally the Fiscal Date field’s values may vary company to company or country to country. Most cases, the fiscal date or reporting period are predefined in the relational table based on calendar date such as Fiscal Year, Fiscal Semester, Fiscal Quarter, Fiscal Bi-Month, Fiscal Month and Fiscal Week.
Here I have created 2 tables, one is Fiscal Source table which contains Fiscal data and other one is Date dimension table.
The following screen shot shows that the Date Dimension table with Empty Fiscal values. In this table is FullDate is defined as Date, the task is to identify the fiscal values for the particular date.
The following image shows that how the Fiscal Periods are defined based on calendar date. In here, you can see that Fiscal Period is defined by DateFrom and DateTo fields, sometime this field refer as StartDate and EndDate.
In here, I am using SSIS package to do this task. To find date between 2 dates, simple method is use Lookup with modify SQL Statement. Ole DB Source connection for DimDate table.
Use Lookup data flow task, and make it cache mode as partial cache or No cache, otherwise “Modify the SQL command” feature will not be enable. 
Once you map the FullDate with DateFrom column, the SQL Command will be shown below.
Enable Modify the SQL Command feature and modify the command as below.
Make sure that Input Column makes it as FullDate and Param direction as Input.
One you have done this, this data flow will pick the appropriate Fiscal values for the particular FullDate.
The below screen shot shows that once you update the Dimension values with Fiscal Data.
Cheers!
No comments:
Post a Comment