Let’s start by looking at a real-world company and the changes it underwent in the 1990s with respect to business data. A global distribution company in 1993 provided storage and distribution of electronic components. The electronic components were packed within the warehouse and shipped to customers throughout the world. Over four years, this company experienced a huge change in the way that it operated, purely due to the development of computer software and how that software was used within each department.

In 1993, the company mainly used Windows 3.0 as an operating system, with only a handful of computers using Windows 3.1, which incorporated networking capabilities. The networking capabilities back then were primarily used for some file sharing, but mainly printer sharing. Besides that, some data was available on dumb terminals from a mainframe system based in the United States.

Teams of people within the organization worked on each customer contract, processing the orders. The order process typically involved receiving faxes of required goods from a customer and then typing the contents of the faxes into a spreadsheet, which was then printed and hand-delivered to the warehouse for packing. The address details for the delivery were then hand-typed again into another software application to produce the shipping label. Tracking the status of the order was usually carried out by telephone.

Soon, technology moved on and the organization began receiving orders by Electronic Data Interchange (EDI), which involved using a single computer to dial up to another computer via modem, in order to receive an electronic file containing the orders to be shipped. This data was then imported into an application called Wisdom.

Within a couple of years, customers were placing their own orders via a website. They used a custom application for the orders and warehouse shipping, which was replicated via an ISDN line to a central server, allowing the rest of the company to report on the data. Customers could track an order using their order tracking number on the website.

Technology evolved very quickly back then, which meant that businesses went from storing physical documents and mailing them when required, to electronic storage and email—within just a few years. Through the use of networking, relational databases, and Internet technology, customers and business users had a much better experience.

However, one big problem existed then and often still exists today: islands of data. For example, say a given tracking system in an organization works exceptionally well, but you want to get a big-picture view of what is going on in the organization. What if you need to know your revenue and profit for the quarter, along with the number of successful deliveries, the number of days your staff were absent, and so on? In the past, obtaining all that information often meant involving other people and departments, and receiving numerous spreadsheets all formatted differently; or you had to track down the information yourself, which meant gaining access to and learning lots of dissimilar systems. The data is out there, but it is separated out on various “islands” spread across different departments, technologies, and systems.

Each department and sometimes each branch of an organization may use its own system for business processes. You will often also find these systems duplicated within an organization with different technologies—for example, Adventure Works in the United Kingdom may use Microsoft Dynamics CRM for customer relationship management, while Adventure Works in the United States might use Salesforce.com. In addition, the human resources group might be using an Oracle application for personnel data, the accounting department may be using Sage for invoicing, and so forth. Each part of the business could be using an application designed specifically for its departmental business processes. It is logical that when you design a system, you are not designing it for the entire organization; rather, you are designing it for a business process, and for that reason, your organization uses great applications for its individual business processes. One issue with this approach is that your organization’s departments may use dissimilar technologies for data storage, and each software application has a different user interface, reinforcing the islands of data problem.

To resolve this issue, you certainly don’t want to create an application that is generic enough to provide all of the required services across the entire organization. You’ll just end up with a jack-of-all-trades application that has fewer capabilities and functionality than the software designed to solve a particular business process. The solution to this problem lies in using Microsoft SharePoint and Business Connectivity Services (BCS) to provide a central hub for all of the business information within the organization.

Today’s businesses thrive on business intelligence. Business people want to be able to troubleshoot problems before they occur (and result in cash-flow issues or even a lack of demand for a specific product). The ability to quickly obtain this business-critical information is of great importance.

A good example of the use of business-critical information is illustrated in a story that comes from a member of the SharePoint community. This person is a business analyst whose job is to provide reporting capabilities for organizations. He was producing some charts based upon products and sales per store when his attention was immediately drawn to a line on a chart showing that sales of chewing gum in a particular store had suddenly stopped. Sales of this chewing gum brand went from hundreds of packages per day to zero per day. He thought this strange, as all of the other store branches were still selling the product successfully. He checked the stock levels at the store, which appeared to be fine, and the product was still current—yet this store had not sold a single pack of the chewing gum for three days. He called the store manager, who discovered that a poster had fallen from the wall and was covering the box of chewing gum. Without business intelligence, this issue might have gone unnoticed for weeks.

SharePoint can be used to bring information together into an easy-to-use, easy-to-navigate central hub of business data. The central hub provides you with the ability to search, view, modify, and report on information across your organization’s data and even beyond, to data outside of your organization.

An Internet search engine is a good example of the central hub concept. You can use a single Internet search engine to search websites for data all over the world, regardless of where that data resides. As a user, you don’t care what technology is used to store the data for Amazon or Expedia, for example, and you don’t care what language was used to develop the website. All you care about is being able to see and retrieve the data quickly when you want it, via a familiar Internet search site.

Figure 1-1 shows how easy it is to get the information you want in a single place. From the Bing search engine, you can retrieve the weather report for your region without having to go to a specific website or television channel.

Figure 1-1. You can easily find information through a single search box.

You can also create a dashboard of information in some search engines, as shown in Figure 1-2. You could spend a good part of your day going to individual websites to check the weather, read the news, find the latest currency exchange rates, and so on. Sometimes it is more useful to have a single page where you can view all of this information at a glance.

Figure 1-2. A dashboard can help you view information and absorb it quickly.

Most of the information in the dashboard in Figure 1-2 is driven through other websites. The data is stored somewhere, but most of the time you don’t know or care where it is stored. Have you ever wondered where websites you visit get weather reports from, for example? The National Climatic Data Center (NCDC) and the National Weather Service (NWS) are two examples of weather information providers. The information from these providers is made available for use by news agencies and other websites.

Figure 1-3 shows an example of information aggregated from multiple data sources, which makes it convenient for people to find and use.

Figure 1-3. You can search data from an Internet search engine regardless of the technology used for the website and data storage.

Aggregating data from multiple external data sources is exactly what you can achieve with business data through SharePoint. SharePoint is quickly becoming a familiar interface within organizations, just as Bing and Google are familiar among users for searching the Internet and finding information. You can view SharePoint as a central hub of information, just like an Internet search engine. When you need to see your organization’s data, you don’t have to learn how to use all of the dissimilar systems the departments in your company use—you simply navigate to SharePoint to see a dashboard of information.

Figure 1-4 shows an example of such a dashboard. The dashboard brings external data into SharePoint from more than one data source.

Figure 1-4. This SharePoint report page shows sales-related information from the Adventure Works SQL database via BCS.

Notice that the Business Connectivity Services (BCS) Chart Web Part is used to display product sales from the current quarter in a 3D bar graph. The bar graph shows the standard cost versus the list price of each order. Below that graph is a Business Data List Web Part that has been customized using SharePoint Designer 2010, showing the products ordered this month along with the quantity, standard cost, and list price. A custom column has been created that shows the list price multiplied by the order quantity minus the standard cost, multiplied by the order quantity. The cost columns are set in currency format using SharePoint Designer 2010, and the sort order is set to the Profit column in descending order. Lastly, the Web Part is configured to show only the 10 most profitable orders that were taken within this quarter.

In the middle-right of the dashboard is another Business Data List Web Part that is obtaining its data from a custom view within the Adventure Works database. The custom view displays the top five salespeople this quarter. Using SharePoint Designer 2010, the rows are formatted with conditional formatting showing those salespeople who achieved at least $5,000 worth of sales. The rows have a green background if that is the case.

Finally, in the top-right corner is a Status List Web Part displaying key performance indicators (KPIs). Unfortunately, the Key Performance List within SharePoint 2010 does not support external lists that contain external data. You can, however, configure SQL Analysis services to base your KPIs on. SQL Analysis Services is being used to determine the number of sales calls made and the number of new customers. This information is obtained from the Microsoft Dynamics CRM application, not the Adventure Works database. In this way, you can see that information from multiple external data sources can be mashed together or combined within a single dashboard to provide a useful view of live business information.

To demonstrate how SharePoint can become the central hub for your organization’s data, you’ll work with a fictitious organization called Adventure Works Cycles throughout this book. Adventure Works Cycles is a large multinational company. The company manufactures and sells metal and composite bicycles to the North American, European, and Asian commercial markets. While its base operation is located in Bothell, Washington, with 290 employees, Adventure Works has several regional sales teams located throughout its market base.

Adventure Works uses a Microsoft SQL Server database to handle buying, manufacturing, and selling bicycles. Within the database, you will find all of the components that make up a bicycle, the cost of each part, and a list of the suppliers for each component. Members of the accounting department use the database primarily to work out the company’s profit and loss based upon the sales of each product. The sales at each Adventure Works store are also recorded within the database so that sales information can be retrieved.

The Microsoft SQL Server database is not the only data store within the Adventure Works organization. Sales representatives use Microsoft Dynamics CRM to track and manage the relationship with each Adventure Works customer. In addition, the human resources group uses an Oracle solution to store information about each of the company’s 290 employees. Within the Oracle solution is information about each employee, such as the employee’s home address, next of kin, salary, skills, and résumé.

Figure 1-5 illustrates all the external data sources that Adventure Works would like to consolidate via SharePoint.

Figure 1-5. Adventure Works wants to use these external data sources within SharePoint to provide a common, more intuitive view of the external data.

In addition to building dashboards with external data via Business Connectivity Services (BCS) in SharePoint, you can perform the following tasks on all business data within your organization:

Through its investment in SharePoint, Adventure Works would like to make better use of the SharePoint product and use it as a central repository where users can obtain business information. Business information, in this case, is a broad category consisting of documents, spreadsheets, databases, websites, employee information, and much more.

You will focus on how you can make business data available to SharePoint throughout this book, but first you need to understand the company’s requirements:

Throughout this book, you will learn how to fulfill the Adventure Works requirements, along with how to connect to the external data systems and administer BCS to ensure that security and performance requirements are also met.

SharePoint is a big investment within an organization due to the need to purchase hardware, software licenses, training, and consultancy. It is therefore sometimes incorrectly assumed that SharePoint should be able to provide everything the organization needs going forward. SharePoint becomes the automatic solution to every problem.

As powerful as it is, SharePoint cannot do everything. It does not replace relational database technology, spreadsheets, or file systems. It often happens that after adopting SharePoint, companies move all their files from the file system into SharePoint, and SharePoint lists are used for data storage. File systems still have their place, as many organizations have extremely large files (such as video files) that are just too big to upload and download on a daily basis. Although SharePoint is not necessarily the place to store such files, it can make your file shares available, helping to make SharePoint the central hub for business information.

This book focuses on new business solutions. After your organization has made a big investment in SharePoint, if you require a new business solution such as a help desk application, the answer that you hear might be to use SharePoint. Is SharePoint the right tool for a help desk application? Maybe. But you still need to analyze all of the help desk applications available and consider them against developing a custom solution versus using existing technology within your company. You might find that SharePoint lists, InfoPath forms, and Workflow provide you with the solution that you require. That said, you might also find that the type of data you want to gather is more suitable for storage in a relational database than in SharePoint lists, or that the chosen help desk solution already uses a relational database back end.

The point is that if you decide to use SharePoint to build all your future solutions, you are likely to be disappointed. SharePoint cannot replace Microsoft Excel or Microsoft SQL Server, but it makes for an excellent tool to expose Microsoft Excel spreadsheets and Microsoft SQL Server databases to users. While SharePoint now offers IDs for documents and list items, it cannot reproduce the relationships required in a relational database management system and, for that matter, it cannot match the performance.

Your organization should still carefully consider its software choices, but keep in mind that even if you opt for an off-the-shelf application that uses a relational database back end, you can make that data available to SharePoint. For example, the help desk team will use the help desk application on a daily basis to receive trouble tickets, escalate tickets, and report bugs. SharePoint can be used to display dashboards showing how many tickets were resolved successfully last month, how many times the service-level agreements were broken, and which employee handled the most tickets.

Although you can’t use SharePoint to resolve every business problem that exists today, you can often use it as part of a new system. SharePoint is many things to many people, and it is a difficult product to describe. Using SharePoint as a central hub of information for existing systems as well as future systems will at least make information more accessible.

This chapter presented an overview of the business problems that Business Connectivity Services (BCS) resolves. These business problems include the fact that organizations often contain lots of data that is stored in dissimilar systems and databases throughout different departments. You learned that by using BCS, you are able to aggregate these external data sources into one familiar application, such as Microsoft SharePoint.

In this chapter, you learned how the problem of isolated data sources came about and also how it can be resolved. The chapter introduced the fictitious company called Adventure Works that you will be working with throughout the book. Some of the business problems that Adventure Works would like to resolve with BCS were highlighted.

This chapter has provided a foundation for subsequent chapters to build on, to show how some of the services BCS has to offer can help a real-world organization.