OpenRIMS-RPM - Data Sources Provided
Objective
The OpenRIMS implements data visualization in two ways:
- Electronic forms and data grids allow collect and manage data
- Data Warehouse to allow external BI tool data querying and online analytical processing
Data querying and online analytical processing require data sources. Currently, a data source is a result of a complex SQL query to the Data Warehouse. To create the SQL query, an advanced expert will be required. At the very first stage of the implementation, it may exert a negative influence on OpenRIMS advancement.
To avoid this, it will be necessary to provide data sources that can be used immediately by the BI tool. A good solution for it is database views built using SQL queries against the Data Warehouse.
This manual covers by example the usage of the provided data sources for the visualization. The annexes contain descriptions of the provided data sources and the usage particularities. The presumed audience is BI developers.
All examples above were created using Google Looker Studio. These examples are for learning.
This document is a successor to the “Google Data Studio application for Pharmadex 2 reporting” manual.
Examples
Introduction
The examples build using the provided data sources and Google Looker Studio. The editor's access to the examples is not publicity allowed. To reproduce the examples:
- Use OpenRIMS installation 2023-06-09 or later
- Create reports in Google Looker Studio using the configurations below
Fields in the data sources are uniform by name. A detailed explanation of them can be found here [Annex 1 Provided Data Sources]
The details are in the following annexes.
Permits Register
https://lookerstudio.google.com/u/0/reporting/745cf19d-2e72-4575-95d7-96e1cdb49557/page/VY3PD
The OpenRIMS is a permit management electronic tool. Thus, at least a register of valid permits should be published. This example demonstrates the usage of
- The provided data source “select * from report_permits where PermitState='ACTIVE'”.
- Visualization language definition
- OpenRIMS publicly available permit data feature [Annex 6 HTTP link to public permit in OpenRIMS]
Figure 1 Valid Permits report.
The explanation is below
| Table | Google Looker Data Studio Data Set | ||
| Dimensions | Metrics | Filters | |
| 1 | PermitModule | COUNT_DISTINCT(PermitID) | Lang = EN_US |
| 2 | PermitHolder | COUNT_DISTINCT(PermitID) | Lang = EN_US |
| 3 | ClassifierValue | COUNT_DISTINCT(PermitID) | Lang = EN_US
classifierUrl=dictionary.admin.units classifierLevel=1 |
| 4 | linked | Lang equal to EN_US
LinkedUrl contains owner | |
| 5 | PermitName
EventDate EventNextDate EventRegNo Details[1] |
Lang equal to EN_US
EventURL contains cert | |
NMRA External Services KPI
This example can be found here
https://lookerstudio.google.com/u/0/reporting/fde4514b-a1c4-4c19-8781-8207571c8f23/page/bpPND
From the business point of view, the NMRA provides a set of services to apply for a permit and, then, maintain it. From the NMRA point of view, any given service is an internal process or a group of ones. The OpenRIMS digitalized workflows automate internal processes.
The example demonstrates:
- The provided data source is “select * from report_kpi”
- Usage of metrics to calculate KPIs
- Quantity of services completed for a permit
- Duration of the completion of each service
Figure 2 Services KPI report
| Table | Google Looker Data Studio Data Set | ||
| Dimensions | Metrics | Filters | |
| 1 | PermitName
Details[2] |
COUNT_DISTINCT(WorkflowID) | Lang = EN_US |
| 2 | WorkflowCome
Workflow |
Average of
DATE_DIFF(WorkflowGo,WorkflowCome)+1 |
Lang = EN_US |
NMRA internal KPI
The example can be found here
https://lookerstudio.google.com/u/0/reporting/f7809d67-d03c-40e8-a8c3-80fb2cef40df/page/29uND
The minimal accountability by OpenRIMS NMRA activity is a work unit in a workflow. The maximal one is a workflow. It is possible to calculate many different KPIs. The example demonstrates only some of them:
1. The provided data source is “select * from report_kpi”
2. KPIs
2.1. Workflows
2.1.1.The average duration in days to complete a workflow
2.1.2.Quantity of the workflows
2.1.3.Quantity of the services completed per year
2.2. Work units
2.2.1.Quantity of the work units
2.2.2.Quantity of the work units by NMRA branches
2.3. Employees performance
2.3.1.Min days to complete a work unit
2.3.2.Max days to complete a work unit
2.3.3.Quantity of work units
Figure 3 Services, Jobs, and Employees KPI
| Table | Google Looker Data Studio Data Set | ||
| Dimensions | Metrics | Filters | |
| 1 | Workflow | AVG(DATE_DIFF(WorkflowGo,WorkflowCome))
COUNT_DISTINCT(WorkflowID) |
Lang = EN_US |
| 2 | YEAR(WorkflowCome) | COUNT_DISTINCT(WorkflowID) | Lang = EN_US |
| 3 | Activity | COUNT_DISTINCT(ActivityID) | Lang = EN_US |
| 4 | ActivityDepartment | COUNT_DISTINCT(ActivityID) | Lang = EN_US |
| 5 | ActivityExecutor | min(DATE_DIFF(ActivityGo,ActivityCome)+1)
MAX(DATE_DIFF(ActivityGo,ActivityCome)) COUNT_DISTINCT(ActivityID) |
Lang = EN_US |
States and transitions
The example can be found here
https://lookerstudio.google.com/u/0/reporting/10412b6e-bbe7-4de8-92e2-3aa597ed1059/page/q9zVD
The previous example demonstrated KPIs calculated using states of permits. This example demonstrates KPIs calculated using state transitions. A permit’s life cycle diagram is below:
Figure 4 Permit life cycle. States and transitions
- The main path of states is “Not Submitted” -> “Pending Considerations” -> “Active” –> “Inactive”.
- States “On Modification”, “On revocation”, and “On deregistration” are sub-states of the “Active” state. For current, there is no way to distinguish them in reports.
- To follow the main path in the forward direction the respective workflows should be started
- Any given workflow has two possible outcomes – Approve and Decline. In case of the outcome is “Approve” the permit will go into the next state. In case of the outcome “Decline,” the permit will go into the previous state.
- The “Pending Consideration” state allows two additional outcomes:
- Return To Applicant – return the application to the applicant to fix minor mistakes
- Company – to approve the company registration application
- The “Lost” state is reserved for permits that do not fit any other state
The “States and Transitions” report demonstrates the reporting of Approved and Declined outcomes of workflows.
Figure 5 The State and Transitions report
This report uses global filters (File-Report Settings):
Figure 6 Global filter by language
Figure 7 Global filter by outcomes
| Table | Google Looker Data Studio Data Set | ||
| Dimensions | Metrics | Filters | |
| 1 | WorkflowDictName | COUNT_DISTINCT(WorkflowID) | |
| 2 | YEAR(WorkflowGo) | COUNT_DISTINCT(WorkflowID) | |
| 3 | Workflow | COUNT_DISTINCT(WorkflowID) | |
| 4 | ActivityOutcome | COUNT_DISTINCT(WorkflowID) | |
| 5 | PermitName
when 'ACTIVE' then 'Approved' when 'ONAPPROVAL' then 'Pending' when 'NOTSUBMITTED' then 'Not submitted' when 'DEREGISTERED' then 'Inactive' END
|
||
Internal Classifiers
In the example [ Permits Register ], table 3 “Provinces” contains a sample of the usage of the “dictionary.admin.units” classifier. This classifier is defined by the applicant.
The classifier also may be defined by NRA in approving or revoking workflows. The example below is a simplified usage of the classifier defined by NRA in approving workflow.
Global filters
Figure 8 Select only applications (permits) that have internal classifiers.
Figure 9 Select only level zero of the classifier - selected value
Figure 10 Select only English US language
Figure 11 Simplified Internal Classifiers report
| Table | Google Looker Data Studio Data Set | ||
| Dimensions | Metrics | Filters | |
| 1 | ClassifierValue | COUNT_DISTINCT(WorkflowID) | |
| 2 | PermitName
when 'ACTIVE' then 'Approved' when 'ONAPPROVAL' then 'Pending' when 'NOTSUBMITTED' then 'draft' when 'DEREGISTERED' then 'Inactive' END
|
||
Annex 1 Provided Data Sources
Introduction
The provided data sources are database views:
· report_permits to publish and analyze currently valid permits
· report_kpi to get KPIs
A view consists of a report data language field and fields responsible for visual dimensions, filters, and metrics.
Both views contain permit data.
Report data language
Any given data source contains the field “Lang”. Possible values of this field are language identifiers, e.g., “EN_US”.
Figure 12 Select only the US_EN language filter
Additionally, it is appreciated to use the data source SQL:
select * from report_kpi where Lang='EN_US'
The report_permit view
Purpose
The report_permit view allows publishing permit data for any kind and state of permit.
The data allows the creation of tables, charts, and GIS visualizations. It is possible to use the public data OpenRIMS feature to link the data visualization to permit data [Annex 6 HTTP link to public permit in OpenRIMS]
Permit data
Fields below allow building visible dimensions, calculation metrics, and filter data by a permit type
| Field | Data type | Dimension | Metric | Filter |
| PermitState | varchar(255) | PermitState. Possible values are[4]:
· ACTIVE · ONAPPROVAL · DEREGISTERED · NOTSUBMITTED · LOST |
Counter of permits in the defined state | Filter permits by state |
| PermitID | bigint(20) | Counter of PermitID is the number of permits | ||
| PermitName | longtext | Main name of permit subject, e.g., “Pharmacy # 12” or “Paracetamol” | ||
| PermitModuleUrl | varchar(255) | Counter of PermitModuleUrl is the number of permit types | To get only permits by type. For example only “vaccine.registration” | |
| PermitModule | varchar(255) | Name of a permit type, e.g., “New Vaccine Registration” | ||
| PermitHolder | varchar(255) | Email address of a permit holder | Counter of PermitHolder is the number of permit holders | It is possible to use this field for access control[5] |
Table 2 Permit Data
Permit classifiers
The Permit Application Form and workflow forms may contain dictionaries or drop lists that allow selection from the pre-defined choices. An example is administrative unit selections. In the “report_permit” data source all these choices are collected to the “classifiers”. The classifiers allow adding to a data set dimensions and filters. Metrics are also possible, however, seem not fully appropriate.
| Field | Data type | Dimension | Metric | Filter |
| ClassifierUrl | varchar(255) | To get only classifiers by defined type. For example, administrative units will be “dictionary.admin.units” | ||
| ClassifierLevel | int(11) | The level of selection in the dictionary. The 0 (zero) is the current selection For administrative units:
0 – municipality 1 – district 2 – province 3 - country | ||
| ClassifierPage | varchar(255) | URL of a page on which the classifier is placed. For example, “nepal.address” | ||
| ClassifierVar | varchar(255) | It is possible to use the same classifier for various purposes. For example, the address of the office and the address of the warehouse. This parameter allows distinct them. | ||
| ClassifierName | mediumtext | Name of the classifier, e.g., “Administrate Units” | ||
| ClassifierValue | mediumtext | Value of the selected classifier on the level defined, e.g., “Diprung Chuichumma Gaunpalika” | ||
| ClassifierAltValue | mediumtext | Currently only GIS coordinates are selected for the Administrative Units” classifier. | ||
| ClassifierPathValue | mediumtext | All levels of the selected classifier, e.g.,
Diprung Chuichumma Gaunpalika, KHOTANG, Province 1, Nepal |
Table 3 Permit Classifiers
Permit links
A permit may refer to external data or include linked data. Examples:
- A medicinal product refers to a manufacturer as the final product manufacturer
- Pharmacy data includes pharmacists’ data
The data source provides the linked data for visible dimensions and filters. Metrics are also possible, however, seem not fully appropriate.
| Field | Data type | Dimension | Metric | Filter |
| LinkedPage | varchar(255) | Data page URL on which the link is placed. | ||
| LinkedVar | varchar(255) | The variable name of the link in the data configuration, e.g., “Owners” | ||
| LinkedUrl | varchar(255) | The URL of linked data, e.g., “pharmacy.owner” or “vaccine.manufacturer” | ||
| Linked | mediumtext | The preferred label value of a linked element, e.g., John Smith or Abbot Laboratory | ||
| LinkedDictUrl | varchar(255) | URL of a dictionary that allows the name of a link[6] | ||
| LinkedDictName | mediumtext | Name of the link names dictionary | ||
| LinkedDictItem | mediumtext | Item of link name, e.g., Active Ingredient | ||
| LinkedDictPath | mediumtext | Full name of a link, including all levels and dictionary name. For example, “Final Product, Manufacturer type” |
Table 4 Permit Links
Permit events
An event is something that has been occurred or scheduled to occur. Examples are:
- Assigning the incoming registration number to an application
- Registration of a permit
- Renewal a permit
The data source provides event data for visible dimensions and filters. Metrics are also possible, however, seem not fully appropriate.
| Field | Data type | Dimension | Metric | Filter |
| EventUrl | varchar(255) | Event type, for example “application.pharmacy.renew” or “register.certificate” | ||
| EventDate | date | Date of an event | ||
| EventRegNo | varchar(255) | Registration number of an event | ||
| EventNextDate | date | Data of the next scheduled event, e.g., expiration of the registration |
Table 5 Permit Events
The report_kpi view
Purpose
The report_kpi view allows calculations of Key Performance Indicators (KPI).
The KPIs can be represented as table and chart visualizations.
Permit data
See [Permit data] above.
Workflows
A workflow is a digitalized form of the NMRA working process related to a single permit. The most used KPIs are duration and quantity.
| Field | Data type | Dimension | Metric | Filter |
| WorkflowDictUrl | varchar(255) | Counter of it is the number of workflow types | To get only workflows by a defined type. For example, “dictionary.guest.
applications” – initial registration applications | |
| WorkflowDictName | varchar(255) | The name of the workflow type, e.g., “Applications” | ||
| WorkflowUrl | varchar(255) | Counter it is the number of workflows subject | To get only workflows by a defined subject, for example, “retail.site.owned.
persons” – only applications related to sites owned by individuals | |
| Workflow | varchar(255) | The name of the workflow subject, e.g., “Register an individually owned pharmacy” | ||
| WorkflowID | bigint(20) | A counter is the number of workflows | ||
| WorkflowCome | datetime | Date when the workflow has been started | Dates difference calculates the duration of the workflow | Select workflows started before/after some date |
| WorkflowGo | datetime | Date when the workflow has been finished or today`s date in the workflow is not finished | Select workflows finished before/after some date or unfinished yet |
Workflow activities (jobs)
A workflow activity is a job unit inside a workflow completed by a single performer. The most used KPIs are duration and quantity.
| Field | Data type | Dimension | Metric | Filter |
| ActivityConfigID | bigint(20) | Count of it is the number of distinct types of activities, e.g., screenings in the workflow “Register an individually owned pharmacy” | ||
| ActivityUrl | varchar(255) | Select only some activities | ||
| Activity | varchar(255) | The name of an activity, e.g., “Screening” | ||
| ActivityID | bigint(20) | Count of it is the number of all activities | ||
| ActivityCome | date | Data, when the activity has been started | Dates difference calculates the duration of the workflow | |
| ActivityGo | date | Date, when the activity has been finished, or today’s date for unfinished ones | ||
| ActivityOutcome | varchar(255) | Outcome code of activity. Possible values are:
NO – passed to next step APPROVE – approval has been issued DECLINE – application has been declined COMPANY – the new company has been registered |
Select only defined outcomes. The most useful is COMPANY–approved companies | |
| ActivityDepartment | varchar(255) | The name of an NMRA department that has been assigned to execute the activity. For the business executor, the name is “-“ | ||
| ActivityDepartmentID | bigint(20) | The count of it is the number of departments. All applicants are in a single pseudo-department with ID=0 | It is possible to filter NMRA and non-MRA executors. For not NMRA ActivityDepartmentID is 0 (zero) | |
| ActivityExecutor | varchar(255) | The email of the activity executor |
Annex 2 How to create filters?
To use filters, it will be necessary to study a configuration of the electronic forms, available by Administrate-Configuration-Data Configurator. Additionally, attention should be paid to:
1. Workflow configuration dictionaries (Administrate-Configuration-Dictionaries):
1.1. "dictionary.guest.deregistration";
1.2. "dictionary.guest.amendments";
1.3. "dictionary.guest.applications";
1.4. "dictionary.guest.inspections";
1.5. "dictionary.shutdown.applications";
1.6. "dictionary.host.applications";
2. Workflow configurations (Administrate-Processes-Workflows)
Filters are necessary to extract by a defined type of:
1. Permits
2. Classifiers
3. Links
4. Events
5. Workflows
6. Activities
For filtering the data sources provide fields containing URLs. For example, to extract addresses from classifiers use filtering by “dictionary.admin.units” in the variable “ClassifierUrl”.
Some data configurations may contain more than one address. In this case, an additional filter by ClassifierPage or ClassifierVar will be needed.
Annex 3 How to use events?
Currently available are two categories of events:
- Scheduled processes
- Office registers
The scheduled processes are processes that are scheduled for a valid permit. An example is a renewal of a permit.
| Field | Description | Example |
| EventUrl | URL of the scheduled process | application.pharmacy.renew |
| EventDate | Date to which the process has been scheduled. Will be in the future | 2024-01-12 |
Table 6 Fields for scheduled processes
The office registers are implemented by the “registers” component. This component allows automated electronic office registers. An example is the pharmacy permit register.
| Field | Description | Example |
| EventUrl | URL of the register as defined in the Data Configuration | register.new.pharmacy |
| EventDate | Date of the record in the register | 2022-01-12 |
| EventRegNo | Number, assigned to the record | P-0119 |
| EventNextDate | Date, when the record will be expired | 2024-01-11 |
Annex 4 How to calculate metrics?
There are no pre-calculated metrics in the provided data sources, however, it is possible to calculate metrics using the BI tool.
Currently can be calculated such metrics:
- Quantities
- Duration
Quantities can be calculated by the COUNT_DISTINCT formula for Google Looker Studio or by a similar one in another BI tool. The examples are below
| Quantity of | Description | Field to COUNT_DUSTINCT |
| Particular permits | The exact quantity of all permits | PermitID |
| Types of permits | Quantity of applications forms used | PermitModuleUrl |
| Permit’s holders | Quantity of applicants who applied for permits | PermitHolder |
| Workflows | Quantity of particular processes | WorkflowID |
| Activities or job units | Quantity of all job units, regardless of workflow | ActivityID |
| NMRA departments | Quantity of NMRA departments serve processes in OpenRIMS | ActivityDepartmentID |
| Job performers | Quantity of NMRA staff serve processes in OpenRIMS | ActivityExecutor |
Table 7 "Quantity of" metrics
Durations can be calculated by the DATE_DIFF formula for Google Looker Studio or a similar one in another BI tool. The examples are below
| Duration of | Description | DATE_DIFF parameters |
| Workflow process | Days between the starting date of the first job unit to the ending date of the last job unit. In case the last job unit is not finished, the last date of it assumes as today | WorkflowCome, WorkflowGo |
| Job unit | Days between the starting date of a particular activity | ActivityCome, ActivityGo |
Annex 5 HTTP link to permit information in OpenRIMS
The OpenRIMS provides a URL to display permit information in electronic form.
For example, the URL
https://pharmadex.irka.in.ua/public#publicpermitdata/%7B%22permitDataID%22:89984%7D
may get
Figure 13 An example of public permit information. All sections are expandable
The structure of this URL:
1. https://pharmadex.irka.in.ua/public - ask for a public access
2. #publicpermitdata – the name of public permit data UI component
3. permitDataID – the parameter name
4. 89984 – database ID of the permit application data – the value of PermitID field in the provided data sources
The access to information is managed by the Data Configuration feature
Figure 14 Access management fields
1. By default, information is not publicly available
2. By default, the information is always available for an applicant
| Value | Applicant | NMRA staff |
| Yes | Denied | Visible |
| No | Visible | Visible |
| Value | Public | Applicant | NMRA staff |
| Yes | Visible | See above | Visible |
| No | Denied | See above | Visible |
[1] HYPERLINK(concat("https://pharmadex.irka.in.ua/public?lang=",Lang,"#publicpermitdata/%7B%22permitDataID%22:",PermitID,"%7D"), 'open')
[2] HYPERLINK(concat("https://pharmadex.irka.in.ua/public?lang=",Lang,"#publicpermitdata/%7B%22permitDataID%22:",PermitID,"%7D"), 'open')
[3] HYPERLINK(concat("https://pharmadex.irka.in.ua/public?lang=",Lang,"#publicpermitdata/%7B%22permitDataID%22:",PermitID,"%7D"), 'open')
[4] For the local language, use formulas, like https://support.google.com/looker-studio/answer/10471275?hl=en&ref_topic=10490726&sjid=12992141586080825928-EU#zippy=%2Cin-this-article
[5] https://support.google.com/looker-studio/answer/9713766?hl=en
[6] For any particular link to the external data, the link name may be defined. For example, manufacturer of final product, active ingredients, packager, etc.