NATIONAL E-PROCUREMENT PROJECT GUIDANCE NOTES

(1)

N

ATIONAL E

-P

ROCUREMENT

P

ROJECT

G

UIDANCE

N

OTES

D

ATA

I

NTEGRATION

Title:

_{Data Integration}

Identification: Defines some of the technical aspects of data integration within organisations and between systems and outlines the business

considerations that need to be understood in order to develop an effective data integration strategy.

Version: 2.0

Date of Issue: 6th February 2004 Current Status: Final

(2)

1. Introduction and Definitions

Organisations have often invested heavily in systems that already support some aspects of the Procurement life cycle or the specific operational requirements of one or more departments. This is especially so in diverse organisations such as local authorities where, for example, social services systems hold details on care providers and contracts placed, and housing systems hold details on inventories. In most organisations there will also be considerable use of spreadsheets and databases containing supplier details, contract and preferred supplier listings, quotation and tender history and commitment/budget tracking.

This situation, combined with the additional systems acquired to support an e-Procurement strategy and the trend towards externally hosted systems, leads to almost infinite opportunities for data transfer and information sharing. Project teams will need to recommend rationalisation, integration, further roll-out and development opportunities in order to deliver a complete Procurement systems strategy, taking into account the requirements of internal stakeholders and external stakeholders, including suppliers. Getting integration wrong will mean that:

•

Efficiency benefits will not be achieved and the project may be perceived as a failure, as manual procedures have to be implemented to support the systems

•

Projects may not progress beyond the pilot stage

•

Data falls out of line and there are multiple versions of ‘the truth’

•

Management information is unusable Getting integration right will mean that:

•

Non value-add tasks are kept to a minimum

•

Stakeholder involvement in the process is kept to a minimum, based on managing risk

•

Data is always up to date and consistent across applications

een embers in

iscussions about the business issues with their IT departments and solution providers.

•

Data can be consolidated, compared and used to support effective decision making

This document defines some of the technical aspects of data integration within organisations and betw systems and outlines the business considerations that need to be understood in order to develop an effective data integration strategy. It is designed to support non-technical project team m

(4)

2. Data Integration Overview

Interfacing vs. Integrating

Simply put, interfacing is the transfer or copying of data from one system to another. Taking a file of orders raised in an inventory system and copying them into the finance system for accounting purposes, for example. An interface is typically the ‘middle bit’ between two independent systems.

Integrating is the sharing of functionality or data between two systems – one system cannot operate without the other.

In ‘integrated’ systems, this definition is often blurred, as system developers decide the most secure way of getting systems to work together. When talking to system providers about whether to ‘integrate’ or ‘interface’ a number of issues will need to be considered – integrating is typically costly and can be higher risk, as systems cannot work independently; interfacing can mean that the same data is held in two places and can be out of line – the question then arises as to which version is ‘the truth’.

Transactional, Static and Management Information

Transactional information is typically document-related information such as tenders, quotations, orders,

acknowledgements, invoices, remittances and statements. Transactional information is event-specific and is typically high in volume.

Static data is that data which is used to support transaction generation and validation. This includes

accounting information such as account and cost centre codes, users, locations, authorisation structures, suppliers, catalogues and prices. Static data needs to be reviewed to determine how volatile it is, e.g. how often there is a change to the list of cost centres or users, compared with how often new catalogue items get added or authorisation levels get amended. Whether changes are one a month or twenty a day will determine the appropriate integration or interfacing approach.

Management information is typically information that is either duplicated directly or summarised from

operational systems into larger information databases to enable more complex queries and reports. Information from e-Procurement, Finance, Inventory, Purchasing Card statements and supplier statements may be consolidated to provide comprehensive spend analysis, for example. Strategies for each type of data integration will be required.

XML

XML (eXtensible Markup Language) is the language developed by the World-Wide-Web Consortium (W3C) for transferring information electronically.

Transmitting data electronically between systems is not new: EDI (Electronic Data Interchange) has been used extensively in some business sectors since the 1970s. Historically, the problems with transmitting data (e.g. using EDI) were that bespoke interfaces needed to be written and maintained as systems had no inbuilt means of working out what information was being transferred. This meant that for Procurement systems, it was cost effective only for large manufacturing and retail organisations to implement.

There are a number of benefits with using XML over traditional means of transmitting data, including:

•

Information is easily identifiable through the use of ‘meta-data’ (i.e. data describing data), e.g.

(5)

•

Receiving systems can determine which bits of information they need and which bits to ignore

•

HTML (the language of web pages) can be used to display XML files such as orders and invoices graphically in a web browser, or a print program can take the XML and format it for printing. The underlying data is the same, and it can be transferred electronically and re-formatted as if it were printed and posted

The issues associated with XML today are:

•

Legacy systems are not yet XML-compliant

•

There are a number of XML standards for Procurement-related transactions

•

XML files may be very large in comparison, as they contain the data and the meta-data

•

Usage is still relatively low, but growing. It is accepted as the e-Government standard for data transfer

Interfacing Options

erred in a number of ways:

•

Internal System Interfacing

ve the e re the receiving system can process it – these programs are what is ce programs’.

ers atic

pplier.com. However, email delivery is non-s not proof of delivery.

reached through agreement on the use of Internet security, passwords and levels of firewall protection. Data and information can be transf

When data is interfaced between systems within an organisation, a file is produced from the sending system at a known point in time. File management applications or human intervention will then mo file to a system directory, where the receiving system will collect it when it is ready or an event is triggered. Often, programs are written to manipulate the file in some way, by re-coding or changing th order of fields, for example, befo

known as ‘interfa

•

Emailing

Sending an XML document as an email attachment means that technically unsophisticated stakehold can open up the attachment in a web browser and print it if required. Sophisticated stakeholders will extract the XML file and import it into their receiving systems automatically. Emails can have an autom response mechanism and can be digitally encrypted. Most business stakeholders will have an email system, and you should recommend (internally and externally) that emails be sent to generic email addresses such as [email protected] or sales@su

guaranteed and proof of posting i

•

FTP (File Transfer Protocol)

FTP is a method of transferring files over the Internet. It involves transferring files between electronic addresses. The debate that organisations need to have is over whether the sending organisation will put the file on the receiving organisation’s computer, or whether the receiving organisation will collect the file from the sender’s computer. This ultimately is a question of what IT departments will allow. Either way, there is some trust involved, as one organisation has to open up their firewall for the other. Solutions are

(6)

•

Private Networks

For organisations where higher volumes of data need to be transferred (between a buying organisation and its e-Procurement provider, for example) a VPN (Virtual Private Network) will be put in place. This enables the private transfer of information using the public Internet. It works through recognising the electronic (IP) addresses of the sending and receiving computers. VPNs can be used to facilitate real-time systems integration and may utilise third party products such as IBM’s MQSeries to act as message brokers and guarantee delivery.

Integration

Web Services allow external pieces of functionality to be integrated into systems. The most common application of this within e-Procurement is the use of Punch Out. Punch Out technology allows users of an e-Procurement solution such as Oracle’s iProcurement to navigate and use external e-commerce web sites without leaving the e-Procurement solution. The benefits of this are that e-Procurement providers no longer have to replicate complex customer service algorithms or catalogues into their own solutions, and the buying organisation does not lose e-Procurement benefits such as integration with the finance system and authorisation.

(7)

3. Business Considerations

This section outlines the issues that organisations face when considering their data integration and interfacing options. Also highlighted are examples of data integration points and where specific issues apply.

Note that throughout the rest of this document both integration and interfacing are referred to generally as integration.

3.1 General Issues

•

Determine what your priorities are from a business case / benefit point of view. Generally, business cases for e-Procurement are built around process efficiencies and cost savings. Process efficiencies will be achieved only if non value-added tasks are removed from the organisation and not

re-introduced elsewhere. Cost savings will be achieved only if correct and complete management information is available to support timely decision making

•

Where duplication of data is necessary, organisations need to determine which system is the ‘master’ and which is the ‘servant’. For example, where organisations have more than one supplier database, how will they ensure that each database is updated with the same information when a new supplier is added, deleted or details changed. Where volumes are relatively low, this can often be resolved through the generation of exception reports and the use of manual procedures. For example, the e-Procurement system may allow new suppliers to be set up by users. Workflow could be set up to ensure that a ‘supplier manager’ is notified and the supplier created manually in the finance system before the invoice arrives. Alternatively, an interface could be built to enable the new supplier to be automatically created in the finance system, and workflow or reporting used to highlight changes on a daily or weekly basis, if required

•

How often do you need to integrate transactional data between systems? Avoid being pressured into ‘real-time’ integration. Organisations need to ask themselves what information is needed where. It may be possible to upload orders placed in the e-Procurement system into the finance system as soon as they are created, but what happens when the order is revised or cancelled. Integrating ‘real-time’ is complex, requires rigorous testing and is therefore costly. Consider integrating batches of completed orders into the finance system instead

•

Supplier Readiness. Integrating directly with suppliers and other external stakeholders should be considered. Organisations are at varying levels of sophistication, and integration strategies need to be established which will be inclusive. What do suppliers expect to achieve through integrating and what do you expect from them. Who will fund any development that the supplier needs to do to his web site or back office systems to support your business case

•

s only ting, so that issues are resolved and confidence is built. Keep integration as simple as possible

Avoid building in reconciliation activities. Integration and automation are designed to remove non value-add tasks from the procurement cycle. Reconcile using control totals and exception report where absolutely necessary. Ensure that interface programs are fully tested and piloted before implemen

(8)

•

In the nance system, but in the long term it will require more

•

ard statements, supplier statements, supplier analysis such as financial ratios or

•

s

rical information. For example, if two supplier records are found to be duplicates, can

•

spend with non-Purchasing Card spend without manual intervention

3.2

stive but is intended to a summary o integration could be consi

In most organisations multiple systems, databases and spreadsheets will be used to support Procurement. Organisations will need to consider automating the consolidation of data to support comprehensive business analysis and to identify trends, exceptions, opportunities and issues. short term this may be done using the fi

appropriate business intelligence tools

Consider what information suppliers and other third parties could provide electronically – bank sort codes, Purchasing C

market intelligence

Organisations will need to consider where to source data from prior to implementation. Where will list of users, suppliers, products/services, locations, cost centres, budgets, etc. be generated from, and what will the quality of the information provided be like. There may be significant data cleansing and resolution of issues needed before the base data can be uploaded. Where data is cleansed, what happens to histo

one be deleted

The most significant issue surrounding data integration is the use of coding and classification. Most solution providers have failed to address the key requirement of being able to compare like with like. Organisations need to be able to code suppliers, products/services, organisations, locations, countries, units of measure, users, etc. identically in all appropriate systems, including third party e-Procurement and supplier systems, even if this is through the use of alternative keys. Not having a standardised supplier coding system for Purchasing Card suppliers means that organisations cannot easily integrate Purchasing Card

Specific Integration Points

Note that this list of integration points by solution type is not meant to be exhau

show f where system dered:

Solution Inte ration Points - IN g Integration Points – OUT

Finance Systems

Including purchase ordering, Accounts Payable, Finance and Budgeting modules.

rs

y

• Accruals and commitments

s, cost ts, etc.)

g) curement)

• Management information

• Invoices and credit notes from supplie

• New suppliers (from e-Procurement)

• Purchase orders created externall

• Master file maintenance (supplier centres, budge

• Payments

• Remittances

• Invoices (to e-Procurement for matchin

(9)

Solution Integration Points - IN Integration Points – OUT

e-Procurement • Master file maintenance (suppliers, users, cost centres, authorisation hierarchies, etc.) from HR, Finance and other systems

• Catalogue maintenance from suppliers

• Invoice details (from suppliers or finance system) to support matching

• Stock availability from suppliers and internal stores

• Budget availability

• GL account validation

• Project accounting validation

• Order acknowledgements from suppliers

• Purchase orders to suppliers and back into the finance system

• Order revisions and cancellations

• Accruals and commitments

• New suppliers

• Punch Out to supplier web sites

• Internal stores

Buying On-Line • Supplier statements

Purchasing Cards • Statement information

• Payment to bank e-Sourcing

Including e-Quotations, e-Tendering, e-Auctions

Business Intelligence • Financial system data

• Inventory / stock information

• Purchasing Card statement data

• Supplier statements

• 3rd Party data feeds

• e-Sourcing history

• Supplier analysis

• Operational system information (social services, libraries, schools, housing, project

management, fleet management, etc.)

(10)

4. Issues and Risks

In addition to the general issues highlighted earlier in this document, the following should also be considered:

•

Organisations tend not to think through their data integration requirements at an early enough stage in a project. This results in objectives not being met and benefits not being achieved, as some of the principles cannot be retrospectively implemented (such as the implementation of procurement classifications, or re-structuring of the supplier database)

•

Be aware that system changes, including software versions, database and operating system versions and hardware versions, can impact integration

•

Organisations need to be clear on the business case for each integration point – it is often the case that these are the areas that are put under pressure due to demands on resources

•

Determine what the costs of not integrating will be. These will include loss of management

information, duplication of effort, requirement for reconciliation, re-keying, perception of failure and multiple versions of ‘the truth’

•

underestimate the development, testing and piloting required for complex integration requirements

•

nsing requirements, especially if legacy systems and procedures are currently poorly implemented

•

ay

repare catalogues or develop systems and web sites to support your integration requirements

•

d versions with suppliers and system providers, or plan to be able to make automatic conversions

Project teams should not

Do not underestimate the data clea

Suppliers may not share your reasons for integrating. Do not underestimate the work that they m have to do to p

At the earliest stage decide on appropriate coding and classification to support the ease of data transfer. Wherever possible, agree coding and classification standards an

(11)

5. Conclusions

•

Keep the number of integration points to a minimum in order to deliver business objectives. Do the main, high volume / high impact bits first, then plan to remove manual integration points as required

•

Ensure that you understand the risk to business objectives of not integrating

•

Accept that some manual interaction is required and set expectations early

•

Make early decisions and plan well – decisions made later on are expensive

•

and

ppliers as this is a rapidly changing area with new solutions and approaches being developed

•

Make sure that changes to roles and responsibilities are well defined and managed

•

ycle but maybe external to the organisation, such as purchasing consortia or supplies organisations

Take as much advice as possible from all system providers, from other customers / user groups from your su

Make sure that you consider integration issues between stakeholders that may be a key part of the procurement c

(12)

6. Links to Other Documents

The following web sites and documents provide useful additional information on this subject:

•

BASDA e-Business, An Overview (www.basda.org)

•

CIPS e-Business Security (www.CIPS.org, access restricted to CIPS members only)

•

Maximising Returns from Purchasing Data - Informed Business Decisions from Coding and Classification (www.CIPS.org)

(13)

Prepared by:

Strategic Procurement Services PO Box 58 Prudhoe Northumberland NE41 8ZA www.strategicps.co.uk [email protected]