The OLAP Procedure

Developing and Managing Shared Dimensions

Creating a Shared Dimension

As with cubes, there are three ways to build a shared dimension with PROC OLAP:

Long form: This option builds the metadata registration and physical files from statements specifying hierarchies, levels, and so on.
Short form: This option builds the physical files from an existing metadata registration. SAS OLAP Cube Studio uses this syntax to build shared dimensions.
REGISTER_ONLY: This option on the procedure statement builds only the metadata registration.

The following example shows the syntax for the METASVR statement using the REGISTER_ONLY option.

PROC OLAP REGISTER_ONLY;
  METASVR OLAP_SCHEMA=schema-name <options>;

OLAP_SCHEMA=schema-name

specifies the OLAP schema that will contain the shared dimension.

options

specify the metadata server connection options.

The following example shows the syntax for specifying a shared dimension and the location of its metadata.

DIMENSION [dim-name | "/folder/dim-name"] SHARED PATH="path-name" <options>

dim-name | "/folder/dim-name"

specifies the name of the shared dimension. dim-name is the dimension name, and "/folder/.." specifies the fully qualified metadata folder path in which the dimension's metadata should be stored. If the folder path is omitted, the shared dimension metadata is stored in the OLAP schema's metadata folder.

SHARED

indicates that this is a shared dimension.

PATH="path-name"

specifies the physical or logical path to the location of a new dimension. Within the specified path, the dimension is stored in a directory that uses the name of the dimension. For example, if you enter the path 'C:\shared_dimensions' and the dimension name is Customer, then the dimension is stored in 'C:\shared_dimensions\Customer'. (If the folder already exists, PROC OLAP will generate a unique pathname based on that path.) Enclose the path within quotation marks.

options

specify the metadata server connection options. All other dimension options are supported for shared dimensions, with the exception of FACTKEY=. This option is used when a shared dimension is included in a cube definition. (See USE_DIMENSION statement.) Use of the FACTKEY= option is ignored.

The following statements complete the definition of the shared dimension and are otherwise the same as those used with private dimensions.

HIERARCHY hier-name LEVELS=(lev-name...lev-nameN)<options>;

LEVEL lev-name <options>;

PROPERTY prop-name LEVEL=lev-name <options>;

Updating a Shared Dimension

The following is the syntax for updating a shared dimension.

DIMENSION dim-name SHARED UPDATE_DIMENSION= [ MEMBERS | MEMBERS_AND_PROPERTIES ]
		[DIMTABLE=dim-table];

UPDATE_DIMENSION= [ MEMBERS | MEMBERS_AND_PROPERTIES ]

causes the specified shared dimension to be rebuilt based on the current contents of the dimension table and the current contents of the OMR metadata. If the MEMBERS_AND_PROPERTIES keyword is specified, then existing members will have their properties updated as well.

[DIMTABLE=dim-table]

allows a temporary input data source to be used for the update. This does NOT change the dimension table defined with the shared dimension's metadata registration.

Any cubes using the shared dimension can be disabled during the update by the server administrator. When the cube is enabled, the new members and properties are available to the server. If the cube is not disabled, then the new members are visible as new sessions, or new queries cause the cube to be reopened. (This is similar to the in-place cube update facility.) No data is associated with the new members until the cube itself is updated with new facts. NUNIQUE measure values change with the addition of the new members.

Changing Captions and Descriptions

The following syntax is used to change captions or descriptions.

PROC OLAP UPDATE_DISPLAY_NAMES

METASVR <options>;

DIMENSION dim-name SHARED [CAPTION=<caption>][DESCRIPTION=<desc>];

HIERARCHY hier-name [CAPTION=<caption>][DESCRIPTION=<desc>];

LEVEL level-name [CAPTION=<caption>][DESCRIPTION=<desc>];

PROPERTY prop-name [CAPTION=<caption>][DESCRIPTION=<desc>];

UPDATE_DISPLAY_NAMES

indicates that the shared dimension's captions and descriptions should be updated. Unlike updating a cube, updating a shared dimension's captions and descriptions does not require the shared dimension to be locked. Thus cubes that use the shared dimension can be queried while its captions and descriptions are updated. As with new members, these updates become visible to cubes as new sessions or queries cause the cube to be reopened.

Reorganizing a Shared Dimension

The following is the syntax for reorganizing a shared dimension.

PROC OLAP REORGANIZE_LEVELS;
METASVR <options>;
DIMENSION [ dim-name | "/folderpath/dim-name" ] SHARED;

REORGANIZE_LEVELS

causes the captions for all levels contained in the shared dimension identified by the DIMENSION statement to be reorganized. The member trees for the hierarchies are rebuilt. This is an online feature. That is, cubes can continue to be online during the reorganization.

REORGANIZE_LEVEL level-name ;

reorganizes only the specified level. This statement can be used as an alternative to the REORGANIZE_LEVELS option.

When the reorganization is complete, a list of all cubes that use the dimension and need to be synchronized is displayed.

All cubes using a shared dimension whose levels have been reorganized continue to reference the previous version of the shared dimension until their aggregations have been synchronized.

If a second reorganization is attempted on the shared dimension before a cube has been synchronized to the first one, then PROC OLAP shows an error and displays a list of the cubes affected. No other updates to the shared dimension are allowed as long as there are any cubes that still need to be synchronized. No updates to a cube are allowed until its aggregations have been synchronized. The cube can instead be rebuilt, using either the long form or short form, at which time it will again have access to all members of the shared dimension. Or it can be deleted.

Deleting a Shared Dimension

The following is the syntax for deleting a shared dimension.

PROC OLAP [ DELETE | DELETE_PHYSICAL ];
METASVR <options>;
DIMENSION dim-name SHARED;

DELETE

deletes the specified shared dimension.

DELETE_PHYSICAL

deletes only the physical files associated with the dimension and leaves the metadata registration. In either case, however, if there are cubes that are referencing this dimension, the deletion is disallowed and the names of the cubes using the dimension are displayed.

Using a Shared Dimension in a Cube

The following is the syntax for using a shared dimension in a cube.

PROC OLAP CUBE=cube-name (FACT|DATA)=fact-table <options>;
METASVR <options>;
USE_DIMENSION [dim-name | "/folder/dim-name"] [OLAP_SCHEMA=schema-name]
		FACTKEY=fact-table column;

USE_DIMENSION [dim-name | "/folder/dim-name"]

specifies a shared dimension to be included in this cube.

OLAP_SCHEMA=schema-name

specifies the OLAP schema that will contain the shared dimension. The schema option is required if shared dimension's schema is different from the schema specified in the METASVR statement.

FACTKEY= fact-table column

provides the name of the key fact table column that corresponds to the DIMKEY key column in the dimension table. This option is required.

HIERARCHY, LEVEL and PROPERTY statements are not needed (or allowed) for those objects in the dimension. They have already been defined and are implied by the use of the dimension in the cube.

If the EMPTY_CHAR or EMPTY_NUM option is specified in the PROC OLAP statement, it does not apply to any shared dimensions used in the cube. It only affects private dimensions defined for the cube.

Synchronizing a Cube After Reorganizing a Shared Dimension

The following is the syntax for synchronizing a cube after reorganizing a shared dimension.

PROC OLAP CUBE=cube-name [SYNC_AGGRS | SYNCHRONIZE_AGGRS] <options>;
METASVR <options>;

SYNC_AGGRS

indicates that the cube aggregations will be checked for consistency with the current members trees for all shared dimensions that it uses. If the cube requires synchronization, the cube aggregations are rebuilt.