When developing OBAs, there are a range of opportunities that can be used to integrate business intelligence into your solutions. These options range from automated data connection string binding to more sophisticated Web service consumption. With each of the options, you’ll have varying degrees of control and reach on your business intelligence logic. In this section, we’ll cover the integration of business intelligence into an OBA either through a) using native Office functionality or b) using VSTO to develop custom client-side business intelligence solutions.
Microsoft Excel 2007 has native functionality that enables you to directly connect to different data sources and then manipulate the presentation of that data. More specifically, Excel enables you to build a connection (that is, information such as type of connection, server name, table name, and so on, that is needed to identify a data source) and to connect to that data source so you can import that data. You can find this functionality on the Data tab within Excel 2007. For example, Figure 3-3 illustrates some of the possible data sources you can directly connect to your Excel spreadsheet, including Access, Web, and text.
Essentially, this new functionality creates a connection string for you and then saves that connection string in association with the specific Excel document. Thus, connection information is stored either inside (default mode) of Excel or in a separate Office data connection file (that is, a .odc file). Excel uses these files when a specific Excel document is opened to establish the connection to the connected data source.
From the From Other Sources option, you can also create a connection to a SQL Server database. Since I predominantly use SQL Server in my application development, I chose to create a connection to my Excel 2007 spreadsheet using a budget database—a database I use a lot when developing demo projects. To make this connection, select From Other Sources, click From SQL Server (see Figure 3-4), and then complete the Data Connection Wizard.
The Data Connection Wizard prompts the user for the database server name and asks for a choice on credentials. Provide a local server name and then choose to use Windows Authentication.
After you’ve finished, click Next to take you to the next dialog box in the wizard. The next dialog box in the Data Connection Wizard asks you to select a database within the SQL Server you selected and also enables you to connect to a specific table, as shown in Figure 3-5. In the following example, I have selected the Budget database and then selected a table I have in this sample database called the BudgetSummary.
After you’ve completed this step, click Next and the wizard will prompt you with the final dialog box, Save the Data Connection File and Finish, shown in Figure 3-6.
In this dialog box, you can change the name of the connection (.odc) file, add a description, edit the display name, add search keywords, and set the Excel Services authentication services on the file as well. After you’ve completed these steps, click Finish. Your database connection has been created.
What results is the auto-population of your Excel spreadsheet with the data from the table in the SQL Server that you designated in the wizard. With the data in the spreadsheet, you can now alter the presentation of the data (for example, change table styles, create a pivot table, and so on) or use the auto-added filters to manipulate the views on the data. See Figure 3-7 for the final table with data from the budget table.
Though this may not be a business intelligence solution that employs, for example, Online Analytical Processing (OLAP) and more complex reporting and analysis, it is a fairly simple way to connect, present, and visualize data from a variety of sources in a quick and efficient way through the Microsoft Excel 2007 functionality.
Further, you can leverage many of the native formatting features of Excel for simple reporting and presentation of data. For example, Figure 3-8 illustrates some of the charting and formatting capabilities within Excel—namely, the conditional formatting, which allows you to shade based on cell value, and the 3D pie chart. Not only can you do this through the Excel 2007 menu, but you can also build this functionality into your reporting programmatically.
The native Excel 2007 functionality offers you an extensive list of features, but there are limitations to this, so if you’re looking for something with more flexibility, extensibility, complex filtering capabilities, connectivity to Web services, and so on, then there is another powerful option that you can employ to integrate business intelligence into your Office Business Application: Visual Studio Tools for Office.
Visual Studio Tools for Office (VSTO) is a powerful way to build customizations for a number of Office applications, including Excel, Word, Outlook, and PowerPoint. VSTO 3.0 ships with Visual Studio 2008 Professional Edition and enables developers to, for example, extend the Office Fluent Ribbon (using Visual Ribbon Designer), create custom task panes that can host managed and Windows Presentation Foundation (WPF) controls, customize Outlook form regions, integrate VBA macros with VSTO managed code, and much more. In terms of Office customizations, VSTO 3.0 is one of the key technologies professional developers should have in their toolkits.
With regard to business intelligence, you can use VSTO to build and integrate customized solutions into your OBA. In this chapter, we’ll discuss how you can customize the user interface of Excel to provide rich database and reporting capabilities, show you how you can use Language Integrated Query (LINQ) to integrate data into the customized user interface components, and finally show you how you can call Web services to consume line-of-business business data—or other data provided through Web services. All of these will again work toward the goal of more closely integrating information workers with the business data they need to do their job. Some of them require some development, whereas others will require more configuration to native Excel 2007 features.