1 2 Previous Next 17 Replies Latest reply: Feb 23, 2013 6:48 AM by 979335 RSS

    Aggregation Node error

    979335
      Hi
      I'm using SH.SALES to do try out using the Aggregation Node. But I get the following error below. I also give the steps I do.

      1. Create data source node and point it at SH.SALES
      2. Create the Aggregation Node and link the Data Source node to the Aggregation Node
      3. I want to do two aggregations. First one (a) based on overall sales for a customer and the next (b)based on overall sales by customer, by product

      for (a) above I double click on the Aggregation Node, the click on the '+'.
      In the Overall Aggregate Element window, I gave the Output the label of 'OVERALL_TOTAL'
      Column = 'AMOUNT_SOLD'
      Function = SUM()

      If I save this and then run the Aggregation Node, everything run fine and I can view the data.

      But when I double click on the Aggregation Node, the window appears but the Source column has an error symbol/triangle in it. I get the following error

      java.lang.NullPointerException
           at oracle.dmt.dataminer.workflow.dialog.aggregate.EditAggregationAttributePanel.initUI(EditAggregationAttributePanel.java:149)
           at oracle.dmt.dataminer.workflow.dialog.aggregate.AddAggregationAttributePanel.initUIComponents(AddAggregationAttributePanel.java:120)
           at oracle.dmt.dataminer.workflow.dialog.aggregate.EditAggregationAttributePanel.<init>(EditAggregationAttributePanel.java:60)
           at oracle.dmt.dataminer.workflow.dialog.aggregate.EditAggregationAttributeDialog.buildDialog(EditAggregationAttributeDialog.java:34)
           at oracle.dmt.dataminer.workflow.dialog.aggregate.EditAggregationAttributeDialog.buildDialog(EditAggregationAttributeDialog.java:21)
           at oracle.dmt.dataminer.workflow.dialog.data.DataConsumerAttributesDialog.invoke(DataConsumerAttributesDialog.java:310)
           at oracle.dmt.dataminer.workflow.dialog.aggregate.AggregationNodeAttributesPanel$6.mouseClicked(AggregationNodeAttributesPanel.java:539)
           at java.awt.AWTEventMulticaster.mouseClicked(AWTEventMulticaster.java:253)
           at java.awt.Component.processMouseEvent(Component.java:6300)
           at javax.swing.JComponent.processMouseEvent(JComponent.java:3275)
           at java.awt.Component.processEvent(Component.java:6062)
           at java.awt.Container.processEvent(Container.java:2039)
           at java.awt.Component.dispatchEventImpl(Component.java:4660)
           at java.awt.Container.dispatchEventImpl(Container.java:2097)
           at java.awt.Component.dispatchEvent(Component.java:4488)
           at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4575)
           at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4245)
           at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4166)
           at java.awt.Container.dispatchEventImpl(Container.java:2083)
           at java.awt.Window.dispatchEventImpl(Window.java:2489)
           at java.awt.Component.dispatchEvent(Component.java:4488)
           at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:668)
           at java.awt.EventQueue.access$400(EventQueue.java:81)
           at java.awt.EventQueue$2.run(EventQueue.java:627)
           at java.awt.EventQueue$2.run(EventQueue.java:625)
           at java.security.AccessController.doPrivileged(Native Method)
           at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:87)
           at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:98)
           at java.awt.EventQueue$3.run(EventQueue.java:641)
           at java.awt.EventQueue$3.run(EventQueue.java:639)
           at java.security.AccessController.doPrivileged(Native Method)
           at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:87)
           at java.awt.EventQueue.dispatchEvent(EventQueue.java:638)
           at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:269)
           at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:184)
           at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:178)
           at java.awt.Dialog$1.run(Dialog.java:1047)
           at java.awt.Dialog$3.run(Dialog.java:1099)
           at java.security.AccessController.doPrivileged(Native Method)
           at java.awt.Dialog.show(Dialog.java:1097)
           at java.awt.Component.show(Component.java:1591)
           at java.awt.Component.setVisible(Component.java:1543)
           at java.awt.Window.setVisible(Window.java:843)
           at java.awt.Dialog.setVisible(Dialog.java:987)
           at oracle.bali.ewt.dialog.JEWTDialog.runDialog(JEWTDialog.java:395)
           at oracle.bali.ewt.dialog.JEWTDialog.runDialog(JEWTDialog.java:356)
           at oracle.ide.dialogs.WizardLauncher.runDialog(WizardLauncher.java:55)
           at oracle.dmt.dataminer.workflow.dialog.data.DataConsumerAttributesDialog.invoke(DataConsumerAttributesDialog.java:342)
           at oracle.dmt.dataminer.workflow.WorkflowContextMenu.handlePrimaryEdit(WorkflowContextMenu.java:1564)
           at oracle.dmt.dataminer.workflow.WorkflowContextMenu.handleEvent(WorkflowContextMenu.java:1976)
           at oracle.ide.controller.IdeAction.performAction(IdeAction.java:529)
           at oracle.diagram.sdm.interaction.SDMSelectInteractor.performEdit(SDMSelectInteractor.java:285)
           at oracle.diagram.core.interaction.CoreSelectInteractor.mouseClicked(CoreSelectInteractor.java:1002)
           at oracle.diagram.core.interaction.CoreSelectInteractor.processMouseEvent(CoreSelectInteractor.java:395)
           at ilog.views.IlvManagerViewInteractor.processEvent(Unknown Source)
           at ilog.views.IlvManagerView.processEvent(Unknown Source)
           at java.awt.Component.dispatchEventImpl(Component.java:4660)
           at java.awt.Container.dispatchEventImpl(Container.java:2097)
           at java.awt.Component.dispatchEvent(Component.java:4488)
           at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4575)
           at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4245)
           at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4166)
           at java.awt.Container.dispatchEventImpl(Container.java:2083)
           at java.awt.Window.dispatchEventImpl(Window.java:2489)
           at java.awt.Component.dispatchEvent(Component.java:4488)
           at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:668)
           at java.awt.EventQueue.access$400(EventQueue.java:81)
           at java.awt.EventQueue$2.run(EventQueue.java:627)
           at java.awt.EventQueue$2.run(EventQueue.java:625)
           at java.security.AccessController.doPrivileged(Native Method)
           at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:87)
           at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:98)
           at java.awt.EventQueue$3.run(EventQueue.java:641)
           at java.awt.EventQueue$3.run(EventQueue.java:639)
           at java.security.AccessController.doPrivileged(Native Method)
           at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:87)
           at java.awt.EventQueue.dispatchEvent(EventQueue.java:638)
           at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:269)
           at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:184)
           at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:174)
           at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:169)
           at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:161)
           at java.awt.EventDispatchThread.run(EventDispatchThread.java:122)


      I delete the aggregation.

      For my second example, if I create an aggregation using the following
      Output = OVERALL_TOTAL
      Column = AMOUNT_SOLD
      Function = SUM()
      Sub Group by = CUST_ID, PROD_ID


      Again everything runs OK. When I view the data I get a Nested Column details, but no values. How can I translate this aggregation in actual data values ?

      When I double click on the Aggregation Node I get the same error as above

      I'm running SQL Dev 3.2.20.09 Build Main 9.87
      On an 11.2.3 DB
        • 1. Re: Aggregation Node error
          Mark Kelly-Oracle
          Hi,
          I have not been able to recreate this on the current code base.
          Can you see if the problem still appears if you do not change the auto generated output column name?
          Thanks, Mark
          • 2. Re: Aggregation Node error
            979335
            Hi Mark

            Yes I get the same error, if I let ODM generate the output coluimn and if I defined the output column name

            java.lang.NullPointerException
                 at oracle.dmt.dataminer.workflow.dialog.aggregate.EditAggregationAttributePanel.initUI(EditAggregationAttributePanel.java:149)
                 at oracle.dmt.dataminer.workflow.dialog.aggregate.AddAggregationAttributePanel.initUIComponents(AddAggregationAttributePanel.java:120)
                 at oracle.dmt.dataminer.workflow.dialog.aggregate.EditAggregationAttributePanel.<init>(EditAggregationAttributePanel.java:60)
                 at oracle.dmt.dataminer.workflow.dialog.aggregate.EditAggregationAttributeDialog.buildDialog(EditAggregationAttributeDialog.java:34)
                 at oracle.dmt.dataminer.workflow.dialog.aggregate.EditAggregationAttributeDialog.buildDialog(EditAggregationAttributeDialog.java:21)
                 at oracle.dmt.dataminer.workflow.dialog.data.DataConsumerAttributesDialog.invoke(DataConsumerAttributesDialog.java:310)
                 at oracle.dmt.dataminer.workflow.dialog.aggregate.AggregationNodeAttributesPanel$6.mouseClicked(AggregationNodeAttributesPanel.java:539)
                 at java.awt.AWTEventMulticaster.mouseClicked(AWTEventMulticaster.java:253)
                 at java.awt.Component.processMouseEvent(Component.java:6300)
                 at javax.swing.JComponent.processMouseEvent(JComponent.java:3275)
                 at java.awt.Component.processEvent(Component.java:6062)
                 at java.awt.Container.processEvent(Container.java:2039)
                 at java.awt.Component.dispatchEventImpl(Component.java:4660)
                 at java.awt.Container.dispatchEventImpl(Container.java:2097)
                 at java.awt.Component.dispatchEvent(Component.java:4488)
                 at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4575)
                 at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4245)
                 at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4166)
                 at java.awt.Container.dispatchEventImpl(Container.java:2083)
                 at java.awt.Window.dispatchEventImpl(Window.java:2489)
                 at java.awt.Component.dispatchEvent(Component.java:4488)
                 at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:668)
                 at java.awt.EventQueue.access$400(EventQueue.java:81)
                 at java.awt.EventQueue$2.run(EventQueue.java:627)
                 at java.awt.EventQueue$2.run(EventQueue.java:625)
                 at java.security.AccessController.doPrivileged(Native Method)
                 at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:87)
                 at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:98)
                 at java.awt.EventQueue$3.run(EventQueue.java:641)
                 at java.awt.EventQueue$3.run(EventQueue.java:639)
                 at java.security.AccessController.doPrivileged(Native Method)
                 at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:87)
                 at java.awt.EventQueue.dispatchEvent(EventQueue.java:638)
                 at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:269)
                 at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:184)
                 at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:178)
                 at java.awt.Dialog$1.run(Dialog.java:1047)
                 at java.awt.Dialog$3.run(Dialog.java:1099)
                 at java.security.AccessController.doPrivileged(Native Method)
                 at java.awt.Dialog.show(Dialog.java:1097)
                 at java.awt.Component.show(Component.java:1591)
                 at java.awt.Component.setVisible(Component.java:1543)
                 at java.awt.Window.setVisible(Window.java:843)
                 at java.awt.Dialog.setVisible(Dialog.java:987)
                 at oracle.bali.ewt.dialog.JEWTDialog.runDialog(JEWTDialog.java:395)
                 at oracle.bali.ewt.dialog.JEWTDialog.runDialog(JEWTDialog.java:356)
                 at oracle.ide.dialogs.WizardLauncher.runDialog(WizardLauncher.java:55)
                 at oracle.dmt.dataminer.workflow.dialog.data.DataConsumerAttributesDialog.invoke(DataConsumerAttributesDialog.java:342)
                 at oracle.dmt.dataminer.workflow.WorkflowContextMenu.handlePrimaryEdit(WorkflowContextMenu.java:1564)
                 at oracle.dmt.dataminer.workflow.WorkflowContextMenu.handleEvent(WorkflowContextMenu.java:1976)
                 at oracle.ide.controller.IdeAction.performAction(IdeAction.java:529)
                 at oracle.diagram.sdm.interaction.SDMSelectInteractor.performEdit(SDMSelectInteractor.java:285)
                 at oracle.diagram.core.interaction.CoreSelectInteractor.mouseClicked(CoreSelectInteractor.java:1002)
                 at oracle.diagram.core.interaction.CoreSelectInteractor.processMouseEvent(CoreSelectInteractor.java:395)
                 at ilog.views.IlvManagerViewInteractor.processEvent(Unknown Source)
                 at ilog.views.IlvManagerView.processEvent(Unknown Source)
                 at java.awt.Component.dispatchEventImpl(Component.java:4660)
                 at java.awt.Container.dispatchEventImpl(Container.java:2097)
                 at java.awt.Component.dispatchEvent(Component.java:4488)
                 at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4575)
                 at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4245)
                 at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4166)
                 at java.awt.Container.dispatchEventImpl(Container.java:2083)
                 at java.awt.Window.dispatchEventImpl(Window.java:2489)
                 at java.awt.Component.dispatchEvent(Component.java:4488)
                 at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:668)
                 at java.awt.EventQueue.access$400(EventQueue.java:81)
                 at java.awt.EventQueue$2.run(EventQueue.java:627)
                 at java.awt.EventQueue$2.run(EventQueue.java:625)
                 at java.security.AccessController.doPrivileged(Native Method)
                 at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:87)
                 at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:98)
                 at java.awt.EventQueue$3.run(EventQueue.java:641)
                 at java.awt.EventQueue$3.run(EventQueue.java:639)
                 at java.security.AccessController.doPrivileged(Native Method)
                 at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:87)
                 at java.awt.EventQueue.dispatchEvent(EventQueue.java:638)
                 at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:269)
                 at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:184)
                 at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:174)
                 at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:169)
                 at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:161)
                 at java.awt.EventDispatchThread.run(EventDispatchThread.java:122)
            • 3. Re: Aggregation Node error
              979335
              When I create a grouping based on, say, CUST_ID and PROD_ID, I get the output appearing with nested columns.

              Is there a way to see the actual data produced or at what point is that evaluated ?
              • 4. Re: Aggregation Node error
                Mark Kelly-Oracle
                Yes, if you do a sub-grouping, then that specific aggregation is a nested table of DM_NESTED_NUMERICALS or DM_NESTED_CATEGORICALS, which allows it to be input as a mining column to ODM model build.
                The sql is sufficient to see the nested table column data, you do not have to persist it.
                I think the View Data in the release you are using may have some limitations on allowing you to view the column details of the nested data column.
                Alternatively, you can copy the sql from the View Data - SQL Tab and paste it into SQL Dev Worksheet.
                They have a column viewer there that should handle it.
                As for the Aggregation Node bug, it appears to be fixed in the current release (SQL Dev 4.0) that is due out in Early Adopter release.
                I am still going to review the old release to see if I can recreate it there.
                That may be able to provide some workarounds.
                Thanks, Mark
                • 5. Re: Aggregation Node error
                  979335
                  Thanks Mark

                  When is SQL Dev 4.0 EA due out ?

                  Here is the SQL that the aggregation node generates

                  When I run it in SQL Dev it seems to take a very long time i.e. it is still running after 15 minutes. The script running task graphic still says it is running

                  WITH
                  /* Start of sql for node: SALES */
                  "N$10007" as (select /*+ inline */ "SALES"."PROMO_ID",
                  "SALES"."TIME_ID",
                  "SALES"."CHANNEL_ID",
                  "SALES"."CUST_ID",
                  "SALES"."PROD_ID",
                  "SALES"."QUANTITY_SOLD",
                  "SALES"."AMOUNT_SOLD"
                  from "SH"."SALES" )
                  /* End of sql for node: SALES */
                  ,
                  /* Start of sql for node: Aggregate */
                  "N$10024" as (SELECT
                  t0."AMOUNT_SOLD_SUM"
                  FROM
                  (SELECT
                       CAST(COLLECT(DM_NESTED_NUMERICAL(ID1, "AMOUNT_SOLD_SUM")) AS DM_NESTED_NUMERICALS ) "AMOUNT_SOLD_SUM"
                       FROM
                            (SELECT
                            ("CUST_ID" || ' - ' || "PROD_ID") ID1,
                            SUM("AMOUNT_SOLD") "AMOUNT_SOLD_SUM"
                            FROM "N$10007"
                            GROUP BY (("CUST_ID" || ' - ' || "PROD_ID")) )
                       ) t0
                  )
                  /* End of sql for node: Aggregate */
                  select * from "N$10024";
                  • 6. Re: Aggregation Node error
                    Mark Kelly-Oracle
                    I can't give a date for SQL Dev 4.0 but it should be coming out pretty soon.
                    The aggregation query may be pretty expensive, so try having the output of the Aggregation node go to the Create Table node.
                    Then you can just view the resulting table.
                    Thanks, Mark
                    • 7. Re: Aggregation Node error
                      979335
                      I've tried that but the resulting table has one column with all the nested description in it (e.g. DM_NESTED_...........). No data values are displayed.
                      The data type in the create table node is DM_NESTED_NUMERIC
                      How can I get the data values
                      • 8. Re: Aggregation Node error
                        979335
                        If i dropped the Repos and went back to a previous version of SQL Dev, say 3.1 for example, Would the Aggregation Node work? or will I encounter the same problem?
                        I'm just looking for a way to get the Aggregation Node to work everything else seems to work without any problems
                        • 9. Re: Aggregation Node error
                          Mark Kelly-Oracle
                          If you have already persisted the result to a table, then you can do something like:
                          select ams.* from OUTPUT_1_2, TABLE(amount_sold_sum_1) ams;
                          Or from sql (obtained from the SQL tab of View Data off of Aggregation node)
                          I just edited the final line in the sql below to include the TABLE() operation.
                          Mark
                          WITH 
                          /* Start of sql for node: SALES */
                          "N$10001" as (select /*+ inline */ "SALES"."PROMO_ID", 
                          "SALES"."TIME_ID", 
                          "SALES"."CHANNEL_ID", 
                          "SALES"."CUST_ID", 
                          "SALES"."PROD_ID", 
                          "SALES"."QUANTITY_SOLD", 
                          "SALES"."AMOUNT_SOLD" 
                          from "SH"."SALES"  )
                          /* End of sql for node: SALES */
                          ,
                          /* Start of sql for node: Aggregate */
                          "N$10002" as (SELECT 
                           t0."AMOUNT_SOLD_SUM", 
                           t1."AMOUNT_SOLD_SUM_1"
                          FROM
                            (SELECT  SUM("AMOUNT_SOLD") "AMOUNT_SOLD_SUM"
                               FROM "N$10001" ) t0,
                            (SELECT 
                               CAST(COLLECT(DM_NESTED_NUMERICAL(ID2, "AMOUNT_SOLD_SUM_1")) AS DM_NESTED_NUMERICALS  ) "AMOUNT_SOLD_SUM_1"
                               FROM
                                    (SELECT   
                                    ("PROD_ID") ID2,
                                    SUM("AMOUNT_SOLD") "AMOUNT_SOLD_SUM_1"
                                    FROM "N$10001" 
                                    GROUP BY (("PROD_ID")) )
                                )  t1
                           )
                          /* End of sql for node: Aggregate */
                          select ams.* from "N$10002", TABLE(AMOUNT_SOLD_SUM_1)ams ;
                          • 10. Re: Aggregation Node error
                            979335
                            Thanks Mark - That works :-)
                            • 11. Re: Aggregation Node error
                              Mark Kelly-Oracle
                              Tested SQL Developer 3.2.2 RTM Version 3.2.20.09 Build MAIN-09.87 on 11.2.0.1 and there were no problems with the Aggregation Node.
                              So, it is hard to provide guidance without being able to recreate the problem.
                              Some additional questions that may help...
                              Are you having any other issues with running workflows, such as building models etc?
                              Was this a clean install or was this a migration?
                              What OS are you running the client on?
                              What client locale settings are used?
                              Was the db recently upgraded?
                              What JDK is SQL Dev using (view the sqldeveloper.conf file in the directory: C:\<install directory>\sqldeveloper\sqldeveloper\bin and look for the SetJavaHome property value)?
                              Thanks, Mark
                              • 12. Re: Aggregation Node error
                                979335
                                Hi Mark
                                See my answers below

                                Are you having any other issues with running workflows, such as building models etc?
                                all seems to be working find, clustering , classification, apply, sample, explore
                                Was this a clean install or was this a migration?
                                I upgraded to 11.2.0.3 a few weeks ago. Then download SQ Dev 3.2.2 and upgraded the repository (I let SQL Dev/ODM to do). I was using 11.2.0.1 DB and SQL Dev 3.1 prod
                                What OS are you running the client on?
                                Windows 7 64bit : DB and SQL Dev on the one machine (laptop)
                                What client locale settings are used?
                                English (Ireland)
                                Was the db recently upgraded?
                                see above re. 11.2.0.1 to 11.2.0.3 As this is a full install, the only migration would have been the via the DB migration tool. All completed without any errors
                                What JDK is SQL Dev using (view the sqldeveloper.conf file in the directory: C:\<install directory>\sqldeveloper\sqldeveloper\bin and look for the SetJavaHome property value)?
                                JDK_1.6
                                • 13. Re: Aggregation Node error
                                  Mark Kelly-Oracle
                                  We will retest the migration to see if that triggers the problem.
                                  In the meanwhile, you could try and drop the repository and reinstall it from 3.2.2.
                                  You will lose all your workflows, so you would want to export workflows to your file system prior to the drop.
                                  Thanks, Mark
                                  • 14. Re: Aggregation Node error
                                    Mark Kelly-Oracle
                                    Testing install of SQL Developer 3.1 RTM 3.1.07 Build MAIN-07.42 on 11.2.0.1 and then upgrading repository to SQL Developer 3.2.2 RTM Version 3.2.20.09 Build MAIN-09.87 did not trigger the Aggregation node bug.
                                    It may be then that the db migration affected the XML DB configuration for the ODMr repository in some way.
                                    We will be testing a db upgrade to validate.
                                    Thanks, Mark
                                    1 2 Previous Next