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 (EIF))

• EXPORT (spreadsheet)

EXPORT (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.

EXPORT (to EIF) is commonly used in conjunction with IMPORT (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 the EIFVERSION option for information about verifying the target version number.

Syntax

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

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

     [NOTEMPDATA] [NLS_CHARSET charset-exp] [AGGREGATE | NOAGGR]

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 (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-name

A text expression that is the name of the file to which output should be written. Unless the file is in the current directory, you must include the name of the directory object in the name of the file.

Note:

Directory objects are defined in the database, and they control access to directories and file in those directories. You can use the CDA command to identify and specify a current directory object. Contact your Oracle DBA for access rights to a directory object where your database user name can read and write files.

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-name. 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.

AGGREGATE

Export aggregated data. This is the default behavior.

NOAGGR

Do not export aggregated data.

Notes

Exporting Relations

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

Exporting 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.

Exporting Concat Dimensions or Objects that Use a Concat Dimension

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. (Note that you cannot use the EXCLUDING phrase with the EXPORT ALL keyword.)

Exporting Dimension Surrogates

When you export a dimension surrogate, Oracle OLAP also exports the dimension of the surrogate. For more information, see "Importing and Exporting Dimension Surrogates".

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 (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 the Type of a Conjoint Dimension

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.

Exporting Unnamed Composites

When you export or import an object with an unnamed composite in its definition, the composite is automatically exported or imported with the object. You cannot import or export an unnamed composite 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.

Saving SEGWIDTH Setting Information

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.

Exporting Objects with the Same Name From Two Different Workspaces

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.

Exporting a PERMIT_READ or PERMIT_WRITE Program

The contents of a PERMIT_READ or a PERMIT_WRITE program is emptied when exported. To successfully copy the contents of these programs to and from analytic workspaces, rename them before using EXPORT (to EIF); and then, after using IMPORT (from EIF) to copy them into an analytic workspace, name them back to PERMIT_READ or PERMIT_WRITE.

Exporting TEXT and NTEXT Values

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

EXPORT actual budget TO EIF FILE 'finance.eif'

DEFINE salescatter VARIABLE TEXT <district>

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

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

IMPORT ALL FROM EIF FILE 'slice.eif' 

EXPORT (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 (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-name -

     [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.

WKS

Indicates that you want to export an Oracle OLAP worksheet to a 1-2-3 file, version 1.

WK1

Indicates that you want to export an Oracle OLAP worksheet to a 1-2-3 file, version 2.

WRK

Indicates that you want to export an Oracle OLAP worksheet to a Symphony file, version 1.0.

WR1

Indicates that you want to export an Oracle OLAP worksheet to a Symphony file, version 1.1.

DIF

Indicates that you want to export an Oracle OLAP worksheet to a data interchange format file.

FILE file-name

FILE specifies the file that you are creating. For file-name, specify a text expression that is the name of the file. Unless the file is in the current directory, you must include the name of the directory object in the name of the file.

Note:

Directory objects are defined in the database, and they control access to directories and file in those directories. You can use the CDA command to identify and specify a current directory object. Contact your Oracle DBA for access rights to a directory object where your database user name can read and write files.

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

(Default) Specifies that Oracle OLAP does not overwrite the target file when it already exists, but instead displays an error.

REWRITE

Specifies that Oracle OLAP 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-name. This allows Oracle OLAP to convert the data accurately into that character set. For information about the character sets that you can specify, see 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

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