Medical Card System Data Warehouse    Muhammad Nadeemà      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    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 others, It was making taking after oddities.    Duplicate Medical cards.  GHOST Medical cards  System deficiency  Increasing Complexity  Budget deficit in health budget  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:    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.    The data come from Legacy system and loaded into new system. That sort of data required big ETL.    Third was supporting documents.    The size of data was 2 tetra bytes per month    2.2 MCS Data Storage:  The data was storing in following technologies:    MongoDB  Neo4j  ORACLE  MYSQL  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    One index per city    Growth by shredding into 2 and 3    Master build index every 10 minutes    Use indexes and pearl code for to generate XML    Build versioning and rollback segment    Slave pull the indexes via resync and reload    Use pre-forking config    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:    Client personnel and Medical History such as Client name, address, ppsno and GP information    GP registered within certain county    Hospital information such as OPD, ANE etc.    CWO in each area    Pharmacies and registered Pharmacies    HSE Local offices    3.5 MCS Data storage:  MCS data store on different devices and system as following:    Quantum StorNext scale-out file system.    NetBackup product. NetBackup is integrated with copy data management, Veritas Resiliency Platform and Veritas Information Map.    MySQL    MangoDB    Neo4j    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:     Relationships between DSS/BI, database, data management  DSS/BI: transforming data into info to support decision making  MCS (Medical Card System) operational data and DSS/BI data differ  What a data MCS (Medical Card System) warehouse is, how data for it are prepared, and how it is implemented  Multidimensional database  Database technology for BI: OLAP, OLTP  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 operations 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:    NOSQL MongoDB, Neo4j Installation and deployment    OLTP in detail    I have studied Data Warehouse comprehensively    I have Learned about Data Analysis such as Statistical Analysis    NoSQL and SQL have both their significance depend on what you want to do.    It was a great learning curve and extend my horizon about technology    There is a lot to learn the especially field in IT things a rapidly changing.    RDBMS are good to work but they will not answer for all your IT needs.    MongoDB and Neo4j are emerging technologies and best fit for the system like the medical card.    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.    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.    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:    http://www2.seas.gwu.edu/~bell/csci243/lectures/data_warehousing.pdf  http://www.hse.ie/eng/services/list/1/schemes/mc/  http://www.hse.ie/eng/  http://www.businessdictionary.com/definition/data-analysis.html  https://www.linkedin.com/pulse/20140728161327-51272350-what-is-collection-in-nosql-databases-specifically-in-mongodb  https://Neo4j .com/why-graph-databases/  http://www.w3resource.com/mongodb/nosql.php  http://www.tutorialspoint.com/Neo4j /Neo4j _features_advantages.htm  http://www.itbusinessedge.com/slideshows/top-five-nosql-databases-and-when-to-use-them.html  https://www.youtube.com/watch?v=1uFY60CESlMlist=PL6gx4Cwl9DGDQ5DrbIl20Zu9hx1IjeVhO  https://www.youtube.com/watch?v=eE6G5BX8GG0list=PL1zjgLKnHOtga1W4cdyjxRbliw4-n84hR  http://dist.Neo4j .org/Neo4j -manual-1.4.M03.pdf  https://www.youtube.com/watch?v=eE6G5BX8GG0list=PL1zjgLKnHOtga1W4cdyjxRbliw4-n84hR