Page History
...
| User Role | Create Data Table | Data Request | Data Request Manager View Details | Data Export/Generate Data file | Change Request Status |
|---|---|---|---|---|---|
| Obfuscated | Enabled | Enabled | Enabled | Disabled | Disabled |
| Aggregate | Enabled | Enabled | Enabled | Disabled | Disabled |
| Protected | Enabled | Enabled | Enabled | Disabled | Disabled |
| LDS | Enabled | Enabled | Enabled | Disabled | Disabled |
| Manager/No LDS | Enabled | Enabled | Enabled | Disabled | Disabled |
| Manager/LDS | Enabled | Enabled | Enabled | Enabled | Enabled |
| Admin | Enabled | Enabled | Enabled | Enabled | Enabled |
Database Configuration
RPDO_TABLE_REQUEST
In order to support the data Table design, a new table RPDO_TABLE_REQUEST is added. The metadata in Data Table design is stored in the table when the data Table is saved.
...
| Column Name | Data Type |
|---|---|
| Group_Id | Varchar (50) |
Database Updates
New rows are logged in db tables at the time of data table definition creation and data request submission
RPDO_TABLE_REQUEST
User created concept details of the data Data table design variables and values are dynamically logged in the RPDO_TABLE_REQUEST at the time of Table data table Definition Save.
| RPDO_TABLE_REQUEST PARAMETERS | DATA TABLE Values |
|---|---|
| Table_Request_id | auto generated incremental value, corresponding to each concept in the data table. Unique for each concept example: 1282,1283, 1284 |
| Table_instance_id | unique for each Data table ( same value for all the concepts underneath) example: 14417 |
QT_QUERY_RESULT_TYPE
The data table definitions are logged as breakdowns in the QT_QUERY_RESULT_TYPE table. The rows are inserted dynamically when the user saves the data table definition.
...
| RESULT_TYPE_ID | NAME | DESCRIPTION | DISPLAYTYPE_ID | VISUAL_ATTRIBUTE_TYPE_ID | USER_ROLE_CD | CLASSNAME |
Auto generated number example : 256144 | RPDO_<TABLE_INSTANCE_ID> example: RPDO_14417 | User-created <Table_Name>Request | CATNUM | LU | DATA_LDS | edu.harvard.i2b2.crc.dao.setfinder.QueryResultPatientRequest |
| Info |
|---|
We recommend data requests be limited to DATA_LDS users and data exports be limited to MANAGER users. The user role determines if exports/requests are visible and runnable. Refer to section on User Roles and Actions |
QT_BREAKDOWN_PATH - Table Definition Creation
QT_breakdown_path logs the table_instance_id and the stored procedure EXEC statement for creating the Data file at the time of Data Table Definition creation
A new row is logged when the user saves the data table defintion.
- Name column logs the The Table_instance_id is logged in the Name column when the user saves the table_definition.
- The Value column logs the EXEC statement of the stored procedure that generates the data file.
- Group_id has the value of project_id
- The result_instance_id variable gets updated with the numeric value from the QUERY_RESULT_INSTANCE where the result_type_id (Ptset)=1 at the time of creation of Data file.
Example:
| NAME | VALUE | Group_id |
|---|---|---|
RPDO_<Table_instance_id> Example: RPDO_ |
17 | EXEC i2b2synthea8.dbo.usp_rpdo2 @TABLE_INSTANCE_ID=<Table_instance_id> @RESULT_INSTANCE_ID={{{RESULT_INSTANCE_ID}}} @MIN_ROW=0 @MAX_ROW=10000 | SQLServerLarge |
QT_QUERY_RESULT_INSTANCE
When the User runs a query with User created Data Request option selected, a row is logged in the QT_Query_Result_Instance for the result_type_id generated in the QT_Result_type
| Column Name | Value (Example) |
|---|---|
| Result_Type_ id | 256144 |
| Result_instance_id | 1729541 |
QT_BREAKDOWN_PATH - Data File Creation
The Value column is updated with resultfor table_instance_id from the Q_Result_Instance and result_instance_id in the EXEC statement and the stored procedure to generate the Data flle file is executed
Example:
| NAME | VALUE |
|---|---|
RPDO_<Table_instance_id> Example: RPDO_144 | EXEC i2b2synthea8.dbo.usp_rpdo2 @TABLE_INSTANCE_ID=<Table_instance_id> 17 @RESULT_INSTANCE_ID=541 @MIN1729@MIN_ROW=0 @MAX_ROW=10000 |
QT_XML_RESULT
Metadata about data request queries are stored in the QT_XML_RESULT table, in the XML_VALUE field. The Value column gets updated with the change in Status of the Data Request. Following are the staus change in the <data column> tag
| Column Name | Value (example) |
|---|---|
| Result_Instance_Id | 1729 |
| XML_Value | < |
| Data Request Status | Data column Value |
| Submitted | Submitted |
| Withdrawn | Cancelled |
| Denied | Incomplete |
| File in Progress | Queued |
| File available | File available |
| Column Name | Value (example) |
| Result_Instance_Id | 541 |
| Value | <?xml version="1.0" encoding="UTF-8" standalone="yes"?> <ns10:i2b2_result_envelope xmlns:ns6="http://www.i2b2.org/xsd/cell/crc/psm/querydefinitionanalysisdefinition/1.1/" xmlns:ns5="http://www.i2b2.org/xsd/hive/msg/1.1/" xmlns:ns8="http://www.i2b2.org/xsd/cell/pm/1.1/" xmlns:ns7="http://www.i2b2.org/xsd/cell/crc/psm/analysisdefinitionquerydefinition/1.1/" xmlns:ns9="http://www.i2b2.org/xsd/cell/ont/1.1/" xmlns:ns10="http://www.i2b2.org/xsd/hive/msg/result/1.1/" xmlns:ns2="http://www.i2b2.org/xsd/hive/msg/resultpdo/1.1/" xmlns:ns2ns4="http://www.i2b2.org/xsd/cell/hivecrc/pdopsm/1.1/" xmlns:ns4ns3="http://www.i2b2.org/xsd/cell/crc/psmpdo/1.1/" xmlns:ns3="http://www.i2b2.org/xsd/cell/crc/pdo/1.1/"> <body> <ns10:result name="RPDO_144"> 1/"> <body> <ns10:result name="RPDO_17"> <data column="SUBMITTED" type="string">20250808_125429</data> <data column="EMAIL" type="string">rmetta@mgb.org</data> <data column="QUEUED" type="string">20250808_125631</data> <data column="PROCESSING" type="string">20250808_125631</data> <data column="DIRECTORY" type="string">/opt/dataexport/SQLServerLarge/724</data> <data column="SUBMITTEDFINISHED" type="string">20250513>20250808_172906<125633</data> <data column="EMAILAPPROVEDBY" type="string">act@gmail.com<>demoManager</data> </ns10:result> </body> </ns10:i2b2_result_envelope> envelope> |
Following are the status change in the <data column> tag of Value column
| Data Request Status | Data column Value |
|---|---|
| Submitted | Submitted |
| Withdrawn | Cancelled |
| Denied | Incomplete |
| File in Progress | Queued |
| File available | File available |
Data file format/location and Email parameters set-up: HIVE_CELL_PARAMS
...
