Modelling minute-level granularity

Moderators: chulett, rschirm

Post Reply
gateleys
Premium Member
Premium Member
Posts: 992
Joined: Mon Aug 08, 2005 5:08 pm
Location: USA

Modelling minute-level granularity

Post by gateleys »

We have a datamart that consists of a factless fact table that keeps track of the statuses of contracts which are loaded daily (incremental). This has allowed users to prepare various kinds of reports. Now, there is a strong voice for extending the mart to allow analyis of response time that it took for contract to go from one status to another.

Now, since a contract may undergo a number of status changes in a day, the granularity of TIME will have to be 'minute' and not 'day' like we currently have. This would also mean that the all the dimensions tie up to the fact on some 'Minute_ID' and not 'Day_ID'. Further, the dimensions will not be conformed to our existing BUS.

However, We seem to have no option, but to create this as an isolated datamart satisfying one particular requirement. Is it possible that the requirement be fulfilled without the new model, but by making some changes in the existing daily granule model itself?

Thanks
kduke
Charter Member
Charter Member
Posts: 5227
Joined: Thu May 29, 2003 9:47 am
Location: Dallas, TX
Contact:

Post by kduke »

Sure, you could have 2 fact tables. One at the current level. The other just has the statuses plus the minute id. You need to join your 2 fact tables at the day level. So you would also need the day_id. You need to figure out what other keys you need to make join work. You will also need an index on all these keys.

Another option is to create a surrogate key on the old fact table. Place this key in the new fact table and join on this one field. This is a much more pure or textbook way to solve this issue. This field needs to be the primary key for the old fact table.

MicroStrategy and other tools have the ability to join 2 fact tables. This will save a lot disk space. The performance maybe better than loading 60 * 24 more records.
Mamu Kim
gateleys
Premium Member
Premium Member
Posts: 992
Joined: Mon Aug 08, 2005 5:08 pm
Location: USA

Post by gateleys »

Considering that the new Fact makes ties to the new MINUTE dimension while the old Fact is at DAY level, if the 2 facts are then joined via the DAY_ID, won't we lose out on the symmetry of the query when we seek the response time between statuses for a particular contract ?

Thanks
kduke
Charter Member
Charter Member
Posts: 5227
Joined: Thu May 29, 2003 9:47 am
Location: Dallas, TX
Contact:

Post by kduke »

The surrogate key concept is cleaner. Then all the keys that made up the uniqueness of the original fact record and are represented in the surrogate key. So whatever is less work or works better either you keep adding dimension keys from the original fact table or you add a surrogate key to it. You need to decide. I have seen both done. I prefer the surrogate key. It is cleaner.
Mamu Kim
gateleys
Premium Member
Premium Member
Posts: 992
Joined: Mon Aug 08, 2005 5:08 pm
Location: USA

Post by gateleys »

Thanks Kim,
Using a surrogate key in the original fact and using it in the new fact to marry the two facts does sound good.

gateleys
Post Reply