Forum Discussion

lgrasset's avatar
lgrasset
New Member
9 years ago

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

    • lgrasset's avatar
      lgrasset
      New Member
      9 years ago

      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'%20and%20PARTNER%20not%20eq%20null

       

      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's avatar
        kmescha
        Frequent Visitor
        9 years ago

        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.