Paper No. Satyam/DW&BI/00 6

A Satyam White Paper

Leveraging thepower ofUNSPSC forBusinessIntelligence
Author:Anantha Ramakrishnan
Ananth_Ark@onsite.satyam.com

Leveraging the power of UNSPSC for Business Intelligence

Introduction
The Universal Standard Products andServices Classification (UNSPSC) was
created for coding of products andservices unambiguously according toindustry-agreed naming standards for
streamlining
commerce
among
companies, particularly over internet.
The development of new technologicalcapabilities such as XML and searchengines help to populate productidentificationtagstocatalogs,directories and enterprise applications
that are operated by manufacturers,
purchasinggroups,
customers,
distributors and other channel partners.
The Meta data standards for productinformation benefit all players in asupply chain.
This white paper attempts to analyze theusefulness of UNSPSC code forstandardizing Product information andensuring data quality in a data
warehousing environment

What is UNSPSC?
UNSPSC is available free for use to thepublic with no copyright restrictions. The
Uniform Code Council (UCC) is thecode manager for UNSPSC, responsiblefor ensuring compliance with theprinciples of the UNSPSC as well as theintegrity of the code schema.

Page 2 of 8

UNSPSC is an open, global electroniccommerce standard that provides logicalframework for classifying goods andservices. The hierarchical structure of
UNSPSC allows drilling down and rollingup for tactical and strategic spend
analysis. UNSPSC is designed to serve
three primary functions:
Spend Analysis – Uniform view of all
spend across the enterprise
Resource Discovery – Identification ofrelevant suppliers for specific product orservice
Product Awareness – Allows listing ofsimilar products and services under asingle categoryUNSPSC began as a merger between
United Nation’s Common Coding
System(UNCCS)
andDun&Bradstreet’s Standard Product andService Codes (SPSC) in 1999. As ofNov’02 D&B and Electronic CodeManagement Association (ECCMA)
merged.

Leveraging the power of UNSPSC for Business Intelligenceextended by adding ninth and tenth digit
after commodity to indicate the business
relationship to the supplier. Current
version
6_0501
contains
18000
commodity level codes.

UNSPSC Taxonomy
UNSPSC is a hierarchical classification
having four levels. Each level contains atwo character numerical value and adescription. UNSPSC can be further

Segment

44 - Office Equipment and Accessories and Supplies

12 - Office supplies

FamilyClass

17 - Writing instruments

Commodity

010203040506070809
10111213
1415161718

Roller ball pens
Pen or pencil sets
Fountain pens
Ball point pens
Mechanical pencils
Wooden pencils
Colored pencils
Markers
Crayons
Writing chalk or accessories
Felt pen
Marker refills
Pen nibs
Pencil or pen grips
Combination pen or pencil
Highlighters
Combination pen and highlighter
Secured pen sets

In the this example UNSPSC code 44121704 identifies the category “Ball point pens ”

Page 3 of 8

Leveraging the power of UNSPSC for Business Intelligence

Basic Design Guidelines
In situations where there are many uses
or many sources for the samecommodity, the classification reflects thedominant use of the commodity within
the global marketplace. Dominant use
varies over time and by country orregion. Every effort shall be made toreflect the international use of acommodity, as opposed to regional orindustry-specific applications
Rule 1: A commodity will be classified
with others that serve or support acommon function, purpose or task.
These segments, families and classesof commodities are normally found in
the same district, store or aisle.
Rule 2: A commodity will be classified
with others made by a similar process
and often manufactured by the samecompany or a similar company.
Rule 3: If the first two rules do not apply,
then the material from which thecommodity is made will help todetermine its classification.

Naming Conventions
Rule 1: All hierarchies are explicit; there
are no implied hierarchies.
•••Commodity titles are not to be used
to imply a hierarchy
Commas are not to be used incommodity titles (the term ‘tennis
balls’ is an appropriate commodity
title but not ‘balls, tennis’)
The order within any given group isnot significant; there are no impliedhierarchies

Rule 2: Commodity titles are unique.

Page 4 of 8

In commodity titles, the conjunction ‘and’implies both components of a title must
be present.
Rule 3: At the class, Family andSegment level the conjunction ‘and’should be used. The ampersand (&)
symbol should not be used.
••

In the Segment, all keywords haveleading capitals.
In the Family, Class and Commodity
only the first word is capitalized.

Benefits••••••••

Helps to get comprehensive insight
into spend landscape and strategicplanning
Enables supplier rationalization anddemand aggregation
Integrates entire procurement flowfrom RFP’s to ordering, to accounts
payable, to general ledger.
Allows “Drill Down” and “Roll up”
analysis of purchase transactions
Aligns supply chain and increases
value chain efficiency
Supports multi-language uses
Increases e-procurement efficiency
and effectivenessSupports analysis with BusinessIntelligence tools

Why UNSPSC for Data
Warehousing?
In a data warehousing environment,
where data is to be extracted frommultiple transactional systems like ERP,
Purchase, Accounts Payable, Legacy
systems and multiple business entities
across the globe, there is a need for acommon standard Dimension to perform
analysis on an Enterprise wide basis.
Data warehousing team has to maketremendous effort to build a ProductLeveraging the power of UNSPSC for Business Intelligence
related Dimensions with hierarchies andcross references.
UNSPSC relieves the pain of building
and maintaining a Product Dimension by
providing data in a consistent schema.
Classification tools are available toautomatically apply UNSPSC codes
initially and for the new codes wheneverit enters data warehouse. Its metadata
structure is perfectly suitable foranalysis by OLAP tools.
UNSPSC is available in multi-languages
like English, French, German, Italian,Japanese and Spanish. Russian,Germanand
Koreanlanguagetranslations will also be available infuture. Having a unique code allows
unambiguous
translationof
thecommodity’s description into any
language and provides enterprise viewfor the data.

UNSPSC
CodesVsInternal

Material

UNSPSC’s
Hierarchicaltaxonomy
allows Roll up and Drill down analysis
and the user can get into the “Level ofSpecificity” that best suits to hisbusiness. Internal Material codes will
have limited or non-existent hierarchy.
UNSPSC helps to perform tactical andstrategic sourcing at many different
levels and helps to integrate thebusiness and eliminate undisciplined
buying activities where as Internal codes
can help only to take operational
decisions.

Page 5 of 8

UNSPSC adds an enterprise-wide viewto the spend analysis. This helps better
Supplier management and to negotiate
volume based discounts. This issupported in a limited way by internal
codes.
UNSPSC schema is available in multilanguages , which helps implementation
across the globe. Internal codes will notsupport multi-language uses.
UNSPSC
increases e-Procurement
efficiency by inserting codes in various
electronic trade documents and media
such as product catalogs, web sites andcomputer applications throughout an
extended supply chain. Internal codes
will extend minimum support to eProcurement.
UNSPSC enables buyers to identify
suppliers for a particular category;where as limited support is available
with internal codes.
UNSPSC requires upfront effort to apply
codes but it can be automated with atool. Comparatively the effort will be
more to apply internal codes.

Implementation
UNSPSC can be implemented in a data
warehouse with minimal/no impact tooperational systems. The process ofclassification has to be integrated with
ETL process in the staging area. An
indicative process for implementing
UNSPSC in a Data warehouse with anautomatic classification tool is described
below.

Leveraging the power of UNSPSC for Business Intelligence

DataSources
ERP

Staging Area
Check fortransactions
without UNSPSC

LegacyAPPrepare and Senda file with Productdescription /attributes andDimension ID

Log error for
unclassified anditems <confidence level
for manualclassification

Receive UNSPSC
codes and update
records >prescribed
confidence level

POClassification Engine
Feed Productinformation intoclassification toolClassify and apply
UNSPSC code
along withconfidence level

Page 6 of 8

Prepare and Send
UNSPSC codes,
with Dimension ID

Data
WarehouseLeveraging the power of UNSPSC for Business Intelligence

Case Study: A Study for the Adoption of UNSPSC
Our client is a Fortune 100 com pany
and world's leading manufacturer ofconstruction and mining equipment,
diesel and natural gas engines andindustrial gas turbines. The company isa technology leader in construction,transportation, mining, forestry, energy,logistics, electronics, financing andelectric power generation. The client isin the process of building a Data
warehouse for Global Purchasing
Division to have an enterprise-wide viewof spend analysis. The main objective isto perform strategic sourcing for Cost
Reductionandbetter
Suppliermanagement.
As a part of Six Sigma initiative for Data
Quality in the data warehouse, the client
wanted to evaluate the adaptability ofUNSPSC categorization for theircommodities. Satyam carried out a pilot
project to help them in making thedecision.
Project Scope
The scope for the project includes:Excel Sheets

Access DB•••••

To collect sample Item data fromdifferent category managers for
direct materials
To categorize them using UNSPSC
To develop a Spend cube with across walk table
To publish the Cube in the intranetsite for evaluation by users
To evaluate the suitability ofUNSPSC.

The Process
Following representative data forcategorization was collected fromdifferent Material category teams likeRough Casting, Machined Casting, CAB
Assembly, Steel and others.
• Part Number and Description• Material code and Description• Family codes and Description• Geometric codes and Description• Buying and Receiving Facility• Supplier• Annual Spend• Estimated Annual Usage

Cognos CubeBus Info Portal

UNSPSC data

The data from different Material
Category Teams were collected in excel
spreadsheets and consolidated into
Access tables. UNSPSC codes wereassigned to each record. An UNSPSC
dimension table was created with theUNSPSC category data V6.0501
downloaded from www.unspsc.com. APage 7 of 8

spend cube was built with UNSPSC,Material, Family, Data Source andFacility dimensions. The cube waspublished in the Business Information
portal. For indirect materials, the client
has decided to go in with UNSPSC andfor direct materials the evaluation isunder progress.

Leveraging the power of UNSPSC for Business Intelligence

Conclusion
Adoption of UNSPSC has clear advantage especially in a Data warehousing
environment in standardizing the item data and ensuring Data Quality. Its
hierarchical structure helps to model the data in a multi-dimensional format and toget a global view of the Spend. In some cases even at the commodity level, thecode is not granular enough to handle detailed analysis. This problem will getresolved when more and more companies adopt UNSPSC and contribute toindustry specific commodity levels.

References
1. Put UNSPSC to work for you – D&B
2. Using the UNSPSC – Granada research
3. Adopting UNSPSC – Zycus

About the Author
Anantha Ramakrishnan is a Senior Project Manager with Satyam Data
Warehousing and Business Intelligence Group. He is a certified ProjectManagement Professional (PMP).
Ananth handles project management, presales and competency building activities
for the group in USA. He has over 15 years of experience in Application
Development, ERP implementation and Business Intelligence. Prior to Satyam,
Ananth was a Senior Executive - Business Planning & IT with TVS Electronics.
Ananth can be contacted at ananth_ark@ onsite.satyam.com

Page 8 of 8