Building a data warehouse for robust analytics requires a versatile accounting calendar

A 1st Step to Building a Data Warehouse that Supports Robust Data Analytics is a Versatile Accounting Calendar

Though it may not seem obvious relative to your thoughts on a data warehouse for your business, building a versatile accounting calendar is an important first step.  The first consideration is what type of accounting calendar your business uses.  There are predominately two types: 1) a normal accounting calendar based on the natrual calendar period endings, such as January 31st, February 28th, March 31st, etc.; and 2) an accounting calendar that when split into quarters, has 13 weeks in each quarter and within each quarter two months comprising 4 weeks and 1 month comprising 5 weeks.  Accounting calendars are frequently referred to by the pattern of 4-week and 5-week months are used to get the 13 weeks.  The three options are 4-4-5 (most common), 5-4-4 and 4-5-4 (the later two being less common).  The final consideration is what month the fiscal year ends.

A good example of how these vary is with the recent ending of calendar 3Q of 2019.  A business using a normal calendar would have ended the quarter on Monday, September 30th.  A business using an accounting calendar, would most likely have ended the quarter on Friday, September 27th, with the first Monday of the 4th quarter falling on September 30th, and 4th quarter ending on Friday, December 27th, which is 13 weeks later.  Hopefully that makes sense.  But the key takeaway is that you need to accommodate the rules around your accounting calendar in the data warehouse calendar.

In this post, we are going to explain how to create an accounting calendar based on the normal calendar, with a December 31st year end.  If you would like, you can request a  copy of our accounting calendar, as a CSV file, that you can import into your system.  If you are using some other calendar, the changes to our example should be fairly straight forward.

More detail considerations for implementing your accounting calendar

We are going to utilize Microsoft Access as the database engine for our data warehouse application.  There are many reasons why, but one of the first is that Access is nearly ubiquitous on Windows-based machines, low cost, and the tables/queries are easily transportable to more robust solutions, such as Microsoft SQL Server, as demands dictate.

You can see a snippet of the table for our calendar below, which consists of 15 different fields that are used to summarize analytics using various calendar methods.  Again, you can request a CSV version of the accounting calendar used in this post, if you wish.  The calendar starts with January 1, 2008 and runs through December 31, 2028.  

The fields are as follows:

• • DateNum: This is a sequential integer, starting at 1 for January 1, 2008 and ending at 7,671 for December 31, 2028.  This field has a number of uses, but at the most basic level, it can be used to easily determine days between two dates.
  • Date: This is a date field that contains the actual date.  Below we discuss the format of this field, which is very important given that we used this field to link to the date file in other data sources.
  • Weekday: This is the abbreviated weekday.
  • Lmonth: This is the long month name, e.g. January, February, etc.
  • Smonth: This is the short month name, e.g. Jan, Feb, etc.
  • Nmon: This is the numeric month number, assuming a December year end.  If you have a different fiscal month start and end, then this would need to be adjusted accordingly.
  • Nday: This is the day within the month.
  • Year: This is the year.
  • Nquarter: This is the numeric quarter.
  • Quarter: This is the quarter name, e.g. 1st, 2nd, etc.
  • YYYY-MM Format: This is a representation for each month and is used to organize monthly data by column or whatever.  In our calendar, the month of October, 2019 would be represented as 2019-10.  But if your business has a September fiscal year end, then October would be the first month of the new fiscal year.  In this case, October 2019 might be 2020-01 given that October would be the first of month of the new 2020 fiscal year.  You need to think about how you want your monthly data summarized in PowerPivot tables, Crosstab queries, etc.
  • YYYY-Q Format:  This is the same as the YYYY-MM monthly field, just it is for quarters.

• DayOfYear: A serial number with January 1 being the numeral 1 and the last day being either 365 or 366.
• WeekNum: This is the sequential week number in the entire table and starts at 1 for the first week in January 2008 and ends at 1,097, which is the last week, though only a partial week, in December 2028, starting on Sunday.
• MonthNum:  This is the sequential month number, like the week number, staring at 1 and running through month 252, which is December 2028.  A frequent use of this field is to produce query results that are time bound.  For example, if only want users to have access to the most recent months of shipment history, we can easily use this field to filter the data.  More on this later.

Accounting calendar field data types used

This is a snippet of our Calendar table using the Design View so you can see what data types are used for the 15 fields.  All the numeric fields are Integers.

Some considerations when using date fields to link tables together

An important consideration or accommodation that needs to be addressed when using dates to link the calendar table to any other table that contains a date field is dealing with the time component embedded in date fields.  Though it goes a bit beyond the scope of this blog post, data warehouse designers need to be cognizant of the fact that date fields are in reality date and time fields.  As you can see below, I changed the Date format in our Access Calendar table to include the time.  You want to note that all the times are 12:00:00 AM.

Date/Time fields are decimal numbers, where the whole number part represents the date and the decimal part presents the time.  This means that you need to always be scrubbing any dates you import into your data warehouse and use the same time  (unless it is an important dimension in your application).  Take a look at the Excel snippet below with different dates:

Access and many other applications treat dates/times in a similar fashion.  Compare the cells in B2 vs. C2.  You can see that both have the same date, yet have different times.  If you were to perform a comparison, as is done in D2, the two fields are not equal.  This also means that you cannot link these two fields together.  Also, at B3 you can see the decimal interpretation of 10/3/2019 @ 10:17 AM, which Excel perceives as 43743.42911782.  Excel is transforming 43743.42911782 into a corresponding date and time.  But if you can run your dates through a function, like Int(), you can remove the decimal part, which will make it very easy to link the date fields as is shown in B4 vs. C4.

Scrubbing dates is a fairly simple process given that you just need to use the INT() function or something similar to remove the decimal part. The INT() function will round down to the nearest integer.  So even if you have a date, that has a time one second before midnight, the INT() function will just round down to that same day, at 12:00 AM, e.g. 43743.99999999 will be rounded to 43743.00000000.  Contact us if that does not make sense.

That is about it for the primary concerns regarding the creation of an all important Accounting Calendar for your data warehouse application.  We hope you found this “how to” on building a data warehouse for robust analytics requires a versatile accounting calendar of value.  In the next installment of the data warehouse application “how to” we are going to show you ways to use your accounting calendar to limit or filter data from your data warehouse to your user apps such as PowerPivot or Power BI, where you want to limit the total data set, by date, that goes out to your user base.

If you are a business owner that is looking for ways to leverage your data analytics, please contact us today.