Period Table Task, The Output tab

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.

mceclip0.png

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.

mceclip1.png

Run the SSIS package to populate the new Posting Period table.

mceclip2.png

In SQL Server Management Studio we can now see the table, columns and data that was produced by running the Period Table Task.

Was this article helpful?
0 out of 0 found this helpful

Comments

0 comments

Please sign in to leave a comment.