Cannot retrieve fields from a Shared Data Extension using the SOAP API

Question

I have a Data Extension which exists in the Shared Data Extensions directory and is available to all Business Units. I want to use the SOAP API to retrieve fields from this Data Extension.

I've tried the following SOAP envelopes in my request:

1. Standard Envelope

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <soapenv:Header>
      <fueloauth xmlns="http://exacttarget.com">insertAccessToken</fueloauth>
  </soapenv:Header>
   <soapenv:Body>
      <RetrieveRequestMsg xmlns="http://exacttarget.com/wsdl/partnerAPI">
         <RetrieveRequest>
            <ObjectType>DataExtensionObject[CFC5D020-6256-4ADC-84A6-72BCE042C351]</ObjectType>
            <Properties>FUNDID</Properties>
         </RetrieveRequest>
      </RetrieveRequestMsg>
   </soapenv:Body>
</soapenv:Envelope>

2. Query All Accounts Option

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <soapenv:Header>
      <fueloauth xmlns="http://exacttarget.com">insertAccessToken</fueloauth>
  </soapenv:Header>
   <soapenv:Body>
      <RetrieveRequestMsg xmlns="http://exacttarget.com/wsdl/partnerAPI">
         <RetrieveRequest>
            <ObjectType>DataExtensionObject[CFC5D020-6256-4ADC-84A6-72BCE042C351]</ObjectType>
            <QueryAllAccounts>true</QueryAllAccounts>
            <Properties>FUNDID</Properties>
         </RetrieveRequest>
      </RetrieveRequestMsg>
   </soapenv:Body>
</soapenv:Envelope>

3. Query individual MID Option

Note: I've tried using the MID of the parent and child Business Unit

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <soapenv:Header>
      <fueloauth xmlns="http://exacttarget.com">insertAccessToken</fueloauth>
  </soapenv:Header>
   <soapenv:Body>
      <RetrieveRequestMsg xmlns="http://exacttarget.com/wsdl/partnerAPI">
         <RetrieveRequest>
            <ObjectType>DataExtensionObject[CFC5D020-6256-4ADC-84A6-72BCE042C351]</ObjectType>
            <Client><ID>7230771</ID></Client>
            <Properties>FUNDID</Properties>
         </RetrieveRequest>
      </RetrieveRequestMsg>
   </soapenv:Body>
</soapenv:Envelope>

Response

I always get the following response:

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope>
    <soap:Header>
        ...
    </soap:Header>
    <soap:Body>
        <RetrieveResponseMsg
            xmlns="http://exacttarget.com/wsdl/partnerAPI">
            <OverallStatus>Error: Invalid object name 'C7231594.Fund Details'.</OverallStatus>
            <RequestID>c735f5c2-ae59-4c67-8fb9-f4b9aa518b2e</RequestID>
        </RetrieveResponseMsg>
    </soap:Body>
</soap:Envelope>

Note that the DE name is 'Fund Details'. I get the same error with different Shared Data Extensions, however if the DE is in the Data Extensions directory (or in subfolder) then my request works fine.

Any ideas how to retrieve fields from a Shared Data Extension using the SOAP API?

Answer 1

According to this discussion with some SFMC folks, all you need to do is set the ClientID to the parent MID to access the shared Data Extensions.

My first thought was to use the ent. prefix, but that's only for DE Names, not external keys.

Answer 2

This is a bizarre one. It appears that DataExtensionObject SOAP object cannot retrieve rows from a shared Data Extension if the DE is created in a Child Business Unit (BU) and then moved to the Shared Data Extensions directory.

Here's a summary of the behavior:

• Retrieve Rows from DE created in Parent BU (using OAuth token from Parent BU): works
• Retrieve Rows from DE created in Parent BU then moved to Shared DE folder (using OAuth token from Parent BU): works
• Retrieve Rows from DE created in Child BU (using OAuth token from Child BU): works
• Retrieve Rows from DE created in Child BU then moved to Shared DE folder (using OAuth token from Child BU): Fails with Error: Invalid object name 'C7231594.{{DE name}}'.

However oddly enough, this does work with the DataExtension object. For example, if I make the following SOAP request, where 25CE803A-F4DF-40DA-BF86-2E393EA577E5 is the External Key of a Data Extension in a Shared Data Extension folder (created in a child Business Unit), then this request works (it returns the requested properties of the DE):

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <soapenv:Header>
      <fueloauth xmlns="http://exacttarget.com">insertAccessToken</fueloauth>
  </soapenv:Header>
   <soapenv:Body>
      <RetrieveRequestMsg xmlns="http://exacttarget.com/wsdl/partnerAPI">
         <RetrieveRequest>
            <ObjectType>DataExtension</ObjectType>
            <QueryAllAccounts>true</QueryAllAccounts>
            <Properties>Name</Properties>
            <Filter xsi:type="SimpleFilterPart">
                <Property>CustomerKey</Property>
                <SimpleOperator>equals</SimpleOperator>
                <Value>25CE803A-F4DF-40DA-BF86-2E393EA577E5</Value>
            </Filter>            
         </RetrieveRequest>
      </RetrieveRequestMsg>
   </soapenv:Body>
</soapenv:Envelope>

However, theQueryAllAccounts option only appears to work for the DataExtension SOAP object, not DataExtensionObject (which is used to retrieve rows from a Data Extension).

I've exhausted every permutation and this simply does not work; the moment you move a DE to the Shared Data Extension directory (and the DE is created in a child BU), you can't retrieve rows from it, no matter which additional options you include in your request.

It's also unfortunate that the REST API DataEvents methods don't support retrieving Data Extension rows at this time.

Workarounds

1. Legacy Authentication

Using the legacy username/password authentication in your request will enable this request to be used across BU's (assuming the user has permission to both BU's). This isn't ideal.

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <soapenv:Header>
      <wsse:Security soapenv:mustUnderstand="1" xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd">
         <wsse:UsernameToken wsu:Id="UsernameToken-32259181" xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
            <wsse:Username>{{insertUsername}}</wsse:Username>
            <wsse:Password Type="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText">{{insertPassword}}</wsse:Password>
         </wsse:UsernameToken>
      </wsse:Security>
  </soapenv:Header>
   <soapenv:Body>
      <RetrieveRequestMsg xmlns="http://exacttarget.com/wsdl/partnerAPI">
         <RetrieveRequest>
            <ObjectType>DataExtensionObject[B9057818-1EA5-44D7-B6EB-891C53EF832F]</ObjectType>
            <Properties>FUNDID</Properties>
         </RetrieveRequest>
      </RetrieveRequestMsg>
   </soapenv:Body>
</soapenv:Envelope>

2. Use separate OAuth Credentials

The other option would be to create two apps in App Center; one using the permissions of the Parent BU and the other of the child BU.

You would use the accessToken retrieved using the Parent BU OAuth credentials for any SOAP requests that require retrieving fields from a Data Extension that exist in the Shared Data Extensions directory.