Ellucian ODS

Summary: ODS Migration

Description: We have the following scenario: our production is currently on ODS Release 2.1. Our goal is to do a complete (new) fresh install of ODS (Release 3.1). During this process, we would like to migrate our current ODS to point to a different database instance. How can we go about doing this task? How can one migrate an ODS?

I was unable to find any suggestions on this in any of the support sites, including the ODS listserv.

Summary: ODS 2.2: Configuring Metadata after Upgrade

Description: I found an earlier post for configuring metadata after the upgrade to ODS 2.2. According to the post, "defects are introduced with ODS 2.2 which will prevent Metadata from being displayed correctly." The document then goes on to describe how to fix the issue. For example, IF when you go to the InfoAccess Admin UI page > Information Access Options > Setup Parameters: Select Job in the 'Show All Internal Groups' & scroll down to the PUBLISH_METADATA row:

IF the row does not exist, the document suggests that you execute the following script in SQL*Plus as iaadmin:
ODS Home\iaadmin\dbscripts\mtvparmmetadatarecs.sql

*Question: when I go to execute this script with an @ sign preceding the command, I get a file could not be opened message.

Summary: Banner vs. ODS

Description: I have been given a task to compare the tables (and columns, if possible) that exist in both the ODS and our Banner production DB. This will thus pinpoint the tables (and/or columns) that do not exist in the ODS and give our users the ability to let us know what they see 'missing' from the ODS and what we can import, if need-be and if appropriate.

And so: I know that via the ODS Meta Data Reports List (inside the ODS Administrative Web Interface App), one can see the target reports & the source reports. Via this view, one can 'manually' do a comparison of tables/columns that exist in the ODS versus Banner. However, this is quite a tedious task.

Hence: my question consists of: is there a table within a particular schema (in the ODS or in production) that one can query to see the tables that exist in the ODS (target) versus the source. How would I go about writing a query to compare the source tables versus the target tables?

Summary: installing new ods3.1

Description: Instead of upgrading the existing ods2.x, we are installing a new ods3.1.

The 2.x version is still running, so this is not critical, but they would like to switchover to 3.x as soon as possible.

At step 3, importing and deploying owb etl mapping, I am receiving the following error...

Summary: OMB01073: LOCATION ODSMGRLOCATION not found OMB01511: Location ODSMGRLOCATION not found.

Description: I'm having the following problem at step 3 of the install guide...

Summary: LOADMFTTRANS_HISTORY 2 & 3 error: ORA-06502

Description: We are currently being upgraded from ODS 2.2 to ODS 3.1. At the moment, I am attempting to load ODS 3.1. Initially, I made sure to perform the following tasks:
Task 1 - Define General Preferences Address Type
Task 2 - Define Fiscal Years for Transaction History Loads in Finance
Task 3 - Load Finance into the ODS

When performing Task 3, particularly, initially running the LOADMFTTRANS_HISTORY process, the process completed with the status of 'error.'

The following errors exist in the ODS control report:
LOADMFTTRANS_HISTORY2: Error: Map package terminated with Exception.
ORA-06502: PL/SQL: numeric or value error: character string buffer too small...

Summary: Error sending JOB NOTIFICATION Email: ORA-01422: exact fetch returns more than requested number of r

Description: Our init files have been accordingly updated and the prior Oracle 6502 error no longer exists.

Now I am encountering the following error on most of the ODS data loads (although the jobs complete with a status of success):
Error sending JOB NOTIFICATION Email: ORA-01422: exact fetch returns more than requested number of rows.

Summary: Change Table Counter Utility - Refresh Processes?

Description: I ran the change table counter utility on our ODS 3.1.1 instance, to produce a report of the number of rows that are in the source system Change tables for each of the ODS composite tables. This report yielded approximately 20 mappings with row counts of > 0. Hence, for each of the founded 'mappings,' I went ahead and ran a single Update process. After running all of the corresponding update processes, I than ran the Change Table Counter Utility once more, hoping to now get 0 'counts' for all of the mappings. Unfortunately, the exact same 'mappings' are reporting with the exact same record counts (which I thought should have been resolved via the single mapping Update procedures). Is there something broken on our side or is there something that I am missing? Please assist.

Summary: MSTEnrollment versus ASEnrollment

Description: I am comparing MSTEnrollment on our ODS 3.1.1 side, where the count equals 865,892. Then, on the source side (BANINST1 schema > ASEnrollment view), the count for this table, however, is 130,997. Quite a big difference. I ran a refresh process to fix the MSTEnrollment table. Since this didn't do it, I then went ahead and performed the LOADMSTEnrollment process. Finally, since this didn't fix the problem either, I performed a deletemstenrollment and then a loadmst_enrollment (although I believe the load does the delete process in itself). Either way, the counts are still difference. Hence the posting of this question: Is this normal and should this be the case or do we have an issue in our system?

Summary: MSTStudentClassification Discrepancy

Description: On the Banner production side, we have a particular student that is displaying as a Senior. I.e., checking the SGVCLAS view under the BANINST1 schema, this user is classified as a Senior for the latest termcode of 200808. On the ODS side, however, this user is always being displayed as a Junior. For example, for the MSTStudentClassification ODS table, this user has the latest academic period of 200805 (versus 200808). On the Student and Enrollment view (ODS side): the same student is still classified as a Junior, however, in these two views, the student has the latest academic period of 200808. We are currently trying to figure out what is wrong in the process from getting the data from Banner to the ODS (in particular, the mststudent_classification view)? We are hoping that we don't have other students displaying this type of discrepancy...

Summary: RUNREP > WBRTPLATFORM_CONFIG password?

Description: We are in the middle of putting up an ODS 3.1.1 -or- 8.0 test instance. A CSScan was run on our ODS 2.2 production instance and one of the 'lossy errors' for UTF8 conversion is for user/schema RUNREP, table WBRTPLATFORM_CONFIG, and column password. The password here has strange foreign characters. How should we resolve this error? Or should we at all?

Summary: 34 objects invalidated after ods_upgrade to version 8

Description: Please see attachments for details, and help explain why we got so many invalids after the upgrade. My searches have yet to turn up anything. Thanks much for your assistance!

Summary: LOADMFTGRANT_FUND fails

Description: As requested, I am placing this as a separate SR. When running LoadMftGrant_Fund, the mapping fails with: "Mapping Failed. Error retrieving OWB Audit messages."
When logging in to OWB and validating this mapping, I get several errors...

Summary: ODS 8.0 Cognos Packages

Description: QE-DEF-0030 Expression parsing error

Summary: UPDATEMSTPRE_STUDENT error

Description: Upon running our nightly STUDENT REFRESH, the student refresh started to fail a few days ago. When looking at the Control Report via the ODS Administrative Application, the error exists for UPDATEMSTPRE_STUDENT:

Error Msg: ORA-01438: value larger than specified precision allows for this column
Target Name: MSTPRESTUDENT
Target Column: *

Error Msg: ORA-06502: PL/SQL: numeric or value error: number precision too large
Target Name: MSTPRESTUDENT
Target Column: MSTPRE0PSGPA_TRANSFERRE

Summary: Load HR versus Load Student

Description: After setting up our ODS 8.0 test instance, we attempted to run LOAD HR and then LOAD Student. To our surprise, LOAD HR completed successfully after 35 hours and 44 minutes of execution. LOAD STUDENT, on the other hand, completed successfully after 15 hours and 51 minutes of execution. In all of our previous versions of ODS: we have had the opposite experience, where LOAD Student would take the longest (up to 2-3 days). HR, on the other hand, would complete rather quickly. Could you please provide an explanation as to why the LOAD HR takes so much longer than LOAD Student? I would imagine the student load has a lot more tables, etc. to process?

Summary: Missing: AFPURCHASEORDER, AFPURCHASEORDERITEM, AFFIXEDASSETITEM

Description: After the ODS upgrade to ODS 8.0 (we were on ODS 2.2 prior): we are now unable to locate the following ODS views: AFPURCHASEORDER, AFPURCHASEORDERITEM, AFFIXEDASSETITEM. These views do not exist on the Banner side (BANINST1 schema).

Summary: PHRHIST_COMMENT replaced by?

Description: Hello, after our upgrade to Banner 8, we no longer see the PHRHIST_COMMENT column/field in the PHRHIST Banner table. Has this field been replaced by another table? If so, from which table can we now retrieve this field from? We use this field in several of our reports.

Summary: ODS 8.0 MSTSTUDENTCOURSE & MST_GPA tables: slow response time?

Description: Why does the MSTSTUDENTCOURSE table run 3-5 times faster on our ODS 2.2 version, versus the ODS 8.0 version?

Summary: MSTRACESLOT -- empty

Description: OUR MSTRACESLOT table in ODSMGR is empty (not populated). Running the student load and the LOADMSTRACE did not populate this table. How can we get this table populated so that we can use it for reporting purposes.

Summary: ODS82/upgrade/install/required_tablespaces.sql error

Description: Using the login.sql for the ODS 8.2 Install.
EXECUTING SCRIPT: REQUIREDTABLESPACES.SQL FILE: requiredtablespaces.sql
Error running script requiredtablespaces.sql:
ORA-20000: Retrieving source database link terminated with the following
error:ORA-20001: ERROR IN MGKTLIB.FGETMTVPARMEXTERNALCODE: EXTERNALCODE not found for GROUP: STAGE CONFIGURATION, CODE: SOURCE ALIAS

Summary: Error in LOADGENERAL and REFRESHGENERAL

Description: Error in Control report: ODSMGR.UPDATEMGTEVENT_INVITEE" has errors ORA-06508: PL/SQL: could not find program unit being called

Error on public synonym AGEVENTINVITEE
ORA-01775: looping chain of synonyms

Summary: MSTRACESLOT -- empty

Description: After ODS upgrade to 8.2 from 8.1, the mstraceslot is empty. We had this problem in 8.1 (see 1-513607331) due to patch 1-34UFQ0. We made the change from RACE to RACE Category in mskrace.sql, but after running the load MSTRACESLOT is still empty.

Summary: ODS 8.2 Cognos Package(s)

Description: The sgheodsBv82.zip file that we have on our ODS server is corrupt. We receive the following error when we attempt to extract the file: "...CRC failed in sgheodsbv\model.xml. The file is corrupt." The _DV file extracts just fine. Could you please provide us with a working BV file? Thanks in advance.

Summary: MSTSTUDENTCOURSE ODS discrepancy

Description: We are seeing a large discrepancy between the following two tables:
The MSTSTUDENTCOURSE table in the ODS and the SFRSTCR table in Banner

There are over 2,000 records that do not exist over in our MSTSTUDENTCOURSE table. Running the Student, General, and HR refreshes - does not resolve this issue. The refreshes complete Successfully. Why is it that the MSTSTUDENTCOURSE table is not being updated by our scheduled refreshes?

Summary: ODS 8.2 broken - discrepancies

Description: We are seeing very large ODS discrepancies: i.e., please see our prior request (which should be escalated to Critical).

For the current term (201005): we are also seeing the ODS version of the TBRACCD table having 100,000 rows less than the NFPO table (515,773 vs 415,142).
Many other tables seem to be affected. Please assist us in resolving these issues.

Summary: Refresh Student Error on MSKPRED.P_POPULATE: ORA-01422

Description:
We are receiving an Error on our Student Refreshes:
MSKPRED.P_POPULATE: Slot package error
ORA-01422: exact fetch returns more than requested number of rows

This tells us that in the mapping, a query is expecting one row (doing a SELECT INTO) -- and in our case, the results are more than one row. Hence the error.
Do you have a fix for this?

Summary: ODS/EDW Admin UI in production: Enrollment Management Mappings missing

Description: Looking through our ODS/EDW Administrative Interface for our ODS/EDW production: we do not see the option to load/refresh the Enrollment Management Mappings. This option does exist in our ODS/EDW test UI, however (under Options > Schedule a Process > Schedule Enrollment Management Mappings).

Summary: SFRSTCA rebuild/redeploy capture failure

Description: I have completely redeployed streams in test Banner and test ODS/EDW, specifically for issues encountered rebuilding SFRSTCA in production earlier this summer of 2010.

Capture, propagation and apply all worked fine for this one table after complete redeploy of streams. Once I rebuilt SFRSTCA in Banner source (alter table x move tablespace x), the capture kicked out a warning of:

WARNING: received KRVXOPERCANNOT_SUPPORT
knlldmm: gdbnm=NFTT.WORLD
knlldmm: objn=22784
knlldmm: objv=0
knlldmm: scn=43893625123
knlldmm: opnum=255

Summary: Student Load; Completing with Errors

Description: While attempting to load the student fraction of the ODS we encounter the following error:

MSKPRED.PPOPULATE Slot package error at: 17-JAN-2011 04:25:22
MSKPRED.PPOPULATE ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes
Running Slot Package: MSKREQR.P_POPULATE

Summary: patch p1-fsyqhv_edw8010112 errors

Description: EDW 8.1.1 patch import/deploy VLD-1134: Invalid location for operator - Configured location for operator does not exist or is not a valid location under the referenced module. - EDWHOLDINPUT "Exception thrown when running mapping: ORA-01403".

Summary: After moving tables, streams fail

Description: After moving some tables to a different tablespace in preparation for an upgrade. We are receiving the following error.

2011/06/19 07:11 26687 ORA-26687: no instantiation SCN provided for "PAYROLL"."PPRR 1
EFE" in source database "NFPO.WORLD"
ORA-06512: at "SYS.LCR$DDLRECORD", line 256
ORA-06512: at "ODSSTG.MGKSTRH", line 104
ORA-06512: at line 1

Summary: UPDATEMSTSTUDENT_CLASS Long Run Time

Description: The UPDATEMSTSTUDENT_CLASS mapping alone takes approximately an hour to run for us (nightly), regardless if only 9 records, for example, are being updated -or- as much as 4000 records. We are a bit surprised that 9 records alone take the same time (1 hr+) to run, as 4000 records. Is there some kind of inefficiency in this mapping, that we should be aware of or that we should address? Our ODS version is 8.2.

Summary: Health Check warning
Description: Question, I am now able to see and access the Staging table and we went ahead and ran the Staging Status Area report. The added tables appear in the STAGED_TABLES list.
However, could you please clarify what the following Warning message means? Is this something we should be concerned about....

Summary: ORA-01018 errors when running LOAD_HR in ODS-test.

Description: Please assist us in resolving the following errors that we are encountering in our ODS-test instance when running the HR Load.

Summary: ODS-production Staged Tables not Synchronizing

Description:
Please assist us with the following issue that we are experiencing in our ODS-production instance:
When comparing the Banner replicated tables in the ODS corresponding schemas (i.e., FIMSMGR.FABINVH, FIMSMGR.FARINVA, etc. etc.), we are getting a large number of discrepancies between the Banner source table and the Banner ODS table. The Refresh process doesn't appear to be updating the data properly. We have ran the ODS Stage Status Area report to confirm that the Streams process is working properly. Please see attached supporting documentation.
In attempting to Synchronize the ODS-prod staged tables via the following method, the counts remain the same and discrepancies between Banner source and target continue to exist.
Why is the following Synchronization method not working for us and how should we proceed to Synchronize the ODS staged tables?

Summary: need to resolve tables out of synch

Description: please see SR #: 1-1897152591 for details.
This has been turned over to DBA; I am unable to work on the existing SR or see existing work that has been done, so would like a new SR to be created.

Summary: ODS MST_GPA results?

Description: According to the ODS Meta Data, the ODS MST_GPA table key and frequency is as follows:
One row per person per GPA time indicator (time based or not) per GPA group (cumulative or period) per academic study area (level) per GPA type (institution or transfer) per academic period. The above combination should then yield one result.

We, however, have a scenario where the above combination yields two results (versus one) for a particular person/pidm. Does that mean that our ODS MSTGPA table is being utilized/loaded improperly? We have traced the two records to the Student SHRTGPA table. However, the two records for the particular shrtgpagpatypeind, shrtgpapidm, shrtgpalevlcode, and shrtgpatermcode have different results for:
SHRTGPATRITSEQNO, SHRTGPAHOURSATTEMPTED, SHRTGPAHOURSEARNED, GPAHOURS, QUALITYPOINTS. The Activity Date is identical.

Summary: can't enable constraints.

Description: Cleaned up tables and can't enable two constraints; get ORA-02298 errors:
alter table WBRTAUDITPARAMETERS ENABLE constraint APFKAE;
ORA-02298
alter table WBRTAUDITSTRUCT ENABLE constraint FKRTTRTD;
SQL Error: ORA-02298:

Summary: Missing columns in destination.

Description: In running the "Stage Area Status" report in our ODS-test instance (note: after the Oracle 11gR2 upgrade), the pstagechecks mapping yields the following error...

Summary: ODS load performance issues after Oracle 11g upgrade.

Description: We have noticed some performance issues with our ODS-test loads after the Oracle 11gR2 upgrade. For example, the HR Load prior to the 11g upgrade usually took around 20 hours to run to completion. After the Oracle 11gR2 upgrade, the HR Load is now taking 62-63 hours to complete. Likewise, the Student Load usually took 32 hours to complete; after the Oracle 11g upgrade, however, the Student Load has been running for 141+ hours (is in fact still running). Finance Load: usually took 9 hours to complete, now takes about 16 hours to complete; Financial Aid Load: usually took 6 hours to complete, now takes about 12 hours to complete. Have other clients experienced load performance issues after upgrading the ODS source and target to Oracle 11g? Please assist…
(Note: Other data loads are still running/being tested)

Summary: ORA-1406 in LOG MINER process after upgrading Banner Database from 10.2.0.4 to 11.1.0.7.6

Description: After upgrading Banner database from 10.2.0.4 to 11.1.0.7.6 and leaving ODS database on 10.2.0.4, streams ran fine for ~a day and then stopped with ORA-01406: fetched column value was truncated.

Summary: Error creating Streams DML rule for FAISMGR.RBBABUD. ORA-01031: insufficient

Description: I cloned our production banner db to a test db and ran : streamssourceclone_script.sql for our ods streams process as SYS, per FAQ 1-TMHHGC and received the following error...

Summary: I was executing sqlplus /nolog @odsupgrade80201.sql and had the following errors

Description: I was upgrading our ODS 8.2 to 8.2.1 and ran into this problem...

Summary: ODS 8.2.1 Reload failing after Upgrade

Description: Following the upgrade of our test ODS environment to 8.2.1, I am encountering an error when attempting to run the "Reload ODS 8.2.1 Data":

LOADMATPLEDGE ORA-20001: Exception thrown when running mapping: ORA-04067: not executed, package body "ODSMGR.LOADMATPLEDGE" does not exist ORA-06508: PL/SQL: could not find program unit being called:
LOADMATPLEDGE "ODSMGR.LOADMATPLEDGE"

Summary: ODS 8.2.1 Missing columns in MetaData & in Reporting View Warnings (Enrollment Management)

Description: We've upgraded our test system from ODS 8.2 to ODS 8.2.1. The pre-req patch of 1-O43S1Z was applied and the new “Reload Utility (v6)” Utility was installed.

In running the ODS Health Check, we are receiving the following warnings in relation to Banner Enrollment Management (RAP). Please help us to resolve these warnings.

Summary: ODS Descriptions for Code Values - APPLSUPLDATA_0x dups

Description: When going to the ODS > "Maintain Descriptions for Code Values" > Choosing Data Element "APPLSUPLDATA_01" for example, we see duplicates. However, when we attempt to delete one of the Code Descriptions, although the delete says it is successful, indeed the delete does not commit. Why is this not working for us?

Summary: ODS Health Check "parameter record" warnings

Description: When running the ODS Health Check, we are receiving the following warnings. Please help us resolve these for our ODS 8.2.1 instance. We have confirmed that indeed an MTVPARM record is missing for the %MSTGENERALSTUDENTTRM mappings, however, the two mappings are also showing as invalid in the ODSMGR schema. Have these mappings perhaps been deprecated along the way? The LOADMGTVALIDATIONADVANC mapping package, however, is rendering as valid, yet the parameter record indeed is not found in the IAADMIN.MTVPARM table. Please assist.

Summary: ODS Health Check type/length mismatch

Description: Greetings, please help us to resolve the following Warnings that we are currently receiving in running the ODS Health Check (version 8.2.1)...

Summary: ORA-26766: Unexpected redo pattern encountered by LogMiner

Description: In our production ODS we are getting the following error...

Summary: Need steps to be reviewed on how to drop and recreate a Banner table faismgr.rorstat

Description: Please review the attached document.

Summary: p1-17ccfl8_ods80200 - An unknown Deployment error

Description: This SR references the following SR, which has been escalated, since the issue also exists in our production environment. (SR # 1-2720743241)

Summary: Load MSTAdmissionsRequirement error

Description: We are currently receiving an error in our ODS 8.2.1 production instance, After upgrading our ODS production from 8.2 to 8.2.1, we are seeing a new error during the student load on the following mapping:

Loadmstadmissionsrequirement
Error Msg: ORA-12899: value too large for column "ODSMGR"."MSTADMISSIONSREQUIREMENT"."REQUIREMENTCOMMENT" (actual: 31, maximum: 30) (Next error)

Summary: Load Transaction History not loading in Dev environment.

Description: We are currently on ODS version 8.2.1. For some reason, in our ODS development environment, when running the LOAD Transaction History, the process completes in 18 seconds and never loads any records. Can you help us troubleshoot as to why this is so? Are we missing something on the Banner dev source or target? Perhaps some paramater? We do not see this occurring in our other environments.

This SR is Currently Open

Summary: ODS 8.3 preparation.

Description: In preparing to upgrade to ODS 8.3, we have installed Oracle 11gR2 and the necessary components in our ODS test/dev environment. In testing the ODS 8.2.1 version against Oracle 11gR2, we are currently encountering an error on the following mapping. In order to move forward with the actual ODS BI upgrade now (from 8.2.1 to 8.3), we need to resolve this error first. Please assist us in resolving this error.