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