DBMS_AW_XML
creates an analytic workspace based on the descriptions of objects stored an XML document.
This chapter includes the following topics:
This section contains topics that relate to using the DBMS_AW_XML
package.
You can define an analytic workspace containing dimensional data objects such as cubes and dimensions using either Analytic Workspace Manager or the OLAP API. The definitions of these dimensional objects are stored in the database as an XML document. You can save the XML document as a text file. You can then load and execute the XML file to re-create the dimensional objects, such as for backup or to copy the objects to a different system. The XML file is often called an XML template, or just a template.
You can use either Analytic Workspace Manager, the OLAP API, or DBMS_AW_XML
to load and execute an XML file. The results are identical.
Providing the Context for Cubes and Dimensions
You can save the XML for an entire analytic workspace, for a single cube, or for a single dimension. When re-creating just a cube or dimension, you must provide the appropriate context. This context is an analytic workspace containing all of the metadata objects.
While Analytic Workspace Manager controls the context, DBMS_AW_XML
does not. You must create an analytic workspace with the metadata before loading the XML for a cube or a dimension. It is not sufficient just to attach an empty analytic workspace. You must first use the READAWMETADATA
function of DMBS_AW_XML
.
Obtaining Diagnostic Information
If the procedures execute successfully but do not generate an analytic workspace, the problem may be in the XML template. You should always load a template into the same version and release of Oracle Database as the one used to generate the template.
The build will also fail if objects by the same name already exist in the schema. For example, you cannot create the Global analytic workspace with a dimension named PRODUCT
in a schema that contains an analytic workspace named Test with a dimension named PRODUCT
.
Error messages generated during the creation of the analytic workspace are stored in OLAPSYS.XML_LOAD_LOG
. This table is publicly accessible. Use a query such as the following:
SQL> SELECT xml_message FROM olapsys.xml_load_log ORDER BY xml_date;
The following table describes the subprograms provided in DBMS_AW_EXECUTE
.
Table 4-1 DBMS_AW_XML Subprograms
Subprogram | Description |
---|---|
Creates all or part of an analytic workspace from an XML document stored in a CLOB. |
|
Creates all or part of an analytic workspace from an XML document stored in a text file. |
|
Loads the definition of an analytic workspace into database memory. |
EXECUTE
creates dimensional objects from an XML document. The XML is stored in a database object.
EXECUTE ( xml_input IN CLOB ) RETURN VARCHAR2;
Table 4-2 EXECUTE Function Parameters
Parameter | Description |
---|---|
|
An XML document stored in a |
The string Success
if successful
The following SQL script 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
. The DBMS_OUTPUT.PUT_LINE
procedure is used to provide status messages during execution of the script.
You would typically use the EXECUTEFILE
function to create an analytic workspace from an XML file. This example creates a CLOB
from a file just for illustrating the EXECUTE
function.
DECLARE clb CLOB; infile BFILE; BEGIN dbms_output.put_line('Create a temporary clob'); dbms_lob.createtemporary(clb, TRUE,10); dbms_output.put_line('Create a BFILE using BFILENAME function'); infile := bfilename('WORK_DIR', 'GLOBAL.XML'); dbms_output.put_line('Open the BFILE'); dbms_lob.fileopen(infile, dbms_lob.file_readonly); dbms_output.put_line('Load Temporary Clob from the BFILE'); dbms_lob.loadfromfile(clb,infile,dbms_lob.lobmaxsize,1,1); COMMIT; dbms_output.put_line('Close the BFILE'); dbms_lob.fileclose(infile); dbms_output.put_line('Create the analytic workspace'); dbms_output.put_line(dbms_aw_xml.execute(clb)); COMMIT; dbms_aw.aw_update; dbms_output.put_line('Free the temporary clob'); dbms_lob.freetemporary(clb); EXCEPTION WHEN OTHERS THEN dbms_output.put_line(SQLERRM); END; /
The successful execution of this script generates the following messages:
Create a temporary clob Create a BFILE using BFILENAME function Open the BFILE Load Temporary Clob from the BFILE Close the BFILE Create the analytic workspace Success Free the temporary clob
EXECUTEFILE
creates dimensional objects from an XML document stored in a text file. This file is also called a template.
EXECUTEFILE ( dirname IN VARCHAR2, xml_file IN VARCHAR2 ) RETURN VARCHAR2;
The string Success
if successful
Table 4-3 EXECUTEFILE Function Parameters
Parameter | Description |
---|---|
|
A directory object that identifies the physical directory where xml_file is stored. |
|
The name of a text file containing an XML document. |
The following EXECUTEFILE
function generates an analytic workspace from the XML document stored in GLOBAL.XML
, which is located in a directory identified by the WORK_DIR
directory object. The DBMS_OUTPUT.PUT_LINE
function displays the Success
message returned by EXECUTEFILE
.
SQL> SET serveroutput ON format wrapped SQL> EXECUTE dbms_output.put_line(dbms_aw_xml.executefile('WORK_DIR', 'GLOBAL.XML')); Success
Loads the XML definition of an analytic workspace into database memory and transfers the definition to the client, so that it can construct the client-side model.
READAWMETADATA ( awname IN VARCHAR2, rights IN VARCHAR2 ) RETURN GENWSTRINGSEQUENCE;
An XML document that defines the analytic workspace
Table 4-4 READAWMETADATA Function Parameters
Parameter | Description |
---|---|
|
The name of the analytic workspace you want to create. |
|
The access rights to attach the analytic workspace:
|
This example first loads the OLAP metadata into memory. It creates an analytic workspace named GLOBAL
that contains this OLAP metadata but no dimensions or cubes. It then uses the EXECUTEFILE
function to create the Product dimension from an XML template file.
DECLARE tmp GENWSTRINGSEQUENCE; BEGIN dbms_output.put_line('Read the metadata'); tmp := dbms_aw_xml.readawmetadata('global', 'RW'); END; / DECLARE tmp2 VARCHAR2(100); BEGIN dbms_output.put_line('Create the Global aw'); dbms_aw.execute('AW CREATE global'); dbms_output.put_line('Create the Product dimension'); dbms_output.put_line(dbms_aw_xml.executefile('work_dir', 'product.xml')); COMMIT; dbms_aw.aw_update; EXCEPTION WHEN OTHERS THEN dbms_output.put_line(SQLERRM); END; /
The successful execution of this script generates the following messages:
Read the metadata Create the Global aw Create the Product dimension Success