DATA WAREHOUSE
Turin -Italy
ASL 3 • One of the four metropolitan local health units of Turin (920.000 inhabitants) • Two sanitary districts (221.000 people) • Two Hospitals: 22.500 hospital discharges/year • 41 clinic divisions • First Level Emergency department • 1.800 employees (Salaries: 70 millions euro/year) • Out of Hospital drugs’ expenditure: 42 millions euro • Annual budget: 209 millions euro
Life in the Pre Data Warehouse Period … Accountancy Drugs prescriptions
Hospital Discharges
Strategic management
DW
AIMS Support to decision-making activities
Multidimensional navigation by OLAP technology
Intuitive and user-friendly utilization
Reporting and statistical analysis DW
DATABASES • SDO (i.e. inpatients care: ordinary and day hospital treatments) • Ambulatory visits - laboratory and radiographic analysis (Prest-C) • Emergency departments visits (Prest-C2) • Analysis and visits during hospitalization (Prest-C4) • Out of hospital drugs’ consumption (Prest-D) • Accountancy • Supplying • Amortizable goods • Salaries • Population (Social Security number, district, physician’s code etc.) • General practitioner’s database (identification code, age etc.) DW
DATA WAREHOUSE ASL 3: OLIAMM
AP_ANA_MAG AM_C_MAG AM_D_MAG
AP_TESMOV MT_MAG MT_ANNO MT_NUM MT_DTMOV MT_TIPMOV MT_CDC
AP_PRODCONTO PC_PROD PC_ANNO PC_FATPRO PC_PROGPDC PC_CONTO AP_RIGMOV MR_MAG MR_ANNO MR_NUM MR_IMPORTO MR_PROD MR_QTA
CE_CDC CC_COD CC_DESC1
AP_CLMAZIE CA_C_CLM CA_D_CLM CA_D_CLM2 CA_C_CLMSUP CA_LIV CA_ULTLIV
AP_ANAPRO AP_PROD AP_DESCR AP_CLM AP_UM AP_CODIFA AP_GEST
AP_CODIFA FA_COD FA_DESCR FA_ATC FA_MINSAN10
AP_ATC AT_COD AT_DESCR
AMORTIZABLE GOODS CS_AMMORT AM_AZ AM_TIPO AM_CODCES AM_ANNO AM_PROGPDC AM_CTIMM AM_FATPRO AM_QUOTA
CO_CONTI CO_PROG CO_COD CO_DESC CO_CODSUP CO_LIV CO_ULTLIV CO_NATCON
ACCOUNTS’ CODIFICATION
SUPPLYING
CS_CESPITI CP_AZ CP_TIPNUM CP_CODCES CP_PROD CP_CODCDC CP_DTAMMORT CP_QTA CP_BASE
CS_TIPNUM TN_TIPNUM TN_DESC
ACCOUNTANCY
CO_CONTIFP CF_ANNO CF_PROGPDC CF_COD CF_FATPRO
CS_VARS VS_AZ VS_TIPNUM VS_CODCES VS_ANNO VS_NUM VS_CDCIN VS_CDFI CO_MOVCON MC_AZ MC_ANNOPN MC_TIPPN MC_NUMPN MC_RIGAPN MC_CAUSALE MC_DESC MC_DTREG MC_PROGPDC MC_CONTO
CS_MOVPROT MP_AZ MP_ANNO MP_NUM MP_DTMOV
CO_MOVCDC M1_AZ M1_ANNOPN M1_TIPPN M1_NUMPN M1_RIGAPN M1_CDC M1_IMPORTO CO_CAUSALI CA_COD CA_DESC
DW
DATA WAREHOUSE ASL 3: RELATIONSHIPS SALARY MATRICOLA STIP. CDC
CODIFICATIONS REP_RICOV CDC
SDO CODFISC PZ. REP_RICOV REP_TRAS1 REP_TRAS2 REP_TRAS3 REP_TRAS4 REP_TRAS5 REP_DIMISS
CODIFICATIONS BRANCA CDC
OLIAMM (CE_CDC) CC_COD CC_DESC1
PREST-C2 COD. ISTITUTO (STS.11) CODFISC PZ
PREST-C COD. ISTITUTO (STS.11) CODICE MMG CODFISC PZ BRANCA
GENERAL SURGEON’S FILE CODICE MMG CODFISC PZ.
PREST-C4 COD. ISTITUTO (STS.11) REP. RICOV REP. EROGAT CODFISCALE
PREST-D CODICE MMG CODFISC PZ. CODIFA
AP_CODIFA FA_COD FA_DESCR FA_ATC
DW
Hospital’s discharges - ASL3 view Prest-A (SDO) Az_erogan
5101 5102 5103 5104
not103
=103
Usl_resid=103
Usl_resid=103 o not 103
tipo_ric=1
tipo_ric=2 (DH)
tipo_ric=1
tipo_ric=2 (DH)
FP 5103
FP 5104
FP 5101
FP 5102
CDC 010101
CDC 010101
Rep_ricov CDC ASL3
Rep_ricov CDC ASL3
ASL3 Hospital Discharges ASL3 DH Other ASL Discharges Other ASL Discharges
DW
Hospital’s discharges - District’s view Prest-A (SDO)
5507 5508 5509 5510
Tipo_ric=1
Tipo_ric=2 (DH)
Patient's Social security number
Patient's Social security number
Phisycian's code
Phisycian's code
District 1 If USL=04
District 2 If USL=05
District 1 If USL=04
District 2 If USL=05
FP 5507
FP 5508
FP 5509
FP 5510
CDC 211103
CDC 211203
CDC 211103
CDC 211203
Ho s pita liz a tio n o f dis trict 1 pa tie nt Ho s pita liz a tio n o f dis trict 2 pa tie nt Da y ho s pita l o f dis trict 1 pa tie nt Da y ho s pita l o f dis trict 2 pa tie nt
DW
Emergency Department
Out of hospital’s drug consumption Prest-D
Prest-C2 Patient's Social security number Phisycian's code
Codice Medico prescrittore Phisycian's code
District 1 if USL=04
District 2 If USL=05
District 1 If USL=04
District 2 If USL=05
FP 5505
FP 5506
FP 5501
FP 5502
CDC 211103
CDC 211203
CDC 211103
CDC 211203
5505 District 1 patients (E.R. services) 5506 District 2 patients (E.R. services)
5501 District 1 patients (Drugs) 5502 District 2 patients (Drugs)
DW
Statistical applications: •Multiple regression •Logistic regression •Forecasting analysis •“What if” scenarios
DW
HARDWARE AND SOFTWARE Pentium III biprocessor SAS/Base SAS/STAT SAS/Graph SAS/Pc File Formats SAS/MDDB server c.p. Enterprise Guide SAS/Fsp SAS/EIS SAS/ODBC
SAS/Base SAS/Connect SAS/Oracle SAS/MDDB Server SAS/MDDB server c.p.
DW
SPECIAL THANKS SISTEMI TERRITORIALI (Edoardo Bracci, Vittoria Liguori, Antonio Pezzella) Dott. DARIO TOGLIATTO Dott. EMANUELE D. RUFFINO Dott.ROBERTO CARIGNANO Dott.ssa FAUSTA BALZARRO SERVIZIO TECNICO (G. Lucarelli, C. Gamba, Ing. Capra) ANNA SPADONE PATRIZIA ANASTASIO ENGISANITA’ (Ing. Consonni) SAS INSTITUTE (Ing. Silvestrin) DOTT. GIUSEPPE DE INTINIS
DW
Budgeting, data analysis and management reporting on the data warehouse of ASL 3 in Turin, Italy. G.Gallone – ASL 3 Turin V.Liguori – Sistemi Territoriali S.r.l
The protection of the health in Italy is part of the fundamental principles of the Italian Constitution; in fact, article 32 of the fundamental charter states: “The Republic protects the health as the fundamental right of the individual and the interest of the collectivity, and guarantees free cures to the indigent ones. Nobody can be obliged to a determined sanitary treatment if not for law disposition. The law can in no case violate the limits set up by the respect of the human person”. The high costs of the Italian Sanitary System (SSN) based on the principles of solidarity and equality (as in the rest of Europe), have imposed criteria of effectiveness, appropriateness of the distributed performances and efficiency. Therefore, thanks to the two recent laws that reformed the SSN (dlgs 502/92 and dlgs 229/99), we came to consider the sanitary structures as business ones. Such transformation has forced both to respect the fundamental principles of the Constitutional charter and to put into effect an accounting method and budgeting in all and all similar to the one in use from companies that do not erogate sanitary services. Such companies, still remaining property of the State, are forced to balance their budget putting into effect cost/efficacy politics and accounting for centres of cost, publishing budgets of economicpatrimonial type. The characteristics of such managerial approach have introduced in the companies the concept of centre of cost, centre of responsibility and productive factors. The centre of cost is a centre of homogenous expense as far as the type of distributed performances or organizational logistics are concerned. The centre of responsibility can enclose more centres of cost and refers to a manager, responsible for sustained costs and distributed services. This new activity of control of the distributed or prescribed services, the greater attention to the data and the information flows (i.e., introduction of the card for hospital resignation, automated registration of pharmaceutical and ambulatory prescriptions) has determined a remarkable increase of the data to process, and the obvious necessity to face them with investments in human resources, technicians and technological supplies. The ASL 3 (Local Health Institution) is one of the four metropolitan ASL in Turin, and it has 1800 employees. It serves a population of 216,000 inhabitants and is equipped with two hospitals which provide nearly 20,000 hospitalizations a year, and numerous territorial structures. The regional financing for the 2002 consists in approximately 400 billions Euro. The amount of information is remarkable and extremely varied, and the analysis of all the informative sources in a separate way evidently turns out to be dispersive and cannot provide a global vision of the business situation. The need of introducing new methodologies of data acquisition, data cleansing and data distribution is strongly growing up. New ways of data representations like multidimensional reports and graphs are requested every day. ASL 3 of Turin has developed a data warehouse containing the main informative flows and accounting data, finalizing them to the creation of an interactive budget card that concurs to plan the activities of the single Operating Units (both clinical and administrative) and to the multidimensional analysis in a “data mining” vision. The data warehouse collects data coming from different informative sources, in different formats, and makes them available at the same time on only one common platform. The use of appropriate software permits to carry out more detailed and efficient analysis, crossing variables from different data bases, which previously were available only uncombined.
The global vision of all activities, deriving from a complete integration of the data, concurs to plan business strategies in a better way and allows supplying a system of reusable reports without delays from the Department and Operating Unit managers. The main flows consist of: - cards for resignation of all the shelters (ordinary or day hospital) - ambulatory prescriptions - pharmaceutical prescriptions - inner advising between units - performances in emergency aid - supplying data (drainages of warehouse and direct deliveries) - accounting data of costs and revenue - sources of income (and their amortizations) - salaries divided by single voices together with reflected burdens - patients with personal identifying data, tax identification number, sanitary identification number and district belonging - general medicine doctors
APPROVVIGIONAM
AP_ANA_MAG AM_C_MAG AM_D_MAG
AP_TESMOV MT_MAG MT_ANNO MT_NUM MT_DTMOV MT_TIPMOV MT_CDC
AP_PRODCONTO PC_PROD PC_ANNO PC_FATPRO PC_PROGPDC PC_CONTO
AP_RIGMOV MR_MAG MR_ANNO MR_NUM MR_IMPORTO MR_PROD MR_QTA
AP_ANAPRO AP_PROD AP_DESCR AP_CLM AP_UM AP_CODIFA AP_GEST
CS_AMMORT AM_AZ AM_TIPO AM_CODCES AM_ANNO AM_PROGPDC AM_CTIMM AM_FATPRO AM_QUOTA
CO_CONTI CO_PROG CO_COD CO_DESC CO_CODSUP CO_LIV CO_ULTLIV CO_NATCON
CE_CDC CC_COD CC_DESC1
CS_TIPNUM TN_TIPNUM TN_DESC
TRANSCOD. CDC-CONTI CONTABILI
AP_CLMAZIE CA_C_CLM CA_D_CLM CA_D_CLM2 CA_C_CLMSUP CA_LIV CA_ULTLIV
CS_CESPITI CP_AZ CP_TIPNUM CP_CODCES CP_PROD CP_CODCDC CP_DTAMMORT CP_QTA CP_BASE
CESPI
CO_CONTIFP CF_ANNO CF_PROGPDC CF_COD CF_FATPRO
AP_CODIFA FA_COD FA_DESCR FA_ATC FA_MINSAN10
CS_VARS VS_AZ VS_TIPNUM VS_CODCES VS_ANNO VS_NUM VS_CDCIN VS_CDFI
CO_MOVCON MC_AZ MC_ANNOPN MC_TIPPN MC_NUMPN MC_RIGAPN MC_CAUSALE MC_DESC MC_DTREG MC_PROGPDC MC_CONTO
Tab. 1 – Example of a basic data flow for supplying and accounting data
AP_ATC AT_COD AT_DESCR
CS_MOVPROT MP_AZ MP_ANNO MP_NUM MP_DTMOV
CO_MOVCDC M1_AZ M1_ANNOPN M1_TIPPN M1_NUMPN M1_RIGAPN M1_CDC M1_IMPORTO
CO_CAUSALI CA_COD CA_DESC
Every record represents a single hospitalization/prescription for a patient or a single movement/product of warehouse. Prest-A (SDO) Az_erogan diversa da 103
=103
Usl_resid=103
Usl_resid=103 o diversa da 103
tipo_ric=1 (ordinario)
tipo_ric=2 (DH)
tipo_ric=1 (ordinario)
tipo_ric=2 (DH)
FP 5103
FP 5104
FP 5101
FP 5102
CDC 010101
CDC 010101
Rep_ricov CDC come da tabella
Rep_ricov CDC come da tabella
Tab. 2 - Centre of cost for resignation from shelter (SDO)
All these data make up a system of multidimensional cubes, separate by flow type or aggregation, where the analysis and the exporting is guided by a user-friendly interface that does not ask for specific training on the software used. The organization of the data warehouse concurs to elaborate one budget card for every Operating Unit (centre of cost) and for different time slices (year, quarter, month). Every budget card contains productive factors, costs, revenues and, for the clinical Operating Units, hospitalizations data and appropriateness of shelters (i.e. essential levels of assistance, day surgery). Through some unique codes (i.e.. tax identification numbers and code of medical doctor) it has been possible to attribute to the two territorial districts the costs of the pharmaceutics and also those deriving from the use of hospital structures (i.e.., Through shelters or ambulatory performances) allowing a preliminary structuring of the district budget regulated by the financial institution of 2001. Multidimensional navigation and ad-hoc statistical analysis together with detailed reporting offer the most complete information delivery to all managers of Operating Units. Some screen captures of the application will follow as exemplification of its usage.
Tab. 3 - Main menu
Tab. 4 – Report for for resignation from shelter (SDO)
Tab. 5 – 3D-Graph for for resignation from shelter (SDO)
Tab. 6 – Setting parameters for budget output
Tab. 7 – Budget in html format
The whole system has been developed through SAS® 8.2 software on three client platform (situated to the manager control room) and a Windows NT server. The modules installed for each client (SAS/GRAPH®, SAS/AF®, SAS/STAT®, SAS/CONNECT®, SAS/EIS®, SAS/ACCESS®, SAS/MDDB® software) are used to visualize and to create multidimensional cubes and allow the extraction of data from different formats, to create interactive graphs and diagrams, to manage geographical maps and to produce statistical analysis. In addition to these modules, the trained staff has benefited of an innovative instrument (Enterprise Guide® software) which concurs to obtain easy-made reporting and statistical analysis without knowledge of the SAS® syntax. The modules installed on the server machine are used to extract data of economic-patrimonial accounting from an Oracle server, situated in another hospital site. The remaining data are extracted via Internet through a console of the regional data processing centre in a text or dbf format. The output produced from SAS® is exported in PDF format in order to be sent it via e-mail. Future developments are the integration of data warehouse in a web-oriented system, using the Web, so that the Operating Unit managers can directly access the information they need whenever they need and with any layout needed. The application has been developed in collaboration with Sistemi Territoriali s.r.l., a certified partner of SAS, and with the involvement of the Management Control, the Quality Office and the Informative System of the ASL 3.