Managing OLAP Cube Data

Building a Cube

The following is a summary of the cube-building process. For additional information about building and modifying SAS OLAP cubes, see the SAS OLAP Server: User's Guide.

Before building a cube, you should collect and scrub your data in addition to planning a dimensional design. When you define the cube, you define the dimensions and measures for the cube along with information about how aggregations should be created and stored. There are two methods of creating a cube:

You can submit PROC OLAP code by using either the SAS Program Editor or a batch job. If you use PROC OLAP, the cube is created, and then the cube definition is stored in a metadata repository. This is referred to as the long form of PROC OLAP.
You can use the Cube Designer interface in SAS OLAP Cube Studio to define and create the cube. The Cube Designer first stores the cube definition in a metadata repository, and then submits a shorter form of PROC OLAP code to create the cube. This is referred to as the short form of PROC OLAP.

Note: The Cube Designer can also be launched from SAS Data Integration Studio. [cautionend]

Preparations for Building a Cube

To build a cube by using either PROC OLAP or SAS OLAP Cube Studio, you must perform several preliminary tasks:

Configure a metadata server.
Define an OLAP server in the metadata. The server does not need to be running to create cubes, but it must be defined in the metadata.
Analyze the data to determine the location of the table or tables that will be used to build your cubes and what dimensions and measures will be created.
Register the table or tables that will be used to create the cube in the metadata. You do this by using SAS Data Integration Studio or by using SAS OLAP Cube Studio and SAS Management Console as follows:
- Use SAS Management Console to register the metadata for the server that will access the tables. This is a SAS Application Server with a workspace server component.
- Use SAS Management Console to register metadata for the library that contains the table.
- In SAS OLAP Cube Studio, specify the server that will access the tables. To set the server, select Tools Options. Or, if the shortcut bar is displayed, select Options to set the server.
- In SAS OLAP Cube Studio, select Source Designer to load the table definitions (or other information source) as follows:
  - From the shortcut bar, select Tools Source Designer or select Source Designer.
  - Select a Source Type (SAS, ODBC, and so on), and then select Next.
  - If you have not specified a server, or if the server that is specified is not valid, then you will be prompted again for a server.
  - Select the library that contains the tables that you want to use, and then select Next.
  - Select the tables to register and then select Next.
  - Select Finish. The table definitions are register in metadata.
- If you start to create a cube and do not see the table that you need to continue, then you can click the Define Table button in any of the windows that prompt for tables.
In the Finish window of the cube designer, you are given the option to create the physical cube. The metadata definition is always stored as you leave the Finish window. However, you can defer creation of the physical cube because it might be a resource and time intensive process. If you choose to create the cube as you leave the Finish window, then you must have a SAS Workspace Server defined that you can submit PROC OLAP code to. This server is defined in SAS Management Console.

For more information about the different data types that you can use to load cubes from, see "Loading Cubes" in the SAS OLAP Server: User's Guide.

Note: The SAS Metadata Server enables duplicate librefs to be defined in the metadata. To ensure that the correct library definition is found on the metadata server, you should assign the libref by using the LIBNAME statement for the metadata engine before submitting the PROC OLAP code. Otherwise, PROC OLAP will select the first library definition that it finds with your specified libref, and it will associate your cube metadata with that definition. The selected library definition might or might not contain a description of the data that was actually used to build your cube. For more information about using the LIBNAME statement for the metadata engine, see "Statements" in SAS Language Reference: Dictionary. [cautionend]

When a SAS OLAP cube is created, a directory for that cube is also created. This directory is assigned the same name as the cube, but in uppercase letters. For example, if you save a cube in c:\olapcubes and name the cube Campaigns, the cube is saved in the directory c:\olapcubes\CAMPAIGNS.

Storage Location Requirements for Cube Metadata and Related Objects

When storing metadata that describes a cube, the metadata objects that describe the cube and the cube's associated libraries and source tables must be stored in the same repository, or the metadata that describes the cube must be in a custom repository that is dependent on the repository that contains the library and table objects. Otherwise, you will not be able to create the cube. In addition, the library and table objects that are referenced by a cube must always be in the same repository. The following options illustrate these conditions:

The library, table, and cube objects can be in a Foundation repository.
The library, table, and cube objects can be in Project A, which is dependent on the Foundation repository.
The library and table objects can be in the Foundation repository, and the cube object can be in Project A.
The cube object cannot be in the Foundation repository, and the library and table objects cannot be in Project A.
The table object cannot be in the Foundation repository, and the library and cube objects cannot be in Project A.
The library object cannot be in the Foundation repository, and the table and cube objects cannot be in Project A.

Top of Page