How to populate CI_RESPONSE_HISTORY with ETL?

[Datino]

Greetings,

I want to populate the CI_RESPONSE_HISTORY table with data from CI_CONTACT_HISTORY plus some other external data. Are there any considerations on how to do it without breaking the data model and/or causing CI to malfunction?

[Dmitry_Alergant]

Hi,

It's not clear from your post what components of CI do you use - is it SAS MA, SAS RTDM, or both?

Generally, this table is supposed to be populated by external ETL. This is very normal. Especially for SAS MA. 

In SAS RTDM there is a built-in capability to store a response from the solution itself, but still it's OK to also populate or update responses in batch using ETL.

I don't think there are any particular considerations or gotchas, you shouldn't break the data model. Just make sure you understand the data model and what to put there. If you do not, ask questions...       

Some things may depend on how are you using this table in CI. For example if you have SAS RTDM and you implement real-time lookups to the response history table, you probably don't want to implement ETL that'd truncate the table before re-loading all the data into it. But that is kind of obvious:)

[Datino]

Thank you.

I should have been clearer about my case. It's for MA only, and the primary purpose is to generate reports via the %CI2LASR macro.

This snippet from MA Admin Guide about history tables has me a bit perplexed though:

"They must be included in a SAS Information Map in order to make the contact history and response history data available for use."

Does this mean if this step is not completed, the information in the table won't be visible to MA? And how does one go about doing it?

[Dmitry_Alergant · Accepted solution]

More or less so.  It needs to be done, yes.

In SAS MA, you can have multiple subject levels, each with a separate Contact and Response History tables.   Every such table will need have a different name.   Because of that, the default CDM table names as created by a script (CI_CONTACT_HISTORY, CI_RESPONSE_HISTORY) are not more than an example.  Even though often people keep the default name when there is only one subject involved, in theory your contact and response tables could be named differently.

Because of that, a properly configuring the information map using "Contact_History" and "Responses" custom attributes on one column is a way to tell SAS MA where to find the right Contact and Response history table. See MA Admin Guide, page 90 (for version 6.5).

Good luck!

[Datino]

Thank you very much for your help Dmitry.