6 Replies Latest reply on Jul 19, 2013 7:47 PM by vlloracle

    4.2.2 Data load wizard


      I'm trying to utilize error handling in data load wizard for both transformation rule and table lookups, but am failing in both cases:


      First of all, in both cases on "Data validation" step I'm just getting "FAILED" for the "ACTION" column, but there is no any error returned at all. I see ERROR on "Data Load Results" step, but this is definitely not what I want - we need to see all the problems/errors PRIOR to the final submit. Looks like this can be fixed by manually adding col48 to the report on "Data validation" step, but why isn't it working by default like this?


      Secondly, "Error message" setup parameter for both transformation rule and table lookup is completely ignored - I'm getting only generic error like "transformation rule failed" or "Failed to retrieve the lookup value". Is there any way to provide custom error messages? Am I doing something wrong?


      Thirdly. Ideally I'd like to have reports per each fail in correspondent column - if phone number is too long, for example, I'd like to see this message in correspondent column for correspondent record. The other column with other validation failed can have the other error message. I can relatively easily create something like this with transformation rule - embed all the validation in custom plsql function and return either the original column value (in case all validation passes) or my custom error message. But how can I mark the whole record as FAILED in this case if any of the column validation fails?


      Thanks a lot in advance!!!



        • 1. Re: 4.2.2 Data load wizard
          Patrick M-Oracle

          Hi Vlad,


          So far data loading feature does not display the exact error on each failed record. The idea was to make it easy for the end user to upload and get a report on inserted/updated and failed records. Also basic reason why each record i.e during transformation rules or data lookups, etc. The workaround is for the developer to use the debug and track the errors that have happen by searching errors that start with 'data loading' word.


          However, you are not the only one asking more detailed error messaging, and the new release will take care of this feature and we will be able to give much more detailed message on what has happen during the data upload process.




          • 2. Re: 4.2.2 Data load wizard

            Thanks a lot, Patrick!


            Understood, and waiting anxiously for new releases


            May I suggest splitting validation between column and row level? Any column validation failing can be shown in correspondent column, and row validation fail can be shown in already existing ERROR column.


            But do I understand correctly that "Error message" in current release is not used for transformation rules or lookups? Just need confirmation so I do not break my head trying to find the problem on my side ?


            Again - thanks a lot and good luck!



            • 3. Re: 4.2.2 Data load wizard
              Patrick M-Oracle

              Hi Vlad,


              You are right, the error message is not used. I appreciate also your input, and will consider them while implementing the new feature. However I believe most of the errors will be row level as of now data loading scans through the records and insert one by one. Regarding the column level suggestion, I think you mean the cases where the transformation rule fails completely ? if that is what you mean, that validation will be at data loading declaration and share components, developer will be able to make sure the code is correct and runs as it is intended to do. While loading the data, we will be reporting what is exactly happening during that process.


              Thanks for your inputs



              • 4. Re: 4.2.2 Data load wizard

                I cannot speak for all, but I think the best scenario of using data loading is something like this:


                1. Data upload. Works great, but I'd suggest adding Excel spreadsheet upload option as well. I believe with already existing Excel upload functionality, provided by new listener, it shouldn't be a big deal - both are already working with collections, so probably just slight effort can solve the problem. And many thanks for copy/paste functionality - works like a charm !

                2. You map the columns. This is already working nearly perfectly. But let me suggest something here also . At my work we have lot of business processes, that require data upload(mostly - Excel spreadsheets). And no matter what we do/ask/force our customers - they are all using slightly different column names. We are fighting with them for years, but no luck. So, in majority of cases we'll have to map columns manually anyway. But the good news - variations in column names are final. Honestly - not more then 10 per column. So, if we could assign spreadsheet column synonyms, that were mapped to the same actual table column - that would allow us to configure data load just once and greatly reduce manual mapping. I'm talking about something like: all spreadsheets columns, named "CUSTOMER PHONE", "CONTACT PHONE" (you name it), are mapped to the actual DB table column "PHONE" and are displayed in UI mapping drop down as "Phone". For me - looks like all you need is to allow using "Attribute dictionary" in order to configure this

                3. Data validation. For it I still think the best way would be to allow column validation/lookups - with validation failing error shown in place of the actual column. So, if the phone is 888123456799 - allow developer to show string "888123456799 is too long" for this column. Preferably - in red. The greatest way to handle it would be to make failed column a hyperlink and show ALL failed column validations in popup when user clicks on it. That is my dream . And allow to have row validation procedure, which handles the whole row validation. If ANY column validation fails or row validation fails - row is marked as FAILED.


                Just my 2 cents...


                Good luck!



                • 5. Re: 4.2.2 Data load wizard
                  Patrick M-Oracle

                  Hi Vlad,


                  Thanks again for your inputs, it is always great to get these from usability point of view.


                  1. I will update you later if this is something we are considering for the coming release but I dont see why not.

                  2. Your point makes sense, we never thought in this direction.. in our last release, we have added opportunity for the developer to rename the columns, so instead of having column names which are most of the time not user friendly, developer can be able to define the names that are more user-friendly. I see couple of issues, on how many synonyms can we allow, but it is definitely something to look at.

                  3. On your last point, that is exactly how we intent to have it in our coming release.



                  • 6. Re: 4.2.2 Data load wizard



                    And for #2 - have a look at "Attribute Dictionary" :

                    Synonyms - already there

                    "Format Mask" - already there and can be used to pre-populate "Date / Number Format" on mapping page

                    "Label" - already there and can be used for building filed map drop down on mapping page

                    "Default value" - already there and can be used for populating blank fields


                    For me - it looks like all you need is just allow to associate record from "Attribute Dictionary" with "Data Load"...