OData

I wanted a way for Tableau public to use data from MySQL. Since Public has no database options, I choose OData. OData.org is the War and Peace of documentation and would be very surprised if this helped at all. Thanks to ChatGPT and Kobi, Hostinger’s own AI, I was able to create an OData stream, Tableau Public could use. Setup Nginx first, with reverse proxies. BTW, use localhost 127.0.0.1 in location block even for remote servers. All of the work happens behind server’s firewall.

    location /odata/sample {
        proxy_pass http://127.0.0.1:5000/;
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
        proxy_set_header X-Forwarded-Proto $scheme;
    }

Inside of
server {
listen 80;

In blue the reverse proxy sets up url portion /odata/sample to relate to port 5000 as in gunicorn –workers=3 –bind 0.0.0.0:5000 –reload odata.sample:app which is the command prompt to call your Flask app odata/sample.py with app = Flask(__name__) in it.

Animalytix odata shows drug sales to animal clinics. Give it a minute for 150k records to load. Copy and past this URL in Tableau Public, Power BI or any app that supports OData version 4. This OData doesn’t yet take input parameters.
Police Violence shows overreach while policing.
State Region Zone reference.

A short sample is just static odata. 3 main sections (@app.route)s are required. At / (in purple) defines the URL with data used, metadata (in blue) at /$metadata which is XML, and the reporting data section in this example /People (in green). Important to get the XML portion correct. NameSpace is JaredService and EntityType Name is Person, then EntitySet Name=”People” EntityType=”JaredService.Person”. People will join $metadata to the main content as well as being the last element in the URL. Tableau Public won’t state a specific error other than saying server is unavailable. Power BI came to the rescue in giving specific errors so configuration can be corrected. ChatGPT was also great at drilling down to find causes and solutions.

from flask import Flask, jsonify, make_response

app = Flask(__name__)

@app.route('/')
def index():
    data = {
        "@odata.context": "http://jaredcoffin.com/odata/$metadata",
        "value": [
            {
                "name": "People",
                "url": "People"
            }
        ]
    }
    return jsonify(data)

# Minimal OData $metadata endpoint
@app.route("/$metadata", methods=["GET"])
def metadata():
    metadata_xml = """<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="4.0"
           xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx">
  <edmx:DataServices>
    <Schema Namespace="JaredService" xmlns="http://docs.oasis-open.org/odata/ns/edm">
      <EntityType Name="Person" OpenType="true">
        <Key>
          <PropertyRef Name="ID"/>
        </Key>
        <Property Name="ID" Type="Edm.Int32" Nullable="false"/>
        <Property Name="Name" Type="Edm.String"/>
        <Property Name="Age" Type="Edm.Int32"/>
      </EntityType>
      <EntityContainer Name="Container">
        <EntitySet Name="People" EntityType="JaredService.Person"/>
      </EntityContainer>
    </Schema>
  </edmx:DataServices>
</edmx:Edmx>"""
    response = make_response(metadata_xml)
    response.headers["Content-Type"] = "application/xml"
    #response.headers["OData-Version"] = "4.0"
    return response

# Minimal OData entity set endpoint
@app.route("/People", methods=["GET"])
def people():
    data = {
  "@odata.context": "http://jaredcoffin.com/odata/$metadata#People",
  "value": [
    {
      "ID": 1,
      "Name": "Alice",
      "Age": 30
    },
    {
      "ID": 2,
      "Name": "Bob",
      "Age": 25
    }
  ]
}
    response = make_response(jsonify(data))
    response.headers["Content-Type"] = "application/json;odata.metadata=minimal"
    response.headers["OData-Version"] = "4.0"
    return response

if __name__ == "__main__":
    app.run()