Friday, August 10, 2007

What no GUI? How to Load AW XML Files

This part of a series of postings prompted by many questions I received about how to manage an analytic workspace without using a GUI (AWM or OWB) . In this post I want to provide some more information on using XML templates. Most of this information came from Marty Gubar, OLAP Product Manager, who is an expert in using AW XML.

Personally, I am still not sure how you would create an XML file for an AW without using Analytic Workspace Manager, however, based on recent posts on the OLAP forum and from questions posted to our internal help mail list, it is obvious there are people actually designing and building AWs without ever using AWM. So I going to make the assumption that you have somehow managed to created your XML file that describes a dimension or a cube, or any other supported object, and question now is how do you load that file into the AW?

The first thing you need to do is to find this extremely useful jar file, called ImportXML.jar file. For the moment you will have to email me ( to get a copy of this file, but hopefully the product management team will get it hosted on the OLAP OTN home page very shortly. Once you have this jar file you can create a script file as follows:

java.exe -classpath .\ImportXML.jar samples.awxml.ImportXML %1 %2 %3 %4 %5 %6 %7 %8 %9

The script takes the following arguments
  • id/pwd ID and Password
  • -con [host:port:sid] Connect string (e.g. localhost:1521:orcl)
  • -aw [TargetAW] AW that is the target for the XML input (required when XML does not include a complete AW)
  • - file [file] Name of XML file

1. Importing an object (e.g. a calculation) into the GLOBAL analytic workspace:
  • import scott/tiger -con localhost:1521:orcl -aw global -file my_model.xml

2. Importing an entire analytic workspace:
  • import scott/tiger -con localhost:1521:orcl -file global_aw.xml

The above script can be used with the XML template files generated by Analytic Workspace Manager as well. Alternatively you can manually create your own (as some of you are quite clearly doing!). If you are manually creating these files, or just want to modify an existing file to make some changes hopefully the following overviews will help:

Structure of an XML File: Each XML file is broken down into four basic blocks, which relate to the type of work being performed.
  • Step 1 is to attach the required AW via an “Attach Id” tag.
  • Step 2 is to process the object identified within the file
  • Step 3 is to commit all changes
  • Step 4 is detach the AW
There is a quick an easy way to understand what is required and how these files are processed. If you use Warehouse Builder, it is possible to use Control Center to generate deployment scripts, which you can then review. For example I used OWB to generate a a CREATE dimension and a DROP dimension script so I could understand the steps involved and review the code it generated. There are a number basic tags that you need to use
  • Attach
  • Commit
  • Delete
  • Create
  • Delete
  • Alter
  • ActiveObject
  • Id
Hopefully, the following sections will help to explain how to use these various tags:

Attaching an AW
The process for attaching is very simple. There are two ways to do this, you either:
  • pass a parameter into your script file via the command line using -AW aw_name
  • code the AW and schema name into the XML file
I think the first is probably the best idea because it makes it easier to move XML and import files into different database instances. But if you want to hard code this information into your XML file then you need provide the following code:

(I am hoping you can read this code!). The process is quite straightforward. The first four lines are the normal XML header information, after that comes the following:
  1. Attach tag with an Id tag that contains an the word Action with a number, like this "Action1" . This controls the order of processing and in an normal XML file you will see this repeated with numbers increasing sequentially through the file
  2. ActiveObject tag starts the block to identify what the file is going to process which is determined by the next line
  3. In this case the attach needs to process an AW so an AW tag is used to identify the specific AW to be attached
  4. The last three rows contain the termination tags for rows 1,2 and 3.
It is possible to define an attach mode within this block as well, allowing the AW to only be attached in read-only mode which allows AWs to be shared across multiple projects and allows an AW to be attached by multiple processes at the same time.

Committing Changes within an AW
The process for committing is the same as the attach process except the tag Commit is used. Because during a session we could in theory have mulitple AWs attached the commit block still requires an ActiveObject block to identify the AW to be updated. This allows to not update other AWs as required.

Detaching an AW
The process for detaching is also the same as the attach process except the tag Detach is used. Because during a session we could in theory have mulitple AWs attached the detach block still requires an ActiveObject block to identify the AW to detach.

Building a CREATE Script
Create scripts are the output from Analytic Workspace Manager. After the main header block you will find a Create tag with an associated Id that defines the action reference for the create statement. If you are using the Attach tag as well, then the number associated with "Action" for the Create block must be greater than that used in the Attach block. It all sounds very complicated but when you see the code it is very simple.

The Create block identifies the create process in full. For a dimension that full process is:
  1. Dimension name
  2. Dimension labels
  3. Dimension attributes
  4. Dimension levels
  5. Dimension hierarchies
  6. Dimension mappings
A lot of information needs to be provided in the XML file, which is why I prefer to use Analytic Workspace Manager to generate these!

Building a DELETE Script
Performing a Delete operation in Analytic Workspace Manager is very simple - highlight the object and hit the delete key. Assuming there are no supporting objects that prevent the delete process from running the object will be deleted - for example you cannot delete a dimension while it is still associated with a cube. However, outside of Analytic Workspace Manager the XML file does provide a Delete tag to allow for command line deletion of objects. The amount of information you need to supply to delete an object is minimal. For a dimension it is simply the lines containing the Dimension tag and the end-Dimension tag. The delete process will remove all the objects with dimension automatically.

Building an ALTER Script
It is possible to update certain parts of an AW using this method. To be honest, I have not tried everything. According to the Java documentation this method allows you to update any of the text attributes, such as:
  • Long descriptive label
  • Short descriptive label
  • Plural descriptive label
So you could change a dimensions description from "Products" to "Company Products" for example. For more heavy duty alterations, such as modifying a hierarchy by changing the levels within the hierarchy I think you would need to delete the hierarchy first and then rebuild it. It may depend on the types of changes you want to make, however, the API documentation seems to indicate this is a heavily restricted tag. But enjoy!