Leg Two of the Reporting Journey

After arriving in the reporting database, our journey continues, taking us to the end reports. This second leg of the journey isn’t a difficult one or long, as there are three quick stop overs before arriving in a chart or table.

Stop 1: Dimension and Fact Tables

Unlike past versions of Sitecore (anything before 7.5) data collected and reporting where ran out of the same analytics database. Because this system was transactional first, the database was reasonably normalized allowing for straightforward finding and summarizing of data for a general developer.

Sitecore Experience Platform has separated the transactional from the reporting, <a href=”TO POST ONE”>the first leg of this journey,</a> creating a denormalized database built specifically with reporting in mind.

The new reporting database is now built on dimension and fact tables. For the non-DBAs riding along, this means the aggregation has processed our data into common data warehouse modeling, allowing more and faster reporting to occur.

Sitecore has also provided twelve pre-built views, combine and summarize data for use in reporting. When looking at this list you can see that many of these are the common initial questions that are asked of the data when first starting out.

Stop 2: Search Indexes

During the aggregation process, much of the data that is collected about a contact record (both known and anonymous) is placed into a search index. Depending on your installation this might be Solr or Lucene.

I’ve included indexes in this leg of the journey as it is used by Sitecore in a couple of the reporting screens. It is a smart place to begin building if your plan to report or create an application on top of the Experience Profile.

The main screen where this indexed information is used is the opening list of recent contacts when you open the Experience Profile. The Search bar and general data shown are all pulled from the index.

Segmentation rules used in Email Experience Manager and List Manager perform the checks against the search index as well.

Stop 3: Reporting Service

The final stop into Experience Analytics (and other reports) is the Reporting Service API. Sitecore has abstracted the retrieval of data from the reporting database into a JSON based REST service. This doesn’t mean we couldn’t create custom reports that pull through other mechanisms (such as directly from SQL) but it does provide a nice standard to base creating reports on.

With a little digging, I found that the URL for accessing the reporting API is https://<my site> /sitecore/api/ao/aggregates/{site}/{segments}/{keys}. This is route is mapped in Sitecore.ExperienceAnalytics.Api.Http.Configuration.RouteMapper, found in the assembly Sitecore.ExperienceAnalytics.dll. The controller that manages are requests is Sitecore.ExperienceAnalytics.Api.Http.AnalyticsDataController, also found in the Sitecore.ExperienceAnalytics.dll assembly.

With this knowledge we can breakdown the basics of what happens from a call used by the Online Interactions by Visits and value per visit line chart on the opening dashboard screen.

[The next post in the series will be looking at the tables and charts that exist by default and how to create our own. Right now will focus on the specifics of how the data is captured.]

The data is retrieved via HTTP GET, to the following URL that breaks down as follows


Mapped API Route


Corresponds to the mapped routes ‘Site’ parameter.

The Site parameter provides filtering information as to if data returned should be all sites being tracked (the default) or another site defined in the web.config’s sites node definition, such as ‘website.


Corresponds to the mapped routes ‘segments’ parameter.

This is the ID to a segment element defined in the master database at: /sitecore/system/Marketing Control Panel/Experience Analytics/Dimensions/<Specific Dimension>/<Segment Item>

Segments provide the cross section (filter) of what type of visitors should be included in the counts of the data shown


Corresponds to the mapped routes ‘keys’ parameter.

Currently this can be one of two values, All or sum, depending on the requirements of the query


Beyond the controller required fields, there is a set list of available filter options that can be passed as query string parameters.

These options are defined/limited by Sitecore.ExperienceAnayltics.Api.Query.ApiParamenters

Available query strings are:

  • dateFrom
  • dateTo
  • dateGrouping
  • keyGrouping
  • keyOrderBy
  • keyTop
  • keySkip
  • KeysFromParent

Sitecore.ExperienceAnalytics.Api.ReportDateService::ExecuteQuerey() is eventually called with the parsed URL, including query string parameters. From within this method, there is a number of helper methods that are then used to build a SQL query that is executed against the reporting database. The helper methods can be found in the Sitecore.ExperienceAnalytics.Api.Query.QueryBuilder namespace. The basic process looks like this

Sitecore provides JavaScript that takes the returned JSON and places it into the chart or table you’ve selected if you’ve add custom dashboards to Experience Analytics. If you are spinning your own reporting page, you’ll need leverage some your own custom JS or server side code to parse and display the data.

Because the actual querying of data is abstracted, the actual handling of report query calls can be placed on a server with the role ‘Reporting Services’. This role can be installed with others, such as the Processing or CM roles, but if your plans involve a lot of custom reporting queries it would be highly advised to scale this role to an individual server for best data return rates.

Arriving into the Station

And with our heads pondering the possibilities of custom reporting applications, we roll into the station of Experience Analytics and the other data dashboards Sitecore has built for us. Understanding where and how the collected data is managed provides the power to begin asking specific questions around how to allow visitors to get the most engagement out of each visit.

Reporting Service Bibliography

<a href=” developing/xdb_overview/architecture_overview ” target=”_blank”> Provides a glossary of different server roles and services, </a>

<a href=” developing/xdb_overview/reporting_architecture ” target=”_blank”> Sitecore diagram showing a basic reporting architecture, sitecore_experience_platform/developing/ xdb_overview/reporting_architecture </a>

<a href=” xdb_configuration/configure_a_reporting_service_server ” target=”_blank”> Explains the files to enable/disabled when configuring the Reporting Services Role, xdb_configuration/configure_a_reporting_service_server </a>