How can I connect to the OData endpoint connection?

OData Feeds represent a major step forward in live data sharing over the internet without the need to develop APIs.   

All ESdat users have access to an OData Feed.  An Enterprise OData Feed is also available for providing data for system integrations (additional fee applies) not tied to a specific ESdat User. 

The OData Feed means users can securely connect to live ESdat data over the web and view their data in external applications, in particular Power BI, Excel, Power Automate, Power Query and ArcGIS. Users can then create their own Power BI dashboards, or their own Excel graphs or calculations that can auto-update with the latest data in ESdat.  ArcGIS integration requires the Data Interoperability Extension or data feeds can be set up using the Microsoft suite of products (Power Automate and/or Power Query) which can populate datasets linked to ArcGIS datastores. 

To create your OData Feed, you first need to set up a specific Application Password for use through the OData Application.  This will be different to the password you use to log into ESdat.    

This is done under your User Profile – Edit Account - Application Passwords. 

In the application passwords menu, select “New Password” and Enter an Application Name 

Once you select OK a one-time password is created 

It must be saved outside of ESdat and will not appear again. If you lose the password, you can generate another. 

Creating the connection:  

This may vary depending on the platform you are trying to connect to. For Microsoft products OData is connected via “Other Sources”  

You will be prompted to enter the OData feed URL. If there is an option to select a Basic or Advanced OData Feed – Select “Basic” 

If you are unsure of your OData URL, it can be found at the top of the External Data Feeds Page in the administration menu. This menu is only accessible to users with administrator access. 

The OData URL is linked to the URL used to access ESdat.  

If you have your own dedicated URL such as yourcompany.esdat.net then you should enter yourcompany.esdat.net/OData  
 

You will be prompted for an Authentication.  Select "Basic" and enter your ESdat Username and the Application Password generated earlier.    Power BI and Excel will store these login details for you on the local machine / user profile, so you don't need to enter it each time.  

You should now be able to select some ESdat Data Feeds. 

In power BI, you will be able to select particular data views which display different types of ESdat data.  

You can select to import tables together in one connection – or separately. A preview is provided for you to view the data structure and see examples of the data. 

Select Load to load your selected data views into Power BI. 

How are these views created? Are there any other data types I can add to the OData feed? 

The list of OData feed views is displayed in the External data Feeds menu. The most commonly used data feeds are provided by default. Others are available and can be added to the list by selecting “Add new record” data views can also be hidden if they are not used.  

Power BI will only import 10,000 rows and I can’t see all of the data that I am interested in. How can I display filtered data in power BI? 

In the report view of Power BI select the table you wish to filter and select the “Edit Query” option 

Start by selecting the Columns that you wish to filter by. There will be an error message warning you that the list may be incomplete – as it is based on the first 10,000 rows which have been imported. As you start to filter, you will get closer to the data you are looking for. 

As you apply filter items Power BI creates its own form of SQL called Power Query M which is shown to the user.    

You can edit it as required to return the specific data you need.   The example returns Water Chemistry results for the Project “DemoProject”, at Locations "BH03" or "BH27", and for the following Analytes ("Alkalinity (Bicarbonate)",  "Calcium" ,  "Lead" ,  "Sodium").   You can click the “Advanced Editor” button to see the full SQL in a larger box. 

Please refer to Microsoft for more information regarding data visualisations in power BI and using Power Query.