Waldar’s SQLing and Datawarehousing Place

Some weeks ago, one client of mine told me something I found really weird.
He told me that he updated the (weekly) day number of a couple of days to have a good count of working days in the month.
In fact, it was a quick and dirty way to take care of something that went in production with a flaw : the need for working days has been detected in a last minute reporting, and it was a faster solution that writing an additional interface.

To me, this is not even thinkable. I dislike very much the production first cogitation last way of handling such problems, mostly thanks to business pressure.
Being a freelance I can’t really blame, but this lead to something i’m qualifying as ”fajitas developement”.

There is a calendar table in our datawarehouse. It is very handful to match, by exemple, a fiscal axis to the calendar. Also, if you’re working with some datas which you’re taking care of the evolution – meaning you have an application date in your primary key – it is extremely efficient in both querying and building sexy sheets and graphics.

Databases can’t create value for you. If you have an invoice the 3rd and another the 5st, no simple query can tell you what’s the invoice amount for the 4th, it is not zero, it is not even null, it is just the void. Having a calendar table does this for you : allowing you to fill every small voids you may have in your tables, to build nulls or zeros, at aggregating or reporting times.
This may sound futile, but trust me on this, it is not. I’ll write a full post about this.

For querying performance, the calendar table I’m using is denormalized : that’s explaining how my client was able to update some unupdatable data.

I have in my calendar table the day value as a date : I don’t use surrogated for this.
Then I have the calculable columns : the day number (in the week), the iso-year / iso-week couple, the month, the quarter and the year.
After, I have business specific column, with a fiscal axis (monthly, quarterly, yearly), and two recently added columns : holidays and an accounting closure indicator (which values 0 for classic day, 1 for the accounting closure day and 2 for the days after and still in the same month).

Between the day number, the holidays and accounting closure now my client can choose the way his reported working days are choosen.

And mondays are no longer sundays.