Office Business Application for Customer Support Management

Summary: Learn to build custom applications that use Microsoft Office SharePoint Server 2007, Microsoft Office InfoPath 2007, SQL Server Analysis Services, SQL Server Integration Services, and the Business Data Catalog. (28 printed pages)

Joel Krist, Akona Systems

Karthik Ravindran, Microsoft Corporation

February 2008

Applies to: 2007 Microsoft Office System, Microsoft Office SharePoint Server 2007, Microsoft Office InfoPath 2007, Microsoft Visual Studio 2008, Microsoft Visual Studio 2005, and Microsoft SQL Server 2005.

Download sample files: 2007 Office System Business Application: Customer Support Management.

Contents

  • Customer Service Request Solution

  • Using the Service Request InfoPath Form Template

  • Using the Service Request Web Service

  • Using the SQL Server 2005 OLTP and OLAP Databases

  • Using the Litware Internal Support Site

  • Using the Business Data Catalog

  • Using the SQL Server Analysis Services Cube

  • Guidelines for Modeling Business Data

  • Design Decisions

  • Enabling Extensibility

  • Conclusion

  • Additional Resources

Customer Service Request Solution

Providing timely, accurate responses to customer service requests is a key factor in the success of any customer support organization. The degree to which an organization's Customer Service Request system facilitates the process of monitoring and responding to service requests has a direct relationship to customer satisfaction.

Some common challenges organizations face when implementing a Customer Service Request solution are:

  • Context switching. Many customer service request solutions require support personnel to switch between applications to track and work with customer requests. This context switching creates the possibility of missed service requests and reduces employee productivity.

  • Deployment and maintenance costs. The cost to the IT department of deploying and maintaining a customer service request system on the desktop of support personnel can be extremely high.

  • Ease of access to customer service request line-of-business (LOB) data. Managers must often switch between applications to monitor service request activity.

This article presents an example of an Office Business Application built on the Microsoft Office system that addresses typical collaboration and business insight requirements of a Customer Service Request application. The solution allows end users to live in familiar Microsoft Office applications and use their natural workflows while being connected to line-of-business systems. The Office Business Application makes use of the following key Microsoft products and technologies:

  • Microsoft Office InfoPath 2007

  • Microsoft Office Outlook 2007

  • Microsoft Office SharePoint Server 2007 including

    • Business Data Catalog

    • Microsoft Office Excel Services and Excel Web Access

    • InfoPath Forms Services

  • Microsoft Visual Studio 2008

  • Microsoft Visual Studio 2005 Extensions for Windows SharePoint Services 3.0

  • Microsoft SQL Server 2005 database, Analysis Services, and Integration Services

Using these products and technologies deliver the following benefits:

  • Rapid application development with familiar tools and reusable components. You can use the familiar Visual Studio development environment to develop the different solution components. The solution uses a single InfoPath form to allow customers to submit requests and support personnel to manage responses.

  • Simplified application deployment and maintenance. The solution uses server-based services and removes the need to deploy anything to the desktop.

  • No context switching. Using Microsoft Office Outlook 2007 as the user interface for working with service requests allows support personnel to stay in the familiar applications they use.

  • Easy access to LOB data. LOB data from the customer service request system appears in Office Outlook as dashboards. This integration removes the need for support personnel to switch to a different system to monitor active service requests.

The combination of rapid application development, ease of deployment, and natural workflow creates an attractive solution to developers, IT staff, and end users.

Scenario Overview

In the scenario supported by the solution, a customer connects to the Litware extranet portal and uses an Office InfoPath 2007 form hosted by InfoPath Forms Services to submit a service request.

Figure 1. Service request submission form hosted by InfoPath forms services

Service request submission form

The form submission uses a Web service to store the service request data in a SQL Server 2005 OLTP database. Then, it creates an associated item in a task list located on the Litware internal support site based on Office SharePoint Server 2007. Support engineers use Office Outlook 2007 as the user interface for working with service requests that reside in the task list on the SharePoint site.

Figure 2. Service requests in Outlook

Service requests in Outlook

The service request InfoPath form renders directly in the Outlook user interface, and allows support engineers to perform the typical service request-related tasks such as assignment, escalation, adding work logs, and closing.

Figure 3. Performing service request tasks in Outlook

Performing service request tasks in Outlook

In addition to working with service requests directly from within Outlook, support engineers and their managers can use the dashboards in Outlook to access the service request data stored by the system. The Business Data Catalog and SQL Server 2005 Analysis Services provide access to the data. Support personnel can view a customer's profile and service request history, view the current and historic data on the service requests handled by support engineers, and perform keyword searches across service request logs.

Figure 4. Customer Support Dashboard in Outlook

Customer Support Dashboard in Outlook

Figure 5. Customer Dashboard in Outlook

Customer Dashboard in Outlook

Figure 6. Manager Dashboard in Outlook

Manager Dashboard in Outlook

Using the Service Request InfoPath Form Template

The Service Request InfoPath form template is a browser-enabled form template hosted by InfoPath Forms Services. By hosting the form in the browser, customers do not need to install Office InfoPath 2007. It also simplifies deployment.

The Service Request form has four views:

  • A Customer view. Used by customers to submit service requests on the extranet. This view uses a Web Part, named XmlFormViewWebPart, to host the XmlFormView Web control to display the form template.

  • A Support view and associated Create Log view. Used by support personnel to perform service request related tasks such as assigning, modifying, escalating, closing, and adding work logs to service requests.

  • A Debug view. Used to view debug messages generated by the form code during processing.

Implementation Details

The Service Request form template uses managed code to provide its core functionality. It uses the methods exposed by the Service Request Web service to submit and retrieve service request data to and from the back-end database. For more information, see Using the Service Request Web Service.

No Service Request form instances are stored when processing service requests. Because the service request data is very structured in nature, it is stored in the back-end SQL database. Some service request metadata is also stored in task lists. At load time, the form determines which view to display based on command line parameters. If there are no parameters, the form creates a service request and displays the Customer view. If there are command line parameters, it displays the Support view and uses the parameters to load the required data from the back-end database by using the Web service.

The form receives command line parameters through a custom action on the task list item. For more information, see Internal Support Site.

Deploying the Solution

Because the Service Request form template uses managed code, an administrator must deploy it to InfoPath Forms Services. Deploying a browser-enabled form template that requires administrator approval for use with InfoPath Forms Services is a three-step process: form template verification, template uploading, and template activation.

For more information, see Deploy administrator-approved form templates (Office SharePoint Server).

Key Sample Files

The following table lists key template files for the service request form.

Table 1. Key template files

File

Description

ServiceRequest.xsn

The form template, located in the InfoPath\Forms\ServiceRequest folder under the root Customer Service Office Business Application folder.

FormCode.cs

The managed code file for the form template. The Visual Studio Tools for Applications solution files for the managed code are located in the InfoPath\Forms\ServiceRequest\Code\ServiceRequest folder under the root Customer Service Office Business Application folder.

XmlFormViewWebPart.cs

The managed code file for the Web Part used to host the XmlFormView Web control. The Visual Studio 2008 solution files for the Web Part are located in the InfoPath\Code\XmlFormViewWebPart folder under the root Customer Service Office Business Application folder.

Using the Service Request Web Service

The InfoPath form for the service request uses the Service Request Web service to access the SQL Server database. For more information, see SQL Server 2005 OLTP Database. The Web service exposes methods and types that support submitting and retrieving service request related data.

Implementation Details

The Service Request Web service exposes the following methods:

Table 2. Service Request Web service methods

Method

Description

SubmitServiceRequest

Creates a service request in the database. Also creates an associated task list item in the Tier 1 Service Request task list. Accepts Service Request form data as an XML string and deserializes it into an instance of the ServiceRequestFields class.

UpdateServiceRequest

Updates the properties of an existing service request. Accepts service request form data as an XML string and deserializes it into an instance of the ServiceRequestFields class.

GetServiceRequestById

Returns an instance of the ServiceRequestData class (see Table 3. Service Request Web service types) for the specified service request ID.

AssignServiceRequest

Sets the owner of the specified service request.

EscalateServiceRequest

Escalates the specified service request to tier 2 support.

CloseServiceRequest

Closes the specified service request as either resolved or not resolved.

CreateLogEntry

Creates a log entry for the specified service request.

GetServiceRequestLogEntries

Returns an array of LogEntry objects, (see Table 3. Service Request Web service types) for specified service request.

GetProductCategories

Returns the list of currently defined product categories.

GetProductsByCategory

Returns a list of products filtered by product category.

GetIssueSeverities

Returns the list of currently-defined issue severities.

GetIssueTypes

Returns the list of currently-defined issue types.

GetContactsByCustomerEmail

Returns a list of all contacts for the customer who has a contact with the specified e-mail.

GetCustomerFromContactEmail

Returns the information for the customer with the contact specified by the supplied e-mail address.

GetContactData

Returns the data for the specified contact.

GetEmployeeData

Returns the data for the specified employee.

GetEmployees

Returns all currently defined employees.

The Service Request Web service exposes the following properties:

Table 3. Service Request Web service types

Properties

Description

ServiceRequestData

Returns the data for a service request using the GetServicerequestById method.

LogEntry

Represents the work log entry for a service request.

EmployeeData

Represents the data for an employee.

The ServiceRequestFields Class

The Service Request Web service makes use of the ServiceRequestFields class. The ServiceRequestFields class allows the SubmitServiceRequest and UpdateServiceRequest methods to receive a Service Request form instance as an XML string and then deserialize the string into an instance of the ServiceRequestFields class. You can then use the members of the ServiceRequestFields instance to access the form field values in a type safe manner without using XPath navigation.

You can use the XML Schema Definition Tool (xsd.exe) to generate the ServiceRequestFields class by running the tool against the Service Request form schema.

To generate the ServiceRequestFields class file

  1. In InfoPath, in design mode, open the Service Request form template.

  2. On the File menu, click Save as Source Files.

  3. Browse to the location where you want to save the form source files, and click OK. InfoPath saves a collection of form source files, including the schema file, to the specified location. The form schema file is always named myschema.xsd.

  4. Close the form in InfoPath. To do so, on the File menu, click Close.

  5. Use xsd.exe to generate a class file from the form schema. To do so:

    1. Open a Visual Studio Command Prompt window

    2. Navigate to the location of the myschema.xsd file, and then run the following command:

      xsd myschema.xsd /classes /l:CS
      
  6. This command generates a new class file based on the form schema. The file is named the same as the schema file, that is, myschema.cs, and is placed in the current directory.

For more information about using xsd.exe, see XML Schema Definition Tool (Xsd.exe).

Deploying the Service Request Web Service

To use the Service Request Web service, create a Web site in Internet Information Services (IIS) Manager and configure the site to use the folder containing the Web service project files.

The Service Request form currently binds directly to the Web service rather than by using a data connection in a data connection library on the SharePoint site. The reference implementation of the Customer Service Request solution binds to the Web service on the local computer through TCP port 2827. If the Web service is configured to use a different port, you need to update the Web references stored by the form to match the new setting. An alternative is to modify the form to use a data connection file for the connection settings. For more information, see Lab 3: Integrating InfoPath 2007 with the Data Connection Library.

Security Considerations

The Service Request Web service does not currently support caller authentication. It is open to all users who call the service. Normally, you want to ensure the Web service allow only authorized customers to access its methods. For information about creating a secure Web service, see Windows Communication Foundation.

Key Sample Files

Table 4. Key Web service files

File

Description

SRXWebService.asmx.cs

The managed code file for the Service Request Web service. The Visual Studio files for the Web service are located in the InfoPath\Code\SRXWebService\SRXWebService folder under the root Customer Service Office Business Application folder.

ServiceRequest.cs

Provides the implementation of the ServiceRequestFields class. The Web service uses this class to access the data in a Service Request form instance programmatically. It is generated by the XSD.exe tool based on the Service Request form schema. The file is located in the same folder as the other Service Request Web service files.

Using the SQL Server 2005 OLTP and OLAP Databases

The Customer Service Request solution makes use of two SQL Server 2005 databases – an OLTP database named CustomerServiceOBA and an OLAP database named CustomerServiceOBADW. The CustomerServiceOBA database stores the live service request data. The CustomerServiceOBADW database is the data warehouse for the solution. It contains the data against which the SQL Server Analysis Service cube performs its processing.

CustomerServiceOBA OLTP Database Overview

The CustomerServiceOBA OLTP database defines the following enumerations:

Table 5. OLTP database enumerations

Enumeration

Values

EnumCompanySize

Small, Medium, Large

EnumCustomerRating

Platinum, Gold, Silver, Bronze

EnumIndustry

Manufacturing, Retail, Health Care, Financial Services, Public Sector

EnumSRXIssueType

Problem, How, Bug, Information

EnumSRXLogType

Phone, Email, Research, Customer

EnumSRXSeverity

Sev-A, Sev-B, Sev-C

EnumSRXStatus

Active (Unassigned), Active (Assigned)
Closed (Resolved), Closed (Unresolved)

The following figure shows the relational model of the CustomerServiceOBA OLTP database.

Figure 7. CustomerServiceOBA relational model

CustomerServiceOBA relational model

CustomerServiceOBADW OLAP Database Overview

The CustomerServiceOBADW database has the following dimensional model.

Figure 8. CustomerServiceOBADW dimensional model

CustomerServiceODADW dimensional model

Populating the CustomerServiceOBADW Database

The Customer Service Request solution uses the Extract, Transform, and Load (ETL) package from SQL Server Integration Services to pull data from the CustomerServiceOBA OLTP database into the CustomerServiceOBADW OLAP database where it can be processed using a SQL Server Analysis Services cube. The Visual Studio-based SQL Server Business Intelligence Development Studio was used to create the package.

The ETL package that extracts service request data from the OLTP database and load it into the OLAP database is not complex. It drops and then recreates the tables in the destination database. Then, it selects data from the source database, performing data normalization along with minimal transformations of date-related values. Then, it saves the data to the destination database. The following figure shows the ETL package in the designer in SQL Server Business Intelligence Development Studio.

Figure 9. ETL package in SQL Server Business Intelligence Development Studio

ETL package

In Solution Explorer, right-click the package name and select Execute Package to execute the ETL package from within the SQL Server Business Intelligence Development Studio. Or, you can execute it by using the dtexec utility from the command line. **Note:   **A shortcut to a batch file that uses the dtexec utility to execute ETL package is on the local administrator desktop. Running the batch file loads the OLAP database with the latest version of the service request data. After loading the service request data, you can run the SQL Server Analysis Services cube against the latest data.

Key Sample Files

Table 6. Key Database-Related Files

File

Description

CustomerServiceOBA.mdf

CustomerServiceOBA_log.ldf

The SQL Server primary data file and database transaction log file for the CustomerServiceOBA OLTP database. Located in the Database folder under the root Customer Service OBA folder.

CustomerServiceOBADW.mdf

CustomerServiceOBADW_log.ldf

The SQL Server primary data file and database transaction log file for the CustomerServiceOBADW OLAP database. Located in the Database folder under the root Customer Service Office Business Application folder.

"CSR OBA DW Package.dtsx"

The ETL package that loads the OLAP database with the latest data. Located along with the package solution files in the BI\Customer Service OBA ETL Package\ Customer Service OBA ETL Package folder under the root Customer Service Office Business Application folder.

Using the Litware Internal Support Site

The Litware internal support site is based on the Team Site template in Office SharePoint Server. It provides the task lists and dashboards used by support personnel to handle service requests. The internal support site uses a custom content type with an associated custom action and list definitions. The content type and list definitions were created with Visual Studio 2005 with Extensions for Windows SharePoint Services.

SRX Task List Content Type Overview

The SRX Task List content type is based on the default task content type in Office SharePoint Server 2007 and introduces two additional fields: SRXID and Severity. The SRXID field stores a service request's unique ID and the Severity column stores the request's severity level. There is a one-to-one mapping between active service requests in the database and the tasks in the service request task lists. The SRXID column provides the link between a task list item and the associated service request data in the database.

The following XML describes the SRX task list content type including its ID, name, group, description, and references to the fields, or columns, associated with the content type.

<?xml version="1.0" encoding="utf-8"?>
<Elements xmlns="http://schemas.microsoft.com/sharepoint/">
  <!-- _filecategory="ContentType" _filetype="Schema" _filename="contenttype.xml" _uniqueid="90e26b3f-243d-4ede-942e-3590945a899f" -->
  <ContentType ID="0x010800582915bd8be34349b2b8657d7356f144"
      Name="SRX Task List Content Type"
      Group="Service Request Content Types"
      Description="Service Request Task List Content Type"
      Version="0">
    <FieldRefs>
      <FieldRef ID="{22cfeaac-47f7-4486-961f-4214d6b97a31}" Name="SRXID" />
     <FieldRef ID="{F3917D1E-44C8-4579-90A3-733C64C7EADB}" Name="Severity" />
   </FieldRefs>
  </ContentType>
</Elements>

The following XML describes the fields, or columns, associated with the content type.

<?xml version="1.0" encoding="utf-8"?>
<Elements xmlns="http://schemas.microsoft.com/sharepoint/">
  <!-- _filecategory="ContentType" _filetype="File" _filename="fields.xml" _uniqueid="22cfeaac-47f7-4486-961f-4214d6b97a31" -->
   <Field
       ID="{22cfeaac-47f7-4486-961f-4214d6b97a31}"
      Type="Counter"
      Name="SRXID"
      DisplayName="SRX ID"
      StaticName="SRXID"
      Hidden="FALSE" 
      Required="TRUE" 
      Sealed="TRUE"
    />
   <Field
      ID="{F3917D1E-44C8-4579-90A3-733C64C7EADB}"
      Type="Choice"
      Name="Severity"
      DisplayName="Severity"
      StaticName="Severity"
      Hidden="FALSE" 
      Required="TRUE" 
      Sealed="TRUE"
   >
      <CHOICES>
         <CHOICE>Sev-A</CHOICE>
         <CHOICE>Sev-B</CHOICE>
         <CHOICE>Sev-C</CHOICE>
      </CHOICES>
   </Field>
</Elements>

For more information about the content type-related attributes used in the content type markup ,see Content Type Definition Schema. For more information about the field-related attributes, see Field Definition Schema.

Defining the SRX Task Lists

The internal support site has two service request related task lists—Tier 1 Service Requests and Tier 2 Service Requests. Both lists are based on the SRX task list content type described previously.

The following XML creates an instance of the Tier 1 Service Request task list.

<?xml version="1.0" encoding="utf-8"?>
<!-- _filecategory="ListInstance" _filetype="File" _filename="instance.xml" _uniqueid="73434f86-046f-4546-a54e-b3d96c6b9aa4" -->
<Elements xmlns="http://schemas.microsoft.com/sharepoint/">
  <ListInstance
   Id="1"
   OnQuickLaunch="TRUE"
    FeatureId="00BFEA71-A83E-497E-9BA0-7A5C597D0107" 
    TemplateType="107" 
    Title="Tier 1 Service Requests" 
    Url="Lists/Tier1SRXTasks">
  </ListInstance>
</Elements>

The following XML creates an instance of the Tier 2 Service Request task list.

<?xml version="1.0" encoding="utf-8"?>
<!-- _filecategory="ListInstance" _filetype="File" _filename="instance.xml" _uniqueid="47ca155b-b1db-4c0e-86c3-9fe0dbf0fb2e" -->
<Elements xmlns="http://schemas.microsoft.com/sharepoint/">
  <ListInstance
   Id="1"
   OnQuickLaunch="TRUE"
    FeatureId="00BFEA71-A83E-497E-9BA0-7A5C597D0107" 
    TemplateType="107" 
    Title="Tier 2 Service Requests" 
    Url="Lists/Tier2SRXTasks">
  </ListInstance>
</Elements>

For more information about the ListInstance attributes, see ListInstance Element (List Instance).

Creating the Custom Action for the Edit Service Request

The SRX Task List content type has an associated custom action for the edit control block that allows the Service Request InfoPath form to be displayed from a task list item. The custom action for the Edit Service Request is registered with the SRX task list content type. The following XML shows the Edit Service Request custom action.

<Elements xmlns="http://schemas.microsoft.com/sharepoint/">
   <CustomAction
      Id="SRXTaskList.EditSRX"Location="EditControlBlock"
      Title="Edit Service Request..."RegistrationType="ContentType"
      RegistrationId="0x010800582915bd8be34349b2b8657d7356f144">
      <UrlAction Url="
        javascript:function process(){var site='{SiteUrl}';
        var list='{ListId}';
        var item='{ItemId}';
        window.location.href=site+'/_layouts/FormServer.aspx?XsnLocation=
        '+'~sitecollection'+'/formservertemplates/servicerequest.xsn?OpenIn=
        Browser&amp;Source='+window.location+'&amp;SiteUrl=
        '+site+'&amp;ListId='+list+'&amp;ItemId='+item;};process();"/>
   </CustomAction>
</Elements>

The Edit Service Request custom action uses a JavaScript function to indicate to InfoPath Forms Services to display the Service Request form template in the browser. The JavaScript function uses the {SiteUrl}, {ListId}, and {ItemId} placeholders. These placeholders are replaced at runtime by Office SharePoint Server 2007 with the actual site URL, list Id, and item Id values. The values are passed as parameters to the Service Request form. The Service Request form uses these parameters to get the value of the SRXID field of the task item so that it can load the associated service request data from the database.

Key Sample Files

Table 7. Key internal support site files

File

Description

SRXTaskListContentType.xml

SRXTaskListContentType.Fields.xml

The files containing the markup for the SRX task list content type and associated fields. Located in the SRXTaskLists\SRXTaskLists\SRXTaskListContentType folder under the root Customer Service Office Business Application folder.

Instance.xml

The file containing the markup for the Tier 1 Service Requests and Tier 2 Service Requests list instances. Located in the SRXTaskLists\SRXTaskLists\Tier1SRXTasks and SRXTaskLists\SRXTaskLists\Tier2SRXTasks folders under the root Customer Service Office Business Application folder.

Actions.xml

The file containing the markup for the Edit Service Request custom action. Located in the SRXTaskLists\SRXTaskLists\SRXTaskListECBActions folder under the root Customer Service Office Business Application folder.

Using the Business Data Catalog

The Business Data Catalog in Office SharePoint Server 2007 provides an easy way to integrate business data from LOB systems with Office SharePoint Server 2007. The Business Data Catalog uses a metadata model to provide access to the underlying data sources. You can use the metadata model to create an application definition file that models the entities in the data sources. The Business Data Catalog uses the application definition file to determine how to access and retrieve the requested data from the LOB system. After the application definition is registered with the Business Data Catalog, the data in the LOB system is available for use in Office SharePoint Server 2007 in the Web Parts, user profiles, and lists associated with the Business Data Catalog.

Creating the Application Definition File

The Customer Service Request solution uses the Business Data Catalog to display service request data from the SQL database in the support dashboards. The application definition file used by the Customer Service Request solution defines the following entities: customer, employee, service request, and service request log.

The next code example is part of the application definition file used by the solution. It shows the GetCustomers method defined for the customer entity. The <Property> element, with the Name attribute set to RdbCommandText, defines the SELECT statement to use to retrieve all customer entities. The GetCustomers method supports filtering with the FilterDescriptor object to allow the set of customers returned to be filtered by customer name.

<Method Name="GetCustomers">
  <Properties>
    <Property Name="RdbCommandText" Type="System.String">
      IF @CustomerName = '*'
      BEGIN
      SELECT
      dbo.Customer.CustomerId, dbo.Customer.OrganizationName,
      dbo.EnumCustomerRating.CustomerRating, dbo.EnumCompanySize.CompanySize,
      dbo.EnumIndustry.Industry, dbo.Customer.Address1, dbo.Customer.Address2,
      dbo.Customer.City, dbo.Customer.State, dbo.Customer.Zip, dbo.Customer.Phone
      FROM
      dbo.Customer
      INNER JOIN
      dbo.EnumCompanySize
      ON dbo.Customer.CompanySizeId = dbo.EnumCompanySize.CompanySizeID
      INNER JOIN
      dbo.EnumCustomerRating
      ON dbo.Customer.CustomerRatingId = dbo.EnumCustomerRating.CustomerRatingId
      INNER JOIN
      dbo.EnumIndustry
      ON dbo.Customer.IndustryId = dbo.EnumIndustry.IndustryId
      END
      ELSE
      BEGIN
      SELECT
      dbo.Customer.CustomerId, dbo.Customer.OrganizationName,
      dbo.EnumCustomerRating.CustomerRating, dbo.EnumCompanySize.CompanySize,
      dbo.EnumIndustry.Industry, dbo.Customer.Address1, dbo.Customer.Address2,
      dbo.Customer.City, dbo.Customer.State, dbo.Customer.Zip, dbo.Customer.Phone
      FROM
      dbo.Customer
      INNER JOIN
      dbo.EnumCompanySize
      ON dbo.Customer.CompanySizeId = dbo.EnumCompanySize.CompanySizeID
      INNER JOIN
      dbo.EnumCustomerRating
      ON dbo.Customer.CustomerRatingId = dbo.EnumCustomerRating.CustomerRatingId
      INNER JOIN
      dbo.EnumIndustry
      ON dbo.Customer.IndustryId = dbo.EnumIndustry.IndustryId
      WHERE dbo.Customer.OrganizationName LIKE @CustomerName
      END
    </Property>
    <Property Name="RdbCommandType" Type="System.Data.CommandType">Text
    </Property>
  </Properties>

  <FilterDescriptors>
    <FilterDescriptor Type="Wildcard" Name="CustomerName" 
      DefaultDisplayName="Customer Name" />
  </FilterDescriptors>

  <Parameters>
    <Parameter Direction="In" Name="@CustomerName">
      <TypeDescriptor TypeName="System.String" AssociatedFilter="CustomerName"
        Name="CustomerName" >
        <DefaultValues>
          <DefaultValue MethodInstanceName="CustomerFinderInstance"
            Type="System.String">%</DefaultValue>
        </DefaultValues>
      </TypeDescriptor>
    </Parameter>
    <Parameter Direction="Return" Name="Customer">
      <TypeDescriptor TypeName="System.Data.IDataReader, System.Data,
        Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
        IsCollection="true" Name="CustomerDataReader">
        <TypeDescriptors>
          <TypeDescriptor TypeName="System.Data.IDataRecord, System.Data,
            Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
            Name="CustomerDataRecord">
            <TypeDescriptors>
              <TypeDescriptor TypeName="System.Int32" Name="CustomerId"
                DefaultDisplayName="ID" IdentifierName="CustomerId" />
              <TypeDescriptor TypeName="System.String" Name="OrganizationName"
                DefaultDisplayName="Name" />
              <TypeDescriptor TypeName="System.String" Name="CustomerRating"
                DefaultDisplayName="Rating" />
              <TypeDescriptor TypeName="System.String" Name="CompanySize"
                DefaultDisplayName="Size" />
              <TypeDescriptor TypeName="System.String" Name="Industry" />
              <TypeDescriptor TypeName="System.String" Name="Address1" />
              <TypeDescriptor TypeName="System.String" Name="Address2" />
              <TypeDescriptor TypeName="System.String" Name="City" />
              <TypeDescriptor TypeName="System.String" Name="State" />
              <TypeDescriptor TypeName="System.String" Name="Zip" />
              <TypeDescriptor TypeName="System.String" Name="Phone" />
            </TypeDescriptors>
          </TypeDescriptor>
        </TypeDescriptors>
      </TypeDescriptor>
    </Parameter>
  </Parameters>
  <MethodInstances>
    <MethodInstance
      Name="CustomerFinderInstance"
      Type="Finder"
      ReturnParameterName="Customer"
      ReturnTypeDescriptorName="CustomerDataReader"
      ReturnTypeDescriptorLevel="0" />
  </MethodInstances>
</Method>

The Customer Service Request application definition file also defines an action for the Customer entity. An entity action allows you to perform custom processing for an entity instance. For more information about entity actions, see Action. The following markup shows the action defined for the customer entity that allows the customer dashboard to be displayed.

<Actions>
  <Action Position="1" IsOpenedInNewWindow="false"
Url="http://moss.litwareinc.com/Support/Dashboards/CustomerDashboard.aspx?
    CustomerId={0}&amp;MDX=[Customer].[Customer].%26[{0}]"ImageUrl=
    "/_layouts/1033/images/viewprof.gif" Name="View Dashboard">
    <ActionParameters>
      <ActionParameter Index="0" Name="CustomerId" />
    </ActionParameters>
  </Action>
</Actions>

The Url attribute of the Action element shown in the previous example specifies that the CustomerId of the Customer entity instance should be passed by using a query string to the customer dashboard page. The Url attribute also specifies a Multidimensional Expression (MDX) query string parameter that contains the CustomerId. The MDX query parameter is passed so that the Excel Web Access Web Part on the customer dashboard page can perform the correct query against the SQL Server Analysis Services cube to return the service request history for the customer. For more information, see MDX Query Fundamentals (MDX).

Importing the Application Definition File

After you create the application definition file, you must upload it to the Business Data Catalog before you can use it.

To upload the application definition file

  1. Connect to the SharePoint Central Administration site.

  2. Click the link for the default Shared Services Provider, usually named SharedServices1.

  3. On the Shared Services Administration page, in the Business Data Catalog section, click Import application definition.

  4. On the Import Application Definition page, browse for the application definition file and then click Import. The Business Data Catalog validates the application definition file and reports any errors.

  5. After you successfully import the application definition file, you can view the entities. The following figure shows the information for the Customer entity.

    Figure 10. Customer entity in the Business Data Catalog

    Customer entity in the Business Data Catalog

Using the Business Data Catalog

The Customer Service Request solution uses the Business Data Catalog and its Web Parts to display data from the SQL database on the various dashboards. The following figure shows the Customer Support Dashboard with the list Web Part for the Business Data Catalog displaying a list of customers filtered by customer name and showing the View Dashboard action.

Figure 11. Using Web Parts for the Business Data Catalog on the Customer Support dashboard

Using Web Parts

Key Sample Files

Table 8. Key files related to the Business Data Catalog

File

Description

CS_OBA.xml

The Customer Service Request Business Data Catalog application definition file. Located in the BI\BDC folder under the root Customer Service Office Business Application folder.

Using the SQL Server Analysis Services Cube

The Customer Service Request solution uses a SQL Server Analysis Services cube to provide business insight capabilities to the Manager dashboard and Customer dashboard. The Excel Web Access Web Part displays data from the cube in Microsoft PivotTable dynamic views and Microsoft PivotChart dynamic views to allow support personnel to view service request data. The Visual Studio-based SQL Server Business Intelligence Development Studio was used to create the SQL Server Analysis Services cube.

The cube operates on the data in the Customer Service database. To perform the cube processing from within SQL Server Business Intelligence Development Studio, right-click the cube in Solution Explorer and select Process. From within SQL Server Management Studio, in the Object Explorer, right-click the cube and select Process.

Figure 12. Processing the SQL Server Analysis Services cube from within Visual Studio

Processing from within Visual Studio

Figure 13. Processing the SQL Server Analysis Services cube from within SQL Server Management Studio

Processing from within SQL Server Studio

Key Sample Files

Table 9. Related files to the SQL Server Analysis Services cube

File

Description

"Customer Service OBA Cube.sln"

The Visual Studio solution file for the SQL Server Analysis Services cube. Located in the BI\Customer Service OBA Cube folder under the root Customer Service Office Business Application folder.

Guidelines for Modeling Business Data

We do not recommend using SharePoint lists and document libraries as a substitutes for a relational database management system. SharePoint lists and document libraries are intended to store and relate unstructured artifacts of relevance to structured business entities. The metadata definitions of these unstructured artifacts can include attributes whose values are sourced from LOB data that is typically stored in a relational database management system and exposed through a Web services layer.

Office SharePoint Server 2007 is an appropriate medium for the metadata modeling and storage of unstructured artifacts such as task lists and documents related to structured business entities. The modeling and storage of such artifacts in Office SharePoint Server 2007 can also facilitate rich unstructured collaborative capabilities through associated workflows and user experiences for process execution that are integrated into Microsoft Office clients such as Outlook, Microsoft Office Word, and Excel.

In Office SharePoint Server 2007, the modeling support for these unstructured objects, can accommodate the integration of related LOB data attributes from LOB systems. Capabilities such as Business Data columns and the ability to connect to LOB systems from associated workflows and event handlers enable deeply-integrated support for embedding LOB semantics in the unstructured artifacts and processes.

These guidelines are illustrated in the Customer Service Request Office Business Application. The use of a SQL Server database to model and capture the core attributes of a structured business entity like a service request, and the use of Office SharePoint Server 2007 to model and relate service request task items that are unstructured and collaborative in nature is an illustration of the application of these guidelines. Modeling the tasks as items in SharePoint lists enables business productivity and collaborative capabilities like the ability to associate human workflows to guide the surrounding processes and the ability to synchronize task items into Outlook for simplified tracking and management. You can achieve similar integration between structured and unstructured information for other types of business data of relevance to business solutions.

Design Decisions

The following sections provide the rationale behind some of the design decisions made during the implementation of the Customer Service Request Office Business Application.

Integrating with Outlook

The Outlook client design illustrated by the Office Business Application reflects and aligns with the Microsoft CRM 3.0 Outlook client experience. This occurs where Outlook is used as a shim for what is essentially at its core a Web-based user interface. We limited the solution's use of the rich customization features of Outlook to eliminate dependency on Microsoft Exchange and to reduce scope. For ideas related to customizing Outlook, see the Enabling Extensibility.

Design Consideration: Workflows in Office SharePoint Server 2007

The highly structured definition of the Service Request type and the structured nature of the end-to-end processing of a service request, do not make this scenario a good candidate for workflows in Office SharePoint Server 2007. Workflows in Office SharePoint Server 2007 facilitate unstructured collaboration around unstructured artifacts with integrated LOB data.

This solution uses task list items as the access point to the structured InfoPath form. The form executes service request work items such as assignment, adding work logs, and closing.

Design Consideration: Document Libraries

The service request is a highly structured entity. Therefore, it is not an appropriate choice for storage in a document library or SharePoint list. Content items in SharePoint lists are intended to store unstructured artifacts with elements of integrated LOB data, not structured artifacts where you need to preserve data modification history in an application database. The Business Intelligence scenarios for service requests are implemented against the LOB database and associated SQL Server Analysis Services cube. It is not good design to duplicate the definition of this entity in a document library because the related end-to-processing is also a structured flow.

Enabling Extensibility

The Customer Service Request Office Business Application illustrates using the Microsoft Office system to implement a customer service request system. The solution is an example of how you can customize the Microsoft Office system. It is not an example of a complete customer service request solution. The following sections list some areas where you can extend the current implementation to support a more robust customer service request application.

Integrating with Outlook

You can extend the solution to create a rich experience for working with service requests directly from within Outlook. Some ideas for extending the provided solution include:

  • Working with SharePoint tasks from Outlook.

  • Creating a custom Ribbon and a replacement form region to work with service request tasks.

  • Adding support for offline handling of service request tasks.

You can also use a custom task message class combined with a custom Ribbon and replacement form region to enable a rich user interface for working with service request tasks, including hosting the InfoPath form in the task management window.

NoteNote

Without an Exchange Server deployment, you must use caution when building task behaviors in Outlook. The ItemAdd event of the TaskList class does not run when you add large numbers of task items. This can introduce suboptimal behavior for new users or users who work disconnected for long periods. For more information, see ItemsEvents_Event.ItemAdd Event (Microsoft.Office.Interop.Outlook).

Customizing the Edit Control Block for SharePoint Task Lists

The Customer Service Request Office Business Application uses an custom action for the edit control block to allow access to the InfoPath-based Service Request form from a task list item. The default menu items for the edit control block, such as Edit Item and Delete Item are visible. This lets users edit or delete task items. This can potentially break the database link. To resolve this, you can edit the CORE.JSS file to either hide the default menu items or provide a custom implementation that works within the service request context.

Conclusion

This article describes a solution that uses the Microsoft Office system to support the processing of customer service requests.

Using the Microsoft Office system to create the Office Business Application delivers rapid application development with familiar tools and reusable components, simplified application deployment and maintenance, a consistent and familiar context, and easy access to LOB data.

Additional Resources

OBA Central

Office Business Applications Developer Portal

Office Developer Center

Office Developer How-To Center

Logical Architect Diagram for 2007 Office system

Business Data Catalog

Office SharePoint Server 2007 Developer Center

Developer Map for SharePoint Products and Technologies Poster

Welcome to the Microsoft Office SharePoint Server 2007 SDK

What's New for Office Developers in the 2007 Microsoft Office system

MSDN Architecture Center

Office Events and Webcasts

How Do I? Screencasts

Visual Studio Tools for Office Developer Portal

ASP.NET Developer Center