Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
You can't get everything into SharePoint and nor do you probably want to. A typical company has many systems. You have got back end databases and SQL Server, Oracle. You have systems like PeopleSoft and SAP, mainframe applications, custom line of business applications. Those aren't going to certainly disappear and it wouldn't be practical to extract all their data to store inside SharePoint. But we do often want to get to that data and to get it into SharePoint lists and reports and dashboards. We can use something called Business Connectivity Services or BCS to connect to those external databases and those external lines of business systems.
Any reasonably up-to-date system that accepts a pretty standardized way of connection such as a web service or a .NET database driver, or OLE DB will allow us to connect to it using BCS. In the last version of SharePoint, SharePoint 2007, this was called the Business Data Catalog. It's been added to since then and it's certainly a little easier to deal with but that doesn't mean it's easy. You first have to understand that what we are asking SharePoint to do is a fairly difficult process indeed.
not just from a technical standpoint, but it usually has big impacts on your business itself. You are wanting SharePoint to connect to an external system, you really have to think about a few things pretty especially. First off, you need to decide exactly what data do you need access to. If you are saying I want SharePoint to connect to our customer service database, do you mean every single piece of data, or just one table, or just several fields? You should, of course, be minimizing the exposure. You should need to have access to the minimum necessary.
You want to ask questions like is this read only or read/write? In the last version of SharePoint, it was really read only. It was the only connectivity story we had, but now we can have write access to that external data as well. Who gets to use this? Are we trying to make it available to every user on every SharePoint or is it just restricted to a few people? How are you going to connect to that external system? From the technical standpoint, what user ID do you have, what password, how is that monitored, how is that taken care of, what happens if the password expires, are there any auditing or logging requirements? If we are certainly reaching in to a line of business database and changing some data, does that have any impact on the system? So a lot of that has to be discussed before you go into the next stage, which is actually creating what's called the BCS Model.
The model is a very long description of exactly how do we connect to that system, where is it, what user ID and password do we connect to, what's the data that we are interested in, what's the key to that data, how might that data relate to another piece of data. Everything is described in the minutest detail. And really while you don't need to write programming code, you will almost certainly need either database administrator or a developer to create the BCS Model for you. Now, in this version of SharePoint, we can actually use SharePoint Designer as a starting point for creating that.
You can create what's called an external content type and this can be exported as a BCS Model but you still run into the issues of how do you describe the connection, do you really understand the data that you are talking to and what you are wanting to bring into SharePoint. Once that file is defined, and I am not going to go through the process of creating a BCS Model in this course. That really is a course all on itself. That file needs to be imported into SharePoint Central Administration and must be configured by a farm administrator.
Just because it's imported, it doesn't mean it will work. They need to describe who gets permission to it, who gets read permission, who gets write permission if necessary, but once that's done, what's the impact? And that's really what I wanted to talk about here. Once your developer, your DBAs and your farm admins have created this BCS Model, and taken it into SharePoint, what do we do? Well let's take a look. What's actually possible is that you can use that business data on potentially every page in every site. I am going to go into my team site here.
I am going to make a new page just so we have some screen real estate to put this data on. I am going to call this BCSDemo. Click Create. So I have a nice blank area of content to play with. The way that you interact with the Business Connectivity Services area is by going to the Insert tab and finding the Web Parts and selecting the categories of Business Data. Most of the Web Parts here are related to that Business Connectivity Services part of the SharePoint.
Classic one here is the Business Data list. It shows me a list of data from that external source. I am going to select that and click Add. This does need to be configured because it has no idea what it's meant to be showing. So it's saying here Open the Tool pane and choose the type of data to display, and over in the Tool pane on the right -hand side, we can click this little button here that says Select External Content Type and I have a couple of different pieces defined here. I am going to select the first one, which is some information from the AdventureWorks database.
I am going to click OK and see we have got a little update bar here. It's connecting to that external database and bringing back all this information. It's looking a little ugly here but luckily this is configurable. I do need to go back into the Edit Web Part mode, because when I do that, I get a link that says Edit View that will allow me to trim the data that I am looking at. So the first question is when this connects, do you retrieve all items? Do you limit the number of item displayed? Which columns are you showing? In this case, I might just want to show Name and ProductCategoryID and I will put Name first.
Then we can have the ability to sort by one of the columns. We can decide to sort or filter and display items on one page, or display items in pages of well let's say 10 in this case, and click OK and it will take care of managing that. In this case, we are now showing in pages of 10. I can just click the little arrow to jump to the next one and that's fetching this information from that database. Of course, they can get a little more complex than this. If you look at the available Web Parts on that Business Data category, you will see things like the Business Data Related List that will allow you to connect one list to another so that when you select say a parent option in one, you will get all the child entries related to it in the other.
You can get a Business Data Item describing a single item from your list. Business Data Actions, if those have been defined in your BCS Model, you can have actions, updating, fetching, deleting against that business data. And while the complexity of working with Business Connectivity Services is all on the front end, it's all about getting it setup and working in the first place. This is really a true integration piece. If you wanting SharePoint to be the program that sits at the middle of the web of your organization and reaches into all your other applications, this is the way that you begin to do that.
Get unlimited access to all courses for just $25/month.Become a member
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.