Skip to main content

Combining information in a data warehouse

We are looking at how best to store data so that we can map trends over months and years, in what is known as a data warehouse.   We do have some digital records already, for example we can looks at trends in student numbers for over a decade.  With more and more processes online, the University administration want to look at trends over a wider range of data, and to ask questions of data that go beyond the individual administration units. 

This is not trivial, especially as we don't know which questions people will want to ask in the future.  We need a flexible data model that allows reports to link data that was taken from more than one operational system.  For example, a question about the cost of running any particular course would need to know about the cost of the rooms used, the staff, library materials, any special equipment, and so on.



We have looked at a number of ways of tackling this problem.  The approach we are now proposing  is to have two levels in our data warehouse.  The first will be a very flexible data model that can integrate information from all sorts of systems.  The second will present greatly simplified views of the data that address a particular set of questions - such as student numbers, or course costs, or research income, and so forth.

The diagram shows the end-to-end model, as it would apply to users in the Estates department.  They would see the operational systems that they use day-to-day (shown on the left), and the strategic reports and dashboards.relevant to their work (shown on the right).  Inbetween, the data warehouse will integrate this data with information from other areas of the University, and preserve it over time.

In a more technical forum, I will write some notes about the details of the data modelling approach.  It's the sort of thing that is very interesting to technical people and rather less so to the people who just want to use the system.


Comments

Popular posts from this blog

Presentation: Putting IT all together

This is a presentation I gave to an audience of University staff: 

In this seminar, I invite you to consider what the University’s online services would be like, if we worked together to design them from the perspective of the student or member of staff who will use them, instead of designing them around the organisational units that provide them. I’ll start with how the services might appear to that student or member of staff, then work back from there to show what this implies for how we work, how we manage our data, and how we integrate our IT systems. It might even lead to changes in our organisational structure.

Our online services make a vital and valued contribution to the work of our students and staff. I argue that with better integration, more consistent user interfaces, and shared data, this contribution could be significantly enhanced.

This practice is called “Enterprise Architecture”. I’ll describe how it consults multiple organisational units and defines a framework …

Service Excellence, Digital Transformation and Enterprise Architecture

Our University Secretary has sponsored a major review of the University’s administrative processes, coining the banner “Service Excellence”.  The aim is to look at the services we provide to staff and students with a fresh eye, making them more effective, more efficient, and focussed on the user rather than administrative convenience.

Our CIO is sponsoring a similar programme called “Digital Transformation”. This will replace old paper-based processes, starting with the question of what would processes look like if we designed them afresh for the modern connected world.  The aim is to make processes that are more focussed on the user and hence more effective and efficient.

Both of these ambitious programmes will need an effective enterprise architecture, if they are to succeed.  Digital Transformation is intrinsically about using opportunities provided by new technology to improve services and, as such, it requires effective technology services to make data available when needed, to pro…

Not so simple...

A common approach to explaining the benefits of Enterprise Architecture is to draw two diagrams: one that shows a complicated mess of interconnections, and one that shows a nicely layered set of blocks. Something like this one, which came from some consultants:


I've never felt entirely happy with this approach.  Yes, we do want to remove as much of the needless complexity and ad-hoc design that litters the existing architecture.  Yes, we do want to simplify the architecture and make it more consistent and intelligible.  But the simplicity of the block diagram shown here is unobtainable in the vast majority of real enterprises.  We have a mixture of in-house development and different third-party systems, some hosted in-house, some on cloud infrastructure and some accessed as software-as-a-service.  For all the talk of standards, vendors use different authentication systems, different integration systems, and different user interfaces.

So the simple block diagram is, basically, a l…