LAMP to be integrated into Jisc’s Learner and Business Analytics R&D activities

Until now, the LAMP project has run in parallel with related activities such as the joint Jisc/HESA/HESPA Business Intelligence and Effective Learner Analytics initiatives.   We are pleased to announce that LAMP will now move forward as an integral part of the overarching learning analytics R&D efforts of Jisc. Specifically, the LAMP project objectives will be combined with those of the Learning Analytics challenge, which is developing a basic student attainment and retention dashboard for all universities and colleges, developing support around navigating the ethics of using analytics about students and providing guidance to help people engage with using learning analytics. A key component of this dashboard will be concerned with the use cases identified by LAMP, and particularly the ability to view library resource usage by subject, course, social demographics and level of student attainment.

This integration with wider analytics efforts requires a more robust and scaleable approach to technical development, and Jisc has started an EU procurement exercise to identify a number of technology providers who can work with us to develop the dashboard. This procurement will also include effort to help identify universities and colleges who are in a good position to act as early adopters for the dashboard, and among these will be institutions that have already contributed to the LAMP project.

For further information and updates on LAMP and Jisc analytics R&D activity,  please now go to:


A Library Analytics and Metrics Service? Moving into the next phase of work

Although we’ve been sharing the work of the LAMP project at the UKSG conference, Jisc Digifest, and the SCONUL conference over the last few months, we realise it’s been a few months since we’ve posted on our progress and intended next steps.

Our work has amassed a lot of interest over the last 6 months, with the leadership in Jisc pointing to it as one of our exemplar projects: responding to clear demand, developed in close collaboration with the stakeholder community, and demonstrating our capability to innovate and develop services in strategically vital areas.

The project is now officially entering into its second stage, aiming to move this exploratory project forward into a fully fledged service. To make this happen, we’re going to be focusing on several areas of work:

Creating a user interface prototype that is easy and pleasurable to use.  We have already developed what we’ve affectionately called the ‘ugly’ prototype, which has allowed us to play with the data and explore the potential for the tools.

This has thrown up all sorts of questions around what level of functionality a data visualisation of this nature should incorporate, and also broader questions over data literacy, and what ‘data analysis’ takes place within the system and what is undertaken by the user herself.  After consultation with our Community Advisory and Planning Group, we have developed a set of wireframes that we feel will support users in analysing their data in different ways, supporting them to view and experiment with the data in different ways, but within a supported environment. We are presently undertaking the technical work to produce v0.1 of LAMP, which will be released in November to the seven partner institutions that have supplied their data: University of Manchester, University of Salford, University of Huddersfield, Wolverhampton University, University of Exeter, De Montfort University, and Lancaster University.

Testing and evaluating the tools. Once the user interface (UI) is released to the institutions, we will be undertaking extensive evaluation of the tools, assessing the usability of the UI, identifying data issues or opportunities, and working to get a better understanding of how tools such as these might fit within library workflows — the benefits they may help deliver, and their overall value. We are also looking at creating a UI with dummy data so that users outside the seven pilot institutions can access and meaningfully experience and experiment with the tools. Outcomes from this work will feed into future versions of the prototype, as well as the overarching business case for the service. We’ll need to understand the value and impact of the service to ensure it’s validity and sustainability.

Beyond v0.1. Bringing in NSS data, institutional profiling and other functions.
The version released in November won’t include institutional profiling (formerly referred to as benchmarking) features, National Student Satisfaction (NSS) data views,  the statistical significance layer, or the ability to look at item level data around individual or batches of resources.  These are all areas identified as priority developments by our Community Advisory and Planning Group and other stakeholders, and we’ll be exploring further how to take them forward over the next few months. Ellen Collins from the Research information Network (RIN) taking the lead and developing specifications where it is feasible to do so, for example, we need to investigate whether NSS data can interoperate with the UCAS data contributed by institutions before we can say we can easily integrate it into the final service. However, our aim is to integrate into the tools:

  • the ability to know whether data is revealing a statistically significant trend or not, i.e. is the disparity between male and female usage on a particular course of significance, or is it merely reflective of the course make up as a whole?)
  • the ability to view resource usage against NSS data, i.e. enabling users to examine the correlation between departmental/subject area usage of resources and NSS scores.
  • the ability to view item level data, so that we users can view overall usage of items or groups of items, and also dig deeper to see who is using those items (which departments, courses, and so on).
  • the ability to view usage of your institution’s resources compared to others using the system, a.k.a. institutional profiling.


Supporting data-driven decision-making — the need for community engagement

We know that our testing of the tools with real users on top of real data will reveal how the tools might be useful. But we also know from our engagement with librarians and bodies such as SCONUL and RLUK over the last year that we’re simultaneously opening up a range of broader questions about the role of data and visualisations in supporting library and institution’s decision-making, the skill-sets and confidence of librarians in working with data in these new ways, and the need to share stories and best practice with the broader community.  We will be developing these case studies as the tools develop, and producing guidance materials based on real use cases, and launching these in spring of 2015.  We recognise there is a need to build a community around Jisc library support and analytics tools, and are in the early stages of planning a wider event around these issues in April 2015. Here we will share the progress of the LAMP work along with similar initiatives, and promote discussion and exploration of the issues surrounding analytics and data-driven decision-making in libraries today.

Beyond measuring loans and logins. Capturing eResource data trails.

Although we can capture eResource logins from many institutions, and tie this to anonymised identifiers that enable us to view the level of eResource usage of particular cohorts, what we can’t tell is what specific eResources, databases, or articles are being viewed by those cohorts. This is an result of the current approach of the UK Access Management Federation, configured to ensure Data Protection and privacy.  However, there are questions over whether it would be feasible to gather and leverage this data in secure ways to support LAMP use cases as well as others, including Learning Analytics.

Indeed, how viable is a service like LAMP if it can only meaningfully track activity around physical items? Jisc and other stakeholders have indicated a strong interest in revisiting this territory so we can identify the opportunities and barriers, and Ben Showers and I look forward to taking this forward in behalf of the LAMP team over the next few months.









Screen flows, wireframes, prototypes and guts.

Following on from my last post where we were looking at gathering user research and requirements, here’s an update on the recent  team workshop where we focussed on the structure and skeleton of the LAMP.

1. The 20 second “Gut” test

We kicked off the session with a 20 Second “Gut” Test, which is technique used to clarify preferences, and better understand the team’s views on the aesthetics of visual design. The test showed a screen capture of 20 different analytics dashboards / user interfaces / visual elements, for 20 seconds only. Each participant has to score their gut reaction to the slide 1-5 (5 being the highest) and make any notes.

This was a really enlightening exercise, which really helped the team to articulate what they did and didn’t like. Looking at our top /bottom 5 there were very apparent themes running between them:

  • Clean and simple style, with space to let the content breath
  • Informative charts with the right balance of detail
  • Modular blocks of content held within frames
  • Restricted colour palette
  • Visualisations of the data were honest and unadorned.

2. Developing the screen flows and navigation model

The navigation model is the big picture, or the “birds eye view” of the system. It considers where users start, how they get from here to there, and what all of the major elements will be. This can then be summarised as a flow diagram that the models the user journeys.

Storyboarding the user experience

  • We started off as a group using our understanding of the tool to build a prototype screen flow.
  • We then validated this against the real life use cases / job stories.
  • We then tried to break the system we’d created. What were the extreme limitations of the system, and had these been taken into account.


This followed an iterative design process: Sketch > Prototype > Present > Validate > Repeat until we had exhausted our time. Collectively  we had formulated a solid idea that, that has been validated against our user research. This can now be taken away and explored in more detail by the UX team, modelled and then presented to the CAB for feedback.

3. Getting into the details

Low fidelity prototyping

Based on the screen flows and navigation model we had created it was clear that there were 2 key areas of action to focus on; the chart creation screen and the Dashboard area where charts are stored. We wanted to start understanding these in more detail and begin to wireframe the user journey, the interactions and functions of these screens. We had generated loads of ideas for these screens and we needed to capture them. We’re not intending hammer down every detail but rather to create a consensus that can be refined outside of the workshop.

The eight guiding principles of prototyping

  1. Understand your audience and intent
  2. Plan a little – prototype the rest
  3. Set expectations
  4. You can sketch
  5. It’s a prototype — Not the Mona Lisa!
  6. If you cant make it, fake it
  7. Prototype only what you need
  8. Reduce risk prototype early and often.

6-8-5 Design studio.

The first iteration of wireframing follows a 6-8-5 rule – do 6-8 sketches, on an 8-up grid in 5 minutes. The sketches can be different versions of a particular aspect you’re working on or a storyboard workflow (before, during and after login) or mix and match! Keep it high level, and get just enough detail down, to convey your concept. When the 5 min is up each person presents their ideas and the group critiques the ideas.

Quantity trumps quality at first.

The idea here is to get a large quantity of ideas rather than quality.  Here’s a short example to illustrate what we mean by this.

“A ceramics teacher announced he was dividing his class into two groups. All those on the left side of the studio would be graded solely on the quantity of work they produced, all those on the right graded solely on its quality.

His procedure was simple: on the final day of class he would weigh the work of the “quantity” group: 50 pounds of pots rated an A, 40 pounds a B, and so on. Those being graded on “quality”, however, needed to produce only one pot – albeit a perfect one – to get an A.

Well, come grading time and a curious fact emerged: the works of highest quality were all produced by the group being graded for quantity!

It seems that while the “quantity” group was busily churning out piles of work – and learning from their mistakes – the “quality” group had sat theorizing about perfection, and in the end had little more to show for their efforts than grandiose theories and a pile of dead clay.”

This story perfectly articulates one of the fundamental Lean UX principles: prioritize making over analysis. Instead of sitting around, debating ad nauseam which direction to go in, what features make sense, which colors perfectly reflect your brand values or which words will get your customers to convert, just make something. It won’t be perfect. It won’t work as well as you’d hoped at first but it will teach you something. You’ll get some feedback, some insight on how building your product can be better and you’ll do a better job the second time around.

A lot of the methods and ideas we’ve used in this workshop have been taken from ‘Prototyping – A practitioner’s guide’  by Todd Zaki Warfel ( In his book he talks about the value of prototyping, the value of show, tell and experience.

Prototyping reduces misinterpretation

Take a 60 page requirements document. Bring in 15 people into a room. Hand it out. Let them read it all. Now ask them what you’re building. You’re going to get 15 different answers. Prototypes are a more concrete and tactile representation of the system you’re building. They provide tangible experiences.

He then goes on to say moving from  requirements-dependant process to a prototype-dependant process has increased consensus on interpretation to 60-80% to +90%. It also requires far less effort and time for everyone involved. Taking this user centred design approach is essential for LAMP as the system is still being explored, designed and interpreted. Manifesting the development work in a physical form helps to generate hundreds of ideas, some will be great, some will be not so. But even these not so great ideas can be the catalyst for great solutions.

The ideas that were generated will be explored in more detail by the UX team, modelled, and validated. We are then meeting again for another wire framing session with the LAMP team to work through more of these details ready to presented to the CAB for feedback.

Sketch > Prototype > Present > Validate > Repeat


So what do we mean when we say ‘Analytics’?

This is a guest post by David Kay of Sero Consulting who describes some of the project’s work to develop user stories and enable a better understanding of the kinds of functionality any shared analytics service would need to have

Analytics has become quite a buzzword over the past couple of years. Awareness has been promoted by general talk of ‘big data’ as well as by increasing emphasis in the sector on the student experience and success factors (linked to ‘Learning Analytics’) and on resource optimisation driven by economic constraints.

Furthermore EDUCAUSE and the Gates Foundation in the US and Jisc in the UK have enabled notable exploratory work.

And now every new generation library systems offering needs the ‘Analytics’ badge.

But what does analytics mean to library professionals UK Higher Education? Is analytics ‘all things to all men’ or simply ‘the emperor’s new clothes’ (formerly known as management reporting or the director’s dashboard)?

So in Autumn 2013 the LAMP project set out to discover what library teams really have on their minds. Whilst LAMP is specifically focussed on the opportunities for shared services around library analytics, we stepped back to the underlying question of ‘What do libraries want to achieve with analytics?’ regardless of whether a shared service can help (our second order question as a project being to identify the cases where LAMP might help).

A total of eleven libraries working with the LAMP project agreed to develop a set of User Stories describing what they would like to achieve with analytics. We agreed to a two-step process whereby seven libraries were interviewed to source a set of stories and then the wider group (the original seven and four more) voted on the relevance of the stories (around 90) from their local perspective.

Thanks go to the library teams at the universities of Birmingham, De Montfort, Exeter, Huddersfield, Hull, Manchester, Warwick, Wolverhampton, York, the London Business School and the Open University.

About User Stories

User Stories are recognised as a valuable tool for capturing clearly focused requirements to inform software development. For the purpose of this investigation, a user story was taken to be a statement in the form of:

As a (role),
I want (a thing)
in order to (achieve an outcome)

For example

As a (late riser),
I want (to get my breakfast quickly)
in order (to catch the train)

We’d consider that to be an ‘epic’ story, from which a number of more detailed stories about real requirements might be teased out; for example

As a (late riser),
I want (a four-slice toaster)
in order (to make breakfast quicker)

I want (a folding bike)
in order (to get to the station quicker)

The stories we collected from library teams fell in to both these categories – epic stories that described the mission to which analytics might contribute and lower level descriptions of how particular analytic activities might deliver or contribute to key outcomes; for example, the mission might be

As a (library manager)
I want (more comprehensive activity data)
in order (to improve student satisfaction)

That mission might be unpacked in to

I want (front desk enquiry analysis)
in order (to improve first level resolution)


I want (short loan turn away data)
in order (to expand the collection to meet demand)

What‘s analytics about? Our Library Stories

So what did our 11 libraries consider the most important contributions to be made by analytics?

As described above, we collected around 90 stories and then put them to the vote! Our voting system allowed a library to allocate 2 points for any story they regarded as ‘important’ and 1 point for a ‘useful’ story. Therefore a story regarded as ‘important’ by everyone could gain 22 points (11 libraries x 2 points). The 49 stories that gained over one third of the maximum points (i.e. 8/22) are listed here.

We classified 19 stories of those 49 as ‘epic’ or ‘mission’ stories – very interesting because they indicate the management and strategic purposes that library analytics need to serve. They are as follows:

We classified 30 of the 49 as ‘activity’ stories – more detailed things that librarians want to do with analytics. They are as follows:

Some reflections

You’ll see from the listings above that we categorised each statement in terms of its broad intent:

  • Mission – High level ‘mission’ statements that are ‘epic’ user stories
  • Data – Stories about the range of data available for analysis
  • Collection – Use of analytics for collection management
  • Service – Use of analytics for service improvement, including enquiries
  • Teaching & Learning – Use of analytics to enhance the learning experience and student success
  • Recommendation – Use of analytics to provide recommender services

It is important to observe that the principal focus of the ‘mission’ stories is collection management (AN) and its contribution to each of value (M), satisfaction (D) and impact (C). There is also strong recognition of analytics as a tool in:

  • Supporting dialogue with faculty (K)
  • Evidencing and positioning library business cases (A, F)
  • Proactively enabling support activity such as skills development to be better designed and targeted (V, AB, AS, AD)

Whilst the ‘activity’ stories mainly speak for themselves, the challenge for libraries and for systems providers is to identify what data is required to support these requirements and how it might feasibly be collected within and across systems.

  • The focus on e-resources emphasises this challenge as represented in two of the top three activity stories (38, 4, also 19) – especially linking e-resource activity to users just as we are accustomed to doing with print.
  • There is a persistent recognition that insightful analytics need to combine data from more than just a single vendor system (2, 29, 32, 1).
  • More firmly within grasp is the use of analytics to respond more effectively to differentiations in terms of faculty (14, 9) and user demographics (33).
  • Analytics relating to enquiry management and related service improvement is an important dimension (29, 48, 54)
  • Whilst clearly recognised as an opportunity (61, 62, 34), there is less emphasis on using analytics for recommendation, surfacing reading options for users as popularised by such as Amazon.
  • Last but not least, we shouldn’t underestimate that presentation is a critical part of the challenge (8, 9)

There is much food for thought here, hopefully informing how services might be developed to exploit the data pool in which ‘no system is an island’!

Whilst JUSP and LAMP are partnering with UK academic libraries to develop responses in specific areas, it is clear from our User Stories that library analytics will demand considerable thought and may reveal even greater potential as our understanding and practices mature.

Custom Normalisations


At the recent 17 October LAMP CAP meeting, I gave a presentation starting from my last blog posts and detailing some of the progress I have been making along a number of separate lines. I know in my last post I said I would be talking about statistics next, but during parallel development of the various architectural components it turned out that some other pieces of work slotted in before then, and so I want to do a quick series of posts going into more detail from my talk.

In this post, I want to build upon previous discussions of normalising data for statistical analysis and discuss the idea of LAMP users being able to use custom normalisations. The other posts I want to write will cover data content standardisation, our database schema, our API, and the graph drawing portion of our user interface respectively.

Problems with Fixed Normalisations

At the moment, the LAMP database uses fixed data normalisation. This means we are collecting records together into similar groups based on their content (e.g. Ethnicities of ‘White’, ‘White African’ and ‘White Irish’ all become grouped together as ‘White’ for statistical purposes), but that the LAMP team have decided what those groupings should be based on an overview of the data which providers have submitted to us.

We’re expecting that these fixed normalisations will cover the majority of use cases fro LAMP, but let me use a user story example to illustrate a situation in which the fixed normalisations will not be sufficient, and to introduce custom normalisations as a solution.

Suppose someone is interested in what percentage of people get first class degrees at their institution, and for diversity purposes wants to break that down by ethnicity. They could ask the LAMP API for this information and get back the following (fictional) graph, which has been normalised (as described above) by grouping related records together into common categories for anonymisation and statistical analysis.

Graph 1: Percentage of first class degrees by ethnicity

Not KnownOtherAsianBlackWhiteChinese00.

Normalisations used to build Graph 1

Content from provider Normalised group
“Asian – Pakistani” “Asian”
“Black – Other” “Black”
“White/Black Caribbn” “Other”
“Asian – Indian” “Asian”
“White and Asian” “Other”
“White Irish” “White”
“Other White Background” “White”
“Black – Caribbean” “Black”
“White” “White”
“Asian – Other” “Asian”
“Black – African” “Black”
“Information Refused” “Not known”
“Asian – Chinese” “Chinese”
“Asian – Bangladeshi” “Asian”
“White/Black African” “Other”
“Latin American” “Other”
“Other Mixed” “Other”
“Other” “Other”

Now, let’s say this particular institution is looking to break into South America and so is interested in the performance of its Latin American Students in particular, but not bothered at all about separating out Chinese students from the rest of Asia. At the moment, as mentioned above, the ethnicity normalisations which appear in graph 1 are a fixed default set of normalisations. In other words, you currently cannot change the categories to focus in on Latin America.

This seems quite restrictive, and so it has been suggested that we should not tie users of LAMP to the groupings we have chosen. The only way to get around this is if users are able to supply a list of their own ‘custom’ groupings if they prefer. Initially, I assumed this would just be a case of the user submitting a list (like the one next to graph 1) to our API when they run their query, and asking the API to use that list instead.

An Example of Custom Normalisations

Since the normalisations table doing the work is similar to the one above, then my initial idea was that altering the normalisations groupings as below and submitting those up to the API would solve the problem of custom normalisations.

Content from provider LAMP Normalised group Custom Normalised group
“Asian – Pakistani” “Asian” “Asian”
“Black – Other” “Black” “Black”
“White/Black Caribbn” “Other” “Other”
“Asian – Indian” “Asian” “Asian”
“White and Asian” “Other” “Other”
“White Irish” “White” “White”
“Other White Background” “White” “White”
“Black – Caribbean” “Black” “Black”
“White” “White” “White”
“Asian – Other” “Asian” “Asian”
“Black – African” “Black” “Black”
“Information Refused” “Not known” “Not known”
“Asian – Chinese” “Chinese” “Asian”
“Asian – Bangladeshi” “Asian” “Asian”
“White/Black African” “Other” “Other”
“Latin American” “Other” “Latin American”
“Other Mixed” “Other” “Other”
“Other” “Other” “Other”

Making the changes detailed in the right-hand column and submitting them to the API would then result in the following graph being output instead:

Not KnownOtherAsianBlackWhiteLatin American00.

Unfortunately, although this is the right general idea, the tables above are limited to the output from just one (fictitious) provider. When I thought about this process in the context of multiple providers, a barrier to this approach became evident. I’ll talk about this barrier more, and how standardising the content of data fields like those in the left-hand columns above will be necessary for a solution, in my next post.

Personas, job stories and simple planes: wireframing a LAMP interface

Let me start by introducing myself – my name is Benjamin Perry  and I am the Creative Design Coordinator at Mimas. My role on this project is alongside Leigh Morris as the User Experience (UX) Team who are responsible for designing the website itself. As part of this team it’s crucial that I not only understand but am involved with the Information Architecture and the User Interaction Design (the Skeleton Plane – more about planes below), However my particular focus is the visual appearance of the website itself (the Surface Plane).

Having recently read the seminal work of Jesse James Garrett on The Elements of User Experience , it’s clear that where I join this project it has already been through some good User Centered Design (UCD) processes. In Garrett’s book he talks about UX design consisting of 5 layers; the Strategy Plane, the Scope Plane, the Structure Plane, the Skeleton Plane and finally the Surface Plane. “These five planes provide a conceptual framework for talking about user experience problems and the tools we use to solve them”


Jesse James Garrett's simple planes Jesse James Garrett’s simple planes

As you work through each of the planes the issues that you have to deal with move from being abstract to more concrete. Each of these planes is dependent on the ones below it, but this does not mean that each plane needs to be finalized before the next can be started. It’s much better to be flexible; sometimes decisions made on an upper plane may force a reevaluation (or an evaluation for the first time) of an issue from a lower plane.

As I said before my dealings are mainly with the Skeleton and Surface planes, so I’m very much at the sharp end of the process. My work requires the planes before these to have been clearly defined (but not finalized!). As much as I’d like it, I don’t ever expect to be handed all the project brief and documentation then sail through the work without having any questions and get sign off first go – It’s never going to happen. User Centered Design is core to our business at Mimas so the UX team need to be the ones preaching this gospel the loudest. If we can enlighten people to the process we use and give them the tools to help work through the issues that they are faced with, then we will not only build better products but our jobs will be made much easier too.

So lets take a step back and look at what’s happened so far:

David Kay has been looking at the Epic Level narrative with the User Stories. These have been essential to help provide a clear business case for this project and define its strategy – the Strategy Plane.

Bethan Ruddock  has then used these to create some Personas, which have been used to create workflows. These are step-by-step guides that detail how a user would potentially perform a tasks based around of the features of the website (we’ve made these available). These have been used to start to flesh out the Scope Plane and also start to inform the Structure Plane.

The UX team has then taken these workflows along with some early prototype wireframes and visuals to create interactive walkthroughs of how these tasks might be achieved in the UI (we used InVision to do this – its fantastic!) – Structure, Skeleton and Surface Planes.

These have been essential to help generate discussion not only within the team but the wider LAMP CAP group. Seeing something physical in front of you is very powerful and it certainly highlighted some of the issues on the lower planes that needed to be readdressed or hadn’t yet been addressed.

In discussing these issues I was reminded of this blog post by Alan Klement  that Leigh had found which introduced the idea of Job Stories. In his post he summarises –

“… the problem with user stories is that it’s too many assumptions and doesn’t acknowledge causality. When a task is put in the format of a user story (As a [type of user], I want [some action], so that [outcome] ) there’s no room to ask ‘why’ – you’re essentially locked into a particular sequence with no context.”

Klement says with mature defined projects his team were able to talk through and understand proposed changes however “because our canvas is blank, we are having trouble getting on the same page when it comes to customer motivations, events and expectations. But today, things have turned around. I’ve come across a great way to use the jobs to be done philosophy to help define features. I call them Job Stories”

This immediately drew parallels with our project and seemed a natural solution for us for us to gather more information to inform our UCD process. We needed to get a more definitive idea of what people want to do with LAMP but also wrap that in real world context and expectations. With the CAP group full of future users of the site we thought this would be the perfect opportunity to introduce this idea and get them to tell us about their own Job Stories. So we gave the group blank forms to fill in following Klement’s process: (situation) When _____ , (Motivation) I want to _____ , (Expected Outcome) so I can _____ .

Getting the CAP group to think in this way was really positive. The information we collected is helping to define the features for this first phase of the project as well as generating discussion around future enhancements.

As a first outing using Job Stories we found this to be very successful. I’m not sure we did this in the same way that Alan Klement did it with his team, but it certainly generated lots of discussion, highlighted issues and gave us much more information to work with than we had before. What’s more, this information is not based on assumptions, as with the personas, but based on real life users, which is what we set out to achieve. You can see the job stories the CAP created.

We’ll be using the information and insights from these job stories as we work on the next stage of developing the LAMP interface.

Standardising Data Content to Allow For Custom Normalisations


In my previous post, I set out to describe a potential infrastructure which would allow users of LAMP to choose how records are normalised by defining their own groupings, but then I mentioned that I had spotted a barrier to doing this easily — that every institution which has submitted data to LAMP describes the same content in a different way. This is like the difference on a web page between a free text field and a drop down list — at the moment, institutions are submitting their data to us as if they had filled in a free text field.

For this post, I’ll explain a little more about standardising the content of the data fields. The process of standardising data content is analogous to converting the ‘free text’ values mentioned above into a fixed set such as the ones you might find on a drop-down list.

I’ve already introduced the idea of standardising, but last time it was with field names from different providers where they hold the same conceptual data (e.g. Country of Domicile from one provider means the same thing as Nationality from another). This time, we’ll be focusing on the content of the fields instead.

Our Current Fixed Normalisations and the Barriers to Custom Normalisations

Let’s consider an example which exposes how difficult it would currently be for users to supply custom normalisations. For the sake of this example, I have only focused on all the various types of Ethnicity that LAMP are currently grouping together as ‘Asian’, with the exception of Chinese, which we are currently putting in a different ‘Chinese’ group.

You can see a sample of the fixed normalisations table we currently have inside the LAMP database below. So far this covers content from four different providers.

1. a sample of Ethnicities from our current comprehensive normalisations table (visualised below as a flow diagram)
institution original_contents normalised_contents
1 Asian Other Asian
1 Bangladeshi Asian
1 Indian Asian
1 Pakistani Asian
1 Chinese Chinese
2 Asian or Asian British – Bangladeshi Asian
2 Asian or Asian British – Indian Asian
2 Asian or Asian British – Pakistani Asian
2 Other Asian background Asian
2 Chinese Chinese
3 Asian or Asian Brisith – Pakistani Asian
3 Asian or Asian British – Bangladeshi Asian
3 Asian or Asian British – Indian Asian
3 Asian – Other Asian
3 Chinese Chinese
4 3[^4] Asian
4 6 Asian
4 34 Chinese

Error generating Graphviz image:

Graphviz cannot generate graph
Command: /usr/bin/dot '-Kdot' '-Tpng' '-o/blogs/wordpress/wp-content/tfo-graphviz/85d3ed9ea91310e55bc27a8ee1f3a365.png'
Warning: : syntax error in line 21 near '-'

Original DOT:
    2 digraph table1{
    3 graph[rankdir="LR"];
    4 subgraph clusterNormalised {
    5 graph[label="normalised_contents"];
    6 node[shape="trapezium"];
    7 "Chinese (n)";
    8 "Asian";
    9 }
   10 subgraph clusterInstitutions {
   11 graph[label="institution"];
   12 node[shape="parallelogram"];
   13 1;
   14 2;
   15 3;
   16 4;
   17 }
   18 subgraph clusterRaw {
   19 graph[label="original_contents"];
   20 node[shape="rectangle"];
   21 1->"Asian Other"->"Asian";
   22 1->"Bangladeshi"->"Asian";
   23 1->"Indian"->"Asian";
   24 1->"Pakistani"->"Asian";
   25 1->"Chinese"->"Chinese (n)";
   26 edge[color="red"];
   27 2->"Asian or Asian British - Bangladeshi"->"Asian";
   28 2->"Asian or Asian British - Indian"->"Asian";
   29 2->"Asian or Asian British - Pakistani"->"Asian";
   30 2->"Other Asian background"->"Asian";
   31 2->"Chinese";
   32 edge[color="blue"];
   33 3->"Asian or Asian Brisith - Pakistani"->"Asian";
   34 3->"Asian or Asian British - Bangladeshi";
   35 3->"Asian or Asian British - Indian";
   36 3->"Asian - Other"->"Asian";
   37 3->"Chinese";
   38 edge[color="green"];
   39 4->"3[^4]"->"Asian";
   40 4->6->"Asian";
   41 4->34->"Chinese (n)";
   42 }
   43 }

In this simplified example, let’s suppose our user doesn’t want to use ‘Chinese’ as a grouping, but would prefer for their business purposes to only use ‘Asian’ for everything in that region. My assumption from my last post was that the user would be able to achieve this by submitting a simple custom normalisations table up to the API. On the other hand, in order for the LAMP application to offer all the same functionality as with our default normalisations, the user would effectively have to submit their own custom implementation of table 1.

The word simple above is the key — as you can see, table 1 is quite complicated owing to different institutions representing ethnicities differently (institution 1 uses ‘Indian’, for example, whereas institution 2 uses ‘Asian or Asian British – Indian’, and so on). Thinking along these lines, in order for a user to draw up their custom copy of table 1, they would need to know all of the possible entries for every different institution. This is not simple at all for the user!

Data Content Standardisation as a Solution

In order to simplify the table which users will need to supply in order to generate custom normalisations, we will need to insert an extra step into table 1. At the moment, we look at the content of the data and decide which normalised grouping it belongs in. If instead, we first look at the content of the data and replace it with a value chosen from a standard list of LAMP-certified values, we can then look at the standardised values and group them into normalisations as a second step. Finally, in order to supply a custom normalisation, a user only needs to know our list of LAMP-standardised values and put them into their own groupings.

In our example, to standardise the values, we would replace the values in table 2a) with the corresponding value from table 2b):

2. Standardising data content
a) the original list of possible ethnicities from our normalisations table
Asian Other
Asian or Asian British – Bangladeshi
Asian or Asian British – Indian
Asian or Asian British – Pakistani
Other Asian background
Asian or Asian Brisith – Pakistani
Asian – Other
b) A suggested list of standardised replacements
Asian – Other
Asian British
Asian – Any

Splitting table 1 into two steps can now be achieved as shown in table 3. The first part would be to use a lookup table to standardise all the different values from different institutions into the ones in table 2b. Ideally, the end user generally doesn’t need to see or know about this step — it would be something we did inside the LAMP application. The result would look something like table 3a):

3. Replacing the normalisations table with two tables: a) a standardisations table…
institution original_contents standardised_contents
1 Asian Other Asian – Other
1 Bangladeshi Bangladeshi
1 Indian Indian
1 Pakistani Pakistani
1 Chinese Chinese
2 Asian or Asian British – Bangladeshi Bangladeshi
2 Asian or Asian British – Indian Indian
2 Asian or Asian British – Pakistani Pakistani
2 Other Asian background Asian – Other
2 Chinese Chinese
3 Asian or Asian Brisith – Pakistani Pakistani
3 Asian or Asian British – Bangladeshi Bangladeshi
3 Asian or Asian British – Indian Indian
3 Asian – Other Asian – Other
3 Chinese Chinese
4 3[^4] Asian – Any
4 6 Pakistani
4 34 Chinese
… and b) a normalisations table
standardised contents normalised_contents
Asian – Other Asian
Bangladeshi Asian
Indian Asian
Pakistani Asian
Chinese Chinese
Asian British Asian
Asian – Any Asian

Error generating Graphviz image:

Graphviz cannot generate graph
Command: /usr/bin/dot '-Kdot' '-Tpng' '-o/blogs/wordpress/wp-content/tfo-graphviz/9727d7839350636bb5c025654663ff79.png'
Warning: : syntax error in line 31 near '-'

Original DOT:
    2 digraph table3{
    3 graph[rankdir="LR"];
    4 subgraph clusterNormalised {
    5 graph[label="normalised_contents"];
    6 node[shape="trapezium"];
    7 "Chinese (n)";
    8 "Asian";
    9 }
   10 subgraph clusterInstitutions {
   11 graph[label="institution"];
   12 node[shape="parallelogram"];
   13 1;
   14 2;
   15 3;
   16 4;
   17 }
   18 subgraph clusterStandard {
   19 graph[label="standardisation"];
   20 node[shape="diamond"];
   21 "Asian - Any";
   22 "Pakistani (s)";
   23 "Asian - Other (s)";
   24 "Indian (s)";
   25 "Bangladeshi (s)";
   26 "Chinese (s)";
   27 }
   28 subgraph clusterRaw {
   29 graph[label="original_contents"];
   30 node[shape="rectangle"];
   31 1->"Asian Other"->"Asian - Other (s)"->"Asian";
   32 1->"Bangladeshi"->"Bangladeshi (s)"->"Asian";
   33 1->"Indian"->"Indian (s)"->"Asian";
   34 1->"Pakistani"->"Pakistani (s)"->"Asian";
   35 1->"Chinese"->"Chinese (s)"->"Chinese (n)";
   36 edge[color="red"];
   37 2->"Asian or Asian British - Bangladeshi"->"Bangladeshi (s)";
   38 2->"Asian or Asian British - Indian"->"Indian (s)";
   39 2->"Asian or Asian British - Pakistani"->"Pakistani (s)";
   40 2->"Other Asian background"->"Asian - Other (s)";
   41 2->"Chinese";
   42 edge[color="blue"];
   43 3->"Asian or Asian Brisith - Pakistani"->"Pakistani (s)";
   44 3->"Asian or Asian British - Bangladeshi";
   45 3->"Asian or Asian British - Indian";
   46 3->"Asian - Other"->"Asian - Other (s)";
   47 3->"Chinese";
   48 edge[color="green"];
   49 4->"3[^4]"->"Asian - Any"->"Asian";
   50 4->6->"Pakistani (s)";
   51 4->34->"Chinese (s)";
   52 }
   53 }

The normalisations step is now performed by a much simpler second lookup table (3b) ) which groups these new standard field contents into the LAMP default categories. In this new table it no longer matters which institution the data originally came from, which makes it a much simpler table.

Custom Normalisations, Revisited

The end result after introducing data content standardisation will be that if you want to specify your own custom normalisation, you will only need to submit something like the following (which groups ‘Chinese’ into ‘Asian’ instead) up to the API:

5. Proposed structure of an alternative normalisation for the data in table 1, which could be submitted by a user to our API
standardised contents target_column normalised_contents
Asian – Other Ethnicity Asian
Bangladeshi Ethnicity Asian
Indian Ethnicity Asian
Pakistani Ethnicity Asian
Chinese Ethnicity Asian
Asian British Ethnicity Asian
Asian – Any Ethnicity Asian

Hopefully this clears up why we want to focus on content standardisation, as well as how we’ll be going about it! The ideas above will obviously result in some slight changes to our database schema, and how our API works, and so in my next posts I want to talk about both of those aspects of our architecture.

It’s time to talk about standards

Look, this is a library project. You knew the s-word was going to come up at some point.

One of LAMP’s most important attributes is that it’s bigger than a single institution. While we want individual universities to be able to upload and interrogate their own data through the platform, we also want to offer them somewhere that they can aggregate with and benchmark against their peers. The tools that we build have to meet the needs of a lot of different people.

We’ve written before about some of the tricky decisions we’re taking about how we standardise and reclassify the data that we get, in order to make sure that it can work with LAMP’s systems, and can be aggregated across institutions. But a recent conference call with the team who are managing Wollongong University’s Library Cube service reminded us that there’s another way to do this: looking at the way we ask that information to be provided in the first place and creating clear standards which help institutions to collect their data the way that we want them to.

A bit of background.

The Library Cube is a pretty well-established initiative from Wollongong University Library which seeks to collect and analyse data from a number of systems to understand how libraries add value. Wollongong have been working on this service for several years and the scope is now extending beyond assessing library value to thinking about real-time data and service development. We’ve been aware of their work through the links they had made with the Huddersfield Library Impact Data Project and the opportunity came up to share progress on their project and on LAMP.

Now, previous work we’ve done on normalisation has tended to be about how we might aggregate groups that are classified differently in different organisations. Subjects are particularly tricky for this, as every university has its own way of organising courses and departments. These decision are taken locally, and it’s improbable that a university’s academic departments will be completely reorganised to meet the needs of a project on library analytics (well, we can dream!).

But the conversation with Wollongong highlighted some areas where we might have a bit more control, and could think about asserting standards and/or best practice about how data are collected and supplied. Take, for example, e-resource logins. These datasets are huge, recording every login from every student over the course of a year. To simplify our analysis for the LIDP at Huddersfield and subsequently with LAMP, we looked at how many times a student had logged in during a given hour over the course of a year, for each of the 24 hours in the day. Wollongong did the same, but their time period was ten minutes.

This means that comparing our data isn’t straightforward. There’s no intrinsic reason that we picked an hour, and that they picked ten minutes; both have advantages and disadvantages. The ten-minute data will give a more nuanced analysis, while the hour-by-hour data will be easier to process. Both choices are valid. But because we made them separately and individually, we didn’t necessarily think about the wider ramifications of our eventual decisions.

Of course, doing a project such as LAMP will begin to set some informal standards, simply because we’re asking for data in particular formats. But, as our conversation with Wollongong made clear, it’s important that we don’t allow those informal standards to evolve into more widely-accepted ones without interrogating and testing them. LAMP isn’t happening in isolation; there’s a wider set of projects especially in Australia and the US which are looking at library analytics and measurement.

Over the next few months, we hope to start talking about the best ways to collect and share data, building on our experiences and that of others, to ensure that LAMP’s collaborative ethos extends to some bigger conversations about library data and capturing library value.

Diagnosing uncertainty: A premortem for LAMP

A few weeks ago (okay, it was early July!), LAMP had the second meeting of its community advisory and planning (CAP) group.

The meeting started with an update on the work of the project so far, and sort advice and input from the group on a number of challenges.

A number of these challenges have already been blogged about, including the technical architecture; the use of unique identifiers associated with the data; data normalisation and statistical analysis, and; designing the database.

Importantly, the project has also drafted Terms and Conditions for institutions submitting data to the project. This is a small, but critical part of the project being able to get data from institutions.

There is a lot happening with LAMP at the moment (as these posts highlight); but what of the future?

The LAMP Premortem

In the afternoon the group undertook a premortem of the project, facilitated by Andy McGregor (of Jisc, but also a member of the CAP group).

The premortem imagines a future where the project has been a failure, and participants must work backwards to understand what contributed to the project’s failure.






Despite the slightly gloomy sounding description, the exercise is actually a huge amount of fun, and generated some really useful insights and ideas for the project team to take away.

What follows is a brief outline of some of the main themes that emerged during the premortem and specific ideas for the project team (and CAP group) to work on.


It was clear that the technical side of things could result in a number of significant risks. The majority of the technical risks actually related to the expectations libraries, our potential users, may have of the prototype service.

It was therefore clear that the project would need to be careful to not over-sell the service; making it clear this project is about collaboration and a large amount of learning as we progress (both the project and the libraries).Some of the possible ways to address these challenges included:

  • Expect some failure in certain areas – a complex project like this may mean not everything will work as expected;
  • Logging and learning as we go, and seek help from institutions/CAP group.
  • Guest blog posts from the community group (maybe around each of the categories identified).


The project will need to expend considerable energy on understanding user requirements; testing the prototypes with different user groups (librarians, reistrars etc).

This also means we need to be able to show users the prototype when it’s still rough and messy, so they have no qualms about providing critical and immediate feedback.

Fortunately we have our Community group to help us test the prototypes and to constantly challenge our assumptions and ideas.

Legal and Ethical

Legal and ethical issues were another significant concern that emerged during the premortem.

Many of the issues revolved around being able to reassure institutional registrars and CIOs about the way the data will be used, and ensure there is no possibility of damage to institutional reputations.

In many ways this is a subtle problem, requiring the project to deal with legal, ethical and reputational issues.

Some possible ways to address these problems included:

  • Use Jisc Legal: Discuss potential issues associated with the project and develop some pre-emptive resources and guidance for institutions;
  •  Produce a legal ‘toolkit’ for institutions and libraries – this might include advice and guidance as well as best practice.

Finally, there was a suggestion that the project, or rather the prototype service, provide the ability for institutions to ‘opt-out‘. This might be an out out clause in any agreement, that also makes it clear how libraries can disengage from the service and what happens to their data – how it is given back to them.

This is an interesting issue, and reminds me of the ‘right to be forgotten’ debate, and is critical legal and ethical issue for the project to consider.


This particular concern is not about things like competitive advantage (the project is very clear that it is meeting a need that falls outside the ability of commercial vendors to meet – an explicit principle of the project is to not duplicate existing product functionality).

Rather, the project needs to ensure it is aware of vendor developments for reasons of interoperability and the possibility of additional functionality for existing systems.

It will be important that LAMP’s API can feed into commercial vendor products. 

Cost and Complexity

This is a critical issue for institutions: The benefits of the service must outway the costs of participation.

Initially, as the prototype is developed the balance of benefits may be outweighed by the challenges of providing the project with data: The complexities of engaging are largely borne by the institutions.

But this will have to rapidly evolve, so that the service is able to absorb much of this complexity and make institutional engagement simple and worthwhile.

Ways the project can start to address this concern includes:

  • Develop some best practice and guidance for participating institutions. Make it clear what they need to do and how (a LAMP manual!);
  • Tools for making the submission of data simple – the service should do the heavy-lifting for institutions;
  • Where possible, link to other institutional systems and data services, or enable these links to be made as easily as possible;
  • Clearly articulate the benefits for the participating institutions – almost a service level agreement (almost!). This might also be done through case-studies with some of the early adopter institutions.


This was a popular challenge for the project – unsurprisingly!

However, in a clever and possibly illegal move, we simply parked it with the following rationale:

Such a risk/challenge is almost always inherited by a project; it’s not simply going to go away. We can park this issue for now, and focus on those risks that are likely to blind-side us.

Of course, that’s not to say it’s not a critical issue that needs addressing. But we can keep in mind that this phase of the project is about demonstrating the feasibility of the prototype. Indeed, this feasibility phase may not succeed – which will require us to think carefully about how the project might be wrapped up or changed.


This is just a very brief overview of the issues and risks that surfaced during the premortem. The exercise was incredibly useful in providing the project with both the key challenges it needs to address, but also an opportunity to crowd-source some of the potential solutions and actions to address those issues.

What, at first glance, appears to be a slightly pessimistic and gloomy activity turned out to be a vibrant session with some useful concrete outcomes.

Having said that, there were one or two ‘doomsday’ scenarios described, including:

  • The Internet ‘goes down’ and there’s no way to get access to the service.

Fingers crossed this won’t happen – but it makes it clear we should double-check on our disaster planning protocols.


Two of the CAP group members also blogged about the meeting and the premortem exercise:

Paul Stainthorp (Lincoln): LAMP project: A lets pretend post-it note post-mortem

Richard Nurse (OU): The Pre-mortem


Creating the LAMP database

We now have data from three different LAMP partners, and we’ve started looking at the structure of the data. On one hand, we are interested in how we normalise the data for statistical analysis, but on the other hand, we also need to start thinking about how data is going to be consumed by the LAMP application. In my previous post regarding the architecture of the LAMP system as a whole, I looked at some theoretical architectures which might be a good fit for the application’s requirements as we understand them right now, and the common point in all of the architecture options I have considered is the LAMP database.

Data Structure Concerns

It has been suggested that we retain the full granularity of data as supplied to us from the partners (although in my anonymous UIDs post I noted that this data will not be available to end users), which in itself raises some interesting challenges. Each partner is storing and sending different pieces of information, kept in different columns. For example, from one provider we might see something like column one of the following table, and from another something like column 2. As you’ll see below, some columns clearly mean the same thing (and I have lined them up accordingly), some are possibly similar, and others have no analogues between different partners.

Provider One Provider Two
user# Identifier
Ethnicity ethnicity
Country of Domicile nationality
Country of Domicile Region
Gender gender
Disability disability
UCAS Tariff Points Tariff
Age on entry
Mode of Attendance Attendance Mode
Class Code
Course Code Prog Code
Course Name Course name
Course Type Course type
JACS code
Location of Study Campus
Franchised Out
Enrolment End Date Date of graduation
Agreed Award final award
Agreed Classification
Agreed Overall mark
loans Loans per borrower
total E Number of different E-resources accessed
all visits Number of visits

In designing a database, one sets out to work out which pieces of data will be present in a database table. For any one partner, this is easy — we are being given the tables directly. Add more than one feed like the ones above, however, and there are some choices to make in our database design regarding how we standardise the column names when they clearly mean the same thing.

Access Control Concerns

The next factor which may influence our choice of database structure is security. Database products such as Postgresql come with built in security, so access can be restricted to certain database users on a per-table basis. This is certainly an appealing model for controlling partners having full access to their own data — we could create one database user for each LAMP partner, and perform access control accordingly.

The difficulty with that access model, however, comes when we want to run analyses which compare data from multiple partners, which implies a degree of access to each others’ data. Of course, we would only be allowing access to other partners data after it had been standardised and normalised, but in order to perform the calculations, access will still be required at a level which is far more granular than the LAMP project wants to expose. And this is the crux of the matter — the kind of statistical comparisons people require mean that it is simply not possible to avoid the LAMP statistics layer having read access to all the normalised data.

The implication of this is that we will not be able to use database access control alone to restrict access to the full granularity of the normalised data. Instead, either our API or our statistics layer will have to use software routines to implement its own filters and sanity checks based on credentials supplied to the API. Only after these have been passed will results from cross-provider queries be returned to the user.

Outside of cross-provider queries, there will still be the need to restrict access to raw data solely to the provider who submitted it, and it remains to be seen whether or not database access control will be able to play a part in achieving this or whether it too will be purely achieved via software routines.

Some Design Options

  1. One table per provider, with normalisations and column name standardisations in a lookup table:

    Error generating Graphviz image:

    Graphviz cannot generate graph
    Command: /usr/bin/dot '-Kdot' '-Tpng' '-o/blogs/wordpress/wp-content/tfo-graphviz/c3047af0fad4741ce1fe25fae80c5c55.png'
    Warning: : syntax error in line 6 near '-'
    Original DOT:
        2 digraph op1{
        3 graph[rankdir="LR"]
        4 subgraph clusterDB{
        5 graph[label="Database"]
        6 Normalisations -> Standardisations -> "Provider 1 table";
        7 Standardisations -> "Provider 2 table";
        8 Standardisations -> "Provider 3 table";
        9 node[shape="trapezium"]
       10 "Statistical Comparison Queries" -> Normalisations;
       11 "Private Queries" -> "Provider 1 table";
       12 "Private Queries" -> "Provider 2 table";
       13 "Private Queries" -> "Provider 3 table";
       14 }}

    The benefit of this approach would be that the original detail submitted by providers would appear in the database, one provider per table. This would also mean making use of database access control on a per-table basis was an option. However, the need to constantly perform lookup routines in order to standardise and normalise the data for comparison could impact performance and result in quite complicated queries, and there would also have to be at least one database user with read access to all the tables for the purposes of running such comparisons.
  2. One table with standardised column names for all providers, with normalisations in a separate lookup table:

    Error generating Graphviz image:

    Graphviz cannot generate graph
    Command: /usr/bin/dot '-Kdot' '-Tpng' '-o/blogs/wordpress/wp-content/tfo-graphviz/cfc16ef1a9a1e902bc0a4c29ad4aeb4e.png'
    Warning: : syntax error in line 6 near '-'
    Original DOT:
        2 digraph op1{
        3 graph[rankdir="LR"]
        4 subgraph clusterDB{
        5 graph[label="Database"]
        6 Standardisations -> "All Providers' Standardised Data";
        7 "Filter for only onenProvider's data" -> "All Providers' Standardised Data";
        8 Normalisations -> "All Providers' Standardised Data";
        9 node[shape="trapezium"]
       10 "Statistical Comparison Queries" -> Normalisations;
       11 "Private Queries" -> "Filter for only onenProvider's data";
       12 }
       13 "Provider 1 raw" -> Standardisations;
       14 "Provider 2 raw" -> Standardisations;
       15 "Provider 3 raw" -> Standardisations;
       16 }

    This may be a ‘best of both worlds’ solution — we would hold the full level of detail submitted to us by providers in our database, in a single table with standardised headings. Queries could then be run through normalisation routines when comparison and statistics are required, but institutions would still be able to get at the data they submitted (albeit standardised) for other types of analysis which would be private to their LAMP dashboard. From an access control pespective, this scenario would rely entirely on software checks — at the provider filter, and on the comparison query results — in order to protect data.
  3. One normalised, standardised table, with no access to original data:

    Error generating Graphviz image:

    Graphviz cannot generate graph
    Command: /usr/bin/dot '-Kdot' '-Tpng' '-o/blogs/wordpress/wp-content/tfo-graphviz/26bc031cec0deeb4314ee2db64e8c63e.png'
    Warning: : syntax error in line 6 near '-'
    Original DOT:
        2 digraph op1{
        3 graph[rankdir="LR"]
        4 subgraph clusterDB{
        5 graph[label="Database"]
        6 Standardisations -> Normalisations -> "All Providers' Normalised Data";
        7 node[shape="trapezium"]
        8 "Statistical Comparison Queries" -> "All Providers' Normalised Data";
        9 }
       10 "Provider 1 raw" -> Standardisations;
       11 "Provider 2 raw" -> Standardisations;
       12 "Provider 3 raw" -> Standardisations;
       13 }

    This scenario would probably perform best and keep queries simple, but has the drawback that the full level of detail in the data as submitted by the providers would not be held in the database, which would limit us to only running queries on the normalised data. Since a number of the LAMP use cases seem to involve providers wanting to store their data with us and query it in place, this option is pretty much ruled out. Restricting access to the detailed normalised data, as in the previous example, would be completely done in software.
  4. Using redundancy and holding both raw individual tables as well as a standardised/normalised combined one

    Error generating Graphviz image:

    Graphviz cannot generate graph
    Command: /usr/bin/dot '-Kdot' '-Tpng' '-o/blogs/wordpress/wp-content/tfo-graphviz/a1c5501eadf600068966f36dc1a9009b.png'
    Warning: : syntax error in line 6 near '-'
    Original DOT:
        2 digraph op1{
        3 graph[rankdir="LR"]
        4 subgraph clusterDB{
        5 graph[label="Database"]
        6 Standardisations -> Normalisations -> "All Providers' Normalised Data";
        7 "Provider 1 table";
        8 "Provider 2 table";
        9 "Provider 3 table";
       10 node[shape="trapezium"]
       11 "Private Queries" -> "Provider 1 table";
       12 "Private Queries" -> "Provider 2 table";
       13 "Private Queries" -> "Provider 3 table";
       14 "Statistical Comparison Queries" -> "All Providers' Normalised Data";
       15 }
       16 "Provider 1 raw" -> Standardisations;
       17 "Provider 2 raw" -> Standardisations;
       18 "Provider 3 raw" -> Standardisations;
       19 "Provider 1 raw" -> "Provider 1 table";
       20 "Provider 2 raw" -> "Provider 2 table";
       21 "Provider 3 raw" -> "Provider 3 table";
       23 }

    Another option exists whereby we could combine options one and three — the raw data goes back in the database, and database user accounts are reintroduced to control access to it as in option one. However, as in option three, the normalised table is also present in the database, for combined queries which are regulated by the API. For even higher levels of security, the raw and the normalised tables don’t strictly speaking even need to be in the same database!

    This last option would really be an implementation suited to high levels of paranoia regarding the raw data and our API’s software safeguards, and faith that the normalisation routines do a good enough job of anonymising that data to justify the combined table not being subject to the same levels of security.

At the moment I’m leaning towards option two — we can reverse-lookup the standardisations if partners absolutely need their original column headings back, but having all the data in one standardised table will help with both private and combined queries. Since database access control cannot offer us the security required in our application, we will need to implement software checks in any case, so we may as well embrace the fact and get on with how those checks will work! It’s conceivable that option 4 might perform better, as the need to do standardisation and/or normalisation lookups at query-time is removed, but we’ll keep an eye on that as we test and build the database.

In my next post, I’m hoping to go into more detail about the statistics layer, and how we implement some of the routines Ellen blogged about, leading into how we build our API!