CHAPTER
5. A CASE STUDY
Question : Is there a Case Study of the Road Map ?
This Chapter presents a
Customer-Services Case Study for Local Government is based on the experience of
Database Answers Ltd. gained over a five year period
working with Local Authorities in the
This Case Study can be applied
to Microsoft’s ‘peopleready’ Project
:-
http://www.microsoft.com/business/peopleready/en-us/
It is presented in a
step-by-step sequence, from Data Sources to Data Governance.
The Steps are
:-
·
Data
Sources -
o
Identify
the Data Sources
o
Create
Data Models
·
Data
Integration –
o
Design
Target ERD Data Model for combined Data Sources
·
Mapping
–
o
Map
Entities
o
Map
Attributes
o
Define
Rules for Relationships and Field validation
·
Data
Quality (DQ) –
o
Produce
DQ Profiles
o
Agree
required DQ Standards
o
Repeat
Data Validation and Clean-Up as necessary
·
Design
the Data Mart
·
Performance
Reports -
o
Agree
KPIs with Users
o
Agree
Top-Level Summary Reports
o
Agree
Detailed Reports
·
Internet
Mashups -
o
Determine
the requirements for Mashups
o
Design
and Build Mashups as appropriate
·
Data
Governance -
o
Ensure
Compliance with Policies and Procedures.
o
Modify
as appropriate
5.1 Data Sources
5.1.1 Data
Source 1 - Electoral Register
This Data Model shows
Residents who are Registered Voters and the Elections that they participate in.
5.1.2 Data
Source 2 - Parking Tickets
This shows people who get
Parking Tickets, who may or may not be Residents, and the Actions that occur
for each Ticket.
5.2 Data Integration
5.2.1 Data
Model for combined Customers
This Data Model combines data
from the Electoral Register and the Parking Ticket Database.
The Customer_Types
Entity is used distinguish between the two types of Customers, which are either
Voters on the Electoral Register or people who have received Parking Tickets.
The Events Entity is used for
all Events that relate to a Customer, including being added to the Electoral
Register, with participation in subsequent Elections and also
receiving a Parking Tickets along with the associated payments, or claims or
cancellations tat might occur.
5.2.2 Rules
for Relationships
Here
is a partial list of the Business Rules define the Relationships between the
Entities in the Data Model shown above. They can be translated into SQL which can be applied as Test Conditions for the Database
or Data Mart.
1.
A Customer can be of one, and only one, Customer
Type.
2.
A Customer Type can be associated with zero, one or
many Customer.
3.
There is a Many-to-Many Relationship between
Customers and Addresses.
4.
In other words, A Customer can have many Customer_Addresses and an Address can be associated with
many Customers.
5.2.3 Mapping
Entities
The Generic Customer Services
Data Model is a
standard Data Platform so that data from any Source System can be
transformed and loaded into a standard Target for MDM.
This
Transformation is supported by Mapping Specifications and the appropriate
software.
This software can be either manually-coded SQL, a specialized solutions, such as Salesforce’s Excel Connector,
or a general-purpose commercial product, such as Informatica.
|
Source Table |
GCDM Entity |
Comment |
|
Activity |
Customer_Event |
For example, send a Letter or carry out an Investigation. |
|
Aspect |
Customer_Event |
These are Issues |
|
Complaint |
Customer_Event |
Contains repeated Options for Gender, Handling Investigator, etc.. |
|
Complaint_People |
Customer |
Includes Complainants and Contacts, such as Edwina Currie. |
|
Contact |
Contact |
People contacted with regard to Complaints. |
|
Cost |
Customer_Event_Notes |
For example, Compensation to a Complainant. |
|
PersonInv |
Customer |
Includes non-Customers, eg Contacts who have not made Complaints. |
|
Letters |
Event_Documents |
|
|
User |
Staff |
|
|
UserGroups |
Team |
Teams of Staff, equivalent to Teams of Social Workers. |
5.2.4 Mapping
Attributes
This Template defines mappings
for the Attributes in the Accommodation Entity.
This shows mapping for YOIS which is the Youth Offenders Information System
Data
Items are defined as Physical Terms which were taken
from the YOIS Workshops Training Manuals.
This
Section defines both the Data Items and their mapping to the Generic Customer
Business Data Model.
Records in the YOIS Database
include parents and victims), over the past five years that YOIS has been in
operation.
SOURCE TABLE
: Accommodation
|
DATA ITEM |
TYPE |
VALIDATION |
TARGET TABLE |
TARGET ATTRIBUTE |
COMMENT |
|
Date
Begin |
Date |
Not
blank and < Date End |
Customer_Addresses |
Date_From |
Start
date for Accommodation or Referral Date.Use Format
DDMMYY, and if unknown, use 010160. |
|
Date
End |
Date |
Blank
or > Date Begin |
Customer_Addresses |
Date_To |
|
|
Age
at Start |
Integer |
>
6 (years old) and< 20 |
Customer_Addresses |
Specific |
Derived - Calculated from Today’s date - Date of Birth. |
|
House
Number, Text |
Text |
|
Customer_Addresses |
line_2 |
Requires
parsing |
|
Street
Postcode |
Text |
|
Customer_Addresses |
postcode |
Gazetteer enabling searching by name or postcode. |
|
DoH Accomm Type |
Text
(80) |
Reference
Data from List of Valid Values |
Customer_Addresses |
Specific |
Dept of Health Accommodation Type. |
|
YJB
Accomm Type |
Text
(80) |
Reference Data from List of Valid Values |
Customer_Addresses |
Specific |
Youth Justice Board Accommodation Category. |
|
Supplier |
Text (80) |
Join to entry in Suppliers Table. |
Customer_Addresses |
|
Resource
providing Accommodation, Name of YOI, Children’s Home, etc.. |
|
Comments |
Memo
|
Not
possible |
Customer_Addresses |
comments |
Issues / Reasons / Notes relating to Accommodation. |
5.2.5 Rules
for Validation
These are the Rules for
validation of the data in the Address Table.
A
standard Address Format is defined which is compatible with the Property
Gazetteer.
|
DATA ITEM |
TYPE |
VALIDATION |
COMMENT |
|
address_id |
Integer |
>0 and
unique |
Unique Identifier for each Address. |
|
Easting |
Integer |
A six-digit
number, not greater than 660000 |
The Easting
coordinate of the visual centre of a
BLPU, the Easting coordinate of the visual
centre of a BLPU, a “ Easting and
Northing combine to define a location on map. . |
|
Northing |
Integer |
An eight-digit
number, not greater than 1300000 |
Northing
coordinate of the visual centre of a BLPU.. |
|
unique_property_ref_nr |
Integer |
If not blank,
this is an alternative Primary Key to this table, and is a
reference to the Property
Gazetteer. |
Pointer to the Property Gazetteer. If not blank, this is an alternative
Primary Key. |
|
Line_1_number_buildng |
Text(80) |
Validation is
not possible for this field on its own. |
If blank, then
the next field must not be blank. |
|
Line_2_number_street |
Text(80) |
Validation is
not possible for this field on its own. |
If blank, then the previous field must not be blank. |
|
Line_3_area_locality |
Text(80) |
Validation is
not possible for this field on its own. |
Can be blank. |
|
town_city |
Text(80) |
Validation is
not possible for this field on its own. |
Mandatory |
|
Postcode |
Text(9) |
Validation against a pattern or specific value. |
Optional |
|
County |
Text(80) |
A List of Valid Values for Reference Data. |
Optional |
|
Country |
Text(80) |
A List of Valid Values for reference Data. |
Optional |
5.3 Data Marts
This is a Data Model for a
Generic Data Mart for Customers.
The Customer Types include
Parking Ticket Holders and Voters
5.4 Performance Reports
5.4.1 Map
display showing KPIs
This Map shows Key
Performance Indicators (KPIs) for the Wards in a
Local Authority
Each Ward is displayed in either Red, Amber or Green, depending in whether the KPIs Threshold values are reached or exceeded.
Red indicates a situation
that requires urgent management attention, amber is a warning and green is
acceptable.
The level of display could be
at the Regional level within the
5.4.2 Summary
Report for a Regional level
This Report shows the total
count of Customers gained and lost in the South-East Region
|
RPt.1 Total
Customers Gained and Lost by Week |
|||||||
|
Date selected:
Month of January, 2010 |
|||||||
|
Week Ending |
|
Location |
|
Total Gained |
|
Total Lost |
|
|
March 6th
09 |
|
SE Region |
|
10 |
|
10 |
|
|
March 13th
09 |
|
SE Region |
|
20 |
|
20 |
|
|
March 20th.
09 |
|
SE Region |
|
30 |
|
30 |
|
|
March 27th.
09 |
|
SE Region |
|
40 |
|
40 |
|
|
April 3rd/
09 |
|
SE Region |
|
50 |
|
50 |
|
|
April 10th.
09 |
|
SE Region |
|
30 |
|
30 |
|
|
April 17th.
09 |
|
SE Region |
|
20 |
|
20 |
|
|
April 24th.
09 |
|
SE Region |
|
10 |
|
10 |
|
5.4.3 Detailed
Report for London
This Report shows the total
count of Customers gained and lost for
|
RPt.1 Total
Customers Gained and Lost by Week |
||||||
|
Date selected:
Month of January, 2010 |
||||||
|
Week Ending |
|
Location |
|
Total Gained |
|
Total Lost |
|
March 6th
09 |
|
|
|
1 |
|
1 |
|
March 13th
09 |
|
|
|
2 |
|
2 |
|
March 20th.
09 |
|
|
|
3 |
|
3 |
|
March 27th.
09 |
|
|
|
4 |
|
4 |
|
April 3rd/
09 |
|
|
|
5 |
|
5 |
|
April 10th.
09 |
|
|
|
3 |
|
3 |
|
April 17th.
09 |
|
|
|
2 |
|
2 |
|
April 24th.
09 |
|
|
|
1 |
|
1 |
5.5 Internet Mashups
At the top level, Widgets will be displayed on a Mobile Phone or Apple iPhone.
These Widgets will provide
access to Strategic Reports.
They would
be based on the Exception Reporting principle using Key Performance
Indicators.
.For example, whether total
number of Customers in a week is more 10%.
The top-level display could
be at the
Drilling-down on a Mobile
Phone or PC Screen would display detailed statistics.
5.6 Data Governance
Controls are in place for
Master Data Management.
Standards have
been established for Data Models.
Generic Data Mart structures
and Report Templates have been established.