These scripts will count the total number of patients for every item in your ontology, and fill these in the c_totalnum column of your ontology tables. This can be used for internal quality checks and will display in the i2b2 query tool (not SHRINE), to help researchers pick ontology items that have data behind them. The obfuscated reports of sites’ totalnums can also be collected, aggregated, and analyzed by quality checking / exploring tools (forthcoming). Current technical documentation will be available in the latest release notes as new versions are released.
Run the procedures manually, or in Release_1-7/NewInstall/Metadata/ run the ant script to create the stored procedures.
ant -f data_build.xml create_metadata_procedures_release_1-7
See database-specific instructions below. After running the scripts, results are placed in: c_totalnum column of all ontology tables, the totalnum table (keeps a historical record), and the totalnum_report table (most recent run, obfuscated). These total counts will also be visible in the ontology browser in the web client.
By Mike Mendis and Jeff Klann, PhD based on code by Griffin Weber, MD, PhD
Run with:
exec RunTotalnum or exec RunTotalnum 'observation_fact','dbo','@'
The optional parameters are:
Note that visit and patient dimension will only be counted in conjunction with the default (observation_fact) tablename!
Option 1) If you have at most one fact table per ontology, run this once with each fact table specified!
e.g., to use on a fact table called derived_fact with just the act_covid ontology: exec RunTotalnum 'derived_fact','dbo','act_covid'
Option 2) Create a fact table view as the union of all your fact tables. (This is essentially going back to a single fact table, but it is only used
for totalnum counting. This is needed to correctly count patients that mention multiple fact tables within a hierarchy.)
e.g.,
create view observation_fact_view as
select * from CONDITION_VIEW
union all
select * from drug_view
And then run the totalnum counter with the wildcard flag, to ignore multifact references in the ontology, e.g.,
exec RunTotalnum 'observation_fact_view','dbo','@','Y'
Note this approach does not work if you have conflicting concept_cds across fact tables.
By Mike Mendis, based on SQL Server code by Griffin Weber, MD, PhD
Performance improvements by Jeff Green and Jeff Klann, PhD 03-20
Run the procedure like this (but with your schema name instead of i2b2demodata):
begin
runtotalnum('observation_fact','i2b2demodata');
end;
You can optionally include a table named if you only want to count one ontology table (this IS case sensitive):
begin
runtotalnum('observation_fact','i2b2demodata','I2B2');
end;
Note: If you get the error as: ERROR at line 1: ORA-01031: insufficient privilege, then run the command:
grant create table to (DB USER)
Original PostgreSQL code by Dan Vianello, Center for Biomedical Informatics, Washington University in St. Louis
2019 - Modified for i2b2 1.7.12 release by Mike Mendis, Partners Healthcare
2020 - Updated to support reporting and single-table runs by Jeff Klann, Massachusetts General Hospital
Usage example:
select runtotalnum('observation_fact','public')
Run the ant command to execute the data_build.xml file with below specified target