What no GUI? How to Build Analytic Workspaces using scripts

What no GUI? How to Build Analytic Workspaces using scripts

Recently on the OLAP OTN forum there have been a number of questions regarding the use of scripting to control the design, build and maintenance procedures for analytic workspace. There are many reasons for using scripting rather than using Analytic Workspace Manager to build and maintain an AW. Many DBAs prefer to use scripts because they can be scheduled and/or quickly and easily moved from one platform to another without having to install any GUI components. Most likely scenario is that for the production environment a direct connection from a GUI client tool is not allowed. Obviously if you are using Warehouse Builder this is not a problem as OWB will automatically generate deployment and maintenance scripts that do not require execution via the GUI. However, when you are just using AWM things can get a little complicated.

As there is so much information to cover I am going to break this into a series of postings. The purpose of this one is to provide a brief summary of some of the ways to create and define objects within an AW from the command line.

Part 1 : How to create a new AW

It is possible to create an AW from the command line by using the AW_ATTACH procedure. The AW_ATTACH procedure attaches an existing analytic workspace to your SQL session so that you can access its contents. The analytic workspace remains attached until you explicitly detach it, or you end your session. AW_ATTACH can also be used to create a new analytic workspace, but the AW_CREATE procedure is provided specifically for that purpose.

DBMS_AW.AW_ATTACH (
awname IN VARCHAR2,
forwrite IN BOOLEAN DEFAULT FALSE,
createaw IN BOOLEAN DEFAULT FALSE,
attargs IN VARCHAR2 DEFAULT NULL,
tablespace IN VARCHAR2 DEFAULT NULL);



Notice using the scripting approach it is possible to set the default tablespace for the AW, a feature that is missing from the GUI. For example, the next SQL call creates the AW GLOBAL_PROGRAMS as the last user-owned analytic workspace in tablespace USERS:

SQL>EXECUTE DBMS_AW.AW_ATTACH('global_programs', true, true, 'last', ‘USERS’);

Alternatively you can go a step further and use the AW_CREATE command, which provides more control in some areas and less control in others. The AW_CREATE procedure creates a new, empty analytic workspace.

DBMS_AW.AW_CREATE (
awname IN VARCHAR2 ,
tablespace IN VARCHAR2 DEFAULT NULL ,
partnum IN NUMBER DEFAULT 8);


For example, the next command creates an analytic workspace named GLOBAL_AW. The resulting table AW$GLOBAL_AW will have two partitions and will be stored in the GLOBAL tablespace.

SQL>EXECUTE DBMS_AW.AW_CREATE('global_aw', 'global', 2);

With the AW_CREATE command it is possible to control the number of partitions, which is a very useful feature. But you do lose some control over where the AW is created in the attach list. This is only important if you have multiple AWs attached during the creation process. So why do we have two different procedures to create AWs? I have no idea but I am sure it seemed like a good idea at the time.

Now we have an empty AW, the next step is to start adding objects, possibly some data as well, to the AW. There are two options:

• Import objects and data from an existing EIF file
• Import objects based on existing XML documents

Part 2a - Importing an existing EIF file

If you already have an EIF file from an existing AW, the next step is import that file into your new AW. Ideally, the EIF file will contain all the required standard form metadata. If this is not the case and the EIF file is from an older version of Oracle OLAP, or Oracle Express, then you will need to have a different approach. Another blog entry will deal with the upgrade process.

To run the import process you need to place the EIF file in a directory on the file system then make a directory within the database to point to that filestore. We can use OLAP DML commands wrapped within the DBMS_AW.EXECUTE procedure to import the EIF file from the command line. Personally I prefer to make two passes through the EIF file, firstly to create the objects and then to load the data. This goes back to the good old days of Express and ensured efficient storage within the database files. Not sure if this still applies to 10g OLAP schemas, but as old habits die hard, here is the two pass process:

SQL>execute dbms_aw.execute ('aw attach name_of_analytic_workspace');

SQL>execute dbms_aw.execute ('cda scripts');

SQL>execute dbms_aw.execute ('import all from eif file ''global.eif'' dfns');

SQL>execute dbms_aw.execute ('update');

SQL>execute dbms_aw.execute ('commit');

SQL>execute dbms_aw.execute ('import all from eif file ''global.eif'' data');

SQL>execute dbms_aw.execute ('update');

SQL>execute dbms_aw.execute ('commit');



The assumption here is the EIF file being used contains objects defined in standard form. If this is not the case then you will need to run a conversion process to generate standard form metadata.

Some warning signs need to set here : The import process above is going to load data into the analytic workspace with no commit point until the very end. Therefore, you need to make sure you have plenty of storage space within your TEMP tablespace, otherwise it is likely the import will fail with one of a number of error messages relating to memory and/or temp space issues. It is possible to control the update process by using the UPDATE keyword. For example:

SQL>execute dbms_aw.execute ('import all from eif file ''global.eif'' data UPDATE');


Will force an update after each object has been loaded. This is fine until you hit a very large cube when you may need slight more detailed control over the update process. The EIFUPDBYTES option lets you define the volume of data to read between updates. The following command will update after each block of 500,000 bytes has been loaded.

SQL>execute dbms_aw.execute ('EIFUPDBYTES = 500000’

SQL>execute dbms_aw.execute ('import all from eif file ''global.eif'' data UPDATE');



Obviously, by executing continual updates during the import the whole process will take much longer to complete than a single read with a final commit point at the end. But in the end “you pays your money and takes your choice” as they say.

One major problem with EIF imports is that most people create a single EIF file, which creates problems if you need to recover from a failed import process. Why? Because you have to start right at the beginning and import the whole thing all over again. Or you would think! The EIF file is in fact quite smart in terms of its contents. It is possible to get a list from the file of the actual contents so in reality it is possible to recover from a failed import process by checking what was imported and now resident in the AW and what is left to import from the EIF file. Requires a bit of OLAP DML skill…something for another blog posting perhaps?

Part 2b - Importing an existing XML definition

Each of the create procedures described above generates a completely blank AW. There is no standard form metadata contained within the resulting AW. Before you can import an XML template into the AW, standard form metadata needs to be added. The only way to do this seemed to be to use AWM (or right your own Java procedure to call the AWXML API). After a bit of digging I did discover the following an undocumented features called create_db_stdform. This is referenced in older versions of the OLAP documentation set and the syntax is as follows:

• call create_db_stdform('my_aw_name','rw')

There is more information on Mark Rittman’s excellent blog website, where one of his posts covers migrating from previous versions of Express to 9i OLAP. The full article is on Mark’s website at:

http://www.rittmanmead.com/2003/10/30/creating-database-standard-form-analytic-workspaces/

To execute this command we can use the normal process as follows:

SQL>execute dbms_aw.execute ('call create_db_stdform(''global_aw'' ''rw'');


Now the AW will appear in AWM under the Model View and it is now possible to start creating dimensions and cubes. However, the purpose of this blog is to explain how not to use the GUI!
The next stage is to use existing XML template documents to define the objects within the AW. As far as I can tell there are no command line tools to create an AW XML definition for an existing object in an existing AW. The only way to do this is to use the Analytic Workspace Manager and the right mouse click menu options, or use the Generate feature in Warehouse Builder (Unless someone knows how to do this from the command line?).

So, assuming you have all the required XML files to create the dimensions and cubes for your new AW, the process for importing these files is very simple. There are to procedures you can use:

• EXECUTEFILE
• creates all or part of a standard form analytic workspace from an XML document stored in a text file.
• EXECUTE
• creates all or part of a standard form analytic workspace from an XML document stored in a CLOB.

The EXECUTEFILE function is the easier to use of the two functions. Assuming you have generated an XML definition of your AW you will also need to define a directory within the database that points to the location of the XML file(s). In the example below the directory SCRIPTS was created and the definition for AW is contained within the file GLOBAL.XML and the whole command is wrapped by the DBMS_OUTPUT.PUT_LINE function so that the "Success" message returned by EXECUTEFILE is displayed:

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


The EXECUTE approach is slight more complicated but achieves exactly the same result. So why do we have two ways of achieving the same thing? Who knows, as I said before I am sure there is a very good reason?

--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;

/




and there it is. A new standard form analytic workspace created and populated with base objects (dimensions and cubes) without using a GUI. In the next blog posting I will explain how to maintain cubes and dimensions without using the GUI.