Add cycle number and status columns to TDS Amanda Review Data (99028) (SS) (Ready for UAT on PROD)

TracyLinder commented 8 months ago

TDS would like their performance data report to include the cycle number and the process status,

The original ticket for this report is #15152

Acceptance Criteria

Add a new column 'Cycle Number' to the existing ATD Report "TDS AMANDA Review Data".
Cycle number column needs to be populated with the total number of processes that exist, regardless of attempt type or process status.
Add a column for Council District.

Processes to include in query: -- ATD Engineering Review -- ATD SIF Review -- CC ATD Review

Cycle Number:

We will have to use DSD's powerbi logic to create our own query to include in this report

Status:

Maps to process status

TracyLinder commented 8 months ago

TDS Review 2/6

Matiur spoke to Kyle (with McKinsey initiative # 8 (KPI dashboard)) who has a dashboard on cycle information. Kyle said he will schedule a meeting with us to share their reporting/dashboard. They are using PowerBI and building the reporting currently. This is something we may be able to replicate for TDS specific needs.

TracyLinder commented 7 months ago

From DSD's data person:

with Main_D as (select distinct fc.case_sk,dc.case_rsn, fpa.case_process_rsn, decode (lpar.process_attempt_result_code, 50000, 'Initial', 'Update') Cycle_Type, fpa.process_attempt_date cycle_start_date,dc.case_folder_section, (row_number () OVER (PARTITION BY fc.case_sk, fpa.case_process_rsn ORDER BY fc.case_sk, fpa.case_process_rsn,fpa.process_attempt_date)-1) Update_Count, fp.process_schedule_end_date from fct_case fc join fct_case_process_attempt fpa on fpa.case_sk = fc.case_sk join dim_case dc on dc.case_sk = fc.case_sk join lkp_type lt on lt.type_sk = dc.case_type_id join lkp_process_attempt_result lpar on lpar.process_attempt_result_sk = fpa.process_attempt_result_sk join fct_case_process fp on fp.case_process_rsn = fpa.case_process_rsn join lkp_process lp on lp.process_sk = fp.process_sk where lt.type_code = 'PR' and lpar.process_attempt_result_code in (50000,50095) and lp.process_code = 50007 and fc.current_record_flag = 'Y' order by fc.case_sk, fpa.process_attempt_date ), Revision_CNT as (SELECT fpa2.process_attempt_date, fpa2.case_sk FROM fct_case_process_attempt_vw fpa2, fct_case_process_vw fp2, lkp_process lp, lkp_process_attempt_result lpar where fp2.case_process_rsn=fpa2.case_process_rsn and lp.process_sk = fp2.process_sk and lpar.process_attempt_result_sk = fpa2.process_attempt_result_sk --and fpa2.process_attempt_date<fpa.process_attempt_date and lp.process_code in (50403,50405,50033,83400,51408) and lpar.process_attempt_result_code in (50096))

SELECT case_sk, case_process_rsn, cycle_start_date, cycle_end_date, Cycle_Due_Date, Update_Count, Cycle_Type, Cycle_Result, p_Type, Revision_Count from ( select m.case_sk,m.Cycle_Type,m.cycle_start_date,m.case_process_rsn, NULL cycle_end_date, --m.cycle_start_date+ lvl.valid_lookup_fee process_schedule_end_date Cycle_Due_Date, NULL Cycle_Result , m.Update_Count, 'PR' as p_Type,count(rcnt.case_sk) as Revision_Count from Main_D m left OUTER JOIN Revision_CNT rcnt ON rcnt.process_attempt_date<=m.cycle_start_date and rcnt.case_sk=m.case_sk group by m.case_sk,m.Cycle_Type,m.case_process_rsn,m.cycle_start_date,process_schedule_end_date, m.Update_Count UNION ALL select case_sk,Cycle_Type,Cycle_Start_Date,case_process_rsn,Cycle_End_Date,Cycle_Due_Date,Cycle_Result,Update_Count,'LUR' as p_Type,0 as Revision_Count from (select fc.case_sk,fp.case_process_rsn, decode((row_number() OVER (PARTITION BY fc.case_sk order by fp.case_process_rsn)-1), 0, 'Initial', 'Update') Cycle_Type, fp.process_schedule_date Cycle_Start_Date, fp.process_end_date Cycle_End_Date, fp.process_schedule_end_date Cycle_Due_Date, (select process_status_desc from lkp_process_status lps where process_status_sk = fp.process_status_sk) Cycle_Result, (row_number() OVER (PARTITION BY fc.case_sk order by fp.case_process_rsn)-1) Update_Count from fct_case fc join fct_case_process fp on fc.case_sk = fp.case_sk join dim_case dc on fc.case_sk = dc.case_sk join lkp_process lp on lp.process_sk = fp.process_sk join lkp_type lt on dc.case_type_id = lt.type_sk where lt.type_code in ('C', 'C8', 'SP', 'GP', 'TAPS','RS') and lp.process_code in (51145,51095,50033,83400,51408,80042, 70000, 85305) --and fc.case_in_date >TO_DATE('01/01/2015','mm/dd/yyyy') and fc.current_record_flag = 'Y' order by fc.case_sk, fp.process_schedule_end_date ) / Begin of WMU Details / UNION ALL select case_sk,Cycle_Type,Cycle_Start_Date,case_process_rsn,Cycle_End_Date,Cycle_Due_Date,Cycle_Result,Update_Count,'LUR' as p_Type,0 as Revision_Count from (select fc.case_sk,fp.case_process_rsn, decode((row_number() OVER (PARTITION BY fc.case_sk order by fp.case_process_rsn)-1), 0, 'Initial', 'Update') Cycle_Type, fp.process_schedule_date Cycle_Start_Date, fp.process_end_date Cycle_End_Date, fp.process_schedule_end_date Cycle_Due_Date, (select process_status_desc from lkp_process_status lps where process_status_sk = fp.process_status_sk) Cycle_Result, (row_number() OVER (PARTITION BY fc.case_sk order by fp.case_process_rsn)-1) Update_Count from fct_case fc join fct_case_process fp on fc.case_sk = fp.case_sk join lkp_process lp on lp.process_sk = fp.process_sk join lkp_sub ls on fc.case_sub_id = ls.sub_sk where ls.sub_code in (83100) and lp.process_code in ( 51408, 83410, 83095) and fc.current_record_flag = 'Y' order by fc.case_sk, fp.process_schedule_end_date ) / End of WMU Requirement /

/ Begin of Mobility and Zoning Data Requiement - No Process Code / UNION ALL select case_sk,Cycle_Type,Cycle_Start_Date,case_process_rsn,Cycle_End_Date,Cycle_Due_Date,Cycle_Result,Update_Count,'LUR' as p_Type,0 as Revision_Count from (select fc.case_sk,fp.case_process_rsn, decode((row_number() OVER (PARTITION BY fc.case_sk order by fp.case_process_rsn)-1), 0, 'Initial', 'Update') Cycle_Type, fp.process_schedule_date Cycle_Start_Date, fp.process_end_date Cycle_End_Date, fp.process_schedule_end_date Cycle_Due_Date, (select process_status_desc from lkp_process_status lps where process_status_sk = fp.process_status_sk) Cycle_Result, (row_number() OVER (PARTITION BY fc.case_sk order by fp.case_process_rsn)-1) Update_Count from fct_case fc join fct_case_process fp on fc.case_sk = fp.case_sk join dim_case dc on fc.case_sk = dc.case_sk join lkp_process lp on lp.process_sk = fp.process_sk join lkp_type lt on dc.case_type_id = lt.type_sk where lt.type_code in ('GENP','UC' ,'ZC','RS') --and fc.case_in_date >TO_DATE('01/01/2015','mm/dd/yyyy') and fc.current_record_flag = 'Y' order by fc.case_sk, fp.process_schedule_end_date ) / End of Mobility and Zoning Data Requiement - No Process Code / )

TracyLinder commented 7 months ago

Update

Matiur would like us to add the cycle number to the existing data report in AMANDA. I put this ticket on the DTS-AMANDA Refinement agenda for 2/27. I need to ask the devs if the sql above will help with this.

TracyLinder commented 7 months ago

Next Steps

We will assign to a DEV for analysis for the next sprint beginning 3/6.
We are not committing to doing the work, just an analysis of DSD's code above.
Are we able to use it to add the cycle number column to our existing report?
How big is the effort to do so?

TracyLinder commented 6 months ago

Raj's Analysis

The query was written for power BI, so if we need to reuse this we need to know which report we want this change applied.
We can however use pieces of it to configure/update our report query
Raj will move forward with updating the query

TracyLinder commented 6 months ago

Hi @rajeshlaunch can you please provide an update on this?

rajeshlaunch commented 6 months ago

@TracyLinder I did some analysis on this query and found that the DSD is using a Revision process to track the cycle number for each process. We at ATD doesn't have that functionality, so it would be hard this query on a "as is" basis. I can talk to the stakeholders to find a way to figure out how are they doing revisions today in Amanda and then provide a solution/query to add this cycle number in the performance report.

rajeshlaunch commented 5 months ago

@TracyLinder Spoke to Matiur this morning and understood how ATD Engineering Review process is being used for revisions. I will add the AC for this card and provide solutions to add the Cycle number into the existing ticket.

rajeshlaunch commented 5 months ago

AC:

Add cycle number to the existing ATD Report "TDS AMANDA Review Data".
Count the number of occurrences of attempt result "Informal update" and "Rejected" under ATD Engineering Review process and add the count as the Cycle number the above mentioned report.

@vschandramouli Please review the AC and confirm

vschandramouli commented 4 months ago

Updated AC, moving to backlog. @rajeshlaunch @TracyLinder

SubhaShroff commented 4 months ago

@Nadin-Nader The report is ready for UAT on TEST. Thank you.

TracyLinder commented 3 months ago

I sent the data I exported to Matiur and asked him to test and provide feedback. I also showed him where the process cycle comment isn't lining up with our cycle column. We believe our cycle column is correct.

TracyLinder commented 3 months ago

Stakeholder Feedback

Hey Tracy, here are some quick observations while we’re still looking in the test data: • We are able to pull the report and it includes the cycle number • There are multiple records that show no cycle number, do you know why this is happening? If there is a record, there should be at least 1 cycle ◦ For example, the case number SP-2021-0311C has multiple cycles, but no cycle number is appearing in the report. This one was under ATD Engineering Review process name ◦ The case number SP-2022-0122D has multiple cycles, but no cycle number is appearing in the report. This one was under ATD Engineering Review process name ◦ The case number C8-2021-0091.0A has 1 cycle, but no cycle number is appearing in the report. This one was under ATD Engineering Review process name ◦ The same is true for multiple cases • For the case status, do you know why everything is showing “closed”? • There are multiple records that the report shows a higher number of cycles than there actually are ◦ For example, The case number SP-2021-0253C has 5 cycles, but the report is showing 7 cycles. This one was under ATD Engineering Review process name ◦ The case number SP-2021-0149C has 5 cycles, but the report is showing 6 cycles. This one was under ATD Engineering Review process name ◦ The case number SP-2021-0338C5 has 7 cycles, but the report is showing 8 cycles. This one was under ATD Engineering Review process name ◦ The case number 2023-114677 PR has 4 cycles, but the report is showing 8 cycles. This one was under ATD SIF Review

Developer Updates

[ ] Update query to count all processes regardless of attempt type
[ ] Add a temporary column for process RSN
[ ] Remove Column K "Process Comment"
[ ] Troubleshoot why the cycle count is sometimes higher than the actual number of cycles. For example, for RSN 12690789, the report shows 7 cycles when there are only 6.