Date and Time Dimension

Date and time dimensions can be used in a dimensional model to analyze and report data more efficiently.

The Date Dimension table has date-specific attributes that include day, date, weeks, quarter, months, fiscal period, national holiday indicators, quarter, and year.

The Time Dimension table has time-specific attributes that include hour formats, minute of the day, seconds of hour, quarter hour, and am or pm.

Note: Both lists are not exhaustive.

How to Use Date and Time Dimensions

This is how you can access and use Date and Time dimensions in Astera Data Stack:

To get the Date Dimension object on the data model, go to Toolbox > Data Model > Date Dimension and drag-and-drop it onto the data model designer.
To get the Time Dimension object on the data model, go to Toolbox > Data Model > Time Dimension and drag-and-drop it onto the data model designer.

You cannot make any modifications in the Date and Time dimension objects. If you right-click on a Date Dimension or Time Dimension object and open its properties, you would see that the Entity Properties options have been disabled for the objects.

The next screen is the Layout Builder, where you can view the entity layout and its specifications. All other options are set to default and cannot be modified.

Next is the Data Model Entity Indexes screen, where you’ll find a list of predefined indexes. Rest of the options for indexes are disabled.

At this point, we can create relationships between the fact entity and the newly added date and time dimensions respectively, and then forward engineer the data model for the date and time dimension tables to be reflected in the target database.

To learn more about Forward Engineering, click here.

To fill the date and time dimension objects with data, follow the steps below:

To fill a Date Dimension table – Right-click on the Date Dimension object and select the Fill Date Dimension Table option. Astera Data Stack would automatically fill values in the provided database table.

To fill a Time Dimension table – Right-click on the Time Dimension object and select the Fill Time Dimension Table option. Astera Data Stack would automatically fill values in the provided database table.

Replacing Date/Time Elements with Date/Time Dimension Relationships:

A Date/Time field within the Fact entity can be replaced with the Date/Time Dimension Relationship, as follows:

Right-click the Date/Time field you want to replace with Date/Time Relationship and select ‘Replace with Date Dimension Relationship’ or Replace with Time Dimension Relationship’ option from the menu.

A Date or Time Dimension entity will be created, and a relationship will be created between the newly created Date/Time dimension and the fact table.

Notice that the Date/Time Field that was replaced with the Date/Time Dimension relationship has been marked as a foreign key that refers to the newly created Date/Time Dimension.

This concludes our discussion on using date and time dimensions in Astera Data Stack.