Pull in cross-client data from MS 365 DFO via O-Data feed

(1) Share

Report

Posted on by RR-22070827-0

Hello everyone,

I have a question about pulling in cross-client data. I currently pull the data from MS DFO 365 via O-Data Feed for working in Excel or Power BI. For cross-client entities, I use the following suffix for each affected entity:

?cross-company=true

As I sometimes need several entities for a report and use the suffix for each entity individually, the performance is of course horrible. Can I choose a better variant for the data export so that I don't have to create individual queries for cross-client entities?

Many thanks for your feedback!

Ramon

All responses (5)

Answers (0)

Waed Ayyad 8,328 Super User 2025 Season 1 on at

Like (0)

Report

Pull in cross-client data from MS 365 DFO via O-Data feed

Hi,

Is your issue resolved? If yes, mark the answers that helped you as verified.

Thanks
Waed Ayyad
Anton Venter 19,610 Super User 2025 Season 1 on at

Like (0)

Report

Pull in cross-client data from MS 365 DFO via O-Data feed

I fully agree with Martin and also recommend using the datalake / dataverse option for BI.
Martin Dráb 233,025 Most Valuable Professional on at

Like (1)

Report

Pull in cross-client data from MS 365 DFO via O-Data feed

A better approach may be using a data warehouse instead of fetching data via OData services from the transactional database. Have you considered using Azure Data Lake as the data source of your Power BI and Excel reports?
Anthony Blake 2,359 Super User 2025 Season 1 on at

Like (0)

Report

Pull in cross-client data from MS 365 DFO via O-Data feed

Hi,

?cross-company=true is the correct way to bring in records from all companies.

You can also use $filter to be more selective at source and reduce the number of records coming over to Excel/Power BI.

For example, if you have 100 companies and only need data from 2 or them, you can combine cross-company with a dataAreaId filter.

You can also reduce the number of fields pulled by using $select.

I've added an example for Customers V2 or all 3 in 1 call to the D365 Finance Postman workspace here:

https://www.postman.com/anthonyblakedev/workspace/dynamics-365-finance-apis/request/22139284-92b3f85a-641a-48dd-8e00-d13539d96a41?action=share&source=copy-link&creator=22139284&active-environment=a6fe8359-b53f-49db-bd87-616db2899d1a

Feel free to make use of it, it is based on Contoso demo data.

It looks like this:

{{resource}}data/CustomersV3?$select=dataAreaId,CustomerAccount&cross-company=true&$filter=(dataAreaId eq 'USSI' or dataAreaId eq 'GBSI')

Anthony
Waed Ayyad 8,328 Super User 2025 Season 1 on at

Like (0)

Report

Pull in cross-client data from MS 365 DFO via O-Data feed

Hi,

You can determine the legal entities that you want to fetch data from it:

{{url}}/data/CustomerGroups?cross-company=true&$filter=dataAreaId eq 'usrt' or dataAreaId eq 'xxx'

Thanks,

Waed Ayyad

If this helped, please mark it as "Verified" for others facing the same issue