Page History
...
- Query Name (Click on Query ID number to display the previous query in the Find Patients window)
- Data Table Definition (Click on View under Data Request Type drop down Request item
- Option to Withdraw Request
- Option to enter Comments
- Log info box displays the User's actions. and log of the Request status
...
...
Data
...
Data Export - Manager process
The User-created data requests are managed by the data Data Manager using the Data Request Manager tool.
...
| Data Request Status | Data column Value |
|---|---|
| Submitted | Submitted |
| Withdrawn | Cancelled |
| Denied | Incomplete |
| File in Progress | Queued |
| File available | File available |
I DON'T UNDERSTAND THIS TABLE - JEFF
Example Export files (all data are fake)
2 sets of Two files are generated for each data exporttable exported.
Definition file: has the the variable names and data types for the data file
...
User-created Demographics
Definition file
Example:1969_Definition_For_Demographics_8_5_25.csv
| TABLE_INSTANCE_ID | TABLE_INSTANCE_NAME | USER_ID | GROUP_ID | SET_INDEX | C_FACTTABLECOLUMN | C_TABLENAME | COLUMN_NAME | C_FULLPATH | C_COLUMNNAME | C_COLUMNDATATYPE | C_OPERATOR | C_DIMCODE | AGG_TYPE | CONSTRAIN_BY_DATE_TO | CONSTRAIN_BY_DATE_FROM | CONSTRAIN_BY_VALUE_OPERATOR | CONSTRAIN_BY_VALUE_CONSTRAINT | CONSTRAIN_BY_VALUE_UNIT_OF_MEASURE | CONSTRAIN_BY_VALUE_TYPE | CREATE_DATE |
| 7 | DEM_6_27 | demoManager | SQLServerLarge | 0 | sex_cd | patient_dimension | Gender | @ | @ | @ | @ | @ | Value | 37:14.3 | ||||||
| 7 | DEM_6_27 | demoManager | SQLServerLarge | 1 | age_in_years_num | patient_dimension | Age | @ | @ | @ | @ | @ | Value | 37:14.3 | ||||||
| 7 | DEM_6_27 | demoManager | SQLServerLarge | 2 | race_cd | patient_dimension | Race | @ | @ | @ | @ | @ | Value | 37:14.3 | ||||||
| 7 | DEM_6_27 | demoManager | SQLServerLarge | 3 | patient_num | patient_dimension | (children) < 18 years old | \\ACT_DEMO\ACT\Demographics\Age\< 18 years old\ | birth_date | N | > | dateadd(YY, -18, GETDATE()) | Exists | 37:14.3 |
Data file:
Example: 1969_Demographics_8_5_25_08052025.cscsv
| patient_num | Gender | Age | Race | (children) < 18 years old | 75-84 years old | >= 65 years old |
| 26 | M | 72 | @ | No | Yes | Yes |
| 53 | F | 55 | WHITE | No | No | No |
| 87 | F | 59 | WHITE | No | No | No |
| 92 | F | 88 | DECLINED | No | No | Yes |
| 94 | F | 67 | ASIAN | No | No | Yes |
| 153 | M | 77 | @ | No | Yes | Yes |
User-created Labs
Definition file
Example: 2282_Definition_For_LAB_TESTS_HUGEPT_AGGOPTIONS
| TABLE_INSTANCE_ID | TABLE_INSTANCE_NAME | USER_ID | GROUP_ID | SET_INDEX | C_FACTTABLECOLUMN | C_TABLENAME | COLUMN_NAME | C_FULLPATH | C_COLUMNNAME | C_COLUMNDATATYPE | C_OPERATOR | C_DIMCODE | AGG_TYPE | CONSTRAIN_BY_DATE_TO | CONSTRAIN_BY_DATE_FROM | CONSTRAIN_BY_VALUE_OPERATOR | CONSTRAIN_BY_VALUE_CONSTRAINT | CONSTRAIN_BY_VALUE_UNIT_OF_MEASURE | CONSTRAIN_BY_VALUE_TYPE | CREATE_DATE |
| 310 | LAB_TESTS_HUGEPT_AGGOPTIONS | act | NODE9 | 0 | sex_cd | patient_dimension | Gender | @ | @ | @ | @ | @ | Value | 25:57.3 | ||||||
| 310 | LAB_TESTS_HUGEPT_AGGOPTIONS | act | NODE9 | 1 | age_in_years_num | patient_dimension | Age | @ | @ | @ | @ | @ | Value | 25:57.3 | ||||||
| 310 | LAB_TESTS_HUGEPT_AGGOPTIONS | act | NODE9 | 2 | race_cd | patient_dimension | Race | @ | @ | @ | @ | @ | Value | 25:57.3 | ||||||
| 310 | LAB_TESTS_HUGEPT_AGGOPTIONS | act | NODE9 | 3 | race_cd | patient_dimension | Ethnicity | @ | @ | @ | @ | @ | Value | 25:57.3 | ||||||
| 310 | LAB_TESTS_HUGEPT_AGGOPTIONS | act | NODE9 | 4 | concept_cd | concept_dimension | Cholesterol (Group:CHOL) | \\i2b2_LABS\i2b2\Labtests\LAB\(LLB16) Chemistry\(LLB17) Lipid Tests\CHOL\ | concept_path | N | like | \i2b2\Labtests\LAB\(LLB16) Chemistry\(LLB17) Lipid Tests\CHOL\ | NumFacts | 25:57.3 | ||||||
Data file:
Example: 2282_LAB_TESTS_HUGEPT_AGGOPTIONS_08122025
...
The i2b2 breakdown architecture is modified to support to support the data table definition and new breakdown types for User created data requests. The database tables have been modified to support the data table definition parametersdefinitions.
- RPDO_TABLE_REQUEST table stores the default data table parameters
- HIVE_CELL_PARAMS has new parameters for global and email configurations and the data file generation location (currently on local defaults to the server's drive)
- QT_RESULT_TYPE table logs will contain a new entries entry for each data table definition breakdown types
- QT_BREAKDOWN_PATH logs the will contain a new entry for each data table definition and definition which has the data export export execution details.
- QT_XML_RESULT logs XML documents containing contains metadata about each data request, such as status and e-mail details.
...
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.
Four patient_dimension Three default rows are initially loaded into the RPDO_TABLE_REQUEST, defining patient_dimension columns which are required in every export table. These are: vital_status_cd, race_cd, age_in_years_num, and sex_cd and gender
| Info |
|---|
The default rows insert script is provided in the data install folder of the release |
Reeta - update this screenshot, the Github does not do Vital Status.
QT_BREAKDOWN_PATH
QT_Breakdown_Path table has been modified to include a new column Group_id. It logs stores the Project _id valueID, in order to support project-scoped data table definitions.
| Column Name | Data Type |
|---|---|
| Group_Id | Varchar (50) |
...
Database updates- Data table definition creation
New rows are dynamically logged in db tables The database is dynamically modified when the user creates the data table definition or submits a data request
RPDO_TABLE_REQUEST
Data Rows for the data table design variables and values are dynamically inserted in the RPDO_TABLE_REQUEST at the time of when a data table Definition Saveis saved.
| 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: 17 |
| TABLE_INSTANCE_NAME | Data table definition name |
| USER_ID | data request user |
| GROUP_ID | project id |
| SET_INDEX | auto incremented for each concept name row for the table instance |
| C_FACTTABLECOLUMN | "concept_cd value in " for the fact table or "patient_num value in " for the patient_dimension |
| C_TABLENAME | corresponding corresponds to table storing concept_cd/patient_num( concept_dimension or patient_dimension ) |
| COLUMN_NAME | corresponds to value in The column _ name of the data table definition |
| C_FULLPATH | corresponds to metadata table column valuethe concept's c_fullname in the ontology table |
| C_COLUMNNAME | value in usually "concept_path column in concept_dimension/patient" for concept_dimension |
| C_COLUMNDATATYPE | corresponds to metadata ontology table column value |
| C_OPERATOR | corresponds to metadata ontology table column value |
| C_DIMCODE | corresponds to metadata ontology table column value |
| AGG_TYPE | corresponds to Aggregate option selection in data table definition |
| CONSTRAIN_BY_DATE_TO | corresponds to date constraint option selection in data table definition |
| CONSTRAIN_BY_DATE_TO | corresponds to date constraint option selection in data table definition |
| CONSTRAIN_BY_DATE_FROM | corresponds to date constraint option selection in data table definition |
| CONSTRAIN_BY_VALUE_OPERATOR | corresponds to set value constraint selection in data table definition |
| CONSTRAIN_BY_VALUE_CONSTRAINT | corresponds to set value constraint selection in data table definition |
| CONSTRAIN_BY_VALUE_UNIT_OF_MEASURE | corresponds to set value constraint selection in data table definition |
| CONSTRAIN_BY_VALUE_TYPE | corresponds to set value constraint selection in data table definition |
| CREATE_DATE | corresponds data table definition creation date |
| SHARED | Y (for project and system shared table definitions); N (User table definition) |
| DELETE_FLAG | Y (if deleted) or NN |
| C_VISUALATTRIBUTES | values are LA or LH (based on visibility table definitions panel) |
This is missing a couple of columns (e.g., REQUIRED)
Example:
QT_QUERY_RESULT_TYPE
...
QT_BREAKDOWN_PATH
A new row is logged also added here when the user saves the data table definition.
- Name Name column logs references the Table_instance_id when the user saves the table_definition.
- Value column logs the EXEC statement of contains the command to run the stored procedure that generates the data file.
- Group_id has the value of project _idID
- The result_instance_id variable parameter gets updated with the numeric value from the QUERY_RESULT_INSTANCE where the INSTANCE for the patientset (result_type_id (Ptset) =1 at the time of creation of Data file) when the export file is created.
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 |
...
