Week days

Even though the Period Table automatically task added a number of additional, useful columns to the PeriodTable, it notably did not add Week days.

To add Week days to the PeriodTable, you can add a Script task to the ETL project that makes use of the DAYOFWEEK function and a CASE statement:

The required script:

alter table PeriodTable add column WeekDayNumber = {DAYOFWEEK(TheDate)}
alter table PeriodTable add column WeekDayName =
{
CASE DAYOFWEEK(TheDate)
WHEN 1 THEN 'Monday'
WHEN 2 THEN 'Tuesday'
WHEN 3 THEN 'Wednesday'
WHEN 4 THEN 'Thursday'
WHEN 5 THEN 'Friday'
WHEN 6 THEN 'Saturday'
WHEN 7 THEN 'Sunday'
ELSE 'none'
END
}

When this script has been run as part of the ETL project, it will produce two extra columns in the PeriodTable. You can verify this with the InMemory Query Tool:

Note: If the culture of the server where this script is run is different from what you want to load into the table, you may need to add or subtract 1 from the dates in the DAYOFWEEK functions.

alter table PeriodTable add column WeekDayNumber = {DAYOFWEEK(TheDate-1)}
alter table PeriodTable add column WeekDayName =
{
CASE DAYOFWEEK(TheDate-1)
WHEN 1 THEN 'Monday'
WHEN 2 THEN 'Tuesday'
WHEN 3 THEN 'Wednesday'
WHEN 4 THEN 'Thursday'
WHEN 5 THEN 'Friday'
WHEN 6 THEN 'Saturday'
WHEN 7 THEN 'Sunday'
ELSE 'none'
END
}

Example: In this example, the 27th of September 2023 is in fact a Wednesday. The WeekDayNumberUS and WeekDayNameUS columns are created from the first script in this article, while the WeekDayNumberDK and WeekDayNameDK columns are created from the second script (subtracting 1 from TheDate) in this article.