Transcript On-line Analytical Processing (OLAP)

On-line Analytical Processing (OLAP);
a step forward in surveillance technology
ML Heginbothom on behalf of the Welsh Antimicrobial Study Group
CDSC, NPHS for Wales, Abton House, Wedal Road, Cardiff, CF14 3QX
Aim
Data Sources
Telepath
METHODS
OLAP technology
OLAP technology was used to develop an integrated data
warehouse to include microbiology, pharmacy and bed
data. An ‘OLAP cube’ is the processed multidimensional
database view of the underlying data warehouse.
OLAP provides interactive access to aggregated data
optimised for fast data output to support data analytics and
reporting. OLAP empowers the user with the ability to view
data from different perspectives by creating dynamic
views interactively. The net effect is that most user queries
can be answered almost instantaneously.
DTS
EDS, JAC, AscribeOLAP
Data Access
Data
Warehouse
Methodologies for automated collection and analysis of
standardised microbiology data from laboratory information
management systems and standardised, denominatorDT
based antibiotic prescribing data from pharmacy systems
S
DataStore
were developed using On-line
Analytical Processing (OLAP)
technology.
Data Integration
OLAP technology provides a simple method to integrate,
interrogate, browse and display data from disparate hospital
IT systems; enabling correlations of antibiotic resistance
with antibiotic prescribing at ward, directorate and hospital
S
level. The application of OLAP technology to hospital
DT
infection surveillance willMedusa
be discussed and presented.
Data
Marts
OLAP
PivotTable
Service
Data was imported from various hospital data sources into
the Microsoft SQL server staging area. The staging area
runs the data transformation services (DTS) – these are
processes that clean, transform, combine, and prepare the
data as data marts for use in the data warehouse Analysis
Services software uses OLAP to aggregate the data as a
multidimensional structure that can be viewed via client
software (Figures 1 & 2).
OLAP
OnOn-line Analytical Processing
Microbiology
Pharmacy
Method
Data Warehouse
Microsoft®
Microsoft® SQL Server™
Server™
Client
Application(s)
On-line Analytical Processing (OLAP) technology was
developed for surveillance purposes as part of a current
Department of Health funded project (DH: 0410015); the
main aim of which was to develop cost-effective IT
strategies for automated collection and analysis of
surveillance data on hospital antibiotic resistance and
factors that may affect its occurrence.
Staging Area
Hospital
MSSQL Server
ESR, Oracle, PIMS
SSAS 2005
CONCLUSION
There is a wealth of data, routinely collected by hospitals in electronic
format that may provide direct or surrogate measures of factors
relevant to the occurrence of antibiotic resistant infections. Much of
this data could be extracted economically with IT automation. We
propose to undertake a scoping study that will give an assessment of
the viability and economics of automated collation and presentation
of these data sets.
Other Data
Source(s)
KEY
DTS: Data Transformation Services
Microsoft®
Microsoft® SQL Server™
Server™ 2005
Analysis Services (SSAS 2005)
On-line analytical processing or OLAP is technology that uses a multidimensional view of aggregate data
to provide quick access to strategic information for further analysis.
Figure 1: Schematic drawing of On-line Analytical Processing (OLAP)
OLAP REPORTING USING OFFICE WEB COMPONENTS
The main component of OLAP is the OLAP server which
acts as the intermediary between the client/user and the
traditional database management system (DBMS).
Conclusion
OLAP systems provide a multidimensional view of the
data, extending far beyond the ability of the relational
database management system or spreadsheet.
OLAP technology provides a simple method to integrate,
interrogate, browse and display data from disparate
hospital IT systems; enabling correlations of antibiotic
resistance with antibiotic prescribing at ward, directorate
and hospital level (Figure 2).
A practical demonstration of OLAP technology and it’s
application to surveillance solutions is available. For
further information contact Maggie Heginbothom during
the FIS conference or contact on 02920 524284 or email
[email protected]
Figure 2: Example of OLAP data viewed using the Office Web Component