20210706 PBI Vaccine Compliance Reports

Date/Time/Location

Attendees

Attendee	Present	Notes
@brian Shively
@David Sanders
Tedi Tehrani
Ann Crawford (Unlicensed)

Meeting Goals and Expected Preparation

Goals

• Create compliance reports on Covid Vaccinations 
• Review new dataset 

Expected Preparation

review return to campus  conditions

Agenda

Discussion 

• Published dataset dataset_CovidVaccineCompliance5 in development  that uses the Registration dataflow in power bi to get registered student information; API for employees that show active employees with Job codes and job class. 
• Brian Shively hard coded a csv that maps department codes to department names.  This can be replaced by a control point  dataflow that Tedi Tehrani shared and the database below.
• The academic calendar has a bug in quarter from current. 
• We can only see current housing.  Need to see who is coming in the fall.
• We may need to generate a report that will show vaccination rates over time. 
• Ann Crawford (Unlicensed) will meet with @joe house to review the impact of two patient ids per person
• Control Point - Dataflow:
  • let
    #"Data Flow" = "Data Warehouse",
    #"Entity Name" = "Control Point",
    Source = PowerBI.Dataflows(null),
    #"FilteredWorkspace" = Table.SelectRows(Source, each ([workspaceName] = WorkspaceFilter)),
    #"Removed Other Columns" = Table.SelectColumns(#"FilteredWorkspace",{"Data"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"dataflowId", "dataflowName", "dataflowDescription", "Tags", "Data", "ItemKind", "ItemName", "IsLeaf"}, {"dataflowId", "dataflowName", "dataflowDescription", "Tags", "Data.1", "ItemKind", "ItemName", "IsLeaf"}),
    #"FilteredDataFlow" = Table.SelectRows(#"Expanded Data", each ([dataflowName] = #"Data Flow")),
    #"ExpandedData1" = Table.ExpandTableColumn(#"FilteredDataFlow", "Data.1", {"entity", "dataCategory", "Data", "Tags", "ItemKind", "ItemName", "IsLeaf"}, {"entity", "dataCategory", "Data", "Tags.1", "ItemKind.1", "ItemName.1", "IsLeaf.1"}),
    #"FilteredEntity" = Table.SelectRows(#"ExpandedData1", each ([entity] = #"Entity Name")),
    Data = #"FilteredEntity"{0}[Data],
    #"Renamed Columns" = Table.RenameColumns(#"Data",{{"Cluster Codes", "Cluster Codes 1"}, {"Cluster Name", "Cluster Name 1"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([cp2_code] = "VCST") and ([Department Code] <> "EOPA" and [Department Code] <> "EOPB" and [Department Code] <> "EOPC" and [Department Code] <> "EOPI" and [Department Code] <> "SIST")),
    #"Renamed Columns1" = Table.RenameColumns(#"Filtered Rows",{{"Cluster Codes 1", "Cluster Code"}, {"Cluster Name 1", "Cluster Name"}})
    in
    #"Renamed Columns1"

Action Items

• Create control point workflow in public Ann Crawford (Unlicensed)
• Correct academic calendar Quarter from current in academic calendar workflow Ann Crawford (Unlicensed)
• Brain storm report requirements WRT return to campus   - All
• Define the scope of the issue associated with having two patient ids for the same person.  How many people?  How many work at UCSB and also were a student?  How many have events associated with two different ids?  Ann Crawford (Unlicensed)
• Need a vaccination source dimension: UCSB, CAIR, Self Ann Crawford (Unlicensed)
• Possibly need to know the status of the CAIR query (no data, ambiguous, success)