One of the many reasons to create an enterprise portal is to collect in one place all of the information necessary to make key decisions. Imagine if a product manager can see a sales report, a marketing analysis, the communication strategy, and the status of development on a new product line all on one page. The product manager can quickly look at all the relevant information and statistics to determine whether the product is on time, on budget, and still fits the target market. This concept is known as business intelligence, or having the right data at the right time in front of the right people to make the right decisions.

Microsoft Office SharePoint Server 2007 is a significant component in Microsoft’s overall Business Intelligence solution. MOSS 2007 has a variety of tools to take large amounts of unstructured data and organize that information in a single, easy-to-use interface. This makes it relatively simple to have important documents, reports, and newsfeeds at your fingertips, assisting you in making corporate decisions.

The Business Intelligence (BI) features in SharePoint 2007 allow you to locate, manage, modify, and share data from many diverse sources, as well as store and present that data later in different presentations.

The new BI utilities in MOSS 2007 give everyone the tools to create meaningful analyses. No longer do developers or warehousing and data mining experts have to struggle with accessing vast amounts of unstructured data to provide useful reports.

SharePoint can connect to many data sources, including SAP, Siebel, and Microsoft SQL Server 2005. The resultant reports generated from acquired data can take the form of Excel workbooks, a SQL Server Reporting Services report, or a Microsoft Office Access report.

The key elements of MOSS 2007 Business Intelligence are:

These key elements can all be created as part of a Report Library on the Create page of your site, as shown in Figure 15-1.

Figure 15-1. Creating Business Intelligence components

The Report Center is used to contain and organize Business Intelligence elements such as Dashboards, Key Performance Indicators (KPIs), document libraries, and connections to external data sources. The Report Center is one of many site templates in the Enterprise site template group. Other Enterprise site templates include the Document Center, Records Center, Site Directory, and more.

The Report Center is completely customizable, providing access to page templates and Web Part pages, and allowing you to create and manage libraries, pages, and lists containing whatever business information you find necessary.

By default, one Report Center site is created when the Top-Level Portal site is created. However, a Report Center can be created for any group, department, or organization as needed. With appropriate permissions, information from the Report Center site can be accessed from any other site or site collection, allowing you to share your data with colleagues, customers, and associates.

The Report Center site template provides a single point of contact for anyone who wants the latest in your company’s Business Intelligence reports. As you see in Figure 15-2, when a Report Center is first created, it is void of any content, allowing you to either add the default Web Parts yourself or to customize it with other selections.

Figure 15-2. New Report Center

The MOSS 2007 Dashboard is a specialized web page template designed to collect and display information from many different sources. This can include charts, metrics, reports, and KPIs. The default Dashboard template contains the following Web Parts:

You can also start with a generic Web Part page and add whatever Web Parts you want, creating a dashboard that will perform to your specifications. You can create a Dashboard from scratch, but it might be easier to start with the basic Dashboard template and modify it as needed.

In Figure 15-3, you can see that information from several sources can be imported into a KPI, including manual input.

Once a key indicator has been created, the indicator’s name and status are displayed, along with values signifying the goal and current value, as shown in Figure 15-4.

Rather than have partners and customers download copies of spreadsheet data from your server, the Excel Services Web Access (EWA) Web Part allows a single copy of an Excel Workbook to exist on a secure server. The EWA service publishes spreadsheet data in your BI Report Center, enabling any authorized person to access this information using only a web browser.

Figure 15-3. Adding information to a KPI

Using EWA enhances the security of your critical business data in the following ways:

EWA technology uses Dynamic HyperText Markup Language (DHTML) and JavaScript to provide web access to .xslx- and .xslb-formatted data without requiring that ActiveX controls be loaded in the observer’s browser. The EWA Web Part can connect to other Web Parts, such as Dashboards, or to other Web Part pages.

Data in an Excel workbook either can be stored directly in the workbook that resides on your secure server or can come from an external data source, such as a database or an Online Analytical Processing (OLAP) cube.

Figure 15-4. One key indicator in the KPI list

You can add an Excel Web Access Web Part to any Web Part page by selecting it in the Add Web Part page, as in Figure 15-5.

Generally an external data source can be either external to the Report Center site but within SharePoint or completely external to SharePoint. External SharePoint sources can include Web Parts, Web Part pages, and Lists. Sources external to SharePoint can be SAP, Siebel, Microsoft Office Access 2007, and Microsoft SQL Server 2005. While it’s outside the scope of this book, you can also write your own data sources.

Figure 15-6. Selecting a workbook to display in an Excel Services Web Part

You can create connections to your external data sources and then store them in the Report Center’s Data Connection Library (DCL). The DCL provides a single interface, allowing you to easily manage all of your external data connections. You can also create such connection libraries in any other part of your MOSS 2007 site as you see fit. Connections can be reused or shared as needed, and they allow anyone with access to view and interact with information from disparate data locations, all presented in SharePoint. As Figure 15-7 shows, creating a DCL is pretty much the same as creating any other list or library. One difference you may notice is that you have the option of versioning in this library. As your needs progress over time, you will likely find yourself either updating a data source you manage or finding that the owner of an external data source has modified that source. You can keep track of which version of the data source you have used at different points in time to identify the various data elements available in each version.

Imagine that in the original version of an external data source, you found regular access to a particular data element useful. As your needs changed, the data source was updated to not include that element. Then, suppose that a condition arises where you need to access that element. You can find it again by accessing the version of the data source that contains it.

Figure 15-7. Creating a Data Connection Library (DCL)

The MOSS 2007 Business Data Catalog (BDC) part makes it easy to search your backend data sources. Use its search function to discover and integrate external data into SharePoint Web Parts. The search aspect of BDC transverses such sources as remote file shares, web sites, Microsoft Exchange Servers, Lotus Notes, and others, allowing you to locate the desired information and view or modify it from inside SharePoint.

Filtering information in libraries and lists lets colleagues and customers view and interact with only the most relevant data in a Web Part. For example, your department may provide support for Software Applications, Languages, and Libraries/APIs, but you may want to see only a list of supported applications. Filtering lets you select the Applications option on the supported software list, so that only applications such as COM+ and SQL Server appear. You can also store all of your sales data on a single list and filter it by region, year, or product.

MOSS 2007 provides 10 distinct Filter Web Parts. For example, the Current User Filter Web Part filters information based on the user’s logon profile. This is a great asset to security and accessibility, presenting only data you want the user to see and hiding any data the user will not find useful.

KPIs are visual indicators that show how much progress has been made in achieving one or more goals. Using KPIs to measure task metrics allows teams, managers, and organizations to quickly assess whether tasks are hitting their milestones and which parts of a task are ahead, behind, or on time.

For instance, if you are developing a new application for a customer, there may be different engineers working on the interface, libraries, and other project elements. KPIs can be used to measure the performance of each engineer on the project relative to the project goals (e.g., how many modules have been built, how many test cases have passed, etc.). This way, all of the stakeholders—including the engineers on the project—know to what degree the performance indicators align with the performance targets that were agreed upon before the project started.

You can use one of four KPI types:

Figure 15-8 shows several indicators on a KPI list, including goals and status.

Figure 15-8. Standard KPI list

Performance Management is the process of creating and maintaining a high fidelity between employee performance and departmental or company goals. MOSS 2007 is only part of the Microsoft BI solution, and many other tools play vital roles in concert with SharePoint. Other applications and third-party tools that play a role in BI performance management include Business Scorecard Manager, ProClarity, and Microsoft Office PerformancePoint Server.

As you can see, SharePoint 2007 includes some of the features required to implement successful performance management, including Dashboards and KPIs that gather data from SharePoint lists.

The Performance Management part of BI uses various integrated platforms to satisfy managing productivity, accountability, and goals. The following Microsoft and ProClarity technologies fully integrate to provide analysis and reporting on performance management data:

SharePoint does its part by providing multiple data views—in the form of Web Parts, Dashboards, and other display components—to offer secure, web-based displays of analyzed information that pull information from databases, spreadsheets, and many other sources.

Microsoft’s Data Analysis and Reporting abilities combine Excel, SharePoint Excel Services, numerous other SharePoint elements, and ProClarity Analytics to provide cutting-edge collaborative performance analysis and reporting for the enterprise.

Document Management

SharePoint Document Libraries and Document Workspaces provide excellent tools for managing text-formatted data and presenting it in a web-accessible interface. Not only can you control the organization and presentation of documents, but you can also manage access rights so that only authorized users can view or modify a document. If you aren’t sure which document contains the information you need, use SharePoint’s search feature to quickly locate it. A SharePoint Document Center is shown in Figure 15-10.

Tip

See Chapter 9 and Chapter 17 for more information on document management.

Figure 15-10. SharePoint Document Center

The Microsoft SQL Server 2005 data warehousing solution is the core source for BI information. SQL Server has a proven track record as a reliable, scalable, and productive database platform. Analysis and Reporting are at the heart of SQL Server’s use as a BI tool:

See Figure 15-11 for an example of Business Intelligence Development Studio.

One of the other data sources you can use with SharePoint to provide BI data is Microsoft Office Access 2007. If you want to connect to and modify data stored in Access 2007, you can use a special web browser plug-in, and then use the Access Web Datasheet. This functions similarly to Excel Web Services in that you can view Access 2007 data in datasheet view from SharePoint. Office 2007 must be installed on the client computer to view either Excel or Access data. You can import or export data between SharePoint and Access and even move an entire Access database into SharePoint. You can also edit SharePoint data inside of Access. You can see an open Access file in Figure 15-12.

Figure 15-11. Business Intelligence Developer Studio

Exporting Data from SharePoint to Access

The door swings both ways: there is also a method of taking SharePoint list data and exporting it into an Access database. You can perform the following exercise only if Access 2007 is installed on your client computer:

1. At the SharePoint site, open the list, either by clicking the list’s name in Quick Launch or by clicking View All Site Content and then clicking on the list’s name.

2. Click Actions, and select Open with Microsoft Access from the list.

3. In the Name field, type in the name of an existing database, or if you want to create a brand new Access database, type in the new name.

4. Choose one of the following options:

   • “Import a copy of the list” will execute an import action similar to the previous exercise.

   • “Create a linked table” will link the table to the data in the SharePoint list.

   Tip

   Creating a linked table means that every time you change the data in the SharePoint list, the changes are automatically reflected in Access.

5. Click OK to open Access to the database you specified in step 3.

6. When Access opens on your local computer, you may be required to authenticate to the SharePoint site again. As you can see in Figure 15-13, you receive similar results when you export data from a SharePoint list into Excel.

7. When the database is created and modified, verify that the User Information List was also created.

Tip

SharePoint automatically copies the User Information Table to Access, listing the members of the SharePoint site. This is useful when you want to preserve list column information such as Created By and Modified By.

Once the process is completed, the list items will be displayed in Datasheet view in either Excel or Access, depending on the target application (Figure 15-14).

Figure 15-13. Importing list data into Excel

Moving an Access Database to SharePoint

In order to view and modify an Access database, your client computer must have Microsoft Office Access installed. If this application isn’t available, you will not be able to open Access files. You may want SharePoint users who do not have Access on their computers to be able to use your database information. Moving the database to SharePoint will allow a much larger audience to access it.

Figure 15-14. List data shown in Excel

Moving a database to SharePoint is a similar process to moving an Access database to SQL, and is done for the same reason: to “upsize” the database to a larger audience. However, upsizing a database to SharePoint creates a list for every table in the database.

Warning

Please keep this fact in mind when deciding to move a database to SharePoint. If the database contains an unusually large number of tables, you may have trouble managing such a large number of lists.

When the process is completed, the database tables are backed up in Access and the originals replaced with links to the SharePoint lists. From that point, all of the information in the database as viewed from Access resides only in SharePoint, although it is possible to restore the Access database from the backup:

1. In Access, open the desired database.

2. Click Choose External Data, click SharePoint Lists, and select “Move to SharePoint.”

3. When the “Move to SharePoint” wizard launches, type or paste in the URL of the SharePoint site in the “What SharePoint site do you want to use?” field.

4. If you want users to be able to see the data from inside Access, click the “Save a copy of my database to the SharePoint site” checkbox.

5. To upload the copy of your database to SharePoint, click Browse and navigate to the document library in SharePoint where the copy is to be stored.

   Tip

   If you didn’t create a document library previously, choose the Shared Documents section.

6. Click Next. You may have to authenticate to the SharePoint site.

7. After the database transfer is completed, in the confirmation window that appears, click the Show Details checkbox if you want to see what changes Access made during the process.

8. Click Finish.