10 Replies Latest reply: Jun 13, 2013 10:30 AM by cbentley RSS

    How do I troubleshoot Class Build tests?

    cbentley
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        DATA_SOURCE ------> BUILD TEXT -------> CLASS BUILD
                        | |
                        | |
                        DATA SOURCE ------> APPLY TEXT --------> APPLY
                        • 9. Re: How do I troubleshoot Class Build tests?
                          Denny Wong
                          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

                            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