Allow simultaneous writes to the same Data Extension

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.