Transcript Ariba Spend Visibility Data Schema Overview

Ariba Spend Visibility
Data Schema Training
Tomkins
May 17, 2010
Keith Luers, C.P.M.
SV Project Mgr
A R I B A® C O N F I D E N T I A L
2007
Agenda
Brief overview of Spend Visibility project as
needed
High level Data Schema overview, Scope &
Process Suggestions
Detailed review of Data Schema
Data Overview Policy
Questions
A R I B A® C O N F I D E N T I A L
2007
Spend Visibility Overview
Any outstanding questions from the SV
overview session?
A R I B A® C O N F I D E N T I A L
2007
What is a “Source System”?
SV Source System
A standalone system that will be accessed to
provide actual payment transactional data
The creation of a complete set of data may require
feeds from multiple types of systems to augment
the Accounts Payable data
Need a complete listing of source systems that
need to be built along with who needs capability to
load in data
A R I B A® C O N F I D E N T I A L
2007
Scope of Extract – These questions need to be
answered before an extract can be generated
 What constitutes an Accounts Payable (AP) Spend Event (Approved Invoice entered for
eventual payment; Invoice receipt, Invoice payment, PO receipt)?
 *What types of transactions/spend should be included/excluded?
 Invoice supported by PO
 Invoice without supporting PO
 P-Card
 Expenses
 Check Requests
 Wire Transfers
 Intra/Inter-Company
 Payroll (should probably be excluded from extract)
 Other Transaction Type(s)
 Which ERP date field should be used to determine transactions that are in scope/out of scope?
 Which timeframe should the extract include?
 How should taxes be handled?
 Is it necessary to flag Direct vs. Indirect spend?
 Is it necessary to flag Internal vs. External Suppliers?
 Are there specific requirements for enrichment we should be aware of upfront?
*In Analysis, is it necessary to be able to track these transaction types?
A R I B A® C O N F I D E N T I A L
2007
Suggested Extract Process
1. Define Business Requirements/Scope (see previous slide)
2. Develop Mapping Document (provided)
 This document should include all the fields of the Ariba Data
Acquisition Schema and mapped (if used) to the client ERP fields
 Any filters used should be described
 This document should be the basis for development of the script
itself
 Provide finalized document to Ariba for the review to ensure critical
data elements are included in the extract files
3. Develop Script
4. Generate Test Files and upload to Spend Visibility site (narrow
date range, this serves to ensure correct formatting/table joins,
etc.)
5. Generate the production files with the complete set of the data in
scope once any issues from Test Files are resolved
A R I B A® C O N F I D E N T I A L
2007
Ariba Data Acquisition Schema
Provides a roadmap to create files in the
proper format
Helps define the data to include for upload
Provides technical “do’s” and “don’ts” for the
data extraction
Defines the various tables and fields, and the
requirements for their successful creation
Is the primary and final reference for all
matters relating to data extraction and file
creation
A R I B A® C O N F I D E N T I A L
2007
Data Schema Relationships
Schema consists of one main table
(Invoice2.csv) and many supporting tables
Enrichment is based on Invoice data and
supporting tables used to define attributes of
invoice data
A R I B A® C O N F I D E N T I A L
2007
Data Flow
 List of tables/files include:



















"Invoice2.csv" for AP transactions table
“PO2.csv” for Purchase Order Line transactions
“Account.csv” for General Ledger accounts table
“CompanySite.csv” for Facility/Site location Master table
“Contract.csv” for Contract Master table
“CostCenter.csv” for Cost Center Master table
“CostCenterMgmt.csv” for Cost Center management hierarchy
“ERPCommodity.csv” for the Material Code Master table
“FlexDimension1.csv” for other related dimensions
“FlexDimension2.csv” for other related dimensions
“FlexDimension3.csv” for other related dimensions
“FlexDimension4.csv” for other related dimensions
“FlexDimension5.csv” for other related dimensions
“FlexDimension6.csv” for other related dimensions
“Part.csv” for Item Master table
“Supplier.csv” for Supplier Master table
“User.csv” for the Buyer Master table
“CurrencyMap.csv” for mapping Currency codes
“UOMMap.csv” for Item Master table
A R I B A® C O N F I D E N T I A L
2007
A R I B A® C O N F I D E N T I A L
2007
Data Flow
Data extracts from each data source will be
loaded from a single ZIP file named for the
source system.
The zip file should include individual text files
named exactly as listed
Files uploaded as .csv can have any name
A R I B A® C O N F I D E N T I A L
2007
Data Field Conventions
 Each file must have a header row containing the data element field
names as specified in the Data Acquisition document
 Data must be provided in comma delimited format
 Double-quotes (“) are used at the beginning and end of each field
 Double quotes imbedded within the data should be escaped with
second double quotes (“replaced with “”)
 The comma delimiter is still necessary where no information is
available – null values
A R I B A® C O N F I D E N T I A L
2007
Example of Field Conventions
Original Data
Extracted text
Field 1
Field2
Field3
CSV formatted
10
100
screwdriver
“10”,”100”,”screwdriver”
(Null)
(Null)
screwdriver
“”,””,”screwdriver”
10
100
hex ½”x4” cap
screw
“10”,”100”,”hex ½"”x4"” cap screw”
A R I B A® C O N F I D E N T I A L
2007
Data Field Types
 ‘Text’ for alphabetic (a-z and symbols including the special
characters and digits)
 ‘Number’ for digits 0 to 9
 Negative values are expressed with negative sign (“-“)
preceding the number
 Use a period (“.”) to denote decimal amounts
 Currency marks ($ for example) should NOT be used in
numbers
 ‘Date’ in the format yyyy-mm-dd where yyyy, mm and dd are
appropriate integer values for year, month number and date
number, respectively
 Example: 2005-05-04 represents 4 May 2005
• Note: zero padding required for month and days
A R I B A® C O N F I D E N T I A L
2007
Use Data Acquisition Schema documents for
detailed Review / Notes Taking
Mapping guide & Schema instructions
A R I B A® C O N F I D E N T I A L
2007
Common Data Collection Problems
 Duplicate Records
 Invalid joins between supporting tables
 Different ID structure
 Missing data on supporting table
 Commas/Double Quotes in data
 Incorrect date format (YYYY-MM-DD)
 Schema mapping is not tracked
 Huge impact if the extract is manual labor intensive
 Prevents timely refreshes
 Prevents new team members from coming up to speed
quickly
 Forget currency conversion load if applicable
 Confirm Fiscal Year settings in site
A R I B A® C O N F I D E N T I A L
2007
Overview of Data Policies
 The Ariba Project Manager will offer support and
guidance when data changes are necessary, but
due to data security policies, the Ariba Project
Manager is not be able to manipulate or update
data. It is the responsibility of the customer to make
changes to their data.
 Common Requests:
 Populate Flex Field after deployment
 Flag invoice and supplier type
 Fixing hierarchy errors in data
 Any data problems that cause load error
A R I B A® C O N F I D E N T I A L
2007
Action Items
Complete list of needed source systems to
have built in SV system and corresponding
Tomkins associates who can load in data
Provide complete zip file
Presentations
 Data Schema mapping
 Data Schema instructions/file structure
Sourcing systems listing
 Upload instructions
A R I B A® C O N F I D E N T I A L
2007
QUESTIONS?
Keith Luers,C.P.M.
[email protected]
719 260 6333
A R I B A® C O N F I D E N T I A L
2007