TLA’s – We’re full of ’em, OBA – Office Business Application!

August 30, 2006

Office Business Applications (OBAs) deliver people-centric, collaborative solutions to the enterprise through familiar Microsoft Office servers, clients, and tools – we’re talking about bringing toegther the component parts of the Office System here. This post will offer an example of the  “results gap” that contributes to reduced productivity, and shows that OBAs are an effective approach that enables enterprises to achieve the “last mile of productivity.” You will see that several key components of the 2007 Microsoft Office system can be used to develop Office Business Applications and that, when Line of Business Integration (LOBi) for Microsoft Office SharePoint Server is released, it will further simplify the development of OBAs.

Before we get to the good stuff lets just consider this results gap. Companies rely heavily on IT to help address many challenges, as evidenced by the huge investment in large financial management systems and solutions for enterprise resource planning (ERP), customer relationship management (CRM), and supply chain management (SCM). Nevertheless, many organizations have not realized the expected value from these investments. There is a clear gap between the efficiency and productivity increases that corporate leaders expected to see, and the actual return on investment (ROI) that they have experienced. Not really ground breaking stuff jamesy!

This “results gap” is caused by a fundamental inconsistency between how business systems work and how people work. The systems are based on transactional processes that are necessary in order to accomplish specific tasks—for example, creating a Purchase Order. What they have not effectively captured are the ad hoc, local people-driven processes that invariably arise. The result is that decision makers take a “feed the machine” view to their corporate business applications, but rely more heavily on the people-to-people collaboration for making decisions and taking actions.

Putting this into practice; A common collaborative planning task within an enterprise is the reconciliation of numbers between Sales and Merchandizing – particularly in a retail environment. By using the 2007 Microsoft Office system, a Sales Director and a Merchandise Planner can complete this process more efficiently. They both can use a single underlying Excel document to store the data, and they can use Excel Services to maintain it. In this way, they can have a single definitive version of the data, and the plan can be shared very easily from the server to other persons in the organization.

The document can be stored in a document library in SharePoint. A workflow can be associated with this document library, with custom business logic that is executed whenever the spreadsheet is saved. For example, the workflow could run validation rules on the spreadsheet; apply approval policies to the data; cleanse, validate, or filter the data; or update back-end systems.

You can take the following steps to implement this collaborative planning process with the 2007 Microsoft Office system:

  1. Build application parts—Use the metadata to create an Excel file that contains the Sales and Merchandise numbers. Partition the numbers into different worksheets, based on the type—for example, a Sales Plan sheet for the sales targets, or a Merchandise Plan sheet for the merchandise numbers.
  2. Create a team portal—Create a SharePoint portal, and publish the file to Excel Services within the portal. The document will reside within a document library. Excel Services allows multi-level permissions to be applied to the file. For example, users may be allowed to view the file contents in a browser, but they will be unable to open the file in the Excel client. Or, users will be able to see only the numbers in the Excel client, but they will not have access to any of the formulae being used in the document.
  3. Build custom GUIs—Create personalized sites for the Sales Director & Merchandise Planner within the portal, and provide links to the Excel file on each of the sites. These users will see only those files that they are interested in. Since the file is being hosted within Excel Services, all users receive the same copy of the file.
  4. Design a workflow—Use .NET Framework 3.0 & Visual Studio 2005 to develop a workflow that takes the contents of the Excel file and saves it to a database. Use the OpenXml libraries (under System.IO.Packaging) that are available in .NET Framework 3.0 to get the Excel data. Because the workflow will be hosted in SharePoint Server, it has access at runtime to the attributes of the file. These include, for example, the stream for the file that has been modified, the user who last modified the file, and the library where the file resides. The workflow could also perform more complex functions, such as creating a SharePoint task for a set of users, or sending users an e-mail message with the details of the task. Alternatively, to support communication across partners, the workflow could also send the data externally to a trading partner. As a final step, create a strong-named assembly containing the workflow, and install it in the local .NET Global Assembly Cache.
  5. Build an input mechanism—Create an association form using InfoPath. This form will be used to accept user data when the workflow is associated with the document library. Create an initiation form if required. The initiation form could be used to accept user data when the workflow begins. Install the workflow in the SharePoint portal as a feature, and associate it with the document library that contains the Excel file. Configure the workflow in such a way that, whenever any changes are made to the file and saved, the workflow runs.
  6. Create analytics—At the back end, create a data warehouse that is based on the schema that matches the Excel spreadsheet metadata. Using SQL Server Integration Services, copy data from the database to the data warehouse in a scheduled or on-demand manner. Create an SQL Server Analysis Services cube by using the warehouse. Create a pivot chart in an Excel file, and link it to the cube. Publish the Excel file to Excel Services. Finally, use the Excel Web Renderer Web Part to display the chart to users of the portal. Alternatively, use Business Data Catalog metadata to declare an entity for each row in the database. Use BDC Web Parts to display lists of the entities and enable users to search the database. You can also use the specification to create a parent-child relationship between entities—for example, a Purchase Order can contain line items. Since the metadata is in XML, it does not require users to be aware of any programming language in order to make changes.

By building on the 2007 Microsoft Office system, organizations can deliver LOB data and logic to the people who are responsible for running the business, in the context of their familiar Office applications. OBAs will drive home a higher ROI for the legacy applications by enabling broader access, and they will make process automation a reality to the organizations and people who use them—for just a fraction of what it would cost to deploy these legacy applications more broadly.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: