Business Data Connectivity (BDC) is one of those killer features that SharePoint 2010 has to offer (even in SharePoint Foundation) but it seems to be a rare sighting amongst SharePoint environments. When planning your information architecture, bringing Line of Business data (LOB) from external sources (databases, business services, etc.) seems to be a daunting task that most leave aside. Even if it needs to be done, in my experience, most take the custom development path instead of using BDC features like External Lists and External Data columns.
Is BDC worth the effort or will it leave you unfulfilled? Without actually going through the process of creating the BDC connection, let’s rundown its features and find out what’s good and what’s not so good.
External Lists – Not your Ordinary List
While the idea of magically adding a list based on your external data is awesome, the fact is that external lists are very limited. Here are some caveats, to name a few:
- No lookups
- If you try creating a lookup column from your External List, it will actually create an External Data Column for you
- If you try creating a lookup column from your External List programmatically (for example, with PowerShell), your column will always remain empty
- Limited field types (text, integer, number, DateTime and Booleans)
- Limited field controls in the new and edit forms (for example, no date picker)
- No item level permissions
- No inline editing
- No workflows
The list goes on. It’s basically a deprived version of a normal list. There is one good thing though: the list is automatically updated when it’s viewed. Notice the infamous “long operation” animation when loading an external list:
External Data Columns
Unlike its big brother the external list, external data columns feel more robust and polished. In terms of display, they are quite similar to lookup columns but provide a richer experience with the entity picker. That said, they are more complicated to setup since they need filters defined in order to correctly use the picker:
One other thing that is great with external data column is the fact that you can add projected fields based on your field value (exactly the same way as with lookups). That way, you can see the columns related to the entity you selected:
As you might have noticed with the previous screenshot, there’s a nice little refresh button next to the external data column’s title. That means that unlike the external list, data surfaced by the external data column isn’t updated upon page view, you must click the refresh button. For a normal user, it might not be very intuitive for them to click on that button in order to get the latest data.
Although the external data column is a great tool, it can be limited when used in some scenarios. For example, when added to a Document Set, the column cannot be used as a shared column:
I also tried adding the shared column programmatically with a rather particular result. It seems to work but the document set content isn’t updated with the value inherited from the document set.
Worthy Mention: You can Migrate BDC Data with Sharegate
Honestly, the fact that Sharegate supports external data is pretty sweet. It also supports the new Excel export/import feature. For example, we can bulk edit the data through Excel. Here is the original data:
Once exported to Excel, I can change the value of the External Data column:
Then once imported back using Sharegate, I get the following results:
Notice that the associated projected fields have also been updated. Assuming you have the same external data column set up elsewhere, you can migrate the data easily using this feature.
So, Should you be using BDC or not?
Looking back on my tests and conclusions, it’s pretty obvious that most people won’t want to get into using BDC because of the challenges it can present. I recommend using BDC only if you can handle the technical challenges you’ll come to face when using it. All in all, it does offer a nice set of features but you should be aware that you might have to code some parts in order to overcome certain limitations.
Considering the fact that you already have a great platform integrated with SharePoint that allows you to surface LOB data is great. It sure does beat coding everything from scratch!