I’m back in Wellington tonight in order to attend the SharePoint User Group Meeting (hosted by Microsoft NZ) on the topic of Business Forms and Business Intelligence in SharePoint 2007. Chris Johnson (“CJ”) from Microsoft NZ is presenting; his days at Microsoft NZ are limited, however, as he’s about to head up to Redmond to head the Windows SharePoint Services team. BI and Forms are the last two sections of the MOSS pie to be discussed at the Wellington SharePoint User Group. There’s about 40-50 people.

Where We’ve Been
CJ pulled out a very old brochure about Microsoft Office … which included Word, PowerPoint, Excel and Microsoft Mail. A key part of that brochure was “How to share information between programs”, eg, “copy-and-paste”. Wow. It also had a “Common Terms” section, such as “toolbar” and “icons”. We have come a long way.

Excel Services 2007
Excel Services 2007 is a new server code based built on SharePoint … a server-side rendering and calculation engine for Excel. It provides browser-based access to live, interactive server-based spreadsheets, along with Web service access to server-side Excel calculations. The idea is to take the Excel models and frameworks that are built and make them more shareable than attaching the current version to email. The Web component provides a full-fidelity rendering experience, for working with data, doing charting, drill through pivot tables, and more. It is interactive, eg, users can enter appropriate parameters and see the spreadsheet recalculate. There’s also a developer story with Excel Services, to permit pulling and pushing information programmatically to Excel spreadsheets. In this edition, VBA and macros don’t work, and full add-ins don’t work. Embedded objects (eg, images and Office Art) also don’t work, as well as no refresh of query tables, SharePoint lists, and XML maps. However, “UDFs”, or “user-defined functions” are supported; these must be written as methods in .NET classes, and are callable from Excel formulas.

Office 2007 Report Center
The hope is that SharePoint 2007 will become the hub for business intelligence in the organization. The “Report Center” site template in MOSS provides an out-of-the-box BI portal experience, along with dashboards, KPIs, and report libraries. It integrates with other parts of the MOSS pie too, eg, Portal, Collaboration, ECM and Workflow. The page can accumulate information from various sources and present it in a seamless interface for end users.

When first creating a Report Center site, it includes teaching information to direct the owner how to set up the center for their requirements. CJ stepped us through creating a spreadsheet to add to the Report Center. He set up a standard spreadsheet … a couple of parameters, along with some calculated cells. He named the cells so he can refer to them in the Web rendition. When publishing it to the Report Center, the user can tick the various named items that they want to make available to Excel Services. Parameters must also be made available / published to Excel Services. CJ then publishes the Excel spreadsheet to the Report Center site. Once published, the browser interface gives a clickable interface; there’s a drop-down box on the top right hand side through which the user can select named items that they want to view. Parameters are shown in an expanded side bar on the right hand side too, and users can change the parameters and see the impact of that on the data.

Chris was in full flight on Excel Services when the pizza arrived. So everyone filed out of the room for dinner for a quick 10 minute feed fest.

One possible next step is to link the published information to a page in MOSS with specific WebParts to show specific data. Eg, CJ added an “Excel Web Access” web part; he then pointed it at a workbook, and a named item therein. The page then shows that slice of the Excel model. The owner of the site can also turn off specific items of functionality, eg, the parameters, the titles, interactivity, and more. It’s a tick or untick option.

A couple of points:
1. The MOSS Administrator must set up trusted locations for Excel workbooks. Excel Services will only work with these trusted locations.
2. There’s also a “Data Connections” construct for introducing a level of indirection between the specific servers on which specific workbooks are located. So workbooks can be shifted around, and Data Connection definitions can be updated to tell the system where the workbooks are now located. Data Connections lives in a specific library.

The KPI idea in the Report Center displays information in a graphical way. The data can come from a SharePoint list, an Excel workbook, Reporting Services, or manual entry.

The final piece of the BI slice that CJ spoke to was the Business Data Catalog, for connecting to line of business data in other systems for exposure in SharePoint. Users can then search that information from SharePoint, or it can be surfaced into Web parts for display to users, or the data can be used in lists and libraries in SharePoint. Eg, if you have a customer list in an SQL database somewhere in the enterprise, then the Business Data Catalog can associate specific items or documents in SharePoint with the authoritative list in the SQL database. Web parts within the Business Data Catalog include Business Data Actions, Business Data Item, Business Data Item Builder, Business Data List, and business Data Related List, among others.

Business Forms: InfoPath Integration
InfoPath 2003 was a rich-client desktop application for the creation of electronic forms, complete with drag-and-drop of fields for constructing forms, saving forms using XML, validation, and more. Forms could be saved to multiple places, but everyone needed to have an InfoPath client for creating or working with forms. With InfoPath 2007, all of the previous ideas are rendered within a browser; however, forms must still be built using an InfoPath Client. There are two ways to deploy InfoPath form templates: a simple wizard-based way for end users, and an advanced deployment option for administrators. After the form is uploaded, it is activated in certain site collections. InfoPath integrates with WSS v3 in a couple of ways, including a document library (multiple forms per library), a form template library (for administrators), and for Content Type templates (to simplify document library creation).

CJ showed an InfoPath form. During the design process, the designer has quite a granular level of control over what users get to see in browser renditions of the form, eg, print, submit. There’s more too, but CJ didn’t have time to demonstrate too much. Prior to deployment, there’s a Design Checker to ensure that everything is AOK. If there are design issues, the Checker will notify the designer of any potential problems. Eg, ActiveX controls that won’t work on the Web version of the InfoPath form. When checking the form in, the administrator can choose to Verify the design, and can also specify what to do if the new form is a new version of an existing form, eg, overwrite or not. The form is then published to a site collection. Then a new forms library is created in the appropriate location, and the form is linked through. The system is able to detect whether the user has InfoPath installed or not, and if yes, open it in the InfoPath application if available. There is an overwrite option for the Forms library, however, to always open the form as a web page.

Michael’s Thoughts
Given Microsoft’s explicit strategy of making SharePoint the essential central part of its Office system, it is very clear that the business intelligence functions in MOSS 2007 is a key part of the outworking of this strategy. And with multiple parts of the MOSS pie being attractive to different parts of the organization’s user, some people will find that the collaboration functionality of MOSS becomes key and central for them; others will find that other parts of the pie become the key attractor for them. As people get used to more of the pie’s functionality, and start embracing it within their work, they’ll find more and more strands tying them to SharePoint for years to come.

The Business Data Catalog is very, very cool; authoritative, non-duplicated lists are so important in organizations, and SharePoint’s ability to integrate with many other systems for such authoritative data is very neat. It looks like there’s a lot of controls available, but CJ didn’t demonstrate too many of those; he hinted at them.

Update: Chan has photos