none
OData "Multi-level Expand" Query Issue RRS feed

  • Question

  • I am using Power Query (July 2014 version in Excel 2010) to query an OData feed with expansions (navigation properties) between the entities in the underlying framework model.

    The issue is that when I design queries that have "multi-level expansion" and then hit "Apply & Close" the database receives queries from the WCF host website that are absolutely poor queries which take forever to return the result set.

    For example when I start from dimA table and expand into factB table, everything is fine. But after I further expand from factB into dimC in the same query things become ugly.<o:p></o:p>

    Instead of sending a single query which has two left outer joins (i.e. dimA left outer join factB left outer join dimC), the data service sends one left outer join query for every individual row of factB!

    Interestingly, when I have multiple expansions that are NOT “multi-level” e.g. from factB into dimA and again from factB into dimC in the same query there is no issue.

    Monday, July 14, 2014 10:22 PM

Answers

All replies