OPTIMIZER: Remove nr_dpa from all_cycles_effective table. It only includes cycle 1 which is confusing

[jsfried]

The all_cycles_effective table is a key table for analyzing prescription effectiveness across all prescriptions and having NetRev info in it that is consistent is important. For cases where cycle 1 does not cut trees, this is coming up as null even when there are trees cut in cycles <> 1, while the econ tables DO show a net rev value.

[lbross]

I'm struggling to remember how this is supposed to work. If you look at the definition of the effective table it defines NR_DPA as ECON_BY_RX_CYCLE.MAX_NR_DPA. Because rxcycle is always ‘1’, this will always be ECON_BY_RX_CYCLE.MAX_NR_DPA for cycle 1. This is consistent with what you reported. I've reviewed the documentation for the implementation of the weighted variables and it looks like some of it moved into the Optimization table so the information you're looking for may be somewhere else. Unfortunately as I tried to track this down, I ran into an SQL error when using a net revenue filter that is likely due to the conversion to SQLite. @druepdx, can you take a look? The offending query starts on line 862 of uc_optimizer_scenario_run.cs. To replicate the error, you'll need to enable a revenue filter on the Optimization tab. You can just use net_revenue_1 and pick a value.

[lbross]

This is the error message I get when using a net revenue filter

[druepdx]

I found the cause of the SQL error and pushed the fix to my fork

[lbross]

Closing. Per the design document referenced above, effective.nr_dpa only contains revenue for cycle 1. Use the econ_by_rx_cycle and/or econ_by_rx_utilized_sum tables in optimizer_results.db to get economic including nr_dpa for all 4 cycles. The economic filtering SQL error is addressed under a new issue #348.

[jsfried]

In a future release that changes db structure, perhaps we can rename this column to NR_DPA_Cycle1 to clue the clueless analyst in on what to expect there. When running an all-cycles analysis (which we do 98% of the time), every other attribute has the potential to be populated for every row, but not this oddball.

[lbross]

I propose dropping the field from this table. I think we left it in to accommodate cycle 1 - only analyses but if only 2% will use it, it would be less confusing to have it gone.

[lbross]

Remove NR_DPA from the Effective table.

[druepdx]

I removed the NR_DPA field from the all_cycles_effective table.

[lbross]

Thanks @druepdx. I just updated the TREATMENT OPTIMIZER TABLE DEFINITIONS.docx to reflect this change.

[lbross]

From @Cat-Ducat-USDA: nr_dpa is not present in the all_cycles_effective table, but net_revenue_1 is.