The OLAP Procedure

DEFINE Statement

The DEFINE statement defines a global calculated member or a named set for any cube that is registered in a SAS Metadata Repository.

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

A calculated member is a dimension member that has been calculated from the member values in the input table. Only the definition 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. 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 definition exists in the metadata repository.

The DEFINE statement can be used alone as shown in this 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=scending
   ;
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;

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.

Top of Page