Skip to content

Dynamic 15 Minutes Dimension

November 19, 2013

Often we need to build dashboard using 15 minute snapshots fact tables. Commonly those dashboards allow selecting different snapshots in the near past only. It is common practice to create a 15 minutes dimension and populated it with data for several years, this produce large than necessary dimension tables.

Here, I show a simple technique to create a dynamic 15 minutes dimension table for the past 15 days, build as a derived view from existing date and time dimensions.

I tested this procedure in Sybase IQ 15.2 ESD3.

Existing date dimension Structure

The date dimension contains one row for each day and is populated for several years.

Column Name

Data Type

Data Example

ddatID (PK)

integer

20131119

ddatDate

date

19/11/2013

ddatYear

smallint

2013

ddatDtYyyyMmDd

varchar(10)

2013-11-19

ddatMonDdYyyy

varchar(11)

Nov 19 2013

ddatDayShort

varchar(3)

Tue

ddatDayLong

varchar(10)

Tuesday

ddatDayNumOfWk

tinyint

3

ddatDayNumOfMon

tinyint

19

ddatDayNumOfYr

smallint

323

ddatWkNumOfYr

smallint

47

ddatMonShort

varchar(3)

Nov

ddatMonLong

varchar(10)

November

ddatMonNumOfYr

tinyint

11

ddatQtr

tinyint

4

ddatYrQtr

integer

201304

ddatMonNumOfQtr

tinyint

2

ddatQtrShort

varchar(2)

Q4

ddatQrtLong

varchar(7)

2013Q4

The primary key of the date dimension is an integer in the format yyyymmdd.

Existing Time Dimension Structure

The time dimension contains one row for each second in a day and is populated (4018 rows).

Column Name

Data Type

Data Example

dtmID (PK)

integer,

1030

dtmTime

time,

10:30:00 AM

dtmQtrHrNum

tinyint,

3

dtmQtrHrStartFlag

bit,

True

dtmQtrHrStart

time,

10:30:00 AM

dtmQtrHrEnd

time,

10:44:59 AM

dtmQtrHrText24

varchar(14),

10:30 – 10:45

dtmQtrHrText12

varchar(20),

10:30AM – 10:45AM

dtmQtrHrShort24

varchar(5),

10:30

dtmQtrHrShort12

varchar(7),

10:30AM

dtmHr

tinyint,

10

dtmHrStartFlag

bit,

False

dtmHrStart

time,

10:00:00 AM

dtmHrEnd

time,

10:59:59 AM

dtmHrText24

varchar(5),

10:00

dtmHrText12

varchar(8),

10:00AM

dtmDayPart

varchar(2),

AM

dtmDayPartStartFlag

bit,

False

dtmWorkShiftNum

tinyint,

1

dtmWorkShiftStartFlag

bit,

False

dtmWorkShiftText24

varchar(14),

07:00 – 15:00

dtmWorkShiftText12

varchar(20),

07:00AM – 03:00PM

dtmWorkShiftStart

time,

7:00:00 AM

dtmWorkShiftEnd

time,

2:59:59 PM

The primary key of the time dimension is a sequential integer on the format hhmm (0 – 2359)

The Dynamic 15 Minutes Dimension

create view DBA.dim15Minutes
as
select
((D.ddatID * 10000) + T.dtmID) d15mID,
D.ddatMonDdYyyy + space(1) + T.dtmQtrHrShort12 d15mDtmMonDdYyyyHrMn,
D.ddatDtYyyyMmDd d15mDtYyyyMmDd,
D.ddatMonDdYyyy1 d15mDtMonDdYyyy,
D.ddatDayShort d15mDayShort,
substring(dtmQtrHrShort24,1,2) d15mHr,
substring(dtmQtrHrShort24,-1,-2) d15mMin
from DBA.dimDate D, DBA.dimTime T
where dtmQtrHrStartFlag = 1
and D.ddatDate between current date 15 and current date + 1

Example of rows in the 15 minutes dimension

select top 10 * from DBA. dim15Minutes
where d15mDtYyyyMmDd = ‘20131119’
order by d15mHr, d15mMin

Row

d15mID

D15mDtmMonDdYyyyHrMn

D15mDtYyyyMmDd

D15mDtMonDdYyyy

D15mDayShort

D15mHr

D15mMin

1

201311190000

Nov 19 2013 12:00AM

20131119

Nov 19 2013

Tue

0

0

2

201311190015

Nov 19 2013 12:15AM

20131119

Nov 19 2013

Tue

0

15

3

201311190030

Nov 19 2013 12:30AM

20131119

Nov 19 2013

Tue

0

30

4

201311190045

Nov 19 2013 12:45AM

20131119

Nov 19 2013

Tue

0

45

5

201311190100

Nov 19 2013 01:00AM

20131119

Nov 19 2013

Tue

1

0

6

201311190115

Nov 19 2013 01:15AM

20131119

Nov 19 2013

Tue

1

15

7

201311190130

Nov 19 2013 01:30AM

20131119

Nov 19 2013

Tue

1

30

8

201311190145

Nov 19 2013 01:45AM

20131119

Nov 19 2013

Tue

1

45

9

201311190200

Nov 19 2013 02:00AM

20131119

Nov 19 2013

Tue

2

0

10

201311190215

Nov 19 2013 02:15AM

20131119

Nov 19 2013

Tue

2

15

Advertisements
Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: