Data Warehouse for Piedmont non-profit services: Piedmont Region, Municipality of Turin and Piedmont Schools

1
Budapest, January, 21th

• Data Warehouse for Piedmont non-profit services
  Piedmont Region, Municipality of Turin and
  Piedmont Schools

Guido Albertini Head of International Projects
Directorate Marco Boero Decisional System
Development, CSI-Piemonte
2
Agenda

• Some information on CSI-Piemonte
• Focus on the Business Intelligence Competency
  centre
• Who we are
• What we do
• Non-profit services

3
Who We Are
Regional-based inter-University
Consortium and Leading Italian ICT operator
4
Main figures

• 75 Associated Consortium Members
• Annual revenue 177 millions in 2008
• 6 sites in Piedmont (1 in Brussels)
• Over 1,200 employees
• CSI-Piemonte is the largest Italian public agency
  developing e-Government projects financed by the
  Italian Central government
• CSI-Piemonte has a company Quality Management
• System certified according to UNI EN ISO
  90012000
• (Vision 2000) standards

5
CSI-Piemonte Mission
Building the Piemonte Systemto be a system
integrator acting

• to implement the quality of the public action
  (administrative decentralization)
• to simplify the public procedures
• to promote the transparency and the
  participation
• to foster data-exchange and integration of the
  PA through integrated information services

through the use of information technologies
6
Areas of Activity

• CSI-Piemonte, plans, develops and manages
  Information Systems for all Public Sector
  ACTIVITY AREAS, eg

• Agriculture and forestry 

• Environment and territory 

• Manufacturing activities and labour  

• Culture

• Land registry and taxes 

• Education and professional training

• Healthcare

• Administration systems
• .

7
International Activities
Some international projects under way

• Business Intelligence consultancy for the
  Province of KwaZulu-Natal, South Africa
• EU MEDA - Twinning Tunisia - Development of the
  Statistical Information System on Enterprises in
  collaboration with the Tunisian Statistical
  Institute (INS)
• Business Intelligence consultancies in the
  banking sector in Cyprus and Turkey
• Interreg IV C Territorial cooperation B3 Regions
  to transfer the acquired know how on programming
  and development of regional broad band programmes
  to the New Member States
• EU MEDA - EuropeAid project for the Ministry of
  Water Resources of Algeria on study, training,
  and technical assistance for document management,
  setting up of an information system and a
  database for the water resources
• EU MEDA - Twinning Tunisia - Strengthening the
  capacity of Regional Development Organizations
• EU PHARE - Twinning Poland - Local Government
  Budget Management System
• EU PHARE - Twinning Hungary - Environmental
  sector
• FP 6 Information Technology Society Picture
  (Process Identification and Clustering for
  Transparency in Reorganising Public
  Administrations)
• FP 6 Information Technology Society HENVINET
  (Health and Environment Network)
• FP6 RDT LUNA Natural speech recognition in
  different languages for CRM services

8
Data -gt Knowledge -gtDecision
Knowledge
update October 2008
9
From IDCs article
Business Intelligence

• Public sector executives are under high pressure
  to raise productivity of government operations to
  release resources for better public services ...
  the role of business intelligence becomes crucial
  to discover, analyze and forecast levels of
  spending, resource utilization, and usage
  patterns of services and thus feedback
  information into decision process for service
  improvement.

10
From IDCs article
Business Intelligence

• The strategic role that business intelligence
  acquires in the government modernization process
  will prompt executives to adopt new
  organizational and technological solutions to
  face these challenges

11
History of Decisional Systems at CSI-Piemonte
12
Data -gt Knowledge -gtDecision
Health, to support the Piedmont Health Bodies in
the daily management activities, as for
exampleDecision making system on pharmaceutical
prescriptions, Health activities control
indicators check indicators of family doctors
prescriptions, Oncology screening Indicators,
Epidemiological Observatory (based on demographic
data), Executive Reporting of Public Hospitals,
Accounting and cost/revenues management system
for Public Hospitals,
Observatories for Education, Labour, Industry
Labour Market, Commercial Network, Mountain,
Handcraft Activities,
Human resources Salary reports, Dashboard of
human resources allocation, Skills,
Criticalities, Salary paths, Resources
development, Reports on personal demographic
data, Re-organization simulations.
Demography Statistics Demographic Evolutive
Data Bank, Questionnaires on the quality of life,
Statistics on population registry office Data
Quality Data quality assurance (DQA) in order to
verify the reliability and effectiveness of
data Metadata Catalogue to describe all the
business and technical metadata and services
Services Control Trade acceptance files,
accidents, offences, penalties contentious,
tourism and sport files, housing
files Management Control Financial indicators,
account plans, policies Strategic Planning
Dashboards Indicators and balanced Scorecards
Agriculture Agriculture Census Observatory,
Agriculture development plan monitoring,
13
Target of our applications

• For more interactive customers
• Services check across the local area
• Municipality Management Taxes, Municipal Police,
  Electoral Observatories
• Services Control Fiscal fraud, Trade acceptance
  files, accidents, offences, penalties
  contentious, tourism and sport files, housing
  files

• For more strategic customers
• Project on monitoring and political evaluation
• Financial indicators, account plans, Social
  policies
• Observatories
• Education, Agriculture Census Observatory,
  Agriculture development plan monitoring, Industry
  Labour Market

• For all
• Dashboards Indicators and Balanced Scorecards

14
Sharing Information

• Unique centralized platform for everyone
• Single software platform for all customers
• Shareable databases
• Both One2One and cross-Customer applications
• But mainly CSI-Piemonte has a Business
  Intelligence Competency centre (BICC) with
• Statistical know-how and support centre
• Standardised and reusable software

15
The BICC at CSI-Piemonte
PA Institution 3
PA Institution 2
PA Institution 1
B I C C
PA Institution 4
APPLICATIONS
DATA
SOFTWARE
INFRASTRUCTURE
16
Supporting Actions Offered by the Organization
Vendor contract management Future trends of BI
architecture Vendor contracts management, user
licenses administration
SupportCross area team to support front-end and
back end activities
Training plans Upgrading applications/platforms
according to new releases in a centralised way
BI Program Matrix organisation Methodology for
decisional applications life-cycle design and
management DW/BI Standard and best practices (18
doc.)
BI Delivery (214) standard front-end custom
front-end (java-jsp)
Data Stewardship Company metadata management Data
quality (29) Data re-use support Classifier
and codes management
Data acquisition New ETLs developing ETLs
management (155) ETL for data massive migrations
Advance analytics Data and text mining
techniques, statistical modeling, optimization
for PA
17
Examples of non-profit services

• Piedmont Region for Piedmont Schools
• Municipality of Turin for citizens

18
Examples

• Piedmont Region for Piedmont Schools
• Da.ma.sco Datawarehouse
• Compulsory Training Register DB
• Professional Training Ranking

19
Italian Education System

• Education and Training as two complementary
  systems
• The Italian educational system consists of
• nursery, preschool (3 years)
• elementary school (5 years),
• lower secondary school (3 years),
• upper secondary school (5 years), followed by
  post-secondary and tertiary education.
• Under the law, all Italian children must attend
  elementary school, lower secondary school and the
  first year of upper secondary school.
• Once they have completed their compulsory
  schooling, pupils not intending to go on to
  further or higher education may opt to leave
  school, but they must enrol for some form of
  training until they are 18 years old.
• Mixed Ownership of Schools Public, Non Public

20
Italian Governance Roles

• State (Department of Education)
• general personnel management
• general indications on subjects (syllabi) and
  level of competencies
• Region
• general planning and coordination of the school
  network system (education and training)
• Financial resources management (Statal and
  Regional funds)
• Monitoring
• Schools
• Administrative issues
• Didactics (loosely)

Constitutional Reform (ongoing) increased role
of Regional Administration personnel
management, regional component of indications on
subjects (syllabi)
21
Education Information System CSIs framework
22
Da.ma.sco School Data Mart

• The Piedmont Region has collected, for 26 years
  now, information on school population, teaching
  staff and structural characteristics of the
  school buildings of all levels through some
  questionnaires.
• Thanks to these data, two decisional services are
  available to browse through the data
• a multidimensional analysis service, set up by
  the Region, accessible to all the public
• a query reporting service for the Regional
  Department of the Italian Ministry of Education,
  University and Research, Municipalities and the
  schools of some Provinces.

23
Da.ma.sco School Information
Questionniares on school surveys
Schools
Historical series on school surveys
Aggregated information
Region
Historical Data Bases
Multidimensional analysis services
Query and reporting services
Other services
Region, Provinces, Schools
Access to school information
24
Da.ma.sco examples
25
Da.ma.sco examples
26
Compulsory Training Register
Centralizes the students personal data from
different sources
SCHOOL
COMPULSORY TRAINING REGISTER
CIA
APPRENTICESHIP
PROFESSIONAL TRAINING

• Data are fed to the system on a daily basis
• Each source contains the personal data of the
  students
• Each student should appear only once and is
  identified through his or her Fiscal Code

27
Compulsory Training Register Cleansing
Activities-1
Improvement methodologies based on data (data
quality techniques)

• Formal checks on fields
• fiscal code correctness, coherence of fiscal
  code with
• personal data, coherence the gender with first
  name, etc..

• Identification of duplicated data
• application of record matching techniques ?
• identification of similar record groups (cluster
  group)

• Criteria definition to identify a valid entry in
  a cluster group
• application of a weight and measure system
• that determines a record ranking
• in a cluster group

28
Compulsory Training Register Cleansing
Activities-2
Improvement methodologies based on processes

• Redesign the Compulsory Training Register data
  base

• Redesign feeding flows, introducing Data Quality
  functionalities

School DB
Professional Training DB
Apprenticeship DB
CIA
Operational DB
ETLq ETL DataQuality
Feeding procedures Data Quality control
New Compulsory Training Register
Operational Data Store
29
Professional Training Rankings

• The Piedmont Region issues funds on a yearly
  basis through tenders dedicated to Professional
  Training on the following main topics
• right and duty to education
• University master degrees
• labour market (on employment/unemployment)

30
Data Warehouse initial logical model
DATA SERVICES CATALOGUE
Decisional world
Operational databases
Operational world
31
Expectations for the Data Warehouse
Decisional World
DATA SERVICES CATALOGUE
Infodir and Sitad
Constabulary
Commerce
Building
Wages
Public Works
Administrative fees
Educational services
Budget
Training
Turnovers
Registry
Sector Data Marts
Operational World
32
Cross-section Registry Data Marts

• Benefits from the new structure
• Unique and optimised registry data supplier for
  all the several Data Warehouse components
• Unique access to the supplying managerial
  database
• Centralized analysis with general DW access tools
• Improved data quality
• Guideline towards a Federal Data Mart structure,
  in which to read info in a cross-sectional way in
  relation to the detailed Data Warehouse
  components, starting from the links we find in
  the registry Data Marts
• Share within the Authority the information
  (alphanumeric and territorial) knowledge
• Give a territorial meaning also to managerial
  information and allow the users to have new
  geographical and non-specialized info

33
Information and Contact

• web site http//www.csipiemonte.it/en
• contacts guido.albertini_at_csi.it
• marco.boero_at_csi.it