Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Migrating Express Server Databases To 10g And Discoverer "Drake"

Oracle Tips by Burleson Consulting
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 organizations 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.

 

  Learn more about the Managing the Oracle Application Server in

Oracle Application Server 10g Administration Handbook

by Oracle Press. In Book Stores Now!



 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational