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.
- Use the PL/SQL Table Function
- 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