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

Table 1 Valid Permits report explanation

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 case PermitState when 'ACTIVE' then 'Approved' when 'ONAPPROVAL' then 'Pending' when 'NOTSUBMITTED' then 'Not submitted' when 'DEREGISTERED' then 'Inactive' END PermitHolder Hyperlink to the public permit data

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 case PermitState when 'ACTIVE' then 'Approved' when 'ONAPPROVAL' then 'Pending' when 'NOTSUBMITTED' then 'draft' when 'DEREGISTERED' then 'Inactive' END Hyperlink[3]

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

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

Table 8 Hide from an applicant access rules

Value	Public	Applicant	NMRA staff
Yes	Visible	See above	Visible
No	Denied	See above	Visible

Table 9 Publicity access rules

---

[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.