OLAP Setup and User's Guide

Using Relational Attributes

You use relational attributes to add attribute columns to the dimension tables, and to add attribute values to those columns. After creating and populating the columns, you can use the contents of the columns to run SQL SELECT statements against the dimension tables. Because the dimension tables can be joined to the fact table, the SELECT statements can retrieve values from the fact table based on attribute values that you define, rather than just on the member name. For information about using SQL to access relational attributes, see Using Relational Attribute Views.

Adding Relational Attribute Columns to Dimension Tables

You can define relational attribute columns by specifying a special user-defined attribute for the top level member of the dimension. This user-defined attribute has the following format:

RELCOL columnname datatype size

The parameters are:

RELCOL

A keyword

columnname

The name of the column that is added to the dimension view. This name must conform to the naming conventions for the relational database being used. If special characters are required in the name, enclose it with single quotation marks; also, do not use single quotation marks as part of the column name. An index is automatically created for the new relational attribute column.

datatype

The data type of the column. It can be any of the following types:

• CHARACTER
• CHAR
• VARCHAR
• INTEGER
• INT
• SMALLINT

sizevalue

The maximum number of characters allowed in the column, if the data type is CHARACTER, CHAR or VARCHAR. Do not specify a size value for INTEGER, INT, or SMALLINT.

The following examples show how to add relational attribute columns to dimension tables:

• To add a 10-character column called "Color" to the dimension table of the "Product" dimension, enter:

  RELCOL Color CHAR(10)  
  

  The following is displayed for "Product" in the outline editor in the application manager:

  Product (UDAs: RELCOL Color CHAR(10) ) 
  

• To add an integer column called "Size" to the dimension table for the "Region" dimension, enter:

  RELCOL Size INTEGER
  

  The following is displayed for "Region" in the outline editor in the application manager:

  Region (UDAs: RELCOL Size INTEGER)
  

• To add a 25-character VARCHAR column called "Audit status" to the dimension table for the "Measures" dimension, enter:

  RELCOL 'Audit status' VARCHAR(25)
  

  The following is displayed for "Measures" in the outline editor in the application manager:

  Measures (UDAs: RELCOL 'Audit status' VARCHAR(25))
  

  In this example, the column name is surrounded by single quotation marks because the name includes a blank space.

Relational columns cannot be removed from relational tables; after you add one, it remains part of the dimension table as long as the table exists.

You can remove a relational attribute column from a dimension view by deleting the appropriate RELCOL user defined attribute, which also deletes the index of the relational attribute column.

Adding Values to Relational Attribute Columns

To add a value to a relational attribute column, create a user-defined attribute for the member that has the following format:

RELVAL columnname datavalue

The parameters are:

RELVAL

A keyword

columnname

The name of the column to which you are adding a value. This name must be the same name that you specified in the RELCOL user-defined attribute.

datavalue

Data that is appropriate to the data type specified for the relational attribute column. For example, it must be character data enclosed in single quotation marks for CHARACTER, CHAR or VARCHAR, and a number for INTEGER, INT, or SMALLINT.

The following examples correspond to the examples in Adding Relational Attribute Columns to Dimension Tables:

• To add "Blue" to the "Color" column of the "Product" dimension for the row corresponding to the "Kitchen Sink" member, enter:

  RELVAL Color 'Blue' 
  

  The following is displayed for "Kitchen Sink" in the outline editor in the application manager:

  Kitchen Sink (UDAs: RELVAL Color 'Blue') 
  

• To add the "Size" value 42 to the "Region" dimension table for the "California" member, enter:

  RELVAL Size 42 
  

  The following is displayed for "California" in the outline editor in the application manager:

  California (UDAs: RELVAL Size 42) 
  

• To add the value "Checked" to the "Audit Status" column of the "Measures" dimension table for the "Sales" member, enter:

  RELCOL  'Audit status'  'checked'
  

  Then the following is displayed for Sales in the outline editor in the application manager:

  Sales (UDAs: RELCOL  'Audit status'  'checked' )
  

You can automate the process of creating user-defined attributes by building data load rules. This is useful if you want to assign values to many members in a dimension. When you use the data load rules editor, you can add text before (and after) a column of data values from the external data table. If you prepare a data table that contains all the relational attribute values, the data load rules editor can generate the 'RELVAL columnname' portion of the user-defined attribute. The data load rules editor then restructures the outline and adds the relational attributes to the dimension.

To remove relational attribute values from their corresponding dimension, delete the user-defined attribute that defined the value. This replaces the value with a null in the relational attribute column.