New Site Plan PowerBI Report for LDE

[atdservicebot]

Create a new performance page on LDE's PowerBi dashboard.

---

Query Parameters

• Folder process code = 51212 (Land Development Engineering)
• The user should be able to query by:
  • Process Start and End Dates both should map to fp.tostart
  • (Optional) Reviewer_ Name fp.assignedto (or assigneduser)

---

For each KPI, the user wants to see this info:

1. No. of Reviews
2. % of On-Time Reviews

---

Logic

No. of Reviews:

• Total count of rows returned (processes)

% on Time

• % = (Count of on-time reviews / (count of total reviews)
• A review is considered on-time when fp.ended date is ≤ fp.toend

---

KPI Categories & Mapping

1. Total LDE Site Plan Reviews

• Folder type is SP
• Total count of Folder process code = 51215 (Land Development Engineering) within the query date range

2. LDE Site Plan Revisions

• TBD; Charlie is going to review the sample queries in the comments

3. LDE Site Plan Corrections

• Folder type is SC

4. LDE Site Plan Exemptions

• Folder type is DA
• Folder subtype is Exemptions

---

DTS SR Form Submittal

Describe the problem.

Need a dashboard/report built in order to report performance measures for the Land Development Engineering review process (if it could also include the ability to query by reviewer that would be helpful): • LDE Site Plan Reviews (# of reviews and % on time) • LDE Site Plan Revisions (# of reviews and % on time) • LDE Site Plan Corrections (# of reviews and % on time) • LDE Site Plan Exemptions (# of reviews and % on time)

How will we know that our solution is successful?

We will be able to report on the listed measures above for quarterly reports and the information will be helpful for individual performance measures.

Users

Elizabeth Boswell and Beth Robinson, potentially Gilbert Molina.

Sponsorship

Elizabeth Boswell

Which Strategic Direction 2023 “Mobility” indicators would this new application impact?

['Safety', 'None']

Which Austin Strategic Mobility Plan goals would this new application support?

['Health & Safety — Protect Austinites by lowering the risk of travel-related injury and promoting public health.', 'Affordability — Lower the cost of traveling in Austin by providing affordable travel options.', 'Sustainability — Promote integrated designs and quality additions to the built environment while reducing impacts and promoting efficient use of public resources.']

Describe any workarounds you currently have in place or alternative solutions you've considered.

I am logging initial site plan review distribution, but this does not capture resubmittals, corrections, or exemptions which would be too numerous to log.

Is there anything else we should know?

Next quarter reporting, end of August.

How have other divisions/departments/cities addressed similar challenges?

I think this is a common type of dashboard in DSD where other similar development reviews take place.

Requested By Elizabeth R.

Request ID: DTS24-115405

[TracyLinder]

Boswell said adding this to the recently created LA dashboard would be great.

[TracyLinder]

I added this to the Data Science refinement agenda for this week.

[TracyLinder]

The only folder/review process in use (that I can find) is Land Development Engineering (51212)

[TracyLinder]

More info needed

• [x] How does the system or user know if the review type is a review, revision, correction, or exemption?

I sent the question to Boz via teams.

[TracyLinder]

We can use this dashboard for inspiration

[TracyLinder]

Scoping 7/8/24 Tracy, Boz, Patrick, Gilbert, Ankush

• Correction - Very very minor update that doesn't impact much. These are on the SC folder.
• Revision - Substantial impact to the entire site.
• Exemption - A full blown siteplan is not required; a smaller version of SP. Possibly DA folder type. (DA folder type, subtype exemptions)

[TracyLinder]

Pending followup on finding revisions in the system. Gilbert has this task.

[TracyLinder]

Tracy Needs To:

• [x] Paste DSD's MS query as a comment
• [x] Get the query that Subha wrote for TDS Performance and add it to this issue
• [x] Get this assigned to Charlie and on deck

[TracyLinder]

DSD's MSTR Revision Query

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 / )

[SubhaShroff]

@TracyLinder Here is the query from 99028 report:

AMANDA TDS Report Query

SELECT f.foldertype, f.folderrsn, f.referencefile, f.foldername, vu.username Reviewer, fp.processrsn, vp.PROCESSDESC ProcessName, fp.scheduledate ToStart_Date, fp.scheduleEndDate DueDate, fp.startdate Started_Date, fp.enddate Ended_Date, vps.statusdesc process_status, ROW_NUMBER() OVER (PARTITION BY f.folderrsn, fp.processcode ORDER BY f.folderrsn, fp.processcode) cyclenumber ,pi.propinfovalue CouncilDistrict FROM folder f JOIN folderprocess fp ON fp.folderrsn = f.folderrsn AND fp.processcode IN (51132,51834,84102) JOIN validprocess vp ON vp.processcode = fp.processcode LEFT JOIN validuser vu ON vu.userid = fp.assigneduser JOIN validprocessstatus vps ON vps.statuscode = fp.statuscode JOIN propertyinfo pi ON pi.propertyrsn = f.propertyrsn AND pi.propertyinfocode = 52026 --Propertyinfo-Council District WHERE f.foldertype IN ('C8','SP','ZC','PR','CC') AND TRUNC(fp.scheduledate) >= {?FromStartDate} AND TRUNC(fp.scheduledate) <= {?ToStartDate} GROUP BY f.foldertype, vp.PROCESSDESC,f.folderrsn,f.referencefile, f.foldername,fp.processcode, fp.processrsn , vu.username,fp.scheduledate, fp.scheduleEndDate,fp.startdate,fp.enddate, vps.statusdesc, pi.propinfovalue ORDER BY vu.username, f.foldertype, vp.processdesc, fp.processrsn

[TracyLinder]

@Charlie-Henry this is ready for you and I'm putting it on deck. Just to recap our conversation today; I'm not sure how to count or identify an SP revision. I pasted DSD's query above for your reference. Maybe you'll be able sus out the revision logic.

[Charlie-Henry]

@TracyLinder

A review is considered on-time when fp.ended date is ≤ fp.toend

I don't see this toend column in folderprocess, I see a BASELINEENDDATE, I think that might be it. Just double checking

[Charlie-Henry]

Also, what folder process code do I need for these? Looks like 51215 returns no rows for foldertype SC?

3. LDE Site Plan Corrections Folder type is SC
4. LDE Site Plan Exemptions Folder type is DA Folder subtype is Exemptions

[TracyLinder]

Update

• [x] Charlie updated the query with the feedback given last week.
• [x] This sprint Charlie will work on creating the dashboard.
• [x] Tracy needs to learn how users identify SP Revisions in AMANDA
• [x] Tracy inform Boz that other folder types popped up; GC & UC. Should they be added to the scope? Also inform Boz that nothing is showing for DA folders.
• [x] Tracy schedule stakeholder meeting for the end of this sprint (around 9/3)

[TracyLinder]

I'm syncing with Boz & Gilbert this afternoon to clarify the outstanding items.

[TracyLinder]

Stakeholder Review 8/22/24

Notes

• [x] Don't include folders in the reporting for "C" folder type.
• A review isn't considered late until the day after the to_end date.
• [x] Add a "Cycle Number" filter
• Site Plan Revisions are identified by the folder revision column where FOLDERREVISION not in ('00', '0')
• [x] Add a column and filters for the Revision (y/n)
• [x] Add a filter for assigned to

[TracyLinder]

Next Steps:

• [ ] Charlie will make final updates
• [ ] LDE will review and provide feedback
• [ ] We will iterate until it's done.