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 :-
- Create a new “Reporting” plugin and some POCO classes that represent some of the data stored in the components in simpler format.
- Create new controllers for data that has been flattened out by the POCO classes.
- Register the new reporting entities with the engine
- 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)
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”
- Add dependencies to the existing plugins to the new plugin.
- Create a new folder called “Model”.
- Add the following three new classes
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
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
- Modify the ConfigureSitecore class and add our block created in the previous steps
We are now ready to access the OData services exposed by the engine.
Your project should look like this…
Ensure the code is working by starting the commerce engine and viewing the metadata. For example, “http://localhost:5000/api/$metadata”, 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
= OData.Feed("http://localhost:5000/api/Coupons",null, [Headers=[Environment="HabitatAuthoring", ShopName="Storefront"]])
= 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…
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
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.