Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Track hierarchical slowly changing data which allows relational multiple future dates and retro terms

Go down

 Track hierarchical slowly changing data which allows relational multiple future dates and retro terms Empty Track hierarchical slowly changing data which allows relational multiple future dates and retro terms

Post  omm Wed Nov 16, 2011 4:26 pm

Q1)Given these requirements, what is the best possible design to model this?
REQUIREMENT: Track hierarchical slowly changing data which allows relational multiple future dates and retro terms.

Let me explain the 4 terms I have mentioned in detail for my scenario
1) hierarchical 2) Relational future dates 3) Retro term 4) Track

1) hierarchical
A Client(top) can have many billing groups
Each billing group can have many groups.
Finally, each group can have many members(bottom).

2) Relational future dates
Apart from each of these entities having their own effective and end dates, their relation with other entity also have dates.
For example:
A Client and billing group have their own effective and end dates. Apart from that, a billing group in relation with the Client has a separate effective and end date which I am referring as relational dates. There could be more than one relational dates which we need to track.
Let us say today is 11/16/2011. The relation dates for a billing group associated with a client might look like the following.
Billing_group_x_client_effective_date | Billing_group_x_end date | status_code
1/1/2011 - 12/31/2011 A (Active)
1/1/2012 - 12/31/2012 F (Future)
1/1/2013 - 12/31/2013 F (Future)

3) Retro term
We can back date an end date for a existing relation.
For example, we can have a record like
Billing_group_x_client_effective_date | Billing_group_x_client_end date | status_code
1/1/2011 | 12/31/2011 |A (Active)

Now, as of today (11/16/2011), I can change the Billing_group_x_client_end date to 10/31/2011 which used to be12/31/2011 . So, it looks like

Billing_group_x_client_effective_date | Billing_group_x_client_end date | status_code
1/1/2011 | 10/31/2011 |A (Active)

4) Track
At any given point in time, we should be able to say how Client, billing group , group and member look like.

Purpose:
This is mainly used for billing purpose where, we should be able to see how things look like in future and bill them. Later, we need to adjust the billing (by going back)to check if any changes happened mean while.

Each of the Entities( Client, billing group , group and member) have the following attributes except (Client will not have relation dates as there is no higher hierarchy to it)
(X= client/ billing group/group/member)
X_name
X_id (key)
X_effective_date (own entity date)
X_end_date(own entity date)
1_X_2_effective_date (relation date) (eg: Billing_group_x_client_effective_date)
1_X_2_end_date(relation date)
X_city
X_addr_line_one
X_addr_line_two
X_state
x_zipcode

omm

Posts : 11
Join date : 2011-07-29

Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum