What's New in the OLAP DML?

Oracle9i Release 2 provides the OLAP data manipulation language (DML) for multidimensional analysis within the Oracle database. With the OLAP option installed, you can execute DML commands for manipulating data in an analytic workspace. Users of Oracle Express Server release 6.3 will find that there are some new and changed features in the OLAP DML.

The following section describes the new features in Oracle9i OLAP:

• Oracle9i Release 2 (9.2) New Features in the OLAP DML

Oracle9i Release 2 (9.2) New Features in the OLAP DML

The following list briefly describes the new features of the OLAP DML.

• The DATABASE command and related commands have been renamed.

  Because the OLAP engine runs in the Oracle kernel and analytic workspaces are stored in relational tables, there is no separate file that stores an analytic workspace. This change is reflected in new names and new functionality for existing DML commands.

  See Also: Chapter 2, "Defining and Working with Analytic Workspaces"

   
• Access to the OLAP DML is through SQL and the OLAP API, not through XCA, SNAPI, or ODBC.

  XCA, SNAPI, and ODBC connections are no longer supported, and the related commands have been removed. Note that session sharing is not supported in the new access methods.

  See Also: The Oracle9i OLAP User's Guide for information about SQL access and the Oracle9i OLAP Developer's Guide to the OLAP API

   
• In order to save changes to an analytic workspace, you must use both the UPDATE and COMMIT commands.

  The UPDATE command moves changes from a temporary area to the dataase table in which the workspace is stored. The changes are not saved until you execute a COMMIT command, either from the OLAP DML or from SQL.

  See Also: Chapter 2, "Defining and Working with Analytic Workspaces"

   
• Custom aggregates are supported.

  Virtual dimension members can be defined at runtime using the new AGGREGATION command within a MODEL object. The AGGREGATE function then calculates data for the custom aggregate the same as any other aggregate.

  See Also: Chapter 12, "Aggregating Data"

   
• Models can be used to aggregate data over nonhierarchical dimensions.

  A MODEL command in an aggregation map executes a model either as a data maintenance step (using the AGGREGATE command) or at runtime (using the AGGREGATE function).

  See Also: Chapter 12, "Aggregating Data"

   
• Allocation of data over a hierarchy is supported.

  A new ALLOCATE command provides support for planning applications, such as enterprise budgeting and demand planning systems, which need to allocate data to lower levels of a hierarchy based on sophisticated allocation rules.

  See Also: Chapter 9, "Allocating Data"

   
• SQL IMPORT command provides a high performance method of copying data from database tables into an analytic workspace.

  The SQL IMPORT command loads fact data into workspace objects more quickly than an SQL FETCH statement.

  See Also: Chapter 10, "Working with Relational Tables"

   
• SQL PREPARE command provides high performance method of loading workspace data into database tables.

  The SQL PREPARE command includes new options that you can use to specify direct-path insertion of analytic workspace data into relational tables.

  See Also: Chapter 10, "Working with Relational Tables"

   
• Concat dimensions join values from multiple dimensions into one dimension.

  In defining a concat dimension, you can combine the values of two or more dimensions into one dimension. You can use a concat dimension to map multidimensional structures to relational schemas and thereby improve data loading from relational sources. You can also use concat dimensions in performing custom aggregations and other customized operations.

  See Also: Chapter 3, "Defining Data Objects" and Chapter 10, "Working with Relational Tables"

   
• NUMBER dimensions store numeric values other than ordinal integers.

  The Oracle OLAP DML has a new NUMBER data type that is the equivalent of the NUMBER data type in the relational database. You can define a NUMBER dimension that has NUMBER values. Oracle OLAP always interprets the values of a NUMBER dimension as dimension values and not as ordinal position values. You can use a NUMBER dimension to represent a series of unique numeric values, such as a surrogate key column in a relational database table.

  See Also: Chapter 3, "Defining Data Objects"

   
• Dimension surrogates provide alternative labels for dimension values.

  A dimension surrogate is a new type of DML object. You define a dimension surrogate based on a dimension, but the surrogate can be of a different data type than its dimension. The surrogate has the same number of positions as the dimension. You assign values to a surrogate as you would to a variable. You can use a NUMBER dimension and a dimension surrogate to load surrogate key values from a relational database into an analytic workspace, and then use those key values to load data from the relational fact table or tables into multidimensional structures.

  See Also: Chapter 3, "Defining Data Objects"

   
• Qualified object names allow you to reference identically named objects in more than one attached analytic workspace.

  In OLAP DML commands, you can specify an object using its qualified object name, which includes not only the name of the object but also the name of the analytic workspace in which the object resides.

  See Also: Chapter 2, "Defining and Working with Analytic Workspaces"

   
• Full names for analytic workspaces allow you to access workspaces that belong to another user.

  In OLAP DML commands, you can specify an analytic workspace that is in another user's shema by using the full name of the workspace. The full name includes the schema name.

  See Also: Chapter 2, "Defining and Working with Analytic Workspaces"

   
• Workspace aliases make possible short and generic names for analytic workspaces.

  Workspace aliases allow you to reference an analytic workspace using a name that is easier to type than its full name. Aliases also let you write generic code that includes a reference to a workspace but does not hard-code its name.

  See Also: Chapter 2, "Defining and Working with Analytic Workspaces"

   
• Directory aliases provide the mechanism through which OLAP DML commands can access files on disk.

  When you read from or write to a disk file using the OLAP DML, you do not directly specify the directory in which the file resides. Instead, you specify a directory alias that has been set up for your use by the Oracle database administrator.

  See Also: Chapter 11, "Reading Data from Files" and the Oracle9i OLAP User's Guide

   
• The new NTEXT data type can hold data from NCHAR and NVARCHAR2 columns in the database.

  All NTEXT values are encoded in the UTF8 Unicode transformation format.

  See Also: Chapter 3, "Defining Data Objects"

   
• The default character set for Oracle OLAP is the database character set.

  Oracle OLAP no longer has a configuration setting that specifies the default character set. The Oracle OLAP default is the same as the databse character set.

• Oracle OLAP NLS parameter settings are coordinated with the settings for the database.

  All Oracle OLAP NLS settings (such as NLS_DATE_FORMAT and NLS_LANGUAGE) reflect the session-wide NLS parameter settings. If you set the NLS options in Oracle OLAP, you change your session-wide NLS parameter settings.

  See Also: The NLS options in the Oracle9i OLAP DML Reference help

   
• The DECIMALCHARS, THOUSANDSCHARS, YESSPELL, and NOSPELL options are read-only.

  The values of these options always mirror the current session-wide NLS parameter settings. You cannot change these settings by changing the values of the Oracle OLAP options.

• The commands that gave access to operating system activities are no longer supported

  To be compatible with Oracle database conventions, Oracle OLAP does not provide direct access to system-level information and commands. Therefore, the SYSINFO function has fewer keywords, and commands such as CHDIR, CHDRIVE, MKDIR, and SHELL have been removed. In addition, EXTCALL objects are no longer supported.

  See Also: The list of deleted commands in the Oracle9i OLAP DML Reference help

   
• In-place variables are no longer supported

  Because analytic workspaces are stored in database tables, in-place variable storage is no longer applicable.

• Interactive debugging is not supported.

  You cannot use the TRACE and WATCH commands for interactive debugging in OLAP Worksheet, but you can use PRGTRACE, MODTRACE, and DBGOUTFILE to record the progress of your programs and models.

  See Also: Chapter 7, "Developing Programs" and Chapter 8, "Working with Models"

   
• Performance statistics are available through relational views, instead of OLAP DML commands

  The DGCART command and function as well as the CACHEHITS, CACHEMISSES, and CACHETRIES options have been removed. However, you can use OLAP dynamic performance views to monitor performance.

  See Also: The Oracle9i OLAP User's Guide

   
• Forecasting capabilities have been enhanced with the addition of multi-cycle periodicity.

  The FCSET command allows for multi-cycle periodicity in the forcasts created with FCOPEN, FCCLOSE, and FCEXEC.

  See Also: The FCOPEN, FCCLOSE, FCEXEC, FCQUERY, and FCSET commands in the Oracle9i OLAP DML Reference help

   
• Stripping of programs is no longer supported.

  The STRIP command has been removed. Use the HIDE command instead. In previous releases, programs were stripped of their definitions in an analytic workspace file before it was delivered as part of an application. Thus, only compiled code was delivered. Now, analytic workspaces are delivered as EIF files, which contain only definitions and cannot contain compiled code. In this new context, stripped programs would not be executable.