...

The code for the totalnum stored procedures can be found here. 

Fast Totalnum

Got it—here’s your updated doc with Oracle and Postgres run syntaxes included and formatted as quick, copy-paste blocks.

Fast Totalnum

i2b2 1.8 introduces a version that is 5-10x faster. This 5–10× faster. As of 1.8.2, this faster version is presently only available for MSSQL and has only been extensively tested with the ACT ontology. 1.8.x versions will improve on this faster version, Postgres, and Oracle. Counting the patient and visit dimension requires the ACT ontology. These replace the  pat_count_dimensions  and  run_all_counts  stored procedures.

Configuration

...

One-time / when ontology changes: run the preparatory

...

routine. This creates

...

:

• OBSFACT_PAIRS view (distinct concept_cd, patient_num)

• TNUM_ONTOLOGY (unified ontology table)

• CONCEPT_CLOSURE (transitive closure table)

This can take up to ~1 hour.

• If you use more than one fact table, customize the obsfact_pairs view (see code comments)

...

• .

• ACT_VISIT_DETAILS_

...

• V41 and ACT_DEM_

...

• V41 table names are presently

...

• hardcoded—change if your

...

• local names

...

• differ.

Counting: runs with no parameters; writes to the totalnum table (created during i2b2 1.7.12

...

/1.7.13

...

/1.8

...

upgrade). Typical runtime: 1–3 hours.

• exec FastTotalnumCount

...

Output: writes obfuscated results to

...

totalnum_report

...

• exec FastTotalnumOutput or exec FastTotalnumOutput 'dbo','@'

• Optionally you can specify the schemaname and a single table name to run on a single ontology table (or @ for all).

Execution 

Run the following commands in a SQL client.

...

and pushes totalnum into ontology tables for the UI.

Execution (by database)

SQL Server (T-SQL)

-- Run once (when ontology changes)
EXEC FastTotalnumPrep;                 -- or:
EXEC FastTotalnumPrep 'dbo';           -- specify schema

-- Actual counting (hours)
EXEC FastTotalnumCount;

-- Output results to report + ontology (all ontology tables)
EXEC FastTotalnumOutput;               -- or:
EXEC FastTotalnumOutput 'dbo','@';     -- schema + all ontologies

-- Single ontology table example
EXEC FastTotalnumOutput 'dbo','MY_ONTOLOGY';

Oracle

Tip: In SQL*Plus/SQLcl, end each anonymous PL/SQL block with /. In SQL Developer, you can run the block directly.

-- Run once (when ontology changes)
BEGIN
  FastTotalnumPrep('MY_SCHEMA');  -- optional schema
END;
/

-- Actual counting (hours)
BEGIN
  FastTotalnumCount;
END;
/

-- Output results (all ontology tables = default)
BEGIN
  FastTotalnumOutput;
END;
/

-- Output results (single ontology table)
BEGIN
  FastTotalnumOutput('MY_SCHEMA', 'MY_ONTOLOGY');
END;
/

Postgres

Note: Some installs expose fasttotalnumprep as a function (call with SELECT) and fasttotalnumcount/fasttotalnumoutput as stored procedures (call with CALL). Replace the schema (public below) with yours—often public.

-- Run once (when ontology changes) on default schema
SELECT fasttotalnumprep();

-- Or on a specific schema
SELECT fasttotalnumprep('my_schema');

-- Actual counting (hours)
CALL fasttotalnumcount();

-- Output results (all ontology tables)
CALL fasttotalnumoutput();

-- Output results (single ontology table)
CALL fasttotalnumoutput('public', 'my_ontology');

Some additional notes on running on OMOP

...

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

   1. If running the counting script in SQL Server, add the wildcard flag, to ignore multifact references in the ontology: e.g. 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.

Execution

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.

...