Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

VHA ODS tables #13733

Closed
4 tasks done
pkarman opened this issue Mar 18, 2020 · 3 comments · Fixed by #14317
Closed
4 tasks done

VHA ODS tables #13733

pkarman opened this issue Mar 18, 2020 · 3 comments · Fixed by #14317
Assignees
@pkarman @yoomlam
Labels
Eng: Data Mark when data has been backfilled, or the issue has any data integrity concerns Team: Delta 🔺
Milestone
PI4-S2: Amelia Ea...

Comments

@pkarman
Copy link
Contributor

pkarman commented Mar 18, 2020 •
edited
Loading

Description

VHA has reporting needs similar to those of VBA. These include tables:

  • HIGHER_LEVEL_REVIEWS: Intake data for Higher level Reviews
  • SUPPLEMENTAL CLAIMS: Intake data for Supplemental Claims
  • APPEALS: Used to keep track of information for AMA appeals
  • DECISION_ISSUES: Issue-level dispositions for AMA claims/appeals
  • REMAND_REASONS: Remand reason for decision issues.

In order to make it simple for VHA to pull the data they need, we need a new ODS table vha_decision_reviews that is scoped to those Decision Reviews (Appeal, HLR, SC) that have an associated benefit type corresponding to VHA.

Acceptance criteria

  • New vha_decision_reviews ODS table with composite schema matching HLR, SC and Appeal.
  • Add remand_reasons ODS table
  • ETL::VHADecisionReview model and syncer added to builder job

Technical notes

Easiest way to identify VHA-related decision reviews is via the BusinessLine to task association. See #13075 for example.
@pkarman pkarman added Eng: Data Mark when data has been backfilled, or the issue has any data integrity concerns Team: Delta 🔺 labels Mar 18, 2020
@pkarman pkarman self-assigned this Mar 18, 2020
@pkarman pkarman mentioned this issue May 7, 2020
Merged
8 tasks
@yoomlam yoomlam mentioned this issue May 7, 2020
Closed
va-bot pushed a commit that referenced this issue May 11, 2020
@pkarman
VHA ODS tables (#14208)
2fe350b 
connects #13733 

### Description

Adds a new ETL table `vha_decision_reviews`.

The new table uses STI (single table inheritance) to coalesce Appeals, HLRs and SCs that all have a benefit type of `vha`.

### Acceptance Criteria
- [x] Code compiles correctly

### Testing Plan
1. Deploy and watch ETL builder for errors.

### Code Documentation Updates
- [x] Add or update code comments at the top of the class, module, and/or component.

### Database Changes
*Only for Schema Changes*

* [x] Timestamps (created_at, updated_at) for new tables
* [x] Column comments updated
* [x] Query profiling performed (eyeball Rails log, check bullet and fasterer output)
* [x] Appropriate indexes added (especially for foreign keys, polymorphic columns, and unique constraints)
* [x] DB schema docs updated with `make docs`
* [ ] #appeals-schema notified with summary and link to this PR
@pkarman
Copy link
Contributor Author

pkarman commented May 15, 2020

in production just now:

irb(main):006:0> ETL::VhaDecisionReview.count
[2020-05-15 11:46:36 -0400]    (1.6ms)  SELECT COUNT(*) FROM "vha_decision_reviews"
=> 764

@yoomlam yoomlam mentioned this issue May 17, 2020
Merged
8 tasks
@yoomlam
Copy link
Contributor

yoomlam commented May 17, 2020

For comparison:

irb(main):003:0> HigherLevelReview.processed.where(benefit_type: "vha").count
=> 260
irb(main):004:0> SupplementalClaim.processed.where(benefit_type: "vha").count
=> 367
irb(main):006:0> vha_appeal_ids=RequestIssue.select(:decision_review_id).where(benefit_type: "vha", decision_review_type: :Appeal)
irb(main):007:0> Appeal.established.where(id: vha_appeal_ids).count
=> 137
irb(main):008:0> 260+367+137
=> 764

@hschallhorn hschallhorn added this to the PI4-S2: XXX Sprint milestone May 18, 2020
va-bot pushed a commit that referenced this issue May 18, 2020
@yoomlam
Add remand_reason ODS table for VHA (#14317)
0d6237b 
Resolves #13733
Follow-on PR to first PR #14208.

### Description
Add ETL remand_reason table for VHA reporting needs. 
Pretty much a straight copy of original table.

### Acceptance Criteria
- [x] Code compiles correctly

### Code Documentation Updates
- [x] Add or update code comments at the top of the class, module, and/or component.

### Database Changes
*Only for Schema Changes*

* [x] Timestamps (created_at, updated_at) for new tables
* [x] Column comments updated
* [ ] Query profiling performed (eyeball Rails log, check bullet and fasterer output)
* [x] Appropriate indexes added (especially for foreign keys, polymorphic columns, and unique constraints)
* [x] DB schema docs updated with `make docs`
* [x] #appeals-schema notified with summary and link to this PR
@pkarman
Copy link
Contributor Author

pkarman commented May 19, 2020

Email sent to stakeholders, scheduling time to review.

@pkarman pkarman mentioned this issue May 19, 2020
Merged
6 tasks
va-bot pushed a commit that referenced this issue May 20, 2020
@pkarman
Rename ETL vha_decision_reviews (#14343)
65a9806 
connects #13733 

### Description
Rename table and models to drop `Vha` since the schema structure can be used for other lines of business.

Adds an index on `decision_issues.benefit_type` in order to make it easier to query by line of business.

### Database Changes
*Only for Schema Changes*

* [ ] Timestamps (created_at, updated_at) for new tables
* [x] Column comments updated
* [ ] Query profiling performed (eyeball Rails log, check bullet and fasterer output)
* [x] Appropriate indexes added (especially for foreign keys, polymorphic columns, and unique constraints)
* [x] DB schema docs updated with `make docs`
* [x] #appeals-schema notified with summary and link to this PR
@pkarman pkarman mentioned this issue May 20, 2020
Closed
3 tasks
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@pkarman pkarman

@yoomlam yoomlam

Labels
Eng: Data Mark when data has been backfilled, or the issue has any data integrity concerns Team: Delta 🔺
Projects
None yet
Milestone
PI4-S2: Amelia Earhart Sprint
Development

Successfully merging a pull request may close this issue.

Add remand_reason ODS table for VHA department-of-veterans-affairs/caseflow
3 participants
@pkarman @hschallhorn @yoomlam