Sample OData Report. Related products

Why Odata?

This report is an example of usage of OData API provided by Power BI Sales Cube extension.

OData is an alternative way of data extraction from Magento store for reports creation in Power BI. The primary, standard way would be usage of a dataset created by Power BI Sales Cube extension via REST API: such dataset includes the most optimal data structure and predefined set of measures that could be extended by users. OData integration is less efficient and requires more development efforts, more knowledge about data modeling for more advanced report creation. 

With OData users can create more customized reports that require special data model. It provide additional flexibility for the cases where it is required. In some cases, custom data model may help to create more efficient calculations and provide better usability.

Integration via OData API allows to do advanced custom data modeling that may also help to merge Magento data with other data sources.

Report Description

The report is showing list of related products where relation is defined as products purchased by the same customer on the same day. 

This is more advanced version of related reports analysis. Usually such analysis is only looking for products that were bought together in one order. But it may often happen that customer will create separete orders for each product. To capture such cases, this report will consider products to be related even if they were purchased in different orders, but they were ordered in the same day by the same customer.

Embedded interactive report

Open this report in Power BI

Report Content

Filters:

  • filter by Website
  • filter by Store
  • filters by Product Categories
  • filters by Product names

Product pairs table. Table lists related product pairs, ordered by Row Total, placing pairs that generate most of the revenue on top of the list. 

Category pairs table. This table lists pairs of categories that have related products. The list is ordered by amount of related product pairs. Selection of a row in this table allows to filter list of product pairs.

The report filters out duplicated pairs. This may be confusing when filters are used, but it should be sufficient for demonstration purpose. 

Data Model

Creation of such relation requires custom data modeling with several role playing dimensions. Fragment of data model behind this report could be seen below. Here Orders and Order Items are used two times to capture pairs of products. Also, Order tables are connected with many-to-many relationships with custom created key Customer-Day.

Related products report data model

Create more reports 

This report is an example of possibilities that will be available while using Power BI Integration extension. With OData interface users can create more of custom reports that fit specific business needs. OData API is not limited only for usage with Power BI, it may also be used with other BI tools, that support OData interface.