This discussion is archived
10 Replies Latest reply: Jun 13, 2013 8:30 AM by cbentley RSS

How do I troubleshoot Class Build tests?

cbentley Newbie
Currently Being Moderated
Hi I'm new to Oracle Data Mining and I am using the SQL Developer GUI to do a few concept proofs. I was able to go through the tutorials successfully - however when I use my own data I am running into the following message on the Class Build node during the Test phase of the model build.

Message:
Test failed due to ORA-00910: specified length too long for its datatype .
Detail:
ORA-06512: at "ODMRSYS.ODMR_ENGINE_MINING", line 1643 ORA-06512: at "ODMRSYS.ODMR_ENGINE_MINING", line 1986

Any ideas what might be wrong? What's the best way to find what's causing the error?

Any assistance anyone could provide would be greatly appreciated.

Edited by: Chris Bentley on Jun 3, 2013 4:57 PM
  • 1. Re: How do I troubleshoot Class Build tests?
    Mark Kelly Oracle ACE
    Currently Being Moderated
    Hi,
    Can you provide the following:
    SQL Dev version
    DB version.
    Run a Describe on input source.

    You mention that the failure was in the test phase.
    So were the models built successfully?
    Are you able to pass the models into a Apply node and score them?

    Thanks, Mark
  • 2. Re: How do I troubleshoot Class Build tests?
    cbentley Newbie
    Currently Being Moderated
    Thanks Mark for responding...

    I'm using
    SQL Developer version 3.2.20.09 on
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

    Yes the models were built successfully...I'll try to pass the models into a Apply node and score them.

    Here's a describe of my input source..prior to building a Text transform on the CHIEF_COMPLAINT field.

    TABLE pop_visit_info
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    KIDS_VISIT_ID NOT NULL NUMBER(20)
    KIDS_PAT_ID NUMBER(20)
    HOSP_ACCT_ID NUMBER(20)
    CLINICAL_INPATIENT_YN NUMBER(1)
    OUTPATIENT_YN NUMBER(1)
    ED_YN NUMBER(1)
    SURGICAL_YN NUMBER(1)
    OBSERVATION_YN NUMBER(1)
    ICU_YN NUMBER(1)
    CICU_YN NUMBER(1)
    PICU_YN NUMBER(1)
    NICU_YN NUMBER(1)
    VISIT_START_DATE NOT NULL DATE
    VISIT_START_TIME NOT NULL NUMBER(4)
    VISIT_STOP_DATE NOT NULL DATE
    VISIT_STOP_TIME NOT NULL NUMBER(4)
    LENGTH_OF_STAY_DAYS NUMBER(4)
    LENGTH_OF_STAY_HRS NUMBER(8,2)
    DSCH_DISPO VARCHAR2(100)
    VISIT_ADDRESS_1 VARCHAR2(100)
    VISIT_ADDRESS_2 VARCHAR2(100)
    VISIT_CITY VARCHAR2(50)
    VISIT_STATE VARCHAR2(12)
    VISIT_ZIP VARCHAR2(10)
    VISIT_COUNTY VARCHAR2(50)
    VISIT_START_AGE_DAYS NUMBER(5)
    VISIT_START_AGE_WEEKS NUMBER(4)
    VISIT_START_AGE_MONTHS NUMBER(4)
    VISIT_START_AGE_YEARS NUMBER(3)
    VISIT_STOP_AGE_DAYS NUMBER(5)
    VISIT_STOP_AGE_WEEKS NUMBER(4)
    VISIT_STOP_AGE_MONTHS NUMBER(4)
    VISIT_STOP_AGE_YEARS NUMBER(3)
    FACILITY VARCHAR2(2)
    FIRST_DEPT VARCHAR2(255)
    LAST_DEPT VARCHAR2(255)
    FIRST_HOSP_SVC VARCHAR2(100)
    LAST_HOSP_SVC VARCHAR2(100)
    PRIMARY_PAYOR VARCHAR2(50)
    FIN_CLASS VARCHAR2(50)
    NEXT_KIDS_VISIT_ID NUMBER(20)
    PREV_KIDS_VISIT_ID NUMBER(20)
    VENTILATOR_YN NUMBER(1)
    CVVH_YN NUMBER(1)
    CODE_YN NUMBER(1)
    PEWS_GTE_6_YN NUMBER(1)
    RESEARCH_STUDY_YN NUMBER(1)
    ECMO_YN NUMBER(1)
    DIALYSIS_YN NUMBER(1)
    DECEASED_YN NUMBER(1)
    VENTILATOR_DAYS NUMBER(5)
    LINE_DAYS NUMBER(5)
    RECORD_DELETED_YN NUMBER(1)
    UPDATE_DTM DATE
    VISIT_START_DTM DATE
    VISIT_STOP_DTM DATE
    NEXT_IP_ED_IC_KIDS_VISIT_ID NUMBER(20)
    PREV_IP_ED_IC_KIDS_VISIT_ID NUMBER(20)
    IMMEDIATE_CARE_YN NUMBER(1)
    VISIT_SEQ NUMBER(6)
    ADMISSION_SOURCE VARCHAR2(100)
    TRANSFER_SOURCE VARCHAR2(100)
    DISCHARGE_DESTINATION VARCHAR2(100)
    CHIEF_COMPLAINT VARCHAR2(1000)
    VISIT_COUNTRY VARCHAR2(50)
    FINANCIAL_CLASS_GROUP VARCHAR2(50)
    ED_LENGTH_OF_STAY_HRS NUMBER(8,2)
    ICU_LENGTH_OF_STAY_HRS NUMBER(8,2)
    INPATIENT_LENGTH_OF_STAY_HRS NUMBER(8,2)
    URGENT_CARE_LENGTH_OF_STAY_HRS NUMBER(8,2)
    ED_LENGTH_OF_STAY_DAYS NUMBER(6,2)
    ICU_LENGTH_OF_STAY_DAYS NUMBER(6,2)
    INPATIENT_LENGTH_OF_STAY_DAYS NUMBER(6,2)
    URGENT_CARE_LENGTH_OF_STAY_DYS NUMBER(6,2)
    CC_FEVER_YN NUMBER(1)
    CC_COUGH_YN NUMBER(1)
    CC_SORE_THROAT_YN NUMBER(1)
    ILI_YN NUMBER(1)
    NEUROSURGERY_YN NUMBER(1)
    APR_DRG NUMBER(3)
    APR_DRG_ASTHMA_YN NUMBER(1)
    APR_DRG_SICKLE_CELL_YN NUMBER(1)
    APR_DRG_DIABETES_YN NUMBER(1)
    APR_DRG_VENT_SHUNTS_YN NUMBER(1)
    CASE_MGT_ADM_PLANNED_YN NUMBER(1)
    CASE_MGT_ADMITS_PAST_12_MONTHS VARCHAR2(30)
    CASE_MGT_ADM_PAST_30_DAYS_YN NUMBER(1)
    CASE_MGT_ED_VISIT_PAST_72HR_YN NUMBER(1)
    HB_TOT_CHGS NUMBER(22)
    APR_DRG_CHEMO_YN NUMBER(1)
    APR_DRG_REHAB_YN NUMBER(1)
    RETURN_PREV_KIDS_VISIT_ID NUMBER(20)
    RETURN_48_HR_YN NUMBER(1)
    RETURN_72_HR_YN NUMBER(1)
    RETURN_7_DAY_YN NUMBER(1)
    RETURN_30_DAY_YN NUMBER(1)
    READMIT_PREV_KIDS_VISIT_ID NUMBER(20)
    READMIT_48_HR_YN NUMBER(1)
    READMIT_72_HR_YN NUMBER(1)
    READMIT_7_DAY_YN NUMBER(1)
    READMIT_30_DAY_YN NUMBER(1)
    VISIT_REASON VARCHAR2(2000)
    ED_DISPO VARCHAR2(50)
    FIRST_INPATIENT_BED_DATE DATE
    FIRST_INPATIENT_BED_TIME NUMBER(4)
    FIRST_INPATIENT_BED_DTM DATE
    DISMISSED_YN NUMBER(1)
    DX_TYPE NOT NULL VARCHAR2(50)
    DX_RANK NOT NULL NUMBER(3)
    KIDS_DX_ID NUMBER(20)
    DX_PRESENT_ON_ADMIT_YN NUMBER(1)
    DX_PRESENT_ON_ADMIT_UNKNOWN_YN NUMBER(1)
    DX_PRESENT_ON_ADMIT_EXEMPT_YN NUMBER(1)
    DX_CODE VARCHAR2(15)
    DX_NAME VARCHAR2(255)
    DX_DESCRIPTION VARCHAR2(500)
    DX_CODING_SYSTEM VARCHAR2(20)
    EPIC_DX_ID NUMBER(10)
    REASON_RANK NUMBER(3)
    REASON VARCHAR2(100)
    NOTE VARCHAR2(1000)
  • 3. Re: How do I troubleshoot Class Build tests?
    Denny Wong Explorer
    Currently Being Moderated
    Hi

    I don't see anything wrong from your dataset.

    Can you export your workflow and post it here?

    Also, a sample (a few rows) of the dataset will be helpful.

    Thanks,
    Denny
  • 4. Re: How do I troubleshoot Class Build tests?
    cbentley Newbie
    Currently Being Moderated
    I think I may have found the issue..

    I attempted to apply the build and it still failed, however in addition to the original error messages another message appeared:

    Message:
    Target DX_NAME exceeds maximum distinct values. Maximum number of distinct values allowed is 1000.
    Detail:
    None

    I had over 1300 distinct values in my Target. I created another data set from that data so that my data only had 300 distinct values and the test failed again.

    I created yet another set so that my data only contained 5 distinct target values and it ran successfully.

    The DX_NAME target is a VARCHAR2(255) so I'm guessing the limitation was exceeded.

    I looked through the documentation and was not able to find the technical limitations of the models. I may have overlooked it...can someone point me to where I can find that info?

    Thanks,
    Chris
  • 5. Re: How do I troubleshoot Class Build tests?
    Denny Wong Explorer
    Currently Being Moderated
    Hi

    By default, we limit the target distinct count to <= 1000 for classification model build. But that can be adjusted in the ODMRSYS.ODMR$REPOSITORY_PROPERTIES property table (change the value in the MAX_TARGET_DISTINCT_COUNT property).

    Please run this query and let us know the result:

    select max(length(DX_NAME)) from pop_visit_info;

    Is your dataset multi-bytes?

    It help us to diagnose the problem if we can look at the specification of your workflow. If you can narrow down your workflow to a few nodes and post it here, we can take a closer look at it.

    Thanks,
    Denny
  • 6. Re: How do I troubleshoot Class Build tests?
    cbentley Newbie
    Currently Being Moderated
    select max(length(DX_NAME)) from pop_visit_info;

    MAX(LENGTH(DX_NAME))
    --------------------
    142
    1 row selected.

    The dataset is not multibyte.

    What's the best way to post the specifications of my workflow? (Sorry - I've been out of the Oracle world for a while...but glad to be back :-) )

    Chris
  • 7. Re: How do I troubleshoot Class Build tests?
    Denny Wong Explorer
    Currently Being Moderated
    Hi Chris

    You can put the workflow in Google Doc or some third party provider and post the link here.

    If the workflow is small (e.g. couple nodes), then you may post it here. If you post it here, please use this format:
     workflow data  
    .

    Thanks
    Denny
  • 8. Re: How do I troubleshoot Class Build tests?
    cbentley Newbie
    Currently Being Moderated
    DATA_SOURCE ------> BUILD TEXT -------> CLASS BUILD
    | |
    | |
    DATA SOURCE ------> APPLY TEXT --------> APPLY
  • 9. Re: How do I troubleshoot Class Build tests?
    Denny Wong Explorer
    Currently Being Moderated
    Hi

    It seems your workflow contains 3 nodes (DATA_SOURCE, BUILD TEXT, CLASS BUILD). Since your data set contains many columns, the exported workflow could be quite sizable. Is it possible to save it to a Google Drive or Dropbox account and provide me a link to it? So I can download the workflow and try to reproduce the problem in house.

    Thanks,
    Denny
  • 10. Re: How do I troubleshoot Class Build tests?
    cbentley Newbie
    Currently Being Moderated

    Denny,

     

    I apologize for the delay ... as I mentioned I'm fairly new to Oracle Data Mining and wanted to make sure I wasn't sharing sensitive information.

     

    https://www.dropbox.com/s/f1roplpw85h24hk/PATIENT_DX_2.xml

     

    Chris

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points