Effectivity Satellites as Views

Data Vault 2.0
1

Hi,

I wonder if it’s okay (according to the DV2-Standard) to build Effectivity Satellites of Links as Views, when no business/effectivity dates are provided by the source system?

In this case, we’ll have to use the Vault’s load_datetime to build the timeline anyway. This means, the Effectivy Satellite would just carry “dead” (duplicated) information.

Hence my idea was to simply create a view like so:

create view silver_rv.s_customer_campaign as
select
	t.hk_customer_campaign,
	t.hk_customer,
	t.hk_campaign,
	t.load_ts,
	coalesce(lead(t.load_ts) over(partition by t.hk_customer order by t.load_ts) - interval '1 ms', to_timestamp('2099-12-31 23:59:59.999', 'yyyy-mm-dd hh24:mi:ss.fff')) as loadend_ts,
	t.record_source
from silver_rv.l_customer_campaign t
--where t.hk_customer = '\x0030b4079ebaa934aef727744a099578'

So far, this seems to work, but is it the preferred way?

I’m currently trying-out AutomateDV on a smal sample database and things work quite well.

Thanks,

Roger

2

Hi there Roger

Effectivity satellites without effectivity? I’m not sure I follow what you’re aiming for though I’d be willing to guess that you’re gonna need to materialize your satellites if you have any hope of storing the history of your warehouse.

I suspect more info is needed here to understand your use case.

No idea if that helps or not but best of luck,

Frankie

3

Hi Roger

For simple cases it may seem to work, but it won’t work in general. Simplest example is when relationship disappears and apoears again in source data. Link registers only 1st occurance, thus you need dedicated satellite to capture re-activation of a relationships.

Regards

Marcin

4

Thanks everyone for your answers. I think it was a mis-conception by me. Meanwhile I switched to AutomateDV’s eff_sat template, which works for most of my experiments.

I’m trying to implements links with effectivity satellits for all the different situations:

1:0..1

1..*

*:*

If a link is “ended” the driven/secondary FK will be null, in the case of m:m there’s a flag/datetime, which works. I’ll open another thread for a AutomateDV-specific question.