GMKTsRIT (SAP BusinessObjects)

Analysis system of preferential provision of medicines in regions for GMKTSRIT LLC

The BI Partner company implemented the project on creation of an analytical information system on the basis of the uniform data warehouse for GMKTSRIT LLC. A new system allows to increase the level of service information customer service of the company, the organizations participating in the program of the collateral preferential provision of medicines (CPPM) provides with up-to-date and reliable information about the statement of recipes, a holiday of medicines, a program runtime in general as suppliers of pharmaceuticals and TFCMI.

Prerequisites of the Project

City Medical Computer Center of Distribution Information Technologies LLC is the center for collection of information in regions within the all-Russian system of provision of medicines of separate categories of citizens in the system of compulsory health insurance. The company works more than 10 years in the field of information support of health facilities of Moscow, mainly, in the field of accounting of preferential provision of medicines. GMKTSRIT LLC is the leading contractor of Healthcare Committee of Moscow on information support of the DLO system in Moscow.

The collected information is of interest to the compulsory health insurances territorial funds, territorial authorities of management of health care, treatment and prevention facilities, suppliers of medicines and the pharmaceutical organizations. On its basis problems are solved:

Control flowing statements and holidays of recipes of DLO;

Issues of references of the set sample;

Developments of solutions on providing regions with medicines on the basis of the exempts given by quantity, incidences, negotiability, etc.;

Carrying out contrastive analysis of system operation of DLO on regions;

The analysis of dynamics of system operation of DLO on regions in time.

GMKTSRIT LLC reports results of the work to the interested organizations in a report type. In general, the operating information system had an opportunity to provide the information required, however in it there was a number of essential shortcomings:

Data for the different periods and on different regions are presented in separate files that complicates carrying out contrastive analysis;

There are several formats of data view;

The big array of information is saved up: some reports are executed very slowly, a part of reports cannot be implemented;

Creation of the new report requires writing of the new program, and change of appearance of the report requires change of the text of the program.

For overcoming these shortcomings by the management of GMKTSRIT LLC the tasks of increase in efficiency of information support of participants of the DLO system including consolidation of data in uniform storage and simplification of process of preparation and obtaining reports were set. For project implementation the BI Partner company was invited.

Solution architecture

The analysis system of DLO in regions is based on standard architecture of information and analytical systems, using technology of data warehouses and business intelligence. As the platform for the data warehouse Oracle 10g Enterprise Edition DBMS, and for implementation of a subsystem of the reporting – Business Objects software was selected. The general system architecture is provided in Fig. 1.

The main source of data for a system are the files with data in the DBF format coming from partners monthly, but without accurately certain regulations. Files undergo preprocessing by the staff of GMKTSRIT LLC then, the system administrator loads data into storage using the ETL procedures implemented using Oracle Warehouse Builder.

In loading process of the data warehouse there is a logical control of integrity of information. Given, not undergone control, are located in the error log which is analyzed by the system administrator.

Data warehouse

The data warehouse provides consolidation and approval of indicators in different – cuts and completely covers data domain of maintaining the register of exempts, statements and holidays of recipes, rejection of recipes which is performed by GMKTSRIT LLC and which is carried out by TFCMI.

Data are consolidated on the following analytical cuts: time, exempts, categories of a lgotnost, regions, pharmaceutical enterprises, authorized pharmaceutical organizations, suppliers of medicines, pharmaceutical points, treatment and prevention facilities, doctors, diagnoses, medicines, statement of recipes, holiday of recipes, prices of medicines.

Methods of storage and structure of information in the data warehouse provide fast accomplishment of SQL queries for reporting and carrying out the analysis. The data warehouse assumes long-term information storage that allows to keep track of dynamics of indicators of activity of the enterprise, and provides correct reflection of change of information. It is especially relevant at change of the regulating documents regulating system operation of DLO that often leads to change of reference books.

Analytical subsystem

For work with information accumulated in the data warehouse Business Objects software, allowing within one application is used:

create database requests in terms of data domain;

integrate data from several sources;

analyze data on different hierarchical levels and analysts;

create professionally issued screen and printing reports. Within the executed project the basic reports on control and job analysis of the DLO system covering everything standard report forms were created. It is possible to carry to standard forms:

Registers

Registers provide summary information for the reporting period on the written-out recipes and the allotted medicines and also the reference information about the citizens having the right to preferential medical care, the doctors having the right to the statement of preferential recipes, and other reference information.

Financial statements

Financial statements include the bills which are made out by the supplier of medicines (further a LAN) in TFCMI for cost recovery on the released drugs.

Reconciliation statements

Reconciliation statements represent summary and itemized statements in which reasons for refusal of TFCMI of compensation of the separate amounts on the released recipes are explained. Can be examples of such reasons (in total more than 30):

the recipe is written out on the person who is absent in the list of exempts;

according to the recipe the medicine which is not entering the list of admissible drugs is released;

according to the recipe the medicine entering the list, but at the prices exceeding the maximum allowed cost is released; etc.

Analytical reports

Analytical reports contain representation of different indicators (the amount of the released drugs, the number of the released recipes, average cost of the patient, average quantity which addressed, etc.) by different analytical cuts - categories of exempts, diagnoses, types of medicines, etc. for the reporting period and on accruing a result.

Summary and statistic reports

Summary and statistic reports are intended for representation of an overall picture of functioning of the DLO system in regions.

Appraisal certificates

This report type allows to identify doctors and patients who are selected from the set planned targets. The amount and the number of the recipes which are written out by one doctor to one patient for the reporting period are among these indicators; amount and number of the recipes redeemed patient for the reporting period; other types of examination.

In addition to procedural reports of analytics of GMKTSRIT LLC had an opportunity to independently create requests to a system in familiar terms and to create any reports of presentation quality. The level of training of the user creating reports corresponds to level of training of the average user of the MS Excel.

Project Results

New c of an istem of the analysis of preferential provision of medicines in regions allows to increase the level of service information customer service of GMKTSRIT LLC company due to operational providing up-to-date and reliable information about the statement of recipes, a holiday of medicines, system operation of DLO in general.

"The uniform data warehouse opens for participants of the DLO program new opportunities of information service. Our company moved to qualitatively other level of automation of process of data collection and preparation of reports that allowed to increase quality and speed of providing reports and reports", - Leonid Lemko, the CEO of GMKTSRIT LLC noted. "A system provides us competitive advantage and for its implementation we invited the BI Partner company having unique experience of successful projects in large information storages and the systems of the business analysis".

In future GMKTSRIT LLC together with BI Partner company plans development of c of an istema of the analysis of DLO in the territory of Moscow.

The data warehouse in digits

Amount of data of storage 5 GB The maximum quantity of lines in the fact table 5 million. Increment in a month 400 thousand lines Load time of a portion of data 30 min. Quantity of the actual indicators 20 Quantity analyst of accounting 150 Number of procedural reports 20