B Upgrading From Express Server

What the Transformation Tool Does For You

The transformation tool enables you to start using OracleBI Beans with your data in a matter of minutes. The transformation step from Oracle Express Objects metadata to database standard form metadata involves a single menu choice in Analytic Workspace Manager. The entire process, from importing the EIF file to querying views of the analytic workspace using a OracleBI Beans application, is very quick and fully automated.

If you load data only at the base level, then you can create an aggregation plan in Analytic Workspace Manager. Aggregation plans, which use the AGGREGATE subsystem, are faster and more flexible than the ROLLUP command.

If you are running Oracle Database in 9i compatibility mode, then your analytic workspace will be converted to version 9i standard form. You can upgrade your analytic workspace to 10g standard form at a later time. If you are running Oracle Database in 10g compatibility mode, then your analytic workspace will be converted to version 10g standard form.

What the Transformation Tool Does Not Do For You

The transformation process circumvents the usual first step in creating an analytic workspace, which is developing a logical data model and mapping the logical objects to the data source. The tools make the appropriate changes to the standard form catalogs. This maintenance process is not available to converted analytic workspaces. 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 transformation. 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 in the OLAP DML. For example, you can handle sparse data with composites (instead of conjoints) and partition large variables. You must define new variables and copy the data from the old variables (or reload it from the data source) to make these changes.

• If your source data is in relational tables, then you can map the logical objects (cubes, measures, dimensions, and so forth) to the appropriate columns. Then you can use the Build Wizard in Analytic Workspace Manager to refresh the data.

  or

  If your source data is not in relational tables, then you must revise your data load programs and run them manually to refresh the data.

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 transformation are already familiar with OLAP DML commands.

Follow these steps to use the Oracle Express Objects metadata transformation 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 directory object.

   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.

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 Time 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 (attribute_name) values identify the names of the variables that you just created.

10. Save these changes.

    UPDATE; COMMIT
    
    

11. Return to the Analytic Workspace Manager window, and choose View > Object View.

12. In the navigation tree, expand the Analytic Workspaces folder for your schema.

13. Right-click the analytic workspace, then choose Transform Analytic Workspace to Standard Form.

    A message appears to advise you to follow these directions. You can continue.

14. Review the output from the transformation tool to assure that all measures and dimensions have been identified properly.

15. To refresh the data from relational tables or views, map the logical objects and run the Build Wizard as described in Chapter 3.

    To refresh the analytic workspace from other sources, revise and run the data load programs, as described in "Revising the Load Programs".

Sorting Time Dimension Members

If the Time members are not already sorted in chronological order within levels, then commands like the ones shown in Example B-1 to sort them correctly. Refer to Appendix A for information about the standard form objects used in the example.

LIMIT time_dim TO ALL
"Sort levels in descending order and time periods in ascending order
SORT time_dim D time_dim_LEVELREL A time_dim_END_DATE
LIMIT member_inhier TO time_dim
MAINTAIN time_dim MOVE VALUES(valueset) FIRST

"Save these changes
UPDATE
COMMIT

Creating and Populating End Date and Time Span Attributes

The end date and time span attributes are variables dimensioned by Time. The end date variable must be defined as a DATE data type. The time span variable is typically defined as an INTEGER data type, but any numeric data type is acceptable.

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 the OLAP DML. 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".

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.

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 directory object 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
      ACCOUNT UNLOCK;
   
   CREATE DIRECTORY xademo_dir as '/users/oracle/OraHome1/xademo_files';
   GRANT READ ON DIRECTORY xademo_dir TO xademo;
   
   

   Refer to Chapter 6 for information about performing these tasks.

2. Open Analytic Workspace Manager and connect to 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 'xademo_dir/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. Repeat these procedures for the other Time dimensions.

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>
   PROPERTY 'USERDATA' FALSE
   DEFINE TIME_TIME_SPAN VARIABLE INTEGER <TIME>
   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. In the Object View, right-click XADEMO, then choose Transform Analytic Workspace to Standard Form.

Figure B-1 shows a report generated by Discoverer Plus OLAP with data from the transformed XADEMO analytic workspace.

Figure B-1 Report of XADEMO Measures

Description of "Figure B-1 Report of XADEMO Measures"

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.

LIMIT time TO FIRST 2
LIMIT time ADD ANCESTORS
REPORT DOWN time W 12 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

Populating the XADEMO Time Attributes

The POP_TIME_ATTRS program shown in Example B-2 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 is dimensioned by T0.HIERDIM, so ROLLUP cannot use T0.PARENT. Instead, the program creates a relation named TIME_PARENTREL_TMP dimensioned only by TIME, populates it from T0.PARENT, and uses the new relation in the ROLLUP command.

Note that AGGREGATE is 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
IF NOT EXISTS('time_parentrel_tmp')
  then define time_parentrel_tmp relation time <time>
  else time_parentrel_tmp = 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