ETL Studio: Period Table Task

With the InMemory ETL Studio you can find a task that has already been designed specifically to create a Time dimension table, the Period Table task:

Once you add it to your ETL project, the Period Table task will parse through all existing tables in your InMemory database to find columns of data type ‘datetime’. The unique set of dates it finds in this way will become the basis for your new Time dimension table.

In properties, from the drop down list, you can select the sub-categories that should be derived from these dates. These sub-categories will become additional columns in the Time dimension table and may later be used for creating separate Time dimension attributes or hierarchies.

When you click the ‘Configure’ button, you can change the different formats, if necessary. E.g., if you prefer Quarter names to be like ‘Q1, Q2, Q3 and Q4’, you should change the QuarterName format to ‘Q#’.

The destination table for the Period Table task will by default be called PeriodTable. Once you have run the task, you can use the InMemory Query Tool to look up the contents of this table.

The TheDate column is the key column of this table and it contains all the unique dates from ‘datetime’ type columns in the other tables. The other columns – Year, YearName, QuarterNumber etc. – have all been derived from the TheDate column.