ODATA API

ODATA API OVERVIEW

The OData API offers an alternative method for retrieving data from the Magento platform, leveraging the datasets and table profiles.

OData's compatibility with a diverse array of business intelligence (BI) software extends beyond Power BI, enabling the integration of Magento data with various BI vendors. Several BI software providers that support OData include:

• Power BI

• Qlik

• Tableau

• Tibco Spotfire

• MicroStrategy 10.5 supports now OData via Data Import

• Dundas

• SAP

• Targit

While our implementation of the OData API has been thoroughly tested with Power BI, we cannot guarantee its compatibility with other software. If you intend to use the OData API with a different BI tool, kindly reach out to our support team for assistance.

OData connectivity allows for the creation of highly personalized reports which might be challenging to create with the default Sales Cube data model. Additionally, it may be leveraged in scenarios that involve more extensive data manipulation prior to report generation. This involves the ability to implement custom grouping, perform custom data transformations, and merge with other data sources.

With the utilization of the OData API, users have the capability to incorporate their Magento data into a Power BI Service Dataflow.

ODATA INSTALLATION AND CONFIGURATION

After installation of the PowerBI extension for Magento, use the following steps to generate an access token and consumer key to send data to PowerBI via OData API:

1. Log in to Admin and click "System" "Extensions" "Integrations" to display the Integrations page.

2. Click "Add New Integration" to display the New Integration page.

3. Enter a unique name for the integration in the "Name" field. Then enter your admin password in the "Your Password" field. Leave all other fields blank.

4. Click the API tab. Select the resource "Insights Ready OData API".

   

5. Click "Save" to save your changes and return to the "Integrations page".

6. Click the "Activate" link in the grid that corresponds to the newly-created integration.

7. Click "Allow".

8. Copy "Consumer Key" and "Access Token Secret" -- those are the user and password for accessing the OData API.

Activating the OData API

In order to utilize OData, it is necessary to install the extension package for Adobe Commerce / Magento that includes OData functionality. If this functionality is not present in your installation, request access to it.

1. Navigate to the Magento Configuration page (Stores Configuration) and select Insights Ready Power BI Integration OData API section.

2. Set "Enable OData API" to "Yes" to enable the API.

3. Set "Enable OData API Log" to "Yes" in case you are facing issues with the usage of OData API. This will document the communication with the OData service in a log file, potentially assisting in the resolution of these issues.

4. Option "Sales Cube preparation schedule" is useful if your data needs to be refreshed periodically to be prepared for being read through the OData API. Read more details on it in the section "Scheduling data actualization for OData API".

Activating OData access to a dataset

Upon completing the dataset creation process, access to the dataset through the Odata API may be enabled by saving it with the "Accessible via OData API" option set to "Yes" when you navigate to "Reports" "Power BI Datasets" "Your Dataset". If "Shared Access to OData feed" is also set to "Yes", other administrative users may access the dataset via the API. By default, if this option remains at "No", only the dataset owner will be able to access it. In this instance, you will avoid using the "Consumer Key" and "Access Token Secret" as authentication credentials for data access. Rather, it is recommended to utilize your Magento user credentials, but this approach of relying on Magento user credentials is considered outdated and not recommended.

Should access be limited to a specific user group, modification of the access control list can be performed by enabling/disabling the "Insights Ready OData API" resource.

It should be noted that access to data for the dataset will only be possible after the data preparation process has been initiated for the dataset.

1. To initiate this process, click on the "Start data preparation now" link located under the Data Preparation Schedule field.

1. You should see the following message "Data preparation was successfully started" which would state that the process has been triggered.

1. Copy the URL that can be found in the "OData API URL".

Test the URL and the combination of consumer key and access token secret ( you may test it in the browser first, if it works, it might download some files).

ODATA CONNECTION TO POWER BI DESKTOP

1. Open Power BI Desktop and select "Get Data" from the home ribbon.

2. In the "Get Data" window, select "OData feed" from the list of available data sources.

1. In the "OData Feed" window, enter the URL of your OData service in the "URL" field.

2. Click on the "Basic" option to expand the window and enter the OData API URL you saved before.

1. Click on the "OK" button to save the changes.

2. You will be prompted to enter your username and password, which in our case are "Consumer Key" and "Acess Token Secret" if you set "Share access to OData feed" to "Yes".

Power BI will connect to your OData service and retrieve the data. You can now select the data you want to work with and start creating your reports by clicking "Load" (you might want to choose "Transform Data" to make the necessary configuration).

SCHEDULING DATA ACTUALIZATION FOR ODATA API

The data accessible via the OData interface is not solely the raw data from the Magento system, but rather data that has been processed for analytical purposes and stored separately from the original data. During the data preparation stage, the data is moved from the Magento operation system to a compact Data Warehouse for further readability in analytical scenarios. The following picture provides a simple schematic of this process.

Therefore, such data preparation occurs each time data is accessed through the OData API. This process is smart and will solely process any new data that was not previously processed. This process can be initiated through a scheduling system. If the dataset is already being loaded to Power BI on a schedule, data preparation will already be included in the schedule. Additionally, it is not necessary for it to be the same dataset, as the data preparation process is performed for all datasets at once.

If the automated loading of data to Power BI datasets is not enabled, users have the option to configure the data preparation step specifically to maintain data prepared for OData access. To do so, users can navigate to the OData API configuration section described in "Activating the OData API". From there, they can choose the desired schedule under the "Sales Cube preparation schedule" option.

Alternatively, users can always trigger the data preparation manually. This could be done on the dataset properties page by clicking on the small link "Start data preparation now" under the Data Preparation Schedule field.