Modelling Time Span with OLAP tools

This is more of a design question. I am trying to create a fact table for ti
me spans. For example an order might go through the following status of Crea
ted, In-Progress, Delivered. The time span between any 2 states could range
from minutes to days to mon
ths. I want to be able to see the current status and also the status as of l
ast month, or last quarter or some other previous date.
Some of the recommendations by extert sites are to have a date in your fact
table for each status accompained by a field
for storing a value 1. So the order fact by look like
Order Id
Order Create Date
Order Created Value
Order In-Progress Date
Order In-Progress Value
Order Delivered Date
Order Delivered Value
Also I am creating my own time dimension. The above approach will work very
well when you use SQLs to access your star schema, but I am not so sure this
approach will work for olap tools like MSAS.
Is there an alternative?
ThanksWhat you have here looks like an accumulating snapshot fact table. These
are best employed IMHO where you have a business process/relatively short
with milestones which you want to track.
It means that you have less rows in your fact table but that you will
revisit the row to perform UPDATEs, something not typically done.
This type of fact table is good for measuring time lags between business
processes i.e.
Time from Order -> Sale
Time from Sale -> Ship
The way I do this is to create a view over the top of the fact table which
calculates these figures for me. This way i can then use it as a measure
just like any other. I usually do it to difference in hours.
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Break It" <anonymous@.discussions.microsoft.com> wrote in message
news:13CB4DFA-500D-4268-B850-5EE207F917CD@.microsoft.com...
> This is more of a design question. I am trying to create a fact table for
time spans. For example an order might go through the following status of
Created, In-Progress, Delivered. The time span between any 2 states could
range from minutes to days to months. I want to be able to see the current
status and also the status as of last month, or last quarter or some other
previous date.
> Some of the recommendations by extert sites are to have a date in your
fact table for each status accompained by a field
> for storing a value 1. So the order fact by look like
> Order Id
> Order Create Date
> Order Created Value
> Order In-Progress Date
> Order In-Progress Value
> Order Delivered Date
> Order Delivered Value
> Also I am creating my own time dimension. The above approach will work
very well when you use SQLs to access your star schema, but I am not so sure
this approach will work for olap tools like MSAS.
> Is there an alternative?
> Thanks