Notes on Date_Generation transform: This is ultimate transform for creating Time dimension tables. It generates dates incremented as you specify.

Options:

• Start date : Well DI document says that start date range starts from 1900.01.01, I did some sanity test and date range starts from 1752.09.14 onwards. In 11.5 you when you mention date like this and while validating the DF, DI will show you an error stating that date range starts from 1900.01.01 onwards. But there is tricky way to workout this. It is as simple as that, only thing that you need to do is save the job after designing your time dimension without validating the DF/Job. However, this has been overwhelmed in later versions. And one can pass variables inspite of selecting values.
• End date : The date range ends with 9999.12.31. Instead of selecting values we can pass variables also.
• Increment : We can specify date intervasl between the sequence. We can increment daily, weekly and monthly.
• Join rank : While constructing the join, sources will be joined based on their ranks.
• Cache : The dataset will be cahced in memory to be used in later transforms.

Design Steps:

• Drag the date_generation transform from the object library on to the workspace, and in the next step connect a query transform and next step is your target object. Your design would be like this

• Now open the date_generation transform and mention values, check the image for reference

• Now in the query transform i had applied some funtions like month(), year(), quarter(), day_in_month(), week_in_year() etc. Check the image how i mapped.