Reporting Overview

  • The SmileCDR data is not stored in a format that is compatible with reporting, it first needs to be transformed into a format that Web Focus can process.
  • A transformation process is scheduled to run every night, it transforms the FHIR json data into traditional relational tables and views.
  • WebFocus has a single comprehensive business view defined that reads the views and some of the tables. The business view is designed to allow users to create their own reports.
  • The clients will use the Web Focus web application to build, run, share, schedule and distribute reports.

ExpansionReporting

SmileCDR Tables

SmileCDRTables

SmileCDR is built on top of HAPI FHIR which is open source. For more information see HAPI FHIR Schema

Table Description
HJF_RES_VER The HFJ_RES_VER table contains individual versions of a resource. If the resource Patient/1 has 3 versions, there will be 3 rows in this table. The complete raw contents of the resource is stored in the RES_TEXT column.
HJF_RESOURCE The HFJ_RESOURCE table indicates a single resource of any type in the database. For example, the resource Patient/1 will have exactly one row in this table, representing all versions of the resource.
HJF_FORCED_ID Since the server has been configured with a Resource Server ID Strategy of UUID a Forced ID record will be generated for all resources.

Transformation Process

  • Technology: Oracle 12c Stored Procedures and Functions
  • Execution: Is scheduled to run nightly
  • Type: Full Refresh. Due to the way historical records are stored the procedure performs a full refresh of the relational tables instead of a delta
Type Object Description
Procedure LOAD_REPORTING_TABLES Main stored Procedure
Function GET_ATTACHMENTS_FROM_RESPONSE Searches JSON that was passed in for attachments and inserts a record(s) in RTE_RESPONSE_ATTACHMENT
Function GET_DATE_FROM_JSON_ARRAY Used to get a Date value from an array of extensions
Function GET_REFERENCE_FROM_JSON_ARRAY Used to get a Reference value from an array of extensions
Function GET_STRING_FROM_JSON_ARRAY Used to get a String value from an array of extensions or indentifiers
Table RTE_JOB_RUN_LOG A log on when the process starts and completes. If an error is encountered that is also logged.

Relational Tables

Table Description Includes History
RTE_APPLICANT Applicant/patient name and birth date No
RTE_CODE_TABLE_LOOKUP All the codes and descriptions No
RTE_ORGANIZATION The client organization, this is needed to get the code (Example: ODSP) No
RTE_PRACTITIONER The Health Care Professional (HCP) details No
RTE_PRACTITIONER_QUALIFICATION Qualifications associated with the Health Care Professional (Example: Medical Doctor (CPSO) license) No
RTE_PRACTITIONER_ROLE A role a HCP plays in the application (Examples: Requestor, Delegate, Designate) No
RTE_QUESTIONNAIRE_ALL Questionnaire Definition including modification history Yes
RTE_QUESIONNAIRE_RESPONSE Questionnaire Reponse, this is needed to join to the correct version of the Questionnaire No
RTE_REQUEST Requests, current record only No
RTE_REQUEST_ALL Requests, includes modification history Yes
RTE_REQUESTOR_LOCATION Needed to report on the name of the location associated with the request No
RTE_RESPONSE_ATTACHMENT Attachment details (size, mime type) the user uploaded in response to a question No

Views

View Description
RTE_REQUEST_VIEW Requests (current record only)
RTE_PRACTITIONER_ROLE_VIEW User Roles (current record only)