Allow simultaneous writes to the same Data Extension

Question

I have a use case where I need to bring _PushAddress data from 60 Business Units back to the Global Business Unit.

For information _PushAddress has a restricted view of data available in a single Business Unit. Therefore It should be queried at each Business Unit in order to have the full picture of MobilePush data.

For that, I am running multiple queries that upsert _PushAddress data to a Shared Data Extension.

The queries fall in error (probably a deadlock) when I run them at the same time whereas I have no issue when I run them with a 5 minute interval.

I was wondering how I can still run them at the same time and have no issue.

Answer 1

Here are a few snippets on WSProxy that can be used inside a SSJS activity that should help get you towards your solution without requiring you to build multiple automations in all your BUs and remove the 'house of cards' effect this would have.

First you need to create the data extension inside the parent account that you want all this data stored in. You then need to share it across each of your BUs.

Then you need to create a rowset or JSON to hold all your BUs. If they are stored inside of a DE, you can do a SSJS Retrieve on the DE or a LookupRows. Or, as I will use in my example, build it as a JSON inside your script:

var midJson = '[{MID:12345},{MID:23456},{MID:43234},....]

You then would need to create a SSJS FOR LOOP based off of the midJson rowset/JSON you just gathered.

for (var i=0; i < midJson.length; i++) { }

Inside of this, you would then need to gather the MID for each row:

var mid = midJson[i].MID;

If you already have the query built, (I would recommend an easy naming convention for customerkey and name. Like perhaps {{MID}}_PushAddressQuery)

so you can then set your query CustomerKey in the FOR loop:

var queryCustKey = MID + '_PushAddressQuery'

you then would run the query using WSProxy(): (I added in a try/catch for error handling)

    try {

        var api = new Script.Util.WSProxy();

        api.setClientId({ "ID": MID }); //Impersonates the BU
        var action = "Start";
        var props = {
                CustomerKey: queryCustKey
        };
        var opts = {};

        var data = api.performItem("QueryDefinition", props, action, opts);
   } catch(e) {
        Platform.Response.Write('error: ' + Platform.Function.Stringify(e));
   }

as a safety measure you can then use another WSProxy API call to check the status of the query and not let it move on until the status is back to Active again.

    do {

         var prox = new Script.Util.WSProxy();

         prox.setClientId({ "ID": mid }); //Impersonates the BU

         var cols = ["Status"];
         var filter = {
             Property: "TaskID",
             SimpleOperator: "equals",
             Value: taskid
         };

         var desc = prox.retrieve("AsyncActivityStatus", cols, filter);

         var queryStatus = desc.Results[0].Status;

     } while(queryStatus != 'Completed')

you have then complete the current iterate of this for loop and move to the next.

You also are able to create/edit the query inside of WSProxy() if you wanted to. See below for example on creating a new QueryDefinition:

var proxy = new Script.Util.WSProxy();

/* proxy.setClientId({ "ID": MID }); */ //Impersonates the BU
api.resetClientIds();  //Resets the MID to the parent as AsyncActivity is on parent BU, not on Child BU

var queryCustKey = MID + '_PushAddressQuery';
var name = MID + '_PushAddressQuery';

var queryObj = {
    Name: name,
    CustomerKey: queryCustKey,
    Description: "PushAddressQuery",
    QueryText: "SELECT * FROM [...]",
    TargetType: "DE",
    DataExtensionTarget: [{
        CustomerKey: "myTargetDEKey",
        Name: "myTargetDEName"
    }],
    TargetUpdateType: "Update"
}

var result = proxy.createItem("QueryDefinition", queryObj);

---

EDIT: Thanks to @zeljazouli for the solution on query status using AsyncActivityStatus object to retrieve the Query's run status as the 'Status' inside the QueryDefinition object is not related to the running of the query.

Answer 2

Here is the solution I have implemented :

1 - Create a staging DE for each BU in shared folder

2 - Create an automation/query in each child BU that upsert PushAddress data to the related DE. These automations are scheduled to run at 3 a.m

3 - Create 60 queries in Parent BU that upserts data from each staging DE to the final DE

4 - Create an automation in Parent BU with 60 steps to run all the queries sequentially. This automation is scheduled to run at 3:30 a.m

This solution runs all the queries in Child Business Unit at the same time and prevents deadlocks from happening by combining all the records in the final DE in a sequential mode.

You can post a comment if you have any improvement.