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!
at java.security.AccessController.doPrivileged(Native Method)
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:
- 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?
- What name limitations should I be aware of, if any.
- 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.
- Should I use the same schema for my fact tables and my cubes/dimensions?
- 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 188.8.131.529
IDE Version: 184.108.40.206.42.170225.0201
Product ID: oracle.datamodeler
Product Version: 220.127.116.119.1532
Oracle IDE 18.104.22.1689.1532
Java(TM) Platform 1.8.0_201
Versioning Support 22.214.171.1249.1532
Name Identifier Version Status Registration Time Initialization Time Total Time
==== ========== ======= ====== ================= =================== ==========