Skip Headers

Oracle® OLAP Application Developer's Guide
10g Release 1 (10.1)

Part Number B10333-02
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page

Go to previous page
Go to next page
View PDF

B Upgrading From Express Server

This appendix provides upgrade instructions and identifies some of the major differences between Oracle Express Server 6.3 and Oracle OLAP. It is intended to provide a frame of reference to help you understand the material presented in this guide.

This appendix includes the following topics:

B.1 Administration

Oracle OLAP is installed as an option in Oracle Enterprise Edition, and it is now integrated with Oracle Database. While Express Server runs in a service environment, Oracle OLAP runs within the Oracle kernel.

In Oracle, the term database refers only to the relational database. An Express database is now called an analytic workspace. In Oracle OLAP, an analytic workspace can be used either as a transient data cache or as a persistent data repository. A persistent analytic workspace is stored as a LOB in a relational table, which in turn is stored in a tablespace. There are no ".db" files.

The administrative tasks for Oracle OLAP are merged with the database tool set.

B.1.1 Management Tools

Oracle Enterprise Manager encompasses the tools for administering Oracle databases, providing a common user interface across all platforms. Performance data for OLAP can be collected in system tables the same as any other Oracle database performance statistics. Oracle Enterprise Manager provides a graphical interface to SQL. Because OLAP now runs within the Oracle Database kernel, many of the basic administrative tasks (such as starting, stopping, and configuring the process) are subsumed into database management.

Analytic Workspace Manager is the tool for creating and managing analytic workspaces.

OLAP Instance Manager, oesmgr, and oescmd are not available.

B.1.2 Authentication of Users

Oracle OLAP does not use operating system identities, except for the installation user under whose identity Oracle Database is installed. You can delete other operating system identities created for use by Express Server (such as the DBA user, the Initialize user, the Default user, and individual user names) if they have no other purpose.

All authentication is performed by Oracle Database. Applications must always present credentials before opening a session, and those credentials must match a user name and password stored in the relational database. Before users can access Oracle OLAP, you must define user names and passwords in the database.

For users to access operating system files, they must have access rights to a directory object that is mapped to the physical directory path. This access is granted either to an individual user ID or to a database role.

See Also:

Chapter 12 for more information about OLAP administration tasks

B.1.3 Data Transfer

An Oracle OLAP session is always connected to the database. You do not open a connection with the database as a separate or optional step.

You can copy data between an analytic workspace objects (such as variables and dimensions) and relational tables in the following ways:

  • A PL/SQL package named DBMS_AWM provides procedures for creating an analytic workspace from relational tables. Analytic Workspace Manager provides a graphical interface to this package.

  • The OLAP DML SQL command fetches data into dimensions and variables for further manipulation. A new SQL IMPORT command facilitates bulk data transfer from relational tables into the analytic workspace, and a new SQL INSERT DIRECT command facilitates data transfer from the analytic workspace into relational tables.

  • Using SQL table functions, it is now possible for a SQL-based application to manipulate and extract data from an analytic workspace. Express Server did not permit a data transfer to be initiated externally. Analytic Workspace Manager provides a graphical interface to the OLAP_TABLE function.

ODBC is not available, and thus access to third-party databases is not available directly from Oracle OLAP.

Oracle Express Relational Access Administrator and Oracle Express Relational Access Manager are not available.

B.1.4 Localization

The Express Server language support has been replaced by Oracle Globalization Technology, which provides more extensive localization support and is much easier to administer than the localization features of Express Server. Oracle Database and Oracle OLAP use the same character set, which is selected during installation.

If you are upgrading Express databases that use translation tables, then you can delete those tables because they are not needed by Oracle OLAP. Likewise, you should check your Express programs for use of obsolete commands and keywords that supported translation tables. Support for Globalization Technology has been added to the OLAP DML. These options enable an application to query the current localization settings and override the behaviors controlled by the default language and territory.

Table B-1 identifies the Unicode character sets available in Oracle that are equivalent to the Express Server character sets. If you plan to import Express databases or to use Oracle OLAP to access multibyte data in external files, then you might find this information helpful in identifying an appropriate database character set. Note that the Express CHARSET option is now obsolete.

Table B-1 Multibyte Character Set Equivalents

Express Server Unicode Character Set

B.2 Applications Support

Oracle OLAP enables applications to access its multidimensional data directly through either a Java API or SQL. Express SPL programs can be executed using either programming method. Be sure to review all SPL programs to remove commands that are no longer available and to take advantage of new functionality.

Analytic Workspace Manager provides wizards for creating a database standard form analytic workspace from relational tables, aggregating the data, and enabling the workspace for access by the BI Beans or Oracle Discoverer. Enablement involves generating relational views of data stored in an analytic workspace, and creating metadata for those views that is in the appropriate type for the application.

You cannot run Windows C++, HTML, or Java applications that were developed for use with Express Server.

See Also:

Chapter 6 for methods of creating standard form analytic workspaces from data in relational tables

B.2.1 Programming Environment

Applications for Oracle OLAP can be developed in Java using the BI Beans. SQL-based applications can access OLAP data through views or manipulate it directly through the OLAP_TABLE functions.

OLAP Worksheet provides an interactive environment for developing stored procedures in either the OLAP DML or SQL. The PL/SQL DBMS_AW procedure executes OLAP DML commands from a SQL environment.

You cannot connect to Oracle OLAP using Express Administrator, Personal Express, or the Express Connection Utility.

See Also:

  • Chapter 4 for information about the BI Beans

  • Chapter 9 for methods of executing OLAP DML commands

B.2.2 Communications

Oracle OLAP provides communications through Oracle Call Interface (OCI) and Java Database Connectivity (JDBC).

OLAP Worksheet uses XCA for communication with the analytic workspace. However, XCA is not supported for user-developed applications and may produce unexpected results.

SNAPI is no longer available. Session sharing is not supported.

B.2.3 Metadata

The BI Beans can query data that is stored either in an analytic workspace or in relational tables. The database administrator defines OLAP Catalog metadata for both types of data source. The metadata is stored in tables and views.

Database standard form is a type of metadata stored in analytic workspaces for use by the server tools provided in Analytic Workspace Manager. This metadata is stored in properties on workspace objects and in catalogs, which are implemented as special dimensions, variables, and valuesets.

Oracle Express Administrator is not available in Oracle OLAP, and the Oracle Express Objects metadata that it generated is not used by the BI Beans.

See Also:

  • Chapter 5 for information about the OLAP Catalog.

  • Chapter 8 for information about database standard form.

B.3 Programming Language Changes

Numerous changes have been made to the Express Stored Procedure Language (now called the OLAP Data Manipulation Language or OLAP DML).

B.3.1 New Commands

Support in the following areas has been added to the OLAP DML:

Dynamic model execution
Bulk data transfers between analytic workspaces and relational tables
Byte manipulation functions
Data conversion functions
New data types

B.3.2 Obsolete Commands

Support in the following areas has been dropped:

Operating system commands

Conjoint dimensions and the ROLLUP command are still available, but composite dimensions and aggmaps are strongly recommended instead, because they are easier to manage and perform better.

See Also:

OLAP DML Reference for comprehensive lists of new, obsolete, and significantly revised commands


The UPDATE command moves analytic workspace changes from a temporary tablespace to a permanent tablespace. Your changes are not saved permanently until you execute a COMMIT command, either from your Oracle OLAP session or from SQL. A COMMIT writes the permanent tablespace to disk.

Changes that have not been moved to the permanent tablespace are not committed. If you issue a COMMIT without first updating your analytic workspace, then no changes to the analytic workspace that you made after your last UPDATE are committed to disk.

The COMMIT command executes a SQL COMMIT command. All changes made during your session are committed, whether they were made through Oracle OLAP or through another form of access (such as SQL) to the database.

B.4 Converting Oracle Express Databases to Standard Form

EIF files are used to transfer the contents of an analytic workspace from one database to another and to upgrade from an Express database. You can create an analytic workspace from an Express database simply by using EIF files to transfer the objects.

The more complex task is to create an analytic workspace in database standard form, so that you can use the current generation of Oracle OLAP tools. You may be able to leverage your investment in Express metadata to create standard form metadata. Otherwise, you must define a new logical metadata model.

B.4.1 Who Should Use CREATE_DB_STDFORM

If your Express database contains Oracle Express Objects metadata (that is, it was created by Oracle Express Administrator), then you can use a conversion program named CREATE_DB_STDFORM. Without Oracle Express Objects metadata, CREATE_DB_STDFORM cannot generate sufficient standard form metadata for the OLAP tools to work.

Especially if your source data is in flat files, then use CREATE_DB_STDFORM if possible. There are no tools currently available for creating a standard form analytic workspace directly from flat files.

If your source data is in tables or views, then you have a choice of using CREATE_DB_STDFORM to convert an Express database, or using other tools to create an analytic workspace directly from the source data. CREATE_DB_STDFORM enables you to use your Oracle Express Objects metadata instead of redefining the logical model in the OLAP Catalog. However, you must perform other steps manually, as described in "What CREATE_DB_STDFORM Does Not Do For You". You can choose which method best suits your needs.

Table B-2 identifies the upgrade options.

Table B-2 Choosing an Upgrade Path for Express Databases

If you have Oracle Express Objects metadata... And your source data is located in... THEN create a standard form analytic workspace using...
Yes Tables or views CREATE_DB_STDFORM or one of the methods described in Chapter 6.
No Tables or views One of the methods described in Chapter 6.
No Flat files Oracle Warehouse Builder as described in Chapter 6, or the method described in Chapter 11.

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 Converting From Oracle Express Objects Metadata

The Oracle Express Objects conversion tool operates on an analytic workspace. It uses the Oracle Express Objects metadata to identify the roles of various objects, and then does the following:

  • Populates existing objects with the appropriate standard form properties. For example, the Oracle Express Objects language dimension is given the AW$ROLE value of ALL_LANGUAGES.

  • Creates new standard form objects with the dimensions and properties required by standard form, and copies the data from existing objects into it. For example, standard form attributes are dimensioned by the hierdim dimension and Oracle Express Objects attributes are not. In an XADEMO analytic workspace, the conversion tool creates a variable named CHANNEL_LONG_DESCRIPTION dimensioned by CHANNEL, C0.HIERDIM, and _XA_LANGDIM, and populates it with values from C0.LONGLABEL.

  • Creates and populates standard form metadata objects, such as the standard form catalogs, member_gid and member_inhier variables, and member_familyrel and member_levelrel relations. For descriptions of these standard form objects, refer to Appendix A.

The conversion tool adds standard form objects and properties; it does not delete any Oracle Express Objects objects or properties. You can delete them manually if you wish.

The BI Beans requires a level-sorted Time dimension with period end dates and time span attributes in order to support time-based analysis.


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])


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:


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
  5. Identify the time dimensions:

    REPORT name
  6. Identify the hierarchy dimension for each time dimension:


    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.

  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:


    Refer to the syntax description in "CREATE_DB_STDFORM Syntax".

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


    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 Populating Time Attributes

A standard form Time dimension has the following characteristics:

  • Dimension members are sorted chronologically within level.

  • The AW$TYPE property has a value of 'Time'.

  • Period end date and time span attributes are defined and populated.

The conversion process sets the AW$TYPE property, defines standard form attributes for period end dates and time span, and registers this information in the standard form catalogs. It does not change the order of the Time dimension members nor populate the attributes.

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.4 Revising the Load Programs

The Refresh wizard in Analytic Workspace Manager only operates on analytic workspaces created using DBMS_AWM procedures, as described in Chapter 6. When you create an analytic workspace using CREATE_DB_STDFORM, you circumvent the mechanisms that provide the Refresh wizard with the information it needs to acquire new data. You must refresh your analytic workspace manually using OLAP DML programs.

Your analytic workspace probably contains programs generated by Express Administrator for refreshing your Express database. You can begin by modifying these programs for use in your analytic workspace; they are unusable in their current state.

Delete the following code from your load programs:

  • Calls to EDDE.MSG. This program displayed Express error messages in the Administrator graphical interface, and deleting calls to it does not affect the operation of your program.

  • Calls to EDDE.HIERMNT. This program managed the metadata associated with dimension hierarchies. It is not available for use in analytic workspaces, nor is any of the information about your data that was stored in an XPDDDATA database. You must manage any changes to the standard form metadata manually.

  • Code to establish a connection with Oracle. Since the analytic workspace is part of Oracle Database, a connection is always open.

The load programs only refresh the dimensions and measures. They do not refresh the dimension attributes, the hierarchy and level objects, or the standard form catalogs. Refer to Chapter 11 for information about writing load programs for standard form objects associated with dimensions. Refer to Appendix A for information about the standard form catalogs.

B.4.5 Example: Converting the XADEMO Database to Standard Form

This example uses an EIF file that contains objects and Oracle Express Objects metadata from an Express database named XADEMO. If you are converting an Express database, you are probably already familiar with XADEMO.

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'
    CREATE USER xademo IDENTIFIED BY 'xademo'
       DEFAULT TABLESPACE olapdata 
       QUOTA UNLIMITED ON olapdata
       QUOTA UNLIMITED ON olaptmp
    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
  5. Identify the time dimensions:

    REPORT name

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

  6. Identify the HIERDIM dimension for TIME.

  7. Create the TIME_END_DATE and TIME_TIME_SPAN variables.

  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
  10. Convert the analytic workspace to database standard form:

  11. CALL CREATE_DB_STDFORM('xademo')

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.

Table B-3 Oracle Express Objects Properties for Hierarchy and Level Support

Property Description
HIERDIM List of hierarchies (dimension)
LEVELDIM List of levels (dimension)
LEVELREL Level associated with each dimension member (relation)
LEVELLABELFRM Description of each level (formula)

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



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
           ------------STANDARD------------- ---------------YTD---------------
---------- ------------ -------------------- ------------ --------------------
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.

Example B-1 OLAP DML Program for Populating TIME Attributes

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')

IF NOT EXISTS('q_temp')

" Format years like TIME year members (1997 instead of YR97)
IF NOT EXISTS('y_temp')
  CONSIDER y_temp
" Define a valueset to store time members
IF NOT EXISTS('t_list')
" 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
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
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
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'
_ytd = time
time_end_date(time, 'LAST.YTD') = time_end_date(time, _ytd)
LIMIT time TO t0.parent EQ 'CURRENT.YTD'
_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
FOR t0.hierdim
  time_parentrel = t0.parent
  ROLLUP time_time_span OVER time USING time_parentrel
" Delete temporary objects
DELETE m_temp q_temp y_temp t_list time_parentrel