Database Answers

 

 

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 UK.

 

This Case Study can be applied to Microsoft’s ‘peoplereadyProject :-

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,

 aBasic Land and Property Unit”.

 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 UK.

 

 

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 London 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

 

London             

 

1

 

1

March 13th 09

 

London

 

2

 

2

March 20th. 09

 

London

 

3

 

3

March 27th. 09

 

London

 

4

 

4

April 3rd/ 09

 

London

 

5

 

5

April 10th. 09

 

London

 

3

 

3

April 17th. 09

 

London

 

2

 

2

April 24th. 09

 

London

 

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 UK level

 

                                    

 

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.