Date Dimension

VinayR · ‎03-14-2022

Introduction

Date is a common dimension used in most application deployments. It is primarily used to roll up data so it can be viewed across a broad time range, facilitating trend analysis.

Most date rollup level functionality is built within Incorta. For example, if we have a column with the data type Date, we can use a built-in function to summarize the data to a higher level (week, month, quarter and year).

What you need to know before reading this article

Let's Go

Why is a date dimension needed?

A date dimension is needed because we need a common dimension to view data across different subject areas. For example, to view sales revenue and cost side by side by periods, a common dimension can help aggregate the data and display the information in more meaningful manner.

How to create a common date dimension

Incorta provides a file, Date_US.csv with the date rollup levels as different columns. It can be used as is if we are running our business in the US and are using the Gregorian calendar. If the business runs in a different country or in a different calendar or has any other period nuances (which day is considered start of the week, how are weeks vs. end of periods handled if the quarter or year ends mid-week), this file can be modified as per requirements.

When we deploy the Audit schema provided by Incorta, the Date_US.csv and a sample date schema table is created in the Audit schema.

In addition to the Date_US.csv file, we can also take a Materialized View based approach. Incorta MV can be used to generate the date table with one day per row and various attributes that can be used for rolling up the data or filtering data.

How do we support workdays or holidays?

In the Date_US.csv, we have a column that indicates if a date is a workday. When you need to count the number of workdays between two days, the day sequence can be used.