0

Does anyone know if it is possible to determine if a data extension has been shared via the API. I don't see any related properties for the Data Extension in the documentation and I don't see any related information during a describe call or otherwise. My specific use case is to retrieve the data extensions for an Enterprise 2.0 account and be able to determine which data extensions have been shared.

1

It is possible.

Shared Data Extensions are physically located in Parent BU (ENT or Enterprise context).

They differ from "normal" DEs in the Parent BU in that they are accessible from inside other BU contexts through SQL and AMPscript / SSJS.

So, one vector to approach this is to try an attempt an SQL SELECT operation from child BU:

Set up an installed package in Parent BU, give access to some child BU, enable interactions with Data Extensions and Automations in the package's scope. now, using the credentials from this package, get an OAuth token for (= authenticate in the context of) the Child BU.

See documentation here, setting the account_id when getting the token will determine your context: https://developer.salesforce.com/docs/atlas.en-us.mc-app-development.meta/mc-app-development/access-token-s2s.htm

Host: https://YOUR_SUBDOMAIN.auth.marketingcloudapis.com
POST /v2/token
Content-Type: application/json

{
"grant_type": "client_credentials",
"client_id": "xxx",
"client_secret": "yyy",
"scope": "email_read email_write email_send",
"account_id": "YOUR CHILD BU MID"
}

Set up a dummy data extension in your Child BU with one field. In my usecase, I used the field "targetCustomObjectKey" and the name & Customerkey testDE123.

Now, using the resulting token for your API call, try to create an SQL query activity through the API (fine for doing it once - for repetition, instead update the same query, otherwise, this creates a lot of query activities). see also here: SOAP Envelop for Marketing Cloud - Update Query Activity or here for references: https://gortonington.com/how-to-interact-with-query-activities-via-rest-api/

The SQL query selects data from the Data Extension of your interest, using an "ENT." (=Enterprise) prefix before the DE name.

We can make it query for the hidden field _customObjectkey, which every DE has. Note that for a valid query, we need to rename that field using AS, hence my "targetCustomObjectKey" field from above.

Here is an example "create SQL query" call. Once you have one query, again: use update instead, or this fills up your child BU with query activities.

Change the QueryText "FROM" part to include your DE's name, and if necessary, repeat for your DEs.

<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <SOAP-ENV:Header>
        <fueloauth>{{access_token}}</fueloauth>
    </SOAP-ENV:Header>
    <SOAP-ENV:Body>
        <CreateRequest xmlns="http://exacttarget.com/wsdl/partnerAPI">
            <Options></Options>
            <Objects xsi:type="QueryDefinition">
                <PartnerKey xsi:nil="true"></PartnerKey>
                <ObjectID xsi:nil="true"></ObjectID>
                <!--<CategoryID>68371</CategoryID>-->
                <Name>testquery</Name>
                <Description>testquery</Description>
                <QueryText>SELECT _customObjectkey AS targetCustomObjectkey FROM ENT.[YOUR DATA EXTENSION NAME]</QueryText>
                <TargetType>DE</TargetType>
                <DataExtensionTarget>
                    <PartnerKey xsi:nil="true"></PartnerKey>
                    <ObjectID xsi:nil="true"></ObjectID>
                    <CustomerKey>testDE123</CustomerKey>
                    <Name>testDE123</Name>
                </DataExtensionTarget>
                <TargetUpdateType>Overwrite</TargetUpdateType>
            </Objects>
        </CreateRequest>
    </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

Two things can happen:

Option a) You get an error due to DE not found. >> This DE isn't shared. It's either in Parent and not shared, or it's in Child.

<StatusMessage>Error saving the Query field.ENT.testjl is not a known data extension or system data view. You can only query existing data extensions or system data views.&lt;br /&gt; </StatusMessage>

Option b) Success >> If you can find an "ENT." data extension from Child context via SQL, then this DE is necessarily Shared from Parent.

In my case, the query creation succeeds:

...
StatusCode>OK</StatusCode>
   <StatusMessage>QueryDefinition created</StatusMessage>
...

Last time I checked... - You will notice that this indeed works from ANY child BU with ANY shared Data Extension regardless of sharing settings on the UI. Yes, that surprised me too.

2
  • I just tried this and in all the child accounts adding ENT., regardless if shared or not, would return Data extension does not exist on the DE. I could retrieve it in the parent BU without the ENT, but not in any child accounts. Can you share a sample of your SOAP API call using ENT? Jul 14 at 17:14
  • 1
    True that. Editing my answer,It's not possible through ANY retrieve through the API, it's possible by forcing an SQL or AMPScript interaction. Jul 15 at 7:06

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.