Difference between revisions of "MDCCD Data Warehouse"

From wiki
Jump to navigation Jump to search
 
(24 intermediate revisions by 2 users not shown)
Line 1: Line 1:
−
The MDCCD Data Warehouse aasdasdasdad
+
[[fr:Entrepôt de données d'DCMMMC]]
 +
{{Delete|reason=Page has been moved to internal Health Canada wiki. Internal database structure should not be public.}}
 +
== Overview ==
 +
The Medical Device and Clinical Compliance (MDCCD) Data Warehouse is an Oracle 19c based data warehouse  maintained by Shared Services Canada (SSC) and Health Canada's (HC) Digital Transformation Branch (DTB).
 +
 
 +
The MDCCD Data Warehouse uses a concept called "Fundamental Datasets". Fundamental Datasets are meant to be a standardized dataset that is optimized for business intelligence and data science.
 +
== Problems to Address ==
 +
 
 +
* There is no single source of truth for MDCCD’s data.
 +
* Program data is stored a various repositories.
 +
** Ex: IRS, MDS, eCES, RADAR, ARISg, eSAP
 +
 
 +
* Repositories are often poorly documented.
 +
 
 +
* Data is often incomplete, inaccurate or missing.
 +
* IT systems are constantly changing.
 +
** Requires tools and business intelligence to be rebuilt.
 +
 
 +
* Data Analysts are not always allowed to access production databases.
 +
 
 +
== Benefits of the MDCCD Warehouse ==
 +
 
 +
* Better data quality: A data warehouse centralizes data from a variety of data sources, such as transactional systems, operational databases, and flat files. It then cleanses the operational data, eliminates duplicates, and standardizes it to create a single source of the truth.
 +
* Faster insights: Data warehouses enable data integration, allowing program users to leverage all of the program’s data into each business decision. Data warehouse data makes it possible to report on themes, trends, aggregations, and other relationships among data collected.
 +
* Smarter decision-making:  A data warehouse supports large-scale BI functions such as data mining (finding unseen patterns and relationships in data), artificial intelligence, and machine learning—tools data professionals and program leaders can use to get hard evidence for making smarter decisions in virtually every area of the organization, from compliance verification to financial management and inspection planning.
 +
* Time savings: By using standardized datasets, business intelligence and automation tools can escape the cycle of constant rebuilding whenever IT systems change.
 +
* Documented data: Having all the program data in one location allows for the data to be properly documented with data dictionaries and entity-relationship diagrams resulting in consistent interpretations and reducing misunderstandings. Well documented data also allows for faster product development and training of data analysts.
 +
 
 +
== Scope, Phases & Scalability ==
 +
'''Scope'''
 +
 
 +
* IT Systems that do not have a preexisting reporting solution.
 +
* Functions where MDCCD is the owner of data.
 +
 
 +
'''Phase 1'''
 +
 
 +
# Provisioning of data warehouse. – Completed
 +
# Creation of standardized datasets for Medical Device inspections. - Completed
 +
# Creation of ETL from MDID (Medical Device Inspection Database) to MDCCD Data Warehouse. – In Progress
 +
 
 +
'''Phase 2'''
 +
 
 +
# AHR Registrations & Inspections
 +
# CTO Registrations & Inspections
 +
 
 +
'''Phase 3'''
 +
 
 +
# Medical Device Compliance Verification, Recalls & Establishment Licencing
 +
 
 +
'''Future Phases'''
 +
 
 +
* Data utilized by MDCCD, but where MDCCD is not the data owner (Ex: ARISg, eSAP)
 +
* Data from new IT systems (Ex: CIELS)
 +
 
 +
'''Potential Scalability'''
 +
 
 +
* Incorporate data currently captured by another reporting system (Ex. RADAR Reporting Database).
 +
* Incorporate data utilized by other program partners (Ex. Medical Device Licencing, Drug Product Dictionary).
 +
 
 +
== Systems ==
 +
This section outlines the IT systems that have their data included in the Data Warehouse.
 +
 
 +
=== '''Medical Device Compliance Program''' ===
 +
 
 +
===== ''Medical Device Inspection Database (MDID)'' =====
 +
 
 +
* This system holds Medical Device Inspection records.
 +
 
 +
== Schemas ==
 +
The MDCCD Data Warehouse contains two database schemas:
 +
 
 +
* MDCCD_DM_OWNER
 +
** Holds all tables and views used for reporting & analysis.
 +
* MDCCD_STG_OWNER
 +
** Holds all tables used for staging as part of the ETL process to populate tables held in the MDCCD_DM_OWNER schema.
 +
 
 +
== Tables ==
 +
 
 +
=== '''Table Naming Convention''' ===
 +
Tables are named using a 3 or 4 code convention. 
 +
 
 +
* PROGRAM
 +
** This two letter code indicates the MDCCD program that the table applies to.
 +
*** MD - Medical Device
 +
*** AH - Assisted Human Reproduction
 +
*** BD - Blood
 +
*** CO - Cells, Tissues, Organs
 +
*** CT - Clinical Trials
 +
* FUNCTION
 +
** This two to three letter code indicates the functional area of the program.
 +
*** INS - Inspection
 +
*** EST - Establishment Licence
 +
*** COM - Company
 +
*** INC - Incident
 +
*** REC - Recall
 +
* TABLE GROUP
 +
** This code indicates if the table is part of a larger grouping of tables that hold common information.
 +
** CASE - Case Information
 +
** IRC - Inspection Report Card Information
 +
* TABLE
 +
** This code indicates the name of the table.
 +
 
 +
The convention follows the following formats
 +
 
 +
* 3 Code: [PROGRAM]_[FUNCTION]_[TABLE]
 +
** Example: MD_INS_CASE (Medical Device Inspections, Case Table.)
 +
* 4 Code: [PROGRAM]_[FUNCTION]_[TABLE GROUP]_[TABLE]
 +
** Example: MD_INS_CASE_CAPA (Medical Device Inspections, Case Table Grouping, Corrective Action Plan Table.)
 +
 
 +
=== Table Listing by Schema & Program ===
 +
 
 +
==== MDCCD_DM_OWNER ====
 +
{| class="wikitable"
 +
|+Medical Devices
 +
!Table Name
 +
!Table Description
 +
|-
 +
|[[MDCCD Data Warehouse Data Dictionary#MD INS CASE|MD_INS_CASE]]
 +
|This table is the main medical device inspection table.
 +
|-
 +
|[[:en:MDCCD_Data_Warehouse_Data_Dictionary#MD_INS_CASE_CAPA|MD_INS_CASE_CAPA]]
 +
|This table holds information about the corrective action plan for an inspection observation.
 +
|-
 +
|[[MDCCD Data Warehouse Data Dictionary#MD INS CASE DESIGNATION|MD_INS_CASE_DESIGNATION]]
 +
|This table holds designation codes for an inspection.
 +
|-
 +
|[[MDCCD Data Warehouse Data Dictionary#MD INS CASE ESTACTIVITY|MD_INS_CASE_ESTACTIVITY]]
 +
|This table holds the activities of the establishment as entered by the inspector.
 +
|-
 +
|[[MDCCD Data Warehouse Data Dictionary#MD INS CASE INSPECTORS|MD_INS_CASE_INSPECTORS]]
 +
|This table holds a list of inspectors working on the inspection.
 +
|-
 +
|[[MDCCD Data Warehouse Data Dictionary#MD INS CASE NOTES|MD_INS_CASE_NOTES]]
 +
|This table holds the notes for an inspection.
 +
|-
 +
|[[MDCCD Data Warehouse Data Dictionary#MD INS CASE OBSERVATIONS|MD_INS_CASE_OBSERVATIONS]]
 +
|This table holds the observations for an inspection.
 +
|-
 +
|[[MDCCD Data Warehouse Data Dictionary#MD INS CASE WORKFLOW|MD_INS_CASE_WORKFLOW]]
 +
|This table holds the business process flow (workflow) for an inspection.
 +
|-
 +
|[[MDCCD Data Warehouse Data Dictionary#MD INS INS IID|MD_INS_IID]]
 +
|This table holds the ''Initial Inspection Deficiency'' information for an inspection.
 +
|-
 +
|[[MDCCD Data Warehouse Data Dictionary#MD INS INS INSPECTORLIST|MD_INS_INSPECTORLIST]]
 +
|This table holds a list of inspectors in the MDID system.
 +
|-
 +
|[[MDCCD Data Warehouse Data Dictionary#MD INS INS IRC ENFORCEMENT|MD_INS_IRC_ENFORCEMENT]]
 +
|This table holds the enforcement actions listed for an inspection as part of the ''Inspection Report Card''.
 +
|-
 +
|[[MDCCD Data Warehouse Data Dictionary#MD INS INS IRC MEASURES|MD_INS_IRC_MEASURES]]
 +
|This table holds the measures listed for an inspection as part of the ''Inspection Report Card''.
 +
|-
 +
|[[MDCCD Data Warehouse Data Dictionary#MD INS INS IRC OBSERVATIONS|MD_INS_IRC_OBSERVATIONS]]
 +
|This table holds the observations listed for an inspection as part of the ''Inspection Report Card''.
 +
|-
 +
|[[MDCCD Data Warehouse Data Dictionary#MD INS INS SITE|MD_INS_SITE]]
 +
|This table holds the listing of sites from the MDID system including those migrated from the Medical Device System (MDS).
 +
|-
 +
|[[MDCCD Data Warehouse Data Dictionary#MD INS INS SITE CONTACT|MD_INS_SITE_CONTACT]]
 +
|This table holds the listing of site contacts from the MDID system including those migrated from the Medical Device System (MDS).
 +
|-
 +
|[[MDCCD Data Warehouse Data Dictionary#MD INS INS LICENCE|MD_INS_SITE_LICENCE]]
 +
|This table holds the listing of site establishment licences from the MDID system including those migrated from the Medical Device System (MDS).
 +
|-
 +
|[[MDCCD Data Warehouse Data Dictionary#MD INS INS SITELICENCEACTIVITY|MD_INS_SITE_LICENCEACTIVITY]]
 +
|This table holds the listing of site establishment licence activities from the MDID system including those migrated from the Medical Device System (MDS).
 +
|}
 +
 
 +
== Contacts ==
 +
 
 +
=== Access & Questions ===
 +
 
 +
* Jeff Tyndall (jeff.tyndall@hc-sc.gc.ca)
 +
* Caroline Mimeault (caroline.mimeault@hc-sc.gc.ca)

Latest revision as of 14:09, 28 August 2024

Subject to deletion
 This page has been highlighted as a candidate for deletion. Please see the discussion. Reason: Page has been moved to internal Health Canada wiki. Internal database structure should not be public.

Overview

The Medical Device and Clinical Compliance (MDCCD) Data Warehouse is an Oracle 19c based data warehouse maintained by Shared Services Canada (SSC) and Health Canada's (HC) Digital Transformation Branch (DTB).

The MDCCD Data Warehouse uses a concept called "Fundamental Datasets". Fundamental Datasets are meant to be a standardized dataset that is optimized for business intelligence and data science.

Problems to Address

  • There is no single source of truth for MDCCD’s data.
  • Program data is stored a various repositories.
    • Ex: IRS, MDS, eCES, RADAR, ARISg, eSAP
  • Repositories are often poorly documented.
  • Data is often incomplete, inaccurate or missing.
  • IT systems are constantly changing.
    • Requires tools and business intelligence to be rebuilt.
  • Data Analysts are not always allowed to access production databases.

Benefits of the MDCCD Warehouse

  • Better data quality: A data warehouse centralizes data from a variety of data sources, such as transactional systems, operational databases, and flat files. It then cleanses the operational data, eliminates duplicates, and standardizes it to create a single source of the truth.
  • Faster insights: Data warehouses enable data integration, allowing program users to leverage all of the program’s data into each business decision. Data warehouse data makes it possible to report on themes, trends, aggregations, and other relationships among data collected.
  • Smarter decision-making:  A data warehouse supports large-scale BI functions such as data mining (finding unseen patterns and relationships in data), artificial intelligence, and machine learning—tools data professionals and program leaders can use to get hard evidence for making smarter decisions in virtually every area of the organization, from compliance verification to financial management and inspection planning.
  • Time savings: By using standardized datasets, business intelligence and automation tools can escape the cycle of constant rebuilding whenever IT systems change.
  • Documented data: Having all the program data in one location allows for the data to be properly documented with data dictionaries and entity-relationship diagrams resulting in consistent interpretations and reducing misunderstandings. Well documented data also allows for faster product development and training of data analysts.

Scope, Phases & Scalability

Scope

  • IT Systems that do not have a preexisting reporting solution.
  • Functions where MDCCD is the owner of data.

Phase 1

  1. Provisioning of data warehouse. – Completed
  2. Creation of standardized datasets for Medical Device inspections. - Completed
  3. Creation of ETL from MDID (Medical Device Inspection Database) to MDCCD Data Warehouse. – In Progress

Phase 2

  1. AHR Registrations & Inspections
  2. CTO Registrations & Inspections

Phase 3

  1. Medical Device Compliance Verification, Recalls & Establishment Licencing

Future Phases

  • Data utilized by MDCCD, but where MDCCD is not the data owner (Ex: ARISg, eSAP)
  • Data from new IT systems (Ex: CIELS)

Potential Scalability

  • Incorporate data currently captured by another reporting system (Ex. RADAR Reporting Database).
  • Incorporate data utilized by other program partners (Ex. Medical Device Licencing, Drug Product Dictionary).

Systems

This section outlines the IT systems that have their data included in the Data Warehouse.

Medical Device Compliance Program

Medical Device Inspection Database (MDID)
  • This system holds Medical Device Inspection records.

Schemas

The MDCCD Data Warehouse contains two database schemas:

  • MDCCD_DM_OWNER
    • Holds all tables and views used for reporting & analysis.
  • MDCCD_STG_OWNER
    • Holds all tables used for staging as part of the ETL process to populate tables held in the MDCCD_DM_OWNER schema.

Tables

Table Naming Convention

Tables are named using a 3 or 4 code convention.

  • PROGRAM
    • This two letter code indicates the MDCCD program that the table applies to.
      • MD - Medical Device
      • AH - Assisted Human Reproduction
      • BD - Blood
      • CO - Cells, Tissues, Organs
      • CT - Clinical Trials
  • FUNCTION
    • This two to three letter code indicates the functional area of the program.
      • INS - Inspection
      • EST - Establishment Licence
      • COM - Company
      • INC - Incident
      • REC - Recall
  • TABLE GROUP
    • This code indicates if the table is part of a larger grouping of tables that hold common information.
    • CASE - Case Information
    • IRC - Inspection Report Card Information
  • TABLE
    • This code indicates the name of the table.

The convention follows the following formats

  • 3 Code: [PROGRAM]_[FUNCTION]_[TABLE]
    • Example: MD_INS_CASE (Medical Device Inspections, Case Table.)
  • 4 Code: [PROGRAM]_[FUNCTION]_[TABLE GROUP]_[TABLE]
    • Example: MD_INS_CASE_CAPA (Medical Device Inspections, Case Table Grouping, Corrective Action Plan Table.)

Table Listing by Schema & Program

MDCCD_DM_OWNER

Medical Devices
Table Name Table Description
MD_INS_CASE This table is the main medical device inspection table.
MD_INS_CASE_CAPA This table holds information about the corrective action plan for an inspection observation.
MD_INS_CASE_DESIGNATION This table holds designation codes for an inspection.
MD_INS_CASE_ESTACTIVITY This table holds the activities of the establishment as entered by the inspector.
MD_INS_CASE_INSPECTORS This table holds a list of inspectors working on the inspection.
MD_INS_CASE_NOTES This table holds the notes for an inspection.
MD_INS_CASE_OBSERVATIONS This table holds the observations for an inspection.
MD_INS_CASE_WORKFLOW This table holds the business process flow (workflow) for an inspection.
MD_INS_IID This table holds the Initial Inspection Deficiency information for an inspection.
MD_INS_INSPECTORLIST This table holds a list of inspectors in the MDID system.
MD_INS_IRC_ENFORCEMENT This table holds the enforcement actions listed for an inspection as part of the Inspection Report Card.
MD_INS_IRC_MEASURES This table holds the measures listed for an inspection as part of the Inspection Report Card.
MD_INS_IRC_OBSERVATIONS This table holds the observations listed for an inspection as part of the Inspection Report Card.
MD_INS_SITE This table holds the listing of sites from the MDID system including those migrated from the Medical Device System (MDS).
MD_INS_SITE_CONTACT This table holds the listing of site contacts from the MDID system including those migrated from the Medical Device System (MDS).
MD_INS_SITE_LICENCE This table holds the listing of site establishment licences from the MDID system including those migrated from the Medical Device System (MDS).
MD_INS_SITE_LICENCEACTIVITY This table holds the listing of site establishment licence activities from the MDID system including those migrated from the Medical Device System (MDS).

Contacts

Access & Questions

  • Jeff Tyndall (jeff.tyndall@hc-sc.gc.ca)
  • Caroline Mimeault (caroline.mimeault@hc-sc.gc.ca)
Retrieved from "https://wiki.gccollab.ca/index.php?title=MDCCD_Data_Warehouse&oldid=124250"