...

Improved Totalnum Scripts

Totalnum Scripts  Scripts (patient counting scripts) have been updated to improve the Totalnum counter performance on both many ontology tables and very large(>1.5 million) ontologies  such as ACT medications. Debug messages have also been added for troubleshooting and profiling purposes. Support for multiple fact tables has been added and bugfixes have been made.

Totalnum Scripts Setup

1. In the 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 Run the stored procedures on your database. This can be done in two ways:
2. Run the ant command to execute the data_build.xml file with below specified target 
   POSTGRESQL : ant -f data_build.xml db_metadata_run_total_count_postgresql
   ORACLE : ant -f data_build.xml db_metadata_run_total_count_oracle
   SQL SERVER : ant -f data_build.xml db_metadata_run_total_count_sqlserver   
3. If using multiple fact tables, the recommended approach is to 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
   
   If running the counting 

   And

   script 

   then

   in 

   run

   SQL 

   the

   Server, 

   totalnum

   add 

   counter with

   the wildcard flag, to ignore multifact references in the ontology:
         e.g.

   ,
   in SQL Server,

   exec RunTotalnum 'observation_fact_view','dbo','@','Y'
   This is automatically accounted for in the other database platforms.
   Note this approach does not work if you have conflicting concept_cds across fact tables.

4. Run the stored procedures on your database. This can be done in two ways:
   1. Run the ant command to execute the data_build.xml file with below specified target 
      POSTGRESQL : ant -f data_build.xml db_metadata_run_total_count_postgresql
      ORACLE : ant -f data_build.xml db_metadata_run_total_count_oracle
      SQL SERVER : ant -f data_build.xml db_metadata_run_total_count_sqlserver   
   2. Execute the RunTotalNum  stored procedure manually against your database in from a sql Client. This can take several hours.  Example Usage:is below.    

...

Running the totalnum stored procedure directly

Oracle:            
                                          begin
                                          RUNTOTALNUMRUNTOTALNUM('observation_fact','i2b2demodata');
                                          endend;   

...

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)  

                                          SQL server:              
                                          exec  exec RunTotalnum 'observation_fact','dbo','@' (
Parameters are: 1) the observation table name (for multi-fact-table setups), 2) the schemaname, -- 3) a single table name to run on a single ontology table or '@' to run on all, and 4) and a wildcard flag that will ignore multifact references in the ontology if 'Y')

                                          PostgreSQL:              
                                          select RUNTOTALNUM('observation_fact','public')
                                      – (replace Replace 'public' by the schema name for the fact table)
                                      – If using a schema other than public for metadata, you might need to run "set search_path to 'i2b2metadata','public' " first as well
                                               wel

When finished, verify it is complete by checking that c_totalnum columns in your ontology tables contain numbers (not nulls).

...

    These total counts will be visible in the ontology browser in the web client.

...

Insert_Concept_FROMTableAccess  is designed to populate concept_dimenison table using Table_access table records.
The stored procedure loops through the table_access  and  inserts values from corresponding c_table_name metadatatable which have 
c_tablename column value as 'concept_dimension'
Example usage: exec Insert_Concept_FROMTableAccess

I2b2-Synthea data Load

A new option is now available for loading Synthea data files into i2b2. Synthetic patient data generated by Synthea is hosted on  SyntheticMass..can be loaded into i2b2. The Synthea SyntheticMass sample files have been converted to i2b2-ACT format. The zipped data files can be downloaded from , and scripts to load Synthea data from scratch are available here: https://github.com/i2b2/i2b2-synthea

Synthea Load Process:

1. Set up an i2b2 project with the ACT ontology.
2. Either download the SyntheticMass 1k sample from TODO LOCATION, or follow the instructions below to load any Synthea dataset from scratch.

Loading Synthea data from scratch

1. Download SyntheticMass Data, Version 2 (24 May, 2017)

...

1. Set up an i2b2 project with the ACT ontology.
2. Download the scripts from https://github.com/i2b2/i2b2-synthea
3. Run create_synthea_table_<your dbServertype>.sql in your project to create the Synthea tables.
4. Import the Synthea data you downloaded in step one into the Synthea tables in your project.
5. Load the i2b2-to-SNOMED table in this repository into your project. https://www.nlm.nih.gov/healthit/snomedct/us_edition.html
   • Click on the "Download SNOMED-CT to ICD-10-CM Mapping Resources" link to download. (You will need a UMLS account.)
   • Unzip the file
   • Import the TSV file into a table called SNOMED_to_ICD10 in your database.
6. In Postgres and Oracle, follow the additional instructions in the comments at the top of synthea_to_i2b2_<your dbServerType>.sql to clean up the date formatting.
7. Run synthea_to_i2b2_sqlserver<your dbServertype>.sql to convert synthea data into i2b2 tables (this will truncate your existing fact and dimension tables!)
   • Replace references to i2b2metadata.dbo in the script. Use the database and schema where your ACT ontology tables are.

...

...