Archive Admissions Tables

On March 4, 2014, ASU_A_ADM (also known as PS_ASU_A_ADM) will be renamed to ASU_A_ADM_ARCH to help ensure that users of this data understand its archived state. Queries that require the frozen data in ASU_A_ADM / PS_ASU_A_ADM must be updated to reference the archive table instead. No column names will be changed. 

If you want to continue to use these inactive tables, here is what you need to do to modify your query:

•        for queries using ASU_A_ADM, please change the table’s Physical name from  ASUDW.ASU_A_ADM to ASUDW.ASU_A_ADM_ARCH

•        for queries using PS_ASU_A_ADM, please change the table’s Physical name from SYSADM.PS_ASU_A_ADM to ASUDW.ASU_A_ADM_ARCH

To see a specific list of reports using either table, please click https://docs.google.com/a/asu.edu/spreadsheet/ccc?key=0AhOZY7Jpd1s1dEtfc2R2QmRwb2YwZlc2TUc3VGpXRHc.  This list contains the suggested contact(s), location and name of report.  We have created a help article (http://help.asu.edu/sims/selfhelp/SelfhelpKbView.seam?parature_id=8373-8193-7884) for instructions on how to change a table name.

Queries that need current/up-to-date admissions information should be adjusted to use the new Admissions Fact tables (ASU_ADM_APPLICATION, ASU_ADM_APPLICANT, ASU_ADM_INTL_STUDENT and ASU_ADM_APP_FIRST_AD_SNP) or other tables/views as needed.

If you need assistance in modifying your existing query, please contact the BI Reporting team at myreports@asu.edu.

Data Warehouse: enhancements and changes - NTNL_ID in PS_ASU_D_PERSON_VW

 

Enterprise Data Warehouse (PMPRD)

Scheduled Date: Wednesday, Jan 29 2014 6:30am - 8:00am

Summary:

In the enterprise data warehouse (PMPRD), the NTNL_ID field in PS_ASU_D_PERSON_VW will begin to be populated with the last four characters of the person's primary national ID from PS_PERS_NID. Previously, the NTNL_ID field was left intentionally blank ("-") for all individuals.

Please contact datawarehouse@asu.edu with questions or concerns about this change.

Thank you,

UTO Data Warehouse team

Data Warehouse Enhancements and Changes - January 21, 2014

There are several upcoming changes to Data Warehouse admissions tables.

Enterprise Data Warehouse (PMPRD)

Summary:
1.      The following ASU_ADM_APPLICATION field changes are related to logic only; there are no database schema changes.
•        APPLICATION_STATUS: the sequence of the logic was adjusted for Grad applications, such that the Enrolled status will take precedence over every variation of Admitted status (e.g. Admitted – Provisions). Jira case(s): AD-642
•        NEW_UNDERGRAD_STATUS_CODE: due to recent changes in registrar handling of exchange students, the logic to determine this status code was adjusted, to include NONDEGREE, in addition to FRESHMAN and TRANSFER, when querying Student Profile tables. Jira case(s): AD-649

Note: the above logic changes will be migrated to production the evening of Monday 01/20/14; the logic will first be applied - and therefore visible in ASU_ADM_APPLICATION – the next morning when the DW load runs during its normal time, which is typically before 7am.

Table(s):
ASU_ADM_APPLICATION

Affected fields:
APPLICATION_STATUS
NEW_UNDERGRAD_STATUS_CODE

2.      Changes to DW tables loaded from PeopleSoft CSPRD table PS_AUDIT_ASU_AACAR (Jira case(s) DW-360, AD-645, DW-359):
•        PS_F_AUDIT_ASU_AACAR: add indexes to improve query performance and create a primary key to be used by the DataStage map. This DW table will continue to store only UGRD records.
•        PS_F_AUDIT_ASU_AACAR _NONUGRD: This new table will contain GRAD and all other academic careers except UGRD. This DW table will be updated nightly from CSPRD table PS_AUDIT_ASU_AACAR.

Table(s):
PS_F_AUDIT_ASU_AACAR
PS_F_AUDIT_ASU_AACAR_NONUGRD (new)


Coming Soon (4th Announcement):

Tuesday, March 4, 2014 8:00pm – 10:00pm

The obsolete Admissions Fact table (ASU_A_ADM, a.k.a. PS_ASU_A_ADM) is being deprecated using a phased approach:

As of 12/31/13, this table is no longer updated. The table will still be available for queries for a period of time, but the data will no longer be updated. Complete 12/31/13

On March 4, 2014, ASU_A_ADM (also known as PS_ASU_A_ADM) will be renamed to ASU_A_ADM_ARCHIVE (the new name is tentative), to help ensure that users of this data understand its archived state. Queries that require the frozen data in ASU_A_ADM / PS_ASU_A_ADM must be updated, to reference the archive table instead. No column names will be changed. Queries that need up-to-date admissions information should have been adjusted to use the new Admissions Fact tables (ASU_ADM_APPLICATION, ASU_ADM_APPLICANT, ASU_ADM_INTL_STUDENT and ASU_ADM_APP_FIRST_AD_SNP) or other tables/views as needed.  Jira case(s): AD-589

Please contact datawarehouse@asu.edu with questions or concerns about these changes. 

Thank you,
UTO Data Warehouse team

Data Warehouse Enhancements and Changes - December 2013

Student Warehouse changes
Summary:
SDSP: The logic for SDSP (Sun Devil Success Program) fields is being enhanced to use a cross reference table to determine which student group(s) are associated to this program. This change also enables these fields to start populating for 2147 and beyond. Jira case(s): AD-349, AD-626

Note: the logic changes will be migrated to production on Monday evening 12/9/13; the logic will first be applied - and therefore visible in ASU_ADM_APPLICATION - on Tuesday morning when the DW load runs during its normal time, which is typically before 7am.

Table:
ASU_ADM_APPLICATION

Affected fields:
GRE_VERB
GRE_QUANT
GRE_ANLY_WRITING
GRE_VERB_PERCENTILE
GRE_QUANT_PERCENTILE
GRE_ANLY_WRITING_PERCENTILE
SDSP_FLG
SDSP_DT


Summary:
Below are two changes related to the Admissions fact data warehouse tables. Please contact datawarehouse@asu.edu with questions or concerns about these changes.

The obsolete Admissions Fact table (ASU_A_ADM, a.k.a. PS_ASU_A_ADM) is being deprecated using a phased approach:

As planned, as of December 31, 2013, we will stop refreshing this table. The table will still be available for queries for a period of time, but the data will no longer be updated. The data warehouse team invites feedback at datawarehouse@asu.edu.

After approximately two months (early March 2014), the table will be renamed, and general queries will no longer have access to it. By that time, queries that need up-to-date admissions information should have been adjusted to use the new Admissions Fact tables (ASU_ADM_APPLICATION, ASU_ADM_APPLICANT, ASU_ADM_INTL_STUDENT and ASU_ADM_APP_FIRST_AD_SNP) or other tables/views as needed. 
Jira case(s): AD-589


Summary:
These changes are related to logic only: there are no database schema changes.

GMAT: The logic to calculate GMAT scores in ASU_ADM_APPLICATION is being adjusted, to 1) use PS_STDNT_TEST_COMP rather than PS_ADM_APPL_MATLS, b) find each maximum test score per student per test component type, and 3) limit to tests taken within 5 years of the application date. This change applies to 2141 cohort term forward. This change mimics the change made to GRE score logic that went into Production on 12/10/13. Jira case(s): AD-638

Note: the logic changes will be migrated to production on Tuesday 12/31/13; the logic will first be applied - and therefore visible in ASU_ADM_APPLICATION - on Wednesday morning when the DW load runs during its normal time, which is typically before 7am.

Table:
ASU_ADM_APPLICATION

Affected fields:
GMAT_TOT
GMAT_PERCENTILE

Data Warehouse: Enhancements and Changes - November 2013

Financial Warehouse changes
Summary:
Because of data changes from the SunRise vendor, two new columns will be added to the SD_PO_DATA table.

Table:
SD_PO_DATA

New Columns:
GREEN_PRODUCT_DESCR
LEED_COMPLIANCE_DETAIL

 
Student Warehouse changes
Summary:
The Financial Aid view PS_ASU_FA_P_YR_VW is being changed in the Data Warehouse to accommodate changes made in the PeopleSoft source database

View:
PS_ASU_FA_P_YR_VW

Columns being removed:
ASU_FA_FAPROG

New Columns:
ASU_FA_FAFSA_DT

ASU_FA_PKG_DT_SUM
ASU_FA_TOT_LN_OFFR
ASU_FA_STD_LN_OFFR
ASU_FA_PRT_LN_OFFR
ASU_FA_NAMU_COHT
ASU_FA_NAMU_DT
ASU_FA_NAMU_AMT
ASU_FA_PROM_COHT
ASU_FA_PROM_DT
ASU_FA_PROM_AMT

Please contact datawarehouse@asu.edu with questions or concerns about these changes. 

Data Warehouse: Enhancements and changes - November 12, 2013

Several Data Warehouse changes are planned in the coming week. Details are below. Please contact datawarehouse@asu.edu with questions or concerns about these changes. 

Enterprise Data Warehouse (PMPRD)

Wednesday Nov 13 2013 8:00pm – 9:00pm
Per user request, CS view PS_ASU_AD113_MC_VW needs to be created and loaded in the Enterprise Data Warehouse.  The source view contains about 1M rows. 
A new table called ASU_AD113_MC_VW_P will be created in the ASUDW schema.
A new view called PS_ASU_AD113_MC_VW that selects from ASUDW. ASU_AD113_MC_VW_P will be created in the SYSADM schema. 
View SYSADM. PS_ASU_AD113_MC_VW will be exposed to the My Reports user community in the CS mart.


Thursday Nov 14 2013 8:00pm - 9:00pm
The following four tables from Student Athlete Academic Profile will be added to the Enterprise Data Warehouse:

• ASU_STD_ATH_PRF
• ASU_ATH_ELG_TRM
• ASU_ATH_LEARN
• ASU_ATH_NOTES

Monday Nov 18 2013 8:00pm - 9:30pm
There are several logic changes to the 'new' Admissions Fact application table in the Enterprise Data Warehouse:
1. Military Changes (Jira case AD-505):
• MILITARY_CONNECTION_CODE (formerly VETERAN_OR_DEPENDENT_FLG): set to A for Active, V for Veteran, D for Spouse/Dependent, U for Unknown
• MILITARY_STATUS: logic change, to form consistent values over time, as available application responses change
• MILITARY_BRANCH (formerly VETERAN_BRANCH): set to military branch of the military personnel, whether active military, veteran, spouse/dependent
The changes above include database schema changes to both ASU_ADM_APPLICATION and ASU_ADM_APPLICATION_SNP.
2. ADM_EVAL_COMPLETE_DT logic change: for reconsidered Grad apps, set ADM_EVAL_COMPLETE_DT to LAST_AP_ACTION_DT (Jira case AD-596).
3. Add Cross Reference table ASU_XREF to store crosswalks from value(s) to a value. Change CI Band and TRN GPA Band logic, to use ASU_XREF rather than hard coding the logic in procedural code. This allows translation changes without code migrations. (Jira cases AD-347, AD-348)
Expected User Impact: Queries that use ASU_ADM_APPLICATION and/or ASU_ADM_APPLICATION_SNP should be adjusted to use the new and changed Military and Veteran columns, listed in #1 above. Also, there will be brief (4-8 minute) unavailability of data in the tables ASU_ADM_APPLICANT, ASU_ADM_APPLICATION, ASU_ADM_INTL_STUDENT.


Coming Soon (Repeat Announcement):

Enterprise Data Warehouse (PMPRD)
The obsolete Admissions Fact table (ASU_A_ADM, a.k.a. PS_ASU_A_ADM) is being deprecated using a phased approach:

In the near future, we will stop refreshing this table. The table will still be available for queries for a period of time, but the data will no longer be updated. There is no firm date for this step, but the timeframe is before the end of November 2013. The data warehouse team invites feedback at datawarehouse@asu.edu.

After approximately two months (January 2014), the table will be renamed, and general queries will no longer have access to it. By that time, queries that need up-to-date admissions information should have been adjusted to use the new Admissions Fact tables (ASU_ADM_APPLICATION, ASU_ADM_APPLICANT, ASU_ADM_INTL_STUDENT and ASU_ADM_APP_FIRST_AD_SNP) or other tables/views as needed. 
Jira case(s): AD-589

Data Warehouse: Enhancements and changes - November 1, 2013

On Monday, November 4, 2013 between 8pm and 10pm:

Legacy Data Warehouse (enterprise1 FINANCIAL):
The COST_SHARE_FLAG field in the ACCOUNT table on enterprise1 FINANCIAL is being changed and renamed. This field was added to the data warehouse earlier this year. Currently, the field contains only Y or N. With this change, the flag will become a code. Possible values include Y, N, C and S. As such, the field is being renamed, from COST_SHARE_FLAG to COST_SHARE_CODE. Jira case(s): DW-322.

Enterprise Data Warehouse (PMPRD)
There are several logic changes to the 'new' Admissions Fact business rules for loading.
1. Last Stack: select min(stdnt_car_nbr), then apply Effective Dating logic, when crossing over to Student Records to get Last Program and related fields. Jira case(s): AD-594.
2. Measles Hold Flg logic: populate this flag only for admitted students. Students who are not [yet] admitted will have a value of NULL. Jira case(s): AD-582.
3. Fix typo in TRN_GPA_BAND (2.49 should say 3.49). Jira case(s): AD-595.
4. Technical Enhancements to improve load performance. Jira case(s): AD-575, AD-573.
There are no table changes, i.e. there are no dropped, new or changed columns.
Expected User Impact: brief (1-8 minute) unavailability of data in the tables ASU_ADM_APPLICANT, ASU_ADM_APPLICATION, ASU_ADM_INTL_STUDENT

Please contact datawarehouse@asu.edu with questions or concerns about these changes. 

Coming Soon:

Enterprise Data Warehouse (PMPRD)
The obsolete Admissions Fact table (ASU_A_ADM, a.k.a. PS_ASU_A_ADM) is being deprecated using a phased approach:

In the near future, we will stop refreshing this table. The table will still be available for queries for a period of time, but the data will no longer be updated. There is no firm date for this step, but the timeframe is before the end of November 2013. The data warehouse team invites feedback at datawarehouse@asu.edu.

After approximately two months (January 2014), the table will be retired (e.g. dropped) and queries will no longer have access to it. By that time, all queries that use the old Admissions Fact table should have been adjusted to use the new Admissions Fact tables (ASU_ADM_APPLICATION, ASU_ADM_APPLICANT, ASU_ADM_INTL_STUDENT and ASU_ADM_APP_FIRST_AD_SNP) or other tables/views as needed.