Advice on Fact Table Design

Hi,

I'm doing some preliminary analysis on designing a solution that models a warrant process for a police force. I’m a little unclear on the optimal way to design the fact table and am looking for some guidance.

Here are the key characteristics of the process

•A warrant has 2 possible paths that it can take. Both these paths involve changes in the warrant status.

Path 1 – The warrant is executed
Not Tracked ---> Issued---> Executed

Path 2  - The warrant is cancelled
Not Tracked ---> Issued ---> Cancelled

•Each of the possible status values on the 2 paths has a date associated with it which is a milestone date for that status change

Status                      Date

Not Tracked                   Date the warrant was created
Issued                           Date the warrant was issued
Executed                       Date the warrant was executed
Cancelled                       Date the warrant was cancelled

My understanding is that looks like a good candidate for an Accumulating snapshot fact design (so far!).

However, things get more complicated…

•A warrant can be forwarded between stations multiple times before it is ultimately executed or cancelled. For example, suppose I have warrant no: 123.

On Jan 1 2013, it’s forwarded to Station A
On Jan 28 2013, it’s forwarded to Station B from Station A
On Feb 28 2013, it’s forwarded to Station C from Station B

The number of forwardings is completely variable and can be anything from 1 to N.  Each forwarding to another station has a date associated with it but it seems to me that the variable number of forwardings makes a single accumulating snapshot fact with forwarding dates impractical.

Reporting Requirements


The key requirement is to be able to perform point in time reporting (e.g. end of a month or week)  and answer the key question:

“How many On Hand warrants (status of Not Tracked or Issued) does the organisation have as at this date (date being a variable) and where are they located?”

Suppose that warrant 123 above was the only warrant that existed and I was reporting on the number of On Hand warrants at 3 different dates. Furthermore, suppose that the warrant is still On Hand.

On Hand Count
Date                     Station  A             Station  B              Station C
Jan 1 2013                     1                          0                          0
Jan 28 2013                    0                          1                          0
Feb 28 2013                   0                          0                          1

As you can see, the counts by station vary depending on the point in time at which you report.

Any opinions on a suggested fact design welcome?

Sounds like you need a regular snapshot fact that looks like an accumulating snapshot. Make sense?

Keeping it simple and at lowest grain, I would recommend a regular snapshot . Cant do an accumulating snapshot since we dont know how many stations are involved, Also the reporting requirement doesn't call for one. Here is how the fact table would look like:

Warrant Tracking Fact
--Warrant Dimension (Stored warrant specific info)
--Station Dimension (Which station the Warrant is on currently.
--Status Dimension (Tracking status Dimension, You can also have this status as part of Warrant Dim)
--Record Snaphot date (Regular snapshot) Date on which the fact table got loaded)
--Count (always be 1 for any specifc Warrant)

Lets see how it can answer the reporting question : “How many On Hand warrants (status of Not Tracked or Issued) does the organisation have as at this date (date being a variable) and where are they located?”

Select Warrant, Station, Record Snaphot date, Sum(Count) from
where Record Snaphot date = <17-July-2013>
 and Status in (Not Tracked , Issued)
Group by Warrant Dimension, Station Dimension, Record Snaphot date

Thanks for the feedback...I'll have a go at a regular snapshot and see how I get on