Project Online - Odata Feed with $select

[lgrasset]

Hi,

I'm trying to build an OData service onto our Cloud data manager to make our financial data available into Power BI.

Basically, our data models are described by star schema - each fact is identified by a tupple of typed dimension values - value for dimensions based on table is taken from an underlying reference table - reference table can be shared by several dimensions.

When a user is requesting data, he always filters it according to what he wants to focus on. He filters according to dimension value (for example year = 2016, account = Turnover) and selects dimensions detail he wants to analyse (for example 2016 turnover detailed by partners, countries and activities). It exactly the purpose of $filter and $select in the OData URL schema.

We have had a feature in our standards querying online tool to allow the user to get the well-formatted OData URL corresponding to its current query. When pasted into the Power BI standard OData connector, Power Query gets the data, but it fills out it with null value in all non-selected dimensions as declared as nullable properties in the $metadata file, even if I repeat the $select in the @odata.context of the feed ("@odata.context": ".../$metadata#fact_table(dim1,dim2,...)").

How can I make Power Query show only columns present in the current feed, according to $select parameter ?

Which namespace needs to be used in the $metadata file ?

Is there something to set in "Property" declarations ?

Is there some specific annotations ?

[v-qiuyu-msft]

Hi @lgrasset,

I think the generated OData URL from your standards querying online tool is not proper. I would suggest you look into the Select System Query Option firstly. Then you can connect to OData in Excel to see if the same results returned as in Power BI desktop, see: Connect to an OData feed (Power Query).

Best Regards,
Qiuyun Yu

[lgrasset]

Hi,

Thanks for your answer.

To make it clear, I've created a minimal OData service to illustrate my issue.

You can find the service at url http://devel.inativ.fr/odata/dbg and the metadata at http://devel.inativ.fr/odata/dbg/$metadata.

if you use this url in your browser, you'll get a synthetic json with only 3 properties.

http://devel.inativ.fr/odata/dbg/FACT?$select=CLOSING,COMPANY,AMOUNT&$filter=ACCOUNT%20eq%20'R70100'...

The same url pasted in Power BI OData connector will give you all extra properties set to null.

How do I avoid Power BI to add these empty properties ?

[kmescha]

Not sure if this is related or not but I'm having a similar issue with an OData query from PowerBI behaving very different than if I run it straight from the browser. Via a trace using Fiddler here is what I'm seeing.

If I run a select from the browser it makes one OData call to the API and I get the expected results in 2 seconds or less for a decent size data set of over 1000 records across 10 fields.

If I run that same OData call from a data set in PowerBI 16 calls are made to the API and in 3 calls captured it makes a call to /api/data/v8.2/$metadata and that call takes 20 seconds each time it runs. So I'm over 1 minute to get the same results in PowerBI. 

Not sure why PowerBI has to make all these extra calls to accomplish pulling the same data.

[vpossoz]

I have exactly the same issue!

PowerBI does an insane amount of calls to the OData feed. In my scenario we have a dashboard that uses many feeds and in PBi we have connected the feed data to each others in order to create the reports we need. It looks like PowerBi is calling the OData feed for every single relationship in which it is used. We get the same feed called 32 times in rapid succession which is crippling the data server, especially because we have about 10 dashboards setup using the same query with different custonmer filters. A couple of days ago PBI requested a total of 768 requests! at 500k rows and 20mb each! totally inaceptable.

Basically PBI actually gets the feed data rather than just getting the meta data when rebuilding the object model internally.

That's pretty rubbish.