Share
Report
1,788Hi
I've experienced very different performance when using ODATA 2 (Organization Data Service URL) versus ODATA 4 (Instance Web API Service Root URL) in Excel 2016 Power Query to connect to OnPrem CRM 2016 via ODATA-Datafeed.
Mainly I'm referring to tests I had done after reading this CRM tip of the day:
https://crmtipoftheday.com/2016/06/10/turbocharge-crm-to-powerbi-connection/
A query for a single table (Account) without any expanding/joins, transformation etc and about 6500 records with ODATA2 took ~ 37% of the time it took with ODATA4.
=> Win for ODATA2
A query of another table (Contact, 13000 records) where I added fullname and parentaccount name (=Customer field) by using expand took with
ODATA 2: 0:47 min / 0:47
ODATA 4: 2:00 min / 1:59
=> Win for ODATA2
By the way: the structure of the available customer field database columns looks different between ODATA2 and ODATA4:
My strange finding was: in ODATA 2.0 the column ParentCustomerId.Name delivers Accounts & Contacts even when ParentContactId.Name was always null, whereas in ODATA 4.0 I get parentcustomerid_account.name which delivers only Accounts and parentcustomerid_account.fullname which delivers only Contacts.
A query for the Contact-Table incl. expand of originatingleadid.fullname
ODATA 2: 0:43
ODATA 4 0:42
=> No winner
Merge vs Expand
Queried 3 tables Account, Contact, Country (custom); Not using a Data Model. Filtering on Account.Addr1_Country<>Germany.
ODATA 2.0:
Expanded columns in Query Editor: 1:04 / 0:55
Merging tables instead expanding columns in Query Editor (see tip of the day) 2:19 / 2:17
ODATA 4.0:
Expanded 0:36 / 0:36
Merged 0:50 / 1:00
Clear winner: ODATA 4 and using expand instead of merge.
In the tip of the day there was a clear statement for using ODATA 4.0 (which is, I think the official statement by Microsoft). In the PowerBI Podcast
https://crm.audio/captain-redlaces-episode-2-the-reckoning/
Scott Sewell recommended to use ODATA 2.0. Also other community members speak more for ODATA 2.0 than ODATA 4.0.
My test scenarios provide a mixed picture of what is better. As I think most queries done by End Customers using Excel will have more the character of my last test (Merge vs Expand) I should go for recommending ODATA 4. Also because Expanding columns is much easier to set up against configuring joins separately via Merge.
Do you have any different test results or recommendations?
*This post is locked for comments
I have the same question (0)
All responses (
Answers (
