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
Modelling minute-level granularity
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.
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
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