One of the key roles of an insurance company in healthcare is to continuously update the list of reimbursable medicines to ensure the best possible treatment for patients. However, this process must also consider the needs of all stakeholders.

• Patients require access to the most appropriate medications for their unique conditions, which may not be limited to their diagnosis but could also depend on factors such as age or co-existing health conditions. For this reason, certain medications are approved for prescription only within specific specialties and not universally by all medical practitioners.
• Pharmaceutical manufacturers or marketing authorization holders must supply medicines under sustainable terms and conditions.
• At the same time, the health insurance institution has a duty to manage its insureds’ funds with the highest level of responsibility.

The need is to establish an automated setup (just Setup from here on) that enables monitoring and analysis of medication use practices across various, pre-defined patient cases and reimbursement parameters.

• Definitions used

  Contract

  set of a set of monitoring parameters defined for a specific drug during a specified contract period. A Contract includes a Head, which denotes the overarching monitoring rules applicable to the drug, and one or more Rows, which represent product-specific drugs connected to the contract, each with its own unique monitoring parameters.

  Contract Head

  the central part of a contract, encompassing the main monitoring parameters applicable to the specific drug. It governs the overall monitoring framework, such as the contract period, total cost, and report frequency, under which the Rows operate.

  Contract Row

  within a contract refers to the product-specific monitoring parameters for drugs connected to the contract. Each Row includes unique parameters such as monitoring periods, the frequency of interim reports, and patient case-mix parameters, which are distinct from the overarching Head.

  Drug

  generic drug name referring to active ingredient that is responsible for the therapeutic effects. Corresponds to Head level of Contract.

  Hierarchical and Periodical Challenges

  the complexities in tracking and analyzing data that must align with both hierarchical structures (e.g., Head and Row parameters) and periodic time frames (e.g., contract periods, monitoring periods).

  Interim Reports

  periodic reports generated during a monitoring period to provide updates and insights into parameters such as drug usage, treatment episodes, and financial outcomes. These reports form the basis for cumulative analysis.

  Medication

  brand-specific medication referring to a marketed product containing a specific combination of active ingredient, dosage form, and manufacturer branding. Corresponds to Row level of Contract.

  Monitoring Parameters

  specific rules and criteria used to track and analyze prescription drugs or claims. These include aspects like patient case-mix parameters, treatment regimes, and financial impacts, which are configurable within the system.

  Setup

  the automated system or application that integrates Python, SAP HANA SQL, and Power BI to monitor, analyze, and manage medication use practices. This system includes functionalities for inserting, deleting, and reinserting contract data, performing SQL-based data manipulations, generating analyses, and visualizing results through Power BI. The “Setup” is designed to handle hierarchical and periodical data challenges, to ensure secure and efficient workflows aligned with client-specific requirements.

• 1. Business system analysis

  • 1.1. Analyzing and documenting client requirements and underlying processes

    Client requirements regarding the setup:

    Insert, delete and reinsert monitoring parameters:

    • Drug name (Generic name, active ingredient) (short: Drug)
    • Brand-specific medication name (Product name) (short: Medication)
    • Determine whether monitoring ought to be considered in connection with another drug name
    • Total monitoring period
    • Frequency of interim monitoring reports
    • Patient case-mix parameters – age, diagnosis (both included and excluded)
    • Treatment regime parameters – number of medications, treatment episode defined either by no of medications or time duration, no of treatment episodes, time between episodes, fist ever episode or recurrent episode in patient’s life (regardless of monitoring period)
    • Compensational parameters – including compensational amount, expected total cost etc

    Visual and automated monitoring of treatments based on inserted parameters:

    • an overview visualization of monitoring conditions, including progress over time and notifications for upcoming interim checks or condition expirations
    • an overview visualization of statistics about patients and used medications concordant with monitoring conditions and in comparison, of same drugs outside monitoring conditions
    • comparative analytics between different settings of monitoring parameters to both, continuation or discontinuation (equivalent to treatment effect) of treatment, and to financial impact on the healthcare expenditure budget
    • automated interim and final reports of comparative analytics

    Reusability

    The data on inserted parameters and result analytics must remain reusable for at least 5 years outside the aforementioned automated monitoring, for example, in institutional performance reports, including financial reporting.

    Requirements for the underlying data model (see Figure 1):

    One set of monitoring parameters or analytical rules (short: Contract from here on) is defined by one drug and one Contract period. One drug is defined by medicine containing certain active ingredient(s) but manufactured or marketed by one company. These parameters form the Head of Contract. Monitoring parameters of the Head includes (but are not limited to):

    • monitoring period,
    • frequency of interim reports,
    • expected total cost.

    One drug may have one-to-many Brand-specific medications but only some of these may be connected to Contract.

    Each medication connected to Contract has its unique monitoring parameters and form the Rows of Contract. So, each Contract has one Head but one-to-many Rows.

    Monitoring parameters of a Row include (but are not limited to):

    • monitoring period (which may not reach outside the monitoring period of the Head but may include many periods of interim reports of Rows),
    • the frequency of interim reports of Rows,
    • patient case-mix parameters,
    • treatment regime parameters,
    • compensational parameters.

    Treatment regime parameters include one or both levels:

    • Individualized parameters, where drug amounts and treatment episodes are monitored in individual patient’s level (considering Row-specific patient case-mix parameters) and aggregative statistics are calculated based on results. Parameters applicable to this level include:
      • first or recurrent treatment episode in one’s life (regardless of monitoring period)
      • duration, number and frequency of treatment episodes
      • total number of medications used within a specific period (of monitoring, episode or interim report period) or the counted number (package sequence number) in a new treatment episode. Etc.
    • Non-individualized parameters include total number of medications used during Row monitoring period (considering Row-specific patient case-mix parameters) and aggregative statistics are calculated based on results. Non-individualized parameters are considered after individuals. For example, if only 1^st treatment episode per one individual is considered individualized level, medications consumed after 1^st episode are still included into this level of monitoring.

    Monitoring analyses must consider both hierarchical and periodical challenges. The following analysis and reporting modules are to be created, following a cumulative approach where each module builds on data from the previous one by incorporating parameters relevant to its specific purpose:

    • Individualized-level medication-specific analyses by interim Row report periods[1]
    • Non-individualized medication-specific analyses by interim Row report period
    • Drug-specific analysis by interim Head report period
    • Row-specific final report (medication-specific) by the end of Row monitoring period
    • Drug-specific final report by the end of Head monitoring period

    ---

    [1] If the monitoring period that determines Row parameters is longer than the reporting period, the interim report serves as informational, and the monitoring results need to be carried over from one report to the next.

    

    Figure 1 – Hierarchical organization (the Head–Rows structure) and the monitoring parameters associated with each level.

    ---

    .

    ---

    Figure 1 – Hierarchical organization (the Head–Rows structure) and the monitoring parameters associated with each level.

  • 1.2. Knowledge of healthcare settings – how data is produced and transformed between all counterparts

    Basically the production medical treatment data is as follows. Drugs are prescribed by doctors either by prescription or as service in hospital settings.

    Prescription drugs

    • Almost all prescriptions are drug-specific[1] electronic documents, initiated by a doctor and containing data about the patient (master data for the prescription owner – name, personal ID number), diagnosis (ICD-10 coding), data about the drug (in most cases, the name of the active ingredient(s), concentration(s), form of medication, and the quantity prescribed), and data about the dosing regimen (how many medications, how often, and for how long).
    • If the drug and the patient’s medical condition align with the health insurance policy, the patient’s health insurance coverage is verified through the X-road service.
      Electronic prescriptions are visible in any pharmacy and in the health insurance fund’s database under the status of ‘prescribed’[2].
    • At the time of prescription dispensation (meaning the medication is purchased), the pharmacist identifies buyer (does not necessarily have to be the prescription owner), rechecks the prescription owner’s health insurance coverage, and the system verifies whether the reimbursement parameters comply with the insurance policy. The prescription status changes to ‘dispensed’, and additional data is recorded (product-specific drug name, package code, the quantity of medication sold, details about pharmacy and the pharmacist, information about person who purchased the medication, the price paid by buyer and the amount to be reimbursed by health insurance).
    • If a paper prescription is issued, it is digitalized by the pharmacist at the time the medication is purchased from the pharmacy.
    • Health insurance settles pharmacy invoices at a later date. The prescription remains in a ‘dispensed’ status in the database but gains accounting settlement date. The latter is the date used as the basis for this setup.

    ———————————————————-

    [1] If there is a clinically relevant need, a brand-specific prescription may be issued on rare occasions. The final decision on which brand medication is purchased by the patient is made at the pharmacy.

    [2] Upon dispensing the prescription, the doctor who issued it can annul it (this status is reflected in the health insurance database). Additionally, the patient can contact a specific pharmacy to reserve the prescription at that pharmacy (this status is not updated in the health insurance database).

    Medical treatments as services in hospital setting

    • The health insurance fund organizes healthcare services, including services related to various drugs administered under hospital conditions. When a patient receives such a service in a hospital, the hospital submits an electronic invoice to the insurance fund. This invoice contains the following information: master data for the hospital and the attending physician, master data for the patient, the patient’s primary and accompanying diagnoses related to the current hospitalization (coded in ICD-10), the dates of the entire hospital stay and each procedure; the code, quantity, and price of the healthcare service provided during the hospital stay.
    • Upon the patient’s discharge from the hospital, the institution prepares an invoice and sends it electronically to the insurance fund. (The time between the service being provided and the invoice being sent can take several weeks.) From that moment, the invoice data becomes visible in the insurance database.
    • Similarly to prescriptions, the health insurance fund settles the hospital’s invoice at a later date. At this point, the invoice is updated with an accounting settlement date and the payment amount. This amount may differ from the price calculated based purely on the price list, as it is determined in accordance with the terms of the agreement.

    The process of setting reimbursement policy and price involves a thorough, protocol-driven effort by numerous health insurance specialists in close collaboration with the scientific committee, professional associations, and pharmaceutical manufacturers’ representatives.

    In the context of current Setup, the monitoring parameters need to be made available in the database where claims are processed, and prescriptions are validated.

  • 1.3. Knowledge of business-wide applications

    • Prescription and claim data are stored in an SAP-BW relational database, which can be accessed through the SAP HANA database management application and by using ODBC-based or pure-Python client drivers
    • Analytical programming tasks can be performed using R and Python.
    • Microsoft Power BI is utilized for data visualization and is connected to the SAP HANA database using ODBC-based driver via the Power BI Gateway, which allows secure and efficient access to the database for data visualization and reporting purposes.

• 2. Data analysis structure of application programming of Setup

  The Setup application is built using a combination of Python, SAP HANA SQL, and MS Power BI’s DAX coding (see Figure 2).

  The Python programming language was chosen primarily for its easy driver connectivity to the SAP BW database and the availability of a GUI toolkit. Python is used to create and manage database tables, which include monitoring parameters (e.g., Contract data). Connectivity to the SAP BW database enables not only the direct insertion of Contract data into database tables but also the provision of predetermined lists, such as drug names or service codes, as master data. By making these lists available in the GUI, data entry becomes faster and more convenient for the user.

  Data manipulations related to identifying Contract-relevant prescriptions and claims are performed within the SAP HANA database using HANA SQL. This approach was chosen because the data itself (prescriptions and claims) resides within the database. Performing all manipulations directly inside the database, saving the results into separate tables (Results), and visualizing aggregated data ensures data protection while meeting the client’s need to reuse analysis-ready data. Client analysts also access and utilize these data tables directly from the same database.

  The use of SAP HANA scheduled procedures for deleting and inserting data was chosen over update primarily because:

  1. Dynamic business rules: Contract periods need to be flexible and changeable. However, the results of analysis based on specific Contract (monitoring parameters) must remain dependent on the analysis period. This requires re-evaluating all corresponding analysis data (prescriptions or claims) and recalculating results whenever Contract periods or parameters are updated.
  2. Client requirements for Contract flexibility: The client requires the ability to delete and reinsert Contract data to analyze different sets of monitoring parameters. Additionally, part of the analysis involves comparing medication regimes related to Contract with the same medication outside the Contract. Therefore, every calculation must account for these new conditions to ensure accurate results.

  Data visualization through Power BI aligns with the client’s requirements, offering up-to-date reports via automated, scheduled refreshes, interactivity, custom-designed notification alerts, and options for data export.

Figure 2 – data analysis structure of Setup.

A combination of Python, SAP HANA SQL, and MS Power BI’s DAX coding were used.