Sample OData Report. Related Products Classification Ordered on the Same Day by the Same Customer

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 the Magento 1 & 2 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 a 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 the 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, sometimes it happens that  the same customer creates separate orders for each product. To capture such cases, this magento custom report sorts related products even if they were purchased in different orders, but they were ordered in the same day by the same customer.

Embedded interactive report

Open Sample OData Report. Related Products  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".

Create more reports