The Sitecore Experience Platform enables you to track all contacts and interactions with your Organization across various channels. All trackable information about an individual contact is stored in the Experience Database (xDB) collection database.
Sitecore Experience Analytics provides dashboards and reports for marketers and marketing analysts to identify patterns and trends in experience data, get behavioral insights across channels such as websites, mobile, email, and commerce. Sitecore Analytics has a large number of reports available out of the box and can be customized to have additional dimensions and reports. Additional reporting capabilities are available in Path Analyzer and Experience Profile. However, access to Sitecore analytics capabilities is limited to Sitecore backend.
Being a leader in Gartner Quadrant for Analytics and BI platforms for 11 consecutive years, Microsoft Power BI has better Analytics capabilities allowing users to model, analyze, and author to deliver business insights. Unlike Sitecore, Power BI is also available for mobile platforms. Power BI reports have better storytelling capabilities to address executives' expectations. Not to mention Power BI has build-in AI capabilities!
Sitecore Community Solutions
A number of solutions are available from Sitecore Community to integrate Sitecore xDB data to Power BI.
Sitecore 8.x
https://citizensitecore.com/2015/11/11/visualizing-xdb-and-crm-data-through-power-bi/
https://www.valtech.com/insights/sitecore-as-a-bi-source-an-ode-to-the-version-8-era-xdb/
https://marketplace.sitecore.net/Modules/E/Experience_Extractor.aspx
Sitecore 9.x via xConnect
http://borisbrodsky.com/sitecore/exporting-and-working-with-sitecore-experience-commerce-9-analytics-data-securely/
http://www.coreblimeysitecore.com/blog/odata-xconnect-sitecore-9/
https://joaoneto.blog/2019/08/28/enable-different-authentication-methods-to-xconnect-odata
where my personal favourite comes from Benjamin Adamski & Boris Brodsky session at SUGCON 2018 https://www.youtube.com/watch?v=lFHpSoTaqLQ
However, all the solutions are built to import data to Power BI Desktop or Power BI Cloud Service. Import mode only supports scheduled or on-demand (manual) data refresh options. Such an approach has limitations, for example, Power BI Service can sync data 8 times a day on a scheduled basis.
Our Goals
The approach described here will have different goals, mainly integrate Sitecore Collection database data to Power BI in real-time, while maintaining security and scalability. We are looking to have a Power BI Dashboard to update automatically.
The approach taken here will be to push data to Power BI Service instead.
Sitecore Collection DB is hosted in secured network infrastructure and is only accessible by Power BI Service (Cloud service) when using data gateway. Accessing Sitecore Collection DB hosted in a cloud does not require a data gateway if Power BI can establish a direct network connection to the source. Both scenarios will still require addressing xConnect authentication limitations. The approach taken here will be to push data to Power BI Service Dataset instead.
Power BI Real-time datasets
On top of Import mode, Power BI has 3 more storage modes: DirectQuery, LiveConnect, Push where Reports get updated automatically. Next, Power BI has a concept of Real-time datasets. There are multiple options to enable real-time datasets such as Push, Streaming, Azure Stream and PubNub. There are pros and cons for each option such as the Push option has a 3-5 seconds delay where Streaming has less than 1-second delay. There is a great video from the Power BI team explaining different options and when you should use each https://www.youtube.com/watch?v=0YIBHfgasok
For our solution, we will use Power BI REST APIs to push data to the Power BI Push dataset. With this option, we can create dataset programmatically and get nearly real-time updates.
Getting data real-time from Sitecore
Sitecore xConnect is the only service layer that sits in between the xDB Collection database and any trusted client or device that wants to read, write, or search contact and interaction data. Any new interaction or contact record is always processed via xConnect. Thus it's a perfect place to “plugin” and push any data bypassing xConnect to additional storage.
Luckily Sitecore xConnect has an option to build and inject Service Plugin implementation. With service plugin, we can subscribe to xConnect operations like AddContactOperation or AddInteractionOperation to capture data submitted to Collection Database and push it to Power BI Dataset. Data stored in Contact and Interaction facets can be extracted and submitted to the Power BI dataset. As soon as the contact session expires and is processed by xConnect it’s data can be submitted to PowerBI dataset.
Collection Database lacks Marketing definitions (such as the name of the Goal triggered, Campaign name or behavior profile name accumulated by contact). Such data will have to be exported separately to make Power BI data more meaningful and executives friendly.
Full solution source code is available on github. Get back here to learn implementation details and setup instructions.
The following Power BI report illustrates sample "Black Friday Campaign report" build based on data pushed from xConnect.
