1 Reply Latest reply: Sep 6, 2012 10:00 AM by AdamMartin RSS

    script to create OLAP cubes

    saranya.a
      i have created a cube in AWM. Now i need a script for the cube created in AWM.By running that script , i should be able to install the schema and the cubes.is there any script will be generated while i create cubes in AWM.?? if so where do i find the scripts??
        • 1. Re: script to create OLAP cubes
          AdamMartin
          You can create an XML template of a cube by right-clicking on the cube and choosing "Save Cube <name> To Template" and saving the file. You can do the same thing for a dimension, multiple objects, or an entire analytic workspace. Then you can import the template to create the objects in a new environment. This can be done via script using dbms_cube.import_xml.

          Pass a clob data type into the procedure like this, using to_clob:
          begin
          dbms_cube.import_xml(to_clob('<Metadata
            Version="1.3"
            MinimumDatabaseVersion="11.2.0.2">
            <Cube
          .
          .
          .
              </ConsistentSolve>
            </Cube>
          </Metadata>'));
          end;
          /
          If the length of the XML is too long, as might be the case for an entire analytic workspace, you can use a clob variable and concatenate each line to it:
          declare
             v_clob clob;
          begin
            v_clob := q'~<Metadata~';
            v_clob := v_clob || q'~  Version="1.3"~';
            v_clob := v_clob || q'~  MinimumDatabaseVersion="11.2.0.2">~';
          .
          .
          .
            v_clob := v_clob || q'~</Metadata>~';
            dbms_cube.import_xml(v_clob);
          end;
          /
          Just be aware that the spacing matters in the imported XML, so don't remove the white space (the indentation).

          Also, just so you know, if you go into AWM to Tools -> Configuration there is a Preference under Logging called Application Logging File. Put a directory and file name in there and it will record every action you take in AWM. If you open the log file, you will see XML commands that can be imported with dbms_cube.import_xml. However, each action will be a separate command, so if you consider the number of actions it takes to build an entire cube, it is much easier to just import a template than it would be to execute the dozens of commands again.