Maintaining Dimensions and Composites

How do you specify dimension values?

The first step in populating an analytic workspace is to store values in the analytic workspace's dimensions. The list of stored dimension values is called the dimension's default status list. When you first attach an analytic workspace, the default status list is the current status list of each dimension.

You can add, delete, merge, reposition, or change dimension values using the MAINTAIN command. Consequently, storing and manipulating the values of a dimension is called maintaining the dimension.

Who can maintain dimensions?

You can only maintain a dimension when you have permission to both maintain and read the dimension. Maintain permission is implicitly denied whenever read permission is restricted for a dimension, even when you specify maintain permission for the dimension.

By default, you have permission to both read and maintain dimensions. However, either or both of these permissions can be changed using the PERMIT command.

For more information on using the PERMIT command, see "Adding Security to an Analytic Workspace" and the topic for the PERMIT command in the OLAP DML Reference.

How maintaining a dimension affects dimension status

As outlined in the following table, using the MAINTAIN command sometimes affects dimension status.

IF you use the MAINTAIN command with . . .	THEN  . . .
the ADD, DELETE, MERGE, or MOVE keyword and the current status of a dimension is not ALL,	the dimension's status is reset to ALL before it performs the requested maintenance.
a dimension that has a pushed status list (that is, a status list that was created using the PUSH commands),	that dimension's pushed status list is cleared, and popping that dimension has no effect.

For more information on popping and pushing values, see "Introducing Dimension Status" and the entries for the POP and PUSH commands in the OLAP DML Reference.

Avoiding deferred maintenance

When you maintain a dimension, the objects that are dimensioned by it must be modified. If these objects are in memory, then they are modified immediately; if these objects are not in memory, then maintenance is deferred until they are loaded into memory.

In situations that involve a lot of dimension maintenance and a large update at the end, deferred maintenance can trigger errors. Examples are issuing a MAINTAIN DELETE ALL command, or performing a data load in which a large number of values is added to a dimension. Before starting such projects, load into memory the objects that are dimensioned by the dimension you are maintaining so that deferred maintenance is unnecessary. You can do this by using commands similar to the following, where the sample dimension is PRODUCT.

limit name to obj(isby product) 
load &values(name) 
maintain product add ... 

Overview: Adding values to dimensions

To add new values to the end of a dimension or composite, use the MAINTAIN command with the ADD keyword. The actual way that the values are added, and the arguments that you use vary depending on whether you are adding values to a non-time dimension, a time dimension, or a composite.

You can use the MAINTAIN command with the MERGE keyword as a quick way to make sure all dimension values on a separate list are included in a dimension. When you use this syntax, the new values from the list are automatically added and the duplicates are ignored. This method of entering dimension values can save a significant amount of time when you have a large number of values to enter.

You can use MERGE with dimensions of any data type, including time data types. However, because the ADD keyword provides a quick way of adding time dimension values, the MERGE keyword may not be as useful with time dimensions as with TEXT or ID dimensions.

Adding values to non-time dimensions

You can use the MAINTAIN command with the ADD keywords to add values to a non-time dimension in the following ways:

• You can merely specify the values that you want to add. In this case, the values are added to the end of the list of dimension values.

• You can specify both the values that you want to add and where you want the values to be placed.

Example: Adding values to non-time dimensions

This command adds ATLANTA at the beginning of the list of cities and inserts PEORIA after OMAHA.

maintain city add 'ATLANTA' first, 'PEORIA' after 'OMAHA'

Displaying the default status list for the CITY dimension shows that the new values have been added in the appropriate places in the list.

show values(city nostatus)
ATLANTA
CONCORD
LINCOLN
NEW YORK
OMAHA
PEORIA
SEATTLE

Adding values to time dimensions

You can use the MAINTAIN command with the ADD keyword to add new values to time dimensions (that is, dimensions with the DAY, WEEK, MONTH, QUARTER, or YEAR data type). You can specify what values you want to add by specifying:

• The number of periods to add at the beginning or end of an existing list of dimension values.

• A list of values. (In this case, you can specify the values as text literals or as date or text expressions.)

Regardless of how you specify the values, keep the following points in mind:

• You can specify any date that falls within the time period you want to add. For example, to add the month January 1999, you can specify any date from 01JAN99 through 31JAN99. The DATEORDER option is used to resolve any ambiguities.

• When adding values to a time dimension that does not yet have values, you must specify only the first and last values you want to add for the dimension. The gaps are automatically filled in with appropriate values for the intervening time periods.

Adding date values to time dimensions

When you add a dimension value by specifying a DATE expression or a text value that represents a complete date, keep the following points in mind:

• If a time dimension already has values, then you can add values only at the beginning or the end of the existing list. To add values, you must specify only the first or last value you want to add. The gap between the existing list and the value you specify is automatically filled in.

• When you specify a time dimension value as a date, you must provide only the date components that are relevant for the type of dimension you are maintaining:
  • For a DAY or WEEK dimension, you must supply the day, month, and year components.

  • For a MONTH or QUARTER dimension, you must supply only the month and year (for example, JUN98 or 0698 for June 1998).

  • For a YEAR dimension, you must specify only the year (for example, 98 for 1998).

For more information on the valid input styles for dates, see the entry for the DATEORDER option in the OLAP DML Reference.

Adding text values to time dimensions

When you add a dimension value by specifying the values as text literals or TEXT or ID expressions (rather than as a date), keep the following points in mind:

• If you use a TEXT expression, then each element or line is treated as a separate value.

• The values can be in either one of the following formats:
  • The format specified by the VNF (value name format) for the dimension (or in the default format for the type of dimension you are maintaining when the dimension does not have a VNF). In this case, each value explicitly indicates the time period you want to add. For example, if the VNF for a MONTH dimension is <MTXT><YY>, then the value JAN99 represents the month January 1999.

  • A valid input style for date values. For more information on the valid input styles for dates, see the entry for the DATEORDER option in the OLAP DML Reference.

For more information on the default formats for time dimensions, see the entry for the VNF command in the OLAP DML Reference.

Example: Adding values to a time dimension

Suppose you define a new time dimension, called QTR, with a data type of QUARTER, and you add dimension values for the quarters in 1998 and 1999. You must add only the first and last dimension values you want, and the intervening values will be filled in automatically.

To add the first and last quarters, you can specify any dates that fall within those quarters.

define qtr dimension quarter
maintain qtr add '01JAN98' '31DEC99'

Displaying the default status list for the dimension shows the new dimension values.

Q1.98
Q2.98
Q3.98
Q4.98
Q1.99
Q2.99
Q3.99

Updating relations when you merge new values

When you are merging values into a dimension it is a good practice to update any relations that involve that dimension:

• In some cases, using the simplified syntax of the MAINTAIN command shown below, you can update a relation at the same time you merge values into a dimension.

  MAINTAIN dimension MERGE [exp [RELATE relation] ]
  

  The exp argument specifies a dimensioned expression whose values you want to merge into the dimension; for example, the name of a dimensioned text variable that contains dimension values.

  The RELATE relation phrase specifies the name of the relation that you want to update.

  Note: The exp argument must be dimensioned and at least one of these dimensions must also be in the definition of the relation that is specified in the RELATE relation phrase.

• In other cases, you need to explicitly update any relations that involve that dimension.

For the complete syntax for the MAINTAIN command, see the entry for the command in the OLAP DML Reference. For information about explicitly updating relations, see "Assigning Values to Data Objects".

Example: Merging values into a composite

Suppose you want to define a composite, named COMP_PRODDIST, that is made up of all combinations of the first three values of the PRODUCT dimension and the first five values of the DISTRICT dimension. You can efficiently include all 15 values with the following commands.

define comp_proddist composite <product district>
limit product to first 3
limit district to first 5
maintain comp_proddist merge <product district>

This method works with conjoint dimensions as well.

Deleting values from dimensions

You can use the MAINTAIN command with the DELETE keyword to remove values from a dimension. Using the MAINTAIN command with DELETE keyword, you select the values that you want to delete in much the same way that you select values using the LIMIT command. You can select for deletion:

• One value, a list of values, a range of values, or all values

• The values that match a list of values of a named related dimension

• The values that are first, last, or in a specified position in the dimension

• The values that meet a Boolean criterion

• After it is sorted according to a specified criterion, the top or bottom n values of the dimension, or the top or bottom n performers, by percentage, of the dimension

• For a hierarchical dimension, the values that have a certain relationship within the hierarchy

• The values in the dimension that match the values in a valueset

You delete values from a dimension with a time data type (that is, DAY, WEEK, MONTH, QUARTER, or YEAR) the same way that you delete values from any other dimension except that you can delete values only from the beginning or the end of the existing list of values.

Example: Deleting values from a dimension

Suppose that you want remove from CITY all those cities with a population of less than 75,000 people. Before you issue the command, the default status list for the CITY dimension contains the six values shown below.

show values (city nostatus)
ATLANTA
CONCORD
LINCOLN
COLUMBUS
PEORIA
SEATTLE

You use the variable POPULATION.C, which contains the population for each city.

maintain city delete population.c lt 75000

Assuming that only Lincoln and Peoria have populations of fewer than 75,000, the default status list of the CITY dimension now contains the following values.

show values (city nostatus)
ATLANTA
CONCORD
COLUMBUS
SEATTLE

Deleting values from conjoint dimensions

You can use the MAINTAIN command with the DELETE keyword to delete values from a conjoint dimension.

You can also delete values from a conjoint dimension by using the MAINTAIN command directly on the base dimension of the conjoint dimension. When you delete a value from the base dimension, any values associated with that base dimension value are deleted from the conjoint dimension.

Example: Deleting dimension values from a conjoint dimension

Suppose you have a conjoint dimension named PROD_DIST with the base dimensions of PRODUCT and DISTRICT. To delete the value <'SNOWSHOES' 'ATLANTA'> from that conjoint dimension, you would use the following command.

maintain PROD_DIST delete <'SNOWSHOES' 'ATLANTA'>

Changing the position of dimension values

For dimensions that have a non-time data type, you can use the MAINTAIN command with the MOVE keyword to change the position of one or more values in a dimension list. You cannot change the position of a value in a time dimension or in a composite.

When you want to store the dimension values in alphabetical order, you can first use the SORT command to temporarily sort the values, and then use the MAINTAIN command to store the values in the sorted order.

Example: Changing the position of dimension values

Use the TEXT variable TEXTVAR to move SEATTLE to the end of the list of cities.

textvar = 'SEATTLE'
maintain city move textvar last

Storing dimension values in sorted order

You can store the values of a dimension in sorted order by taking the following actions:

1. Limit the dimension to all of its values.

   LIMIT dimension TO ALL
   

2. Sort the dimension values based on your desired sorting criterion.

   SORT dimension A sort-criterion
   

   Note: To sort the values alphabetically, sort by the dimension itself.

3. Store the dimension values in their sorted order.

   MAINTAIN dimension MOVE VALUES(dimension) FIRST
   

   Note: To sort the values alphabetically, sort by the dimension itself.

Note: You cannot use the MAINTAIN command to save the sorted order as the permanent order of a time dimension. The values of a time dimension must be stored in increasing chronological order.

For more information on using the SORT command, see the entry for the command in the OLAP DML Reference.

Example: Storing dimension values in alphabetical order

Suppose that the default status list for the CITY dimension contains the following values.

show values (city nostatus)
ATLANTA
CONCORD
LINCOLN
COLUMBUS
PEORIA
SEATTLE

The following commands sort the values of CITY in alphabetical order and then store the values in that order.

sort city a city
maintain city move values(city) first

The default status list of CITY reflects the new sorted order.

show values (city nostatus)
ATLANTA
COLUMBUS
CONCORD
LINCOLN
PEORIA
SEATTLE

Maintaining composites and conjoint dimensions

Both composites and conjoint dimensions are lists of dimension-value combinations in which one value is taken from each of the dimensions on which the composite or conjoint dimension is based. Composites and conjoint dimensions differ in the way that they are maintained.

Maintaining composites

Composites are internal structures that are automatically maintained. Consequently, the simplest way to maintain a composite is to merely maintain its base dimensions and let the values in the composite be maintained automatically.

In most cases, it is not necessary to do anything to maintain composites. However, if you want to have a very fine degree of control, you may have to explicitly maintain the composite. In this case, you can use the MAINTAIN command to add, delete, and merge values.

Maintaining conjoint dimensions

Conjoint dimensions, unlike composites, are actual dimensions that you must explicitly maintain. Conjoint dimensions are not automatically maintained. In programs, you use the MAINTAIN command to maintain the values in a conjoint dimension.