Time Dimension population

[srao]

Hi,

I need to populate the time dimension using below columns. How to achieve in datastage ? any help please

Calendar year
Calendar Quarter
Calendar Month
Calendar Mth
Fiscal Quarter

[chulett]

So, I assume you have to actually generate this data rather than load it from a source, yes? What have you tried so far? What kind of approach are you thinking of taking? Please tell us that your efforts so far have been more than to simply post here...

The Parallel Job Developer Guide pdf has a section on both Date and Time Formats and Date and Time Functions, both of which can come into play here. You can generate dates for however long of a period as you like, then get the year and month portions and build from there. Quarter would (worst case) be a simple Mod() check. Fiscal Quarter may be more difficult but that will depend entirely on how your company's fiscal year relates to a calendar year. I'm sure you can work it out.

[ray.wurlod]

Let's start by asking for the business definition of "Fiscal Year" where you are. Is it July-June, May-April, or something else? Obviously that will affect the solution.

The others are easy using functions such as year from date. Fiscal year and fiscal quarter are derived from calendar year and calendar month using simple arithmetic based on the business rule.

Consider now what other useful columns you might have in a time dimension (for example first day of month, last day of month, weekday name, and so on).

Use a Row Generator stage to generate the dates - set Epoch to the first date to be generated and Limit to the number of dates to generate.