better productivity in municipal government: a purchasing ... · csi-piemonte is a public agency...

Better Productivity in Municipal Government: A Purchasing

System Dashboard

SAS Forum 2007

Stockholm, May 22 – 23

Andrea MuracaCSI-Piemonte

Stockholm, May 22 – 23 SAS Forum 2007

CSI-PiemonteMunicipality of TurinApplication ContextProject’s GoalBusiness SolutionBenefitsConclusions

Agenda


PromotingBodies

SupportingBodies

Ordinary Local AuthoritiesBodies (8 Provinces, 15 Municipalities, 5 Agencies, 19 Health

Institutions and Hospitals, 1 Mountain Community, 1 Association of Municipalities)

CSI-Piemonte is a Public Agency with the legal status of a Consortium owned by several public stakeholders of the Piemonte

area, acting in the field of information technology and telecommunication since 1977

Universityof Turin

Polytechnicof Turin

Cityof Turin

CSI-Piemonte Company Profile


Piemonte Region facts and figures

• Located on the north-west of Italy, bordering with France and Switzerland

• Capital city: Turin

• 4,5 Ml inhabitants

• 8 Provinces

• 1.206 Municipalities/Towns

• 8,7% contribution to the national GDP

• 5,3% unemployment rate


To aggregate public demand, in order to create scale economiesand make room for ICT companies, favouring the economic and productive development of the territory

To be a “system integrator” acting as a neutral element withreference to single administrations, in order to help them provideservices to citizens and businesses

To transfer academic competences in public planning, in order toupdate PA technological skills over time

CSI-Piemonte Mission


CSI-Piemonte Facts and Figures

CSI-Piemonte main figures:

• 54 Associated Public Authorities and PA offices

• Annual revenue 181.7 millions € in 2005

• 8 sites in Piedmont

• Over 1,200 employees

CSI-Piemonte is the largest Italian ICT companydeveloping e-Government projects financed by the Italian Central government (source Datamonitor)


The CSI-Piemonte Model

CSI-Piemonte represents a unique player in terms of it being a ‘public’

consortium organized along ‘private’ lines.

Thanks to CSI-Piemonte, Piedmont is the only Italian region with an ICT shared service center for the local

government able to implement“inter-authority” information

services anddata sharing


E-GovernmentAgricultureTerritory managementEnvironment monitoring and disasters preventionHealth-care systemsDemography, Fiscal Administration and CadastreMunicipality automationBudget and administration Systems Labor and job creation sector activities Cultural heritage and schools

CSI-Piemonte sectors of activity

CSI has a long experience in developing, maintaining and operating the Public Administration’s information systems, both at local and national

level, dealing with different topics and sectorial competencies:


CSI-Piemonte is:

A SAS customer in Italy for more than 20 years

The European best practice in Public Administration SAS applications

The SAS number 1 customer at European scale for variety of toolsused

A pool of more than 70 skilled staff on SAS technology

A SAS 9 competency center

One of the first companies signing the accreditation process to the EMEA Professional Services Partner Program

The owner of a SAS R&D laboratory set up with Piedmont Regionand Politecnico di Torino

CSI-Piemonte Partnership with SAS


The CSI-Piemonte SAS V9 competency centre (BICC)

Skills by Area:

Decision Support Systems design and development

Decision Support Systems for HR management, Strategic

control management

Metadata management, Data re-usage Standards

Catalogues, Cross-area Data, Production Activities

Observatories

Health Care Decision Support Systems

Data Quality and Data, Text Miner


Data -> Knowledge ->DecisionTerritory and Environment

Land Register and TaxesDemography

Health and Public AssistanceDatabases

Libraries and Cultural HeritageAdministrative-managerial processes

JusticeAgriculture

Labour and EducationProducing activities

Internet and ICT

DA

TA

DA

TA

DA

TA

DA

TA

DA

TA

InformationsKnowledge

Decisions

DA

TA


How many databases today in Piedmont PA?

DECISION MAKING

OPERATIONAL

Piedmont Region 108 574 682Province of Turin 23 137 160Municipality of Turin 31 228 259Others 11 29 40TOTAL 173 968 1.141

TYPE OF DBPA CUSTOMER TOTAL

TOPICS DBAgriculture 125Territory 67Environment 88Health-care systems 183Productive activities and work-related topics 232

Demography, Fiscal 125Administration Systems for Public Offices

321

TOTAL 1.141


The ETL Process (Extract, Transform & Load)

Data Staging AreaData Staging Area

aggregation

classification

validation

standardization

(Enterprise) Data Warehouse

(Enterprise) Data Warehouse

Data MartData Mart Data MartData Mart

Data OLTP

System

Extraction Transformation Loading

Metadata

Cleaning

External Data


History of Decisional Systems at CSI-Piemonte

Time

1980 1985 1990 1995 2000 2005

Report on mainframe

Application on Mainframe

Stand-alone applications

Client-serverapplications

Web applications

Palm services


Data Access

Process Type

Display Analyse Find

Users

Manager

Responsible Analyst

Clerks

Time

Dashboard Indicator

Query / Reporting

Data MiningOLAP

Multidimensional Analysis


The different customers and the main projects activities:


Inhabitants of the City:Male 431,562Female 469,424Total 900,986

Employees (permanent)Male 3,906Female 8,278Total 12,184

Municipality of Turin Figures


Application Context

Large dimensions and organizational complexity of the municipality’s machine increase the risk of losingtouch with the global vision of the PurchasingSystem

Diseconomies of scale

Increase of burocracy of the purchasing process


Application Context

In particular, all the purchases of the Municipalityare organized according to a mixed modality:

Generic purchasesby the Treasury

Office

Specifical purchasesby the otherDepartments


Application Context

Therefore we need to understand:

how certain purchasing methods have spread

whether it would be better to make anychanges to the purchasing process, whereverdiseconomies or new needs are found

how to manage the screening of the purchasing system in the most effective way


Project’s Goal

Development of a Purchases Screening tool, which can establish:

Who buys

What they buy

How they buy

How much they spend

From whom they buyBudget Database

ActsDatabase


Who Buys

From the partially structured texts of the Acts we have drawn out the Departments and the Sectors in charge of the purchasethrough the search of keywords


How they buy

The same thing has been done to look for the PurchasingModalities

= “Public Auction”


How they buy

Private NegotiationN.D.

Other

Direct Entrusting

Contract Competition

Private Auction Sale

Auction

Consip

Contract Competition

Auction

Private Auction Sale

Private Negotiation

Consip

Direct Entrusting

N.D.

Other

Public Tenders

Private Tenders

(*)

(*) Italian Government eProcurement agency

(*)


How they buy

Aids

Representation and Events Expenses

Associations and Foundations

Rents and subscriptions

Competitions

Tickets

Petty and Urgent Expenses

Compensations for Damages

Insurance Covers

Conventions

Funds and Benefits

Stamp Duty

Assignments and Appointments

Interests Allowed and Loans

Legal CostsRefunds

Hires

Advances

Grants

Advances

Associations andFoundations

Tickets

Rents and subscriptions

Grants

Competitions

Aids

Conventions

Insurance Covers

Funds and Benefits

Stamp Duty

Other


What they buy

The information on the objects of the purchases is contained in the text body of the Acts, fully not-structured


What they buy

36.339Commitment

ActsText Mining

Goods Groups

The best tool to be used in such a situation is TextMining, which is able to classify in a semi-authomatical way the Acts into homogeneous groupsaccording to their content


What they buy

Text Mining has found 32 homogeneous Groups of Acts

ID GOODS GROUPS1 CLOTHES AND ACCESSORIES2 AQUARIUS, CATS AND DOGS3 ANTI INJURIES4 COMPUTER DEVICES5 WEAPONS AND MUNITIONS6 GOODS FOR CEREMONIES AND DEMONSTRATIONS7 GOODS FOR PACKING AND STORAGE8 STATIONERY9 FUELS10 HOUSEHOLD APPLIANCES11 PHOTOGRAPHY12 LAMPS - EQUIPMENTS FOR LIGHTING13 BOOKS - MAGAZINES - PUBBLICATIONS14 TYPEWRITERS - CALCULATORS15 PRINTING MACHINES16 FUNERARY GOODS17 IDRAULIC GOODS18 BUILDING GOODS19 CLEANING GOODS20 ELECTRICAL GOODS21 MEDICINES - CHEMICAL AND HYGIENICAL PRODUCTS22 LIFTING MEANS23 FURNITURE24 PLANTS AND GARDENING GOODS25 CHEMICAL PRODUCTS FOR PAINTING26 RADIO & VIDEO27 SERVICES28 TRAFFIC SIGNS29 MEASURING TOOLS30 TELEPHONES AND ELECTRONIC COMPONENTS31 TISSUES AND LINEN32 VEHICLES


The not-structured informations (who-how-what) havebeen tidied up into a structured decisional database

ID DTI COD DIVISIONE DESC DIVISIONE DESC SETTORE MODALITA'

D'ACQUISTOGRUPPO

MERCEOLOGICO

2002-00563 005 ECONOMATOServizio Centrale

Contratti, Appalti ed Economato

Trattativa Privata CANCELLERIA

2004-56897 011 SISTEMI INFORMATIVIServizio Centrale

Controllo Strategico Direzionale

Divisione Patrimonio, Partecipazioni e Sistema

Informativo

APPARECCHIATURE INFORMATICHE

… … … … … …

…to this we’re going to add the information takenfrom the Budget Database as well

What they buy


How much they spend

COMMITMENT ACTS

(purchases database)

PAYMENT ACTS

(budget database)

COMMISSION ACTS

(budget database)


How much they spend & From whom they buy

ETL flow from the structured data of the Budget Database

Budgetxxx,xxx.xx €

SUPPLIERS


Business Solution

Used tools:

SAS Enterprise Miner(Text Miner)SAS Data IntegrationSAS WebReportStudio

Commitment Acts

Payment Acts

Amounts, Suppliers

Text Mining

Keywords Search

Query and

Reporting

Acts DB Budget DB


Business Solution: SAS Enterprise Miner


1.369.582Terms



Books- Newsp.-Magazines

CenterLibrary

AnnouncementMunicipal. newsbulletin

1.369.582Terms



Business Solution: SAS Data Integration


Business Solution: SAS WebReportStudio

Number of Commitment Acts forDepartment and Goods Group



Purchase of Goods for Events by the differentDepartments of the Municipality



Stationery’s purchases by the differentDepartments of the Municipality


Business Solution: the Municipality’s data warehouse

During these years the Municipality of Turin hasbuilt a complex decisional system in order to

support the Administration



RegistryData marts

Operational databases

DA

TA A

ND

SER

VIC

ES C

ATA

LOG Management Control

Decisional System/Budget

Polic

e

Com

mer

ce

Bui

ldin

g

Wag

es

Pubb

licW

orks

Taxe

s

Educ

atio

nal S

ervi

ces

Bud

get

Trai

ning

Abs

ence

sPre

senc

es

Citi

zens

Reg

istr

y

Sectorial Data marts

EmployeesEconomical and productive Activities

Citizens

Territory (georeferences)

Infodirand Sitad

Access (statisticaland territorialdata)

Catalog’ssearch and consultation

Metadatawriting out and pubblication



The Decisional System is placed on the top of the pyramid as a tool of management control


Benefits

Updated visualization of the overall trend of the Purchases

Evaluation of any possible reviews of the Purchasing System, considering new areas of centralized purchase

Growth of Scale Economies, and therefore of the assets of the Municipality


Conclusions

This Screening Tool will help the Management of the Municipality to control the complexPurchasing System and will make it possible tohave a better collaboration among the Departments


Thanks for your attention

[email protected]

www.csi.it

better productivity in municipal government: a purchasing ... · csi-piemonte is a public agency...

Documents