The OLAP Procedure

DEFINE Statement

Defines a global calculated member or a named set for any cube that is registered in a SAS Metadata Repository.

Syntax

DEFINE MEMBER | SET 'member-or-set-name' AS 'mdx-expression' ;

Details

Overview

The DEFINE statement is used to create a global calculated member or a named set. Only the registration of the member is stored; the value is calculated when a query is submitted. A named set is an alias for a specified MDX expression that returns a member or set. Named sets are often used to make complex MDX queries easier to read and maintain.

The defined calculated members and named sets are available to any session that creates a query in the context of the SAS OLAP Server and the schema defined in the METASRV statement of the PROC OLAP code that is used to create the global member or set.

DEFINE statements can apply to more than one cube, so the CUBE= option is not required to use this statement. The METASVR statement verifies that the cube registration exists in the metadata repository.

Required Arguments

MEMBER | SET: indicates whether you are creating a calculated member or a named set.

'member-or-set-name': specifies the name of the member or set that you are creating. If you are creating a calculated member, then this value specifies a name for the member that will be calculated by the MDX expression. If you are creating a named set, then this value is the alias for the specified MDX expression.

AS 'mdx-expression': specifies the MDX expression.

Options

MLSID=n

is a positive integer identifier between 0 and MACINT (2147483647) that identifies the observation in the data set that contains the translated caption and description for the calculated member or named set. This identifier is expected in the MLSID column of the data set specified by DIMTABLECAPPREF=, DIMTABLELIBREF=, and the USER_DEFINED_TRANSLATIONS statement.

Note: For further information on MLS caption tables and MLSID, see USER_DEFINED_TRANSLATIONS Statement.

Note: The MLSID=n option is part of the syntax of the MDX Expression, as shown in the following example.

define Member "[&testname].[date].[all date].[June]" as '6, mlsid=11'

Example

The DEFINE statement can be used alone as shown in the following example, which defines two calculated members and one named set. The METASVR is the only other required statement. To define multiple sets or calculated members, separate option values with a comma.

proc olap;
metasvr olap_schema='Services Schema'
   repository='services'
   host='misdept.us.mar.com'
   port=9999
   userid=jjones
   pw='my password'
   ;
 define member '[mddbcars].[Measures].[avg]' as
             '[Measures].[sales_sum]/[Measures].[sales_n]',
      member '[sales].[Measures].[stat1]' as
             '[Measures].[qty] +1',
      set '[campaign].[myset]' as
          '[campaign_dates].[All campaign_dates].children'
   ;
run;

The DEFINE statement can also be used with a PROC OLAP program that creates a cube or with a program that adds aggregations to or deletes aggregations from an existing cube. Cube builds, additions, and deletions occur before the DEFINE statement is processed, so the DEFINE statement is not processed if those statements fail.

proc olap data=olapsio.cars
   cube=mddbcars
   path='d:\services\'
   ;
metasvr olap_schema='Services Schema'
   repository='cars'
   host='misdept.us.mar.com'
   port=9999
   userid=jjones
   pw='my password'
   ;
dimension date
   hierarchies=(date)
   sort_order=ascending
   ;
hierarchy date
   LEVELS=(dte)
   ;
level dte
   ;
dimension cars
   hierarchies=(cars
   sort_order=ascending)
   ;
hierarchy cars
   levels=(car color)
   ;
dimension dealers
   hierarchies=(dealers)
   sort_order=ascending
   ;
hierarchy dealers
   levels=(dealer dest)
   ;
measure sales_sum
   column=sales
   stat=sum
   format=dollar15.2
   ;
measure sales_n
   column=sales
   stat=n
   format=12.0
   ;
define member '[mddbcars].[Measures].[avg]' as
   '[Measures].[sales_sum] / [Measures].[sales_n]'
   ;
run;