B Upgrading From Express Server

B.4.1.1 What CREATE_DB_STDFORM Does For You

CREATE_DB_STDFORM enables you to start using the BI Beans against your data in a matter of minutes. The conversion step from Oracle Express Objects metadata to database standard form metadata involves running a single program. You can then enable the analytic workspace for the BI Beans using a dialog in Analytic Workspace Manager. The entire process, from importing the EIF file to querying views of the analytic workspace using a BI Beans application, is very quick and fully automated.

If you load data only at the base level, then you can use the Aggregation wizards in Analytic Workspace Manager to create and deploy an aggregation plan. This method of aggregation is faster and more flexible than the ROLLUP command.

B.4.1.2 What CREATE_DB_STDFORM Does Not Do For You

The conversion process circumvents the usual first step in creating an analytic workspace: developing a logical data model in the OLAP Catalog and mapping the logical objects to the data source. In this usual scenario, if you want to modify the logical model, you modify the OLAP Catalog; the tools make the appropriate changes to the standard form catalogs by refreshing them from the OLAP Catalog. This maintenance process is not available to analytic workspaces converted by CREATE_DB_STDFORM. Thus, you must do the following tasks manually:

• If you want to perform time-based analysis on your data, you must identify all time dimensions and populate end date and time span attributes before using CREATE_DB_STDFORM. A sample program is provided in this appendix.

• Your analytic workspace may contain programs with references to obsolete commands. You must revise them. You may also want to use some of the new features. For example, you can handle sparse data with composites (instead of conjoints) if you are not doing so already. You must define new variables and copy the data from the old variables (or reload it from the data source) to make this change.

• You cannot use the Refresh Wizard in Analytic Workspace Manager to copy new data into a converted analytic workspace. Instead, you must modify the load programs or create new ones, and run them manually.

• You must make any changes to the standard form metadata manually using the MAINTAIN command and qualified data references.

B.4.2.1 CREATE_DB_STDFORM Syntax

The CREATE_DB_STDFORM program runs the Oracle Express Objects conversion tool. It has this syntax:

CREATE_DB_STDFORM(aw, [mode], [debug], [directory], [filename], [metacheck])

Where:

aw is the name of the analytic workspace (TEXT)

mode is the attachment mode (RO | RW | RWX)

debug controls whether the debugger runs (YES | NO)

directory is a database directory where the debug file is written (TEXT)

filename is the name of the debug file (TEXT)

metacheck controls whether a metadata check precedes the conversion (YES | NO)

For example, the following command attaches XADEMO in read/write mode, verifies that the Oracle Express Objects metadata is complete, converts the analytic workspace to standard form, and sends status messages to the screen:

CALL CREATE_DB_STDFORM('xademo')

The next command attaches XADEMO in read/write exclusive mode and redirects the status messages to a file named xademo.log in a database directory named xademo_dir. It also performs the metadata check.

CALL CREATE_DB_STDFORM('xademo', 'rwx', yes, 'xademo_dir', 'xademo.log')

B.4.2.2 Procedure: Converting From Oracle Express Objects to Standard Form

Most of the steps for converting to standard form (such as creating a new analytic workspace and importing the EIF file) can be done using the Object View in Analytic Workspace Manager. However, this procedure uses the command-line interface provided by OLAP Worksheet, on the basis that users making this conversion are already familiar with OLAP DML commands.

Follow these steps to use the Oracle Express Objects metadata conversion tool to create a standard form analytic workspace.

1. Create an EIF file from your Oracle Express Objects database, and copy the file to a physical directory that is mapped to a database directory.

   For information about database directories, refer to Permitting Access to External Files.

2. Open Analytic Workspace Manager and attach to Oracle Database, as described in "Introduction to Analytic Workspace Manager".

3. From the Tools menu, choose OLAP Worksheet.

   OLAP Worksheet opens in a separate window. For information about using OLAP Worksheet, refer to "Using OLAP Worksheet to Execute OLAP DML".

4. Create a new analytic workspace from the EIF file using commands like these:

   AW CREATE aw
   IMPORT ALL FROM EIF FILE 'directory/filename.eif' DATA DFNS
   UPDATE
   COMMIT
   
   

5. Identify the time dimensions:

   LIMIT name TO OBJ(PROPERTY 'DIMTYPE') EQ 1
   REPORT name
   
   

6. Identify the hierarchy dimension for each time dimension:

   SHOW OBJ(PROPERTY 'HIERDIM' timedim)
   
   

   Note: The Oracle Express Objects metadata identifies all of the objects that support hierarchies and levels for a dimension. You can use the FULLDSC command to see all of the properties of a dimension, or use the OBJ function as shown here to obtain the value of particular properties, such as HIERDIM, LEVELDIM, and LEVELREL.

7. Create date and time span attributes for each dimension.

   DEFINE TIME_TIME_SPAN VARIABLE INTEGER <timedim hierdim>
   PROPERTY 'USERDATA' FALSE
   
   DEFINE TIME_END_DATE VARIABLE DATE <timedim hierdim>
   PROPERTY 'USERDATA' FALSE
   
   

8. Populate the end date and time span attributes, as described in "Populating Time Attributes".

9. Set properties on the Time dimension:

   CONSIDER timedim
   PROPERTY 'END_DATE' attribute_name
   PROPERTY 'TIME_SPAN' attribute_name
   
   

   The END_DATE and TIME_SPAN values (attribute_name) identify the names of the variables that you just created.

10. Run the conversion tool with a command like this:

    CALL CREATE_DB_STDFORM('aw')
    
    

    Refer to the syntax description in "CREATE_DB_STDFORM Syntax".

11. After the conversion tool completes successfully, save the changes:

    UPDATE
    COMMIT
    
    

    You now have a standard form analytic workspace.

12. Enable the workspace for the BI Beans. Refer to "Enabling an Analytic Workspace for an Application".

    You can do this step now or after you have completed the other steps in this procedure.

13. To refresh the analytic workspace with new data, revise and run the data loader programs, as described in "Revising the Load Programs".

B.4.3.1 Sorting Time Dimension Members

If the Time members are not already sorted in chronological order within levels, then use a program like the one shown in "Sorting Dimension Members" to sort them correctly. This topic assumes that your analytic workspace contains an embedded total dimension for time periods.

B.4.3.2 Creating and Populating End Date and Time Span Attributes

The end date and time span attributes are variables dimensioned by Time and Time's hierdim dimension. The end date variable must be defined with a DATE data type.

The method that you use to populate the end date and time span attributes depends on your data source and the format of your Time dimension members. If the information is available from your original data source (that is, the source from which you populated the Express database), then you can load the information using a file reader program like those discussed in "Reading Flat Files". Otherwise, you must derive the information from the dimension members or their descriptions. An example of this method is shown in "Populating the XADEMO Time Attributes".

B.4.3.3 Setting Properties on Time Objects

You must define and set the following properties before running CREATE_DB_STDFORM:

• On the Time dimension, set the END_DATE and TIME_SPAN properties to the object names for these attributes. The DIMTYPE property should be set to 1 already.

• On the end-date and time-span attributes, set the USERDATA property to FALSE.

B.4.5.1 Creating a Standard Form XADEMO Analytic Workspace

Suppose that an EIF file named xademo.eif is located in a system directory named \users\oracle\xademo_files. Take these steps to create a standard form analytic workspace from this file.

1. Log in to your Oracle database as the SYSTEM user and create the XADEMO user, permanent and temporary tablespaces, and a database directory for access to the EIF file.

   CREATE TABLESPACE olapdata DATAFILE '$ORACLE_HOME/oradata/olapdata.dbf'
      SIZE 5M REUSE AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
      EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
   
   CREATE TEMPORARY TABLESPACE olaptmp TEMPFILE '$ORACLE_HOME/oradata/olaptmp.tmp'
      SIZE 5M REUSE AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
      EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K;
   
   CREATE USER xademo IDENTIFIED BY 'xademo'
      DEFAULT TABLESPACE olapdata 
      TEMPORARY TABLESPACE olaptmp
      QUOTA UNLIMITED ON olapdata
      QUOTA UNLIMITED ON olaptmp
      ACCOUNT UNLOCK;
   
   CREATE DIRECTORY xademo_dir as '/users/oracle/OraHome1/xademo_files';
   GRANT READ ON DIRECTORY xademo_dir TO xademo;
   
   

   Refer to Chapter 12 for information about performing these tasks.

2. Open Analytic Workspace Manager and connect to your Oracle Database as the XADEMO user.

3. Open OLAP Worksheet.

4. Create an analytic workspace from the EIF file:

   AW CREATE xademo
   IMPORT ALL FROM EIF FILE 'olapdata/xademo.eif' DATA DFNS
   UPDATE
   COMMIT
   
   

5. Identify the time dimensions:

   LIMIT name TO OBJ(PROPERTY 'DIMTYPE') EQ 1
   REPORT name
   
   NAME
   --------------
   TIME
   QUARTER
   YEAR
   MONTH
   
   

   This example shows how to provide support to the TIME dimension.

6. Identify the HIERDIM dimension for TIME.

   SHOW OBJ(PROPERTY 'HIERDIM' 'TIME')
   
   T0.HIERDIM
   
   

7. Create the TIME_END_DATE and TIME_TIME_SPAN variables.

   DEFINE TIME_END_DATE VARIABLE DATE <TIME T0.HIERDIM>
   PROPERTY 'USERDATA' FALSE
   DEFINE TIME_TIME_SPAN VARIABLE INTEGER <TIME T0.HIERDIM>
   PROPERTY 'USERDATA' FALSE
   
   

8. Populate the TIME_END_DATE and TIME_TIME_SPAN variables, as described in the following sections.

9. Set the properties on TIME.

   CONSIDER time
   PROPERTY 'END_DATE' 'TIME_END_DATE'
   PROPERTY 'TIME_SPAN' 'TIME_TIME_SPAN'
   
   

10. Convert the analytic workspace to database standard form:

11. CALL CREATE_DB_STDFORM('xademo')
    UPDATE
    COMMIT
    
    

The XADEMO database does not have a data loader program, so no example is provided here. However, Chapter 11 includes examples of file load and SQL fetch programs.

B.4.5.2 About the Time Dimension in XADEMO

Oracle Express Objects metadata stores the names of supporting objects in properties on the TIME dimension, as shown in Table B-3.

By using the OBJ function, you can discover the names of objects that support the TIME dimension:

SHOW OBJ(PROPERTY 'LEVELDIM' 'TIME')
T0.LEVELDIM

SHOW OBJ(PROPERTY 'LEVELLABELFRM' 'TIME')
T0.LVLLABFRM

The TIME dimension has two hierarchies, which are listed in the T0.LEVELDIM dimension. They are named STANDARD and YTD. The following report shows sample TIME members at each level.

REPORT DOWN time t0.levelrel W 20 t0.lvllabfrm
 
           ----------------------------T0.HIERDIM-----------------------------
           ------------STANDARD------------- ---------------YTD---------------
TIME       T0.LEVELREL      T0.LVLLABFRM     T0.LEVELREL      T0.LVLLABFRM
---------- ------------ -------------------- ------------ --------------------
JAN96      L3           Month(s)             L5           YTD Month(s) Detail
FEB96      L3           Month(s)             L5           YTD Month(s) Detail
Q1.96      L2           Quarter(s)           NA           NA
LAST.YTD   NA           NA                   L4           YTD Summaries
1996       L1           Year(s)              NA           NA

B.4.5.3 Populating the XADEMO Time Attributes

The POP_TIME_ATTRS program shown in Example B-1 populates the TIME_END_DATE and TIME_TIME_SPAN variables.

For TIME_END_DATE, the program uses the ENDDATE function to identify the last day of each time period. The ENDDATE function only operates on dimensions with a time data type (such as MONTH and YEAR). However, the XADEMO TIME dimension has a TEXT data type. Several transformations are needed before the ENDDATE function can be used. The program takes these steps:

1. For each level, defines a dimension with the appropriate data type (MONTH, QUARTER, or YEAR). In the example, the dimensions are named M_TEMP, Q_TEMP, and Y_TEMP.

2. Stores the names of the dimension members for particular level in a valueset. In the example, the valueset is named T_LIST.

3. Uses the current status of the T_LIST valueset to add members to the new dimensions (M_TEMP, Q_TEMP, and Y_TEMP).

For TIME_TIME_SPAN, the program extracts the first two characters from TIME_END_DATE at the month level, which has values like 30APR96, to get the number of days in each month.

The program then uses the ROLLUP command to calculate the number of days in each quarter and year. T0.PARENT is a self-relation that identifies the parent-child relationships among dimension members. However, T0.PARENT and TIME_TIME_SPAN are both dimensioned by T0.HIERDIM, so ROLLUP cannot use T0.PARENT. Instead, the program creates a relation named TIME_PARENTREL dimensioned only by TIME, populates it from T0.PARENT, and uses the new relation in the ROLLUP command.

Note that aggmaps are more efficient than ROLLUP, but since this case involves just a single dimension in which all aggregate values are stored, ROLLUP is slightly more convenient and the performance differences are negligible.

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('m_temp')
  THEN DEFINE m_temp DIMENSION MONTH
  ELSE MAINTAIN m_temp DELETE ALL

IF NOT EXISTS('q_temp')
  THEN DEFINE q_temp DIMENSION QUARTER
  ELSE MAINTAIN q_temp DELETE ALL

" Format years like TIME year members (1997 instead of YR97)
IF NOT EXISTS('y_temp')
  THEN DO
  DEFINE y_temp DIMENSION YEAR
  CONSIDER y_temp
  VNF <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
" *******************************************  
"    Set values for the STANDARD hierarchy
" *******************************************
LIMIT t0.hierdim TO 'STANDARD'
" Select all time members at the month level
LIMIT time TO t0.levelrel 'L3'
" Store months in the valueset
LIMIT t_list TO time
" Populate M_TEMP so all months have a MONTH data type
MAINTAIN m_temp MERGE values(t_list)
" Calculate the end date
FOR m_temp
   time_end_date(time, m_temp) = ENDDATE(m_temp)
" Extract the number of days in each month
time_time_span = CONVERT(EXTCHARS(time_end_date, 1, 2), DECIMAL)
 
" Store quarters in q_temp
LIMIT time TO t0.levelrel 'L2'
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 t0.levelrel 'L1'
LIMIT t_list TO time
MAINTAIN y_temp MERGE VALUES(t_list)
FOR y_temp
   time_end_date(time, y_temp) = ENDDATE(y_temp)   
" *******************************************  
"    Set values for the YTD hierarchy
" *******************************************
LIMIT t0.hierdim TO 'YTD'
" Limit status of months to YTD
LIMIT time TO t0.levelrel 'L5'  
LIMIT t_list TO time
LIMIT m_temp TO t_list
 
" Calculate end date and time span for months
FOR m_temp
   time_end_date(time, m_temp) = ENDDATE(m_temp)
time_time_span = CONVERT(EXTCHARS(time_end_date, 1, 2), DECIMAL)
 
" Get current and previous YTD
LIMIT time TO t0.parent EQ 'LAST.YTD'
LIMIT time KEEP LAST 1
_ytd = time
time_end_date(time, 'LAST.YTD') = time_end_date(time, _ytd)
LIMIT time TO t0.parent EQ 'CURRENT.YTD'
LIMIT time KEEP LAST 1
_ytd = time
time_end_date(time, 'CURRENT.YTD') = time_end_date(time, _ytd)
 
" Rollup time span for quarters and years
LIMIT t0.hierdim TO ALL
LIMIT time TO ALL
FOR t0.hierdim
  DO
  time_parentrel = t0.parent
  ROLLUP time_time_span OVER time USING time_parentrel
  DOEND
 
CLEANUP:
" Delete temporary objects
DELETE m_temp q_temp y_temp t_list time_parentrel
END