DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
gateleys



Group memberships:
Premium Members

Joined: 08 Aug 2005
Posts: 992
Location: USA
Points: 7244

Post Posted: Tue Sep 19, 2006 11:53 am Reply with quote    Back to top    

DataStage® Release: 7x
Job Type: Server
OS: Windows
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

Premium Poster


since February 2006

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 29 May 2003
Posts: 5227
Location: Dallas, TX
Points: 35013

Post Posted: Tue Sep 19, 2006 12:06 pm Reply with quote    Back to top    

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_i ...

_________________
Mamu Kim
Rate this response:  
Not yet rated
gateleys



Group memberships:
Premium Members

Joined: 08 Aug 2005
Posts: 992
Location: USA
Points: 7244

Post Posted: Tue Sep 19, 2006 12:33 pm Reply with quote    Back to top    

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
Rate this response:  
Not yet rated
kduke

Premium Poster


since February 2006

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 29 May 2003
Posts: 5227
Location: Dallas, TX
Points: 35013

Post Posted: Tue Sep 19, 2006 1:30 pm Reply with quote    Back to top    

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
Rate this response:  
Not yet rated
gateleys



Group memberships:
Premium Members

Joined: 08 Aug 2005
Posts: 992
Location: USA
Points: 7244

Post Posted: Tue Sep 19, 2006 6:55 pm Reply with quote    Back to top    

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
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours