Sitecore 9: A Workaround for xConnect API Issue with Experience Profile

The latest release of Sitecore 9.0 finally includes xConnect, a very much anticipated API Framework that allows integrating contacts and their experience data from practically any third party data source. xConnect is a scalable service layer that sits between xDB and any client (including Sitecore roles like Delivery and Reporting) that wants to read, write or search contacts and interactions.

On top of outstanding documentation available for xConnect, Jason Wilkerson has already published a number of posts introducing some xConnect API features

There is, however, at least one known issue that you will find very frustrating as soon as you submit your first interaction using xConnect API which is:

The Experience Profile currently only supports website visit interactions. As a result, if an interaction does not fully populate the facets required for a web visit, an error is displayed.

A full list of known issues can be found here:

The following image illustrates the error:

Sitecore 9 experience profile hotfix

A quick search in Logs reveals the following error:

11936 17:40:25 ERROR Object reference not set to an instance of an object.
Exception: System.NullReferenceException
Message: Object reference not set to an instance of an object.
Source: Sitecore.Cintel
Sitecore.Cintel.Reporting.ReportingServerDatasource.Visits.GetVisitsWithLocations.FillTableWithRow(DataTable rawTable, Interaction curInteraction, Int32 index)
Sitecore.Cintel.Reporting.ReportingServerDatasource.Visits.GetVisitsWithLocations.GetTableFromContactXconnect(DataTable rawTable, Guid contactID, Nullable`1 interactionID) at Sitecore.Cintel.Reporting.ReportingServerDatasource.Visits.GetVisitsWithLocations.Process(ReportProcessorArgs args)

Decompiling Sitecore.Cintel assembly we can see the following method:

    private void FillTableWithRow(DataTable rawTable, Interaction curInteraction, int index = 1)
      WebVisit webVisit = CollectionModel.WebVisit(curInteraction);
      IpInfo ipInfo = CollectionModel.IpInfo(curInteraction);
      int count = Enumerable.ToList<PageViewEvent>(Enumerable.OfType<PageViewEvent>((IEnumerable) curInteraction.Events)).Count;
      DataRow row = rawTable.NewRow();
      row["ContactId"] = (object) curInteraction.Contact.Id;
      row["_id"] = (object) curInteraction.Id;
      row["ChannelId"] = (object) curInteraction.ChannelId;
      row["StartDateTime"] = (object) curInteraction.StartDateTime;
      row["EndDateTime"] = (object) curInteraction.EndDateTime;
      row["AspNetSessionId"] = (object) Guid.Empty;
      if (curInteraction.CampaignId.HasValue)
        row["CampaignId"] = (object) curInteraction.CampaignId;
      row["ContactVisitIndex"] = (object) index;
      row["DeviceId"] = (object) curInteraction.DeviceProfile.Id;
      row["LocationId"] = (object) Guid.Empty;
      row["UserAgent"] = (object) curInteraction.UserAgent;
      row["SiteName"] = (object) webVisit.SiteName;
      row["Value"] = (object) curInteraction.EngagementValue;
      row["VisitPageCount"] = (object) count;
      row["Ip"] = (object) ipInfo.IpAddress.ToString();
      row["Keywords"] = (object) webVisit.SearchKeywords;
      if (!string.IsNullOrEmpty(webVisit.Referrer))
        Uri uri = new Uri(webVisit.Referrer);
        row["ReferringSite"] = (object) uri.Host;
      row["GeoData_BusinessName"] = (object) ipInfo.BusinessName;
      row["GeoData_City"] = (object) ipInfo.City;
      row["GeoData_Region"] = (object) ipInfo.Region;
      row["GeoData_Country"] = (object) ipInfo.Country;

The method is used to copy each interaction coming from xConnect to DataRow. DataTable with all interactions will further be used to bind data in a number of Experience Profile views including visits, latest statistics, visit summary.

A quick look to the collection database (which is a SQL 2016 DB by default in Sitecore 9 XP0 deployment option), InteractionFacets and Interactions tables will indicate your newly created interaction lacks a number of interaction facets including WebVisit, IpInfo and an entity reference for DeviceProfile. FillTableWithRow() method will fail in a number of places with NullReferenceException if your interaction lacks such data. If your interaction comes from online visits, Sitecore (Sitecore.Analytics.Tracker) will take care of such Facets and you won’t face any issues in the Experience Profile.

One solution could be to add missing data when you submit interaction to xConnect API. You can create new Device Profile entity and attach the missing facets:

//Add Device profile
DeviceProfile newDeviceProfile = new DeviceProfile(Guid.NewGuid());
newDeviceProfile.LastKnownContact = existingContact;
interaction.DeviceProfile = newDeviceProfile;

//Add fake Ip info
IpInfo fakeIpInfo = new IpInfo("");
fakeIpInfo.BusinessName = "Home";
client.SetFacet<IpInfo>(interaction, IpInfo.DefaultFacetKey, fakeIpInfo);

//Add fake webvisit
WebVisit fakeWebVisit = new WebVisit();
fakeWebVisit.SiteName = "Offline";
client.SetFacet<WebVisit>(interaction, WebVisit.DefaultFacetKey, fakeWebVisit);

Note that your interactions will be accompanied with fake/dirty Facets data and more importantly above code violates the purpose of DeviceProfile entity. I don’t recommend you to do so, unless you know the purpose very well or replace fake data with valid meanings.

Another solution is to override FillTableWithRow() method. Basically, we need to add a number of checks for null reference and bind column with DBNull if facets are missing.

Sitecore 9 experience profile hotfix 2

Once your code is ready, you will need to patch Sitecore configuration to use your GetVisitsWithLocations class. Create a new patch file in /App_Config/Inlcude with the following:

<?xml version="1.0" encoding="utf-8" ?>
<configuration xmlns:patch="">
      <group groupName="ExperienceProfileContactViews">
            <processor type="Sitecore9.HotFixes.Processors.Profile.GetVisitsWithLocationsCustom, Sitecore9.HotFixes" patch:instead="*[@type='Sitecore.Cintel.Reporting.ReportingServerDatasource.Visits.GetVisitsWithLocations, Sitecore.Cintel']" />
        <! –Another 7 pipelines using the same processor-->

Complete source code available for download on github.

Sitecore will certainly get the current list of known issues fixed in the next Update.

Fly high with Sitecore! Ask Brimit for boost.

Do you need help with your Sitecore project?