Migrating Express Server Databases To 10g And Discoverer “Drake”

Mark Rittman -

A task I set myself over the Christmas break was to put together an Oracle 10g OLAP demo for one of our customers. Like many of the organisations we've worked with over the years, the customer had a business intelligence solution built on Oracle Express Server technology, and they were interested in what their system might look like if migrated to Oracle 10g technology. My job therefore was to migrate their Express Server 6.3.4 database to Oracle 10g OLAP, and then to show what it might look like if delivered through Discoverer Drake and Oracle Portal. For many Express Server customers, being able to integrate their OLAP reporting in with their regular data warehouse and OLTP reporting is a real benefit and they've been waiting for just such an opportunity to arise before making plans to migrate off of the old Express Server technology.

The first step in the migration was to export out the Express Server database (in their case called SALES) in the form of an EIF file, which brought the size of the database file down from the 15GB down to around 853MB, which then went down to around 200MB when zipped. Then, I installed Oracle 10g 10.1.0.2 for Windows 32-bit, Oracle 10g Client 10.1.0.2, patched both server and client up to 10.1.0.3 and then applied the 10.1.0.3C OLAP Patch (Patch No. 4074620) to both server and client.

Next I created a new user to hold my analytic workspace, and had it use separate data and temporary tablespaces that I'd set up for this work:

CREATE USER DEMO IDENTIFIED BY PASSWORD
DEFAULT TABLESPACE AW_DEMO
TEMPORARY TABLESPACE AW_DEMO_TEMP
ACCOUNT UNLOCK;

GRANT CONNECT, RESOURCE, OLAP_USER TO AW_DEMO;

(Yes, I know I shouldn't grant CONNECT and RESOURCE...)

Then I defined a directory to point to where my EIF file was stored, and granted read access to it to the DEMO user.

CREATE DIRECTORY C AS 'C:\';

GRANT READ ON DIRECTORY c TO DEMO;

Next step was to fire up Analytic Workspace Manager, connect as the DEMO user, and start the OLAP Worksheet. Then, to create the analytic workspace that's going to hold the migrated database, I issued the commands:

     AW CREATE SALES_NEW

     IMPORT ALL FROM EIF FILE 'c\SALES_NEW.EIF' DATA DFNS

UPDATE

COMMIT

At this point, the data from the Express database is now in the analytic workspace, with the UPDATE moving it from the AW_DEMO_TEMP tablespace into the AW_DEMO one, and the COMMIT committing the transaction. If I was going to use Oracle Web Agent, or access the data through the OLAP_TABLE function, this would be all there was to it, but in our case we wanted to analyze the data using Discoverer Plus OLAP, a.k.a Discoverer "Drake". To do this we have to put the analytic workspace into Database Standard Form and then enable it for BI Beans and the OLAP API.

Creating the standard form metadata involves two steps: firstly, you need to create some additional attributes for your time dimension, so that the OLAP API knows which dimension refers to time, and it has the additional time-specific metadata (principly start and end dates for each time dimension member) required for time-series analysis. Secondly, you need to run the OLAP DML program "CREATE_DB_STDFORM" which is shipped with the OLAP Option and creates all the standard form metadata for you. Note that CREATE_DB_STDFORM only works when you have what's referred to as "Express Objects Metadata" in your Express Server database, which you'll have anyway if you created your OES database using Administrator or subsequently used it with OFA or Express Objects.

Starting off then, within the analytic workspace we identified a dimension called TIME that was obviously our time dimension, and so we started off by creating a couple of variables to hold our time attributes:

"***************************************************
" CREATE THE TIME ATTRIBUTES
"***************************************************
DEFINE TIME_END_DATE VARIABLE DATE <TIME SATI.HIERDIM>
PROPERTY 'USERDATA' FALSE
DEFINE TIME_TIME_SPAN VARIABLE INTEGER <TIME SATI.HIERDIM>
PROPERTY 'USERDATA' FALSE
UPDATE
COMMIT

Next, we defined a program that would populate the time attributes for the dimensions that go to make up the time dimension.

"***************************************************
" CREATE PROGRAM TO POPULATE TIME ATTRIBUTES
"***************************************************
DEFINE POP_TIME_ATTRS PROGRAM
PROGRAM

VARIABLE _ytd TEXT "Stores YTD time members
TRAP ON cleanup "Divert processing on error to CLEANUP label

"Define dimensions for each level with date data types
IF NOT EXISTS('w_temp')
THEN DO
DEFINE w_temp DIMENSION WEEK
CONSIDER w_temp
VNF T<FFFF><PP>
DOEND
ELSE MAINTAIN w_temp DELETE ALL

IF NOT EXISTS('m_temp')
THEN DO
DEFINE m_temp DIMENSION MONTH
CONSIDER m_temp
VNF M<M>.<YY>
DOEND
ELSE MAINTAIN m_temp DELETE ALL

IF NOT EXISTS('q_temp')
THEN DO
DEFINE q_temp DIMENSION QUARTER
CONSIDER q_temp
VNF Q<P>.<FF>
DOEND
ELSE MAINTAIN q_temp DELETE ALL

IF NOT EXISTS('y_temp')
THEN DO
DEFINE y_temp DIMENSION YEAR
CONSIDER y_temp
VNF YR.<YYYY>
DOEND
ELSE MAINTAIN y_temp DELETE ALL

"Define a valueset to store time members
IF NOT EXISTS('t_list')
THEN DEFINE t_list VALUESET TIME
ELSE LIMIT t_list TO NA

"Define a one-dimensional time self-relation for rollup
IF NOT EXISTS('time_parentrel')
THEN DEFINE time_parentrel RELATION time <time>
ELSE time_parentrel = NA

"Initialize target variables
ALLSTAT
time_time_span = NA
time_end_date = NA


LIMIT SATI.hierdim TO 'STANDARD'
"Select all time members at the day level
LIMIT time TO SATI.levelrel EQ 'L8'
"Store days in the valueset
LIMIT t_list TO time

"Populate W_TEMP so all days have a DAY data type
MAINTAIN w_temp MERGE values(t_list)

rpr w_temp

"Calculate the end date
FOR w_temp
IF ISVALUE(time w_temp)
then time_end_date(time, w_temp) = ENDDATE(w_temp)

"The timespan for day is always 7
time_time_span = 7

" Store months in m_temp
LIMIT time TO SATI.levelrel EQ 'L7'
LIMIT t_list TO time
MAINTAIN m_temp MERGE VALUES(t_list)
FOR m_temp
time_end_date(time, m_temp) = ENDDATE(m_temp)

" Store quarters in q_temp
LIMIT time TO SATI.levelrel EQ 'L6'
LIMIT t_list TO time
MAINTAIN q_temp MERGE VALUES(t_list)
FOR q_temp
time_end_date(time, q_temp) = ENDDATE(q_temp)

" Store years in y_temp
LIMIT time TO SATI.levelrel EQ 'L5'
LIMIT t_list TO time
MAINTAIN y_temp MERGE VALUES(t_list)
FOR y_temp
time_end_date(time, y_temp) = ENDDATE(y_temp)

" Rollup time span for quarters and years
LIMIT SATI.hierdim TO ALL
LIMIT time TO ALL
FOR SATI.hierdim
DO
time_parentrel = SATI.parent
ROLLUP time_time_span OVER time USING time_parentrel
DOEND

CLEANUP:
"Delete temporary objects
DELETE w_temp m_temp q_temp y_temp t_list time_parentrel

after which we ran the program:

"***************************************************
" EXECUTE PROGRAM TO POPULATE TIME ATTRIBUTES
"***************************************************
CALL pop_time_attrs
UPDATE
COMMIT

Next we had to sort the TIME dimension:

"***************************************************
" SORT THE TIME DIMENSION
"***************************************************
DEFINE time_vset VALUESET time
LIMIT time TO ALL
"Sort levels in descending order and time periods in ascending order
SORT time D SATI.levelrel A time_end_date
LIMIT time_vset TO time
MAINTAIN time MOVE VALUES(time_vset) FIRST
UPDATE
COMMIT

and then set the time properties for the TIME dimension (to tell the OLAP API that this was our time dimension)

"***************************************************
" SET TIME PROPERTIES
"***************************************************
CONSIDER time
PROPERTY 'DIMTYPE' 1
PROPERTY 'END_DATE' 'TIME_END_DATE'
PROPERTY 'TIME_SPAN' 'TIME_TIME_SPAN'
UPDATE
COMMIT

Finally, now that we'd created the additional time attributes required for the OLAP API, we could then run the CREATE_DB_STDFORM routine.

"***************************************************
" CREATE THE STANDARD FORM METADATA
"***************************************************
CALL CREATE_DB_STDFORM('sales_new')
UPDATE
COMMIT

Now we had an analytic workspace that was in database standard form. My next step, from working with earlier versions of Oracle OLAP and Analytic Workspace Manager, was going to be to enable the standard form analytic workspace for OLAP API and BI Beans. However, when I switched back to the OLAP Catalog view within AWM and (thankfully) noticed that the AW was now appearing, the option to enable it was greyed out.

So what was going on here? Well of all people I should have known, as what's happened now is that the dynamic enablement feature that Oracle (and myself) have been talking about for the last few months has enabled the AW for OLAP API and BI Beans for me. I must admit I thought this was coming with the 10.1.0.4 patch, together with AWM2, but it's actually a feature of 10g OLAP from 10.1.0.3A upwards, so in this instance there was no need to manually enable the AW for the OLAP API, and that's why the option was greyed out.

So now it's over to Application Server 10g Release 2 to analyze the cube using Discoverer Drake. The first step with this though is to use Application Server Control to firstly create the Discoverer Catalog, and then enable the DEMO schema for use with the Catalog.

Then it was just a simple case of starting up Discoverer Plus OLAP, choosing the OLAP datasource option, logging in as my DEMO user, and then creating a workbook against my migrated data.

So, how difficult was this exercise? Well, the import of the AW was straighforward (although it took about 12 hours and eventually took up around 14GB of TEMP space and 12GB of regular tablespace), and I guess the only bit that was complicated was the creation of the time dimension attributes (thanks to Jon Mead for putting the script together). That particular bit is going to be different for each migrated OES database, as you're going to have to understand how your time dimension is set up, but then again if you're currently using Express and can put simple programs together, this shouldn't be difficult. After that, the creation of the standard form metadata was pretty simple, and now with 10.1.0.3A upwards there's no need for the further step of enablement for the OLAP API. Once you've got this metadata created, displaying the data through Drake is a simple step as everything just works as if it was regular relational data.

More details on the steps required to create database standard form metadata, including an example that uses the XADEMO database, can be found in "Converting Oracle Express Databases To Standard Form" in the online docs.