Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

SOLVED

How to track changes in Adobe Campaign's database

Jae_H_Lee
Level 2
Level 2

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:

cdcart1.gif

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

1 Accepted Solution
David__Garcia
Correct answer by
Level 9
Level 9

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.

View solution in original post

5 Replies
Vapsy
Employee
Employee

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

David__Garcia
Correct answer by
Level 9
Level 9

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.

View solution in original post

Jae_H_Lee
Level 2
Level 2

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?

David__Garcia
Level 9
Level 9

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.

Jae_H_Lee
Level 2
Level 2

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

Jae