Versions Compared

Old Version 1

changes.mady.by.user Julie Schneider

Saved on

compared with

New Version Current

changes.mady.by.user Julie Schneider

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Overview AnchorTopTop

Statement paragraph

General Report Information AnchorGeneral Report Information

Overview

TBD

General Report Information

Report Name:
BIA SOR DW DQ

Report
Report
Title:
Data Warehouse Data Quality Report

Data Set Name:
BIA SOR DW DQ Report

Contact:BIA
SOR
Data Analytics and Reporting Team
Contact email:
aisteam@usf
it-analytics@usf.edu
Contact phone number:(813) 974-2867
Purpose
:The purpose is to check for data issues associated with Data Warehouse ETL loads. Info
iconfalse
titleTable of Contents
Page Names
:
  • 12915944338
  • 12915944338
  • 12915944338
  • 12915944338
  • 12915944338
  • 12915944338
  • 12915944338
  • 12915944338
  • 12915944338
    • 12915944338
  • 12915944338

    • Page Name: DWHOUSE Access Requests AnchorDWHOUSE Access RequestsDWHOUSE Access RequestsData Quality Check:Saved and approval pending DWHOUSE user and service account requestsDescription:This data quality check returns DWHOUSE user account and service account requests in SAVED and/or PENDING approval status (i.e. Id requests). If an account request is in SAVED status for 3 or more days or is pending a UDSC group approval, the request needs to be addressed. For instructions, see DWHOUSE Id Requests in SAVED or PENDING Status.  

    Back to Top

    Page Name: Invalid Characters in Comments AnchorInvalid Characters in CommentsInvalid Characters in CommentsData Quality Check:Invalid Characters in Table/View CommentsDescription:This data quality check returns Data Warehouse tables and/or view owned by MARTIAN that have comments containing an invalid character, the ampersand (&) or the single quote ( ' ). If not for a step in the nightly DAL process that removes any ampersands and replaces any single quotes ( ' ) with double quotes ( " ), these characters would cause the DAL process to fail. This step makes changes to the comments only for the DAL views that get created. The comments in the source table/view need to be modified.Data Quality Check:Invalid Characters in Column CommentsDescription:This data quality check returns Data Warehouse column comments (in tables/views owned by MARTIAN) that contain invalid characters, the ampersand (&) or the single quote ( ' ). These invalid characters would cause the nightly DAL process to fail if not for a step in the process that removes any ampersands and replaces any single quotes ( ' ) with double quotes ( " ). This process makes changes to the comments only for the DAL views that get created. The comments in the source need to be modified.

    Back to Top

    Page Name: Table/View Name Too Long AnchorTable/View Name Too LongTable/View Name Too LongData Quality Check:Table/View Name Too Long for DAL ProcessDescription:This data quality check returns Data Warehouse tables/views owned by MARTIAN that have a name longer than 27 characters, but do not have a name like ODS%, %DIRV, '%DV1, %DV2, or %DV3. When a table/view name is longer than 27 characters the nightly DAL process will fail because the DAL view name for that source table/view would be longer than Oracle allows when the identifying DAL characters are added to the end of the name ( _DAL1, _DAL2, or _DAL3). The table/view name will need to be shortened.

    Back to Top

    Page Name: Table/View Comments w/ Return AnchorTable/View Comments w/ReturnTable/View Comments w/ReturnData Quality Check:Table/View Comments with a Carriage ReturnDescription:This data quality check returns Data Warehouse tables/views owned by MARTIAN with comments that contain a hard return. A carriage return would cause the nightly DAL process to fail if not for a step in the process that removes hard returns. This process makes changes to the comments only for the DAL views that get created. The comments in the source need to be modified. Data Quality Check:Column Comments with a Carriage ReturnDescription:This data quality check returns Data Warehouse column comments (in tables/views owned by MARTIAN) that contain a hard return. A carriage return would cause the nightly DAL process to fail if not for a step in the process that removes hard returns. This process makes changes to the comments only for the DAL views that get created. The comments in the source need to be modified. 

    Back to Top

    Page Name: AIS DAL User w/out User Account AnchorAIS DAL User w/out User AccountAIS DAL User w/out User AccountData Quality Check:DAL users exists in AIS_DAL_USER that do not have a user account in DWHOUSE.Description:

    A DAL user records exists in MARTIAN.AIS_DAL_USER, but that person does not have a user account in ALL_USERS. The orphaned record in AIS_DAL_USER needs to be removed. Submit a JSD incident to the service team: Enterprise Systems Security requesting the removal of the record from AIS_DAL_USER.

    Back to Top

    Page Name: DAL Privileges Missing AnchorDAL Privileges MissingDAL Privileges MissingData Quality Check:DAL Users are Missing DAL PrivilegesDescription:A DAL user in the table MARTIAN.AIS_DAL_USER is missing DAL privileges (DAL1, DAL2, or DAL3) in the table DBA_ROLE_PRIVS. This could mean the user is no longer employed by USF or the process that creates the DAL privileges did not create the privileges. In the latter case, check the AppWorx process IT_DAL_C.Data Quality Check:DAL Views are Missing DAL PrivilegesDescription:

    A DAL view in the table ALL_VIEWS owned by MARTIAN (name will end in D0, D1, D2, or D3) does not have DAL privileges in the table DBA_TAB_PRIVS (grantee <> DAL1, DAL2, or DAL3). This most likely means the process that creates the DAL privileges did not create the privileges. Check the AppWorx process IT_DAL_C.

    Back to Top

    Page Name: DW Accounts Missing or Orphaned AnchorDW Accounts Missing or OrphanedDW Accounts Missing or OrphanedData Quality Check:DWHOUSE Accounts are Missing in the AIS_DAL TablesDescription:

    A DWHOUSE user account or service account exists in DBA_USERS, but it does not exist in the table MARTIAN.AIS_DAL_USER or MARTIAN.AIS_DAL_SYSTEM_ID. If a user account is missing in AIS_DAL_USER, submit a JSD incident to the service team Enterprise Systems Security requesting the record be added to AIS_DAL_USER. If a service account is missing in AIS_DAL_SYSTEM_ID, add the record to AIS_DAL_SYSTEM_ID using the System ID Maintenance page https://usfpro1.forest.usf.edu/pls/ax_dwhouse/f?p=1057:102:0::NO.

    Data Quality Check:DWHOUSE Accounts are Orphaned in AIS_DAL TablesDescription:

    A DWHOUSE user account or service account exists in the table MARTIAN.AIS_DAL_USER or MARTIAN.AIS_DAL_SYSTEM_ID, but it does not exist in the table ALL_USERS. If a user account is orphaned in AIS_DAL_USER, submit a JSD incident to the service team Enterprise Systems Security requesting the removal of the record from AIS_DAL_USER. If a service account is orphaned in AIS_DAL_SYSTEM_ID, delete the record in AIS_DAL_SYSTEM_ID using the System ID Maintenance page https://usfpro1.forest.usf.edu/pls/ax_dwhouse/f?p=1057:102:0::NO. 

    Back to Top

    Page Name: Invalid Information in AIS_DAL_SYSTEM_ID AnchorInvalid Information in AIS_DAL_SYSTEM_IDInvalid Information in AIS_DAL_SYSTEM_IDData Quality Check:Contacts in AIS_DAL_SYSTEM_ID are No Longer Employed by USFDescription:

    A contact in the table MARTIAN.AIS_DAL_SYSTEM_ID is no longer employed by USF (i.e. emplid not in martian.gems_appointment_curr_view where empl_status_code = 'A').

    Look up the contact's previous position in GEMS_APPOINTMENT to determine who the contact reported to while in that previous position. Send an email to that person asking who the new contact should be for the service account. Once you receive that information, update the service account record with the new contact's emplid using the System ID Maintenance page https://usfpro1.forest.usf.edu/pls/ax_dwhouse/f?p=1057:102:0::NO. 

    Data Quality Check:Contacts in AIS_DAL_SYSTEM_ID have a department Id different than their department Id in GEMSDescription:

    A contact in the table MARTIAN.AIS_DAL_SYSTEM_ID has a department Id in AIS_DAL_SYSTEM_ID that is different from the contact's department Id in GEMS (the source for the GEMS department Id is martian.gems_appointment_curr_view.deptid).

    When a contact's department Id does not match their GEMS department Id, it means the contact has changed departments. Look up the contact's previous position in GEMS_APPOINTMENT to determine who the contact reported to while in that previous position. Send an email to that person asking who the new contact should be for the service account. Once you receive that information, update the service account record with the new contact's emplid using the System ID Maintenance page https://usfpro1.forest.usf.edu/pls/ax_dwhouse/f?p=1057:102:0::NO. 

    Back to Top

    Page Name: DWHOUSE Tables Have Unusable Indexes AnchorDWHOUSE Tables Have Unusable IndexesDWHOUSE Tables Have Unusable IndexesData Quality Check:DWHOUSE Table Indexes are UnusableDescription:A DWHOUSE table owned by MARTIAN, DATA_HUB, or BOG has an index with a status = 'UNUSABLE' in ALL_INDEXES. Check to see if there is an ETL process running that drops and recreates indexes. That's usually the case. 

    Back to Top

    Page Name: DWHOUSE Views are Invalid AnchorDWHOUSE Views are InvalidDWHOUSE Views are InvalidData Quality Check:DWHOUSE Views are InvalidDescription:A DWHOUSE view owned by MARTIAN  DATA_HUB, or BOG has a status = 'INVALID' in DBA_OBJECTS. Usually the view will validate if a simple select statement is ran against it.Back to Top

    Page Name: TDB

    Data Quality Check:
    Description:



    IT Analytics, SVC4010, Contact Us