Ariba Spend Visibility Data Schema Overview
Download
Report
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