Author Affiliations
Jeff Brown, Ph.D.1
David Hertz, B.S.2
Richard Platt, M.D., M.Sc.1
Robert Rosen, M.B.A.2
Adam Sigelman, B.A.3
Beth Syat, M.P.H.3
Kiran Shah, B.S.2
1Harvard Medical School and Harvard Pilgrim Health Care Institute
2Lincoln Peak Partners
3Harvard Pilgrim Health Care Institute
Abstract
The Agency for Healthcare Research and Quality (AHRQ) has identified the need for clinical, administrative, and other kinds of health care information from very large populations to address topics central to its mission, and to the missions of other Federal agencies. A distributed health data network advances AHRQ’s Effective Health Care and Health Information Technology research goals by focusing on standardized and reusable information technology approaches for speeding the initiation and conduct of large-scale comparative effectiveness studies, including studies of treatments covered by Medicaid, Medicare, CHIP, and other Federal payers.
As part of a prior task order contract (HHSA 290-2005-0033-I-TO5), the current team developed design specifications for a scalable, distributed, health data network, created a prototype for the network, performed a study using distributed principles assessing the comparative effectiveness of second line anti-hypertensive therapy, and created a blueprint for implementing a distributed health data network.
The current activity ("Enhanced Functionality for the Distributed Research Network Pilot") involved continued development of the previously created prototype. Work involved building more robust infrastructure components for a distributed network for comparative effectiveness research and illustrating its use using health plan data. Specifically, network software and portal development included more advanced access control, permissions, usage controls, and query type mechanisms, a redesigned research user interface, additional user notifications, and workflow, auditing and monitoring features. A functioning pilot network that supports distributing menu-driven queries was created, whereby queries are executed against local data resources, and results are aggregated for review.
Introduction
Overall Goal of the Distributed Research Network (DRN)
The overall goal of the project is to create a secure Web-based network that can integrate health data from a wide array of sources and make it available to AHRQ and its partners without requiring the large amount of time and effort that currently delays effective decision making. The network also will help avoid the redundancy that has begun to occur as different Federal programs begin to use the same data resources for similar purposes.
Rationale
AHRQ has identified the need for clinical, administrative, and other kinds of health care information from very large populations to address many topics central to its mission, and to the missions of other Federal agencies, including CMS, and other partners.
A distributed network advances AHRQ’s Effective Health Care and Health Information Technology research goals by focusing on standardized and re-usable information technology approaches for speeding the initiation and conduct of large-scale comparative effectiveness studies, including studies of treatments covered by Medicaid, Medicare, SCHIP, and other Federal payers. Other research topics that could be addressed include coverage with evidence development and assessment of long-term outcomes. The network also will allow rapid generating of evidence related to the diffusion of new medical technologies. The system will make actionable information available in a more timely way than traditionally occurs in standard research environments.
A fully functioning network will expedite comparative research studies by facilitating access to large and diverse populations and through reuse of existing or newly created data resources, and standardization of study procedures and analytic approaches. Such a network also will allow rapid response to simple queries that are often needed to assess the size and scope of emerging public health priorities.
Background
Our prior work (contract 290-2005-0033I-TO5)* developed a comprehensive framework and design for a scalable and secure distributed research network.1,2 Detailed stakeholder discussions and technical assessments identified the following as paramount concerns for a network: complete control of, and access to, uses of their data; strong security and privacy features; limited impact on internal systems; minimal data transfer; auditable processes; standardization of administrative and regulatory agreements; transparent governance; and ease of participation and use.2
The proposed network design therefore features a secure Web-based portal that performs network functions, such as operations (e.g., workflow, policy rules, auditing, query formation and distribution) and security (e.g., authentication, authorization) and distributed data marts that remain under the control of the data partners.
The intent of this activity is to make progress in developing the capabilities and functionality necessary for a fully functioning network. The enhancements described below will be incorporated into Phase 2 of our Distributed Research Network (DRN) activities (290-2005-0033I-TO12), reducing the demand on the new task order’s resources and accelerating the availability of functional network components. In addition, continuing development serves to improve the usefulness of the stakeholder feedback, as it can be elicited through demonstrations of the prototype and its current and future capabilities. We designated this activity as Phase 1.5, to distinguish it from the task order that ended in August 2009 (DRN 1.0) and the new task order that began in December 2009 (DRN 2.0).
Figure 1 illustrates the high-level network architecture. Our approach was based on lessons learned from Phase 1 of DRN and specifically on our findings that a network should be built in phases to improve the efficiency of software development, elicit strong support and trust from early adopters, and provide a functional platform to solicit expansion of the network.
Figure 1. Distributed research network system architecture
Specific Aims and Methods
The project includes three specific aims.
Aim 1: Develop and test software that allows authorized users to execute queries across distributed, de-identified health plan databases and rapidly return summary data.
This aim focused on building additional areas of the architecture to improve usability and speed and drive acceptance of the system by participating and prospective members. The new functionality and capabilities developed as part of this aim are listed below.
DRN Portal Functions
- Access Control Manager/Data Entitlements—An access control architecture was designed, and an access control framework was constructed that includes data entitlement functions enabling system and data mart administrators to restrict access to data marts, query types, and query parameters based on individual users’ roles or affiliations with organization or groups.
- Workflow Manager/Notification Functions—The workflow manager functionality was extended to include a notification framework that centralizes notification management and provides end-user control over delivery of notifications, including an alert to query submitters when results become available.
- Data Mart Administrator User Interface—A data mart provisioning feature was added, enabling non-technical administrators to set up and maintain data mart information in the Portal, including the ability to add new data marts and edit a data mart’s configuration.
Data Mart Client Functions
- Workflow Manager—In this phase, the Data Mart Client was extended so that it can receive and execute queries in an unattended mode that periodically polls the Portal to find queries, and, based on local query rules, downloads and executes queries and uploads results automatically without any user interaction required.
- Audit Manager—Auditing and reporting capabilities were added for transaction and event logging, logging of query receipt and execution, as well as errors and exceptions.
Aim 2: Deploy the software developed in Aim 1 in multiple data owner partners and test the functionality of the system in a real world setting.
We deployed and tested the infrastructure developed in Aim 1. Testing involved querying data resources maintained and secured by the data partners to establish the functionality of the system in a real-world setting. We installed and tested at five sites: Harvard Pilgrim Health Care, Group Health Cooperative, Geisinger Health System, HealthPartners Research Foundation, and Kaiser Permanente Colorado.
Aim 3: Document the technical infrastructure and provide specific recommendations for continued deployment and development.
Implementation of the software included a Webinar describing the new features, instructions for use, and a discussion of suggested revisions.
Results
This section describes the final design of the DRN 1.5 system. The first section details the system architecture, the second section describes the design of the new portal functions, and the last section describes the enhancements to the DRN Data Mart Client.
System Architecture and Components
The components of the DRN System Architecture are illustrated in Figure 1.
The major architectural components for the Network are the Portal, the Data Marts, and the Data Mart Client.
The web-based Portal is hosted on a centrally accessible server and provides all of the centralized services. Investigators log in to the Portal using a web-based user interface, and submit menu-driven queries via the Portal. After queries are executed by the Data Marts, results are returned to the Portal, where they are aggregated and made available to the submitter.
The data mart components run locally at the data partner location. Two different types of data marts allow for flexibility in the deployment model. The Server Based Data Mart is hosted on a server, and it exposes a web services interface utilized by the Portal to automatically send and execute queries and retrieve results. The Client Based Data Mart is a lightweight desktop application, intended to run on the Data Mart administrator’s desktop computer. The Client Based Data Mart uses a web services interface exposed by the Portal to manually download and execute queries and upload results.
The components of the DRN System Architecture are described in the table in Appendix A1. The code for the components of the System Architecture is organized into "projects." The table in Appendix A2 summarizes the languages and tools used in the network architecture implementation.
Portal Functions
Access Control Manager/Data Entitlements
System enhancements now allow system and data mart administrators to restrict access to data marts and query types (e.g., ICD-9 diagnosis codes or generic names) based on individual users, organizations, or groups. This new functionality greatly improves data partner acceptance by permitting fine-grained control over who can submit queries to their data marts and which data marts are available for querying. Several access control concepts were created to allow this level of access control.
Access Control Concepts
Entity—refers to the recipient of a permission, such as a user or an organization. Entities are organized into a hierarchy, whereby permissions granted to "parent" entities (e.g., an organization) are "inherited" by "child" entities (e.g., a user). For example, an entity could be a user (e.g., Jeff Smith), an organization (e.g., Harvard Pilgrim Health Care), or a group (e.g., the HMO Research Network).
Permission—refers to an allowance granted or restricted by access control. Permissions are associated with a target and granted to an entity.
Target—refers to the object of a permission. Targets can be a right (e.g., "submit query", "view results"), a resource (e.g., a data mart), or a data resource (e.g., query type).
Role—refers to the classification assigned to each user. The assignment of a role confers certain rights. Roles currently include investigator, data mart administrator, and system administrator.
Access Control Entities
Figure 2 illustrates the entity hierarchy for access control.
Figure 2. Access control entity hierarchy
Permissions are inherited by entities through parent/child relationships. An Organization inherits all permissions granted to all Groups to which it belongs, and a User inherits all permissions granted to its parent Organization. Thus, a User has the union of all permissions granted to the User, the parent Organization, and all parent Groups.
Access Control Targets
Appendix Table A3 summarizes the access control targets. Targets are the objects of permissions. Each permission grants access to a target. In addition to the rights inherited through parent entities, all users also retain system rights assigned to their system role. Together, these access control concepts, entities, and targets give system and data mart administrators fine-grained control over who can query their data marts and what investigators can query of that data mart (e.g., query types)
Workflow Manager/Notification Functions
The notification functionality was extended to include centralized notification management and end-user control over delivery of notifications. Most significantly, a query status notification feature was added to alert query submitters when results become available. Details of the notification controls are below.
Notification Concepts
Event—refers to an occurrence of a particular transaction within the system, for example, the submission of a new query. Appendix Table A4 describes the events to be included in DRN 1.5.
Notification—refers to a message describing the occurrence of an event, for example, an email message regarding submission of a new query. Multiple notifications may be generated for a single event.
Event Generation
Events are generated by various components and subsystems, typically at the time the event occurs—e.g., when a new query is entered, a New Query event is generated by the Query Manager.
When an event is generated, the system records the event and notifications in the database. The notification entries are recorded for processing only when the user has opted for such events on the currently established portal.
Notification Options
Users can control generation of notifications by configuring their Notification Options. Notification options can be configured in the Portal through the Investigator User Interface.
Figure 3 depicts a screenshot of how the notification option appears.
Figure 3. Notification option screenshot
Taken together, this new functionality provides an enhanced user experience by creating various notification options and by giving users flexibility over use of those options.
Data Mart Admin User Interface
This enhancement allows nontechnical administrators to set up and maintain data marts in the portal. The provisioning feature includes the ability to add new data marts, edit a data mart’s configuration, and enter a data mart administrator’s contact information. The specific new features are listed below.
Data Mart Administration Menu Item
Users can access the Data Mart Administration feature through the User Interface of the Portal through a new "Data Mart Administration" menu item added to the "Administration" menu of the main navigation bar.
Data Mart List Screen
The Data Mart List screen features a grid containing a list of data marts, as illustrated in Figure 4.
Figure 4. Data mart administration screenshot
Data Mart names are hyperlinks; clicking on the Data Mart’s name will bring up the Data Mart Detail screen for the selected data mart.
Data Mart Detail Screen
The Data Mart Detail screen is used to edit existing data mart information or create a new data mart. The screen contains a form for displaying and editing the data mart attributes as illustrated in Figure 5.
Figure 5. Data mart detail screenshot
Clicking the Permissions button brings up the Data Mart Permissions form as shown in Figure 6.
Figure 6. Data mart permission screenshot
This permissions screen allows data mart administrators and system administrators to control which entities can send queries to the data mart. These enhancements allow system and data mart administrators to update and maintain the list of available data marts in the network.
Query Manager/Results Manager/Investigator User Interface
Three new query types have been added: outpatient procedure codes, inpatient procedure codes, and enrollment. In addition to these query types, query parameter selection has been expanded to include age and sex filters.
New Query Type: ICD-9 Procedure Codes
A new query type was added to the system to support ICD-9 Procedure Code queries as shown in Figure 7.
Figure 7. ICD-9 Procedure code screenshot
New Query Type: HCPCS Procedure Codes
The Healthcare Common Procedure Coding System (HCPCS) is a set of health care procedures codes based on the Current Procedural Terminology (CPT). HCPCS are typically associated with outpatient care. The HCPCS User Interface is as shown in Figure 8.
Figure 8. Health care common procedure coding system screenshot
New Query Type: Enrollment
Enrollment describes information, such as the age and sex distribution of health plan members included in each data mart. The Enrollment User Interface is as shown in Figure 9.
Figure 9. Enrollment screenshot
New Query Parameter: Age Group
A new query parameter has been added to the Query Entry screen to support filtering results by age group. The user interface should present the age group parameter using a list of check boxes, as in Figure 10.
Figure 10. Age parameter screenshot
New Query Parameter: Sex
A new query parameter has been added to the Query Entry screen to support filtering results by sex. The user interface should present the sex parameter using a list of check boxes, as in Figure 11.
Figure 11. Sex parameter screenshot
These three new query types and two new parameters allow investigators more flexibility in the health data queries they submit.
Data Mart Metadata
Metadata was added to the portal to document the available data at each site and provide site-specific information for users. Metadata include years of data available, types of data available by year, key contacts, and special requirements or restrictions on use.
New Database Attributes
Appendix Table A5 lists the new database attributes to be added to the portal database to support metadata attributes for each Data Mart.
User Interface—Display Changes
The Data Mart Metadata is available through a drop-down menu on the Query Entry Form. The Data Mart selection control will be modified to provide hyperlinks to a popup display containing the metadata. The hyperlinks will appear as in Figure 12.
Figure 12. Data mart hyperlinks screenshot
Clicking on a hyperlink causes a popup window to appear containing a listing of the metadata for the selected Data Mart.
Administration
Administration of data mart metadata, including entry and modifications of metadata values, will be done through the Data Mart Admin User Interface as described above.
This data mart metadata enhances investigator experience by giving investigators instant access to detailed information on each of the Data Marts in the network.
Data Mart Client Functions
Workflow Manager
The Data Mart Client was extended so that data mart administrators can setup the system to receive and execute queries automatically in an "unattended mode." A feature was added allowing the Data Mart Client to run "in the background" on the data mart administrator’s desktop system. In this mode, the application periodically polls the Portal to find, download, and execute queries and upload results automatically, without any user interaction required. A set of preferences governs the operation of the service and includes settings, such as the polling duration.
Background Processing operates in one of two modes:
- In Notify Only mode, the application will poll for new queries and alert the user when new queries are found, but it will not process any queries automatically.
- In Process mode, the application will poll for new queries, execute them, and upload results automatically.
A Polling Interval configuration setting specifies the number of minutes between polling attempts.
When new queries are found while in Process mode, the system automatically runs the query and uploads the results to the portal. If the application is operating in Notify mode, the application will notify the user by causing the new query indicator to appear in the system tray icon and a description of the query to be briefly displayed as a tool tip above the system tray icon.
System Tray Icon
The Data Mart Client now contains an icon in the System Tray for the purposes of: (1) indicating that the background process is running; and (2) providing basic status information through a "tool tip."
The "tool tip" is shown when the cursor is hovered over the system tray icon, and provides a status message as illustrated in Figure 13.
Figure 13. System tray icon tool tip screenshot
When the Background Process is in Notify mode, the system tray icon is used to deliver notifications and signal status to the user as illustrated in Figure 14.
Figure 14. System tray icon notification screenshot
When new queries are found, a tool tip is displayed containing a description of the new query as shown. In addition, as long as new queries are present, the new query indicator appears in the system tray icon.
Settings Dialog
The Data Mart Client settings dialog will be modified as shown in Figure 15.
Figure 15. DRN client settings dialog screenshot
These enhancements allow data mart administrators the option to process and upload queries much more efficiently with little need for user interaction.
Audit Manager
To coincide with the unattended processing feature added in the Workflow Manager, a set of auditing and reporting capabilities were added to the Data Mart Client. The auditing features include transaction and event logging, including logging of query receipt and execution as well as errors and exceptions. Reporting features include the ability to run reports filtered by date range, query type, and entity.
Logging
The logging feature provides functions for writing log messages to the Windows Event Log. Appendix Table A6 notes the types of information that are logged. All log messages are written to a separate Windows Event Log File named DRN, which is visible using the Windows Event Viewer.
Figure 16. Windows event view screenshot
Options for log size, rollover, etc. are configurable using the standard Windows Event Viewer properties dialog (Figure 17).
Figure 17. Windows event view properties dialog screenshot
Reporting of events logged through the Audit Manager is possible using the Windows Event Viewer (Figure 17).
This audit manager allows system and data mart administrators to ensure that only authorized users are using the system and that they do so in an appropriate manner.
Discussion
This section describes the lessons learned from the project and the anticipated next steps in the upcoming DRN version.
Lessons Learned in Development and Implementation
The activities involved in the technical development of the DRN 1.5 system reinforced the need for detailed governance policies. Many governance questions arose during the project, including:
- What pre-defined user roles should the system contain, and what rights should those roles confer on users?
- How should system administrators set up new users in the system? What should the "default settings" be for new users? Should data mart administrators be allowed to add new users at their organizations or is that a system administrator function?
- How should the system set up external investigators? Should they fall under an umbrella group or get individual logins?
The governance panel formed in the next phase of the project will address these issues and many others.
Additionally, the Data Mart Administrators from the implementation sites were primarily concerned with issues relating to data protection and privacy. Concerns center on the proposed future inclusion of low cell counts or other Personal Health Information (PHI). Data Mart Administrators also wish to minimize the risk of unauthorized or fraudulent uses the DRN system to access their proprietary information for nefarious purposes. While the DRN partners are comfortable with the development of the tool thus far, many had suggestions on how to improve the security of the system in future versions. These suggestions are outlined in the following section.
Identification of New Functionality
Data Protection and Privacy
All partners and stakeholders stressed the importance of protecting proprietary information. During the course of the project, the project team identified the following additional functionalities that would serve this goal:
- Data deletion: Currently, the system retains query results indefinitely. Future versions should include functionality that deletes results after a specified period.
- Low cell counts: Currently, the system does not mask low cell counts; although data partners could do so on their own. Future versions should give data mart administrators the option of masking low cell counts. Preferably the system would allow data mart administrators to manually set the threshold at which to mask low counts.
- Unattended Mode Permissions: Currently, if the Data Mart Client is set to unattended mode, all queries submitted to the Data Mart will run automatically. Data Mart Administrators have expressed the interest in creating permissions for use of this mode. In other words, they want the ability to restrict the use of unattended mode to only their most trusted partners. Other system users would still be able to submit queries, but the data mart administrators would have to manually run and upload those queries.
- Period Ranges: Currently, the system allows investigators to submit queries to data marts regardless of the available data in that data mart. A more sophisticated system would only display data marts as options if those data marts contained the data in the requested periods. For instance, if a data mart did not have data from 2008, an investigator would not see it in the Query Interface after selecting 2008 as the requested period.
Validation of Local Data Marts
DRN 1.5 assumes that the underlying data is in the correct format. However, as new partners begin to create their own tables, it will be important to verify the structure of the data in the data marts. Validation will result in fewer DRN errors and higher overall data quality. The requested enhancement would entail a series of functions that would scan the underlying database to validate that:
(a) All necessary tables exist;
(b) Each table has the appropriate column setup; and
(c) Data in each of the table rows match the expected format and accepted values.
Both data mart administrators and system administrators should have the option of validating the underlying data. This activity would be performed when adding new data marts or updating the data in existing data marts.
Next Steps
The primary suggestions for additional development of the system include:
- Continued development to improve access control, security, and usability
- Create the ability to exchange files, thereby allowing investigators to submit analytic code to data marts and receive result sets in return
- Investigate the technical requirements needed to perform distributed multivariate analysis using network resources
- Additional query types should be investigated, including more granular diagnosis and procedure information and incident exposure/diagnosis counts in addition to the prevalent counts now included
- Incorporate data validation and visualization components in the portal to help investigators validate query results, view them graphically, and actively manipulate the results within the portal
Translation of Findings
The experience with this project corroborates the team’s hypothesis surrounding the viability and value of a distributed research network. While partners still have concerns about data protection, they are unanimous in their support of the underlying architecture and access control features. The distributed model of data querying allows a high degree of automation and efficiency while still including the necessary protections that satisfy data partners, data partners’ IT security requirements, and IRB representatives.
Future versions of the DRN tool should build on the model of this version while considering more robust querying options for investigators. As sites become more comfortable with the system and its protections, the DRN can expand to include more detailed and varied data sets, including patient-level data sets. There is tremendous value in providing analysis tools for patient-level data sets that would not require data to leave its parent organization. Various public and private organizations may have interest in using these kinds of tools for multi-site studies. Building upon and expanding the network will entail a number of activities, including speaking engagements at conferences, developing a secure and professional DRN website with project information, and engaging known research consortia in discussions on how they might benefit from the system.
References
1. Maro JC, Platt R, Holmes JH, et al. Design of a national distributed health data network. Ann Intern Med 2009;151(5):341-4.
2. Brown JS, Holmes JH, Shah K, et al. Distributed Health Data Networks. A Practical Preferred Approach to Multi-Institutional Evaluations of Comparative Effectiveness, Safety, and Quality of Care. Med Care 2010;48(6):S45-S51.
*Developing a Distributed Research Network and Cooperative to Conduct Population-based Studies and Safety Surveillance 290-2005-0033I)
Appendix A.
Table A-1. System components
Component | Description |
---|---|
Access Control Manager | Manages all aspects of security for Portal including authentication, session management, policies, group permissions, user permissions, and access rights. |
Query Manager | Manages query entry, routing and distribution. |
Results Manager | Manages receipt, organization, assembly, merging and aggregation of result sets. |
Workflow Manager | Manages workflow (e.g., for query approval) including request routing, alerting and notification, approval management and tracking. |
User Manager | Manages user accounts. |
Audit Manager | Provides auditing functions including activity and error logging. |
Investigator User Interface | User interface for Investigators, including menu driven and ad hoc query entry, query management, result status, and result set management. |
Data Mart Administrator User Interface | User interface for data mart administrators including data mart setup and configuration, access control management, and workflow management. |
System Administrator User Interface | User interface for system administrators including portal setup and configuration, access control management, and user management. |
DRN Portal API | Application Programming Interface (e.g., web service API) for the Portal; exposes Portal functions for remote applications including query retrieval and results submission. |
Portal Database | Database for the Portal. |
Message Protocol | Protocol for messaging between the Portal and external applications including the Data Marts. |
Data Mart Security Manager | Manages all aspects of security for Data Mart including authentication, session management, policies, group permissions, user permissions, and access rights. |
Query Execution Manager | Manages execution of queries including queue management, query translation, query engine interface, and results handling. |
Data Mart API | Application Programming Interface (e.g., web service API) for the Data Mart. Exposes DRN functions for remote applications including query submission and results retrieval. |
Data Mart Audit Manager | Provides auditing functions including activity and error logging. |
Data Source Manager | Manages exchange of data between the Data Mart database and source systems. |
Data Mart Database | Database for the Data Mart. |
Table A-2. Languages and tools
Component(s) | Tools/Languages |
---|---|
Portal | Microsoft Visual Studio .Net 2005 |
Data Mart Client | Microsoft.Net Framework 2.0 C# |
Portal Database | Microsoft SQL Server 2005 |
Data Mart databases | Microsoft Access (can be any ODBC-accessible data source) |
Table A-3. Targets
Target | Description |
---|---|
Data Mart | Instance of a remote database, typically hosted by a data partner. Granting permission to a data mart allows an entity to submit queries to that data mart. Permissions to administer a data mart and respond to queries submitted to a data mart are covered under Rights. |
Rights | Rights represent capabilities or functions available to users of the system – such as, "log in", or "submit query". Granting a right allows the entity to utilize a particular function (e.g., "administer user profile") or utilize a particular function in a particular way (e.g., "administer user profile for another user in the same organization"). |
Query Type | Granting permission to a Query Type allows the entity to submit queries of that type. |
Table A-4. Event types
Event Type | Event Source | Description |
---|---|---|
New Query | Query Manager | Occurs when a new query is created, typically through the Query Entry screen of the Investigator User Interface in the Portal. |
Query Status Change | Query Manager | Occurs when a query’s status changes. Query status changes can happen automatically, as when the Portal background service changes the query status. Query status changes can happen as a result of a user action, such as when query status is set to Awaiting Approval when a user clicks "Hold" in the Data Mart Client or as a result of the Data Mart administrator clicking "Reject" in the Data Mart client. |
Query Reminder | Query Manager (via Portal Background Service) | Occurs when no response is received from a data mart within n days of query submission (where n is a configurable system wide parameter). In addition, this notification also occurs when a data mart has not responded within n days of the last reminder sent to this data mart. |
New Results | Results Manager | Occurs when query results are returned. Query results can be returned automatically, as with server-based data marts, or as a result of a user action, such as when a user clicks Upload Results from the Data Mart Client. |
Submitter Reminder | Query Manager (via Portal Background Service) | Occurs when no response is received from one or more data marts within n days of a query submission (where n is a configurable user selected frequency parameter). The submitter will receive one notification per query submitted, which occurs n days after the last submitter reminder was sent for a query when one or more data marts have not responded. |
Table A-5. Database attributes
Attribute | Description |
---|---|
Available Periods | Text string containing a description of the available periods, e.g., "Annual and quarterly data from 2001 to present." |
Contact First Name | First name of key contact, e.g., "Jeff" |
Contact Last Name | Last name of key contact, e.g., "Smith" |
Contact Phone | Phone number of key contact, e.g., "(617) 555-1212" |
Contact Email | Email address of key contact |
Special Requirements | Text box containing a description of any special requirements, e.g., "All procedure queries will be subject to IRB review, typically requiring 3-5 business days for approval." |
Usage Restrictions | Text box containing a description of any usage restrictions, e.g., "Data not available for export outside of the United States." |
Health Plan Description | Text box describing the health plan, such as population size, geographic region, etc. |
Organization ID | Organization with which Data Mart is associated. |
Table A-6. Audit information types
Type | Description |
---|---|
Transaction and Event Logging | Startup and shutdown of Data Mart Client State changes (Start/Stop) of Data Mart Client Background Service Login and Logout through Data Mart Client |
Query Receipt and Execution | New query found by Data Mart Client Background Service Query executions Query rejections Queries placed on hold Upload of results |
Errors and Exceptions | Unexpected errors and unhandled exceptions. |
Appendix B. Technical Documentation: Requirements and Design Specification
Enhanced Functionality for the Distributed Research Network Pilot
TECHNICAL DOCUMENTATION
Requirements and Design Specification
DRN 1.5
Requirements and Design Specification
Revision 1.5
April 26, 2010
Document History
Revision | Author | Date | Description |
---|---|---|---|
0.1 | Kiran Shah | 11/19/2009 | Internal draft |
0.2 | Kiran Shah | 11/20/2009 | Internal draft |
0.3 | Kiran Shah | 11/29/2009 | Developed sections 3.2, 4.1, and 4.2. Incorporated preliminary feedback from Jeff Brown. |
1.0 | Kiran Shah | 12/4/2009 | Requirements/Design Milestone submission.
Incorporated feedback from LP/HP working session on 12/2. Developed section 3.1. Added multiple Notification Email Added HealthPlanDescription attribute |
1.1 | Kiran Shah | 12/17/2009 | Incorporated feedback from Harvard Pilgrim:
Changed "Owner" to "Organization" and updated "Notification" details Added "OrganizationId" to DataMarts Added clarifying language |
1.2 | Kiran Shah | 12/28/2009 | Minor edits to make several rights in-scope for DRN 1.5.
Added schema diagram and additional tables. Added "Supported Queries" and "Permissions". Added Required field designations in Table 35. Added explanatory verbiage in various sections. |
1.3 | Kiran Shah | 12/30/2009 | Made "Administer user profile for any user" right in scope for DRN 1.5.
Changed "Outpatient Procedure Codes" to "ICD-9 Procedure Codes" throughout. Updated intro verbiage to reflect use of the existing query type in several sections. Added "Setting" parameter to Table 39. Added Setting parameter. Revised "Procedure Code" parameter and added "Setting" parameter to Table 40. Modified SQL code and added Setting parameter. Updated intro verbiage to reflect use of the existing query type. Added ification Email Templates section. |
1.4 | Kiran Shah | Changed verbiage throughout section 4. | |
1.5 | Srinivasan Karunanidhi | 22/02/2010 | Removed excessive Rights (Administer Users, Administer Organization, Administer Data Marts and Administer Groups) from section 3.1.5
Added New Right (Administer Roles) to section 3.1.5 Added New Method signatures (AddRightToRole and RemoveRightToRole) to support Role based access at the user level in section 3.1.7. Added new validation rules to section 3.1.9.1 Updated tables 11,13,14,15,36,37 Added tables 24,25,29,30,39 Added note to section 3.1.10.3 Added new requirement to filter Data Marts based on query types to section 3.1.11 Added verbiage for new reminder events/table Event Detail Query Reminder and Event Detail Submitter Reminder. Updated all the email template in section 3.2.14 Added new section 3.2.14.5 (email template for Submitter email reminders). Updated verbiage and added note to section 3.3.2 Updated SQL queries in section 3.4.2.2, 3.4.3.2, 3.4.4.2 Added and Appendix to the document with three separate sections to describe changes & enhancements made as follow: Appendix A Role-Based Rights; Appendix B – Notification Enhancements/Query Submitter Reminder; and Appendix C – View Data Marts’ meta data. Updated all figures to reflect the changes added to this version. |
Appendixes 1 - 3
1. Introduction
1.1. Background
Harvard Pilgrim Health Care (HPHC) previously engaged Lincoln Peak to develop a prototype of a distributed health information network—a distributed research network (DRN)—to conduct population-based studies of the risks and benefits of therapeutics.
In Phase 1, the prototype demonstrated how participating members can issue queries through a network portal (hub) that are then distributed across network nodes and executed locally. Query results are returned in aggregate form, and collated into unified result sets. The prototype also demonstrates how authentication and access control could be provided by the network. The prototype demonstrates the following functions:
- Assembly of a simple query using a menu driven user interface
- Issuance of the query
- Distribution of the query to multiple network servers
- Local execution of the query against a test dataset
- Return of the results to the portal
- Aggregation of the results and presentation as a single results set
As an extension to Phase 1, Lincoln Peak extended the prototype to beyond test datasets to include information representing actual patient care. Portal functionality was extended to support a "publish and subscribe" query fulfillment model. Query selection, parameter specification, data mart selection, query status, query detail, and result viewing remained unchanged. However, instead of having queries dispatched to "live" servers for automated execution against test datasets, queries are queued for later semi-automated processing by network partners using their desktop or laptop computers. An email-based notification mechanism was added to the portal to alert data partners when new queries have been submitted. A lightweight desktop application was developed for use by partners to fulfill queries. To participate in the prototype network, data partners do a one-time download and installation of this lightweight desktop application. No servers, no firewalls, and special network setups are required. Once the desktop application is installed, the partners can fulfill queries wherever they are, with only their desktop/laptop computer and an internet connection. Partners are in complete control of which queries get answered, and what results get uploaded to the portal.
This next phase (DRN 1.5) will focus on building out additional areas of the architecture to improve usability and drive acceptance of the system by participating and prospective partners.
1.2. Document Scope
This document describes the functional requirements and design for DRN phase 1.5. The document is arranged into the following sections:
Section 2 describes the high level system architecture originally envisioned for DRN, the organization of the code, and the languages and tools used.
Section 3 describes the DRN Portal functions being developed in this phase, including Access Control, the Notification functions of the Workflow Manager, the Data Mart Admin User Interface, additions and changes to the Investigator User Interface, and addition of Data Mart Metadata.
Section 4 describes the DRN Data Mart Client functions being developed in this phase, including the Workflow Manager for providing unattended operation, and the Audit Manager.
2. System Architecture
2.1. Existing System Architecture
Requirements and design specifications described in this document refer to components contained within the DRN System Architecture as illustrated below.
Figure 1. System architecture
The major architectural components for the DRN Network are the Portal, and the Data Marts.
The Portal is hosted on a centrally accessible server, and provides all of the centralized services. Investigators log in to the Portal using a web-based user interface, and submit menu-driven queries which are stored in the Portal Database. After queries are executed (by the data marts), results are returned to the Portal, where they are aggregated and made available to the query submitter.
The Data Mart components run locally, in close proximity to each data source. Queries submitted to the Portal are sent to each data mart, which executes the queries against the local data source. Results are then returned to the Portal. Two different types of data marts allow for flexibility in the deployment model. The Server Based Data Mart is hosted on a server, and exposes a web services interface utilized by the Portal (i.e., in a "push" type model) to send and execute queries and retrieve results. The Client Based Data Mart is a lightweight desktop application, intended to run on the Data Mart administrator’s desktop computer. The Client Based Data Mart uses a web services interface exposed by the Portal (i.e. in a "pull" type model) to download and execute queries and upload results.
The components of the DRN System Architecture are described in the table below.
Table 1. DRN System architecture components
Component | Description |
---|---|
Access Control Manager | Manages all aspects of security for DRN Portal including authentication, session management, policies, group permissions, user permissions, and access rights. |
Query Manager | Manages query entry, routing and distribution. |
Results Manager | Manages receipt, organization, assembly, merging and aggregation of result sets. |
Workflow Manager | Manages workflow (e.g., for query approval) including request routing, alerting and notification, approval management and tracking. |
User Manager | Manages user accounts. |
Audit Manager | Provides auditing functions including activity and error logging. |
Investigator User Interface | User interface for Research users, including menu driven and ad hoc query entry, query management, result status, and result set management. |
Data Mart Admin User Interface | User interface for data mart administrators including data mart setup and configuration, access control management, and workflow management. |
System Admin User Interface | User interface for system administrators including portal setup and configuration, access control management, and user management. |
DRN Portal API | Application Programming Interface (e.g., web service API) for the DRN Portal. Exposes Portal functions for remote applications including query retrieval and results submission. |
Portal Database | Database for the DRN Portal. |
Message Protocol | Protocol for messaging between the DRN Portal and external applications including the data marts. |
Data Mart Security Manager | Manages all aspects of security for data mart including authentication, session management, policies, group permissions, user permissions, and access rights. |
Query Execution Manager | Manages execution of queries including queue management, query translation, query engine interface, and results handling. |
Data Mart API | Application Programming Interface (e.g., web service API) for the data mart. Exposes DRN functions for remote applications including query submission and results retrieval. |
Data Mart Audit Manager | Provides auditing functions including activity and error logging. |
Data Source Manager | Manages exchange of data between the data mart database and source systems. |
Data Mart Database | Database for the data mart. |
2.2. Code Organization
The code for the components of the System Architecture is organized into "projects" as follows:
Table 2. DRN system components project organization
Component(s) | Project Name | Description |
---|---|---|
Portal Database | PortalDatabase | SQL database project for the Portal database. |
Investigator User Interface Data Mart Admin User Interface System Administrator User Interface |
PortalWeb | ASP.NET web application for the Portal application. |
Access Control Manager Query Manager Results Manager Workflow Manager User Manager Audit Manager |
DRNLib | Shared library for Portal components. |
Data Mart Client Security Manager Data Mart Client Query Execution Manager Data Mart Client Workflow Manager Data Mart Client User Interface Data Mart Client Audit Manager Data Mart Client Data Source Manager DRN Portal API |
DataMartClient | Windows forms application for Client Based Data Mart. |
Data Mart Security Manager Query Execution Manager Data Mart API Data Mart Audit Manager Data Source Manager |
DataMartWeb | ASP.NET web service for Server Based Data Mart. |
2.3. Languages and Tools
The table below summarizes the languages and tools used in DRN:
Table 3. Languages and tools
Component(s) | Tools/Languages |
---|---|
DRN Portal DRN Data Mart Client |
Microsoft Visual Studio .Net 2005 Microsoft .Net Framework 2.0 C# |
DRN Portal Database | Microsoft SQL Server 2005 |
Data Mart databases | Microsoft Access (can be any ODBC-accessible data source) |
3. DRN Portal Functions
This section specifies requirements and design for changes to the DRN Portal.
3.1. Access Control Manager/Data Entitlements
Overview of Requirement
The existing system contains "demo" functionality which is suggestive of potential access control features. The "demo" functionality will be replaced by a true access control function. An access control architecture will be designed and a base access control framework will be constructed. The initial implementation will include data entitlement functions enabling system administrators and data mart administrators to restrict access to data marts and query types (e.g., ICD-9 diagnosis codes or generic names), based on individual users or entire organizations.
Access Control Concepts
Entity—refers to the recipient of a permission, such as a user or an organization. Entities are organized into a hierarchy, with permissions granted to "parent" entities (e.g., an organization) are "inherited" by "child" entities (e.g., a user).
Permission—refers to an allowance granted or restricted by access control. Permissions are associated with a target and granted to an entity.
Target—refers to the object of a permission. Can be a right (e.g., "submit query"), a resource (e.g., a data mart), or data (e.g., query type).
Access Control Entities
The diagram below illustrates the entity hierarchy for access control:
Figure 2. Access control entity hierarchy
Table 4. Access control entities
Entity | Description |
---|---|
Group | A group of related organizations (e.g., Kaiser Permanente, HMO Research Network) |
Organization | A company or institution (e.g., Kaiser Permanente Colorado, or AHRQ). Organizations belong may belong to any number of groups (or no groups). |
User | An individual person. Users belong to one organization. |
Note that the User entity already exists in the DRN Portal database. Group and Organization are new entities.
Permissions are inherited by entities through parent/child relationships. An Organization inherits all permissions granted to all Groups to which it belongs, and a User inherits all permissions granted to its parent Organization. Thus a User has the union of all permissions granted to the User, the parent Organization, and all parent Groups.
Access Control Targets
The table below summarizes the access control targets to be included in DRN 1.5.
Table 5. Access control targets
Target | Description |
---|---|
DataMart | Instance of a remote database, typically hosted by a partner. Granting permission to a data mart allows the target (user, organization, or group) to submit queries to that data mart. Permissions to administer a data mart and respond to queries submitted to a data mart are covered under Rights. |
Rights | Rights represent capabilities or functions available to users of the system – such as "log in", or "submit query". Granting a right allows the target (user, organization, or group) to utilize a particular function (e.g., "administer user profile") or utilize a particular function in a particular way (e.g., "administer user profile for another user in the same organization"). |
QueryType | Granting permission to a Query Type allows the target (user, organization, or group) to submit queries of that type. |
Rights
The list below specifies the rights which can be granted in DRN 1.5.
Items listed in italics will not be included in DRN 1.5 but are contemplated for future phases.
- Portal Functions
- Access
- Log in to portal (Login)
- Queries
- Submit query
- View list of queries submitted by self
- View list of queries submitted by any user in same organization
- View list of queries submitted by any user in same group
- View list of queries submitted by any user
- Results
- View results summary for queries submitted by self
- View results summary for queries submitted by any user in same organization
- View results summary for queries submitted by any user in same group
- View results summary for queries submitted by any user
- View results detail for queries submitted by self
- View results detail for queries submitted by any user in same organization
- View results detail for queries submitted by any user in same group
- View results detail for queries submitted by any user
- Administration
- Administer user profile for any user in same organization
- Administer user profile for any user in same group
- Administer user profile for any user
- Create user in same organization
- Create user in same group
- Create any user
- Grant organization-level permissions
- Grant group-level permissions
- Grant system wide permissions
- Administer Data Mart for same organization
- Administer any Data Mart
- Administer Roles
- Access
- Data Mart Client Functions
- Access
- Log in to portal through Data Mart Client
- Queries
- View list of queries submitted to data mart
- View query detail
- Hold query
- Reject query
- Run query
- Results
- Upload results
- Access
Access Control Data Model
The diagrams below illustrate the data model for Access Control—for DataMarts, QueryTypes, and Rights. In each case, the permissions are stored in a many-to-many "join table" which joins the access control target (DataMart, QueryType, or Right) to the entity being granted permission (user, organization, or group). The presence of a record in the join table represents a granted permission, while absence of a record indicates that the permission has not been granted.
Access Control Data Model for Users, Groups and Organizations
The diagram below illustrates the data model for access control entities—users, groups, and organizations. Note that the Users entity already exists in the DRN Portal database, however Groups and Organizations are new in DRN 1.5:
Figure 3. Access control data model for organizations and groups
Access Control Data Model for DataMarts
The diagram below illustrates the data model for defining permissions between entities and DataMarts.
Figure 4. Access control data model for DataMarts
Access Control Data Model for QueryTypes
The diagram below illustrates the data model for defining permissions between entities and QueryTypes.
Figure 5. Access control data model for QueryTypes
Access Control Data Model for Rights
The diagram below illustrates the data model for defining permissions between entities and Rights.
Figure 6. Access control data model for rights
Access Control Manager Design
The Access Control Manager implements methods for granting and checking permissions, and includes the methods described below.
Methods for Granting Permissions and Rights
void GrantUserDataMartPermission(int UserId, int DataMartId)
Grants specified User permission to use specified DataMart.
void GrantOrganizationDataMartPermission(int OrganizationId, int DataMartId)
Grants specified Organization permission to use specified DataMart.
void GrantGroupDataMartPermission(int GroupId, int DataMartId)
Grants specified Group permission to use specified DataMart.
void GrantUserQueryTypePermission(int UserId, int QueryTypeId)
Grants specified User permission to use specified QueryType.
void GrantOrganizationQueryTypePermission(int OrganizationId, int QueryTypeId)
Grants specified Organization permission to use specified QueryType.
void GrantGroupQueryTypePermission(int GroupId, int QueryTypeId)
Grants specified Group permission to use specified QueryType.
void AddRightToRole (int RoleTypeId, int RightId)
Grants specified Right to specified User.
void GrantOrganizationRight (int OrganizationId, int RightId)
Grants specified Right to specified Organization.
void GrantGroupRight (int GroupId, int RightId)
Grants specified Right to specified Group.
Methods for Revoking Permissions and Rights
void RevokeUserDataMartPermission(int UserId, int DataMartId)
Revokes specified User permission to use specified DataMart.
void RevokeOrganizationDataMartPermission(int OrganizationId, int DataMartId)
Revokes specified Organization permission to use specified DataMart.
void RevokeGroupDataMartPermission(int GroupId, int DataMartId)
Revokes specified Group permission to use specified DataMart.
void RevokeUserQueryTypePermission(int UserId, int QueryTypeId)
Revokes specified User permission to use specified QueryType.
void RevokeOrganizationQueryTypePermission(int OrganizationId, int QueryTypeId)
Revokes specified Organization permission to use specified QueryType.
void RevokeGroupQueryTypePermission(int GroupId, int QueryTypeId)
Revokes specified Group permission to use specified QueryType.
void RemoveRightFromRole (int RoleTypeId, int RightId)
Revokes specified Right from a role (Each user shall be assigned with a role).
void RevokeOrganizationRight (int OrganizationId, int RightId)
Revokes specified Right from specified Organization.
void RevokeGroupRight (int GroupId, int RightId)
Revokes specified Right from specified Group.
Methods for Checking and Listing Permissions
bool CheckUserRight(int UserId, int RightId)
Returns true if specified user has specified Right by virtue of user having user-level assignment of the Right, or inheriting organization-level or group-level assignment of the Right.
int[] GetUserDataMartPermissions(int UserId, bool IncludeInherited)
Obtains the list of DataMarts to which specified user has access by virtue of user having user-level permission or (if IncludeInherited is true) inheriting organization-level or group-level permission to the DataMart.
int[] GetUserRights(int UserId, bool IncludeInherited)
Obtains the list of Rights granted to specified user by virtue of user having user-level assignment of the Right or (if IncludeInherited is true) inheriting organization-level or group-level assignment of the Right.
int[] GetUserQueryTypePermissions(int UserId, bool IncludeInherited)
Obtains the list of QueryTypes to which specified user has access by virtue of user having user-level permission or (if IncludeInherited is true) inheriting organization-level or group-level permission to the QueryType.
int[] GetOrganizationDataMartPermissions(int OrganizationId)
Obtains the list of DataMarts to which specified organization has permission.
int[] GetOrganizationRights(int OrganizationId)
Obtains the list of Rights assigned to specified organization.
int[] GetOrganizationQueryTypePermissions(int OrganizationId)
Obtains the list of QueryTypes to which specified organization has permission.
int[] GetGroupDataMartPermissions(int GroupId)
Obtains the list of DataMarts to which specified group has permission.
int[] GetGroupRights(int GroupId)
Obtains the list of Rights assigned to specified group.
int[] GetGroupQueryTypePermissions(int GroupId)
Obtains the list of QueryTypes to which specified group has permission.
Data Mart Client Security Manager Design
The Data Mart Client Security Manager will implement access control functions for the Data Mart Client.
For DRN 1.5, the Data Mart Client does not require direct access to the access control functions. Access control will be implemented in the portal, and within the DRN Hub API.
Administration for Users, Organizations, and Groups
User administration functions were added to DRN in a previous phase. In DRN 1.5, the user administration is expanded to include the user/organization relationship, and adds administrative functions for organizations and groups.
New administrative functions will be added to the "Administration" menu of the main navigation bar, as follows:
Figure 7. Administration menu
Access to the Administration functions for Groups, Organizations, Users, and Rights is governed by Rights. Only users with sufficient permissions are allowed access to these functions.
User Administration
The existing User Administration form will be changed to include a control for selecting Organization as follows:
Figure 8. User administration form
The new controls on the User Admin form are as follows:
Table 6. User administration form controls
Control | Description |
---|---|
Organization select | Presents a list of Organizations from the Organization table. |
Rights button | Presents the Rights Selection form as described in section 0 on page 24. |
Role select | Presents a list of Roles from the RoleTypes table. |
Email entry | Presents a textbox to Add/Edit email address. |
Delete button | Presents the admin user with an option to delete the selected user. |
The following validation rules will be implemented:
- Valid organization must be selected. All users must be associated with an organization.
- Valid email must be entered.
- Valid role must be selected. All users must be associated with a role.
To facilitate identification of Users associated with an Organization, the following changes will be made to the User List page:
Figure 9. User list form
- An Organization column will be added to the User list, displaying the name of the Organization with which the user is associated.
- An Organizations filter will be added. This will be a drop-down list of Organizations. Selecting an Organization will filter the list of users to those associated with the selected Organization. A special list item labeled "All" will be available as the first item in the pick list, and will cause users from all organizations to be displayed.
Organization Administration
The Organization Administration will include functions to create, edit, and delete organizations, and will consist of two forms—the Organization List form, and the Organization Detail form.
The Organization List form will present a list of organizations, as follows:
Figure 10. Organization list form
Organization names will be presented as hyperlinks. Clicking on the organization name will bring up the Organization Detail screen for the selected organization.
The New Organization button will bring up the Organization Detail screen for creating a new organization.
The Organization Detail form presents the attributes of the organization, as follows:
Figure 11. Organization detail form
The fields on the Organization Detail form are as follows:
Table 7. Organization detail form fields
Field | Database Table/Column |
---|---|
Organization name | Organization.OrganizationName |
The controls on the Organization Detail form are as follows:
Table 8. Organization detail form controls
Control | Function |
---|---|
Delete button | Presents a confirmation dialog, as in:
If the user clicks OK, the system flags the organization as deleted by setting the Organization.isDeleted flag = 1. |
Save button | Performs validation checks and saves entries made on the form.
Validation checks are as follows:
For new Organizations, inserts a new record in the Organizations table. For existing Organization records, updates the existing record in the Organizations table. |
Cancel button | Returns to the Organization List screen. |
Rights button | Presents the Rights Selection form. |
Group Administration
The Group Administration will include functions to create, edit, and delete groups, and will consist of two forms—the Group List form, and the Group Detail form.
The Group List form will present a list of groups, as follows:
Figure 12. Group list form
This screen shot depicts the user interface used to administer groups.
Group names will be presented as hyperlinks. Clicking on the group name will bring up the Group Detail screen for the selected group.
The New Group button will bring up the Group Detail screen for creating a new group.
The Group Detail form presents the details of the group, as follows:
Figure 13. Group detail form
This screen shot depicts the user interface used to add organizations to a group.
The fields on the Group Detail form are as follows:
Table 9. Group detail form fields
Field | Database Table/Column |
---|---|
Group name |
Group.GroupName |
Not In Group |
Initially displays Organizations. OrganizationName for all records found in the Organizations table (where isDeleted=0) but not found in the OrganizationsGroups table for the group being modified. This list contains all Organizations (where isDeleted=0) when a new group is being added. |
In Group |
Initially displays Organizations. OrganizationName for all records found in the OrganizationsGroups table for the group being modified, where Organizations.isDeleted=0. This list is initially empty when a new group is being added. |
The controls on the Group Detail form are as follows:
Table 10. Group detail form controls
Control | Function |
---|---|
Add button |
Moves the record selected in the “Not In Group” list into the “In Group” Members list. In other words, adds an organization to this group. |
Remove button |
Moves the record selected in the “In Group” Members list into the “Not In Group” list. In other words, removes an organization from this group. |
Delete button |
Presents a confirmation dialog, as in: If the user clicks OK, the system flags the group as deleted by setting the Group.isDeleted flag = 1. |
Save button |
Performs validation checks and saves entries made on the form. Validation checks are as follows:
For new Groups, inserts a new record in the Groups table. For existing Group records, updates the existing record in the Groups table. Also updates the OrganizationsGroups table by adding any new associations and removing any that should no longer exist. |
Cancel button |
Returns to the Group List screen. |
Rights button |
Presents the Rights Selection form. |
Access Control Administration
All Access Control (including Access Control settings for the Data Mart Client) is administered through the Portal. Access Control administration functions are exposed in various places, including administrative functions for both access control entities (users, organizations, and groups) and targets (data marts, rights, and query types).
Administering Data Mart Permissions
Data Mart permissions relate to which entities (users, organizations, and groups) are granted access to a data mart (for submitting queries). Data Mart permissions are administered through the Data Mart Admin User Interface.
Administering Rights—Rights Admin
Administration of Rights occurs through a new Rights Admin function, reachable through the Administration > Rights menu item in the main navigation bar.
Clicking on the Rights menu item presents the Rights List Form as follows:
Figure 14. Rights list form
This screen shot depicts the complete list of system rights.
Each Right Code is presented on a hyperlink. Clicking on the hyperlink leads to the Rights Detail form, as follows:
Figure 15. Rights detail form
This screen shot depicts the user interface used to give individual entities a particular right (the right to log into the system).
The fields on the Rights Detail form are as follows:
Table 11. Rights detail form fields
Field | Database Table/Column |
---|---|
Right Code |
Rights.RightCode |
Description |
Rights.Description |
Groups |
Initially displays list of Groups.GroupName for groups found in GroupsRights for the current Right, where Groups.isDeleted=0. |
Organizations |
Initially displays the list of Organizations.OrganizationName for organizations found in OrganizationsRights for the current Right, where Organizations.isDeleted=0. |
Users |
Displays the list of Users.UserName for those users whose role has been granted the current right, where Users.isDeleted=0. |
The controls on the Rights Detail form are as follows:
Table 12. Rights detail form controls
Control | Function |
---|---|
Groups – Add button |
Opens the Group Selection popup window and adds the user’s selections to the Groups list. |
Groups – Remove button |
Removes the row selected in the Groups list. |
Organizations – Add button |
Opens the Organization Selection popup window and adds the user’s selections to the Organizations list. |
Organizations – Remove button |
Removes the row selected in the Organizations list. |
Save |
Saves all changes made on the Rights Detail form and returns to the Rights List. Updates the GroupsRights and OrganizationsRights table adding records to and removing records from each as needed, according to any changes made. |
Cancel |
Returns to the Rights List form without saving changes. |
Administering Rights—Rights Selection
In addition to providing a “Rights-centric” mode of Rights administration, the system also allows Rights to be administered within the context of administering individual entities—i.e. groups, organizations, and users. In other words, while administering a group, organization, or user, clicking on the Rights button from the detail form brings up the Rights Selection dialog as shown below:
Note: The “Rights” section was overhauled to provide a role-based rights and access at the user level as requested by the client. Please refer to Appendix “A” for the approved Rights proposal that details the changes implemented.
Figure 16. Rights selection form (approved changes)
This screen shot depicts the user interface used to give rights to an individual user.
The controls on the Rights Selection form are as follows:
Table 13. Rights selection form controls
Control | Function |
---|---|
Cancel button |
Close the form opened as popup. |
Administering Query Type Permissions
Query Type permissions relate to which entities (users, organizations, and groups) are granted access to each Query Type.
Administration of Query Type permissions occurs through a new Query Type Permissions Admin function, reachable through the Administration > Query Permissions menu item in the main navigation bar.
Clicking on the Query Type Permissions menu item presents the Query Type Permissions List Form as follows:
Figure 17. Query type permissions list form
This screen shot depicts the user interface used to set access controls by query type.
Each Query Type is presented as a hyperlink. Clicking on the hyperlink leads to the Query Type Permissions Detail form, as follows:
Figure 18. Query type permissions detail form
This screen shot depicts the user interface used to allow access to individual entities for a particular query type (pharmacy dispensings by drug class).
The fields on the Query Type Permissions Detail form are as follows:
Table 14. Query types permissions detail form fields
Field | Database Table/Column |
---|---|
Query Type |
QueryTypes.QueryType |
Description |
QueryTypes.Description |
Groups |
Initially displays list of Groups.GroupName for groups found in GroupsQueryTypes for the current QueryType, where Groups.isDeleted=0. |
Organizations |
Initially displays the list of Organizations.OrganizationName for organizations found in OrganizationsQueryTypes for the current QueryType, where Organizations.isDeleted=0. |
Users |
Initially displays the list of Users.UserName for users found in UsersQueryTypes table for the current QueryType, where Users.isDeleted=0. |
The controls on the Query Types Permissions Detail form are as follows:
Table 15. Query types permissions form controls
Control | Function |
---|---|
Groups – Add button |
Opens the Group Selection popup window and adds the user’s selections to the Groups list. |
Groups – Remove button |
Removes the row selected in the Groups list. |
Organizations – Add button |
Opens the Organization Selection popup window and adds the user’s selections to the Organizations list. |
Organizations – Remove button |
Removes the row selected in the Organizations list. |
Users – Add button |
Opens the User Selection popup window and adds the user’s selections to the Users list. |
Users – Remove button |
Removes the row selected in the Users list. |
Save |
Saves all changes made on the Query Types Permissions Detail form and returns to the Query Types Permissions List. Updates the GroupsQueryTypes, OrganizationsQueryTypes, and UsersQueryTypes tables, adding records to and removing records from each as needed, according to any changes made. |
Cancel |
Returns to the Query Types Permissions List form without saving changes. |
Access Control Changes in Query Entry
The Query Entry form in the DRN Portal application will be modified to include Access Control. The following changes will be implemented:
- Query Type Filtering—the Query Type pick list will be filtered to display only the query types for which the current user has permission. If the user does not have permissions to at least one query type, the following message will be displayed:
You have not been granted permission to submit any of the available query types. Please contact the System Administrator for assistance. - Data Mart Filtering—the Data Mart pick list will be filtered to display on the data marts to which the current user has permission. If the user does not have permissions to at least one data mart, the following message will be displayed:
You have not been granted permission to submit queries to any of the available data marts. Please contact the System Administrator for assistance. - Data Mart Filtering based on supported Query types—the Data Mart pick list will be filtered to display only the data marts that support the selected Query type from the Query type pick list.
3.2 Workflow Manager/Notification Functions
Overview of Requirement
A basic notification function was added in the previous phase, to alert data mart administrators when queries are entered against a data mart. This functionality will be extended to include a notification framework which will centralize notification management and provide end-user control over delivery of notifications. A query status notification feature will be added, to alert query submitters when results become available.
Notification Concepts
Event—refers to an occurrence of a particular transaction within the system, for example the submission of a new query.
Notification—refers to a message describing the occurrence of an event, for example an email message regarding submission of a new query. Multiple notifications may be generated for a single event.
Workflow Manager Design
The Workflow Manager implements functions to manage workflow including request routing, alerting and notification, approval management, and tracking. Alerting and notification functions are exposed through an Event Interface and a Notification Interface.
Figure 19. Workflow manager design
The Event Interface exposes functions for event generation, and used by other system components such as the Query Manager and Results Manager to generate events when they occur.
The Notification Interface exposes functions for event notification, including functions to set notification options and retrieve notifications, used by other system components such as the DRN Hub API and the Investigator User Interface.
Event Generation
Events are generated by various components and subsystems in DRN, typically at the time the event occurs—e.g., when a new query is entered, a New Query event is generated by the Query Manager.
When an event is generated, the system records the event and notifications in the database. The notification entries are recorded for processing only when the user has opted for such events on DRN portal (“Settings > Notification Options” page). Generation and delivery of notifications happens subsequently, as part of background processing
Event Data Model
The diagram below illustrates the data model for Event data.
Figure 20. Event data model
The Events table is the master/parent table for recording event data and contains the following attributes:
Table 16. Events attributes
Attribute | Data Type | Description |
---|---|---|
EventId |
Int |
Identity |
EventSourceId |
Int |
Event Source, referring to the module which generated the event. |
EventTypeId |
Int |
Event Type, specifying the type of event which occurred. |
EventDateTime |
Datetime |
Date and time the event occurred |
The EventSources and EventTypes tables are lookup tables for Event Source and Event Type, respectively. Attributes are as follows:
Table 17. EventSources attributes
Attribute | Data Type | Description |
---|---|---|
EventSourceId |
Int |
Identity |
EventSource |
Varchar(50) |
Event source description |
Table 18. EventTypes attributes
Attribute | Data Type | Description |
---|---|---|
EventTypeId |
Int |
Identity |
EventType |
Varchar(50) |
Event type description |
The EventDetailNewQuery, EventDetailQueryStatusChange, EventDetailNewResult, EventDetailQueryReminder and EventDetailSubmitterReminder tables are detail/ child tables, used to store event details specific to each Event Type. For example, for a New Query event, there is one record in the Events table and a corresponding record in the EventDetailNewQuery table.
Table 19. EventDetailNewQuery attributes
Attribute | Data Type | Description |
---|---|---|
EventId |
Int |
Event to which this detail pertains |
QueryId |
Int |
Query id of new query |
Table 20. EventDetailStatusChange attributes
Attribute | Data Type | Description |
---|---|---|
EventId |
Int |
Event to which this detail pertains |
QueryId |
Varchar(50) |
Query id of query with status change |
NewQueryStatusTypeId |
Int |
New query status type |
Table 21. EventDetailNewResult attributes
Attribute | Data Type | Description |
---|---|---|
EventId |
Int |
Event to which this detail pertains |
ResultId |
Int |
Result id of new result |
Table 22. EventDetailQueryReminder attributes
Attribute | Data Type | Description |
---|---|---|
EventId |
Int |
Event to which this detail pertains |
QueryId |
Int |
QueryId associated with the notification |
DataMartId |
Int |
Unique Id for the data mart that has not responded |
Table 23. EventDetailSubmitterReminder attributes
Attribute | Data Type | Description |
---|---|---|
EventId |
Int |
Event to which this detail pertains |
QueryId |
Int |
QueryId associated with the submitter reminder notification |
Events
The table below describes the events to be included in DRN 1.5:
Table 24. Workflow manager events
Event Source | Event Type | Description |
---|---|---|
Query Manager |
New Query |
Occurs when a new query is created, typically through the Query Entry screen of the Investigator User Interface in the Portal. |
Query Manager |
Query Status Change |
Occurs when a query’s status changes. Query status changes can happen automatically, as when the Portal background service changes the query status. Query status changes can happen as a result of a user action, such as when query status is set to AwaitingApproval when a user clicks “Hold” in the Data Mart Client or as a result of the data mart administrator clicking “Reject” in the Data Mart client. |
Query Manager (via Portal Background Service) |
Query Reminder |
Occurs when no response is received from a data mart within n days of query submission (where n is a configurable systemwide parameter). In addition, this also occurs when a data mart has not responded within n days of the last reminder sent to this data mart. |
Results Manager |
New Results |
Occurs when query results are returned. Query results can be returned automatically, as with server-based data marts, or as a result of a user action, such as when a user clicks Upload Results from the Data Mart Client. |
Query Manager (via Portal Background Service) |
Submitter Reminder |
Occurs when no response is received from one or more data marts within n days of a query submission (where n is a configurable user selected frequency parameter). The submitter will receive one notification per query submitted, which occurs n days after the last submitter reminder was sent for a query when one or more data marts have not responded. Note: The user level setting shall be selected on the “Notification options” page available under “SETTINGS” menu option in the DRN portal. |
Event Interface
The Event Interface exposes methods for event generation, including the following:
int RaiseEvent(string EventSource, string EventType, object[] EventArgs)
Generates the specified event.
Generation and Delivery of Notifications
Notifications are messages describing the occurrence of an event. Notifications are generated, and then delivered.
Generation of notifications occurs during background processing. A background process identifies new events, and then generates notifications for those events.
Delivery of notifications occurs through multiple channels. E-mail notifications are delivered to end users via email. In the future, notifications may also be published through a web services API, providing a mechanism whereby remote systems can subscribe to automatically receive event notifications.
Notification Data Model
The diagram below illustrates the data model for Notification data.
Figure 21. Notifications data model
The Notifications table is the main table for recording notifications and contains the following attributes:
Table 25. Notifications attributes
Attribute | Data Type | Description |
---|---|---|
NotificationId |
Int |
Identity |
NotificationTypeId |
Int |
Type of notification |
EventId |
Int |
Event for which this notification was generated |
UserId |
Int |
Recipient of this notification |
GeneratedTime |
Datetime |
Time this notification was generated. |
DeliveredTime |
Datetime |
Time this notification was delivered. |
The NotificationTypes table is a lookup table for Notification Type and contains the following attributes:
Table 26. NotificationTypes attributes
Attribute | Description |
---|---|
NotificationTypeId |
Identity |
NotificationType |
Description of this notification type |
The NotificationOptions table stores user preferences for notification delivery. Each row is a user id / event type id / notification type id tuple, which, if present, causes notifications to be generated for the given user / event type / notification type. Attributes are as follows:
Table 27. NotificationOptions attributes
Attribute | Description |
---|---|
UserId |
User |
EventTypeId |
Event Type |
NotificationTypeId |
Notification Type |
Notification Types
In DRN 1.5, Notification Types include the following:
Table 28. Notification types
Notification Type | Description |
---|---|
|
Email message containing a description of the event |
Notification Frequencies
The Notificationfrequency table stores available frequency look up values that the user will be able to select on the “Notification options” page.
In DRN 1.5, the Notification Frequencies table includes the following:
Table 29. Notification frequencies
Attributes | Description |
---|---|
FrequencyId |
Unique Id for frequency to be selected by the user on the Notification options page |
Days |
Available frequency in days |
EventTypeId |
Unique Id for EventTypes that the frequency is associated with. |
Description |
Description pertaining to the available frequency. |
Notification Interface
The Notification Interface exposes methods for retrieving notifications and setting notification options, including the following:
Notification[] GetNotifications(int UserId, bool IncludeDelivered)
Retrieves notifications for the specified user. The IncludeDelivered flag specifies whether to include events which have been already been delivered.
Notification Options
Users can control generation of notifications by configuring Notification Options. Notification options can be configured in the Portal through the Investigator User Interface.
A new Settings menu will be added, with a menu item named Notification Options.
The Notification Options form will contain a grid of checkbox items for selecting the preferred notification Options.
The following screenshot depicts how the notification option will appear based on the latest requirement change. Appendix ‘B’ describes the latest changes made to the requirements described in this section.
Figure 22. Notification options form
Notification Email Templates
This section specifies the content for notification email messages for each event.
New Query
The New Query event occurs when a new query is created. The notification email template is as follows:
Figure 23. New query email template
This template shows the format of email notifications to DataMart administrators for new query submissions.
Parameters:
Table 30. New query email template parameters
Parameter | Description |
---|---|
{0} |
Users.UserName |
{1} |
Queries.QueryId |
{2} |
DataMarts.DataMartName |
{3} |
Queries.Name |
{4} |
Queries.CreatedAt |
{5} |
Users.Email where UserId=Queries.CreatedByUserId |
{6} |
QueryTypes.QueryType where QueryTypeId=Queries.QueryTypeId |
{7} |
Queries.QueryDescription |
{8} |
Queries.QueryText |
Query Status Change
The Query Status Change event occurs when a query’s status changes. The notification email template is as follows:
Figure 24. Query status change email template
This template shows the format of email notifications to DataMart administrators for changes in query status.
Parameters:
Table 31. Query status change email template parameters
Parameter | Description |
---|---|
{0} |
Queries.QueryName |
{1} |
Queries.QueryId |
{2} |
Datamarts.DataMartName |
{3} |
QueryStatusTypes.QueryStatusType for old status |
{4} |
QueryStatusTypes.QueryStatusType for New status |
{5} |
Users.Username |
Query Reminder
The Query Reminder event occurs when no response is received from a data mart. The notification email template is as follows:
Figure 25. Query reminder email template
This template shows the format of email notifications for DataMart administrators to remind them to respond to a query.
Parameters:
Table 32. Query reminder email template parameters
Parameter | Description |
---|---|
{0} |
Users.UserName |
{1} |
Query.QueryId |
{2} |
DataMarts.DataMartName |
{3} |
Difference in days between current date and Queries.CreatedAt |
{4} |
Queries.CreatedAt |
New Results
The New Results event occurs when query results are returned from a data mart. The notification email template is as follows:
Figure 26. New results email template
This template shows the format of email notifications to query submitters for when DataMart administrators upload query results.
Parameters:
Table 33. New results email template parameters
Parameter | Description |
---|---|
{0} |
Queries.QueryName |
{1} |
Queries.QueryId |
{2} |
DateMarts.Name |
{3} |
Users.UserName |
Submitter Reminder
The submitter reminder event occurs when one or more data marts have not responded to the submitted query in a timely fashion. The notification frequency shall be set by the user in the “Settings > Notification options” menu option made available after logging in to the DRN portal with required credentials. The notification email template for submitter reminders is as follows:
Figure 27. Submitter reminder email template
Parameters:
Table 34. Submitter reminder email template parameters
Parameter | Description |
---|---|
{0} |
Queries.QueryId |
{1} |
Queries.CreatedAt |
{2} |
Queries.QueryName |
{3} |
Users.Email |
{4} |
Queries.QueryType |
{5} |
Queries.Description |
{6} |
Queries.QueryText |
{7} |
Comma delimited data mart names who have not uploaded results |
{8} |
Comma delimited data mart names who have rejected the query |
{9} |
Comma delimited data mart names who have uploaded results |
3.3. Data Mart Administrator User Interface
Overview of Requirement
In the existing system, provisioning of data marts in the portal, i.e. making a new data mart known to the system, requires a series of manual steps performed by a technical system administrator. In this phase, a data mart provisioning feature will be added, thus enabling non-technical administrators to set up and maintain data marts in the portal. The provisioning feature will include the ability to add new data marts, edit a data mart’s configuration (e.g., whether client or server based, remote server connection configuration, Data Mart administrator’s contact information, etc.).
Data Mart Data Model Changes
Several changes will be made to the data model (schema and class designs) for representing data mart information within DRN.
The data marts table in the DRN Portal database contains a DataMartTypeId attribute, identifying the data mart as either server-based or client-based. The users responsible for client-based data marts will be identified through the organization Id available on the data mart’s table. The OrganizationId shall then be used to execute a join on the Data Mart users table to retrieve the administrator(s) for a data mart. The administrative users identified will be permitted to retrieve and respond to queries made against that data mart. The Email field on the User’s table will be used to deliver email notifications to the respective users.
In DRN 1.5, the following changes will be made:
Figure 28. Data mart data model changes
- The AdminUserId and NotificationEmail attributes will be dropped from the data mart’s table.
- A new OrganizationId attribute will be added to the data mart’s table. This will be a foreign key to the Organizations table.
- A new table DataMartsNotifications will be added. This table will store a list of UserId’s of users to be notified when new queries are sent to the Data Mart.
- A new table PermissionsOrganizationsDataMarts will be added. This table will store a list of OrganizationId’s of Organizations allowed to submit queries to the Data Mart.
- A new table PermissionsGroupsDataMarts will be added. This table will store a list of GroupId’s of Groups allowed to submit queries to the Data Mart.
- A new table PermissionsQueryTypesDataMarts will be added. This table will store a list of QueryTypeId’s of QueryTypes allowed by the Data Mart.
- A new field “Email” will be added to the users table. This field will be used to deliver email notifications for the users of the system.
Data Mart Administration Menu Item
The Data Mart Administration feature will be accessed through the User Interface of the DRN Portal through a new “Data Mart Administration” menu item to be added to the “Administration” menu of the main navigation bar.
Data Mart List Screen
The entry screen for data mart administration will be the Data Mart List screen. The Data Mart List screen will feature a grid containing a list of data marts, as illustrated below:
Figure 29. Data mart list grid
The grid columns will include the following:
- Name: Data Mart name (DataMarts.DataMartName)
- Type: Data Mart type (DataMartTypes.DataMartType)
Data mart names will be presented as hyperlinks. Clicking on the data mart name will bring up the Data Mart Detail screen for the selected data mart.
The New Data Mart button will bring up the Data Mart Detail screen for creating a new data mart.
Data Mart Detail Screen
The Data Mart Detail screen will be used to edit existing data mart information or create a new data mart. The screen will contain a form for displaying and editing the data mart attributes1 as illustrated below:
Figure 30. Add/Edit data mart form
If the “Data mart type” is set to “Client”, then the “Server Url” field will be hidden, and the “Organization” and “Notifications” fields will be shown, as illustrated below:
Figure 31. Add/Edit data mart form (client variation)
The fields on the Add/Edit Data Mart form are as follows:
Table 35. Data mart detail form fields
Field | Database Table/Column |
---|---|
Data mart name (*) |
DataMarts.DataMartName |
Data mart type (*) |
DataMarts.DataMartTypeId |
Server Url |
DataMarts.Url |
Organization (*) |
DataMarts.OrganizationId |
Notifications |
List of Users.Username for users found in DataMartsNotifications for the DataMart. |
Available Periods |
DataMarts.AvailablePeriods |
Contact First Name |
DataMarts.ContactFirstName |
Contact Last Name |
DataMarts.ContactLastName |
Contact Phone |
DataMarts.ContactPhone |
Contact Email |
DataMarts.ContactEmail |
Special Requirements |
DataMarts.SpecialRequirements |
Usage Restrictions |
DataMarts.UsageRestrictions |
Supported Queries |
PermissionsQueryTypesDataMarts.QueryTypeId for the DataMart. |
(*) = Required field
The controls on the Add/Edit Data Mart form are as follows:
Table 36. Data mart detail form controls
Control | Function |
---|---|
Administrators – Add button |
Opens the User Selection dialog and adds the selected user(s) to the Notification Email list. |
Notification Email – Remove button |
Removes the selected row from the Notification Email list. |
Delete button |
Presents a confirmation dialog, as in: If the user clicks OK, the system flags the data mart as deleted by setting the DataMarts.isDeleted flag = 1. |
Save button |
Performs validation checks and saves entries made on the form. Validation checks are as follows:
For new data marts, inserts a new record in the data marts table. For existing data mart records, updates the existing record in the Data Marts table. |
Permissions button |
Presents the Data Mart Permissions form as described below. |
Cancel button |
Returns to the Data Mart List screen. |
Clicking the Permissions button brings up the Data Mart Permissions form:
Figure 32. Data mart permissions form
The fields on the Data Mart Permissions form are as follows:
Table 37. Data mart permissions form fields
Field | Database Table/Column |
---|---|
Data Mart Id |
DataMarts.DataMartId |
Groups |
Initially displays list of Groups.GroupName for groups found in PermissionsGroupsDataMarts for the current DataMart, where Groups.isDeleted=0. |
Organizations |
Initially displays the list of Organizations.OrganizationName for organizations found in PermissionsOrganizationsDataMarts for the current DataMart, where Organizations.isDeleted=0. |
Users |
Initially displays the list of Users.UserName for users found in PermissionsusersDataMarts for the current DataMart, where Users.isDeleted=0. |
The controls on the Data Mart Permissions form are as follows:
Table 38. Data mart permissions form controls
Control | Function |
---|---|
Groups – Add button |
Opens the Group Selection popup window and adds the user’s selections to the Groups list. |
Groups – Remove button |
Removes the row selected in the Groups list. |
Organizations – Add button |
Opens the Organization Selection popup window and adds the user’s selections to the Organizations list. |
Organizations – Remove button |
Removes the row selected in the Organizations list. |
Users – Add button |
Opens the User Selection popup window and adds the user’s selections to the Users list. |
Users – Remove button |
Removes the row selected in the Users list. |
Save |
Saves all changes made on the Data Mart Permissions form and returns to the Data Mart Detail form. Updates the PermissionsGroupsDataMarts, PermissionsOrganizationsDataMarts, and PermissionsUsersDataMarts tables, adding records to and removing records from each as needed, according to any changes made. |
Cancel |
Returns to the Data Mart Detail form without saving changes. |
3.4. Query Manager/Results Manager / Investigator User Interface
Overview of Requirement
Existing query functionality will be extended to support new query types: outpatient procedure codes, inpatient procedure codes, and enrollment. Query parameter selection will be expanded to include filtering by age group and gender.
New Query Type: ICD-9 Procedure Codes
A new query type will be added to the system to support ICD-9 Procedure Code queries.
The ICD-9 Procedure Codes query will use the existing "ICD-9 Procedures" query in the QueryTypes table.
User Interface
Figure 33. ICD-9 procedures
The existing Query Entry page (Query_Entry.aspx) will be modified to support specification of the ICD-9 Procedure Codes query.
The parameters for the ICD-9 Procedure Code query are listed in the table below. The Query Entry page will be modified to show the required parameter selection controls, and hide all others.
Table 39. Parameters for ICD-9 procedure codes query
Parameter | Description |
---|---|
Procedure Code | List of procedure codes and names.
Available from the demo database2 using the following SQL query: SELECT |
Period | List of periods. The underlying data only supports yearly periods, so the list of periods should be a list of years. |
Setting | List of settings – inpatient, outpatient, or emergency. |
Gender | List of Genders available for selection |
Age Groups | List of Age range available for selection |
Note: A special character (*) will be placed next to the mandatory fields on the query entry page
SQL
The Query Entry code and Results Manager will be modified to support the new query. The SQL for the ICD-9 Procedure Codes query is as follows:
SELECT
FROM WHERE
|
Where:
{0} is the setting value (‘INP’, ‘OUTP’, etc.)
{1} is the list of procedure codes
{2} is the list of periods (years)
{3} is the list of Age groups selected
{4} is the Gender selected
New Query Type: HCPCS Procedure Codes
A new query type will be added to the system to support Inpatient Procedure Code queries. The Healthcare Common Procedure Coding System (HCPCS) is a set of health care procedures codes based on the Current procedural Terminology (CPT). HCPCS are typically associated with outpatient care.
The HCPCS Procedure Codes query will use the existing "HCPCS Procedures" query in the HCPCSProcedures table.
User Interface
The existing Query Entry page (Query_Entry.aspx) will be modified to support specification of the HCPCS Procedure Codes query.
Figure 34. HCPCS procedures
The parameters for the HCPCS Procedure Code query are listed in the table below. The Query Entry page will be modified to show the required parameter selection controls, and hide all others.
Table 40. Parameters for HCPCS procedure codes query
Parameter | Description |
---|---|
Procedure Code | List of HCPCS procedure codes and names.
Available from the demo database3 using the following SQL query: SELECT |
Period | List of periods. Currently the underlying data only supports yearly periods, so the list of periods should be a list of years. |
Setting | List of settings – inpatient, outpatient, or emergency. |
Gender | List of genders(‘M’, ‘F’) available for selection |
Age Groups | List of age range available for selection |
Note: A special character (*) is placed next to the mandatory fields on the query entry page
SQL
The Query Entry code and Results Manager will be modified to support the new query. The SQL for the HCPCS Procedure Codes query is as follows:
SELECT
FROM WHERE
|
Where:
{0} is the setting value (e.g., ‘INP’, ‘OUTP’, etc.)
{1} is the list of HCPCS codes
{2} is the list of periods (years)
{3} is the list of Age groups selected
{4} is the Gender selected
New Query Type: Enrollment
A new query type will be added to the system to support Enrollment queries. Enrollment describes information such as the age and sex distribution of health plan members included in each data mart.
The Enrollment query will use the existing "Eligibility and Enrollment" query from the QueryTypes table.
User Interface
The existing Query Entry page (Query_Entry.aspx) will be modified to support specification of the Enrollment query.
Figure 35. Eligibility and enrollment
The parameters for the Enrollment query are listed in the table below. The Query Entry page will be modified to show the required parameter selection controls, and hide all others.
Table 41. Parameters for enrollment query
Parameter | Description |
---|---|
Period | List of periods. Currently the underlying data only supports yearly periods, so the list of periods should be a list of years. |
Age Group | List of age ranges available for selection |
Gender | List of genders available for selection |
SQL
The Query Entry code and Results Manager will be modified to support the new query. The SQL for the Enrollment query is as follows:
SELECT
FROM WHERE
|
Where:
{0} is the list of periods (years)
{1} is the list age group selected
{2} is the gender selected.
New Query Parameter: Age Group
A new query parameter will be added to the Query Entry screen to support filtering by age group, for queries which allow it. At the present time, all of the existing queries and the new queries being added in this phase support filtering by age group. The queries which can be filtered by age group include:
Pharmacy Dispensings by Generic Name
Pharmacy Dispensings by Drug Class
Dispensings by National Drug Code
ICD-9 Diagnoses
ICD-9 Procedure Codes
HCPCS Codes
Eligibility and Enrollment
The user interface should present the age group parameter using a list of check boxes, as in:
Figure 36. Age group
All values should be checked by default.
If all values are selected, then no filtering by Age Group is needed.
If no values are selected, a warning message should be presented stating that at least one Age Group value must be selected.
If some but not all values are selected, then the filtering criteria should be added to the SQL query by appending the following construct to the WHERE clause:
AND age_group IN {0} |
Where:
{0} is the list of age group strings, e.g., ‘0-4’,’5-9’,’15-19’
New Query Parameter: Sex
A new query parameter will be added to the Query Entry screen to support filtering by Sex, for those queries which allow it. At the present time, all of the existing queries and the new queries being added in DRN 1.5 support filtering by sex. The queries which can be filtered by sex include:
Pharmacy Dispensings by Generic Name Pharmacy Dispensings by Drug Class Dispensings by National Drug Code ICD-9 Diagnoses ICD-9 Procedure Codes HCPCS Codes Eligibility and Enrollment
The user interface should present the gender parameter using a list of check boxes, as in:
Figure 37. Gender
Both choices should be selected by default.
If both choices are selected, then no filtering by gender is needed. If either "Male" or "Female" is selected, then the filtering criteria should be added to the SQL query by appending the following construct to the WHERE clause:
AND gender = {0} |
Where:
{0} is ‘M’ for Male, or ‘F’ for Female.
Miscellaneous Investigator User Interface Changes
The following changes to the Investigator User Interface will be included in DRN 1.5:
- On the Query Entry page, the list of data marts will be filtered according to the User’s data mart permissions as described in section 3.
- On the Query Entry page, the list of data marts will be initially presented with all checkboxes unchecked.
- On the Query Entry page, the Period parameter selection for all queries will utilize a multiple-select list (i.e. list of checkboxes), such that multiple periods can be selected.
Query Type Change Summary
The table below summarizes the changes to Query Types in DRN 1.5:
Table 42. Query type change summary
Query Type | Comment |
---|---|
Pharmacy Dispensings by Generic Name | No change |
Pharmacy Dispensings by Drug Class | No change |
Dispensings by National Drug Code | No change |
ICD-9 Diagnoses | No change |
ICD-9 Procedures | Implemented in DRN 1.5 |
HCPCS Procedures | Implemented in DRN 1.5 |
Eligibility and Enrollment | Implemented in DRN 1.5 |
Ad hoc SQL | No change (not implemented) |
Ad hoc SAS Program | No change (not implemented) |
Query Type/Parameter Summary
The table below summarizes the available parameters for each query type in DRN 1.5:
Table 43. Query type/parameter summary
Query Type | Periods | Setting | Age | Sex |
---|---|---|---|---|
Pharmacy Dispensings by Generic Name | l | m | m | |
Pharmacy Dispensings by Drug Class | l | m | m | |
Dispensings by National Drug Code | m | m | ||
ICD-9 Diagnoses | l | l | m | m |
ICD-9 Procedures | l | l | m | m |
HCPCS Procedures | l | l | m | m |
Eligibility and Enrollment | l | m | m | |
Ad hoc SQL | ||||
Ad hoc SAS Program |
- Required
- Available but not required
3.5 Data Mart Metadata
Overview of Requirement
Searchable metadata will be added to the portal to document the available data at each site and provide site-specific information for users. Metadata will include years of data available, types of data available by year, key contacts, special requirements or restrictions on use, etc.
New Database Attributes
The table below lists the new database attributes to be added to the portal database to support metadata attributes for each data mart:
Table 44. New DataMarts attributes
Attribute | Description |
---|---|
AvailablePeriods | Text string containing a description of the available periods, e.g., "Annual and quarterly data from 2001 to present." |
ContactFirstName | First name of key contact, e.g., "Jeff" |
ContactLastName | Last name of key contact, e.g., "Smith" |
ContactPhone | Phone number of key contact, e.g., "(617) 555-1212" |
ContactEmail | Email address of key contact |
SpecialRequirements | Text string containing a description of any special requirements, e.g., "All procedure queries will be subject to IRB review, typically requiring 3-5 business days for approval." |
UsageRestrictions | Text string containing a description of any usage restrictions, e.g., "Data not available for export outside of the United States." |
HealthPlanDescription | Text string describing the health plan, such as population size, geographic region, etc. |
OrganizationId | Organization with which data mart is associated. Replaces the AdminUserId attribute. |
User Interface—Display Changes
The Data Mart Metadata will be made visible through the user interface as follows.
On the Query Entry form, the Data Mart selection control will be modified to provide hyperlinks to a popup display containing the metadata. The hyperlinks will appear as follows:
Figure 38. DataMart selection mockup
Clicking on a hyperlink will cause a popup window to appear containing a listing of the metadata for the selected data mart, as follows:
Figure 39. DataMart metadata popup
Administration
Administration of Data Mart metadata including entry and modifications of metadata values will be done through the Data Mart Admin User Interface as described in section 3 on page 28.
4. Data Mart Client Functions
4.1. Workflow Manager
Overview of Requirement
The Data Mart Client developed in the Phase 1 extension is an interactive desktop application, requiring hands-on use by a data mart administrator. The admin is alerted to new queries via email, and launches the Data Mart client, which guides the admin through a semi-automated process of downloading and executing queries and uploading results to the Portal. In this phase, the Data Mart Client will be extended so that it can receive and execute queries in an unattended mode. A feature will be added allowing the Data Mart Client to run "in the background" on the data mart administrator’s desktop system. The application will periodically poll the Portal to find queries, and will download and execute queries and upload results automatically, without any user interaction required. A set of preferences will govern the operation of the service and include settings such as the polling duration, and whether to require manual approval before uploading results.
Data Mart Client Architecture
The Architecture of the Data Mart Client will remain largely unchanged in this phase. The background processing feature will employ a system interval timer, delivering timer events on a separate thread. This "background" thread will handle unattended processing operations "in the background", such that they should not interfere with normal user interface operations in the Data Mart Client.
Background Processing
Background Processing will be a feature of the Data Mart Client which will periodically poll for new queries, execute queries, and upload results. The feature will operate "in the background" without requiring user interaction.
Background Processing operates in one of two modes:
- In Notify Only mode, the application will poll for new queries and alert the user when new queries are found, but will not process any queries automatically.
- In Process mode, the application will poll for new queries, execute them, and upload results automatically.
A polling interval configuration setting specifies the number of minutes between polling attempts.
When polling for new queries, the application will connect to the DRN Portal (using the DRN Hub Web Service API) and check for new queries.
When new queries are found, if the application is operating in Process mode, the system will automatically run the query and upload the results to the portal. If the application is operating in Notify mode, then the application will notify the user by causing the new query indicator to appear in the system tray icon and a description of the query to be briefly displayed as a tool tip above the system tray icon.
The methods used for polling for new queries, executing queries, and uploading results will be the same methods presently employed by the existing Data Mart Client.
System Tray Icon
The Data Mart Client will add an icon to the System Tray, for the purposes of 1) indicating that the background process is running, 2) providing basic status information through a "tool tip".
The "tool tip" is shown when the cursor is hovered over the system tray icon, and provides a status message as illustrated below:
Figure 40. DataMart display on task bar
The content of the status message includes the following information:
Table 45. DRN data mart client background service tool tip content
Item | Description |
---|---|
Query Count | Number of new queries processed by the service since it last entered the "Running" state. |
When the Background Process is in Notify mode as described in section 0, the system tray icon is used to deliver notifications and signal status to the user as illustrated below:
Figure 41. DRN data mart client background service notification tool tip
When new queries are found, a tool tip is displayed containing a description of the new query as shown. In addition, as long as new queries are present, the new query indicator appears in the system tray icon.
Settings Dialog
The Data Mart Client settings dialog will be modified as shown below:
Figure 42. Data Mart client settings dialog
The Data Mart Client Settings Dialog contains the following controls:
Table 46. Data mart client settings dialog controls
Control | Description |
---|---|
Unattended operation checkbox | Enables/disables the background processing feature |
Minutes textbox | Number of minutes for the polling interval. |
Notify-only radio button | Sets the Background Processing to "notify only" mode. The application will poll for new queries, and notify the user when new queries are found, but will not process queries automatically. |
Process radio button | Sets the Background Processing to "process" mode. The application will poll for and execute new queries and upload results automatically with no user interaction. |
Error Handling
Errors encountered by the Data Mart Client Background Service are reported to the Windows System Event Log through the Audit Manager as described in section 0.
4.2. Audit Manager
Overview of Requirement
To coincide with the unattended processing feature added in the Workflow Manager, a set of auditing and reporting capabilities will be added to the Data Mart Client. The auditing features will include transaction and event logging, including logging of query receipt and execution, as well as errors and exceptions. Reporting features will include the ability to run reports filtered by date range, query type, and user or organization.
Logging
The logging feature will provide functions for writing log messages to the Windows Event Log.
The following types of information will be logged:
Table 47. Types of logging
Type | Description |
---|---|
Transaction and Event Logging |
|
Query Receipt and Execution |
|
Errors and Exceptions | Unexpected errors and unhandled exceptions. |
All log messages will be written to a separate Windows Event Log File named DRN, which will be visible using the Windows Event Viewer:
Figure 43. Event viewer
Options for log size, rollover, etc. will be configurable using the standard Windows Event Viewer properties dialog:
Figure 44. Event log properties
Audit Manager
The Data Mart Client Audit Manager exposes methods for logging and reporting including the following:
void WriteLogEntry(LogEntrySeverity Severity, string Message)
Writes the specified Message to the log.
Severity specifies the message severity, one of:
LogEntrySeverity.Error
LogEntrySeverity.Information
Reporting
Reporting of events logged through the Audit Manager will be possible using the Windows Event Viewer.
Appendixes
This part of the document outlines the major updates to the DRN 1.5 application (new / changed requirements) implemented during this phase of the project.
Appendix 1. Approved Rights Admin Changes
Introduction
The web-based DRN 1.5 application provides users of the distributed research network (DRN) the capabilities to: Create, Submit and View results for various query types that can be submitted to different Data Marts (databases) based on users’ assigned or inherited rights & permissions. The rights for any user shall be administered by the system administrator or any user who has been granted the appropriate administrator "rights".
Existing Rights Administration
The current "RIGHTS" admin page lists the set of rights available in the system. Rights govern what users are able to do in the system (login, submit queries, add users…). Right are currently assigned to entities (Groups/Organizations/Users). The existing GUI for the RIGHTS Admin page is represented by the picture (#1) below.
Pic.1
Note: Rights granted at the High-level (e.g., Groups—which are comprised of 2 or more organizations), shall automatically be inherited by lower level entities (organizations or users).
For example: Assume right ‘X’ is granted to Group ‘A’. Then the organizations that belong to Group A (Organizations ‘B’ & ‘C’) and the users assigned to organizations ‘B’ & ‘C’ will automatically “inherit” right ‘X’, which has been assigned at the Group level.
Currently, rights assigned to specific entities (at any level) are set-up and updated by clicking on the “Rights” image button available on Group (Pic.2), Organization (Pic.3) and User (Pic.4) admin detail pages.
Pic.2 (Group Admin-Detail)
Pic.3 (Organization Admin-Detail)
Pic.4 (User Admin-Detail)
To illustrate, the rights for a given user are modified on the current rights popup page, which is opened by clicking the "Rights" image button as shown in the picture (#5) below.
Pic.5 (Existing Rights Selection Page)
Business Problem
Although DRN 1.5 allows the system administrator a way to administer rights for the selected entities, feedback from people using the system indicated that the rights section is
- Somewhat confusing: There are rights simply to gain access to the admin sub menu items, and these specific rights have confusing titles and descriptions. In addition, these rights could be eliminated; the system could generate the Administration sub-menu items automatically.
- Cumbersome, particularly in setting-up users and organizations (It takes too many clicks and the administrative user has to navigate to several different pages to complete the set-up and admin tasks)
- Difficult to understand what rights have been "inherited" by users. There is not one page that summarizes, for a given user, what rights they have inherited from Groups or Organizations; the current User rights page only lists those rights that have been directly to that user. Therefore, administrators cannot easily tell what rights a user has “inherited” from Groups or Organizations.
Client’s Preference
Weighing various aspects regarding rights administration with the client, it was identified that client preferred a role-based system that will enable the system administrator to easily set-up users with the appropriate rights by just assigning a role to that user on their user profile page; that role will have already been assigned the appropriate rights.
Also, the client wanted to enhance the "Right Selection" page (i.e., the pop-up page displayed by clicking the "Rights" button on the entity admin pages (Pic.2 to 5)) with a way to view all the rights granted to a given user (via the role assigned to that user) as well as displaying which rights were inherited from Groups and/or the User’s Organization.
Proposed Solution
The following design (GUI) changes are proposed to support administration capabilities based on the role assigned to the users of the system. The technical aspects of these changes are out of scope of this document and hence not elucidated in this document.
- Create a new Role Administration page:
This page (Pic.6) will allow the system administrator to define and assign appropriate rights to the role being created. The system administrator shall create a new role or select an existing role from a dropdown box; selecting a role will automatically give that user the rights that have been assigned to that role. (The "Rights" and "Role" mapping shall be saved to the database and retrieved as and when required.)
Pic.6 (Role admin page under administration menu)
- Remove excessive rights defined in the system:
The excessive rights (those rights that provide access to various "admin" pages) shall be removed to make the rights page more intuitive and less confusing. In addition, "Administration" sub-menu options will be generated automatically based on logic built into the DRN application.The following rights have been identified for removal. This will address business problem #1 mentioned above.
1. Administer Users (Allow access to administer user).
2. Administer Groups (Allow access to administer groups).
3. Administer Organizations (Allow access to administer organizations).
4. Administer Data Marts (Allow access to administer data marts). - Add new right to Administer "Role".
A new right, "Administer Roles", shall be added to the rights page. Checking this right shall grant access to the "Role Administration" page. - Auto select associated Rights:
While addressing business problem #1 by removing excessive rights as mentioned in Proposed Solution #2, it was also requested that a feature is included to automatically assign/select related rights when a dependant right has been granted.
For Example: Assuming that the administrator grants a user to view any query results detail, the user would automatically get to view any query results status and to view any query results summary. The same would be applicable while revoking (un-checking) these rights. - Modify the "Right Selection" Page (from Picture #2):
The existing "Rights" selection page (opened from the user profile page, Picture #2) shall be modified to include three columns (Groups, Organization, Role) of read-only checkboxes as displayed below (See Pic.7).
These three columns will indicate at what level the user was granted (inherited) a given right. This change shall address business problem #2 specified above. As requested by the client, the Administrator/User will not be allowed to change any rights on this page.
To change rights, the administrator will need to:
1. Select another available role on the user profile, or create a new role Note: A new role shall be created on the Role admin page. To update a user’s role, the user shall be assigned with appropriate role on user profile page.
Pic.7 (G?Group, O?Organization, R?Role)
When a user has to be granted additional rights other than what is offered by one of available roles (System Administrator, Investigator, Data Mart Administrator), the system administrator shall create a new role and assign the user this newly created role on that user’s profile page. (Our client did not want rights previously granted to be removed -- without formally changing that user’s role).
The list of groups that the user’s organization is assigned to shall be displayed on the top (summary) section of the page. (Why? Because Organizations can be part of multiple groups, and thus inherit those Groups’ Rights. We want to make sure we can identify which Groups may have granted rights to the organizations and users that belong to various Groups.)
Rules:
- The user shall be assigned with only one role.
- The rights selection page (Pic.7) when launched from the user profile page shall contain a summary section at the top of the page, displaying the user name, role, user organization and the group(s) that user belongs to. A user’s rights will not be editable on this page. To change a user’s rights, either that user must be re-assigned to an existing role or a new role must be created with the appropriate rights (And the user’s role will need to be changed on his or her user profile).
- The rights selection page opened from the Organization admin page shall contain two columns: Group & Organization (i.e., Role column shall not be applicable to the Organization Admin page). The user will be able to modify rights assigned to the Organization rights without changing the user role; the user will automatically "inherit" these rights.
- The right selection page opened from the Group admin page shall contain only the following columns (checkbox columns for Group, right code, and right description). An Administrator will be able to modify the Group rights without changing roles assigned to various users that are part of that group.
- The Group/ Organization/ User administration pages shall be equipped with two additional buttons "Query Type" and "Data Marts" to ease the entity setup, the latter only available for the "System" and "Data Mart" administrators. This shall be covered in a separate document.
Note: The system eventually checks for the assigned rights (defined within the role) although the user is being assigned with a role.
Appendix 2. Approved Notification Option Changes
Introduction
DRN 1.5 delivers various notifications to the users of the DRN systems that include
For Submitters (Investigators):
a. When a query Status has been Changed by the Data Mart administrator
b. When a query result has been uploaded by the Data Mart administrator (for client Data Marts or Automatic upload (for server Data Marts).
For Data Mart Administrators:
a. When a new query has been submitted to a data mart.
b. When the Data Mart owner(s) has not uploaded the results for the queries submitted to their Data Marts.
Suggested Changes
The submitter also should get the reminder(s) when the queries submitted have not been responded by one or more data marts that the query was submitted to. It was also preferred that the submitter receives one consolidated email at the preferred frequency for each query instead of one mail per data mart that have not uploaded the results for a query.
Approved Changes
Following changes will be made to Notification Options page available under the "SETTINGS" menu option on the DRN portal.
- Notification options will be grouped under sub sections ("Investigator" or "Data Mart Administrator") for display purpose.
- Notification descriptions will be edited to make it more self explanatory.
- A new notification option "Submitter reminder" will be added under the "Investigator" sub section.
Note: Unlike the existing notification options, the submitter reminder is specific to a user and each user will be able to choose a preferred reminder frequency. - The user will only be able to choose the notification frequency for submitter reminders, when the user has opted to receive this type "Submitter reminder" of notification.
- Along with the existing notification feature, an email will be delivered to the submitter for each query submitted that has not been responded by the respective data marts. The background service will be modified to include this feature. The frequency of such reminders shall be set to preferred frequency (in days) from the available list.
Note: No notifications will be delivered had the user not opted in for any of the notification options.
The Notification Option page when incorporated with the above changes will display as follows.
Pic.8 Notifications Options
Appendix 3. Approved Requirements to View Data Marts’ Meta Data
Introduction
The options available under the “Administration” menu option in DRN Portal (‘Data Marts’, ‘Rights’…) will be available for users with only appropriate role or rights.
Suggested Change
Every user—irrespective of their role / right—should be able to view the list of data marts available in the system and their respective metadata.
Approved Solution
A new option, “Data Marts”, will be added to the Menu on the DRN portal, which will be made available to all the users of the system irrespective of their roles / rights.
A new page (screenshot below) that displays the meta data of a data mart will be created and be launched when the user clicks the “Data Marts” menu option. The page launched will display the meta data of a data mart (defaulting to the first available data mart in the collection returned). The user will also be able to choose a different data mart from a drop down control, containing the list of available data mart names, which will enable the user to view the corresponding meta data for the specific data mart selected in the drop-down control.
Note:
- The data marts by default will be sorted on the Data Mart Id (ascending).
- Users will not be able to edit any of the Data Mart’s meta data. The controls on this screen will be made read-only.
Depicted below is a screenshot displaying the meta data available to be viewed.
Pic.9 DataMart Meta Data
This screen shot depicts the user interface where users can view DataMart meta data for all DataMarts in the system.
1Including the new data mart metadata attributes as described in section 3.5
2The demo database is the Access database "Demonstration Query Tool.mdb"
3The demo database is the Access database "Demonstration Query Tool.mdb"