Integrating External Data into Sitecore

The Sitecore Competency Center team at Perficient has worked on projects in the past where we needed to integrate data that would not be stored within Sitecore with items from Sitecore.  We’ve learned a few lessons from doing this, and I hope to share some with you in this post.

The first lesson is to remember what Sitecore is, and what it isn’t.  It IS a tool to help non-coders manage their website content, but it IS NOT a data warehouse tool.  If you’ve got large amounts of data that does not correspond to needing a separate web page / URL for each piece of that data, chances are, you’ll have better performance keeping that data outside of Sitecore.  The second lesson is that you have to figure out how integrated your external data needs to be with Sitecore – you might need to have access to that data within Sitecore, or you might not.  This decision is an important one, as it helps you decide a methodology to use for the integration.  Next, I’ll detail some of the different methodologies that we’ve used for integrating external data with Sitecore.

If your content managers need to be able access some of that data within the Sitecore Desktop / Content Editor, then you’re probably going to have to look into using or writing an External Data Provider.  We’ve used the Northwind Data Provider for this in the past.  It definitely has its pros and its cons, like almost everything else in the world of Sitecore development.  The pros are that it’s very configurable to your specific situation.  For example, we’ve used it to pull data out of a SQL database, and we’ve also adjusted it so that it could pull in physical files from a file system as Sitecore items.  It also lets you use config entries to manage where the external data will end up in the tree – with a little bit of critical thinking and some modifications to the default provider, you can set this up to require fewer entries and still be quite powerful.  The other pro is that it’s already written and on the SDN, so if nothing else, it’s a great starting point.  Conversely, I could write an entire post on what gotcha’s we’ve encountered while using this code (and maybe I will someday). However, for the sake of this post, I’ll just say that you need to be careful with how you set up your data to be pulled in when using the Northwind Data Provider.  Otherwise you may find yourself needing hundreds of config entries, which will cause your Content Editor to load extremely slowly. You also need to be cognizant of how much data you’re pulling in as well.  Even though this is external data, if you’re pulling it directly into the Sitecore tree, you need to adhere to the “No more than 100 items on any given level” policy, or you will see your performance degrade.

Choosing a Global Software Development Partner to Accelerate Your Digital Strategy

To be successful and outpace the competition, you need a software development partner that excels in exactly the type of digital projects you are now faced with accelerating, and in the most cost effective and optimized way possible.

Get the Guide

We’ve also worked with clients who need data integration, but don’t need to have access to their external data within the Sitecore desktop.  For example, a recent client of ours is redesigning their e-commerce website so that promotional content can be powered by Sitecore.  The client already has a fully fleshed out database / web services to retrieve the tens of thousands of products they sell on their site – which means that there’s no reason to even contemplate storing them within Sitecore – which is good, because once again, Sitecore is not meant to be a data warehouse.  That being said, the client wants to be able to have their marketing team create promotions within Sitecore and have them associate with products – whether that means a specific product, or a subset of products that match the promotion. 

 When presented with this challenge, our first thought was that we could maybe pull into Sitecore a subset of products data from the client’s database – the subset would consist of whatever products needed to be tied to promotions.  As we considered this option, we realized that this methodology becomes clunky very fast.  For example, pulling in a subset of products requires some sort of indicator in the client’s custom database to designate which products are to be pulled in.  That means someone has to maintain that indicator – and in our case, Marketing does not have access to maintain data in the external DB, nor should they have to manage this data both on the external DB side and on the Sitecore side of connecting it to promotions.  Another major flaw in this thinking is scalability – when talking about a site that sells tens of thousands of products, it’s easily conceivable that they would have thousands of products requiring promotions in any given year…travel out to 2-3 years, and you’re already talking about integrating huge amounts of data into Sitecore, and possibly threatening your site’s performance.  (I probably shouldn’t have to say this, but to an e-commerce site, after products offered and price of those products, performance is probably the next most important factor to the success of that site.)

So, we had to go back to the drawing board, and we’re glad we did, because we came up with a much nicer methodology.  We simply added fields to our Sitecore Promotion templates that allow the Marketing team to enter “keywords” for their promotions.  These keywords can be as specific as a Product ID, or as broad as a manufacturer or word found in the product name / description (such as type of material used in the product).  Suddenly, all marketing needs to do to create product-based promotions is to create the promotion within Sitecore and enter the appropriate keywords to connect to the right products!

Well, wait a minute you’re probably saying…after all, there’s still the matter of connecting the product data to the keywords in the promotion right?  Sure, but we can handle that through a little bit of custom code.  The site is already coded to return products based on some sort of user-entered criteria – this data is all retrieved from the external database.  All we had to do to integrate our promotions was add some code that pulls up the promotions, and then matches the details of a given product with our set of promotions.  If a match is found, you’ve got a product with a promotion, and you can now display that promotion accordingly!

Hopefully this post helps you understand that integrating external data into a Sitecore site is certainly possible, as well as gives you some food for thought on the methods that this can be used to do this integration.