On the Output tab you define the names of the new Time dimension table and the Key Date Column containing the input dates.
The Language setting determines the names of Months and Weekdays, e.g. Montag, Dienstag, Mittwoch etc. for Weekday names if German had been selected.
In the Columns section you can add all the columns, or attributes, that should be derived from the Key Date Column. Some default columns have already been defined.
You must right-click in the columns section to add custom columns. Then define type, name and format for each column.
Use the following table for reference when defining the format for output columns. The examples are based on February 5th, 1998.
Format string |
Output |
February 9th, 2012 |
#YYYY |
= four-digit year |
2012 |
#YY |
= two-digit year |
12 |
#Q |
= quarter [1-4] |
1 |
#M |
= one/two-digit month [1-12] |
2 |
#MM |
= two-digit month [01-12] |
02 |
#MMM |
= 3-letter month in upper case |
FEB |
#Mmm |
= 3-letter month in proper case |
Feb |
#mmm |
= 3-letter month in lower case |
Feb |
#MMMM |
= month name in upper case |
FEBRUARY |
#Mmmm |
= month name in proper case |
February |
#mmmm |
= month name in lower case |
february |
#W |
= one/two-digit week [1-53] |
6 |
#WW |
= two-digit week [01-53] |
06 |
#D |
= one/two-digit day of month [1-31] |
9 |
#DD |
= two-digit day of month [01-31] |
09 |
#DW |
= one-digit day of week [1-7] |
4 |
#DDW |
= 3-letter day name in upper case |
THU |
#Ddw |
= 3-letter day name in proper case |
Thu |
#ddw |
= 3-letter day name in lower case |
Thu |
#DDDW |
= day name in upper case |
THURSDAY |
#Dddw |
= day name in proper case |
Thursday |
#dddw |
= day name in lower case |
thursday |
#DY |
= one/two/three-digit day of year [1-366] |
40 |
#DDY |
= three-digit day of year [001-366] |
040 |
Further notes on the Period Table Task:
- The table, as defined by the Period Table Name, will be dropped and recreated every time the task is executed.
- Format strings are case sensitive.
- The ‘Numeric’ option, if selected, will produce a numeric data type column – if unselected, a string data type column.
The Period Table Task should be inserted immediately before the Analysis Services Processing task. We do this because the Period Table Task may be dependent on input tables that need to be populated first.
Run the SSIS package to populate the new Posting Period table.
In SQL Server Management Studio we can now see the table, columns and data that was produced by running the Period Table Task.
Comments
Please sign in to leave a comment.