5

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.

  • Instead using write to the global (shared) dataextension, can't you just use one sql query that reads from multiple sources at once, within one query? – Johannes Schapdick Jan 2 '19 at 13:51
  • The only source is _PushAddress but I have to run the query in the Context of each business unit to have access to its data. – zeljazouli Jan 2 '19 at 14:00
  • Yeah you are right, just thought that this could be possible. Unfortunately it is not like shown here – Johannes Schapdick Jan 2 '19 at 14:03
  • You're right. _PushAddress and _Job have the same behavior. – zeljazouli Jan 2 '19 at 14:08
  • Why not run one query, then the others right after as updates to the same data extension in an automation? Pretty sure there is no way to write simultaneously to one DE. – garek007 Jan 2 '19 at 14:24
4

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.

  • Thank you for providing this complete answer. This is exactly how I imagined the solution in the first place but I was not keen on adding a do while loop that will check for the status of each query. I will try this code and let you know. Thanks again. – zeljazouli Jan 11 '19 at 15:18
  • As a note, I found that sometimes the Perform requires you to use the ObjectID of the Query or it can throw an error. It might be worth doing a retrieve script to get all the MID and ObjectID and place in either a JSON or DE for easy access. – Gortonington Jan 11 '19 at 16:47
  • 1
    I tried many times to get the status of the Query from the QueryDefinition object but I always get 'Active' in the response. I managed to get it using AsyncActivityStatus object by passing 'Task ID' but It only works in the Parent BU. I get an empty Results array for chid BU queries. Here is a pastebin for the script : pastebin.com/Tep7naes. Response for AsyncActivityStatus (Child BU) : pastebin.com/0qKKbzHB . Response for AsyncActivityStatus (Parent BU) pastebin.com/rkk3GvwK – zeljazouli Jan 15 '19 at 10:33
  • Good catch @zeljazouli and good solution for the status issue. It definitely sounds like the issue is that it is not finding the Async activity for the Child BU. The impersonation SHOULD run it at the local level, but try removing the impersonation for the Async retrieve. perhaps it is running a child activity from the parent BU (somehow) so the activity is on parent BU, even though the target is a child. (It is MC so stranger things have happened.) Try using api.resetClientIds(); instead of the impersonation function or switch MID value to parent for that WSProxy call. – Gortonington Jan 15 '19 at 13:52
  • 1
    Thank you. I will ask SFMC support and hopefully find a solution. I will let you know. – zeljazouli Jan 15 '19 at 14:51
0

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.

  • This seems a bit unsustainable. Especially as Business units get added or removed. Plus you run into a higher risk of error due to a significant number of moving parts, essentially creating a house of cards. I would instead recommend using an API call inside of a FOR loop to run your query in each BU aimed at a shared DE. This would keep it sequential without locking and is easy to update and maintain. – Gortonington Jan 10 '19 at 19:20
  • Thanks @Gortonington for your comment. That is exactly what I wanted to do in the first place but automations run in Async mode which means that I will have to check the status of each automation before launching the next one in the for loop. I think It will be time and ressource consuming. What do you think ? – zeljazouli Jan 11 '19 at 8:51
  • You would have all this automated inside of a single Script Activity in the parent account. You could even automate the Script to include the Query creation/edit itself inside the Script Activity so you do not even need to build it out prior to utilizing the script. You would utilize WSProxy and its Impersonation ability to target each of your BU inside the SSJS FOR LOOP. (WSProxy Impersonation: developer.salesforce.com/docs/…) – Gortonington Jan 11 '19 at 14:07
  • I get you. How am I going to schedule my automations if they run in Async mode ? I have to wait until automation in BU 1 is over to run automation 2 in BU 2. – zeljazouli Jan 11 '19 at 14:11
  • You aren't using but 1 automation. The script in this automation will iterate and run through each query using the for loop. No other automations are needed. – Gortonington Jan 11 '19 at 14:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.