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....
> 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:
> 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?