Time Dimension - Oracle

Time Dimension

Quite simple!

I will create a Time Dimension. It is sometimes used when the client wants to have this grain.

Sometimes, I saw my colleagues put this information on the same date dimension. This seems wrong, very wrong, you will populate a dimension quite large, it will consume a lot of disk space and CPU processing when creating analysis.

Let's calculate an example:

One day has 60 seconds, 60 minutes and 24 hours.

Then, 60 * 60 * 24 = 86400.

If you have two years of data to use in your dimension, you will have:

86400 * 365 * 2 = 6307200.

Wow! is a lot of data!

But if we divide into two dimensions, we will have a cleaner model and practical to create our analysis.

I'll use Oracle 11gR2 for this table.

Let's create our table:

DROP TABLE DIM_TIME;

CREATE TABLE DIM_TIME

AS

SELECT 

   n AS SK_TIME,

   TO_NUMBER(TO_CHAR(to_date(n,'SSSSS'),'HH24')) AS HOUR,

   TO_NUMBER(TO_CHAR(to_date(n,'SSSSS'),'MI'))   AS MINUTE,

   TO_NUMBER(TO_CHAR(to_date(n,'SSSSS'),'SS'))   AS SECOND,

   TO_CHAR(to_date(n,'SSSSS'),'HH24') || ':' || TO_CHAR(to_date(n,'SSSSS'),'MI') || ':' || TO_CHAR(to_date(n,'SSSSS'),'SS') AS TIME,

   CASE WHEN n BETWEEN 0 AND 21599 THEN 'DAWN'

        WHEN n BETWEEN 21600 AND 43199 THEN 'MORNING'

        WHEN n BETWEEN 43200 AND 64799 THEN 'AFTERNOON'

        ELSE 'NIGHT'

   END AS PERIOD,

   SYSDATE AS LOAD_DATE

FROM (

   SELECT

      level-1 n

   FROM 

      DUAL

   CONNECT BY LEVEL <= 86400

);

Guithub file:
https://github.com/igorgentil/PLSQL/blob/master/SCRIPT_DIM_TIME.sql

Much better!

Enjoy!