Sitecore made it very simple to export and read analytics, site content or commerce data. There are many ways to expose Sitecore data securely, but in few cases, security incompatibility could complicate data export. In this post, I’ll describe the approach of offloading or proxying SSL protected data to the dedicated workplace and thus enabling its safe and secure consumption. An excellent example of applying this approach is integrating Sitecore with analytics platform, such as PowerBI.

Out of the box Sitecore ecosystem provides three main data sources for data extraction: xConnect for anything Analytics, OData Item Service to expose content hosted in Sitecore Content Tree and for commerce applications, Sitecore Commerce Service API through which we can gain access to Customers, Orders, Carts and much more.

OData Item Service.

Configuring this read-only service does not require any development effort and could be configured by power administrators. In some cases, proper firewall and network configuration need to be applied to secure the connection.

xConnect OData Service

Out of the box the service gives us access to Contacts, Interactions, and Devices. The service can be easily extended to create custom end-points utilizing the same undelaying security mechanisms as one provided by Sitecore natively. One of the better ways to consume this service securely by PowerBI or any other client is to offload TLS/SSL authentication using xConnect Certificate. In this example, we developed node.js reversed proxy, hosted on dedicated PowerBI workstation. Sample code can be downloaded from my GitHub.

As you can see in this diagram, we are pulling xConnect requests over SSL to our local or cloud(in my case) workstation, on that workstation we are running the proxy script, which takes ‘localhost’ requests and proxying them to the proper API endpoint while applying security during offloading of the requests. PowerBI Gateway will take data from your local workstation into PowerBI cloud securely on demand or on schedule. In the case of xConnect, TLS certificate used to handle the security.

xcoonect

To run the proxy, just create a BAT file, execute it and watch your requests are being proxied over to the server:

Sitecore Experience Commerce API

Mainly due to .NET Core and Sitecore modular architecture, Commerce API can be easily consumed by any HTTP based client. To generate JWT and offload security we will user node.js (learn more about JWT in Sitecore here) and use it to authenticate with Sitecore XC9 APIs. Sample code can be downloaded from my GitHub.

Similarly to the above diagram, to proxy XC9 APIs we use a custom script, while to authorize/authenticate xConnect requests we use Certificate, here we are using JWT token, generated by XC9 Identity Server

xcoonect

To run the proxy, just create a BAT file, execute it and watch your requests are being proxied over to the server:

The proxy data extraction approach is great for initial data analysis or for proof-of-concept applications as it’ll allow Data Teams gain access to Sitecore without messing with new code development or deploying code to production servers. It may not be suitable for extracting large datasets or getting close-to-near-time data, though.