|The OLAP Procedure|
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;
indicates whether you are creating a calculated member or a named set.
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.
specifies the MDX expression.