The Data Services/OData API is the right place to start for developers and companies who want to build advanced reporting based on Epicor Prophet 21 data. It is fast, real-time, and straightforward to implement.

What is OData?

The Open Data Protocol (OData) is a standard for consuming data via RESTful APIs. The key to this is that it is RESTful. These APIs are the most common type of web API, well understood, and have lots of support for interacting with them regardless of the technology being used to consume the API. OData works on top of the RESTful API, providing standardized parameters and access methods.

What are the P21 OData APIs

The Data Services API has two schemas for extracting. The first is the V1 API which is exposed via URLs containing “/data/erp/” and the second is OData V4, which is exposed via URLs containing “/odataservice/odata/”. The P21 documentation uses these two access points interchangeably, but developers should consider using the OData V4 endpoints as they are the most recent version of the API.

To extract data from the OData endpoint, a developer needs to access a URL on their middleware server. The URL is customized based on the specific table or view that access is needed to. A sample URL looks like https://{yourmiddleware}/odataservice/odata/table/address. When using the URL, the “table” part can be a value of “table” or “view” depending on which is being accessed. The “address” is the name of the specific table or view that the data will be retrieved from. Additional query parameters are available to filter the data retrieved as well, and these can be found in the P21 documentation.

What is Qlik Sense?

Qlik Sense is a data analytics tool that allows users to solve complex problems using a friendly interface. Qlik Sense’s associative engine enables users to interact with data and visualizations to gain an increased perspective on what is happening. Qlik Sense is quick to implement and easy for users to learn how to use.

Connecting Qlik Sense to Prophet 21 Data Service API

Security

Epicor’s P21 Data Service API requires that users be granted permission to access the API along with securing each table and view that a user has access to.

To start, ensure that the user has access to the Data Service API by logging into P21 and opening User Maintenance. Choose the users User ID and then switch to the Application Security tab. Find the “Allow OData API Service” and choose Yes. Press Save after making the change.

Next, go back to the User tab and find the role to which your user is assigned. Write the role down. Close User Maintenance and Open Role Maintenance. Enter the role to which your user is assigned. Go to the Dataservice Permission tab. Here, you can choose what tables and views the role can have access to via the Data Service API. The user can be granted access to everything by selecting “Allow All” and pressing Save.

Getting an API Token

Before starting with Qlik Sense, you will need to get your API token. To get this, go to https://{yourmiddleware}/docs/admin.aspx. The token will be in the “Current Token:” area.

You are now good to start working with Qlik Sense.

Connecting Qlik Sense

Create a new Qlik Sense application and choose “Add data from files and other sources.” Find and choose the REST connector and select it. Qlik Sense does have an OData connector but see our note below for it.

Use the following information for the REST connector.

URL: https://{yourmiddleware}/odataservice/odata/{table or view}/{desired table or view}

Query Header:

Name: Authorization Value: Bearer {your token from above}

Name: P21 OData {desired table or view name}

Optional –

Allow “WITH CONNECTION” – Checked

Press “Test Connection,” and you should see “Connection succeeded.”

Press Create.

You will now get a Preview Window. Expand “root” on the left side and check “value.” You will see a preview of the data.

Press next and the data will be loaded into a Qlik Sense application, ready to start building. As more data is needed, new REST Connections for each table can be created. The Qlik Sense data modelling tools can be used to join the data together to create a more complete data set for analysis.

Notes and Issues

Qlik Sense provides a native OData connector for working with OData systems. Qlik OData connector requires a URL for the OData Service Root. Epicor P21 doesn’t document this value, and it is not easily guessable. Should we figure out the Service Root in the future, we will update this article, but for now, the REST connector works very well.

The authentication token can change over time. When the token changes, the Qlik Sense REST connections will break and need to be updated. This token can be programmatically retrieved via the /api/security/token/ API and we will publish an article about how to connect to it with Qlik Sense REST connections in the future.

Summary

Connecting from Qlik Sense to Epicor P21 Data Service API is simple and straightforward. Once you extract information from P21 into Qlik Sense, Qlik Sense’s ETL and script tools can be used to create complex data models for analysis.

Learn more about BizXcel’s P21 and Analytics services by visiting https://www.bizxcel.com/epicor-prophet-21 or schedule time to meet with us at https://www.bizxcel.com/contact.

Connecting Qlik Sense to Prophet 21 Data Services (OData) for Reports and Analytics