Generating Date Dimension data in Microsoft Excel

Generating Date Dimension data in Excel

Having shared the SQL technique and the script for generating data for Date Dimension for use in self-service BI and Analytics in my , here I share another technique for the same purpose but for those of you who are not familiar with SQL.

Here is the that lets you create Date Dimension data for two years from a given start date. All that you have to do is enter the desired start of the year in date format in cell B3. Rest is taken care that everything refreshes automatically.

Hit the like stars if you find it useful. Share the post further to spread the learning. Follow the analyst arsenal for effortless continued learning. Happy Learning..!!

Advertisements

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

 

Truncating time part from date & time data in Excel

Did you ever come across a situation where you had to compare two dates ignoring their time part to arrive at a status indicator ? Let’s see an example and the trick to address it:

Example Scenario: Here you have some order ids and their respective order dates, target dates and delivery dates as in the below screenshot. Now, you are asked to arrive at the order status using a business rule that states – “If the delivery date is on or before target date, disregarding the time of the day, then update the status as ‘Target Met’ else update it as ‘Past Due’.”

Excel_TruncateTime_1

so the business rule when translated into Excel formula, looks like this: =IF(D2<=C2, “Target Met”, “Past Due”)

When this formula is entered in cell E2 and copied down till E8, the order status looks as in the below figure:

Excel_TruncateTime_2

As per the business rule, the comparison of Order Date and Delivery Date should ignore time part of the day. Irrespective of what time of the day the order/product has been delivered, if the delivery date falls on the target date, the status should be updated as ‘Target Met’.

However, the status’ for Order IDs 12126 and 11231 are updated as ‘Past Due’ although they are expected to be updated as ‘Target Met’.

Challenge: While comparing Order Date and Delivery Date, Excel is comparing the complete date and timestamps.

Solution: To let Excel consider only dates and ignore timestamps while comparing, truncate the timestamps from dates using INT function, before doing the comparison. So the formula becomes:     =IF(INT(D2)<=INT(C2), “Target Met”, “Past Due”)

Now the result appears as desired as in the below figure.

Excel_TruncateTime_3

Explanation: All dates and timestamps in Excel are actually serial numbers. 1-Jan-1900 is treated as 1, 2-Jan-1900 is treated as 2 and so on. While Excel also stores timestamps as fractional numbers between 0 and 1. 0 represents 12:00:00 AM (start of the day) and 0.999 represents 11:59:59 PM (end of the day).

Using this process, in our example, for the Order ID 12126, the numerical representation of Order Date becomes 41443.66 and for Delivery Date, it becomes 41443.68. When these two values are compared, Delivery Date (41443.68) is not less than Order Date (41443.66), so the status is updated as ‘Past Due’.

INT() is a function in Excel that takes a number as input and returns the number rounded down to the nearest integer. i.e., for Order ID 12126, INT(D4)<=INT(C4) becomes INT(41443.68)<=INT(41443.66) which evaluates to 41443<=41443 which is TRUE. Hence the status gets updated as ‘Target Met’.

Hope you enjoyed reading this post..!! Want to master Excel with more such techniques..? Follow my blog by entering your email and then hitting on FOLLOW button at the top right or simply click on the FOLLOW button at the bottom right of the window, if it appears in your browser.

Do leave your comments or questions, if any. Happy learning.. Smile

Truncate time part from Datetime column or literal in Oracle SQL

I have shown how to truncate the time part from a datetime column value in SQL Server T-SQL in one of my previous posts here –

https://analystarsenal.wordpress.com/2012/12/19/comparing-datetime-column-with-a-date-literal-in-t-sql/

Here is the same (how to truncate the time part from a datetime column value) in Oracle SQL:

TRUNC(datetime_column)

or

TRUNC(datetime_expression)

very straight forward…!! isn’t it.. ? 🙂

Comparing datetime column with a date literal in T-SQL

Hi Friends..

I have seen that comparing a datetime column in a table in SQL Server database, that has both date and time parts (e.g. 2012-12-05 10:37:36.897) with a date literal that has only date part is a not straight forward. Let me explain with an example:

T-SQL DateTime Comparison

Have created a temporary table with two date columns – From_Date and To_Date and inserted a couple of random date values. First output shows the table contents. Observe that the timestamps are also included with the date values. 

Now declared a date variable and assigned a date value without timestamp. Then retrieved results from the table where From_Date is less than or equal to the date variable in two ways: 

– once by converting the From_Date column into string taking the format “mm/dd/yyyy” (note the format style 101 in the convert function syntax) and then casting this date string back to datetime data type. This removes the timestamp from the datetime values.

– the other time by comparing the direct comparison between From_Date datetime column and datetime variable. This comparison compares the both date and time pieces of the From_Date column and the datetime variable, hence resulting in incorrect(less) number if results. 

Hope that helps. Do leave your comments or questions, if any.. 🙂