...

Improved Totalnum Scripts

Totalnum Scripts for Postgres and MSSQL Scripts  have been updated to improve the Totalnum counter performance on both many ontology tables and very large(>1.5 million) ontology elements ontologies  such as ACT medications, . Debug messages have also been added for troubleshoot troubleshooting and profiling purposes

Totalnum Scripts Setup

1. In the Release_1-7/NewInstall/Metadata/ run the ant script to create the stored procedures.

...

1.  
   ant -f data_build.xml create_metadata_procedures_release_1-

...

1. 7 
2. Run the stored procedures on your database. This can be done in two ways

...

1. :

   ...

   1. 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   

   ...

   1. 1. 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
             And then run the totalnum counter with the wildcard flag, to ignore multifact references in the ontology
               e.g., in SQL Server, exec RunTotalnum 'observation_fact_view','dbo','@','Y'
             Note this approach does not work if you have conflicting concept_cds across fact tables.

      2. Execute the RunTotalNum  stored procedure

   ...

   1. 1. manually against your database in from a sql Client. This can take several hours.  Example Usage:    

                                             Oracle:            
                                             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)  
                                             SQL server:              
                                             exec RUNTOTALNUM    RunTotalnum 'observation_fact','dbo','@' (the observation table name (for multi-fact-table setups), the schemaname, -- a single table name to run on a single ontology table, and a wildcard flag that will ignore multifact references in the ontology if 'Y')

   
                                             PostgreSQL:              
                                             select RUNTOTALNUM('observation_fact','public')
                                         – (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
                                                  

   ...