4 Replies Latest reply: Aug 10, 2012 7:54 AM by wendress RSS

    Using OLAP DML to maintain measures

    952749
      Can anyone point me to documentation on running custom DML programs in maintenance scripts in AWM? I would like to be able, for instance, to output information to the Logs.
        • 1. Re: Using OLAP DML to maintain measures
          Nasar-Oracle
          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.
          • 2. Re: Using OLAP DML to maintain measures
            952749
            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?
            • 3. Re: Using OLAP DML to maintain measures
              DavidGreenfield
              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.
              DECLARE
                PRAGMA AUTONOMOUS_TRANSACTION;
              BEGIN
                INSERT INTO ...
                COMMIT;
              END;
              /
              You would then call your PL/SQL procedure from OLAP DML every time you wanted to log a record.
              • 4. Re: Using OLAP DML to maintain measures
                wendress
                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)?