Ioc Database Improvements

IoC database improvements April 26, 2002 Improved naming All table, field and variable names have had embedded spaces and special characters (except for the underscore character) removed. This ensures potentially portability to other programming environments such as SAS, APL, etc. Although field names are now compacted (e.g. no spaces), all tables and queries have field headers that are generally more descriptive and easier to read. Reorganized structure All tables have the following prefixes which denote their role in the database: tbl – refers to "core" tables that include, for example, DRG listings, OPC listings, various "decode" tables, descriptive tables, mapping tables, etc. Most of these tables are fixed although a few require additional input in setting up a new project (e.g. a completely new baseline dataset, for example). In addition, these tables are updated when adding new technology/payment items. itbl – refers to "input" tables that are typically changed (added to) with a new project. These include, for example, new "institutions", new technology and/or payment timings, new economic factors, etc. A new calculation may not necessarily require all these to be input and may utilize previous timings, factors, etc. ctbl – refers to "calculated" tables that represent intermediates in the various integrations and other such computations. otbl – refers to "output" tables that include intermediate and final output data. utbl – refers to "union" tables that hold the "vertical' tables suitable for PivotTable, PivotChart manipulations. These utbl tables are generally the "final" output from the database.

The organization and naming of queries have also been rationalized. These include: cqry – refers to queries that perform major calculations (mostly intermediate) dqry – refers to "delete" queries that clear tables at the start of a calculation. Required because of the new use of Append queries (see below) oqry – queries that generate otbls (or output tables) qry – regular "queries" mostly of an output nature but that run fairly quickly and are not generally used by other queries (e.g. we try to avoid using nested queries as much as possible) uqry – union queries that generate the "vertical" lists suitable for PivotTable / PivotChart analysis muqry – same as above but creates (appends) a table since the result is sometimes used by other queries. New modules / components and/or "wedges" Two new calculation modules have been added: the "X" series which was included previously as part of the "E" series. This has now been separated out. The "X" series represents an global impact on all utilization (e.g. a shock factor, entry of competitor, secular impact of managed care, etc.) which can evolve over time. The new "P" series can map individual payment trends (also evolving over time with a pulse, logistic, or linear fashion) to individual DRG/OPCs. Multiple ("simultaneous") data runs Multiple datasets can be run at a single time. Each dataset takes about 20 seconds to run. Each calculation is differentiated by a unique CalcID which can be crossreferenced to the underlying baseline information (e.g. baseline dataset, institution, timings, etc.) and parameters (scale factors, probability factors, etc.) used to generate the result. Running multiple datasets at once allows the final 'utbl" tables to be generated which can create PivotTable and/or PivotChart reports that allow the user to quickly visualize the difference between datasets/dataruns. Simulataneous dataset runs also allows for the important goal of generating a "sensitivity" analysis (see below).

Age/Sex distributions Multiple data runs also allow for different datasets to be simultaneously run that have (for a given institution and/or geography) age group and gender specific data. Sensitivity Analysis There are two ways that this can be run. Each calculation can specify globabl scale factors that apply to each of the components (technology, payment, sociocultural, etc.). Thus a given dataset can be run with the technology impact factors scaled, for example, by 1 (the default), 2, 0.5 and zero. We can see, then, how sensitive the overall forecast is to the technology effect (on an impact level). In addition, a new capability has been added (both to the payment and the technology sides) in which a "decelerator" or global time shift can be added to all the technology and/or payment curves. For example, multiple calculations can be run that shift the timing curves by 0 (the default), 1 year (decelerate by one year), and -1 years (accelerate by one year). Hence the sensitivity can be mapped (at the time level). Computational flexibility A given dataset (e.g. DRG volumes, DRG ALOS, and OPC volumes) can be run with any aribitrary set of: Technology timings (including the option to scale individual technologies) Sociocultural factors Economic – consumer – external factors Payment timings (including the option to scale individual payment trends) For example, we can run primary MGH data, with Cleveland Clinic technology timings, New York City sociocultural factors, Los Angeles Economic – consumer – external factors, and Chicago payment timings. Not that we'd want to do that but this example outlines the scope of what can be done. Arbitrary service line, technical group and clinical group definitions The database was previously fixed with the Partners service line definitions (e.g. DRG 1 in Neurosurgery, etc.). There is a new DRG Groupings and OPC Groupings tables that allows for arbitrary assignments of DRGs / OPCs to arbitrary service lines.

Speed of calculations The speed of calculations has been markedly improved. Some factors behind this include: 1) Technique of sparse matrix calculations. Basically not computing curves and output where the input values are all zero. 2) Creating a temporary baseline table from the master list. E.g. "volumes" are computed from a smaller baseline table containing only the selected data rather than searching through the master list. 3) Use of Append queries instead of MakeTable queries. This allows for intermediate calculated tables to include indexes and keys which facilitates more efficient calculations. In addition, Access has a "bug" which makes the database progressively less efficient as each Make Table query is executed. That extreme frustation element has been eliminated. Two integration methods If we want to run some quick-and-dirty calculations (debugging, etc.), there is the option to specify a simpler (though somewhat less precise) integration method. This shaves a couple of seconds of the runtime. Technology / Payment probability calculations The technology probability weighting factors (essentially downweighting technologies as a function of how far out they are from the baseline year) has also been utilized for the "P" or payment series

VBA code for functions Most of the mathematical functions in the database (other than simple products) are now encoded in Visual Basic Code. These include specialized: AddZero – used to regenerate the complete DRG listing at the conclusion of the calculations CalcALOS – Dividing Days by Discharges returns an error if the discharges are zero or null. This function traps such errors Consumerism – Computes the consumerism effect Integration – Basic integration module (including the two methods) OPS – computes the outpatient shift (the threshold is a parameter) Payment – Computes the payment curves PmtVolume – Computes the "P series" volumes. Similar to the general volume function below but applied only to non-medicare fractions Technology – Computes the technology curves Unemployment – Computes the "E" series rates Volume – Computes "volumes." Two options: either a "component" volume or an "aggregate" "volume"

Here's an example function: Function TechCurve(Baseline As Double, ApplyProb As Double, ProbScale As Double, _ Start As Double, Inflection As Double, DeclineYear As Double, rValue As Double, _ YearShift As Double) As Double Dim DiffBase As Double ' Declare the tech probability variables Dim ApplyTechProb As Double Dim DiffStart As Double ' Declare the logistic curve variables Dim PreStart As Double Dim Logistic As Double Dim ApplyLogistic As Double Dim DiffDecline As Double Dim ApplyDecline As Double Dim PreDecline As Double

' Declare the decline curve variables

' Compute the tech probability downweight DiffBase = Start - Baseline ApplyTechProb = Exp(-1 * ApplyProb * ProbScale * (Sgn(Sgn(DiffBase) + 1))_ * DiffBase * DiffBase) ' Compute the logistic curve build-up at point "YearShift" DiffStart = Baseline + YearShift PreStart = (Sgn(DiffStart - Start) + 1) / 2 Logistic = 1 / (1 + (Exp(-1 * rValue * (DiffStart - Inflection))) ^ 2) ApplyLogistic = PreStart * Logistic ' Compute the decline curve at point "YearShift" DiffDecline = DiffStart - DeclineYear PreDecline = Sgn(DeclineYear) * Sgn(Sgn(DiffDecline) + 1) ApplyDecline = Exp(-0.5 * rValue * PreDecline * DiffDecline) 'Compute aggregate curve TechCurve = ApplyTechProb * ApplyLogistic * ApplyDecline End Function

ALOS As mentioned, all references to LOS have been changed to ALOS. In addition, there is now a framework to account for different effects on ALOS and have them "sum" up to a composite effect on the final ALOS. Systematic, regular output All outputs can now be regularly exepcted to have exactly the same (complete) number of DRGs and OPCs. Before, if a dataset was missing some DRGs, then the output would be the "least common denominatory" between the DRG table and the DRGs listed in the baseline. Hence, the number of DRGs output (and the number of rows in the output data) would be a function of what was in the baseline data. The output is now structured that the "missing" DRGs are reconstituted with zero as their data values.