DATA WAREHOUSE. Turin -Italy

DATA WAREHOUSE Turin -Italy ASL 3 • One of the four metropolitan local health units of Turin (920.000 inhabitants) • Two sanitary districts (221.00...
Author: Alexis Hensley
6 downloads 2 Views 1MB Size
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.