Hi All,
I have a set of databases as source systems which all have the same schemas. We are working to pull all that data into a single data warehouse. One issue I have is in the application design some people made lookup tables in some cases updatable by the end user. So we need to capture source data from all and make sure each record is captured as in the case of updatable the key then can be different for the same values eg drugs of concern and Heroin is a 6 in one system and 23 as the id in another one of them. I’m using a key of the sourcesystem and ID as a hash to try and avoid collisions in loads.Are their any issues I might be missing which are going to bite me when trying to use these tables? For example to show heroin as the correct textual value for data sets regardless of the data and source system. I know this means source system is required for nearly every join and that’s ok. Anything else?
Hey mate,
Reference data is probably not the best model to model as a data vault; you end up attempting to manage these things integrated into ref-hubs and therefore may become something unmanageable – should this be managed by something like an RDM or of a similar construct?
Ref data is only meant to enrich DV in the info-mart layer
Thanks Patrick, yeah we have ref tables not hubs and satellites to manage it and I feel my approach is about as good as it might get. Yes, and that is the only reason I get to handle these 400 odd tables is it’s for the outputs of the DV, not the DV itself.
1 Like