Error when opening an OData service in Excel, incorrect EDMX metadata

I’m trying to write a RESTful API to publish some data sets. Doing this in Python and wanting to follow OData standards. Goal is to be able to open the data from within Excel (Data > New query > OData).

First attempts seem hopeful: I’ve produced a Flask api that connects to a mysql database and returns the desired rows. What doesn’t work yet unfortunately is opening from Excel. It errors out on the metadata descriptor, saying it encountered an unexpected property name of ‘Article name’ (alphabetic first column of my dataset) where it expected only ‘name’ and ‘url’ in a service document.

I can’t seem to figure out what’s wrong with the metadata that’s being produced, so any help is most appreciated.

app.py:

from flask import Flask, jsonify, request, make_response
from flask_restful import Resource, Api, reqparse
import pandas as pd
import mysql.connector as sql
import ast
import xml.etree.ElementTree as ET

app = Flask(__name__)
api = Api(app, default_mediatype='application/json')

config = {
  'host': '127.0.0.1',
  'port': '3306',
  'user': '***',
  'passwd': '***',
  'database': '***',
  'charset': 'utf8mb4',
  'raise_on_warnings': True
}

class FinancieleInstrumenten(Resource):
    def get(self):
        parser = reqparse.RequestParser()
        parser.add_argument('begrotingsjaar', required=False)
        parser.add_argument('begrotingshoofdstuk', required=False)
        args = parser.parse_args()  # parse arguments to dictionary

        try:
            cnx = sql.connect(**config)

            # Build query depending on input variables
            if not args['begrotingsjaar'] and not args['begrotingshoofdstuk']:
                qry = "SELECT * FROM FinancieleInstrumenten LIMIT 25;"
                res = pd.read_sql(qry, cnx)
            elif not args['begrotingshoofdstuk']:
                qry = """SELECT * FROM FinancieleInstrumenten WHERE Begrotingsjaar = {} LIMIT 25;"""
                res = pd.read_sql_query(qry.format(args['begrotingsjaar']), cnx)
            elif not args['begrotingsjaar']:
                qry = """SELECT * FROM FinancieleInstrumenten WHERE Begrotingshoofdstuk = {} LIMIT 25;"""
                res = pd.read_sql_query(qry.format(args['begrotingshoofdstuk']), cnx)
            else:
                qry = """SELECT * FROM FinancieleInstrumenten WHERE Begrotingsjaar = {} AND Begrotingshoofdstuk = {} LIMIT 25;"""
                res = pd.read_sql_query(qry.format(args['begrotingsjaar'], args['begrotingshoofdstuk']), cnx)

            # Build response with OData header
            resp = make_response({
                '@odata.context': 'https://stukkenparser.gitlab-minfin.nl/financiele-instrumenten/$metadata',
                'value': res.to_dict('records')
                })
            resp.headers['OData-Version'] = '4.0'
            return resp
        except Exception as e:
            print(str(e))
        finally:
            if cnx.is_connected():
                cnx.close()

    def put(self):
        return {'message': 'Only method GET allowed.'}, 405
    def post(self):
        return {'message': 'Only method GET allowed.'}, 405
    def patch(self):
        return {'message': 'Only method GET allowed.'}, 405
    def delete(self):
        return {'message': 'Only method GET allowed.'}, 405


api.add_resource(FinancieleInstrumenten, '/financiele-instrumenten')


@app.route("/financiele-instrumenten/$metadata", methods=['GET'])
def index():
    if request.method=='GET':
        root = ET.parse('financiele-instrumenten.metadata.xml').getroot()
        return app.response_class(ET.tostring(root), mimetype='application/xml')


if __name__ == '__main__':
    app.run(debug=True, host='127.0.0.1', port=1337)

financiele-instrumenten.metadata.xml:

<edmx:Edmx xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx" Version="4.0">
  <edmx:DataServices>
    <Schema xmlns="http://docs.oasis-open.org/odata/ns/edm" Namespace="NL.MinFin.OData.FinancieleInstrumenten">
      <EntityContainer Name="FinancieleInstrumentenEntities">
        <EntitySet Name="FinancieleInstrumentenSet" EntityType="NL.MinFin.OData.FinancieleInstrumenten.FinancieleInstrumentenType" />
      </EntityContainer>
      <EntityType Name="FinancieleInstrumentenType">
        <Property Name="Begrotingsjaar" Type="Edm.String" Nullable="false" />
        <Property Name="Begrotingshoofdstuk" Type="Edm.String" Nullable="false" />
        <Property Name="Begrotingsnaam" Type="Edm.String" Nullable="false" />
        <Property Name="Artikelnummer" Type="Edm.String" Nullable="true" />
        <Property Name="Artikelnaam" Type="Edm.String" Nullable="true" />
        <Property Name="Artikelonderdeel" Type="Edm.String" Nullable="true" />
        <Property Name="Instrument" Type="Edm.String" Nullable="true" />
        <Property Name="Regeling" Type="Edm.String" Nullable="true" />
        <Property Name="Ontvanger" Type="Edm.String" Nullable="true" />
        <Property Name="KvK-nummer" Type="Edm.String" Nullable="true" />
        <Property Name="Bedrag" Type="Edm.Int64" Nullable="false" />
      </EntityType>
    </Schema>
  </edmx:DataServices>
</edmx:Edmx>

Answer

short answer:

your medatata is incorrect, because your entitytype misses a key

long answer:

from: https://docs.microsoft.com/en-us/odata/concepts/data-model:

Entity types Entity types are named structured types with a key. They define the named properties and relationships of an entity. Entity types may derive by single inheritance from other entity types.

The key of an entity type is formed from a subset of the primitive properties (e.g. CustomerId, OrderId, LineId, etc.) of the entity type.

So, the essence of an entity is, that you can uniquely identitfy it, to reach it from the outside our be source for navigation properties. Therefore, you will have to define an entity key, so that, for example, when ODATA wants to navigate to one of them, it can do that, for example /FinancieleInstrumentenEntities(key)/.

Your edmx should look something like this ( i took a guess about the key, hope its unique ) :

<edmx:Edmx xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx" Version="4.0">
  <edmx:DataServices>
    <Schema xmlns="http://docs.oasis-open.org/odata/ns/edm" Namespace="NL.MinFin.OData.FinancieleInstrumenten">
      <EntityContainer Name="FinancieleInstrumentenEntities">
        <EntitySet Name="FinancieleInstrumentenSet" EntityType="NL.MinFin.OData.FinancieleInstrumenten.FinancieleInstrumentenType" />
      </EntityContainer>
      <EntityType Name="FinancieleInstrumentenType">
        <Key>
          <PropertyRef Name='Begrotingsjaar'/>
          <PropertyRef Name='Begrotingshoofdstuk'/>
          <PropertyRef Name='Begrotingsnaam'/>
        </Key>
        <Property Name="Begrotingsjaar" Type="Edm.String" Nullable="false" />
        <Property Name="Begrotingshoofdstuk" Type="Edm.String" Nullable="false" />
        <Property Name="Begrotingsnaam" Type="Edm.String" Nullable="false" />
        <Property Name="Artikelnummer" Type="Edm.String" Nullable="true" />
        <Property Name="Artikelnaam" Type="Edm.String" Nullable="true" />
        <Property Name="Artikelonderdeel" Type="Edm.String" Nullable="true" />
        <Property Name="Instrument" Type="Edm.String" Nullable="true" />
        <Property Name="Regeling" Type="Edm.String" Nullable="true" />
        <Property Name="Ontvanger" Type="Edm.String" Nullable="true" />
        <Property Name="KvK-nummer" Type="Edm.String" Nullable="true" />
        <Property Name="Bedrag" Type="Edm.Int64" Nullable="false" />
      </EntityType>
    </Schema>
  </edmx:DataServices>
</edmx:Edmx>

As you can see, for composite keys you can add multiple property refs. You need at least one .

The properties you use as key must be not nullable, and it is important that the combination is unique. (im not sure about that in that case)

If you don’t have items that are uniquely identifyable, you can try complex types instead of entities, which you attach as property to a root entity (which still needs a key).