Oracle SQL script to generate Date Dimension data for use in Microsoft Excel Data Models

Date dimension is that one dimension or business perspective without which an Excel Data Model cannot ever exist. Here is an Oracle SQL script to generate the data for a Date Dimension. All that you have to do is change the start date and end date values in the in the last line of the below SQL query and execute it. Try it and experience the magic..!! Please leave your suggestions, comments and any enhancements that you might want to see in this.

Here is the downloadable script on Oracle LiveSQL:

/***********************
Description: Date Dimension Data Generation
Date Created: 14/Nov/2016
Author: Balakrishna Sappa
************************/

CREATE OR REPLACE TYPE DATE_OBJ AS OBJECT(DATE_KEY VARCHAR2(8), DATE_DT DATE, DATE_TYPE VARCHAR2(10), DAY_OF_MONTH SMALLINT, CAL_DAY_OF_YEAR SMALLINT, DAY_OF_WEEK_FULL_NAME VARCHAR2(9)
, DAY_OF_WEEK_SHORT_NAME CHAR(3), CAL_MONTH_FULL_NAME VARCHAR2(9), CAL_MONTH_SHORT_NAME CHAR(3), CAL_MONTH_ENUM SMALLINT, CAL_QTR_NAME CHAR(2), CAL_QTR_ENUM SMALLINT, CAL_HYR_NAME CHAR(2)
, CAL_HYR_ENUM SMALLINT, CAL_YEAR INT, START_OF_MONTH DATE, END_OF_MONTH DATE);
CREATE OR REPLACE TYPE CALENDAR_DATES IS TABLE OF DATE_OBJ;
CREATE OR REPLACE FUNCTION UDF_CALENDAR_GENERATOR(START_DATE IN DATE, END_DATE IN DATE)
RETURN CALENDAR_DATES
IS
CALENDAR_DATE CALENDAR_DATES;
BEGIN
SELECT CAST(MULTISET(WITH RCTE_DATES (DATE_DT)
AS
(SELECT START_DATE AS DATE_DT
FROM DUAL
UNION ALL
SELECT DATE_DT + 1 AS DATE_DT
FROM RCTE_DATES
WHERE DATE_DT + 1 <= END_DATE
)
SELECT CAST(EXTRACT(YEAR FROM DATE_DT) AS CHAR(4)) || SUBSTR(’00’ || EXTRACT(MONTH FROM DATE_DT), -2,2) || SUBSTR(’00’ || EXTRACT(DAY FROM DATE_DT), -2, 2) AS DATE_KEY
, DATE_DT
, ‘DATE’ AS DATE_TYPE
, EXTRACT(DAY FROM DATE_DT) AS DAY_OF_MONTH
, CAST(TO_CHAR(DATE_DT, ‘DDD’) AS SMALLINT) AS CAL_DAY_OF_YEAR
, TO_CHAR(DATE_DT, ‘DAY’) AS DAY_OF_WEEK_FULL_NAME
, TO_CHAR(DATE_DT, ‘DY’) AS DAY_OF_WEEK_SHORT_NAME
, TO_CHAR(DATE_DT, ‘MONTH’) AS CAL_MONTH_FULL_NAME
, TO_CHAR(DATE_DT, ‘MON’) AS CAL_MONTH_SHORT_NAME
, EXTRACT(MONTH FROM DATE_DT) AS CAL_MONTH_ENUM
, ‘Q’ || TO_CHAR(DATE_DT, ‘Q’) AS CAL_QTR_NAME
, CAST(TO_CHAR(DATE_DT, ‘Q’) AS SMALLINT) AS CAL_QTR_ENUM
, ‘H’ || CASE WHEN EXTRACT(MONTH FROM DATE_DT) IN (1,2,3,4,5,6)
THEN 1
ELSE 2
END AS CAL_HYR_NAME
, CASE WHEN EXTRACT(MONTH FROM DATE_DT) IN (1,2,3,4,5,6)
THEN 1
ELSE 2
END AS CAL_HYR_ENUM
, EXTRACT(YEAR FROM DATE_DT) AS CAL_YEAR
, TRUNC(DATE_DT, ‘MONTH’) AS START_OF_MONTH
, TRUNC(ADD_MONTHS(DATE_DT, 1), ‘MONTH’)-1 AS END_OF_MONTH
FROM RCTE_DATES
UNION
SELECT ‘999999’ AS DATE_KEY
, TO_DATE(’31-DEC-9999′, ‘DD-MON-YYYY’) AS DATE_DT
, ‘NA’ AS DATE_TYPE
, 0 AS DAY_OF_MONTH
, 0 AS CAL_DAY_OF_YEAR
, ‘NA’ AS DAY_OF_WEEK_FULL_NAME
, ‘NA’ AS DAY_OF_WEEK_SHORT_NAME
, ‘NA’ AS CAL_MONTH_FULL_NAME
, ‘NA’ AS CAL_MONTH_SHORT_NAME
, 0 AS CAL_MONTH_ENUM
, ‘NA’ AS CAL_QTR_NAME
, 0 AS CAL_QTR_ENUM
, ‘NA’ AS CAL_HYR_NAME
, 0 AS CAL_HYR_ENUM
, 9999 AS CAL_YEAR
, TO_DATE(’31-DEC-9999′, ‘DD-MON-YYYY’) AS START_OF_MONTH
, TO_DATE(’31-DEC-9999′, ‘DD-MON-YYYY’) AS END_OF_MONTH
FROM DUAL
)
AS CALENDAR_DATES
)
INTO CALENDAR_DATE
FROM DUAL;
RETURN CALENDAR_DATE;
END;

Below is as screenshot of the results of this SQL executed in Oracle’s fee online SQL learning and execution tool: Oracle Live SQL.

datedimensiondatagenerator

 

Advertisements

Calculation of metrics at database level or reporting level..? which is the best way..?

I just came across a same old situation of deciding where to do the calculation of certain metrics like percentages ?

Here is my take on it with a fictitious example –

Scenario 1:  Calculating it at database level and preserving it in the database:

see Fig.:1(Associate Level Sales) showing a table with sales made by a few sales executives in the years 2011 and 2012. Assuming that the percentage increase or decrease in the sales made by each of these executives is calculated at the database level and preserved in the database, the table appears as in the Fig.1 (Associate Level Sales).

Benifits: Now, since the calculations are materialized/preserved in the database, the performance of the reports containing this percentage calculation might be better, as the reports need to directly pull this calculation from the database instead of calculating on the fly.

Caveats: However in this scenario, the problems surfaces when these sales figures are expected to be aggregated at Manager level. See Fig.: 2(Manager Level Sales Pivot table) showing a pivot table built on top of the table data as in Fig.:1(Associate Level Sales) to arrive at the aggregated percentage change calculations. The percentage chanage calculations are wrong since they are doing the sum of the percentage changes at the associate levels to aggregate at the Manager level. whereas the calculations should have happenned as in the Fig.4.
In Fig.4 First manager level volumes are arrived at by adding up the associate level volumes. Percentage changes are then calculated on these manager level aggregated volumes.

PercentCalculationsDecisioning
PercentCalculationsDecisioning

Scenario 2:  Calculating it at the report level (e.g. pivot table in MS Excel or OBIEE or some other reporting tool) on the fly:

In this scenario, a pivot table is built as in scenario:1, but the percentag change calculation is done inside the pivot table as a calculated item using the formula (sales of 2012 – sales of 2011)/sales of 2011. see the results in Fig.: 3. Now these calculations match exactly with those of the Fig.: 4.

Benifits: Aggregations at higgher levels are calculated correctly.

Drawbacks: Since the percentage change calculation is not preserved in the database, it is calculated on the fly everytime the pivot table is refreshed resulting in report performance drop down.

Conclusion:  It’s just common sense that certain measures cannot be aggregated directly. Such measures are called Non-Additive measures. Additive measures should be preserved in database and Non-Additive measures should be calculated at the report level on the fly.

Hope you enjoyed reading this post. Please do share your comments, suggestions or questions, if any and help me improving this further..

Do rate this blog post on the top in terms of number of starts. The more you like it, the more the number of stars.. Happy time until my next blog post.. 🙂