Previous Page | Next Page

Importing and Exporting SAS OLAP Cubes

Export SAS Package and Import SAS Package

A SAS OLAP cube contains a physical files component and a metadata component. Both of these components must be addressed and kept in-synch when copying or moving a cube. When copying or moving SAS 9.2 version cubes between SAS 9.2 systems, the physical files that compose a cube are copied with standard operating system functions and line commands. The cube metadata is then copied with Export SAS Package and Import SAS Package functions that are found in SAS OLAP Cube Studio and other SAS Intelligence Platform products.

SAS Packages - Copying the Cube Metadata Registration for a SAS OLAP Cube

Part of copying a cube includes copying the metadata registration for the cube. In SAS 9.2, OLAP cubes and their supporting objects can be exported and imported as a group in a SAS package (SPK) file. You can use the Export SAS Package function to create an (SPK) file. This package file can then be imported into another system with the Import SAS Package function. The Export SAS Package and Import SAS Package functions are part of the SAS Intelligence Platform promotion tools. These tools enable you to promote individual metadata objects or groups of objects from one metadata server to another, or from one location to another on the same metadata server.

Export SAS Package

In SAS OLAP Cube Studio you can create a SAS package to export to another system. In the tree view, determine the object or objects that you want to export. Use the CTRL key to select multiple objects. These objects can include cubes, jobs, libraries, tables, folders, or OLAP schemas. You can also select multiples of the same object. For example, you can export two cubes in the same package.

Note:   A cube must be exported with its corresponding cube job.   [cautionend]

After you select the needed objects, select Export SAS Package from the File menu or from the cube's context menu. This opens the Export SAS Package wizard. To export a SAS package, perform the following tasks:

  1. On the Welcome page of the wizard, specify the name of the SAS package that you are exporting cube objects to. You can also select the option Include dependent objects when retrieving initial collection of objects. This signals the wizard to automatically include any dependent objects for the item(s) you initially selected in the tree view.

  2. On the Select Objects to Export page of the wizard, select the objects to include in the exported SAS package.

  3. On the Summary page of the wizard, confirm the objects to include in the SAS package. Select Next. The SAS package is created.

  4. On the Export Complete page, you can view the export log and verify that the package was created.

Be aware that SAS OLAP cubes are dependent on a number of other objects. These include, in particular, cube jobs, tables, libraries, and OLAP schemas. For a successful import, all these objects need to be available on the target system. You must know whether these objects are already available in the target system. However, you can export the objects now and verify their availability later, when you import the package. If you are selecting a cube to export, always select the cube together with its job. The export does not work if the job is not present.

Import SAS Package

After you have exported the SAS package, you can import the package on the target system. Switch to your target system and make sure that the exported SAS package file and the copied cube files are available there.

At this point, you can open SAS OLAP Cube Studio and select a folder in the tree view. This selected folder is where you are importing the package to. If the OLAP schema that you need is on the target system, you can import your cube and related objects into the folder that contains your OLAP schema.

Note:   The Import SAS Package function is available only when you select a folder in the tree view.  [cautionend]

Select Import SAS Package from the File menu or from the cube's context menu. The Import SAS Package wizard opens. To import a SAS package, perform the following tasks:

  1. On the Welcome page of the wizard, specify the name of the SAS package that you are importing. On this page you can select to include the objects' access control templates. In addition, you can select to import all the objects in the package or only those that are new and do not exist in the destination folder.

  2. On the Select Objects to Import page, you can select the objects from the package that you want to import. When you click an object in the list of objects to import, any available options for that object are available on the Options tab.

    An important option to verify when importing a SAS OLAP cube is the cube option The physical cube will need to be built after the import process. This indicates that the physical cube files do exist on the target system. By default, this option is automatically selected. If you select this option, deselect any objects that are already available on the target system. Also, deselect any objects that you don't want to create in the target folder. The physical cube will be built after the cube is imported into the target system.

    Note:   A cube must be imported with its corresponding cube job.   [cautionend]

  3. After you have selected the objects to import and build, the wizard identifies those objects that you must establish metadata definitions or connection points for. The About Metadata Connections page lists the metadata objects that you must define. This list is determined by the objects that you selected on the previous page. The Import SAS Package wizard enables you to specify values for the target location that correspond to values from the source location. Depending on what you import with the cube, this can include the following objects:

    • OLAP schema (This can have the same name as the original OLAP schema, but reside in a different folder.)

    • tables (These include fact tables, dimension tables, drill-through tables, or aggregation tables.)

    • libraries

    • SAS Application Server

    • directory paths (These include the cube path and, optionally, data paths, index paths, and library paths.)

  4. Next, complete the wizard page for each metadata object identified on the About Metadata Connections page. When you have finished entering the connection point information, you can view the Summary page of the wizard. This page lists the objects that are being created and the import objects that are being mapped on the target system.

User Privilege and Permission Considerations

It is recommended that the person performing the cube export or import be the AdminUser and preferably the unrestricted user. This is because other users might have restrictions on parts of the cube that could result in a partial cube being exported and imported. It is also important that the identities, groups, and permissions for the imported data be set to correspond to the target environment security settings. Security settings can be created using the Authorization tab on the cube Properties dialog box.

Creating Connection Points

Certain metadata must exist on the target system prior to importing a cube. All metadata connection points for the cube must exist with the same names. If a metadata connection object is not found, then the import fails. The following metadata connection points must exist:

Object Connection Points Details
Cube Job(cube), OLAP schema, tables, directory path object A cube must be imported with its corresponding job(cube).
Job(cube) Cube A cube must be imported with its corresponding job(cube).
OLAP Schema SAS Application Server and its logical OLAP server Only one OLAP schema can be associated with a SAS Application Server.
Library SAS Application Server and directory path object The target application server that you select determines the SAS libraries that are available.
Tables Library If there is a matching SAS library on the target system, then that SAS library is selected by default. This occurs if the target system library has the same library name and SAS Application Server name as the original SAS library.

File Naming Considerations

When importing cube metadata, if you have not selected an import file before, then the selected file shown by default is user-default-directory\extractedcube.xml. If you have selected an import file before, then the last selected file is displayed by default.

Multi-Language Cubes

You can export and import multi-language cubes. However, only the dimension tables for the server language (the first language in the UDT statement) are verified for registration. You must ensure that all the tables are present. If one of the tables is missing when the cube is imported, the import is still successful, but the cube might not rebuild correctly.

Manually Copying Physical Files for a SAS OLAP Cube


SAS OLAP Cube Files

When you copy a SAS OLAP cube between environments, you can choose to copy the physical files that are part of the cube. To do this, copy or move all of the files from the source cube subdirectory to the target cube subdirectory. You can use standard operating system functions to copy or move the files. It is important to check and verify that the target files have the same operating system permissions as your original files. In addition, when you are copying files between environments, note that you can copy SAS OLAP cube files only among systems that have the same data representation. You cannot move between 32-bit and 64-bit systems, and you cannot move between Windows and UNIX. Manually copying SAS OLAP cube files involves the following components:


Creating Cube Directories

Before you copy the files for a cube, you must create a directory for the cube on the target system. The path that you create the directory in is known as the root path for the cube and should have the same name as the cube. The cube's path is specified with either of the following methods:

In SAS OLAP Cube Studio

When creating or editing a cube in the Cube Designer wizard, enter a file path in the Path field on the Cube Designer - General page.

Using PROC OLAP

Include the PATH= option in the PROC OLAP statement.

For example, if you create the cube OrionStar in the cube path myserver\testolap\testcube, then the cube root path is myserver\testolap\testcubes\OrionStar.

When you copy the OrionStar cube to your target cube path, create a subdirectory with the cube's name. For example, if you want to copy or move the cube to otherserver\prodolap\prodcubes, you would create the directory otherserver\prodolap\prodcubes\OrionStar.

If the cube has aggregations or indexes stored in other directories, then these files must also be copied or moved to a subdirectory with the same name. You can use the DATAPATH= and INDEXPATH= options to specify what directories the aggregations are stored in.


Manually Copying the Header Files for Cube Generations

The header files for each generation of the cube are located in subdirectories in the cube root path. They are named gen0000, gen0001, and so on. For example, if the cube OrionStar is created in the cube path \\myserver\testolap\testcubes, then the cube header files are stored in the path \\myserver\testolap\testcubes\OrionStar\gen0000. If you have made incremental updates to the cube, you might have more than one genNNNN directory. Or your genNNNN directory might have a numeric suffix other than 0000. Copy or move all the genNNNN subdirectories with their contents to the target cube root directory.


Manually Copying the MOLAP Aggregation Tables

SAS OLAP cubes store aggregated data values in tables that have the same file structure as SAS SPD Engine tables. These tables consist of a number of different files and can be broken into partitions. By default, all the component files and partitions are stored in the cube root directory. However, you might have chosen to distribute the MOLAP aggregation storage over multiple locations. You can specify distributed file storage locations with either of the following methods:

In SAS OLAP Cube Studio

When creating or editing a cube in the Cube Designer wizard, you can define the storage location for one or more aggregations. On the Aggregations page of the wizard, select the Advanced button to open the Performance Options dialog box. From here you can define the storage location for all aggregations or a single aggregation. On either the Default tab or the Aggregations tab, enter the file path in either of the following fields:

  • Physical path to indexes

  • Physical path to aggregation tables

Using PROC OLAP

Use the DATAPATH= and INDEXPATH= options in the PROC OLAP statement or in the individual AGGREGATION statements.

Copy your MOLAP aggregation tables by using operating system utilities into the target directories. Make sure to copy the files from all locations, if you have distributed your cube aggregation storage over multiple directories.


Manually Copying Rolap Files

ROLAP tables are used as data sources for SAS OLAP cubes and can be stored in many formats. These formats include SAS tables, SPD Engine tables, SPD Server tables, and tables in external RDBM systems. When you are building SAS OLAP cubes, ROLAP tables can be used as the following data sources:

You can use PROC COPY to copy or move SAS tables, SPD Engine tables, and SPD Server tables. To copy or move tables in external RDBM systems, use standard operating system functions. When you copy or move any of these files, you should verify that your target SAS OLAP Server has access to those files. In addition, you need to change the LIBNAME specifications to point to the new file locations. Librefs for an OLAP server are allocated by using an AUTOEXEC file during the SAS OLAP Server invocation or by using preassigned libraries.

Previous Page | Next Page | Top of Page

Copyright © 2010 by SAS Institute Inc., Cary, NC, USA. All rights reserved.