A tool for integrating your Sitecore Analytics with your BI platform
About this Project • Prerequisites • Installation • Configuration • PowerBI • Extending the Service
xDB Analytics Extractor is a project that allows you to access and use data from xConnect, the service that communicates with the xDB, where Sitecore stores all of your visitors' data and interactions. With xDB Analytics Extractor, you can retrieve data from xConnect, modify it to meet your requirements, and save it in a format that BI tools can understand. This allows you to create custom charts and reports using data not available in the Sitecore Analytics dashboard.
| Sitecore Analytics Dashboard | PowerBI |
|---|---|
 |
 |
- MSSQL Server 2019 or later (required only if you want to store the data in a database).
- You need a valid Sitecore certificate. To get your certificate follow this guide.
- All of the connection urls you are going to need are outlined in the xDB Analytics Extractor Configuration below. All connection URLs follow the pattern
<instance-name_xconnect>/<subdirectory>
Before running the installation script, make sure that you have made the required changes in the appsettings configurations for the service's projects.
To configure the xDB Analytics Extractor, change the following values:
Value Description Example Certificate Your xConnect Certificate StoreName=My;StoreLocation=LocalMachine;FindType=FindByThumbprint;FindValue=804F256208CCFEA7F17570583EC9BBDA32ECE32EDCEClientUrl Your xConnect url https://sc103xp0_xconnectCollectionClientUrl Your xConnect odataURLhttps://sc103xp0_xconnect/odataSearchClientUrl Your xConnect odataURLhttps://sc103xp0_xconnect/odataConfigurationClientUrl Your xConnect configurationURLhttps://sc103xp0_xconnect/configurationInternalDBPath The path and filename of the internal db for the tool c:\\xDBAnalyticsExtractor\\xDBAnalyticsExtractor.dbCSVExportPath The path you want the files to be exported c:\\xDBAnalyticsExtractorHistoricalDaysExport The number of days in the past the worker will fetch data from 1 SqlServer.ConnectionString The connection string for the database Server=LT-JDOE\\SQL2019;Database=xDB Analytics Extractor;User Id=jdoe;Password=12345;Trusted_Connection=True;
[!NOTE] The SQLite that is defined by
InternalDBPathis used by the tool to keep track of exported data.
[!NOTE] You only need to change the
HistoricalDaysExportonly if you want to run the service in historical mode.
[!NOTE] You need to change the
SqlServer.ConnectionStringonly if you want to export the data into a database.
[!NOTE] You need to change the
CSVExportPathonly if you want to export the data into a file. If you don't have any existing folder that you want to use, you can use theCSV Actionto create a new directory path with all the required priviledges.
To configure the InteractionEvaluator, change the following values:
Value Description Example Certificate Your xConnect Certificate StoreName=My;StoreLocation=LocalMachine;FindType=FindByThumbprint;FindValue=804F256208CCFEA7F17570583EC9BBDA32ECE32EDCEClientUrl Your xConnect url https://sc103xp0_xconnectCollectionClientUrl Your xConnect odataURLhttps://sc103xp0_xconnect/odataSearchClientUrl Your xConnect odataURLhttps://sc103xp0_xconnect/odataConfigurationClientUrl Your xConnect configurationURLhttps://sc103xp0_xconnect/configuration
To configure the DatabaseBuilder, change the following values:
Value Description Example SqlServer.ConnectionString The connection string for the database `Server=LT-JDOE\SQL2019;Database=xDB Analytics Extractor;User
[!IMPORTANT] You need to update the database name in the Script0001_CreateTables.sql too.
The installation of the service requires minimal interaction with the user. All you need to do is to run the
install.ps1 file and the installer will do the rest.
PS > install.ps1The script will read the install.json configuration file and run the defined steps. The default configuration creates a minimal setup. If you want to tailor your installation refer to the Configuration section.
To calculate the workload that your instance can handle and to determine how often the scheduler should trigger, you can use the InteractionsEvaluator project. This project will perform a benchmarking run on your Sitecore instance to statistically determine
- How many interactions can be exported your instance with the XConnect per minute
- How many interactions are added to your instance per day
More info on how to use the InteractionsEvaluator tool and the results it produces, can be found here.
Click on Start and type Task Scheduler to open it. You can also select it from the Start Menu under Window Administrative Tools (or Windows Tools when using Windows 11).
Click the New Folder... in the action bar on the right side and create a new folder. Then click on Create Basic Tasks... in the action bar nad give your task a name.
See image
Tip
It is not necessary to create a new folder, but it is better to organize your tasks in folders for better maintenance.
The trigger determines when the task should be executed. Choose the Daily trigger, and click Next to configure when exactly the tasks need to be executed.
Here comes the important part, for the action, we are going to Start a Program
See image
The executable that we want to run is the xDBAnalyticsExtractor.exe. You can find this executable file in the publish folder, generated during the installation process. Just enter the path in the Program/script field (see screenshot in step 6).
See image
In the arguments field, we are going to add the runtime arguments we want to use. The service supports four arguments out of the box:
- Current Data (
-current): These are the data generated in near-real time on your instance. This option is mutually exclusive with the-historicalargument. - Historical Data (
-historical): These are the data generated from the beginning of time UP TO the first run of the service. This option is mutually exclusive with the-currentargument - CSV Exporting (
-file): This option exports the generated data in CSV format. - SQL Export (
-sql): This option exports the generated data in an SQL Server database.
Caution
Do not use both -current and -historical arguments for the same scheduler, because the scheduler will never run. If you want both -current and -historical data, create two schedulers.
See image
Tip
You can use both -file and -sql options if you want to export to different formats
Tip
You can create your own exporters. For more info refer to the Add a new exporter section.
Click on Next and review your settings on the finish screen. Make sure that you select Open the properties dialog... so we can configure some more settings.
See image
There are two settings that we need to change for our scheduled xDB Analytics Extractor task. We will need to make sure that the service runs even when we are not logged on, which you can do on the General tab.
See image
Also we need to configure when the task will repeat during the day. To do that, click the Triggers tab and select the time, you have calculated using the interactions evaluator tool
See image
Click Ok and enter your password, so the task can run when you are not logged on.
The yourconfig.json file outlines the steps that the installer will run. You can add any of the available steps in your
configuration file, only the provided steps will run.
Caution
While it might be tempting to skip certain steps in the process for the sake of efficiency, doing
so could lead to unexpected outcomes. For instance, if you choose to test your modifications or
deploy your personalized version of the service without first building the project, you may
encounter issues. Therefore, it’s generally advised to complete the clean, restore, build and publish
steps in their entirety. Only consider bypassing these steps if you have a thorough understanding
of the potential implications and are completely confident in your decision to do so.
There are several configuration options for the script, outlined in the Configuration Guide
Note
You can download and install the PowerBI app from Microsoft Store.
- Open PowerBI
- From the Home tab, select Get data> SQL Server
- In the following pop up, set the below values as follow:
| Key | Value |
|---|---|
| Server: | < your-computer-name >\SQL2019 |
| Database | xDB Analytics Extractor |
| Import? | True |
- Click ok.
- Select all the tables to import
- Click Load.
- Open PowerBI
- From the Home tab, select Get data > Text CSV
- Select the Files to Import
- Click on Load
If you import from Database and you get this error:
- Go to file click on Options and Settings > Options.
- Under the Data Cache Management Options click on Clear Cache and Restore Defaults and click OK.
- Save the file and click apply changes.
- Data from the DB will load automatically.
If you import from CSV files and you get this error:
- Select the files you want to import
- Right click with your mouse
- Click on properties
- Change the files to open with “Notepad“
- Save
In this example we create an "Online interactions by visits and value per visit" "line and stacked column" chart, using xDB Analytics Extractor.
- Create a new column on the Interactions Table
Months = FORMAT(Interactions[EndDateTime],"yyyy-MMMM"). - Then select this column created and go to tab Column tools > set the DataType = Date/Time.
- Select the:
SUM(EngagementValue)column from Interactions table, and change the metric display to Average.
- Select the ChannelId and tick all the checkboxes
An exporter is a component that allows you to convert the data of the models into a format that can be easily accessed and analyzed by BI tools. Depending on the type of exporter, you can export data in different formats, such as CSV, JSON, or SQL. An exporter can also store the data into a database for further processing.
For a detailed guide, please refer to the exporter extension guide here.
A processor is a component that transforms raw data from the xDB to a representation that adds value in a conceptual model. Processors can be used to alter, transform or combine raw data to different representations.
One way to understand processors is to think of them as functions that take raw data as input and produce a more meaningful output. For example, a processor can convert the Download event data in a model, ready to be consumed by BI tools. Processors can also combine multiple raw data sources into a single representation, such as combining data to create the Device model. Processors are useful for creating conceptual models that are easier to interpret and analyze than raw data.
For a detailed guide, please refer to the processor extension guide here.
For a detailed guide, please refer to the database altering guide here