How to track changes in Adobe Campaign's database

[#1]

How does one track changes in Adobe Campaign's database?

We upload batch files to Adobe Campaign's database each day. We would like to validate changes made to the database. How can we create a workflow to validate that changes or updates that have been made to the various database tables we update each day?

I am not talking about schemas or time stamping, but rather, actual fields (entities within the schema, or fields within the columns and rows in the tables).

For example, if a client's address has been changed, how can we automatically detect that that address field had been updated within our Adobe Campaign's database?

To give you another example, SQL Server 2017, for instance, provides two features that track changes to data in a database: change data capture and change tracking. These features enable applications to determine the DML changes (insert, update, and delete operations) that were made to user tables in a database. Change data capture and change tracking can be enabled on the same database:

Is there a similar function in Adobe Campaign? If not, how we we develop an automated data change track feature?

[#2 · Accepted solution]

This sort of activity is normally tracked on a database level. The data warehouse (where batch files are generated) should keep historical changes for each customer, you can setup a 'view' on the data warehouse and link neolane to it, then you can query latest updated record against historical ones.

[#3]

First you need to create a linked server on a database level. It requires an sql account in dwh with read/write privileges on the object which you want to query. Follow some guides online.

After your linked server is created then you need to test it by querying the dwh from Neolane's sql instance, after you are able to do this first step, then you need to create a schema in Neolane and configure it to access data from an external source within the element's xml structure- i.e.

  <element label="viewtest" name="viewtest" sqltable="[xx-DWH].[Neolane].[dbo].yourtableorview">
    <key internal="true" name="MEMBER_ID">
      <keyfield xpath="@MEMBER_ID"/>
    </key>
    <attribute advanced="false" label="MEMBER_ID" length="50" name="MEMBER_ID" sqlname="MEMBER_ID"
               type="string"/>
    <attribute advanced="false" label="EMAIL_ADDRESS" length="300" name="EMAIL_ADDRESS"
               sqlname="EMAIL_ADDRESS" type="string"/>
    <attribute advanced="false" label="MOBILE_NUMBER" length="15" name="MOBILE_NUMBER"
               sqlname="MOBILE_NUMBER" type="string"/>
  </element>

Where [xx-DWH] is the name of the linked server, the database name and table or view name.

This method allows you to query the dwh data from within a workflow 'query' activity as if it was stored within Neolane.

[#4]

Hi Jae,

If the instance is hosted by you, enable triggers at the table level (Which will result in decreased performance) and you can have an audit table where the changes can be preserved.

Apart from that, there is no inbuilt feature to store this audit of data being changed.

I'll request others to correct me if I'm wrong, otherwise, you can reach out to Professional Services under a paid engagement to see if such a solution can be implemented.

Regards,

vipul

[#5]

Thank you so much! I will explore this option along with others. Much appreciated!

Jae

[#6]

How does one setup a view on the data warehouse and link neolane to it? Are we building an application or a script and linking it to neolane via an API?