## Time periods in a star schema?

### Time periods in a star schema?

Howdy,

How does one model time periods in fact tables?

Suppose I have dimensions:
DIM_DATE (granularity = a day)
DIM_TIME (granularity = a second)
DIM_SESSION (granularity = a session)

Now I would like to design a fact table modeling
session durations. My idea is the following:

FCT_SESS_DUR:
DATE_ID_FROM
TIME_ID_FROM
DATE_ID_TO
TIME_ID_TO
SESSION_ID

I would like to feed then this fact table into
an OLAP server, that is I'd like to calculate
all possible aggregates there, so FCT_SESS_DUR
must be flexible. Any other ideas for modeling
time periods?

### Time periods in a star schema?

The granularity of the fact table ( mostly ) does not consist in a change in
the data model. It might consist of pre-aggregating information stored in
the fact table however... Using the MS food mart as an example, the
transactional fact is whenever someone buys any one thing from the grocery
store... The fact sales units and sales dollars  are stored in the fact
table along with the PK of the product dimension and the time dimension when
the fact occured. The time dim may have yy.mm.dd and hh.mm.... If you choose
the grain of the fact table to be a daily grain. then you would add up all
of the sales of a particular product for a given day and store the sum of
sales units and sales dollars in the fact table, with PKs to the product and
the time dimension. This time the time dimension will probably have only
yy.mm.dd and allow the time to default to 0 ( since it is not relavent for
this grain.) The grain of the fact table MUST match the grain of the
dimensions used with it...

Regarding your second question, one would probably model the session
duration as a fact  (session duration seconds) and the Time dim PK could
contain the begin time....

Quote:> Howdy,

> How does one model time periods in fact tables?

> Suppose I have dimensions:
>     DIM_DATE (granularity = a day)
>     DIM_TIME (granularity = a second)
>     DIM_SESSION (granularity = a session)

> Now I would like to design a fact table modeling
> session durations. My idea is the following:

>     FCT_SESS_DUR:
>         DATE_ID_FROM
>         TIME_ID_FROM
>         DATE_ID_TO
>         TIME_ID_TO
>         SESSION_ID

> I would like to feed then this fact table into
> an OLAP server, that is I'd like to calculate
> all possible aggregates there, so FCT_SESS_DUR
> must be flexible. Any other ideas for modeling
> time periods?

### Time periods in a star schema?

Quote:> Regarding your second question, one would
> probably model the session duration as a
> fact  (session duration seconds) and the
> Time dim PK could contain the begin time....

I'm still undecided between this option, and
putting two pairs of date+time FKs into fact
table.

Why? Because this way I'd be able to display
from-to histograms of session durations. Example:
session A from 2 to 4 PM, session B from 3 to 5 PM,
and session C from 2 to 6 PM.

This way I can produce the following
histograms:

A|xxx
B| xxx
C|xxxxx
-----
23456

(Non-proportional font needed for proper display.)

Not only this, I can narrow down the query/histogram
above to 3 AM only:

A|x
B|x
C|x
-----
3

### Time periods in a star schema?

You might want to read Ralph Kimballs article on time dimensions which I
found quite interesting. It is called Tricky Time Spans and you can find
it here:

http://www.intelligententerprise.com/020613/510warehouse1_1.shtml

I think what you have in mind is considered the 'hardest' type of
dimension.

I cannot give you any hints myself, since I just started with Olap and
cubes.

Lutz

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

I would like to exchange the logical design of
our star schema  time and geography
dimensions with others.  Please let me
know if you would like to do so.  I think it
would be helpful to see how others have
approached these two common dimensions.

Ron