Dylan's BI Study Notes

My notes about Business Intelligence, Data Warehousing, OLAP, and Master Data Management

Generate Date dimension in PL/SQL Table Function

Posted by Dylan Wan on October 30, 2015

Almost all data warehouse have a date dimension.  The purpose of the date dimension is to provide some pre-calculated grouping for dates.  It helps rolling up the data that entered against dates to a higher level, such as year, quarter, month, week, etc.

In some system, source files are used in generating the date dimension.  IMHO, it makes the modification to the logic difficult.  In some ETL programs, the task involves various table joins, try to generate the rows for the year range.

This post is for describing how to populate a table with rows for each date for a given year range.

  1. Use the PL/SQL Table Function
  2. Use SQL Date functions

Use PL/SQL Table Function

PL/SQL Table function is a special type of PL/SQL function that can be called in the FROM clause of a SQL statement.  Typically the PL/SQL functions are used in the SELECT or WHERE clause and process the input values and return a single value.

The PL/SQL Table function, on the other hand, generate the result set in a tabular format.  Since it is an in memory processing.  I distribute the work to generation of rows and generation of the date attributes and the varies of grouping columns.

Use SQL Date Functions

Oracle already provides a strong capability of handling the date data type.  In the script, I just uses a couple of them.

The purpose of the script is to demonstrate that when the ETL is running against an Oracle database, there is a alternate way to generate the seed rows and generate the date dimension.  Sometime dealing with files is tricky.  Using a PL/SQL function may be easier.

The SQL can be downloaded from



Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s