The following post outlines a method for flattening out the Sitecore commerce data returned by the OData services for use with reporting tools. While this approach can be used for any reporting tool that supports OData, such as excel, we will focus on PowerBI.

The Sitecore commerce engine provides a component based extensibility framework for rapidly enhancing the out of the box features of the product. While this approach is a great way to programatically extend the product, it makes retrieving data for reporting purposes a challenge in some instances.

Our example pulls out all the orders, orders lines, order adjustments and coupons directly from the engine in a nice flat simple structure. The final result organises the data in a “relational” manner which is more familiar to traditional reporting authors.

From a technical perspective, what we are doing it making full blown entities out of the sub components of a order and exposing them. They are never saved so there is no duplication of data. It’s purely a pass through and is only for reporting.

This example will allow Power BI users to create dashboards and reports that display insights into orders and the coupons\promotions consumed by those orders.

If you have requirements to report on other pieces of information stored by the engine the same approach can be followed.

At the end of the post there is also information on how you can create a “Reporting” instance of the commerce engine. You can also cache the data more heavily in the controllers for better performance.

We will complete the following steps :-

  1. Create a new “Reporting” plugin and some POCO classes that represent some of the data stored in the components in simpler format.
  2. Create new controllers for data that has been flattened out by the POCO classes.
  3. Register the new reporting entities with the engine
  4. Create a new Power BI report and hook it up to our new flat OData services.

Our final result will look something like this (Apologies if my poor design skills burn your eyes)

PowerBiReport

Step 1 – Create a new plugin and POCO classes

Here we create a new plugin and add new POCO classes that will flatten out the data for the order lines, adjustments and coupon components. You can add more classes or change these to meet your needs. For instance, we are creating a class to represent the discount adjustment but you can create one for the tax adjustments if you were writing a report on taxes.

Where it makes sense, we just extend the component. Alternatively, a new class is created, examples for each below.

  • Open the Sitecore Commerce Engine SDK solution “Customer.Sample.Solution”
  • Create a new plugin using the new template provided called “Sitecore.Commerce.Plugin.Reporting”new plugin dialog.png
  • Add dependencies to the existing plugins to the new plugin.

dependancies.png

  • Create a new folder called “Model”.
  • Add the following three new classes


using Sitecore.Commerce.Plugin.Coupons;
namespace Sitecore.Commerce.Plugin.Reporting.Model
{
public class OrderCoupon : CartCouponsComponent
{
public string OrderId { get; set; }
public OrderCoupon()
{
}
}
}

view raw

OrderCoupon.cs

hosted with ❤ by GitHub


using Sitecore.Commerce.Core;
using Sitecore.Commerce.Plugin.Pricing;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
namespace Sitecore.Commerce.Plugin.Reporting.Model
{
public class OrderDiscountAdjustment
{
public string Id { get; set; }
//
// Summary:
// Gets or sets the display name.
public string DisplayName { get; set; }
//
// Summary:
// Gets or sets the name.
public string Name { get; set; }
//
// Summary:
// Gets or sets the adjustment.
public Money Adjustment { get; set; }
//
// Summary:
// Gets or sets the type of the adjustment.
public string AdjustmentType { get; set; }
//
// Summary:
// Gets or sets the awarding block.
public string AwardingBlock { get; set; }
//
// Summary:
// Gets or sets a value indicating whether [include in total].
public bool IncludeInGrandTotal { get; set; }
//
// Summary:
// Gets or sets a value indicating whether this instance is taxable.
public bool IsTaxable { get; set; }
public string OrderId { get; set; }
public OrderDiscountAdjustment()
{
}
}
}

view raw

OrderDiscountAdjustment.cs

hosted with ❤ by GitHub


using Sitecore.Commerce.Plugin.Carts;
namespace Sitecore.Commerce.Plugin.Reporting.Model
{
public class OrderLine : CartLineComponent
{
public string OrderId { get; set; }
public OrderLine()
{
}
}
}

view raw

OrderLine.cs

hosted with ❤ by GitHub

Step 2 – Create new controllers

The controllers are really the meat of the solution, here you fetch the data to be wrapped in our new entities and return them. You can also manipulate the data returned here. For instance, here we filter out the adjustment component and only return the discount ones.

You should also look at adding some caching mechanism i.e. once the collection is built the first time, don’t build it again for X amount of time.

Take care in the naming of your controllers, the routing logic in ASP.Net core has strict rules regarding matching the controller names to the entities they return.

  • Add a new folder called “Controllers”
  • Add the following classes to create the classes


// ——————————————————————————————————————–
// <copyright file="OrderCouponsController.cs" company="Sitecore Corporation">
// Copyright (c) Sitecore Corporation 1999-2017
// </copyright>
// <summary>
// Defines the OrderCouponsController controller.
// </summary>
// ——————————————————————————————————————–
namespace Sitecore.Commerce.Plugin.Reporting.Controller
{
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Sitecore.Commerce.Core;
using Sitecore.Commerce.Plugin.Orders;
using Core.Commands;
using Coupons;
using Model;
using Pricing;
public class OrderCouponsController : CommerceController
{
/// <summary>
/// Initializes a new instance of the <see cref="OrderCouponsController"/> class.
/// </summary>
/// <param name="serviceProvider">
/// The service provider.
/// </param>
/// <param name="globalEnvironment">
/// The global environment.
/// </param>
public OrderCouponsController(IServiceProvider serviceProvider, CommerceEnvironment globalEnvironment)
: base(serviceProvider, globalEnvironment)
{
}
[HttpGet]
[Route("Get")]
public async Task<IActionResult> Get()
{
//Here you should can add some caching…
var findOrdersCommand = this.Command<FindEntitiesInListCommand>();
var orders = await findOrdersCommand.Process<Order>(this.CurrentContext, CommerceEntity.ListName<Order>(), 0, int.MaxValue);
if (orders == null)
{
return null;
}
var cartLineComponent = new List<OrderCoupon>();
foreach (var order in orders.Items)
{
var coupons = order.GetComponent<CartCouponsComponent>();
var reportingCoupon = new OrderCoupon();
reportingCoupon.OrderId = order.Id;
reportingCoupon.List = coupons.List;
cartLineComponent.Add(reportingCoupon);
}
return Ok(cartLineComponent);
}
}
}

view raw

OrderCouponsController.cs

hosted with ❤ by GitHub


// ——————————————————————————————————————–
// <copyright file="OrderDiscountAdjustmentsController.cs" company="Sitecore Corporation">
// Copyright (c) Sitecore Corporation 1999-2017
// </copyright>
// <summary>
// Defines the OrderDiscountAdjustmentsController controller.
// </summary>
// ——————————————————————————————————————–
namespace Sitecore.Commerce.Plugin.Reporting.Controller
{
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Sitecore.Commerce.Core;
using Sitecore.Commerce.Plugin.Orders;
using Core.Commands;
using Model;
public class OrderDiscountAdjustmentsController : CommerceController
{
/// <summary>
/// Initializes a new instance of the <see cref="OrderLinesController"/> class.
/// </summary>
/// <param name="serviceProvider">
/// The service provider.
/// </param>
/// <param name="globalEnvironment">
/// The global environment.
/// </param>
public OrderDiscountAdjustmentsController(IServiceProvider serviceProvider, CommerceEnvironment globalEnvironment)
: base(serviceProvider, globalEnvironment)
{
}
[HttpGet]
[Route("Get")]
public async Task<IActionResult> Get()
{
var findOrdersCommand = this.Command<FindEntitiesInListCommand>();
var orders = await findOrdersCommand.Process<Order>(this.CurrentContext, CommerceEntity.ListName<Order>(), 0, int.MaxValue);
if (orders == null)
{
return null;
}
var repAdjustments = new List<OrderDiscountAdjustment>();
foreach (var order in orders.Items)
{
foreach (var adjustment in order.Adjustments)
{
if(adjustment.AdjustmentType == "Discount")
{
var repAdjustment = new OrderDiscountAdjustment();
repAdjustment.Adjustment = new Money(adjustment.Adjustment.CurrencyCode, adjustment.Adjustment.Amount);
repAdjustment.DisplayName = adjustment.DisplayName;
repAdjustment.Name = adjustment.Name;
repAdjustment.OrderId = order.Id;
repAdjustment.Id = Guid.NewGuid().ToString();
repAdjustments.Add(repAdjustment);
}
}
}
return Ok(repAdjustments);
}
}
}

view raw

OrderDiscountAdjustmentsController.cs

hosted with ❤ by GitHub


// ——————————————————————————————————————–
// <copyright file="CommandsController.cs" company="Sitecore Corporation">
// Copyright (c) Sitecore Corporation 1999-2017
// </copyright>
// <summary>
// Defines the OrderLinesController controller.
// </summary>
// ——————————————————————————————————————–
namespace Sitecore.Commerce.Plugin.Reporting.Controller
{
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Sitecore.Commerce.Core;
using Sitecore.Commerce.Plugin.Orders;
using Core.Commands;
using Model;
/// <summary>
/// The resend order controller.
/// </summary>
public class OrderLinesController : CommerceController
{
/// <summary>
/// Initializes a new instance of the <see cref="OrderLinesController"/> class.
/// </summary>
/// <param name="serviceProvider">
/// The service provider.
/// </param>
/// <param name="globalEnvironment">
/// The global environment.
/// </param>
public OrderLinesController(IServiceProvider serviceProvider, CommerceEnvironment globalEnvironment)
: base(serviceProvider, globalEnvironment)
{
}
[HttpGet]
[Route("Get")]
public async Task<IActionResult> Get()
{
var findOrdersCommand = this.Command<FindEntitiesInListCommand>();
var orders = await findOrdersCommand.Process<Order>(this.CurrentContext, CommerceEntity.ListName<Order>(), 0, int.MaxValue);
if (orders == null)
{
return null;
}
var cartLineComponent = new List<OrderLine>();
foreach (var order in orders.Items)
{
foreach(var orderLine in order.Lines)
{
var reportingLine = new OrderLine();
reportingLine.Adjustments = orderLine.Adjustments;
reportingLine.OrderId = order.Id;
reportingLine.Quantity = orderLine.Quantity;
reportingLine.ItemId = orderLine.ItemId;
reportingLine.Name = orderLine.Name;
reportingLine.Totals = orderLine.Totals;
reportingLine.UnitListPrice = orderLine.UnitListPrice;
cartLineComponent.Add(reportingLine);
}
}
return Ok(cartLineComponent);
}
}
}

view raw

OrderLinesController.cs

hosted with ❤ by GitHub

Step 3 – Register our reporting entities

The last step is to register our pass through reporting entities with the engine, this will allow us to access the OData via the controllers in our reporting tools.

  • Add a folder call “Pipelines” and a sub folder called “Blocks”
  • Add a new class as follows


// ——————————————————————————————————————–
// <copyright file="ConfigureServiceApiBlock.cs" company="Sitecore Corporation">
// Copyright (c) Sitecore Corporation 1999-2017
// </copyright>
// <summary>
// ConfigureServiceApiBlock pipeline block.
// </summary>
// ——————————————————————————————————————–
namespace Sitecore.Commerce.Plugin.Reporting.Pipelines.Blocks
{
using System.Threading.Tasks;
using Microsoft.AspNetCore.OData.Builder;
using Sitecore.Commerce.Core;
using Sitecore.Framework.Conditions;
using Sitecore.Framework.Pipelines;
using Model;
/// <summary>
/// The process orders minion block.
/// </summary>
public class ConfigureServiceApiBlock : PipelineBlock<ODataConventionModelBuilder, ODataConventionModelBuilder, CommercePipelineExecutionContext>
{
/// <summary>
/// The _pipeline.
/// </summary>
private readonly IPersistEntityPipeline _pipeline;
public ConfigureServiceApiBlock(IPersistEntityPipeline persistEntityPipeline)
{
this._pipeline = persistEntityPipeline;
}
public override Task<ODataConventionModelBuilder> Run(ODataConventionModelBuilder modelBuilder, CommercePipelineExecutionContext context)
{
Condition.Requires(modelBuilder).IsNotNull($"{base.Name}: The argument can not be null");
modelBuilder.EntitySet<OrderLine>("OrderLines");
modelBuilder.EntitySet<OrderCoupon>("OrderCoupons");
modelBuilder.EntitySet<OrderDiscountAdjustment>("OrderDiscountAdjustments");//.EntityType.HasKey(x => x.OrderDiscountAdjustmentId);
return Task.FromResult(modelBuilder);
}
}
}

view raw

ConfigureServiceApiBlock.cs

hosted with ❤ by GitHub

  • Modify the ConfigureSitecore class and add our block created in the previous steps


// ——————————————————————————————————————–
// <copyright file="ConfigureSitecore.cs" company="Sitecore Corporation">
// Copyright (c) Sitecore Corporation 1999-2017
// </copyright>
// <summary>
// The SamplePlugin startup class.
// </summary>
// ——————————————————————————————————————–
namespace Sitecore.Commerce.Plugin.Reporting
{
using System.Reflection;
using Microsoft.Extensions.DependencyInjection;
using Sitecore.Commerce.Core;
using Sitecore.Framework.Configuration;
using Sitecore.Framework.Pipelines.Definitions.Extensions;
/// <summary>
/// The carts configure sitecore class.
/// </summary>
public class ConfigureSitecore : IConfigureSitecore
{
/// <summary>
/// The configure services.
/// </summary>
/// <param name="services">
/// The services.
/// </param>
public void ConfigureServices(IServiceCollection services)
{
var assembly = Assembly.GetExecutingAssembly();
services.RegisterAllPipelineBlocks(assembly);
services.Sitecore().Pipelines(config => config
// Registers the route for our custom command
.ConfigurePipeline<IConfigureServiceApiPipeline>(c => c.Add<Pipelines.Blocks.ConfigureServiceApiBlock>()));
services.RegisterAllCommands(assembly);
}
}
}

view raw

ConfigureSitecore.cs

hosted with ❤ by GitHub

We are now ready to access the OData services exposed by the engine.

Your project should look like this…

Reporting project.png

Ensure the code is working by starting the commerce engine and viewing the metadata. For example, “http://localhost:5000/api/$metadata&#8221;, you should see a new entity set for our new classes.

Step 4 – Create the new Power BI report

You can now add individual queries for each new flat data entity in Power BI. One of the key things to note here is how to set the headers in the request that the engine requires.

  • Open Power BI desktop
  • Click “Edit queries”
  • Add a new blank query and name it “OrderLines”
  • Ensure your engine is running and exposing the pass through entities.
  • Paste in the following OData query, note the headers.

= OData.Feed("http://localhost:5000/api/OrderLines",null, [Headers=[Environment="HabitatAuthoring", ShopName="Storefront"]])

  • Refresh the query and it should retrieve your order lines in a flat table for you

Repeat the process for our other entities as follows

  • Coupons


= OData.Feed("http://localhost:5000/api/Coupons",null, [Headers=[Environment="HabitatAuthoring", ShopName="Storefront"]])

  • Orders


= OData.Feed("http://localhost:5000/api/Orders",null, [Headers=[Environment="HabitatAuthoring", ShopName="Storefront"]])

  • Order coupons


= OData.Feed("http://localhost:5000/api/OrderCoupons",null, [Headers=[Environment="HabitatAuthoring", ShopName="Storefront"]])

  • Order discount adjustments


= OData.Feed("http://localhost:5000/api/OrderDiscountAdjustments",null, [Headers=[Environment="HabitatAuthoring", ShopName="Storefront"]])

Your queries should look like this…

PowerBi Queries

You will now be able to use these queries to build out your dashboards and reports. As we have added the order id as a property on our pass through entities you can join the queries together.

You can download the example dashboard, here… Commerce

Scalability

You would not want to run reports directly on the same engine instance that either your content editors (Authoring environment) or the production CD servers (Shops environment) are using.

For a production environment, you can install a new engine instance and have your reports point at that particular instance. You can use the standard caching configuration built into the engine to ensure that the data is more heavily cached in the reporting environment.

As mentioned earlier you can also add more caching in the individual controllers. On top of this Power BI also provides the functionality to store the data locally.

With all the these things combined this solution should perform pretty well as is in a production environment.

There is also the option to extract the data via the OData controllers and store it in a data warehouse rather than accessing the the data directly from Power BI if necessary.