I have a requirements for reports on business units from the enterprise level. I am trying to generate reports on data from each business unit from the enterprise level. Is there a way to write SQL queries at the Enterprise account level to retrieve information from business units? I have searched the documentation and found nothing on this. Is this possible or is there a work around?
You can't query a Data Extension that resides in a child BU from the Enterprise BU. FYI, child BU DE's are stored within the C-level schema, but there's no way of querying this schema.
The only solution would be to make the DE shared, then append the prefix ENT. before the data extension name to query it from your Enterprise BU.
Within the Parent Business Unit you can run a query to access most data view information relating to business units, but not subscriber data that only lives within that business unit.
What you can query:
- Shared Data Extensions
What you can't query:
- Lists in child business units
- Data Extensions in child business units
- Job data for sends not made in Parent
- Subscriber status on the business-unit specific All Subscribers list
On almost all Data Views (https://help.marketingcloud.com/en/documentation/exacttarget/interactions/activities/query_activity/data_view_open/) you will see a column labeled "OYBAccountID". This stands for 'On-Your-Behalf' accounts, which can be considered legacy speak for child business units. So you could filter or label specific business units using that field, or just pull in data for all business units.