Query Tool and Analysis Types

The Query Tool tables are contained in distribution files crc_create_query_sqlserver.sql or crc_create_query_oracle.sql within the Demodata scripts. You can find the 1.5 versions at edu.harvard.i2b2.data/Release_1-5/NewInstall/Demodata/scripts within the demodata distribution.

These will repay some study.

When you query using the Query Tool (workbench or web client) it is possible to run a query against one or more analysis types; eg: age, race, vital status or gender. Beware that control data for analysis types is provided in so-called seed data held within the above files...

insert into QT_BREAKDOWN_PATH(name,value,create_date) values ('PATIENT_GENDER_COUNT_XML','\\i2b2\i2b2\Demographics\Gender\',getdate())
insert into QT_BREAKDOWN_PATH(name,value,create_date) values ('PATIENT_RACE_COUNT_XML','\\i2b2\i2b2\Demographics\Race\',getdate())
insert into QT_BREAKDOWN_PATH(name,value,create_date) values ('PATIENT_VITALSTATUS_COUNT_XML','\\i2b2\i2b2\Demographics\Vital Status\',getdate())
insert into QT_BREAKDOWN_PATH(name,value,create_date) values('PATIENT_AGE_COUNT_XML','\\i2b2\i2b2\Demographics\Age\',getdate())

There are implications to the above.

• Analysis breakdowns do not work if the ontology details are incorrect.
• Presumably, analysis types do need to be of the enumerated variety within the ontology tree in order to provide some structure for the histograms within the analysis view. So for example, gender must be an enumeration of male/female, race must be an enumeration of ethnic types, age must be an enumeration of age ranges, and so on.
  

NB: In the 1.5 data distribution for SqlServer, there does seem to be an error, as Age does not appear within the ontology tree, even though there are inserts for Age into the ontology. Why is still unknown, but likely to be a trivial SQL discrepancy.

Found it! In the job log, amongst the ontology inserts, this (and others occur):

[sql] Failed to execute:  INSERT INTO I2B2(C_HLEVEL, C_FULLNAME, C_NAME, C_SYNONYM_CD, C_VISUALATTRIBUTES, C_TOTALNUM, C_BASECODE, C_METADATAXML, C_FACTTABLECOLUMN, C_TABLENAME, C_COLUMNNAME, C_COLUMNDATATYPE, C_OPERATOR, C_DIMCODE, C_COMMENT, C_TOOLTIP, UPDATE_DATE, DOWNLOAD_DATE, IMPORT_DATE, SOURCESYSTEM_CD, VALUETYPE_CD) 
VALUES(2, '\i2b2\Demographics\Age\', 'Age', 'N', 'FA', NULL, NULL, NULL, 'concept_cd', 'concept_dimension', 'concept_path', 'T', 'LIKE', '\i2b2\Demographics\Age\', NULL, 'Demographics \ Age', NULL, NULL, NULL, 'DEM2FACT CONVERT', NULL)
[sql] com.microsoft.sqlserver.jdbc.SQLServerException: Cannot insert the value NULL into column 'UPDATE_DATE', table 'i2b2metadata.dbo.I2B2'; column does not allow nulls. INSERT fails.