Multiple Life cycle entities in One dimension table...

When we have more than one life cycle attributes in a dimension table, how do we track Type-2 on that table ?

Say, we have an agent dimension table and we have agent start and end dates, agent agreement start and end dates.
Now, agreement start and end date can change, agent's start and end date can change and there may be other fields in the table which can cause a new version to be created.

when we come to this table to get a key during the fact population what date range should we use ?
if we use any one life cycle dates then there may be multiple records for it because of change in other...
We can't use the Type-2 Row Effective dates as we would also have future dated records in this table. How do we solve this issue ?

Appreciate your time...

Thanks

In a basic type 2, the row that the fact references is the current dimension row at the time the fact was loaded. It doesn't matter what dates are on the row.

From that there have been variations to the basic load strategy, including choosing dimension rows based on some date. Normally, this is used when you need to deal with late arriving facts.

Thanks for the reply but the question here is how do you choose the right dimension record during the fact load ?
I don't think it's late arriving fact situation rahter soon arriving dimension...As I said before, I can't use row effective dates as there will be future dated record. for ex., there will be a record whose business effectiveness will start in 2015 but we received the record now itself and that will be shown as current record in the dimension table which is wrong....

Let me know if I did not explain the problem properly...

In a normal situation, reference the current version of that dimension row at the time you are loading the row in the fact table. Usually you update the dimension table first.

In reporting you can use whichever version of the dimension you need by performing a self join on the dimension using the natural key and a predicate on whichever date range you wish.

I think none of your business dates, agent and agreement start/end dates should influence the SCD dates that would be set based on new dimension NK and attribute change. Treat the business dates like other attributes based on appropriate SCD types. If you allow the agent to start or end multiple times, then these business dates are SCD 2, otherwise SCD 1.

When you load your fact, if the dimension NK appears in the fact, you should load the dimension record based on SCD dates, or current indicator, unless it's late arriving dimension, in which case, you create a new dimension record with the NK as a place holder. The business dates are only used in your queries and reports, not in ETL.

kaps wrote:Thanks for the reply but the question here is how do you choose the right dimension record during the fact load ?
I don't think it's late arriving fact situation rahter soon arriving dimension...As I said before, I can't use row effective dates as there will be future dated record. for ex., there will be a record whose business effectiveness will start in 2015 but we received the record now itself and that will be shown as current record in the dimension table which is wrong....

Let me know if I did not explain the problem properly...

Why would a row with a future effective date be the current record? Most people do not load future records in their dimensions. When they say current, it means the active record, which normally is the most recently inserted. If you load future records, then current means the active record, not the most recent. And that should be defined by row effective and end dates.

I think I did not explain the scenario properly. Let me give the example...

Code:


Surr Key   Natural Key   Col1   Status   Bus Start Date   Bus End Dt   Row Eff Dt    Row End Dt
1   100               xyz   Status1   1/10/2011   1/1/2012               1/10/11 12:00 AM    1/14/11 12:00 AM
2   100               xyz1   Status1   1/10/2011   2/11/2012   1/15/11 12:00 AM    2/11/11 12:00 AM
3   100               xyz1   Status2   2/12/2011   1/1/2015     2/12/11 9:00 AM    2/12/11 10:00 AM
4   100               xyz1   Status3   2/12/2011   1/1/2015               2/12/11 10:01 AM    2/15/11 10:00 AM
5   100               xyz2   Status3   2/12/2011   1/1/2015               2/15/11 10:01 AM    12/31/99 12:00 AM


I want to find a dim for the fact record which happened on 2/13/2012. If I use Row Eff dates then I would get 4 which is wrong as it's not effective now. I should have gotten 3. If I use Bus dates then I would get both 4 & 5 and then we have to use more logic to narrow it down and it can get more complicated when one more set of start and end dates.

One solution is to omit future records in the dimension then we need to pick it up when it's ready to be picked up…so was wondering about what others did in this scenario.

Thanks

If you use row effective dates, then for a given date (e.g. 2/13/2012), each natural key should have 1 effective row.

In your example, it looks like there are issues with the row effective dates - there are gaps with no effective row due to the time aspect. Also, if your row effective dates are datetime fields, then you'll need to establish an ETL rule to determine what time you will use when performing your lookup.

For example, rather than looking for 2/13/2012, you need to look at 2/13/2012 12:00am.

You assign row #5, the current one. Add a current flag to your table to make it easier to find.

Why are three different statuses in effect at the same time?

Ngaalemmo

It's my bad. I messed up the examples...Here you go...

Code:


Surr Key  Natural Key      Col1    Status  Bus Start Date  Bus End Dt  Row Eff Dt    Row End Dt
1        100      xyz    Status1  1/10/2011     1/14/2011      1/10/11      1/14/11
2        100      xyz1  Status1  1/15/2011     2/11/2011      1/15/11        2/11/11
3        100      xyz1  Status2  2/12/2011     1/1/2015        2/12/11        2/12/11
4        100      xyz1  Status3  1/2/2015     12/31/9999      2/13/11        2/15/11
5        100      xyz2  Status3  1/2/2015     12/31/9999      2/15/11        12/31/99


On 2/15/2012, I should get Row#3 but in this case(If row eff dates used) then I would get Row#5 which is not correct.