Visualizing xDB and CRM data through Power BI

Since the introduction of the Experience Database in v7.5, Sitecore has steadily been introducing more and more tools to allow you to get a better picture of the users interacting with your brand.  In a previous post, I showed a proof of concept on synchronizing data from your CRM directly into xDB.  From there, you can tailor the user’s experience based on what tends to be a richer dataset of customer data.

While we could continue synchronizing more and more data from CRM to get a better picture directly in Sitecore, that approach could have tremendous performance implications.  Why not just let each system do what they do best and stitch together that data, creating logical relationships between disparate datasets, in a tool like Power BI?  I’m very new to the Power BI world, but what I’ve seen so far really has amazed me!  The insights you can glean by combining these data sources can be incredibly powerful.

Enough rambling though…  Hold on tight and let’s get to it!

Exposing xDB Data to Power BI

I went round and round a few times with this problem.  While Power BI has a number of content packs available to connect to various datasources, MongoDB is unfortunately not one of them.  Being an old-school SQL guy who is very new to interacting with MongoDB, I went on a quest to see what I could find to easily get started.

I started down the route of creating an OData feed using an OData Provider for MongoDB called MongOData, written by Vagif Abilov.  However, I ran into an issue with some of the null property types stored in xDB.  Again, with my newness to working with MongoDB as a whole, I wasn’t terribly confident in my ability to troubleshoot his codebase, so I moved on.

After a couple more false starts, I landed on creating a Web API service to expose select collections.  The source code for this project can be found here, but since we all like looking at code, here you go!

The Data Layer

Since the code to get any collection is pretty much the same, I used the repository pattern to create a generic IAnalyticsRepository<T>.

public interface IAnalyticsRepository<T> where T : IMongoEntity
{
   IEnumerable<T> GetCollection(string collectionName);
}
public class xDBAnalyticsRepository<T> : IAnalyticsRepository<T> where T : IMongoEntity
{
   private readonly IApplicationSettings _appSettings;

   public xDBAnalyticsRepository(IApplicationSettings appSettings)
   {
      _appSettings = appSettings;
   }

   public IEnumerable<T> GetCollection(string collectionName)
   {
      if (String.IsNullOrEmpty(collectionName))
         throw new ArgumentNullException("collectionName", "A required parameter was null or empty");

      var database = GetDatabase();
      var collection = database.GetCollection<T>(collectionName);
      return collection != null ? collection.FindAll().ToList() : null;
   }

   private MongoDatabase GetDatabase()
   {
      var server = MongoServer.Create(_appSettings.ConnectionString);
      return server.GetDatabase(_appSettings.DatabaseName);
   }
}

IMongoEntity is just an empty interface I used to constrain my generics.

The Domain Layer

The domain layer is where I put any business logic.  I started with just retrieving the Contacts and the Interactions collections from Mongo.  Here’s the code for getting the Contacts collection.  I put in a little bit of logic to identify Anonymous contacts as such so I didn’t have empty rows in my Power BI datasets, but more on that later.

public class SimpleResponse
{
   public List<string> Errors { get; set; }
   public bool Success { get { return !Errors.Any(); } }

   public SimpleResponse()
   {
      Errors = new List<string>();
   }
}
public class CollectionResponse<T> : SimpleResponse where T : IMongoEntity
{
   public IEnumerable<T> Collection { get; set; }
}
public interface IContactService
{
   CollectionResponse<Contact> GetContacts();
}
public class xDBContactService : IContactService
{
   public readonly IAnalyticsRepository<Contact> _repository;
   private readonly IApplicationSettings _appSettings;

   public xDBContactService(IAnalyticsRepository<Contact> repository, IApplicationSettings appSettings)
   {
      _repository = repository;
      _appSettings = appSettings;
   }

   public CollectionResponse<Contact> GetContacts()
   {
      var response = new CollectionResponse<Contact>();

      try
      {
         var contacts = _repository.GetCollection(_appSettings.ContactsCollectionName);

         if (contacts != null)
         {
            foreach (var contact in contacts)
            {
               if (contact.Identifiers == null)
               {
                  contact.Personal = new Person() { FirstName = "Anonymous" };
               }
            }

            response.Collection = contacts; 
         }
      }
      catch (Exception ex)
      {
         response.Errors.AddRange(ex.ToCollection());
      }

      return response;
   }
}

The Web API layer

And finally, the Web API layer…  By the way, I’m using Simple Injector for dependency injection.

public interface IApplicationSettings
{
   string ConnectionString { get; }
   string DatabaseName { get; }
   string ContactsCollectionName { get; }
   string InteractionsCollectionName { get; }
   string SecurityKey { get; }
}
public class xDBAppSettings : IApplicationSettings
{
   public string ConnectionString { get { return GetConnectionString("mongoDb"); } }
   public string DatabaseName { get { return GetValue<string>("DatabaseName"); } }
   public string ContactsCollectionName { get { return GetValue<string>("ContactsCollectionName"); } }
   public string InteractionsCollectionName { get { return GetValue<string>("InteractionsCollectionName"); } }
   public string SecurityKey { get { return GetValue<string>("SecurityKey"); } }

   private static T GetValue<T>(string key)
   {
      var returnValue = default(T);
      var converter = TypeDescriptor.GetConverter(typeof(T));
      if (converter != null)
      {
         object value = ConfigurationManager.AppSettings[key];
         if (value != null)
         {
            try
            {
               returnValue = (T)converter.ConvertFrom(value);
            }
            catch (Exception)
            {
               Trace.TraceError(String.Format("Failed trying to convert '{0}' to type '{1}'", value, key));
            }
         }
         else
            Trace.TraceError(String.Format("Could not find the config value '{0}'", key));
      }
      return returnValue;
   }

   private static string GetConnectionString(string key)
   {
      var returnValue = String.Empty;
      object value = ConfigurationManager.ConnectionStrings[key];
      if (value != null)
      {
         try
         {
            returnValue = value.ToString();
         }
         catch (Exception)
         {
            Trace.TraceError(String.Format("Failed trying to convert '{0}' to type '{1}'", value, key));
         }
      }
      return returnValue;
   }
}
public abstract class BaseWebApiController : ApiController
{
   public IApplicationSettings AppSettings { get; private set; }

   public BaseWebApiController(IApplicationSettings appSettings)
   {
      AppSettings = appSettings;
   }
}
public class WebApiAuthorizationAttribute : ActionFilterAttribute
{
   public override void OnActionExecuting(HttpActionContext actionContext)
   {
      var baseController = (BaseWebApiController)actionContext.ControllerContext.Controller;
      var arguments = baseController.ActionContext.ActionArguments;

      if (arguments.ContainsKey("key") && arguments["key"] != null)
      {
         var key = arguments["key"].ToString();
         if (key == baseController.AppSettings.SecurityKey)
            base.OnActionExecuting(actionContext);
         else
            throw new HttpException(401, "Unauthorized");
      }
      else
         throw new HttpException(401, "Unauthorized");
   }
}
public class ContactsController : BaseWebApiController
{
   private readonly IContactService _contactService;

   public ContactsController(IContactService contactService, IApplicationSettings appSettings) : base(appSettings) 
   {
      _contactService = contactService;
   }

   [WebApiAuthorization]
   public IEnumerable<Contact> Get(string key = null)
   {
      var response = _contactService.GetContacts();

      if (response.Success)
         return response.Collection;
      else
         throw new HttpException(500, response.Errors.Stringify());
   }
}

<configuration>
   <appSettings>
      <add key="DatabaseName" value="rba_analytics" />
      <add key="ContactsCollectionName" value="Contacts" />
      <add key="InteractionsCollectionName" value="Interactions" />
      <add key="SecurityKey" value="1d6af5d1far81ad6516a4v6f5a46fd" />
   </appSettings>
   <connectionStrings>
      <add name="mongoDb" connectionString="mongodb://localhost:27017" />
   </connectionStrings>
</configuration>

Because I haven’t figured out how to configure either header api keys or certificates in the Power BI connection yet, I just put a random string in the web.config and pass that as a parameter in the Web API URL.  Since xDB is storing PII data, access should be tightly controlled.  I’ll get this figured out before it goes into any type of production environment.  🙂

Now that you have the basics for a Web API project, host this in IIS or something and you’ll get a result something like this:

Connecting the Dots in Power BI

Now that we have access to xDB data in a way that Power BI can natively access it, let’s configure those Power BI queries.

I start with an empty report in Power BI Desktop and chose Get Data->Web.

xDB Interactions Data Set

Enter the URL for the Interactions Web API method.

This will open the Query Editor.  From the Query Editor, in the Transform tab, click Convert To Table.  Select the default options in the To Table dialog.

In the header of the table, click the button in the column header to select which columns to expand.

Select SaveDateTime, ContactVisitIndex, VisitPageCount, BrowserName, City, PostalCode and click OK.  In the Properties of the Query in the right-hand pane, change the name from Query1 to xDB Interactions.  Select the SaveDateTime column, click the Transform tab, then select Date->Parse.  This parses the string value to a Date.

Now, click the Home tab and Close & Apply.

xDB Contacts Data Set

Follow the steps above, but connect to the URL for the Contacts Web API method, but this time, select the columns, ID, First Name, Surname and Identifier.

On the Home tab, select Merge Queries.

In the bottom table, select the xDB Interactions table, select the Id column and the ContactId columns in the top table and bottom tables respectively.  For Join Kind, select Inner, then click OK.

Expand the last column and select all the columns.

Select the FirstName and Surname columns, click the Add Column tab and Merge Columns.  Name this new column Full Name.

Select the VisitPageCount column, select the Transform tab and change the Data Type to a Whole Number.

Finally, in the Properties of the Query, in the right-hand pane, rename this query from Query1 to xDB Contacts and Interactions.  Then click Home->Close & Apply.

Unique xDB Identifiers Data Set

Create a new query pointing to the Contact Web API method by clicking on Get Data->Web.  Convert this data to a table and expand the column.  Select only the Identifier column.  In the Home tab, click Remove Duplicates and Remove Rows->Remove Blank Rows.  Rename this query to be xDB Unique Identifiers.  This query will be used to link together the many-to-many relationship between the ContactSet and the xDB Contacts and Interactions queries.

CRM ContactSet Data Set

Now, let’s go get data from CRM!  Click on Get Data and select OData Feed.  Enter the URL for the Dynamics CRM OData Service (for Dynamics CRM 2015 Online, it’ll be something like: https://your-org.crm.dynamics.com/XRMServices/2011/OrganizationData.svc) and click OK.  Configure the appropriate access for environment and click Connect.

This will bring up the Navigator dialog.

Select the AccountSet and ContactSet and click Load.

Depending on the amount of data in your CRM, this could take some time to load.  We can optimize this by filtering out columns.  A LOT of them.  🙂

In the Query Editor, you can click the Choose Columns button to select the columns you’d like to retrieve.  For the ContactSet, I filtered the columns down to FirstName, LastName EmailAddress1 and ParentCustomerId.  Then I expanded the ParentCustomerId column to display Id and Name.  For the AccountSet, I filtered the columns down to AccountId, Name and TerritoryId.  I then expanded the TerritoryId column to show the Name column.

Creating the Logical Relationships

Now that we have all this data, let’s create a relationship between the AccountSet and ContactSet.  On the left side of the main window, click on the relationships tab.

In the Ribbon, click the Manage Relationships button.  This will open the Manage Relationships dialog.  Create the following relationships.

Creating the Visuals

Now that all of the relationships have been created, we can start creating the visualizations of the data.

Let’s start by adding a Slicer visualization against the Territory column in the AccountSet.

This will yield:

Next, add a Card visualization against Account Name in AccountSet and specify that it should be a Distinct count.

Do the same with a Card for Full Name in the xDB Contacts and Interactions data set.  It should be a distinct count as well.

Next, add a Card visualization against the VisitPageCount field in the xDB Contacts and Interactions data set.  But this time, it’s not a Distinct Count, but a Sum.  This should yield something like:

Next, let’s add a Funnel visualization against the SaveDateTime and VisitPageCount fields in the xDB Contacts and Interactions data set.

It should look something like:

Next, let’s add a Area Chart visualization against those same two columns – SaveDateTime and VisitPageCount.

Finally, we’ll add two Table visualizations.  One against the Account Name field in the AccountSet data set and the other against the FirstName, LastName and EmailAddress1 columns in the ContactSet data set.

All up, it should look something like this:

Since we created all of the relationships between these data sources, we can filter on the Territory Slicer or the Date Funnel and the other visualizations will update automatically.  So, with this report, we can see how many of our Accounts or Contacts from those accounts are interacting with our site by day.

You could also create a report that tells you what percentage of your traffic is coming from existing customers, what percentage is from active leads and what percentage is wholly unknown, rather than just New Visitors and Returning Visitors from Google Analytics.

I’ll admit, I’m new to BI, but hopefully this gets your imagination running on how you can combine these disparate datasources with data from xDB to gain deeper insights into how users are interacting with your site!

Happy Sitecore trails, my friends!