Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
lgrasset
New Member

Project Online - Odata Feed with $select

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 ?

 

4 REPLIES 4
v-qiuyu-msft
Community Support
Community Support

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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 ?

 

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.

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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors