OLAP Setup and User's Guide

Using Other Views in SQL Applications

This section provides information about other views that you might find useful when writing SQL applications to query DB2 OLAP Server multidimensional data. The views include relational attributes, user-defined attributes, alias names, and linked reporting objects (LROs).

Using Relational Attribute Views

When you add a relational attribute column to a dimension table, DB2 OLAP Server records its name, data type and size in a relational attribute table for that dimension. You can access the table through a relational attribute view.

The relational attribute view name is obtained from the RATViewName column of the cube view.

Table 19 shows details about columns in the relational attribute view.

Table 19. Contents of Relational Attribute View

Name	Type	Max Size	Contents
RATCOLUMNNAME	VarChar	20	The name of the relational attribute column, possibly surrounded by single quotations.
RATCOLUMNTYPE	Integer		A number indicating the data type of the relational attribute columns: 1 = Character (CHAR) 4 = Integer (INT) 5 = Small Integer (SMALLINT) 12 = Variable character (VARCHAR)
RATCOLUMNSIZE	Integer		If the RATCOLUMNTYPE is 4 or 5, RATCOLUMNSIZE is 0. If RATCOLUMNTYPE is 1 or 12, RATCOLUMNSIZE is the size specified for the column.

To access data in a relational attribute view, your application must first determine the name of the view from the cube view.

For example, to find the name of the relational attribute view for the Product dimension in the Basic database, you use the following SQL statement:

SELECT RATVIEWNAME FROM OLAPSERV.SAMPBASI_CUBEVIEW WHERE DIMENSIONNAME='PRODUCT'

This query might return: OLAPSERV.SAMPBASIR_PRODUCT

You can use information from the relational attribute view in SQL statements. By selecting the rows from the relational attribute view, you can get a list of relational attribute columns, their types, and sizes for the corresponding dimension.

In the following example, an SQL SELECT statement retrieves the relational attribute information for the PRODUCT dimension in the BASIC database in the SAMPLE application.

SELECT RATCOLUMNNAME,RATCOLUMNTYPE,RATCOLUMNSIZE FROM SAMPBASIR_PRODUCT.

The result of this query might be:

RATCOLUMNNAME  RATCOLUMNTYPE  RATCOLUMNSIZE
=============  =============  =============
COLOR          1              10

In this result, there is a relational attribute column on the Product dimension called Color. The column type of 1 indicates that it is a character column, and size of 10 indicates it can hold a maximum of 10 characters per row.

Using User-defined Attribute Views

DB2 OLAP Server maintains one user-defined attribute view for each dimension of a cube. There is one row for each member/attribute combination. Use this view to get information on a member of a dimension.

The name of the user-defined attribute view is obtained from the UDAViewName column of the cube view.

Table 20 shows details about columns in the user-defined attribute view.

Table 20. Contents of User-defined Attribute View

Name	Type	Max Size	Contents
MemberName	VarChar	80	The name of the member.
UDA	VarChar	80	The user-defined attribute text string.

To access data in a UDA view, your application must first determine the name of the UDA from the cube view.

For example, to find the name of the UDA view for the Product dimension in the Basic database, you use the following SQL statement:

SELECT UDAVIEWNAME FROM OLAPSERV.SAMPBASI_CUBEVIEW WHERE DIMENSIONNAME='PRODUCT'

This query might return: OLAPSERV.SAMPBASIU_PRODUCT

To list the member names of all products that have an associated user-defined attribute of Promotion for the Basic database in the Sample application:

SELECT MEMBERNAME FROM OLAPSERV.SAMPBASIU_PRODUCT WHERE UDA = 'Promotion'

Using Alias ID Views

DB2 OLAP Server maintains one alias ID view for each relational cube. It contains one row for each multidimensional alias table used within an outline. Use this view to determine what aliases are available for a cube.

The alias ID view name is obtained from the cube catalog view.

Table 21 shows details about columns in the alias ID view.

Table 21. Contents of Alias ID View

Name	Type	Max Size	Contents
AliasTableName	VarChar	80	The name of the multidimensional alias table. This is a collective name for a set of aliases associated with members of a cube.
RelAliasTableName	VarChar	18	The DB2 OLAP Server name for this alias table. This name is used for the alias columns of the dimension view.

To access data in an alias ID view, your application must first determine the name of the alias ID view from the cube catalog view.

For example, to find the name of the UDA view for the Basic database in the Sample application, you use the following SQL statement:

SELECT ALIASIDVIEWNAME FROM OLAPSERV.CUBECATALOGIEW
  WHERE APPNAME='Sample' AND CUBENAME='Basic'

The result of this query might be:

OLAPSERV.SAMPBASI_ALIASID

To list the alias tables for a cube:

SELECT ALIASTABLENAME FROM OLAPSERV.SAMPBASI_ALIASID
 

To determine which dimension view column to use when constructing constraints using aliases from the French Names alias table:

SELECT RELALIASTABLENAME FROM OLAPSERV.SAMPBASI_ALIASID
  WHERE ALIASTABLENAME='French Names'
 

To list the member names and their French aliases for the members of a dimension with RelAliasTableName of FrenchNames:

SELECT MEMBERNAME,FRENCHNAMES FROM OLAPSERV.SAMPBASID_PRODUCT

Using Linked Reporting Object (LRO) Views

DB2 OLAP Server maintains one LRO view for each cube. Use this view to determine which linked reporting objects are associated with individual cells in a cube. There is one row for each linked object or cell annotation.

The name of the LRO view is obtained from the cube catalog view.

LRO View Contents

Table 22 shows details about columns in the LRO view. The view has one additional column for each dimension, and columns containing information about the associated object.

Table 22. Contents of Cube View

Name	Type	Max Size	Contents
Dimension columns. Short name for the dimension, taken from the RelDimensionName column of the cube table	VarChar		The name of the member of this dimension that the object is associated with.
STOREOPTION	Small Integer		The value for this column is: 0 if the associated object is stored on the client 16 if the associated object is stored on the server
OBJTYPE	Small Integer		The value for this column is: 0 if the associated object is an annotation 1 if the associated object is application data
Handle	Integer		Unique identifier for each annotation or object. When more than one object is associated with a cell, use the handle to uniquely identify one of the objects.
USERNAME	VarChar	31	The name of the user that created this object.
UPDATEDATE	Integer		The UTC timestamp for when the object was last updated.
OBJNAME	VarChar	512	If the object type is 1 (application data), this column contains the file name of the object.
OBJDESC	VarChar	80	If the object type is 1, this column contains a description of the object.
NOTE	VarChar	600	If the object type is 0 (annotation) this column contains the text of the annotation.

Querying the LRO View Using SQL Statements

To access data in the LRO view, your application must first determine the name of the LRO view from the cube catalog view.

For example, to find the name of the LRO view for the Basic database in the Sample application, you use the following SQL statement:

SELECT LROVIEWNAME FROM OLAPSERV.CUBECATALOGVIEW 
  WHERE APPNAME='Sample' AND CUBENAME='Basic'

The result of this query might be:

OLAPSERV.SAMPBASI_LROVIEW

To list the descriptions of all application objects associated with a cube:

SELECT OBJDESC, USERNAME FROM OLAPSERV.SAMPBASI_LROVIEW WHERE OBJTYPE=0

To list all annotations made by Gary Robinson:

SELECT NOTE FROM OLAPSERV.SAMPBASI_LROVIEW WHERE OBJTYPE=1 AND USERNAME='Gary Robinson'

You can query the linked reporting objects associated with a cell by specifying the member IDs of the cell for each dimension in the WHERE clause of the SELECT statement.