Laravel - Creating a date dimension (calendar) table

What is a date dimension table

Date Dimension is a table that has one record per each day, no more, no less! Date dimension plays a vital role in your data warehouse designing, it provides the ability to study behavior and trend of your data over a period of time.

Most BI tools have an in-built date dimension table, but sometimes you might want to create one. Date dimension table is only created and loaded once since the data is not changed that often.

This article is inspired by the article "Creating a date dimension or calendar table in SQL Server". The article is specific for MS SQL database, but in our example, we'll create one using Laravel and Carbon so it can work with any kind of database driver.

We will be creating the following files with Laravel.

  • Migration - To create the table.
  • Model - To reference the table.
  • Console Command - To populate the table.

Step 1 - Create the migration

The columns in this table are self-explanatory, you can also google them if you want to learn more.

Step 2 - Create the model

In the model we are also typecasting the attributes, so it sets/gets the value in the correct format.

Step 3 - Create a command

In this command, update the start date and end date (line 55) as per your requirement. Also, the quarter in this script run from November-January, February- April, May-July, August-October, this might be different for your use case, so it should be updated accordingly.

Conclusion

In this article you were able to design a date dimension table using Laravel. The migration, commands, etc. are database driver independent. So you can essentially create this table in MS SQL or MySQL or any other database supported by Laravel.

References

Posted in DATABASE, PHP, LARAVEL, BLOG
Akki K

Akki

Experienced Backend Developer.