3 Replies Latest reply on May 23, 2019 7:56 PM by Izik Warn

    Export to AW analytic workspace trouble & advice requested

    Izik Warn

      Hello - I am using Oracle SQL Developer Data Modeler to document an Oracle 12cR2 physical model of a transactional database and to build a multi-dimensional model intended for a dedicated OLAP analytic workspace Oracle 12cR2 database.  I am attempting to Export the multi-dimensional model but am getting an error message "Error in Export to Oracle AW!"  with source "oracle.dbtools.crest.util.logging.Logger" and external log:

       

      2019-05-19 22:52:30 SEVERE - Error in Export to Oracle AW!

      java.lang.NullPointerException

      at oracle.dbtools.crest.exports.oracleaw.OracleAW11Exporter.export(OracleAW11Exporter.java:263)

      at oracle.dbtools.crest.swingui.ControllerApplication$ExportToOracleAW.execute(ControllerApplication.java:1947)

      at oracle.dbtools.crest.swingui.ControllerApplication$ExportToOracleAW.doActionPerformed(ControllerApplication.java:1937)

      at oracle.dbtools.crest.swingui.MDBAction$ActionInvoker.run(MDBAction.java:248)

      at java.awt.event.InvocationEvent.dispatch(InvocationEvent.java:311)

      at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:758)

      at java.awt.EventQueue.access$500(EventQueue.java:97)

      at java.awt.EventQueue$3.run(EventQueue.java:709)

      at java.awt.EventQueue$3.run(EventQueue.java:703)

      at java.security.AccessController.doPrivileged(Native Method)

      at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:74)

      at java.awt.EventQueue.dispatchEvent(EventQueue.java:728)

      at oracle.javatools.internal.ui.EventQueueWrapper._dispatchEvent(EventQueueWrapper.java:169)

      at oracle.javatools.internal.ui.EventQueueWrapper.dispatchEvent(EventQueueWrapper.java:151)

      at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:205)

      at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:116)

      at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:105)

      at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101)

      at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:93)

      at java.awt.EventDispatchThread.run(EventDispatchThread.java:82)

       

       

      Separately in Analytic Workspace Manager / AWM I was able to create an analytic workspace, dimension with 5 levels, hierarchy, cube, and measure.  Within AWM, I get other error messages (telling me how not to build things).  AWM is not as scalable or understandable a tool for my purposes.  I am impressed by SQL Developer Data Modeler as a documentation and design tool capable of forward and reverse engineering.  I really need it to easily export to an analytic workspace enabled oracle database.  My next step is to populate the data in the analytic database from the transactional one.  I have identified Oracle Data Integrator / ODI as the best tool for that step, even though ODI did not have the same capability for designing cubes (not its purpose it seems), I imagine you could build one it would be much more complicated than doing it in SQL Data Modeler.  Ideally the Data Modeler and ODI products could be interchangeable somehow with respect to AW / OLAP options.

       

      Apart from understanding / fixing the bugs, I am also curious: 

      1. I saw The CREATE DIMENSION step in the ddl from the physical model (using mdm engineered to oracle) - does the Analytic Workspace setup prefer to use physically defined dimensions, is there any advantage?
      2. What name limitations should I be aware of, if any.
      3. What is the metadata model trying to do with Long and Short descriptions, seeing as I am copying in RDBMS comments and adding the aggregation (Sum) it seems unnecessary.
      4. Should I use the same schema for my fact tables and my cubes/dimensions? 
      5. A DBA set up a DB Link in the analytic database so I could directly query a training copy of the transactional database.  Sounds like a good idea, but I think I will need to copy (load) all the data into the analytic database to improve cube refresh / build times, correct?

       

       

      About (from my SQL Developer Data Modeler Help menu)

      -----

       

       

      Oracle SQL Developer Data Modeler 18.4.0.339

      Version 18.4.0.339

      Build 339.1532

       

       

      IDE Version: 13.0.0.1.42.170225.0201

      Product ID: oracle.datamodeler

      Product Version: 18.4.0.339.1532

       

       

      Version

      -------

       

       

      Component Version

      ========= =======

      Oracle IDE 18.4.0.339.1532

      Java(TM) Platform 1.8.0_201

      Versioning Support 18.4.0.339.1532

       

       

      Properties

      ----------

       

       

      Name Value

      ==== =====

       

       

      Extensions

      ----------

       

       

      Name Identifier Version Status Registration Time Initialization Time Total Time

      ==== ========== ======= ====== ================= =================== ==========

        • 1. Re: Export to AW analytic workspace trouble & advice requested
          thatJeffSmith-Oracle

          I am attempting to Export the multi-dimensional model but am getting an error message

           

          You're using the Import > Data Dictionary feature on the schema where your AW lives?

          • 2. Re: Export to AW analytic workspace trouble & advice requested
            Philip Stoyanov-Oracle

            Hi Izik,

             

            you need DM 19.1 in order to Export to Oracle 12cr2 AW. DM only can create AW not to modify it - or drop and create it.

            On your questions - for some of them it's better to ask in OLAP forum.

            I saw The CREATE DIMENSION step in the ddl from the physical model (using mdm engineered to oracle) - does the Analytic Workspace setup prefer to use physically defined dimensions

            Those are dimensions used by SQL engine to do query rewrite - you can read on that in DWH guide. OLAP engine (AW) uses other dimensions

             

            What name limitations should I be aware of, if any.

            Limitation for Database version.

            Should I use the same schema for my fact tables and my cubes/dimensions

            You can - just use different names.

             

            Philip

            • 3. Re: Export to AW analytic workspace trouble & advice requested
              Izik Warn

              Thanks! Upgrading to 19.1 fixed things, probably a version compatibility problem it seems. 

               

              I will read up but doubt I need dimensions in the physical model if the analytic workspace is more flexible and faster, works better with the other tools like ODI.  The DDL generator for dimensions was missing either a column or a space in the join key, perhaps ODM folks only tested it with a surrogate key.

               

              ```JOIN KEY (SCHEMA.COLUMN_1SCHEMA.COLUMN_2SCHEMA.COLUMN_3)``` instead should be SCHEMA.COLUMN_1, SCHEMA.COLUMN_2... I believe

               

              I had to change a bunch of auto-generated names back to 30 characters long, a bit annoying.

               

              I was using the import data dictionary as a starting point.