EXPORT

The EXPORT command copies workspace objects from your analytic workspace to an external file. You can use EXPORT to copy both data and object definitions from your workspace to an EIF file, or you can use it to copy an OLAP DML worksheet object to a spreadsheet file.

Because the syntax of the EXPORT command is different depending on whether it is being used to produce an EIF file or a spreadsheet file, two separate entries are provided:

EXPORT (to EIF))
EXPORT (to spreadsheet)

EXPORT (to EIF)

The EXPORT (to EIF) command copies data and definitions from your Oracle OLAP analytic workspace to an EIF file. EXPORT also copies all dimensions of the exported data, even when you do not specify them in the command. The status of the data's dimensions in Oracle OLAP determines which values are exported. For information on exporting data dimensioned by unnamed composites, see "Unnamed Composites".

EXPORT (to EIF) is commonly used in conjunction with IMPORT (from EIF) to copy parts of one Oracle OLAP workspace to another. You export objects from the source workspace to an EIF file and then import the objects from the EIF file into the target workspace. The source and target workspaces can reside on the same platform or on different platforms. When you transfer an EIF file between computers, use a binary transfer to overcome file-format incompatibilities between platforms. The EIF file must have been created with the EIFVERSION set to a version that is less than or equal to the version number of the target workspace. See EIFVERSION for information about verifying the target version number.

Syntax

EXPORT export_item TO EIF FILE file-id [LIST] [NOPROP] -

[NOREWRITE|REWRITE] [FILESIZE n [K, M, or G]] -

[NOTEMPDATA] [NLS_CHARSET charset-exp]

where:

export_item is one of the following:

name [AS newname]

exp [SCATTER AS scattername [TYPE scattertype] [EXCLUDING (concatbasedim . . .)]

exp AS name [EXCLUDING (concatbasedim . . .)]

ALL

Arguments

name

The name of an analytic workspace object or option to be exported. You can list more than one name for export.

AS newname

Specifies a new name for the analytic workspace object or option. When you specify an expression, or a local variable, or a local valueset, then you must use AS name to provide a name for the object that IMPORT (from EIF) will use to receive the data

Important:

You cannot rename dimensions.

exp

An expression to be computed and exported. You can list more than one name at a time for export.

SCATTER AS scattername [TYPE scattertype]

When you want to export a large multidimensional object that may require multiple passes to write into memory, then you can use SCATTER AS scattername to improve file I/O performance. You must first define one or two new single-dimension text variables (scattername and scattertype) and assign text values and their corresponding data types to scattername. When you use SCATTER AS scattername, this tells Oracle OLAP to export the multidimensional expression as separate variables in the slices you have specified in scattername. When each of the slice variables is to have the same data type, you can simply make exp have that data type, in which case you will not need to use TYPE scattertype.

EXCLUDING (concatbasedim . . .)

The EXCLUDING phrase applies only to a concat dimension that you specify with the name argument. The value you specify for concatbasedim, specifies the base dimensions of the concat that Oracle OLAP does not export.

ALL

Specifies that Oracle OLAP exports all the objects currently in the status of NAME (and, therefore, not necessarily all objects in the workspace).

TO EIF FILE

Indicates that you want to create an EIF file.

file-id

A text expression that represents the name of the file. The name must be in a standard format for a file identifier.

LIST

Sends to the current outfile the definition of each object as it begins to export it. For dimensions, EXPORT indicates the number of values being exported, and for composites, it lists the number of dimension value combinations. EXPORT also produces a message that shows the total number of bytes read every two minutes and at the end of the export procedure.

NOPROP

Prevents any properties that you have assigned to each object using a PROPERTY from being written to the EIF file.

NOREWRITE

REWRITE

Specifies whether EXPORT will overwrite the target file when it already exists. NOREWRITE (the default) leaves an existing target file intact and sends an error message to the current outfile. REWRITE causes EXPORT to replace the existing file with the new EIF file.

FILESIZE n [K|M|G]

Sets the maximum size of each component file (main file and extension files) for EIF files. When a file's size grows beyond the value of FILESIZE or the current disk or location becomes full, Oracle OLAP creates an EIF extension file. See"EIF Extension Files".

FILESIZE affects component files created after it is set. Previous component files may have various sizes, determined by the FILESIZE setting at the time each one was created or by the size it reached when its disk was full.

When you do not specify K, M, or G, the value you specify for n is interpreted as bytes. When you specify K, M, or G after the value n, the value is interpreted as kilobytes, megabytes, or gigabytes, respectively.

You can set FILESIZE to any value between 81,920 bytes (80K) and 2,147,479,552 bytes (2G).

NOTEMPDATA

Prevents data in TEMP variables from being written to the EIF file.

NLS_CHARSET charset-exp

Specifies the character set that Oracle OLAP will use when exporting text data to the file specified by file-id. This allows Oracle OLAP to convert the data accurately into that character set. This argument must be the last one specified. When this argument is omitted, then Oracle OLAP exports the data in the database character set, which is recorded in the NLS_LANG option.

Notes

EIF Options

A number of options determine how EIF files are imported and exported. These options are listed in Table 12-1, "EIF Options".

Table 12-1 EIF Options

Statements	Description
EIFEXTENSIONPATH	An option that contains a list of directory objects that identify the locations where EIF extension files should be created.
EIFNAMES	An option that contains a list of the names of all the objects imported by the most recent IMPORT (from EIF) command.
EIFSHORTNAMES	An option that controls the structure of the extension of EIF overflow (extension) file names.
EIFTYPES	An option that contains a list of the types of objects that are contained in the list produced by the EIFNAMES option.
EIFUPDBYTES	An option that controls the frequency of updates when you are using the IMPORT (from EIF) command with its UPDATE keyword.
EIFVERSION	Used with the EXPORT (to EIF) and IMPORT (from EIF) commands, an option that specifies the EIF version when copying data between different versions of Express Server or Oracle OLAP.

Relations

When you export a relation, EXPORT sends out the definition and the values in status for the related dimension as well as the dimensions of the relation.

Conjoint Dimensions

When you export a conjoint dimension, make sure that the status of the base dimensions and the status of the conjoint dimension match. Since there is an implicit relation between conjoint and base dimensions, Oracle OLAP exports the base dimensions with the conjoint dimension, but it cannot export all the conjoint dimension values in the current status when the related base values are not also in status.

Concat Dimensions

When you export a concat dimension without using the EXCLUDING phrase or when you implicitly export a concat because you are exporting a variable dimensioned by the concat, an expression that uses the concat, or a concat of which the concat is a component, then Oracle OLAP exports each component of the concat dimension. Oracle OLAP uses the current status of each simple or conjoint component dimension when exporting the component. It does not use the status of the concat dimension when exporting the simple or conjoint components.

When you export a concat dimension using the EXCLUDING phrase, then the definition of the concat dimension that Oracle OLAP exports does not include the base dimensions that you specify with the concatbasedim argument. When you also export a variable or expression that uses the concat dimension, then the definition of the exported expression or variable uses the altered concat dimension definition. Oracle OLAP does not export variable or expression values that correspond to the excluded base dimensions.

You cannot use the EXCLUDING phrase with the EXPORT ALL keyword.

Dimension Surrogates

When you export a dimension surrogate, Oracle OLAP also exports the dimension of the surrogate.

Reducing Workspace Size

When you have added and then deleted many objects or dimension values, you might want to use EXPORT (from EIF) in conjunction with the IMPORT (from EIF) command to remove extra space from your analytic workspace. You can make your workspace smaller, perhaps substantially so. To do this, use the EXPORT command with the ALL keyword to put all the data in an EIF file, create another workspace with a different name, and then import the EIF file into the new workspace. You can then delete the old workspace and refer to the new one with the same workspace alias that you used for the original one.

Preserving Conjoint Type

When you export a HASH, BTREE, or NOHASH conjoint dimension to an EIF file, the conjoint type is exported along with its definition in the EIF file. When you then import the conjoint dimension into an analytic workspace, Oracle OLAP preserves the conjoint type when you import into a new dimension or a dimension already using that conjoint type. When you import the dimension into an existing dimension that does not use the same conjoint type, Oracle OLAP does not preserve the original conjoint type that was saved in the EIF file.

Unnamed Composites

When you define variables or other objects with the SPARSE keyword in the dimension list, Oracle OLAP creates an unnamed composite that corresponds to the SPARSE dimension list. When you export or import an object with the unnamed composite in its definition, the composite is automatically exported or imported with the object. Because the unnamed composite is not a regular workspace object, you cannot import or export it independently.

EIF Extension Files

EIF extension file names have the structure filename.ennn, where nnn is a three-digit number beginning with 001. For example, assume you have an EIF file named export.eif, the extension files are named export.e001, export.e002, and so on. You can set the extension to three characters by using the EIFSHORTNAMES option. Extension files are created in the same directory object as the original EIF file, unless you specify a different one with the EIFEXTENSIONPATH option.

Variable Segments Specified with SEGWIDTH

When you use the SEGWIDTH keyword of the CHGDFN command to specify the length of variable segments, segment information cannot be exported and imported automatically. You can save your SEGWIDTH settings by exporting the entire workspace, creating a new workspace, importing only the workspace objects into the new workspace, specifying segmentation, and then importing the variable data into the new workspace.

Duplicate Object Names

When you want to export two objects that have the same name from two different workspaces, you must rename one of them in the EIF file by exporting it with the AS keyword. Objects in an EIF file cannot have duplicate names.

Permission Programs: Copying to and from Analytic Workspaces

When you export PERMIT_READ or PERMIT_WRITE programs which are hidden, they are empty when imported. Additionally, when you outfile PERMIT_READ or PERMIT_WRITE programs which are hidden, then they are empty when infiled.

Tip:

Rename PERMIT_READ and PERMIT_WRITE programs before using EXPORT to EIF or OUTFILE After copying the programs to an analytic workspace using IMPORT (from EIF) or INFILE.

TEXT and NTEXT

You can export and import TEXT and NTEXT values. Both data types can be exported to a single EIF file.

Exported TEXT values are stored in the EIF file using the character set specified for the file in the EXPORT command.
Exported NTEXT values are stored in the EIF file as NTEXT (UTF8 Unicode).
NTEXT values imported into TEXT objects are converted into the database character set. This can result in data loss when the NTEXT values cannot be represented in the database character set.
TEXT values imported into NTEXT objects are converted into the NTEXT (UTF8 Unicode) character set.

Examples

Example 12-1 Exporting Variables

Suppose you want to export the values in status and the dimensions of two variables called actual and budget from your current Oracle OLAP workspace to a disk file called finance.eif in your current directory object. Use the following statement.

EXPORT actual budget TO EIF FILE 'finance.eif'

Example 12-2 Exporting a Large Object

Suppose you want to export a large, multidimensional object that is likely to require multiple passes to write into memory. To improve file I/O performance, you can create a single-dimension variable to tell Oracle OLAP how to slice the multidimensional variable into smaller pieces.

Suppose the large object is the SALES variable, which is dimensioned by month, product, and district. To specify how sales should be sliced, create a single-dimension variable, as shown in the following statement.

DEFINE salescatter VARIABLE TEXT <district>

Because salescatter is dimensioned by district, this will tell Oracle OLAP to divide sales into district slices. Because district has six values, sales will be divided into six slices. Each slice must be named. To do so, assign values to each district in salescatter. You can then assign the appropriate data type to each slice, for example, by using a QDR (qualified data reference), when desired.

To export SALES, execute the following statement.

EXPORT sales SCATTER AS salescatter TYPE TYPEVAR -
   TO EIF FILE 'slice.eif'

To import the variables, specify which of the named slices you want, as in the following statement.

IMPORT dist1 dist2 dist3 dist4 dist5 dist6 -
   FROM EIF FILE 'slice.eif'

Alternatively, you can import all of the variables.

IMPORT ALL FROM EIF FILE 'slice.eif'

EXPORT (to spreadsheet)

The EXPORT (to spreadsheet) command copies an Oracle OLAP worksheet object that you have created to a spreadsheet file and automatically translate it into the appropriate format. An analytic worksheet's dimensions form the columns and rows of the spreadsheet file. The current status of these dimensions determines which part of a worksheet is exported.

You can also export an analytic worksheet to an EIF file as described in EXPORT (to EIF). EXPORT (to spreadsheet) is commonly used to copy part of your Oracle OLAP workspace into a file that can be read by other software, such as Lotus 1-2-3, or Symphony.

Syntax

EXPORT worksheetname TO {WKS|WK1|WRK|WR1|DIF} FILE file-id -

[STATRANK] [NOREWRITE|REWRITE] [NLS_CHARSET charset-exp]

Arguments

worksheetname: An Oracle OLAP worksheet object that you have created. In any one EXPORT (to spreadsheet) command, you can export only one worksheetname to one spreadsheet file.
TO WKS
TO WK1
TO WRK
TO WR1
TO DIF: Indicates that you want to export an Oracle OLAP worksheet to a 1-2-3 file, version 1 (WKS) or version 2 (WK1); a Symphony file, version 1.0 (WRK) or version 1.1 (WR1); or a data interchange format file (DIF).
FILE file-id: A text expression that represents the name of the file you are creating. The name must be in a standard format for a file identifier.
STATRANK: Specifies that the row and column numbers exported with worksheet data should be the current status rankings of the WKSROW and WKSCOL dimensions.
NOREWRITE
REWRITE: NOREWRITE (the default) leaves the existing file intact and displays an error. When you specify REWRITE, EXPORT overwrites the target file when it already exists.
NLS_CHARSET charset-exp: Specifies the character set that Oracle OLAP will use when exporting text data to the worksheet file specified by file-id. This allows Oracle OLAP to convert the data accurately into that character set. For information about the character sets that you can specify, see the Oracle Database Globalization Support Guide. This argument must be the last one specified. When this argument is omitted, then Oracle OLAP exports the data in the database character set, which is recorded in the NLS_LANG option.

Examples

Example 12-3 Limiting Before Exporting

This example exports part of a pricing worksheet by limiting its dimensions, WKSCOL and WKSROW, before the EXPORT command.

LIMIT WKSCOL TO 2 TO 4
LIMIT WKSROW TO 3 TO 4
EXPORT pricing TO WRK FILE 'price1.wrk'