Oracle OLAP XML Schema

The EXECUTE and EXECUTEFILE functions process XML that conforms to the Oracle OLAP XML schema defined in awxml.xsd. The XML generated by the Analytic Workspace Java API automatically conforms to awxml.xsd. You can also create your own XML and validate it against the Oracle OLAP XML schema.

Example 25-1 provides an excerpt from an XML document that conforms to the Oracle OLAP XML schema.

Tip:

You can obtain AWXML.xsd, as well as the latest version of the Oracle OLAP Analytic Workspace Java API Reference (Javadoc), from the Oracle Technology Network Web site:

http://www.otn.oracle.com/products/bi/olap/olap.html

Example 25-1 Oracle OLAP XML Document

<AWXML version = '1.0' timestamp = 'Mon Feb 11 13:29:11 2002' >
<AWXML.content>
  <Create  Id="Action3">
    <ActiveObject >
      <AW  Name="GLOBAL_AW.GLOBAL" LongName="GLOBAL_AW.GLOBAL" 
          ShortName="GLOBAL_AW.GLOBAL" PluralName="GLOBAL_AW.GLOBAL" 
          Id="GLOBAL_AW.GLOBAL.AW" Schema="GLOBAL_AW" MetaDataFormat="10.2"
          DefaultLanguage="AMERICAN" Languages="AMERICAN">
        <Dimension  Name="TIME" LongName="AMERICAN::Time"
             ShortName="AMERICAN::Time" PluralName="AMERICAN::Time"
             Id="TIME.DIMENSION" Schema="GLOBAL_AW" isTime="true"
             isMeasure="false" UseNativeKey="true">
          <Attribute  Name="END_DATE" LongName="AMERICAN::END_DATE"
             ShortName="AMERICAN::END_DATE" PluralName="AMERICAN::END_DATE"
             Id="TIME.END_DATE.ATTRIBUTE" DataType="DATE" 
             Classification="END_DATE" InstallAsRelation="false"
             IsDefaultOrder="false"/>
          <Attribute  Name="TIME_SPAN" LongName="AMERICAN::TIME_SPAN"
             ShortName="AMERICAN::TIME_SPAN" PluralName="AMERICAN::TIME_SPAN"
             Id="TIME.TIME_SPAN.ATTRIBUTE" DataType="INTEGER"
             Classification="TIME_SPAN" InstallAsRelation="false"
             IsDefaultOrder="false"/>
          <Attribute  Name="LONG_DESCRIPTION" 
             LongName="AMERICAN::Long Description" 
             ShortName="AMERICAN::Long Description" 
             PluralName="AMERICAN::Long Descriptions" 
             Id="TIME.LONG_DESCRIPTION.ATTRIBUTE" DataType="TEXT"
             Classification="MEMBER_LONG_DESCRIPTION" InstallAsRelation="false"
             IsDefaultOrder="false" IsMultiLingual="true"/>
                 .
                 .
                 .

Summary of DBMS_AW_XML Subprograms

The following table describes the subprograms provided in DBMS_AW_EXECUTE.

Table 25-1 DBMS_AW_XML Subprograms

Subprogram	Description
EXECUTE Function	Creates all or part of a standard form analytic workspace from an XML document stored in a CLOB.
EXECUTEFILE Function	Creates all or part of a standard form analytic workspace from an XML document stored in a text file.

EXECUTE Function

The EXECUTE function builds an analytic workspace using XML that conforms to the Oracle OLAP XML schema. The XML is stored in a database object.

Syntax

EXECUTE (
          xml_input     IN     CLOB)
     RETURN VARCHAR2;

Parameters

Table 25-2 EXECUTE Function Parameters

Parameter	Description
`xml_input`	An XML document stored in a `CLOB`. The XML contains instructions for building or maintaining an analytic workspace. The XML describes a logical model, provides instructions for loading data from relational tables, and defines aggregation and other calculations to be performed on the data in the workspace.

Example

The following SQL program creates a CLOB and loads into it the contents of an XML file. It then creates an analytic workspace named GLOBAL in the GLOBAL_AW schema from the XML document in the CLOB.

--Use DBMS_LOB package to create a clob
   DECLARE
      clb CLOB;
      infile BFILE;
      dname varchar2(500);
   BEGIN
 
   -- Create a temporary clob
      DBMS_LOB.CREATETEMPORARY(clb, TRUE,10);
 
   -- Create a BFILE use BFILENAME function
   -- Use file GLOBAL.XML in the SCRIPTS directory object.
      infile := BFILENAME('SCRIPTS', 'GLOBAL.XML');
 
   -- Open the BFILE
      DBMS_LOB.fileopen(infile, dbms_lob.file_readonly);
 
   -- Load temporary clob from the BFILE
      DBMS_LOB.LOADFROMFILE(clb,infile,DBMS_LOB.LOBMAXSIZE, 1, 1); 
 
   -- Close the BFILE
      DBMS_LOB.fileclose(infile);
      
   -- Create the GLOBAL analytic workspace
      DBMS_OUTPUT.PUT_LINE(DBMS_AW_XML.execute(clb));
      DBMS_AW.AW_UPDATE;
      COMMIT;
      
   -- Free the Temporary Clob
      DBMS_LOB.FREETEMPORARY(clb);
   EXCEPTION
     WHEN OTHERS
      THEN
       DBMS_OUTPUT.PUT_LINE(SQLERRM);
   END;
   /

EXECUTEFILE Function

The EXECUTEFILE function builds an analytic workspace using XML that conforms to the Oracle OLAP XML schema. The XML is stored in a text file.

Syntax

EXECUTEFILE (
          dirname    IN     VARCHAR2
          filename   IN     VARCHAR2)
     RETURN VARCHAR2;

Returns

The string SUCCESS if successful

Parameters

Table 25-3 EXECUTEFILE Function Parameters

Parameter	Description
`dirname`	A directory object that identifies the physical directory where xml_file is stored.
`xml_file`	The name of a text file containing an XML document. The XML contains instructions for building or maintaining an analytic workspace. The XML describes a logical model, provides instructions for loading data from relational tables, and defines aggregation and other calculations to be performed on the data in the workspace.

Example

The following EXECUTEFILE function generates a standard form analytic workspace from the XML statements stored in GLOBAL.XML, which is located in a directory identified by the SCRIPTS directory object. The DBMS_OUTPUT.PUT_LINE function displays the "Success" message returned by EXECUTEFILE.

SQL> execute dbms_output.put_line(dbms_aw_xml.executefile('SCRIPTS', 'GLOBAL.XML'));Success