This content has been marked as final. Show 4 replies
If its a standard-form dimension created through AWM, then you can only load/refresh it through DBMS_CUBE.BUILD procedure. Read documentation at http://docs.oracle.com/cd/E11882_01/appdev.112/e16760/d_cube.htm#CHDFCJBF
If its a custom dimension created through OLAP worksheet command line or through DBMS_AW.EXECUTE , only then you have to write your own OLAP DML program to maintain it.
Thanks for your reply. I'm experienced in writing code to maintain structures, but in this case I want to populate stored measures with results that can only realistically be calculated in programs. So I have an OLAP dml program that can do that, and I can run it from a Cube Script or a Maintenance Script. Only I would like to be able to get some logging and diagnostics during the run, and to be able to see the results from my error trapping. I can outfile to a log file, but then I have to go outside the system to see it. It would be better to see stuff in the Summary/Detailed logs. Can this be done from OLAP dml?
You can run an insert statement from the OLAP DML to populate any table you want. The only complication is that the rows will only be persisted if the transaction is committed, and you can not (should not!) commit the transaction during the current build since it will persist bad data in the case of a failure. A possible solution is to write a PL/SQL procedure that begins an "autonomous transaction" and make this be responsible for logging. E.g.
You would then call your PL/SQL procedure from OLAP DML every time you wanted to log a record.
DECLARE PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO ... COMMIT; END; /
Look at the OLAP DML help (easily accessable through AWM's OLAP Worksheet) and review the SQL command. This allows you to issue SQL commands (e.g., SELECT into a cube variable, INSERT into a table). Pay attention to David Greenfield's warning about issues COMMITs. A COMMIT in OLAP DML is a COMMIT in the database and will commit anything during that session, including data during the build (if that's where the script is being run).
What types of info are you trying to log? Does it need to be run within the cube script or can it be run in a separate session (in which case a commit will not affect the sessions running the build script)?