Purchase Order Management

One of our consulting engagements on Microsoft Business Intelligence Stack is with Purchase Order Management for a retail giant in apparels and home goods across the United States. When it comes to the Retail Industry, most people tend to think it as a Point of Sale (POS) solution. Apart from a POS application, the Retail industry also has to cater to the needs of the retail stores for their Order Management. In simple terms a Purchase Order Management System is a system which is used to manage the purchase orders. But what makes a purchase order (PO) a vital document is that, it is a document issued by a buyer to a seller, indicating the type, quantities and agreed prices for products or services the seller will provide to the buyer. Sending a PO to a supplier constitutes a legal offer to buy products or services. Acceptance of a PO by a seller usually forms a once-off contract between the buyer and seller so no contract exists until the PO is accepted. So this makes a PO, very critical document for any retailer.

The company has a Purchase Order Management application suite which is several releases old. This application suite has few client applications built over Microsoft .NET coupled with legacy systems like Mainframes and RS6000 for Retail shops to place orders from their retail outlets.

Our team proposed new application architecture to cater to the needs of the PO Systems, as there were several challenges with upgrading the application, i.e., any change one of the modules in the application involved end-to-end changes in all the layers of the application. The new architecture of the application is based on .NET 3.5, WCF, BizTalk, SharePoint, SQL Server 2008 BI Stack. Now let us discuss about the role of Microsoft BI stack in this engagement.

Scenario:-
As per the existing application architecture, the retail shops and merchandising agents have the flexibility to either fill out a worksheet using a thick client application and transmit them to the server, where the purchase orders are created and moved to the Mainframes, or, the retail shops could place their orders via the merchandising agents, in which case, there were two workflows, one being the Merchandising Agents using the application to fill out a form to generate a PO or, create the PO on the Mainframe. So these two types of the purchase orders are categorized as to Application generated and non application generated purchase orders.

Worksheets are created using the Client Application is stored in the form of an XML document in the SQL Server 2000 (Client Application) and DB2 (Mainframe and RS6000) databases.

Here’s the block diagram that shows the current workflow of Purchase Order (PO) Processing,

Objective:-
The scope of this engagement is widespread, and there are several teams working different modules of the engagement.
Here are few of the modules that we were involved in,

1. Conversion from SQL Server 2000 MSDE to SQL Express 2008 for Client Applications.
2. Implement Integrated Windows Authentication on the Client Application.
3. Generate Statistics and Audit Reports for Application/Feature usage.

Conversion from SQL Server 2000 MSDE to SQL Express 2008 for client application
This task looks simple though but posted a numerous issues at the time of implementation, here are the few of the tasks that were carried out for the same,

1. Silent installation script to install SQL Express 2008 pre requisites and resume installation on system restart.
2. Silent installation batch script to install SQL Express 2008 with a named instance.

Implement Integrated Windows Authentication on the Client Application

Implementing integrated windows authentication was rather simple task. We had to create one LDAP group for the Retail shops/Merchandising Agents and one LDAP group for the administrative users and attach them to the corresponding roles to have data entry or administrative permissions. Thanks to Credential caching feature of Windows, this ensures integrated windows authentication on SQL Express 2008 even in a disconnected mode.

Generate Statistics and Audit Reports for Application/Feature usage

The Statistics and Audit Reports for Application usage is one of the major tasks that we were involved in. There are 3 different reports,

1. A report to determine how many retail shops are using the client application. The selection criteria for this report is flexible to produce the result based on different combinations of divisions (for ex, apparels, home goods, etc), departments (ex, winter wear, men’s footwear, accessories, etc), retail shop and worksheet transmitter, this is also called the productivity report.
2. A report that tells how many PO’s were created for individual departments also called as master report, with the division as selection criteria.
3. An audit report that provides the audit information regarding use of the Lead Time Calculation (LTC) logic, that suggests optimal shipping dates, within the client application, in order to assess the level of use as well as the amount of manual adjustment that is performed after the logic has been applied in the client application. The weekly report illustrates by user the methods by which ship date and ladder plan information was supplied to the client application.

To generate these reports we have used SQL Server Integration Services (SSIS) as ETL tool and SQL Server Reporting Services (SSRS) for the reporting, coupled with ASP.NET 2.0 Website to host the reports and provide selection criteria.

The SSIS packages fetches PO’s from various data sources such as the SQL Server 2000 databases, Mainframe and RS6000 DB2 databases and as per the new architecture of the system, the master data will also be available in a SQL Server 2008 databases. But since there are divisions which are still running on the older version of the application suite, it is necessary to keep the data from the older system as is and provide a switch for the package to start fetching data from SQL Server 2008 databases when all of the divisions are migrated to new version.

The productivity report and the master report are straight forward. These reports count the number of PO’s generated using client application, Mainframes, and RS6000. After getting the count of PO’s these are then stored in the corresponding tables for the reports.

The audit report requires to read through the worksheet and PO XML documents stored in the database to check whether the LTC logic was used or not. SQL Server provides us with XQuery language, using which we can query xml data type. XQuery handles both typed and untyped data. After reading through the XML documents, the values of the required fields are stored in a table. This table contains all the line items of the worksheet, in which the LTC logic was switched on/off.

Apart from these reports there are other BI requirements in this application suite which are planned, for example, analyzing quarterly sales results and forecasting revenues, using SQL Server Analysis Service. Soon we will have more updates on this blog, with more data related to Microsoft BI technologies used in this engagement.

WCM Fundamentals

Web Content Management or WCM in short is one of the more interesting topics on MOSS. This blog aims to provide an overview of WCM, explain what is WCM, why is it special and more importantly, how is it useful for you.

So what is WCM in simple terms

WCM is a rich content authoring and management platform. It provides a set of controls and publishing features that allow the site owners to host content centric sites. It takes care of the site branding, publishing, content authoring, workflows etc. WCM forms a part of the Enterprise content management solution, which in turn forms a part of MOSS 2007. It also leverages the Office Word and Infopath.

In short it is a very scalable solution that separates the content and presentation, relieving the burden on the IT department.

To better understand the solution that WCM provides, we need to understand the problem first.

Managing a content centric web site is by no means a simple task. In most of the organization, it will be the IT team that will have access to add new pages, maintain the pages and keep the site running smoothly.

The content contributor has to undergo the overhead of approaching the IT staff for each and every change. This translates to longer process and higher cost of operation. Many a times the content will have to be edited a few times before it is correctly published.

This is where WCM comes into picture. WCM provides a platform. It defines the site branding, sets the templates, look and feel, authoring rules, publishing rules, workflows, various levels of securities etc.

The content contributor can now focus on his content alone and leave the development hassles aside. He simply submits his data. This will in turn validate the data, start the workflows, approval cycles and finally publish the content without the support of IT staff. The final content will be published in accordance with the look and feel of the rest of the site.

WCM incorporates all features in Microsoft content management server 2002 (MCMS). Microsoft has discontinued providing CMS as a separate product, but instead, it provides the enhanced version ( WCM ) along with MOSS 2007.

Some of the important features of the WCM are listed below.

• Workflows
  
• Search functionality
  
• RSS facilities
  
• Built in Caching mechanism 
  
• Supports multiple devices 
  
• Better Versioning mechanism
  
• More events captured
  
• Pluggable Authentication
  
• Reusable Content
  
• Web based management
  

Having said all this, Does this really make sense?

A content heavy web site will have frequent changes. New pages will be added by various contributors. Managing the new pages, recording the version history, validating data, format etc is a mammoth task. Creating a application to handle the same will cost a fortune. WCM automates most of the processes and brings the focus to what matters the most, the content. This way, the contributor will be able to focus more on the data and be able to publish the content in a quick efficient manner.

The process does not require support from the IT department as the contributor can himself manage the content online. Thus saving a lot of effort as well as money.

In short, WCM saves Time and Money. And that makes a lot of sense.