Healthwatch: A Management Tool Combining Clinical And Population Data Sets

HealthWatch: A Management Tool Combining Clinical and Population Data Sets Mark David Hawker Informatics 2007/2008

The candidate confirms that the work submitted is their own and the appropriate credit has been given where reference has been made to the work of others. I understand that failure to attribute material which is obtained from another source may be considered as plagiarism.

(Signature of student) _______________________________

Summary A leading heath expert, Muir Gray (2006), was famously quoted as saying that the analysis of data in healthcare will have a “bigger impact than any single drug or technology likely to be introduced in the next decade”. As society becomes more reliant on technology in their every day lives, and information is more readily available on demand, it is clear that this could also be used in the healthcare industry. There are several benefits to being better informed including reducing costs from having more accurate resource provisioning, improving quality of care through better awareness of factors affecting disease and increasing staff professionalism. We hope to explore the concept of healthcare data analysis and provide a base for others to investigate in future years. This project demonstrates how software tools and techniques from a computing discipline can be applied to a problem in a healthcare domain. We use a novel requirements analysis technique known as personas as well as applying an innovative combined data mining and project management methodology inspired by the Agile Unified Process (AUP), participatory design (PD) and the Cross Industry Standard Process for Data Modelling (CRISP-DM). Through the exploration of clinical and population data sets we create a data model which is exploited through the implementation of a management tool. We reinforce the term “management” as it is important to note that we are not attempting to replace human-computer interaction (HCI), rather use technology to complement human knowledge acquisition. Evaluation is demonstrated throughout in both the methodology and postimplementation using the DECIDE framework Preece et al. (2002). Evaluation is carefully constructed addressing technical and socio-technical issues such as usability and finally recommendations are made for further extensions.

i

Acknowledgements Firstly I would like to thank my project supervisor, Dr. Natasha Shakhlevich, for her continued support and guidance throughout the project. The feedback received from my project assessor, Dr. Roy Ruddle, from the mid-project report and during the progress meeting was extremely helpful in deciding the scope of the project and is greatly appreciated. Finally, I wish to thank my friends and family who took part in the evaluation and Dr. Rick Jones for providing additional comments.

ii

List of Abbreviations Third Normal Form (3NF)

Model-View-Controller (MVC)

Aggregate Local Region (ALR)

National Administrative Codes Service (NACS)

Aggregate National Region (ANR)

National Centre for Health Outcomes Development (NCHOD)

Active Server Pages (ASP)

National Health Service (NHS)

Agile Unified Process (AUP)

Object-Relational Mapping (ORM)

Boyce-Codd Normal Form (BCNF)

Primary Care Trust (PCT)

Census Area Statistics (CAS)

Participatory Design (PD)

County Local Authority (CLA)

Practical Extraction and Reporting Language (PERL)

Cross Industry Standard Process for Data Modelling (CRISP-DM)

Hypertext Pre-Processor (PHP)

Comma-Separated Variables (CSV)

Quality Outcomes Framework (QOF)

Database Management System (DBMS)

Questionnaire for User Interaction Satisfaction (QUIS)

Department of Health (DH)

Rapid Application Development (RAD)

Don’t Repeat Yourself (DRY)

Rich Internet Application (RIA)

Enterprise Unified Process (EUP)

Rational Unified Process (RUP)

File Transfer Protocol (FTP)

Strategic Health Authority (SHA)

Geographic Information System (GIS)

Structured Query Language (SQL)

Government Office Region (GOR)

Scalable Vector Graphics (SVG)

General Practice (GP)

Terminology Reference Data Update Distribution (TRUD)

Graphical User Interface (GUI)

eXtensible Markup Language (XML)

Human-Computer Interaction (HCI)

eXtreme Programming (XP)

Integrated Data Modelling, Analysis and Presentation (IDMAP)

Yorkshire Centre for Health Informatics (YCHI)

Information Technology (IT)

iii

Table of Contents Summary ..................................................................................................................................................i Acknowledgements.................................................................................................................................ii List of Abbreviations .............................................................................................................................iii Table of Contents...................................................................................................................................iv 1.

Requirements Analysis ................................................................................................................... 1 1.1

Problem Statement................................................................................................................. 1

1.2

Requirements Capture ........................................................................................................... 1

1.3

Project Scope ......................................................................................................................... 2

1.4

Project Aim and Objectives................................................................................................... 3

1.5

Project Approach ................................................................................................................... 3

1.5.1

Data Models ...................................................................................................................... 4

1.5.2

Existing Solutions ............................................................................................................. 5

1.6 2.

3.

4.

5.

Project Evaluation ................................................................................................................. 7

Project Management ....................................................................................................................... 8 2.1

Agile Unified Process (AUP) ................................................................................................ 8

2.2

Participatory Design (PD) ..................................................................................................... 8

2.3

CRoss Industry Standard Process for Data Modelling (CRISP-DM).................................... 9

2.4

Conclusion............................................................................................................................. 9

Project Schedule............................................................................................................................ 12 3.1

Milestones............................................................................................................................ 12

3.2

Deadlines ............................................................................................................................. 12

3.3

Tasks.................................................................................................................................... 13

Data Phase..................................................................................................................................... 14 4.1

Data Understanding ............................................................................................................. 14

4.2

Data Preparation .................................................................................................................. 17

4.2.1

Logical Database Design................................................................................................. 18

4.2.2

Physical Database Design ............................................................................................... 19

Presentation Phase ........................................................................................................................ 25 iv

6.

7.

5.1

Modelling ............................................................................................................................ 25

5.2

Construction ........................................................................................................................ 29

Evaluation ..................................................................................................................................... 34 6.1

Evaluation Paradigms and Techniques................................................................................ 34

6.2

DECIDE Evaluation Framework......................................................................................... 35

6.2.1

Determine........................................................................................................................ 36

6.2.2

Explore ............................................................................................................................ 36

6.2.3

Choose............................................................................................................................. 37

6.2.4

Identify ............................................................................................................................ 38

6.2.5

Decide ............................................................................................................................. 38

6.2.6

Evaluate........................................................................................................................... 39

6.3

Further Work ....................................................................................................................... 44

6.4

Evaluation Summary ........................................................................................................... 45

Conclusion .................................................................................................................................... 46

References............................................................................................................................................. 47 Appendix A: Personal Reflection ......................................................................................................... 50 Appendix B: Project Plan...................................................................................................................... 52 Appendix C: Yorkshire and The Humber Aggregate Local Region Pairings....................................... 53 Appendix D: Informed Consent Form .................................................................................................. 54 Appendix E: User Survey Template ..................................................................................................... 55 Appendix F: Dr. Rick Jones’ Evaluation Cover Letter ......................................................................... 58

v

1.

Requirements Analysis

In this section we discuss the background to the project, including aim and objectives, approach and scope, and requirements capture through a technique known as personas. This user-centred design approach focuses on systems development from the perspective of the user rather than on technical requirements (Junior & Filgueiras, 2005). The ethos is to demonstrate the application of information technology (IT) as a management tool to support decision-making rather than replacing humancomputer interaction (HCI) with intelligent agents. Finally, we discuss how the project will be evaluated through technical and socio-technical perspectives. 1.1

Problem Statement

Data providers such as The Information Centre - a National Health Service (NHS) authority that collect, analyse and distribute national statistics on health and social care across the UK - provide reports that are freely available to view and download online. These reports are well-produced, but are not dynamic as they cannot be queried or analysed electronically. They also only present data from a single universe of discourse which cannot be cross-tabulated with other areas of interest such as population data. This presents a challenge for health professionals attempting to compare clinical data with other types of data such as demographics to distinguish factors affecting disease prevalence. 1.2

Requirements Capture

As we do not have any explicit end-users, a persona has been used for the understanding of the requirements of this project and as a source for evaluation. Personas can be used to simulate the needs of real users and the features they require. They are one of many user modelling techniques including user roles, user profiles, extreme characters, stereotypes and archetypes (Junior & Filgueiras, 2005). All of these techniques are similar in that they create a picture of potential users through research or data collection, whereas personas differ in that they can be based on imaginary or perceived information to enable more accurate characterisation (Junior & Filgueiras, 2005). This technique was first documented by Cooper (1999) in his book entitled The Inmates Are Running the Asylum which is a testament to keeping users happy by ensuring technology works in the same way they think. We have only detailed one persona but multiple personas can enhance system design by providing multiple perspectives which can be illustrated using feature-weighted priority matrices (Grudin & Pruitt, 2003). The decision was made to only detail one persona as the scope of data collection, preparation and visualisation was already large. Adopting the recommendations of Cooper & Reimann (2007) the persona has three important goals, classified as life, experience and purpose goals. Life goals are holistic and describe the overall aim of the individual’s existence; experience goals describe how the individual wants to feel when using the product or service; and purpose goals describe what features the individual would like to use. Grudin & Pruitt (2003) advocate the use of this technique in conjunction with participatory design (PD) for 1

the development of systems where user engagement is paramount. The following persona specification will be used throughout the project: Tom Clayton “The system shall enable me to compare clinical and population data.” “The system shall reduce the time needed to manually find and extract clinical and population data.” “The system shall enable me to filter results by geographic region and demographics.” Life Goals: He is a health professional who researches diabetes prevalence. He creates reports that describe the quality of care received across the UK and looks for reasons why there may be variance. In the past these reports have been used for service commissioning where particularly prevalent populations required more treatment options than less prevalent populations. Experience Goals: He feels he is skilled in his work, and has good knowledge of statistical data analysis. He already uses a site provided by The Information Centre (2007) to compare general practice (GP) results but this does not include any population factors. He wants to remain in control of the final results and be able to investigate them further. Purpose Goals: He wants to be able to extend his knowledge by looking at population data which provide him with demographic details such as age, sex or ethnicity. The main question he needs to answer is: Is diabetes prevalence affected by the ethnicity of a population at a local or national level? 1.3

Project Scope

Considering the scope of the project helps identify which areas will, and will not, be covered in the design of the system and influences the project aim, objectives and approach. This is important as it ensures there are boundaries to prevent time being wasted. It is recognised that scope may change over time but this may not be applicable in the case of this project as the requirements and environment are not changing. In large-scale projects, scope is generally defined in terms of deliverables, functionality and data, and technical structure (Turbit, 2005). For a project of this scale it is unnecessary to go into such detail (and the fact that these issues are discussed in other areas of this report) but we will look at describing the intentions. As there is no “out-of-the-box” solution combining clinical and population data the majority of time will be spent collecting and preparing the data structures. Presentation elements will be condensed and it is hoped that this project will act as a springboard for others to continue and explore more advanced areas of visualisation. For example, we wish to integrate an export feature into the software which will enable users to download data. The export will be in both a generic comma-separated variables (CSV) format, which can be imported into popular software packages such as Microsoft Excel or OpenOffice Calc, and a specialised data mining tool WEKA ARFF format.

2

Data mining is the process of discovering useful patterns in data from large databases and using it to make decisions (Kantardzic, 2002). We envisage that the data could be used to present correlations between attributes, such as age and diabetes prevalence, but also for identifying geographical areas which have similar characteristics. This may be useful as health professionals can begin to “learn” what programmes and services are most effective in areas of high (or low) prevalence. WEKA is an open-source collection of machine learning algorithms and tools for data classification, regression, clustering, association and visualisation used in data mining tasks (Witten & Frank, 2005). WEKA can only process files that are in a proprietary file format which will need to be considered when creating export functions. Many of the details are contained on the WEKA web site www.cs.waikato.ac.nz/ml/weka/ and discussed by Witten & Frank (2005). 1.4

Project Aim and Objectives

The project will incorporate the development of an independent software system, including the production of a data model, from clinical and population data sets. Focus will be on the development of the data model and software tools and techniques that can be used. The aim is to: Develop a data model that incorporates clinical and population data sets and develop a prototype system that queries and presents outputs from the data model that can be used by a health professional to make inferences about disease prevalence in populations. The project will involve an investigation into several data providers and the challenge is to create a data model which links data sets with differing levels of granularity (local or national). To satisfy the aim the following objectives need to be achieved: •

Identify the scope and approach to the problem including evaluating existing solutions.

•

Identify an appropriate methodology for data processing and project management.

•

Research and develop a data model for describing the multiple data sets.

•

Design and develop a prototype system that queries the data model and presents the results.

•

Evaluate the system to see if it influences working practice.

These objectives will be useful in helping plan the project and in constructing the project schedule (see Section 3) to support its delivery. 1.5

Project Approach

We recognise the project could be implemented in a number of ways and using a number of technologies, from presenting the data as a printable report to developing a fat client desktop application which enables the user to analyse and store data locally and extract updates from a server. For this project, where the primary focus is not on software development but on data modelling, the implementation needs to be as “rough and ready” as possible. A web-based thin client approach which only requires access to a web browser rather than a client application will ease the distribution of the 3

solution and recognises that installing applications may be restricted in the user’s work environment. The selection of programming language and database management system (DBMS) is transparent to the user and so has not been discussed in much detail in this report. What has been included is the discussion of what is meant by a data model as there are a number of options available. The most important factor in development is the choice of design pattern - the model-view-controller (MVC) architecture - and dynamic charting tool which are discussed later in this report. There are many programming languages that could be used including Active Server Pages (ASP), Practical Extraction and Reporting Language (PERL), Python or Ruby. However, Hypertext PreProcessor (PHP) has been chosen as an appropriate programming language for two reasons: •

The developer has more experience of this language as opposed to alternatives through external web development projects. This will reduce the time needed to learn a new language which is beneficial as the presentation component is secondary to producing the data model.

•

PHP5 introduced support for object-orientation and increased MySQL and eXtensible Markup Language (XML) handling. This includes the ability to create constructors, methods, and cleaner exception handling via try and catch blocks (Trachtenberg, 2004).

As well as a programming language we also require a suitable DBMS that will be used to store the data. Again, there are many choices available including Apache Derby, MySQL, PostgreSQL or SQLite. For such a relatively small project, where we will (hopefully) not be processing millions of rows of data, any platform would be suitable as variance is minimal. We chose MySQL as it was available alongside the PHP installation and there is an interactive add-on called PHPMyAdmin which can be used to install and view databases via a web interface. This is useful for debugging as most of the other DBMS only provide access via a command-line rather than a graphical interface. We intend to use a web application framework to support development as it helps to reduce the overheads associated with development activities such as database access, page templates and code structure as functions are pre-coded. The use of these frameworks promotes code re-use according to the Don’t Repeat Yourself (DRY) process philosophy (Hunt & Thomas, 1999) and they often utilise MVC architectures. Having referred to the comparison of web applications frameworks presented on Wikipedia (2007) and browsing the documentation and examples for each, CodeIgniter http://www.codeigniter.com/ was identified as being the easiest to use and was well documented. CodeIgniter supports the MVC architecture as well as being PHP. There is no need for a more sophisticated framework as we do not require any of the advanced forum, e-commerce or gallery features offered by other frameworks. 1.5.1

Data Models

A data model is a way of describing the schema of some data store which enables us to concentrate on design principles without an immediate concern with technical implementation (Beynon-Davies, 4

2004). There are a number of data models available: relational, object-oriented, deductive and postrelational. We will investigate the relational data model as it is considered to be the most appropriate to the project. This is based on the lack of object-oriented database management systems, increases in object-relational mapping (ORM) tools for translating relations into object-like structures and the prevalence of the MVC architecture. A database is a set of structures for organising data therefore we must have a set of principles for describing and implementing them. Adhering to three data model rules - data definition, manipulation and integrity – ensures data will “just work” on any database management platform. All of these rules will be important when designing and implementing the database and so we will briefly describe how each is enforced in the relational data model. Data definition is the process of exploiting data structures to suit an application. In the relational data model we have a single data structure - the relation. Being a mathematical construct (Codd, 1970) a relation must obey a number of rules which are presented by Beynon-Davies (2004). In particular, every relation must have a distinct name, column names must be distinct and entries must be of the same domain. For example, you cannot mix text and integer values. Data manipulation has four aspects: how we input data; how we remove data; how we amend data; and how we retrieve data (Beynon-Davies, 2004). Insertion of records is only performed once when populating the database and so has not been included. The language for manipulating data in the relational model is known as structured query language (SQL) which contains structured methods for performing each of the tasks. We shall focus on the ones appropriate to this project, in particular, how we retrieve data from the relation: Restrict: Extract rows that match a condition and is represented by the WHERE operator in SQL. Project: Extract specified columns and is represented by the SELECT operator in SQL. Natural Join: Takes two (or more) relations and joins them into a single relation. This can be represented in SQL using a combination of SELECT, FROM and WHERE r1.id = r2.id. Data integrity is split into two key aspects: entity integrity and referential integrity. Entity integrity relates to primary keys and states that every table must have one which must be unique and cannot be null. If this isn’t adhered to it is possible that a relation will have duplicated rows, which is against the rules of the relational data model. Referential integrity relates to foreign keys and ensures that if a reference from a relation is updated or deleted then all corresponding references are also updated or deleted. As we are not updating or deleting any records, referential integrity will simply ensure that any foreign keys reference a valid column of another relation. 1.5.2

Existing Solutions

We have identified two existing solutions which show different ways of presenting data. Both are provided on behalf of the NHS and are aimed primarily at health professionals. The Information Centre provides a web site for comparing Quality Outcomes Framework (QOF) results for all GPs 5

across the UK. The site enables users to search for a GP (or list of GPs) and then select which QOF indicators they want to compare, e.g., diabetes or asthma statistics. The results are presented in a tabular format and are scored from 0 to 100% which can then be compared to the Primary Care Trust (PCT) average (a regional score) or the national average (see Figure 1, below). An export to Microsoft Excel feature is also available to download the results. The site is a good example of how simple techniques can be effective in conveying information, such as in the form of bar charts and combinations of colour and text.

Figure 1 Screenshot Showing the Number of Diabetes Instances from 7 General Practices (GP) in Keighley (The Information Centre, 2007) The National Centre for Health Outcomes Development (NCHOD, 2007) provides comparative data for 700 local and health government organisations across England. Data is collected annually through comparative analysis of a number of health-related factors, known as compendium indicators, which are at national and local levels. Each indicator has a specification giving precise details about how the statistic was calculated, which parts of the population it includes as well as concise definitions.

Figure 2 Screenshot Showing Male Mortality Rates from Diabetes (NCHOD, 2007) 6

As well as being able to download the data there is also an “interactive atlas” which has features enabling the user to select, filter, sort and explore the data (see Figure 2, above). The atlas was created using a commercial product, InstantAtlas™, which utilises scalable vector graphics (SVG) technologies. SVG is a language used to create interactive two-dimensional graphics and applications using XML (W3C, 2008) and may require a web browser plug-in to be installed to enable graphics to be viewed. Whilst this may be seen as a barrier to entry, it enables increased functionality as processing can be performed client-side rather than server-side. There are three types of atlas, each presenting the data in a different way (NCHOD, 2007): •

Nested Rate Plot: Presents organisations’ data and confidence intervals nested within groups of similar organisations to enable comparisons to be made between them.

•

Funnel Plot: Presents the distribution of organisations’ data compared to average figures.

•

Correlation Plot: Allows users to map and compare the relationship between two indicators.

This system is the most advanced implementation we have found as it presents data using geographical locations to show a “heat map” of results which gives a quick summary of the entire region. It also enables the user to toggle the display of boundaries and to zoom in on specific regions. The implementation is particularly clever as it holds data over multiple years enabling comparisons to be made not just at a geographic level but also over different periods of time. A trial workbench is also provided which allows users to download customised data created using different statistical methods. For example, the indicator “deaths at home from all cancers” can be divided into two products, indirectly age-standardised rate or number and percent. This is useful as it enables analysts to customise outputs requiring minimal time and effort to re-format the results. 1.6

Project Evaluation

An evaluation framework known as DECIDE Preece et al. (2002) has been identified which ensures appropriate goals and questions are defined. Evaluation will be split into two types defined by Scriven (1967) as formative and summative. Formative evaluation addresses issues throughout the design process – an element which is implicitly built into the project methodology through elements of PD and expert-developer interaction – and summative evaluation is completed at the end of the project by “observers” who did not take part in development. The balance of formative and summative evaluation provides an adequate review from multiple user perspectives in an attempt to increase response validity and reduce bias. As the project is targeted towards health professionals it will be important to gain domain expert feedback to ensure the system results are accurate. Three experts were contacted and provided with a brief of the project and all three replied saying that they would be willing to provide feedback when required.

7

2.

Project Management

In this section we present an overview and evaluation of three popular methodologies and then conclude with the chosen methodology which we will follow throughout the project. A methodology is a framework of procedures, techniques, tools and documentation aids (Bennett et al. 2006) intended to aid the acquisition of an information system (Bocij et al. 2006). Following a methodology helps in managing and prioritising tasks, and provides a mechanism for assessing and monitoring progress against a project schedule (see Section 3). A combination of methodologies may be required, or a single methodology may need to be modified to suit the specific project tasks. 2.1

Agile Unified Process (AUP)

The Agile Unified Process (AUP) is an evolution of the Rational Unified Process (RUP) and the Enterprise Unified Process (EUP) by Ambler (1999; 2006). The underlying principle of the RUP was to invest time into planning activities and artefact production which should result in lower costs, timely delivery and better software quality (Germain & Robillard, 2005). Development in the AUP is spread over four iterative phases: Inception, Elaboration, Construction and Transition. Each phase emphasises a different discipline such as modelling, implementation and testing which ensures full coverage over the project life-cycle. The combination of phases and iterations enables developers to spend less time modelling requirements and more time on implementation and refining models. Bennett et al. (2006) summarise that although complex it is possible to adhere to its principles without having to complete each phase slavishly. The AUP suits projects with multiple developers and is oriented towards a business environment. For example, estimating costs and risks are not part of this project. Whilst iterative working can be beneficial when requirements are constantly changing, for a project such as this where time is constrained and requirements are fixed it would be inappropriate to iterate through the entire cycle. However, iterating over each phase individually may be more appropriate which links into the participatory design (PD) approach. 2.2

Participatory Design (PD)

PD is an approach to information systems development where stakeholders share a guiding vision which can lead to hybrid experiences (Bennett et al. 2006). Practices take place in an “in-between” region that is neither in the software professionals’ domain nor in the workers’ domain (Muller, 2002). The origins of PD are from Scandinavian work with trade unions and research projects in userparticipation in systems development date back to the 1970’s (Bødker, 1996). PD is an exciting concept which is similar to newer methods such as eXtreme Programming (XP) which can be seen in today’s development world. These concepts promote rapid development through knowledge sharing. There are several benefits to using PD such as being able to challenge assumptions of others, learning reciprocally and generating new ideas through shared experiences (Muller, 2002). Whilst its application may seem like common sense, it can be a very powerful tool as described in the 8

integration of personas by Grudin & Pruitt (2003) and in providing synergy with Rapid Application Development (RAD) methods as researched by Beynon-Davies & Holmes (1998). Whilst not strictly a methodology, PD is a way of working that can be incorporated into a project management framework. This enables a single developer to gain opinions and criticisms from others on issues such as implementation, development and testing. This sociological approach to development is one favoured by the author as it treats software engineering as more than just developing solutions, but solutions that are used by the “inmates” described by Cooper (1999). 2.3

CRoss Industry Standard Process for Data Modelling (CRISP-DM)

The Cross Industry Standard Process for Data Modelling (CRISP-DM) is a tool-neutral data mining process model that was conceived in 1996 (Chapman et al. 2000) and builds on attempts to define knowledge discovery methodologies (Wirth & Hipp, 2000). The aim of the process model is to act as a safety net for data mining practitioners to ensure they can demonstrate to prospective customers that data mining is sufficiently mature to be adopted as a key part of business processes. The CRISP-DM process model consists of six phases that are broken into generic and specialised tasks: •

Business Understanding: State the business and data mining objectives and success criteria for which evaluation will be based on. This will help in understanding which data mining concepts will be most appropriate. For example, classification, or clustering.

•

Data Understanding: Collect, describe and explore the data to verify its quality.

•

Data Preparation: Clean the data by removing any “noisy” data and consolidate it by performing transformations to create new variables or formats.

•

Modelling: Select and apply appropriate techniques to satisfy the data mining objectives.

•

Evaluation: Identify how well the model performed and whether it meets the needs of the business objectives. Interpret the model to determine its usefulness.

•

Deployment: Determine how the results will be used and how often they need to be updated.

A major challenge highlighted by Wirth & Hipp (2000) was in implementing such a methodology inside a project management framework. (They had to employ external service providers to complete some tasks.) It is clear from the user guide (Chapman et al. 2000) and the experiences of Wirth & Hipp (2000) that the process is complex and time-consuming and needs to be adapted to create a sufficient balance between following the methodology and achieving the requirements of the project. 2.4

Conclusion

The final methodology was created using a combination of principles from the AUP, PD and CRISPDM. As we have suggested: the AUP emphasises iterating over a number of phases and refining models and code over time and is suited to projects with multiple developers and changing requirements; PD enables shared working and promotes collaboration between developers and 9

workers; the stages in CRISP-DM are useful, although care needs to be taken not to get too involved in the detail of deliverables from each stage. The Integrated Data Modelling, Analysis and Presentation (IDMAP) Process (see Figure 3, below) has two phases. The phases incorporate a synergy between a developer and expert who crosscollaborate and share domain knowledge. The expert is not necessarily the problem originator although they can be included at the Evaluation stage.

Figure 3 Integrated Data Modelling, Analysis and Presentation (IDMAP) Process The first phase is influenced by CRISP-DM and focuses on data capture, understanding and modelling and will be referred to throughout the report as the Data Phase. The second phase is influenced by the AUP and focuses on the development of the prototype system and will be referred to as the Presentation Phase. The processes in each phase are iterative and can be considered complete when 10

both the developer and expert are in acceptance. Although the time-scale of the project will only enable a single iteration of the life cycle it is possible to iterate over the entire process if required. If data was more volatile then the project stages may be more complex. The six stages of the IDMAP life cycle are: •

Requirements Analysis: This stage provides an understanding of requirements which will form the basis for design, implementation and evaluation of the project. This includes identifying the project aim and objectives, scope and formalising requirements. Background reading is also included in this stage as it is important to investigate what solutions already exist as it may be possible to either use them or learn from the lessons of others.

•

Data Understanding: This stage includes initial data collection and data familiarisation. This may identify data inconsistencies or quality issues that would require replacement data to be used. Understanding the data may also lead to the detection of interesting subsets of data that can be exploited to add value to the requirements.

•

Data Preparation: This stage involves re-constructing the data into a format that can be queried and translated into information. Included in this stage is removing any “noisy” data and performing transformations to create new variables or formats.

•

Modelling: This stage includes manipulating the data model into information, e.g., constructing queries or functions which present the results as a table or chart. This stage assumes the data has been translated into an appropriate data model that can be manipulated.

•

Construction: This stage involves the development of a graphical user interface (GUI) or report which presents the results from the modelling stage. The results will be influenced by the aim of the project and the objectives of the study.

•

Evaluation: Although each process has an implicit evaluation created through the PD collaboration of developer and expert, the project should be evaluated to present conclusions for further study and evaluating the outputs against specified criteria.

Application of this methodology is presented in the project schedule (see Section 3) and is reflected in the design of this report which includes the four phases of the IDMAP process: Requirements Analysis, Data Phase, Presentation Phase and Evaluation. Reflections on the success or otherwise of the methodology is presented in Section 7 and Appendix A and as it is hoped that it will provide an improved framework for composite data and systems projects.

11

3.

Project Schedule

In this section we detail the tasks required to complete this project using the chosen Integrated Data Modelling, Analysis and Presentation (IDMAP) methodology including the tasks, milestones and deadlines which will be used to judge the success of the planning of the project. Online project management and collaboration tool Basecamp http://www.basecamphq.com/ will be used to manage and track project milestones, to-do lists and messages as it sends reminder e-mails when deadlines are close. Tick http://www.tickspot.com/ was also used, at least initially, as an online time tracking tool as it could be integrated into Basecamp but was later replaced by Microsoft Excel as it was often difficult to monitor how many hours were spent on tasks. The tool is probably more useful in a situation with multiple developers who are being paid hourly or are working to a budget.

Figure 4 Final Project Plan The final project plan (see Figure 4, above) contains the sections defined in this report on the lefthand side and follows the methodology which separates data collection and preparation from the creation of the graphical system. (Background Reading was separated from Requirements Analysis for clarity.) The tasks specific to each section are detailed below including milestones and deadlines to ensure timely project completion. Grey areas denote exam periods where no work was expected to be completed and green areas are where work was completed. 3.1

Milestones

26/11/2007

Complete Data Phase

25/02/2008

Complete Presentation Phase

3.2

Deadlines

07/12/2007

Mid-Project Report

07/03/2008

Table of Contents and Draft Chapter

14/03/2008

Project Meeting

23/04/2008

Project Report (Hard Copy)

25/04/2008

Project Report (Electronic Copy) 12

3.3

Tasks

Requirements Analysis: w/c 1st October 2007 – w/c 15th October 2007 Define and formalise the persona, aim and objectives, scope and how the software will be evaluated. Initial project aim and minimum requirements must be submitted by the 19th October 2007. Background Research: w/c 15th October 2007 – w/c 17th December 2007 Investigate methodologies, existing solutions and software tools and techniques which can be included in the mid-project report. This will be an on-going task but will be targeted to be completed early in the project lifecycle to enable any further reading to be completed (if required). Data Understanding: w/c 22nd October 2007 – w/c 19th October 2007 Identify and review data sets. Cleanse the data of anomalous values and ensure all relevant data has been collected. Make contact with appropriate experts who have knowledge of the data sets in case there are any questions or gaps in developer knowledge. Data Preparation: w/c 12th November 2007 – w/c 19th November 2007 Re-construct the data into a data model suitable for manipulating. Documentation will include logical and physical database design including an entity-relationship diagram. The notional milestone set for completion of this stage is 26th November 2007 which ensures the presentation phase will be started on time to leave enough time for evaluation. Construction: w/c 17th December – w/c 25th February 2008 Create the graphical user interface for the data model using the software tools and techniques identified in the background reading including the model-view-controller (MVC) architecture. The notional milestone set for completion of this stage is 25th February 2008. Evaluation: w/c 3rd March 2008 – w/c 31st March 2008 Evaluate the project in terms of technical and socio-technical perspectives including design heuristics, usability and user satisfaction. Provide possible extensions and general successes/failures. Project: w/c 1st October 2007 – w/c 21st April 2008 Although this is an on-going process, there are deliverables such as the mid-project report, table of contents and draft chapter, project meeting and final report which will need to include some written elements. The milestones set for the data and presentation phases should allow adequate time for the report to be written up and checked prior to each submission.

13

4.

Data Phase

In this section we discuss the data sources and steps required to translate them into a relational database format. Data granularity is discussed, as some sources are available at a finer grain of detail, and an ideal method for matching is proposed along with the approach taken in this project. A logical and physical design is included to ensure the integrity of the final data model. Consultation took place between the developer and experts who had knowledge of the availability of data and included an employee at The Information Centre and the National Administrative Codes Service (NACS). 4.1

Data Understanding

Figure 5 Data Sources and Proposed Data Architecture The initial understanding is that we have two distinct data sources (see Figure 5, above) – clinical and population – with the clinical data being separate from the population data as there are no relationships between the two. The aim is to aggregate these data marts so that we can link the clinical and population data sources together using common attributes. There are already links between the Quality Outcomes Framework (QOF) and Terminology Reference Data Update Distribution (TRUD) data marts and the NACS and TRUD data marts which are discussed below. Clinical Data The QOF is an annual reward and incentive programme detailing general practice (GP) results, with the latest being from 2006/2007. It details how well a practice is organised, customer experiences and also includes how diseases are managed, e.g., diabetes. Other sources of data could have been the National Diabetes Audit but unfortunately could not be obtained for confidentiality reasons. The QOF data set contains records from 8,372 GPs in England each with the following attributes: •

GP Code which uniquely identifies a GP in the UK along with its Practice List Size.

•

Number of Disease Cases. (We can work out prevalence, or density, by taking the number of cases and dividing by the Practice List Size.) 14

•

Primary Care Trust (PCT) Code for linking a GP to a PCT. This enables data from a collection of GPs to be aggregated at a local level.

The NACS contains the geographical details of all GPs, PCTs and Strategic Health Authorities (SHA) and so will be useful for linking a GP to a PCT and a PCT to a SHA. (A SHA is a collection of PCTs which are aggregated at a national level.) This will enable results to be obtained for a “cluster” of GPs belonging to a PCT, or for all GPs in a SHA. The NACS data set contains records from 157 PCTs, 8,631 GPs and 10 SHAs. However, the codes in the QOF data do not match the coding structures of NACS and so a third service TRUD is required to match values for the two data marts. This was an unforeseen problem as it was assumed that the QOF and NACS data would be connected. This highlights one of the problems of applying theory to a practical task and how additional knowledge was needed to find the appropriate service that would link the two sets. The TRUD is a service hosted by the UK Terminology Centre that provides NHS reference data to NHS and non-NHS third parties and contains national reference code data on NHS individuals and organisations. Restricted access is given via File Transfer Protocol (FTP) which contains several data files including a Microsoft Access database file with lookups for all 12,217 GP records in the UK. (Note that the QOF data mart contains 8,372 GP records, NACS 8,631 and TRUD 12,217 which means that already there are discrepancies in numbers and will lead to losses of data.) Population Data The Census is one of the most comprehensive sources of information about the UK population. A form was sent to every household and establishment to be completed and sent back via post before Census day which included details on demographics and employment (Directgov, 2007a). The most recent Census was conducted on Sunday 29th April 2001 and the next is scheduled for 2011. The results are broken into manageable “chunks” that are available at varying geographical levels. Levels that we are interested in for this project are Government Office Regions (GOR) which roughly translates to a SHA and County Local Authorities (CLA) which roughly translate to a PCT. Along with these levels the results are split into three “products”. A description of each product has been adapted from Directgov (2007b): •

Key Statistics consist of a series of 33 tables which provide a summary of the complete results of the Census. The summaries are designed to enable easy comparisons between areas across the full range of Census variables and cover the most significant or requested counts.

•

Census Area Statistics (CAS) consists of a series of tables which provide detailed information down to the most local geographic level. “Themed” tables are available which are specific to a certain population type, e.g., dependent children, and univariate tables are available which provide detailed information on a particular topic, e.g., age or country of birth.

15

•

The Standard Tables consist of a series of detailed cross-tabulations that can be used to compare a multitude of statistics. As with the CAS, “themed” tables are available.

Standard Table S108, which contains sex, age and economic activity by ethnic group, is ideal for this project as it provides summaries of all of the relevant variables that are required. The data set contains 1,479 records for all 9 GOR and 148 CLA. A software tool, Nomis (2007), has been used as it provided access to customise downloads of Census data. The software had a simple web interface where selections can be made based on which attributes are required and at which geographic level. Granularity The major challenge in combining the two data sets is ensuring accuracy of data. We describe an ideal technique for achieving this. However, due to time constraints a simpler technique was used which was less accurate but ensured maximum coverage and speed. From the data we can see that the clinical data sets include this hierarchy: GP > PCT > SHA and the population data set includes this hierarchy: CLA > GOR. Note the Census does include other data sets such as counties, Unitary Authorities and electoral wards but were less appropriate for the simpler technique. Level of Detail

Clinical

Population

Top

SHA (10)

GOR (9)

Middle

PCT (157)

CLA (148)

Bottom

GP (8,631)

N/A

Table 1 Granularity of the Clinical and Population Data Sets Table 1 (above) shows that as we drill down to a finer level of detail the difference between the numbers of instances increases. At the top level, there is one more SHA instance than GOR. This is because there are three regions: South East, South Central and South West in the clinical data and only South East and South West in the population data. A simple technique is to combine these regions into an “aggregate” Southern region. However, this technique does not work as easily (if at all) for the middle level as some regions have multiple PCTs which do not map onto any CLA and vice versa. Because of this we deemed it appropriate to take a single GOR – Yorkshire and The Humber – and linked the middle regions together (see Appendix C) which enabled parts of the solution such as drilling down into a national region to be demonstrated. A more “scientific” method would be to investigate the land borders of each region and statistically work out their boundaries. Once we knew these it would be possible to pinpoint whether a location lay within a particular region. This would be similar to tracing the outlines of regions from one data set onto a map and then adding points from the second data set on top. This was the reason why post codes were collected from the data marts as it was thought this technique would be feasible in the

16

time-frame. With every post code it is possible to use a geo-coding service to gain the latitude and longitude of an area which can then be used to plot it onto a map. Geographic Information Systems (GIS) provide a means for visualising data, such as that which we described above, and are discussed by Leonard & Samy (1998) using software to forecast demand for services in healthcare regions (see Figure 6, below).

Figure 6 Screenshot Created from the SAS Software (Leonard & Samy, 1998) This example is similar to that of the NCHOD (2007) application with the use of a map and graphical representation of results using adjacency matrices and complex time-series models. However, a manual technique was adopted in this project by taking a sample of instances from a GOR and using an online mapping tool Google Maps http://local.google.co.uk/ to identify which was the closest PCT to the CLA. Evidently the GIS approach would’ve been more exciting! 4.2

Data Preparation

Having identified the sources of data we are able to highlight the appropriate entities and relations needed for this project. It may be appropriate to clean the data so that only relevant attributes are used. For example, Economic Group from the original population data contained several categories for Full and Part Time workers, Retired or Permanently Sick or Disabled which could be aggregated into four main groups: Active, Inactive, Unemployed and Student. Clinical Data The QOF data was prepared by removing all attributes that were not required, such as other disease statistics, leaving a GP Code, Practice List Size and Diabetes Cases. A new attribute, Disease Prevalence, was also created by dividing Diabetes Cases by Practice List Size. As the NACS and TRUD data sets were already structured, it was a case of removing attributes that were not required and combining the PCT and SHA into a single relation. The final TRUD relation now has two attributes, a GP Code and a PCT Code which will be used as a lookup for the NACS and QOF data. 17

Population Data The Census data was the most time consuming data set to prepare. The steps were: 1. Download all nine GOR data sets contained in Microsoft Excel files using the Nomis (2007) online tool. Each CLA was represented as a column and each of the Census statistics as a row. 2. The un-normalised statistic column was normalised to occupy a single column for each value, e.g., All People, Aged 16 to 24 Years, Economically Active, Employee, Part Time, Mixed, White and Black Caribbean now occupied seven columns instead of one. A new column Aggregate Economic Group was created which removed some of the unnecessary detail from the economic statistics. Four Aggregate Economic Groups were created: Active, Inactive, Unemployed and Student. This was repeated for all GOR and they were all pasted into a single data file. Microsoft Excel was used as formulae could be created to split and concatenate values quickly. 3. All un-used attributes were removed from this file, e.g., specific Ethnic Group types (Irish) so that they would be aggregated into their aggregate group (White). 4. All redundant rows were removed. For example, the data set initially included a summary of all records which aggregated all of the Male and Female statistics. 5. A Microsoft Access database was created to aggregate all of the statistics. It used GROUP BY Sex, Age, Ethnic Group and Economic Group and then a SUM function on each of the values. The original Census data contains 1,479 records for all 148 CLA. This means that we had a potential 1,479 x 148 = 218,892 values which was reduced to 11,920 (5%) through the cleansing phase. 4.2.1

Logical Database Design

Logical database design shows how each entity can be arranged without considering the physical limitations of the DBMS (Beynon-Davies, 2004). Logical design will not be the same as physical design as it does not take into account volume and transaction analysis used to optimise relations. Bracketing notation has been used to describe the relations with primary keys underlined and foreign keys tagged with an asterisk*. CENSUS (Statistic Code, CLA Name, GOR Name, Sex, Age, Ethnic Group, Economic Group, Value)

NACS (PCT Code, PCT Name, PCT Post Code, SHA Code, SHA Name, SHA Post Code) QOF (GP Code, GP List Size, Diabetes Cases, Diabetes Prevalence) TRUD (GP Code*, PCT Code*) Normalisation is performed to remove data redundancy and anomalies that reduce the integrity of data (Beynon-Davies, 2004). Normalisation involves transforming data from an un-normalised form into third normal form (3NF) or Boyce-Codd normal form (BCNF) by identifying the determinacy between attributes. Functional determinacy means that for every value of A there is a single value for 18

B. For example, PCT Code functionally determines PCT Name; however, a PCT Name may not be unique and so does not functionally determine PCT Code. The CENSUS relation contains a composite key of Statistic Code and CLA Name as both attributes are required to functionally determine the other attributes. Anomalies will arise if we update the relation and so conversion is required to BCNF. CENSUS_CLA (Statistic Code, CLA Name*, Sex, Age, Ethnic Group, Economic Group, Value) CENSUS_GOR (CLA Name, GOR Name) The NACS relation is un-normalised as SHA Code, Name and Post Code are not functionally dependent on PCT Code. To transform this into 3NF we separate the PCT and SHA attributes. NACS_PCT (PCT Code, PCT Name, PCT Post Code, SHA Code*) NACS_SHA (SHA Code, SHA Name, SHA Post Code) Through the process of normalisation we have created additional relations not initially described in the data sets. To join the data we require a new AGGREGATE_NATIONAL entity which lists the high level names that are common to both data sets, and an AGGREGATE_LOCAL entity which lists the medium level names. The final logical database design is: CENSUS_CLA (Statistic Code, CLA Name*, Sex, Age, Ethnic Group, Economic Group, Value, Aggregate Local Region*) CENSUS_GOR (CLA Name, GOR Name, Aggregate National Region*) NACS_PCT (PCT Code, PCT Name, PCT Post Code, SHA Code*, Aggregate Local Region*) NACS_SHA (SHA Code, SHA Name, SHA Post Code, Aggregate National Region*) QOF (GP Code, GP List Size, Diabetes Cases, Diabetes Prevalence) TRUD (GP Code*, PCT Code*) AGGREGATE_NATIONAL (Aggregate National Region*) AGGREGATE_LOCAL (Aggregate Local Region*) Note that to link the sets to the AGGREGATE relations we have added foreign keys to each relation which references either a local or national region. 4.2.2

Physical Database Design

The physical database design involves taking the results of the logical design and fine-tuning them against transaction, performance and storage requirements (Beynon-Davies, 2004). This requires us to change the design to suit the needs of the system, e.g., de-normalising relations to improve performance. The physical design process consists of volume and transaction analysis and defining a physical schema. This may seem like a step backwards, but it is useful in ensuring the database is configured as optimally as possible which will aid the speed of querying and retrieving results. 19

Volume Analysis Volume analysis is used to establish the average and maximum number of instances per entity and is useful for deciding storage requirements and for transaction analysis. There were 12,217 GPs listed in the TRUD data set of which 6 listed in the QOF data set do not appear in the TRUD data set and so have been omitted. The final number of records is 8,366. The AGGREGATE_LOCAL relation has been excluded from the analysis as it has been deemed out of the scope of this project. Using the figures from the logical database design, we are able to determine the relation sizes (see Table 2, below). Column size was calculated by estimating the field sizes of each record based on documentation found at the understanding phase. For example, CENSUS_CLA has eight fields with indicative character sizes shown in parentheses: Statistic Code (15), CLA Name (100), Sex (1), Age (7), Ethnic Group (1), Economic Group (1), Value (8) and Aggregate Local Region (100).

Rows (Exact)

Column Size

Relation Size (Chars.)

CENSUS_CLA

11,920

233

2,777,360

CENSUS_GOR

149

300

44,700

NACS_PCT

157

219

34,383

NACS_SHA

10

211

2,110

QOF

8,372

21

175,812

TRUD

8,366

16

133,856

8

100

800

Relation

AGGREGATE_NATIONAL Total

3,169,021 Table 2 Volume Analysis Summary

Assuming each character is a byte we can estimate the size of the database as being around 3MB. Transaction Analysis Transaction analysis involves determining the expected frequency and number of instances returned by a search query, e.g., viewing all population and diabetes prevalence data for an Aggregate National Region (ANR) in order to assess memory and storage requirements: 1. Select an Aggregate National Region from the AGGREGATE_NATIONAL relation. 2. Select the CLA Names from CENSUS_GOR that match the Aggregate National Region. 3. Extract the statistics from CENSUS_CLA that match the CLA Names. 4. Select the SHA Codes from NACS_SHA that match the Aggregate National Region. 20

5. Select the PCT Codes from NACS_PCT that match the SHA Codes. 6. Select the GP Codes from TRUD that match the PCT Codes. 7. Extract the diabetes data from QOF that match the GP Codes. For each step (see Table 3, below) we identified an average number of instances returned and estimated that this transaction would be run 10 times per hour (period). Step

Type of Access

No. of Instances Returned

Per Period

1

Read

1

10

2

Read

15

10

3

Read

750

10

4

Read

2

10

5

Read

15

10

6

Read

500

10

7

Read

500

10

Table 3 Transaction Analysis for viewing all Data for an Aggregate National Region (ANR) By multiplying the number of instances returned against the column size of each relation we can see how much data will be stored when performing the query (see Table 4, below): (1 x 100) + (15 x 300) + (750 x 233) + (2 x 211) + (15 x 219) + (500 x 16) + (500 x 21) = 201,557 bytes.

Step

Relation

No. of Instances Returned

Column Size

1

AGGREGATE_NATIONAL

1

100

2

CENSUS_GOR

15

300

3

CENSUS_CLA

750

233

4

NACS_SHA

2

211

5

NACS_PCT

15

219

6

TRUD

500

16

7

QOF

500

21

Table 4 Transaction Analysis Results If this was an inefficient process this would mean that to perform the restrictions the DBMS would need to create natural joins between CENSUS_GOR and CENSUS_CLA and between NACS_SHA and NACS_PCT and TRUD and QOF. This would mean a staggering (149 x 11,920) + (157 x 10 x 21

8,372 x 8,366) = 109,964,814,720 rows would be placed in memory. Although we recognise this is not how a modern DBMS works, it will help in generating a baseline figure for comparison. To reduce the number of transactions required de-normalisation can be used to hard code relationships into the entities. For example, rather than a foreign key from NACS_PCT to NACS_SHA we add the SHA Name and SHA Post Code attributes to NACS_PCT and drop NACS_SHA. In general, denormalisation involves identifying a foreign key entry in a primary relation and then transferring the attributes from the secondary relation into the primary relation and removing the duplicated foreign key. By completing this we now have a single entity for clinical data and a single entity for population data which are linked together by two aggregate entities (see Figure 7, below).

Figure 7 Final Physical Database Design To test the hypothesis that de-normalisation will reduce the number of calculations we completed the volume and transaction analysis again. The following changes are apparent in the entities: •

DATA_CENSUS contains 11,920 rows of size 433 (233 + 300 - 100 [duplication of CLA Name]) making the relation size 5,161,360 bytes.

•

DATA_CLINICAL contains 8,366 rows of size 448 (219 + 211 + 21 + 16 – 19 [duplication of GP Code, SHA Code and PCT Code]) making the relation size 3,747,968 bytes. Comparator

Normalised

Un-Normalised

Volume Analysis

3,169,021

8,909,428

Transaction Analysis

201,557

548,850

Natural Joins

109,964,814,720

20,286

Table 5 Comparisons between Normalised and De-Normalised Entities This has changed the transaction steps to: 1. Select an Aggregate National Region from the AGGREGATE_NATIONAL relation. 22

2. Extract the statistics from DATA_CENSUS that match the Aggregate National Region. 3. Extract the diabetes data from DATA_CLINICAL that match the Aggregate National Region. The results are presented in Table 5 (above) which shows that the physical size of the database has increased by approximately 280% from 3,169,021 bytes to 8,909,428 bytes. For transaction analysis, we will be returning an average of 750 rows from DATA_CENSUS and 500 rows from DATA_CLINICAL (based on the averages for Steps 3 and 6 from Table 3). By multiplying the number of instances returned against the column size from the new volume analysis we can see how much data will be stored when performing the query: (1 x 100) + (750 x 433) + (500 x 448) = 548,850 bytes, an increase of over 270%. However, using the calculations for performing restrictions, as used above, the DBMS would not need to create any natural joins and so only 20,286 rows would be placed in memory. This shows that an increase in physical size can improve performance by reducing the number of calculations required to process a query. Attribute

Original Source

Final Relation

Statistic Code, CLA Name, GOR Name, Sex, Age, Ethnic Group, Economic Group, Value

Census

DATA_CENSUS

GP Code

QOF, TRUD

DATA_CLINICAL

GP List Size, Diabetes Cases, Diabetes Prevalence

QOF

DATA_CLINICAL

PCT Code

NACS, TRUD

DATA_CLINICAL

PCT Name, PCT Post Code, SHA Code, SHA Name, SHA Post Code

NACS

DATA_CLINICAL

Aggregate Local Region

N/A

DATA_CENSUS, DATA_CLINICAL, AGGREGATE_LOCAL

N/A

DATA_CENSUS, DATA_CLINICAL, AGGREGATE_NATIONAL

Aggregate National Region

Table 6 Attribute Mappings from Original Source to Final Relation Clarification is presented in Table 6 (above) which shows the attribute mappings from original source to final relation. The final database schema is: DATA_CENSUS (Statistic Code, CLA Name, GOR Name, Aggregate National Region*, Sex, Age, Ethnic Group, Economic Group, Value, Aggregate Local Region*)

23

DATA_CLINICAL (GP Code, GP List Size, Diabetes Cases, Diabetes Prevalence, PCT Code, PCT Name, PCT Post Code, SHA Code, SHA Name, SHA Post Code, Aggregate National Region*, Aggregate Local Region*) AGGREGATE_NATIONAL (Aggregate National Region) AGGREGATE_LOCAL (Aggregate Local Region) To conclude, this section presented the steps taken from understanding the data sets to preparing them for the project and described two techniques - logical and physical database design – used to create the final entities. Volume and transaction analysis was performed and results for a normalised and denormalised schema compared to ensure the most optimal database structure. Granulation of data was discussed and a technique for linking the data sets was described as a suitable extension. The final database design is included in Figure 7 which is a culmination of the logical and physical design.

24

5.

Presentation Phase

In this section we discuss the role of the model-view-controller (MVC) architecture in separating the application into three components: models that comprise functionality and access to data; views that present the user interface; and controllers that manage updates to views (Bennett et al. 2006). This architecture aids code maintainability by enabling multiple views to be created using the same models. We also describe the dynamic charting tool, amCharts, and how it was integrated into the project. When problems arose, consultation took place between the developer and experts who had knowledge of amCharts, in particular from the application’s user forum.

Figure 8 Proposed System Architecture Using the MVC Architecture An overview of the system is presented in Figure 8 (above) which illustrates the application of the MVC architecture. We haven’t included server components as it is possible to store the database, models, controller and views on separate servers (if required) demonstrating the scalability of the architecture. To access data from the database requests are made via the view (user interface) which are processed by the controller which in turn interacts with the models containing the logic to extract data from the database. The results are then passed back to the controller which updates the view in response to the request. For example, if a user requests a list of GPs the system will print back a list to the console of GPs extracted from the database. 5.1

Modelling

Modelling describes the low-level details of the application related to models and controllers. Models are the building blocks of the application and provide an interface between the data and application layers. The way models have been used in this application promotes code re-use as the four main functions - creating a pie and bar chart, data table and exporting data – all have similar components 25

which are described in this section. The application uses two models which represent Aggregate National Region (ANR) and Aggregate Local Region (ALR) concepts. These concepts reference the database, and link together the clinical and population data sets. Both models have similar methods but at different levels of abstraction, with the difference being that the ALR concept accepts a parameter so that results can be obtained for a single ANR. AmCharts is a free, customisable Adobe Flash chart and graph creator available from http://www.amcharts.com/ and supports importing data and settings from comma-separated variables (CSV) or eXtensible Markup Language (XML) files. It has been chosen as it can dynamically render a chart based on files generated from live data. Code can be re-used as the same chart component can be used with a number of different data files. To display a chart the following JavaScript is placed inside the element of a page: 1. <script type="text/javascript" src="swfobject.js"> 2. 3. <script type="text/javascript"> // From the code there are a number of components that are used to create the chart: 1. The amCharts JavaScript library is imported which contains details on how to create the chart along with any other information required by the tool such as directory paths. 2. A container