OpenRIMS-RPM - Data Sources Provided: Difference between revisions

From OpenRIMS Wiki
Jump to navigation Jump to search
 
(No difference)

Latest revision as of 20:19, 14 August 2023

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 1 Valid Permits report explanation
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


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

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

Table 8 Hide from an applicant access rules
Value Applicant NMRA staff
Yes Denied Visible
No Visible Visible
Table 9 Publicity access rules
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.