How to Access the Data Warehouse via OData

OData

The Data warehouse API is based on OData (Open Data Protocol), which is an OASIS standard that defines a set of best practices for exposing data (usually business related) to be easily read and modified by partners. The standardised protocol enables seamless integration with tools like Microsoft® Excel®, Tableau®, SAP® tools, IBM® tools and many more. In most cases you only need a URL identifying the API to be consumed and a set of credentials. This document contains information about how to interact with the itslearning data model exposed via our Data Warehouse API.

Enabling the API

Access to the Data warehouse API is usually via an add-on module and must be enabled by itslearning. Please contact your sales representative/account manager or your local itslearning service team to inquire about making the API available for a specific site. Once you have been given access to the site you will need to activate access to data from the data warehouse. The steps to access the data are as follows:

  1. Get confirmation of activation from your sales representative/account manager.

  2. Get your service endpoint URL. This will be needed your reporting/BI tool the data warehouse.

  3. Activate access to Data Warehouse/OData from within itslearning.

  4. Get the OData service credentials to access the from within itslearning. These will be needed by c

Service Endpoint URL to Access the OData API

The OData "endpoint" denotes the location on the web from where you can access your data. The data warehouse OData endpoint URL varies by the your itslearning hosting centre. To workout your endpoint URL, add the site's short name, without the square brackets, to the end of the URL. For example, the URL for the U.S. site boston.itslearning.com would be https://us1reporting.itslearning.com/odata/boston

  • Most United States sites: https://us1reporting.itslearning.com/odata/[site_short_name]

  • USIT sites: https://reporting.itslearning.com/odata/[site_short_name]

  • EU1Master sites: https://eu1reporting.itslearning.com/odata/[site_short_name]

Please contact your local support if you are not sure which hosting center your site uses.

OData Service Login credentials

The credentials (username and password) from an itslearning admin user account is required to access OData API. These are the same credentials that your BI/reporting tool will use.

You can create a specific administrator account within itslearning to access the OData API.

  • The account must be an administrator. S tudent or guest accounts will not be able to access the API.

  • The account must be a "native" itslearning account created in itslearning. Usernames and passwords from SSO or other authentication options will not work. E..g. you cannot use an Active Directory login.

  • It is possible to lock a the user account, and so access to Odata API by providing an incorrect password multiple times in a row, just like in itslearning.

  • It is vital that the user name and password for this account are kept secret and you should use both a complex user name and a complex password to protect access to your data. If you forget or need to change these, you can so it is better to start with complexity and security from the beginning.

Activating Access to the OData API from itslearning

Even if you have created an administrator account, access to the OData API is not yet enabled. As default, no administrator has access to the OData API.

The administrator account must have the 'Data Warehouse API' policy. This grants that user access to the API. A profile can be used to give the policy to just that user account.

Note: Only administrator roles can access the API. Even if the API profile setting or policy is given to a non-administrator (e.g. student) the credentials will not work. This is a security feature to protect access to sensitive data.

Accessing OData from 3rd Party Tools

It is important when accessing the OData APO to make sure you follow our best practice guidelines.

Currently Itslearning supports:

This API should be usable with any tool that can use an OData API as a data source, or connect via an ODBC driver.

For many tools you will need to enter the location of the feed and then the credentials of the administrator account with access to the Data Warehouse.

Most reporting tools will have a similar login UI:

Logging in with Chrome

Microsoft Excel

images/download/attachments/46639414/AuthenticationDialogWebBrowser.png

images/download/attachments/46639414/image2016-7-21_11_44_16.png

Example: Accessing OData from Power BI

Microsoft Power BI

images/download/attachments/46639414/image2018-7-6_8_34_40.png

images/download/attachments/46639414/image2018-7-6_8_35_26.png

images/download/attachments/46639414/image2018-7-6_8_37_4.png


Example: Accessing OData from Excel

images/download/attachments/46639414/image2018-7-5_16_14_53.png

OData feed: add URL from access form "Data Warehouse API"

images/download/attachments/46639414/image2018-7-5_16_18_59.png

Choose table or tables which you need load and click choose "Load" button.