Medical Card System Data Warehouse

Mentor: Mr. Philip Hickey

Data Sciences

Project

 

S.No	Title	P.No
1	Introduction (What project is about)	3-4
2	System Overview, Data architecture and storage,	5-6
3	ER-Diagram, OLTP-Architecture,Master / Slave Medical Card System	6-10
4	MCS OLTP, MCS data, storage,MCS Business Module or Services process:	11-14
5	Request Flow, Data repository, MySQL	15-19
6	Data warehousing	19-27
7	Service improvement	28
8	Statistical Analysis	29-30
9	Summary, conclusion, Learned	31-35
10	Appendix	36

 

1. Introduction:

A Medical Card is a plastic card, about the span of a Visa, issued by the HSE. Individuals who hold a Medical Card are qualified for a scope of Health Services for nothing out of pocket. Sometime recently, 2009. Therapeutic card framework was a decentralized and comprising littler wellbeing sheets. Since, they are isolated to each other’s, It was making taking after oddities.

1. Duplicate Medical cards.
2. GHOST Medical cards
3. System deficiency
4. Increasing Complexity
5. Budget deficit in health budget
6. Lack of staffing and System expertise

To determine these issues the HSE, choose to incorporated Medical card framework. They gather master from every wellbeing board and accumulate in Dublin. On framework level, it was a major perplexity mass before centralization. The reason was every wellbeing board has their own medicinal card framework on a few innovations like prophet, SQL server and so forth. A Single Medical card framework was running on ORACLE, SQL, MYSQL and MUMS thus on rely on upon the decision of wellbeing board. There were loads of reports and Hauge paper works was included and it was night horse to handle it. To, determine this issue The HSE made a system to gap information into three databases, for example, ORACLE, MongoDB.

Each of the database has there on noteworthiness. The HSE additionally choose to making and dealing with their own information distribution center. There was an alternative accessible for cloud benefits but since of the way of information. The HSE fabricate their own information product house. The HSE utilized Mongo DB since it is a record situated database and what it does, it is intended for even versatility. Because this, if your database develops, you can basically include more equipment or more assets from the cloud.

2.1 MCS System Overview:

The Medical card system data were divided into following:

1. The data from new medical card forms was divided in 3 parts. First data was Manual filled application which was later typed in the system.

2. The data come from Legacy system and loaded into new system. That sort of data required big ETL.

3. Third was supporting documents.

4. The size of data was 2 tetra bytes per month

2.2 MCS Data Storage:

The data was storing in following technologies:

1. MongoDB
2. Neo4j
3. ORACLE
4. MYSQL
5. hybrid system (HyPer)

MongoDB	MySQL	Oracle	Neo4j
Document-oriented	Cross-platform support	Reliable database	OLTP
Supports JSON format.	Stored procedures	Advanced Index Compression”	JSON and XLS format
No DBA	SQL/PSM	Approximate Count Distinct”	Indexes by using Apache Lucence
Flexible replication for shading across nodes.	Triggers.	“Attribute Clustering”	supports full ACID
Multi-version concurrency	Cursors	Automatic Big Table Caching”	UI for CQL
consistency in complex transactions	Updatable views	FDA Support for CDBs”	Native GPE(Graph Processing Engine).
Dynamic queries and powerful aggregates.	Online DDL	Full Database Caching”	(CRUD) operation
Index support and ap/reduce functions	Information schema	In-Memory Aggregation”	Access by Java, Spring, Scala

3.1 MCS Database Architecture

3.2 MCS OLTP Architecture

Master / Slave Medical Card System

1. One index per city

2. Growth by shredding into 2 and 3

3. Master build index every 10 minutes

4. Use indexes and pearl code for to generate XML

5. Build versioning and rollback segment

6. Slave pull the indexes via resync and reload

7. Use pre-forking config

8. Hardware was dual proc, dual core AMD opterons with 32 GB RAM

3.3 MCS OLTP:

Medical card OLTP systems are used for order new application, Medical card transactions, customer relationship management (CRM) etc. Such systems have many users who conduct short transactions. Database queries are usually simple, require sub-second response times and return relatively few records. An important attribute of medical card OLTP system is its ability to maintain concurrency. To avoid single points of failure, MCS OLTP systems is decentralized.

MCS data-model-self-governing and planned to professionally handle accidental, ad hoc queries in an analytical system environment. We are using Mango DB, Neo4j, Oracle, MySQL along with legacy System like MUMS. The Size of the data per week is 1 tetra byte. We have Online replication. HSE have hot backup and full disaster recovery model implemented. HSE have one cold server run in Waterford region which they used as cold backup. HSE policy to store data in multi places so in case of disaster recovery will be easy.

3.4 MCS Data:

It consists on the following:

1. Client personnel and Medical History such as Client name, address, ppsno and GP information

2. GP registered within certain county

3.   Hospital information such as OPD, ANE etc.

4. CWO in each area

5. Pharmacies and registered Pharmacies

6. HSE Local offices

3.5 MCS Data storage:

MCS data store on different devices and system as following:

1. Quantum StorNext scale-out file system.

2. NetBackup product. NetBackup is integrated with copy data management, Veritas Resiliency Platform and Veritas Information Map.

3. MySQL

4. MangoDB

5. Neo4j

6. Oracle

4.1 MCS Business Module or Services process:

FOR NEW APPLICATION

FOR RENEWAL APPLICATION

4.2 MCS Request Flow

4.3 MCS Flow

4.4 MCS Data Repositories

4.5 MCS My SQL

5.1 MCS Data Warehousing:

1. Relationships between DSS/BI, database, data management
2. DSS/BI: transforming data into info to support decision making
3. MCS (Medical Card System) operational data and DSS/BI data differ
4. What a data MCS (Medical Card System) warehouse is, how data for it are prepared, and how it is implemented
5. Multidimensional database
6. Database technology for BI: OLAP, OLTP
7. Examples of applications in healthcare

5.2 MCS BI: Extraction of Knowledge from Data

5.3 MCS DSS/BI Architecture: Learning and Predicting

5.4 MCS DSS/BI

• DSS/BI are technologies designed to extract information from data and to use such information as a basis for decision making
• Decision support system (DSS)
  • Arrangement of computerized tools used to assist managerial decision making within business
  • Usually requires extensive data “massaging” to produce information
  • Used at all levels within organization
  • Often tailored to focus on specific business areas
  • Provides ad hoc query tools to retrieve data and to display data in different formats

5.5 MCS DSS/BI Components

• Data store component
  • Basically, a DSS database
• Data extraction and data filtering component
  • Used to extract and validate data taken from operational database and external data sources
• End-user query tool
  • Used to create queries that access database
• End-user presentation tool
  • Used to organize and present data

5.6 MCS Main Components of A DSS/BI

5.7 MCS DSS/BI: Needs a different type of database

• A specialized DBMS tailored to provide fast answers to complex queries.
• Database schema
  • Must support complex data representations
  • Must contain aggregated and summarized data
  • Queries must be able to extract multidimensional time slices
• Database size: DBMS must support very large databases (VLDBs), Wal-Mart data warehouses is measured in petabyte (1,000 terabyte)
• Technology: Data warehouse and OLAP
  • emphasize speed, security, flexibility, reduce redundancy and abnormalities.

5.8 MCS Operational vs DSS Data

6.1 MCS Data Warehouse

• The Data Warehouse is an integrated, subject-oriented, time-variant, non-volatile database that provides support for decision making.
• Usually a read-only database optimized for data analysis and query processing
• centralized, consolidated database
• periodically updated, never removed
• Requires time, money, and considerable managerial effort to create

6.2 MCS OLAP (Online Analytical Processing)

• Advanced data analysis environment that supports decision making, business modeling, and operations research
• “engine” or platform for DSS or Data Warehouse
• OLAP systems share four main characteristics:
  • Use multidimensional data analysis techniques
  • Provide advanced database support
  • Provide easy-to-use end-user interfaces
  • Support client/server architecture

6.3 MCS OLAP vs OLTP:

• Online Transactional Processing (OLTP)
  • emphasize speed, security, flexibility, reduce redundancy and abnormalities.
• Online Analytical Processing (OLAP)
  • multi-dimensional data analysis
  • advanced database support
  • easy-to-use user interface
  • support client/server architecture

6.4 MCS Multidimensional Data Analysis

• Goal: analyze data from different dimensions and different levels of aggregation

6.7 MCS Multidimensional Data Analysis Techniques

• Data are processed and viewed as part of a multidimensional structure
• Particularly attractive to business decision makers
• Augmented by following functions:
  • Advanced data presentation functions
  • Advanced data aggregation, consolidation and classification functions
  • Advanced computational functions
  • Advanced data modeling functions

6.8 MCS integration OLAP with Spreadsheet

6.9 MCS easy-to-Use End-User Interface

• Many of interface features are “borrowed” from previous generations of data analysis tools that are already familiar to end users
  • Makes OLAP easily accepted and readily used

6.10 MCS Client/Server Architecture

• Provides framework within which new systems can be designed, developed, and implemented
  • Enables OLAP system to be divided into several components that define its architecture
  • OLAP is designed to meet ease-of-use as well as system flexibility requirements

6.11 MCS OLAP Architecture

• Designed to use both operational and data warehouse data
• Defined as an “advanced data analysis environment that supports decision making, business modeling, and an operation’s research activities”
• In most implementations, data warehouse and OLAP are interrelated and complementary environments

6.12 MCS Facts

• Numeric measurements (values) that represent specific business aspect or activity
  • Normally stored in fact table that is center of star schema
• Fact table contains facts that are linked through their dimensions
• Metrics are facts computed or derived at run time

6.13 MCS Dimensions: simple star schema

6.14 MCS Attribute Hierarchies in multidimensional analysis

6.15 MCS Star Schema Representation

6.17 MCS Multi-dimensional database

6.18 MCS Star Schema

6.19 Snowflake schema

7.1 Service improvement

• MCS Outcome Database
• Center for Medical Service
• More than fifty community health centers contributed to this database.
• 547,719 transactions
• 13 Outcome indicators, 72,541 episodes of treatment, 17,205 patients, 108 therapists, 48 institution

8.1 Statistical Analysis

MCS Difference in Clinical Services Improvement: Young and Old patients

8.2 Compare Cancer Incidence of Dublin County to Carlow County from 1996-2000

9.1 Conclusion:

A Medical Card is a plastic card, about the size of a credit card, issued by the HSE. People who hold a Medical Card are entitled to a range of Health Services free of charge. In this project, we have seen a change of centralized medical card system with the help of NOSQL and RDBMS changed the service outcome. HSE have Mongo DB which make it suitable for this kind of project is it is Schema-less.  A document can have any number of key/value pairs. Instead of using a schema, documents of the same time (for example, documents representing blog posts) all have a similar set of key/value pairs. Second, a database which HSE have here is Neo4j graph database. The reason why they have used Neo4j because it provides OLTP and supports Jason and XLS format. Another reason to use Neo4j is it is Create, Read, Update and Delete (CRUD) operations working on a graph data model.

MCS data-model-self-governing and planned to professionally handle accidental, ad hoc queries in an analytical system environment. We are using Mango DB, Neo4j, Oracle, MySQL along with legacy System like MUMS. The Size of the data per week is 1 tetra byte. We have Online replication. HSE have hot backup and full disaster recovery model implemented. HSE have one cold server run in Waterford region which they used as cold backup. HSE policy to store data in multi places so in case of disaster recovery will be easy.

The MCS Data Warehouse is an integrated, subject-oriented, time-variant, non-volatile database that provides support for decision making. Usually a read-only database optimized for data analysis and query processing. centralized, consolidated database, periodically updated, never removed. It is Requires time, money, and considerable managerial effort to create. Relationships between DSS/BI was studied in detail along with, database, data management.  We have explored the DSS/BI: transforming data into info to support decision making. The MCS (Medical Card System) operational data and DSS/BI data differ from which we have used to test the system. We have explored what data MCS (Medical Card System) warehouse is, how data for it are prepared, and how it is implemented Multidimensional database. The Database technology for BI: OLAP, OLTP. Examples of applications in healthcare.

During this project, we were Combining Data Warehouse (OLAP) and GIS.OLAP: handles large data, fast retrieval multidimensional, multilevel aggregation, analyses/data mining on huge complex databases. IS: visualization and spatial analyses. Visualization and Analysis: Charts and Maps + Statistical Analysis.

The outcome we have from the MCS Database is we have center for Medical Service More than fifty community health centers contributed to this database. The transaction span to 547,719 transactions. WE have 13 Outcome indicators, 72,541 episodes of treatment, 17,205 patients, 108 therapists, 48 institutions.

9.2 Learned:

During completing this project, I have learned following:

1. NOSQL MongoDB, Neo4j Installation and deployment

2. OLTP in detail

3. I have studied Data Warehouse comprehensively

4. I have Learned about Data Analysis such as Statistical Analysis

5. NoSQL and SQL have both their significance depend on what you want to do.

6. It was a great learning curve and extend my horizon about technology

7. There is a lot to learn the especially field in IT things a rapidly changing.

8. RDBMS are good to work but they will not answer for all your IT needs.

9. MongoDB and Neo4j are emerging technologies and best fit for the system like the medical card.

10. During, my lab I have come across the term like horizontal scalability It is the capability of a system, network, or process to cover a rising sum of work, or it is potential to be magnified in rank to accommodate that increase.

11. For object lesson, it can refer to the capability of a system to increase its total output under an increased load when resources (typically hardware) are added.

12. Another, an inserting term I have discovered is a document database. Although it was covered in a lecture but not so clear. Hereafter working and installing it make quite a sense.

9.3 Problems/Issues

• For MongoDB, it is hard to work on command prompt

• Download inteleJ IDEA and configured and that will make the job easier.

Available online: https://www.jetbrains.com/idea/download/#section=windows

• I have tried to install Oracle NOSQL and there were no windows version

• All process required extra expertise in Linux and Unix and one point I gave up

• Installing/configuring process in case of MongoDB and Neo4j is very simple and straight forward.

• Neo4j is quite straight forward to install and work.

• Once installed the Neo4j you need to look around how to run Neo4j. it is almost hard to run Neo4j on http://127.0.0.1 instead if you run it on http://localhost:7474/browser/ on your browser window.

• Command structure not so great, as long your system gets complex, the query process of Neo4j is getting complex as well.

• IT required previous Knowledge of Jason.

• If there is a problem in query design, Neo4j prompt for the mistake, but if you have query structure problem or logical error there is no error message.

• Like all technology, you need to memories a lot. There is no toll-like workbench for help.

• If you have previously worked with RDBMS like oracle or MySQL it will take a while to get a hand on Neo4j.

10.1 Appendix:

1. http://www2.seas.gwu.edu/~bell/csci243/lectures/data_warehousing.pdf
2. http://www.hse.ie/eng/services/list/1/schemes/mc/
3. http://www.hse.ie/eng/
4. http://www.businessdictionary.com/definition/data-analysis.html
5. https://www.linkedin.com/pulse/20140728161327-51272350-what-is-collection-in-nosql-databases-specifically-in-mongodb
6. https://Neo4j .com/why-graph-databases/
7. http://www.w3resource.com/mongodb/nosql.php
8. http://www.tutorialspoint.com/Neo4j /Neo4j _features_advantages.htm
9. http://www.itbusinessedge.com/slideshows/top-five-nosql-databases-and-when-to-use-them.html
10. https://www.youtube.com/watch?v=1uFY60CESlM&list=PL6gx4Cwl9DGDQ5DrbIl20Zu9hx1IjeVhO
11. https://www.youtube.com/watch?v=eE6G5BX8GG0&list=PL1zjgLKnHOtga1W4cdyjxRbliw4-n84hR
12. http://dist.Neo4j .org/Neo4j -manual-1.4.M03.pdf
13. https://www.youtube.com/watch?v=eE6G5BX8GG0&list=PL1zjgLKnHOtga1W4cdyjxRbliw4-n84hR