Office Business Applications: Pricing Exception Management Solution
Summary: Learn to implement an internal Price Exception Management solution. Examine how you can use Office Business Applications to establish interoperability between SAP and the 2007 Microsoft Office system. (19 printed pages)
Bas Kamphuis, Microsoft Corporation
Erika Ehrli, Microsoft Corporation
Leanne Brodzinski, Microsoft Corporation
Jean Wijsman, Microsoft Corporation
Mike Lockhart, Microsoft Corporation
Randy Hongo, Microsoft Corporation
January 2008
Applies to: 2007 Microsoft Office System, Microsoft Office SharePoint Server 2007
Contents
Connecting Back-End Systems and Real-World Work Processes by Using Office Business Applications
Overview of the Pricing Exception Management Solution
BCWeb Conceptual Architecture
Conclusion
Acknowledgments
Additional Resources
View video: Office Business Applications: Price Exception Management.
Connecting Back-End Systems and Real-World Work Processes by Using Office Business Applications
Organizations and IT departments invest in line-of-business (LOB) applications such as enterprise resource planning (ERP), customer relationship management (CRM), and supply chain management (SCM) to manage work processes and line-of-business information. These applications play a critical role in different workflows and scenarios across the organization. However, the back-end LOB applications and the tools and programs used by information workers are rarely connected. Much of the ad-hoc way that people work occurs in Microsoft Office system applications. Usually, this is disconnected from back-end systems. In many scenarios, LOB applications are seen as overhead since they do not map to the real way that people work.
The 2007 Microsoft Office system is a unified solutions platform for building Office Business Applications that makes LOB applications, enterprise data, and workflows accessible and relevant to users.
Office Business Applications are a new breed of applications that use the Microsoft Office system to find and surface LOB information. Office Business Applications enable businesses to extend the Microsoft Office clients and servers into business processes running in LOB applications. Figure 1 shows the conceptual architecture of Office Business Applications.
Figure 1. Office Business Applications
.gif "Office Business Applications")
The goal of this article is to show you how the Microsoft IT department designed and implemented a Price Exception Management application using a custom Office Business Application solution. This solution exposes and synchronizes real-time data in SAP, and provides a workflow routing and approval component that helps provide a consistent user interface (UI) for approvers.
Overview of the Pricing Exception Management Solution
In Microsoft, sales employees in the field can request special deals, product promotions, on-price list promotions, and off-price list promotions for different markets. For example, Jean Philippe Bagel, a corporate vice president in France, can request 10 percent off Microsoft Office Professional Plus 2007 on an open license to a specific customer. At the same time, Armando Pinto, a sales representative in Brazil, can request 10 percent off of all Portuguese versions of the software. If both price promotions are approved, customers would get the following discounts:
10 percent off of Microsoft Office Professional
10 percent off of Portuguese software
20 percent off of the Portuguese version of Microsoft Office Professional
Price exception management is the set of processes and tools that help manage price list promotions, special deals, and product promotions applicable to product lines in a company. Microsoft sells software, hardware, games, and many other product lines and needs a price exception management solution to orchestrate requests and manage business rules and a routing and approval workflow. The Microsoft Information Technology group implemented a Price Exception Management solution (named BCWeb) based on Office Business Applications that helps simulate pricing discounts, manage business rules, customize routing and approval workflows, and update the LOB data stored in SAP.
One of the major goals of this system is to improve field productivity by simplifying the process and removing training requirements for complex back-end systems, in this case SAP.
In the past, sales fields needed knowledge of IT systems to create a promotion. Sales fields were also required to use different promotion types in different programs. BCWeb integrates different promotion types into a simplified Web application. It also allows users to specify business rules using Microsoft Office Excel 2007 files and to get notifications in Microsoft Office Outlook 2007. Additional goals of this system include:
Reduce revenue leakage by having a business case documented for each promotion field.
Obtain visibility decisions and understand who required a discount and why.
Understand promotion effectiveness and ensure the field is making informed decisions.
Provide a configurable set of files to easily change empowerment guidelines by having a single master data of workflow routing and approvals.
Specific business process in price-execution domain, however, pattern addresses larger solutions space
Scenario Overview: A Day in the Life
Madeleine Kelly, a technical specialist in Romania, supports the subsidiary sales goals by introducing additional incentives for small to medium business customers for Microsoft Office SharePoint Server (MOSS) 2007. Her goal is to create a promotion price based on the published price list with the following parameters:
5 percent price discount on any License or License and Software Assurance offerings
The promotion will be effective for three months
35 percent growth target in the sales between fiscal year 2007 and fiscal year 2008
If the sales representative wants to move forward with a promotion request, she submits a business case and the Price Exception Management solution starts a routing and approval workflow. After the promotion discount is approved, the system updates the back-end LOB data. Figure 2 shows a high-level workflow process.
Figure 2. Price exception management high-level process
.gif "Price exception management high-level workflow")
As shown in Figure 2, the Price Exception Management solution workflow includes four steps:
Create the promotion request. A sales representative uses a Web-based application to select promotion parameters such as the prices, countries, and distributors. The sales representative can also run price simulations and approval routing simulations before entering a business case and submitting a request.
Submit the business case. After the sales representative is satisfied with the promotion request, she completes another Web form where she defines the rationale and return of investment details. After the information is ready, the system drops an XML file to a document repository and starts the route and approval process.
Route and approve request. The route and approval process defines a set of configurable rules and decision tables for routing and approval. Based on these settings, the system generates a custom workflow for each promotion request. Next, it sends notifications to the set of approvers involved in the custom workflow and orchestrates the custom routing and approval process until completion.
Execute a price range. After a promotion request approval is complete, the system notifies the sales manager and SAP conditions are automatically created and applied for the effective date.
Figure 3. Price exception management solution high-level workflow
.gif "Price exception management workflow")
BCWeb Conceptual Architecture
BCWeb showcases MOSS 2007, Windows Workflow Foundation, SAP interoperability, and the Open XML File Formats. Operationally, the implementation of the Price Exception Management solution using the previous technologies can be the best and most flexible solution for the following reasons:
The front-end user interface provides the following:
A UI experience that is much like a wizard
The use of ntelligent filtering to help improve the user experience
Because there is no SAP UI, a Microsoft ASP.NET 2.0 and front-end SharePoint site avoid the use of confusing SAP user ID maintenance.
MOSS 2007 provides the platform for custom workflows. A custom-built workflow routing and approval component is generic and allows configuration of up to five levels of workflow approval.
BCWeb has three main components that orchestrate the entire promotion request workflow:
BCWeb front-end application for SAP. AnASP.NET Web application that provides a set of Web forms that help capture price exception details and information on the business case for doing discounts on price lists, and helps calculate the percentage of discount.
SAP Pricing Engine. System that connects with SAP to run real-time price simulations based on user preferences. It uses the SAP .NET Connector and custom Web services to pull real-time price information from SAP, and provides sales executives with a light front-end interface to SAP.
Workflow Routing and Approval Process system. Configurable, rules-based empowerment and workflow system that defines business rules by using Microsoft Office Excel 2007. Users can use decision tables to capture approval and routing domain values. Additionally, a Microsoft Visual Studio 2005 state machine workflow uses the Open XML Open Packing conventions to extract routing and approval information from Excel 2007 files. Then, it compiles and deploys custom workflow templates to a SharePoint site.
Figure 4. BCWeb architecture diagram
.gif "BCWeb architecture diagram")
BCWeb Front-End Application for SAP
The BCWeb front-end application is implemented as an ASP.NET 2.0 Web application. This application is the starting point for all promotion requests and provides a simple set of entry forms to help sales employees create promotion requests and submit a business case. It also provides Web services that pull real-time data from SAP.
Creating the Promotion Request
The BCWeb front-end application provides a set of Web forms to help users define promotion request settings, as shown in Figure 5.
Figure 5. BCWeb application Web forms
.gif "BCWeb application Web forms")
The BCWeb front-end application connects with SAP every day in real-time by using the SAP .NET Connector. This application provides a set of custom Web services that follow Service Oriented Architecture principles. BCWeb calls the SAP Web services to determine the valid price lists and customers and the possible SKUs for a promotion based on inputs provided during on-screen selections. It also calls the SAP Web services to run pricing simulations that show what the possible impact of the promotion is and to create or change the promotion condition records in SAP. Every night, a scheduled job runs in SAP to extract promotion intelligent filtering files to provide possible values for all the selection options on the front-end Web forms s and to generate an XML data repository. The file is dropped in the application directory and is cached in memory to improve performance. The BCWeb front-end Web forms use intelligent filtering by displaying dynamic drop-down list controls that bind data to the XML file through an XMLDataSource object. This implementation helps reconcile records and promotions automatically. The Web application displays the prices, countries, distributors, and other fields to filter data. Based on user selection, a different Web service connects to SAP to generate price simulation information. This execution is run by the SAP Pricing Engine component. Additionally, before submitting a request, sales representatives use this application to run routing simulations and visualize the routing and approvers that may be involved in a discount process.
Submitting the Business Case
After a sales representative is satisfied with the price and routing simulation information, she fills the business case form and submits the business case. The BCWeb front-end application provides a set of Web forms that help users define business case information as shown in Figure 6.
Figure 6. BCWeb business case submission
.gif "BCWeb business case submission")
The BCWeb application helps capture the rational for a price discount and return on investment information. The application serializes pricing information and the business case into an XML file and stores the file in a SharePoint library. Finally, through a custom API call, the BCWeb application uses a Windows Communication Foundation TCP/IP call to invoke and submit a request object. This object serializes the document stored on the document library, document headers fields, and parameters used to kick-start the workflow routing and approval process components.
Workflow Routing and Approval Process
So far, we explained how the BCWeb front-end application helps with the first two steps of the high-level workflow: creating a promotion request and submitting the business case. The next step of the high-level workflow is routing and approving requests. Even when this represents a single step in the high-level workflow, this is the central part of the system.
Routing and Approving Requests
Microsoft has a set of policies and rules that define the set of actors that must approve different types of requests such as negotiated deals and price promotions. In the past, approvers had to use multiple systems to approve different types of requests. In multiple occasions, some requests were approved by the wrong person or not in time. Microsoft needed a new system to help route and orchestrate the many different and concurrent approval workflows for promotion requests. The Microsoft IT department implemented a workflow routing and approval process to provide an extensible and agile solution that provides a consistent UI for approvers. Figure 7 shows how the workflow routing and approval process manages approval processing within a larger process.
Figure 7. Workflow routing and approval process
.gif "Workflow routing and approval process")
The workflow routing and approval process is extensible, by using APIs, to receive different types of requests. It collects and archives decisions for auditing and for consumption by and for systems that consume the decisions, such as price publication and order processing. The workflow routing and approval process also implements the concept of an Approval Routing Domain, which is routing rules for each request type, such as price promotions, special deals. Routing approval workflows can change from one promotion type to another. The workflow routing and approval process system provides configurable files to define custom settings for three different approval workflows:
Serial. Requires one approver at each level in order. Any approver can reject and therefore stop the process.
Parallel. Requires one approver in each department in any order.
Hybrid. Combines a serial approval routing workflow and a parallel approval routing workflow.
Figure 8. Different configurable approval workflows.
.gif "Configurable approval workflows")
Workflow Routing and Approval Process Architecture
The workflow routing and approval process system integrates different Microsoft products and technologies to implement the business rules engine and a workflow engine:
Business rules. Excel 2007 allows rule administrators to capture business rules. The workflow routing and approval process uses Excel 2007 as the interface for decision sets. Excel 2007 works as a business rules engine and helps manage the rules capture and decision process for promotion approvals and notifications. The workflow routing and approval process uses Excel 2007 to define the complete set of routing and notification rules.
Workflow engine. The workflow routing and approval process uses the rules engine provided by the Windows Workflow Foundation and hosts workflow in MOSS 2007.
In the first section of the article, we explained that organizations can build custom Office Business Applications by defining components and systems that integrate Microsoft Office system programs, servers, services, tools, and technologies with LOB information. The following section of the article explains how the Microsoft IT department used Excel 2007, MOSS 2007, and custom APIs to define a rules engine and a custom routing and approval workflow.
Figure 9. Workflow routing and approval process architecture
.gif "Workflow routing and approval system architecture")
Excel 2007 as a Business Rules Engine
Rule administrators are the people that manage and write business rules. In the past, legacy systems constrained an administrator's ability to manage rules. Excel 2007 provides a comfortable environment for rules administrators and requires minimal investment in training. New rule administrators must understand how the business rules tables work, but are already familiar with Excel. To add to the benefits, implementing decision tables by using Excel 2007 represents low costs for companies that already invested in 2007 Microsoft Office systems licenses.
The workflow routing and approval process defines two different configurable sets of business rules in Excel 2007:
Routing rules. Rule administrators use Excel 2007 to define maximum approval levels. A participant spreadsheet identifies individual approvers for each matching condition (country/region, segment, and level). Rule administrators also have the flexibility to define child rule sets. This flexibility makes it easier for Rule Administrators to manage the rules, as they are able to collapse repeated rules into one Excel worksheet and reference them from other worksheets. For example, specifying the different maximum approval levels for different countries and segments.
Notification rules. Rule administrators can define the e-mail templates to use for different notification events and participation roles.
Figure 10. Routing and notification rules in Excel 2007
.gif "Routing and notification rules in Excel 2007")
Rule administrators can change the spreadsheets anytime. However, to validate that business rules are correct, the workflow routing and approval process runs a nightly provisioning process to validate routing and notification rules. All approvers listed in the Excel 2007 spreadsheets should be valid approvers listed in Active Directory directory service. The nightly provisioning process also validates against the rules, as administrators can specify applicable dates for approvers. Running this process helps reduce the risk of possible errors in approval workflows. This functionality was implemented to fulfill a business requirement. To comply with Sarbanes-Oxley principles, we had to verify that an approver was valid for that day to make a decision for a given request.
Workflow Engine
Windows SharePoint Services 3.0 and MOSS 2007 provide a new platform for defining and running workflows. The workflow platform is based on the Windows Workflow Foundation, a Windows platform component that provides a rules engine, a programming infrastructure, and tools for development and execution of workflow-based applications. For more information, see Developer Introduction to Workflows for Windows SharePoint Services 3.0 and SharePoint Server 2007.
The Microsoft IT department used the Microsoft Visual Studio 2005 Workflow Designer as a Workflow designer, the Microsoft .NET Framework Windows Workflow Foundation as a workflow engine, and MOSS 2007 as a run-time host for the workflow routing and approval process.
The Price Exception Management solution uses a state machine workflow. State machine workflows are designed to work in event-driven scenarios. A state machine workflow contains two or more states, with one state being the active state at any given time. Figure 11 shows the workflow routing and approval process state machine workflow definition in Visual Studio 2005.
Figure 11. Workflow routing and approval process state machine workflow
.gif "WRAP state machine workflow")
The ultimate goal of the workflow routing and approval process is to generate a compiled set of "rules.dll" libraries that contain rules definitions for every single promotion request. The workflow routing and approval process deploys each rules.dll to the global assembly cache of a server running MOSS 2007 that hosts and persists multiple workflow instances.
The workflow routing and approval process defines a six steps process to import and execute rules:
Import a data package. Business rules administrators use an administration console to import the rules defined in the Excel 2007 spreadsheets. That is, they can import new rules by using a smart client application on the rules administrator's desktop. The tool generates an import package (ARD) that contains three different files:
Business rules. An Excel 2007 file that defines the set of routing and notification rules
Configuration parameters. An XML file that provides a schema definition for custom configuration parameters.
E-mail templates. XML files and XSLT files that define optional templates that can override predefined e-mail templates.
Convert to intermediate data format. The workflow routing and approval process components convert the Excel 2007 rules to an intermediate data structure. The workflow routing and approval process component reduces the time of the importing by parsing the underlying Open XML File Formats files that make up an .xlsx file. What previously took several minutes on the client using the Office primary interop assemblies was reduced to a few seconds. For more information see, Introducing the Office (2007) Open XML File Formats.
Validate rule set. We explained earlier that the workflow routing and approval process runs a nightly provisioning process to validate routing and notification rules. Provisioning happens separately from validating a workflow routing and approval process ARD package. Provisioning reruns the rules against in-flight requests. Workflow routing and approval process provisions both nightly and when an ARD is imported. Nightly provisioning evaluates all pending requests; while import provisioning is specific to requests for the new imported ARD. This process validates invalid domain values for attributes, use of undefined columns, circular references between child rule sets, invalid actions for a given rule set type, and invalid approver aliases.
Optimize "Find ALL" rules. When a sales representative creates a promotion request, she defines a set of fields that define the required conditions for a promotion. Each rule set has unique request types, countries, amounts, and approvers. The workflow routing and approval process APIs search for all applicable rules that match a given condition. Promotion requests need to "find all" matches for a given condition. Parsing large files rule sets with multiple approvers is a custom scenario needs an optimized search algorithm. The execution of a "find all" optimized search algorithm helps improve the performance of rules and that satisfy the conditions of a search. This optimization helps "find all" matching rules for routing and approver determination.
Generate Custom Rules. After the rules are optimized and validated, the workflow routing and approval process components use the Code Document Object Model (CodeDOM) Quick Reference to generate Workflow Rules CodeDom. The Workflow CodeDom provides custom rule sets that define the behavior of workflows that hosted on a server running MOSS 2007.
Compile Rules Set.dll. The Workflow CodeDom is compiled into a Microsoft .NET Frameworkâbased assembly (.dll) for better performance. There is one .dll file for each RuleSetType (Routing Level determination, Approver determination, or E-mail Template determination) per ARD package. Windows Workflow Foundation and MOSS 2007 run the rules compiled in the .dll file. This greatly improves the execution of multiple promotion request routing and notification workflows.
Execute Price Change
The last step of the high-level price execution management process is executing the price change in SAP. BCWeb uses the workflow routing and approval process to define the routing and approval process for user-defined pricing concessions. After each custom workflow is deployed to MOSS 2007, the real-time routing and approval workflow instance starts running. Each approver defined in the workflow rule set gets the appropriate notifications and makes decisions to approve or reject a promotion request until completion. If a price promotion is approved, the workflow routing and approval process calls a Web service hosted by BCWeb, which in turn communicates with SAP to execute a price change. A SAP condition is created automatically and applied for an effective date.
Every month, price analysts run a process in SAP to regenerate all the price points and apply all discounts coming from the multiple approved price promotion requests.
Conclusion
The 2007 Microsoft Office system provides a platform for building composite solutions, named Office Business Applications. This platform is designed to support cross-functional processes and allow information workers to become more productive. Office Business Applications allow global information workers to gain insight, collaborate, make related time decisions, and take action.
The Microsoft IT Price Execution Management solution showcases how Office Business Applications can help connect back-end systems with the 2007 Microsoft Office system to take advantage of existing technology investments, and to define custom workflows and solutions that greatly improve business processes and return on investments for organizations.
Acknowledgments
We want to thank the following people for their contributions to this article:
Amit Garg, Pradeep Balakrishnan, Joyjeet Majumdar, Radha Ramaswamy, Dona Mukherjee, Sundaresan Krishnamurthy, Tim Ragain, Padma Chigurupati, Clay Sales, Kiruthika Baskar, Nathan Helgren, Robyn Kilponen, Mark Antone, Lisa Cranfill, Lisa Jackson, Duke Hafferman, Donna Taylor, Eric Morris, Chris Lawson, Ram Sastry, Aisling Duggan, Nagaraj Venkataramu, Yossi Avnon, Adonis Acuario, Emil Tso, Gordon McIntosh, Russ Martin, Prakash Duggirala, Sirisha Chalasani, Anil Kumar Busa, Sridhar Reddy Gandra Cathy Swenton, Robert Peacey, Alexey Morozov, Clint Simmons, Aaron Wiley, Randy Neumaier, Venumadhava rao Yandapally, Mukundan M K, Pavan Adhyapak, Adam Johnson, Patrick O'Brien, Kamal Desilva, John Degel, Betina Duro, Subodh Kirtane, Andrew Whitaker, Anthony Young, Au Casiano, Demian Crumb, Dinesh Srinivasan, Glenn Sawatsky, Greg Grivas, Kevin Neher, Leanne Brodzinski, Manjunath Khatawkar, Niket Kalgaonkar, Ryan Perlman, Lyanne Ma, Bill Dow, and Jerre McQuinn.
Additional Resources
For more information, see the following resources: