10/1/2017 – 10/31/2017
Completed
- Automation of the ETL process for Legacy data the ones which are complete.
- Student Mapping Complete.
- Additional columns added to AR Bliss Tech specifications.
- Re-established the access to SISSHARE registrar database.
- SZRADAP Extension script sent for review with Tom Braun.
In Progress
- Summer Archive tables created in ODS.
- ETL development for legacy summer tables.
- Working with mapping of legacy registrar data into Composite table (STUDENT_COURSE) columns.
- DCE Course Information - I&C SCI X426.53 Data Structures, Data Mining and Big Data with Python class began.
Planned
- Continue to work with the remaining technical leads to identify the legacy data connectivity.
- Start the ETL Development of the known Legacy inputs.
9/1/2017 – 09/29/2017
Completed
- Summer ETL Access established.
- Grad Aid Staging development put on hold as the source data development is in progress.
- Grad Aid Change to column names completed.
- Automation of the ETL process for Legacy data the ones which are complete.
In Progress
- Testing of ETL connectivity for summer Database.
- Extension on a table (SZRADAP)from Base line into ODS.
- Student Mapping.
- Time to degree report.
Planned
- Continue to work with the remaining technical leads to identify the legacy data connectivity.
- Start the ETL Development of the known Legacy inputs.
6/16/2017 – 08/31/2017
Completed
- Issue on ODI Application resolved.
- Grad Aid Staging development.
- Grad Aid (PPS Data & Quarterly Info) composite completed.
- Individual Access to Summer Database.
- UG Admission Legacy data.
- Financial Aid Legacy data.
- Grad Division Legacy data for tables requested by Rachel.
- Ethnicity Data.
- KFS Data.
- Soap UI Installed.
- KSAMS Webservice mapping complete.
- AR Bliss Tech specs.
- Automation of the ETL process for Legacy data the ones which are complete.
In Progress
- Establish the ETL connectivity for Summer database.
- Testing for KSAMS Webservice.
- ODI Work on Composite tables for remaining Grad Aid.
- Extension on a table (SZRADAP)from Base line into ODS.
Planned
- Continue to work with the remaining technical leads to identify the legacy data connectivity.
- Start the ETL Development of the known Legacy inputs.
5/19/2017 – 06/15/2017
Completed
- Access to Registrar Oracle Database.
- MySQL 32 bit version installed to support the creation of ODBC Data sources.
- Informatica connectivity for UG Admission Database.
- Informatica connectivity for OIR.
- Automation of the ETL process by using the parameters.
- Load the PPS data into ODS.
- Created the links for the ETL documentation in the Wiki.
- Created the list of Registrar composite tables that needed for reporting.
- ODS Admin KT - Tom Braun
- ODI Admin KT - Chris Chadbourne.
In Progress
- Grad Aid Staging development testing.
- Rework on the development for the staging table names from STG_ to ARCHIVE_.
- Establish the ETL connectivity for Summer database.
Planned
- Continue to work with the remaining technical leads to identify the legacy data connectivity.
- Ethnicity data rollup between the UCOP and IPEDS.
- Start the ETL Development of the known Legacy inputs.
- ODI work on Grad Aid tables.
5/1/2017 – 05/18/2017
Completed
- Grad Division - GRADM Staging Tables design and creation.
- Grad Division - GRADM Staging ETL Development.
- Grad Division - GROGS Staging Tables design and creation.
- Grad Division - GROGS Staging ETL Development.
- Grad Division - GRENR Staging Tables design and creation.
- Grad Division - GRENR Staging ETL Development.
- Performance Issue – Resolved.
- After the Native Oracle driver and SGA memory bump up.
- Grad Division - GRADM Staging source to target mapping specs.
- Grad Division - GROGS Staging source to target mapping specs.
- Grad Division - GRENR Staging source to target mapping specs.
- ODS Admin KT - Tom Braun
- ODI Admin KT - Chris Chadbourne.
In Progress
- Grad Aid Staging development testing.
- Establish the ETL connectivity for OIR and UG Admissions database.
Planned
- Continue to work with the remaining technical leads to identify the legacy data connectivity.
- Start the ETL Development of the known Legacy inputs.
4/17/2017 – 04/28/2017
Completed
- Grad Division - GRAADM Staging Tables design and creation.
- Grad Division - GRAADM Staging ETL Development.
- Performance Issue – Worked with the team (Ellucian & Nisha).
- Received the AWR report.
- Case created with Informatica.
- Grad Aid - Staging Tables design and creation.
- Grad Aid - Staging ETL Development.
- ODS Tech Design Specs - Financial Aid.
- ODS Admin KT - Tom Braun.
In Progress
- Grad Division - GRAADM Composite Tables Design and Creation.
- Grad Division - GRAADM Composite ETL Development.
- ODS Tech Design Specs - Financial Aid.
- Establish the ETL connectivity for OIR and UG Admissions database.
- Performance issue on the ODS instance.
Planned
- ODI Admin KT - 5/5/2017
- Performance issue on the ODS instance.
- Continue to work with the remaining technical leads to identify the legacy data connectivity.
- Start the ETL Development of the known Legacy inputs.
- Grad Aid - Staging ETL Testing waiting for source data.
4/3/2017 – 04/14/2017
Completed
- POC - Grad Division - Complete.
- Receiving the Registrar files for the legacy data input (SIF/SOC) - Complete.
- Performance Issue – Worked with Nisha to – Complete.
- Confirmation the list of source for Grad Aid - Complete.
- Development for Fund Detail Report - Complete.
- Financial Aid - Mapping spec Documentation.
...
- Performance issue on the ODS instance.
- Establish the connection for the known Legacy data inputs through Informatica.
- Continue to work with the remaining technical leads to identify the legacy data connectivity.
- Start the ETL Development of the known Legacy inputs.
- Grad Div - Development
3/20/2017 – 03/31/2017
Completed
- POC - Function Rewrite - Complete.
- Performance Issue – Worked with Nisha to identify the bottleneck – Complete.
- Grad Aid - Informatica connectivity established.
- Grad Div – Informatica connectivity established.
- Ticket created to cleanup Legacy schemas and corresponding users.
...
- POC - Grad Division
- Establish the connection for the known Legacy data inputs through Informatica.
- Continue to work with the remaining technical leads to identify the legacy data connectivity.
- Start the ETL Development of the known Legacy inputs.
- Grad Aid - Development.
- Financial Aid - Mapping spec Documentation.
3/6/2017 – 03/17/2017
Completed
- Demo Registrar POC Enrollment report - Complete.
- Support the POC on Enrollment report.
- Ticket created to drop the old schemas for legacy data – Done.
- Performance Issue - Loading data into KFS – complete Loaded 300,000 records in 2 min 25 secs and confirmed that it’s the ODS database issue on the poor performance.
- Informatica AWS testing - Complete
- Worked on establishing the firewall between the Grad DB and Informatica.
- Worked on establishing the firewall between the Admissions DB and Informatica.
- Worked on establishing the firewall between the OIR DB and Informatica.
- Rewriting the Financial Aid Database function (Identifying the student is a CA resident or not) in Informatica. – Development Complete.
- Testing of the POC function is on hold as the test environment does not have data in the underlying base tables.
...
- POC Demo – 3/13/2017
- Establish the connection for the known Legacy data inputs through Informatica.
- Continue to work with the remaining technical leads to identify the legacy data connectivity.
- Start the ETL Development of the known Legacy inputs.
2/20/2017 – 03/03/2017
Completed
- Registrar Enrollment report - POC work complete.
- Created a PowerPoint presentation on POC to have a walkthrough on the demo.
- Created indexes to address the report performance.
- Added unique identifier in MST_REG_STUDENT table.
- Added OLD and NEW student ID in the MST_REG_STUDENT table and loaded the same.
- Supported the POC report development activities.
- Creation of separate Legacy schema – Done.
- Discussed about the options for the poor performance of loading the data into ODS tables.
- Worked on establishing the firewall between the Grad DB and Informatica.
- Established the PC connectivity to UG Admissions MySQL DB.
- Established the PC connectivity to OIR database.
- Submitted a request to create a temporary schema in the KFS oracle Database.
- Submitted a ticket to establish the firewall connection between PC and KFSDEV database.
- Provided a brief summary to Chris Schultz regarding the performance issue.
- Rewriting the Financial Aid Database function (Identifying the student is a CA resident or not) in Informatica.
...
- POC Demo – 3/13/2017
- Establish the connection for the known Legacy data inputs through Informatica.
- Continue to work with the remaining technical leads to identify the legacy data connectivity.
- Start the ETL Development of the known Legacy inputs.
2/01/2017 – 2/17/2017
Completed
Met with the team to discuss the need for multiple schemas for legacy data, so will create only one legacy schema(UCI_LEGACY) for all different functional units.
Can we have one schema created to store all functional unit legacy data and name the tables accordingly to segregate the data and identify.
Staging Tables:
STG_REG_SIF
STG_REG_SOC
STG_FINAID_CODE
Composite Tables:
MST_REG_STUDENT
MST_REG_COURSE
Advantages are listed below by going through the above option.
One individual user account.
For reporting it’s easy to join the data or else need to create separate data sources for each of them.
ETL just one connection object created.
ETL code we don’t need to specify schema when you join data across different functional units.
...