보건소 의사결정지원을 위한 데이터웨어하우스 구축에 대한 연구

Size: px

Start display at page:

Download "보건소 의사결정지원을 위한 데이터웨어하우스 구축에 대한 연구"

홍철 도
6 years ago
Views:

3 ..,.,,,,,,,,,,,

4 I II OLTP OLAP KMS(Knowledge Management System)

5 III ETT(Extraction, Transformation, Transportation) 7 2. OLAP(On Line Analytical Processing) 9 2 IV ETT OLAP V. 53 VI

6 60

7 1. OLAP OLTP OLAP K

8 PTS ODS DTS ETT OLAP Microsoft Excel 2000( ) Microsoft Excel 2000( ) Microsoft Excel 2000( )

9 ..,,.. (data warehouse) ODS ODS.. Microsoft SQL Server 2000 OLAP(On-Line Analytical Processing : ) Microsoft Analysis Services. (Pilot). ASP,

10 HTML, Java Script, Olectra 6.0 Chart Control....

11 ,.,,., (knowledge management)., (datawarehouse), (data-mining), OLAP(On Line Analytical Processing)..,

12 .,,, (, 1998)..,,.

13 K,..,.,,, OLAP(On-Line Analytical Processing).,.

14 1980IBM, IBM (information warehouse).,,, 1980 Inmon(1994) ' '. Poe(1996) ' '., (warehousing). Gardner(1998) ' '.,. Inmon,,,

15 . (view).(butler Group, 1999) (Historical Information)., (Operational Environment). (Subject-Oriented), (Integration), (Time Variant), (Non-Volatile). (Subject Orientation) (Subject Area) (Operational System),.,..,

16 .,. (Integration).,. (Time Variancy).,. (,,, ). UPDATE. (Non-Volatility)

17 . Current Detail Data : Old Detail Data : ( ) Lightly Summarized Data : Highly Summarized Data : Metadata :, OLTP..,,. OLTP. ODS(Operational Data Store).

18 Fact table, Dimension table, summary table, meta data.,, 4.( 1) 1.

19 2... ODBC. ODBC - (CLI) ODBC.,. OLE DB. OLE DB COM API. OLE DB.

20 ISAM(Indexed Sequential Access Method)/VSAM(Virtual Storage Access Method), HDB(Hierarchy Database) OLE DB ODBC..,.,, DTS. (ETT : Extraction, Transformation and Transportation ETL : Extraction, Transformation and Loading). DTS. OLE DB. - DTS. Microsoft Repository OIM((Open Information Model). DTS,,.,

21 . Analysis Services,,, OLAP. DTS. DTS.... DTS... DTS....

22 . Analysis Services., OLAP. (PTS ) Analysis Services (Analysis Server)-. (Excel.),.. PTS. Analysis Services PTS. PTS. Analysis Services MDX(Multidimensional expressions),

23 HTTP HTML PivotTable. PTS.

24 (online analytical processing, OLAP) RDBMS(relational database management system) OLAP (online transaction processing, OLTP) (Codd, 1993). OLAP ꡒ (, 1996). OLAP (Arbor, Comshare, IRI, Pilot ) ꡒOLAP Councilꡓ OLAP ꡒFast Analysis of Shared Multidimensional Information(FASMI)ꡓ.,. OLAP 4.

25 OLAP,, MOLAP, ROLAP, DOLAP, HOLAP.

26 ,,,., OLTP (cleansing) (transformation).. OLAP (, 1996).,.

27 ( ) (,,, ) 2,,, ( ),, (, / ),,, (Off-the-Shelf, (4GL) Customizing / Out-of-Box) Customizing / EUC(End User Computing)

28 ,.., (, 1998).,,, (Nonaka & Takeuch, 1999).,, (Ruggles, 1998).,, (Prusak, 1998).,,, (Nonaka, 1991).

29 ...,,, 4.(, 2001) IQ,,. 4.,,,

30 ,,..,.. 3 -,..,,

31 ., ,, EVA,,, TQM,...,

32 .....,..

34 K 1995,.,,,. OLTP(On Line Transection Process)... RWM(Rapid Warehouse Methodology).

35 . 1) 2) 3) 4) 5).,,.,,...,.,. (Pilot System) (Prototype).

36 ,,.,,,..,,,,, (Aging)/ (Achieving).,,,.,. OLAP. ETT,,.,.

37 . 5).,,,..,,.. ETT (Extraction) (Transformation), (Transportation),,., DBMS(DataBase Management System),,,., ETT. ETT

38 ,,,,,,,.,. Microsoft SQL Server 2000 ETT DTS(Data Transformation Service)....,.

39 ,.,.,.. ODS(Operational Data Store).,,,,,. (Repository), OLAP OLAP.

40 K,.. ODS,, ETT Microsoft SQL Server 2000 Microsoft Analysis Services. (Pilot). 5.

41 1),, ( 2002).

42 OLAP, = ( / ) 100 = ( / ) 100, ( ),,, DT rate( )

43 OLAP,,,,,,,,,,,,,,,,,,,,

44 .,

45 . Analysis Services. Application.. Analysis Services Web Server.. Analysis Services Web Server.

46 Analysis Services Analysis Services. client

47 . client.. Cache Server WAN Traffic Local Traffic,, HTTP, FTP, SMTP, POP3, NNTP,., DNS URI IP URI.

48 3 4. Network traffic fact dimension fact dimension.... DW,. DW, DW....

49 .,. Client.. 1 K,,,,,. K

50 DB PEOPLE ORCL FAMILY ORCL INSUNUMB ORCL CLIENT ORCL CLIENT1 ORCL HCLIENT ( ) ORCL DISECODE ORCL DATAPILE ORCL CHK_SUM ORCL X_RAY ORCL CHECK DBF ILBANVAC ORCL HBSCODE DBF PREVCODE DBF VAGR_RE1 ORCL TAGI ORCL CHC01 DBF CHC02 6 DBF CHC03 18 DBF CHC04 DBF MHC01 DBF MHC02 DBF MHC03 DBF MEDICODE ORCL MEDISTOCK ORCL ZIP DBF

51 2,,. OLTP. ODS(Operational Data Store). 3,,... factdata. 4 ODS(Operational Data Store) ODS OLTP

52 , OLTP. 8. ODS

53 5 ODS.,,,, , ID,,,,,, code ID,, TimeID,, ID,, TimeID,,,,, ID,, TimeID,, ID,, TimeID,,,,, Time TimeID,,,,,,,,, ID,, PW,

54 9..

55 ETT ODS, ODS, ODS,. Microsoft SQL Server 2000 DTS ETT. 10. DTS ETT

56 OLAP. SQL2000 olap manager.( 11) 11. OLAP

57 4. OLAP CUBE. (Pilot). Microsoft Excel 2000 Analysis Services. 12. Microsoft Excel 2000( )

58 12 Microsoft Excel 2000( ) Microsoft Analysis Services., Microsoft Excel 2000( )

59 14. Microsoft Excel 2000( ) 14. 2D 3D.

60 , ASP, HTML, Java Script, (Web Components) Olectra 6.0 Chart Control.

61 16 OLAP

68 . -L (Customer Relationship Management based on Knowledge Management -The case of L convenience store) KAIST Graduated School of Management, 2002, ꡒ :, 1999, ꡒ 2 ꡓ, 1999., 2002., , 2002, ꡒ ꡓ,1999, ꡒ ꡓ, 1999,. Ⅰ,Ⅱ, , , 2000,. OLAP., 1999

69 Bremer W & Winslow C, ꡒFutureworkꡓ Brobst S. D/W. CIO, 2001 Clancey WJ, ꡒPractice cannot be reduced to theory: Knowledge Representations and change in the work placeꡓ Davenport TH & Prusak L, ꡒWorking Knowledgeꡓ, 1998 Drucker PF, ꡒPost-Capitalist Societyꡓ, 1993 Ewen EF, Medsker CE, Dusterhoft LE. Data Warehousing in an Integrated Health System; Building the Business Case. DOLAP 98 Washington DC USA ACM, Inmon WH, Building the Data Warehouse(2nd Ed.). John Wiley & Sons, Inc., 1996 Liebowitz J & Wilcox LC (Editor), ꡒKnowledge Management and Its Intergrative Elementsꡓ Myers PS (Editor),ꡒKnowledge Management and Organizational Designꡓ Pedersen TB, Jensen CS. Multidimensional Data Modeling for Complex Data. Proc. of the 10th IEEE Int l Conference on Scientific and Statistical Database Management, 1999; Spek R & Spijikervet A, ꡒKnowledge management: Dealing intelligently with knowledgeꡓ, 1997

70 Abstract Development of a Data Warehouse for Decision Support in Public Health Center Yoo Heon Ko Graduate School of Health Science and Management Yonsei University (Directed by Professor Young Moon Chae, Ph. D.) With improvement in our economy and rising interest in health, the role of public health centers in our society is increasing. In order for health centers to provide quality services, there is a need for an information system that provides essential information to health center managers to support their decision-making based on a comprehensive data warehouse which contains decision-related information. In this study, model data warehouses that support decision-making were presented using centralized and decentralized approaches at three levels: community, province, and the nation. In the centralized model, data warehouse would not be constructed at an individual health center. Instead,

71 provincial data warehouse would be constructed at the province level using the data collected from health centers. The national data warehouse would be constructed using the data collected from the provincial data warehouse. On the other hand, data warehouse would be constructed at every health center and provincial department in a decentralized model. Advantages as well as methods for constructing data warehouse in a centralized model were presented. Microsoft SQL Server 2000 was used to design the data warehouse and Microsoft Analysis Services was applied for OLAP(On-Line Analytical Processing). The decision support system for health center was developed using graphs and spreadsheets, which enables the user to easily access data warehouse and analyze the various policy scenarios. The program languages used in building the websites were ASP, HTML, and Java Script. Visual Basic, and Olectra 6.0 Chart Control were used in developing the web components. To date, the Ministry of Health and Welfare is only experimenting with a decentralized model. However, as we presented in the study, a centralized model is less costly and is easier to implement at the community level considering a lack of system skilled workforce. Accordingly, a pilot data warehouse should be constructed using a centralized model at one province as a demonstration basis in the future.

DW 개요.PDF

DW 개요.PDF Data Warehouse Hammersoftkorea BI Group / DW / 1960 1970 1980 1990 2000 Automating Informating Source : Kelly, The Data Warehousing : The Route to Mass Customization, 1996. -,, Data .,.., /. ...,.,,,.