In this series of articles we will take the RRC data from the SQL database and serve it via an OData REST API and show how we did it.
The series will consist of 3 parts:
- Part 1: Creating the OData REST API in Visual Studio using Entity Framework Core and C#
- Part 2: Setup Continuous Integration and Delivery for automated build and release pipelines to publish to Azure using Azure DevOps
- Part 3: Offering the API to the public via a secured Azure API Management gateway and developer portal
Sounds like a lot…I know, so if you want to fast forward to the end and start testing the API and the data just continue reading…you should be up and running in under 5 minutes.
Give Me The OData!
To get started testing the OData API you’ll need two things, the URL and a subscription key. The main reason behind that is to prevent abuse, this API is a proof of concept, for now at least, and as such I haven’t put in all the normal protections, just a simple free sign-up and key.
Below is a step by step guide to get you the data:
Sign up on the developer portal: https://mi4publicdata.portal.azure-api.net/signup
Go to the Products page and create a new subscription to the TXRRC DATA
After you create a new subscription, go to your profile page and grab the key
Now that you have the key, go to your favorite app Excel, Power BI, SpotFire, Tableau…all these apps support OData feeds
As an example in Excel, go to the Data Tab, Get Data, From Other Sources, From Odata Feed
Copy paste the OData feed URL and the subcription key to look like this:
https://mi4publicdata.azure-api.net/txrrc/odata?subscription-key=[insert your key]
Replace [insert your key] with the key from the portal!
Click OK and on the next prompt, leave authentication as Anonymous and click Connect, you will see the tables:
Select the table you want, if you want to download multiple tables, just check the “Select multiple items” checkbox on the top left.
Click Load…and that is that!
Subscribe below to get notified of future updates to this series, I will be talking about how I created the project in more details and also about the different ways you can query OData feeds, and how to create custom reports based on that data.