IBM Apptio

View Only

Date Dimension Table

Mon November 14, 2016 08:15 PM

Matt Temple

Occasionally, I have found myself challenged to establish a sustainable approach to some date specific calculations. To this end, I created a Date Dimension Table which provides me a way to quickly look up date details which include the following and more:

Day Suffix (eg 6th, 21st, 31st)
Day of Week USA
Day of Week UK
Day of Quarter
Week of Quarter
Last Day of Month
First Day of Quarter
Last Day of Quarter
Is Holiday for USA (extensible if day is company holiday)
Holiday Name in USA (extensible if day is company holiday)
Is Holiday for UK (extensible if day is company holiday)
Holiday Name in UK (extensible if day is company holiday)
...and Fiscal columns

The table can provide an easy way to count the number of regular working days in a month by analyzing the factor of the respective Is Holiday value with the Is Weekday value. For example, New Year's Day is a holiday for both US and UK and lands on a weekday in 2013, so 1 multiplied by 1 is 1...not a regular work day...whereas Jan 2's Is Holiday is 0 and multiplied by 1...is a regular work day.

The table is built using SQL. Thus, you can use the attached SQL script to generate whatever output desired or simply use the attached Excel file reflecting dates between Jan 1, 2013 and Dec 31, 2025.

If you go with the database approach for managing the data, you can VERY easily administer it for your own needs and have DataLink take care of any loads into your Apptio CT instance. Definitely what I recommend to do. ;-)

And, yes...I know some of the values provided in the table can easily be accommodated for with existing functions in Apptio. However, the table also serves additional purposes within my SQL database.

hi5

**** UPDATED ****

Added columns for the following:

IsWorkingDayUSA
IsWorkingDayUK
DayRemainingInMonth
WorkDaysRemInMonthUSA
WorkDaysRemInMonthUK

Also added in values for fiscal columns to demonstrate its output using the provided SQL script.

Enjoy! =)

**** UPDATED 2017-09-18 ****

Added columns for the following to identify the first and last days' date of each week:

FirstDayOfWeek
LastDayOfWeek
FiscalFirstDayOfWeek
FiscalLastDayOfWeek
PPMFirstDayOfWeek
PPMLastDayOfWeek

And, performed the following:

Changed the Date column's SQL data type from DATETIME to DATE.
Updated the attached SQL script and sample date in Excel.

**** UPDATED 2019-08-14 ****

Created a self-contained Excel file...no SQL needed! See attached file named "Date Details NO SQL.xlsx". The file is still a work-in-progress, but it contains many important attributes. To prepare the file for your use:

On the "Reference" worksheet, change the order of fiscal period months' names to match your organization's order. In the uploaded version, the order is shown as April -> March. If your organization matches a standard calendar order, simply copy the values from cells B2:B13 and paste values only onto D2:D13.
On the "Reference" worksheet, clear the Fiscal Start and Fiscal End values and enter values specific to your organization starting with fiscal period 1 in cells F2 and G2. Afterwards, add or remove values from the Fiscal Period and Fiscal Year columns as needed.
If your organization maintains an offset calendar for labor hours, continue reading...else skip this step. On the "Reference" worksheet, clear the PPM Start and PPM End values and enter values specific to your organization's labor calendar starting with fiscal period 1 in cells J2 and K2.
On the "Reference" worksheet for each US holiday or other non-work weekday, enter the date into column L. If you would like a name associated to the date, enter the value into column M.
On the "Dates" worksheet, go to the highlighted cell B3 and enter January 1 of the year in which you want the dates to start from. By default, it shows January 1, 2017. To start in 2016, simply overwrite the value as January 1, 2016.
On the "Dates" worksheet, go to the bottom of the table to verify the last row of the Date column shows December 31 of the year you wish to end in. If you need more rows, copy column B of the last row and (regular) paste into as many rows as you need to get to December 31 of the year desired.

After the above changes, upload the file into Apptio CT, specify data should be taken from the "Dates" worksheet, start import at row 2, and set "Enable data validation" to off. I recommend setting your Freshness Rule to "1 version; Update every year" to encourage you to revisit the data yearly. More frequently than yearly is okay, but you should be taking a look annually at minimum. Then, follow these steps to configure your table:

Set these fields as Label data type:
DateKey
DayName
DaySuffix
FirstDayNameOfMonth
FiscalQuarterName
FiscalYearName
HolidayUSA
IsHolidayUSA
IsWeekday
IsWorkingDayUSA
IsWorkingHolidayUSA
LastDayNameOfMonth
MonthName
PPMQuarterName
PPMYearName
QuarterName
YearName
Set these fields as Number data type:
DayOfMonth
DayOfQuarter
DayOfWeekUSA
DayOfYear
DaysRemainingInMonth
FiscalQuarter
FiscalWeekOfYear
MonthOfQuarter
PPMDayOfYear
PPMDaysRemInMonth
PPMDaysRemInMonthUSA
PPMDaysTotInMonthUSA
PPMHoursTotInMonthUSA
PPMQuarter
PPMWeekOfYear
PPMWorkdaysRemInMonth
PPMWorkdaysRemInMonthUSA
PPMWorkdaysTotInMonthUSA
PPMWorkHoursTotInMonthUSA
Quarter
WeekOfDayInMonth
WeekOfDayInYear
WeekOfMonth
WeekOfQuarter
WeekOfYear
WorkDaysRemInMonthUSA
WorkDaysTotInMonthUSA
WorkHoursTotInMonthUSA
Set these fields as Date data type and formatted as "MMyyyy":
FiscalMMYYYY
MMYYYY
PPMMMYYYY
Set these fields as Date data type and formatted as "MMM-yyyy":
FiscalMonthYear
MonthYear
PPMMonthYear
Set these fields as Date data type and formatted as "M":
FiscalMonth
Month
PPMMonth
Set these fields as Date data type and formatted as "yyyy":
FiscalYear
Year
PPMYear
Set the FullDateUK field as Date data type and formatted as "dd/MM/yyyy".
Set the FullDateUSA field as Date data type and formatted as "MM/dd/yyyy".
Set all other fields as Date data type and formatted as "yyyy-MM-dd".
Add a new Formula step and for each field name starting with "Is" assign the following formula override:
=If($_="1","YES","NO")

**** UPDATED 2019-08-22 ****