Essbase® Analytic Services Database Administrator's Guide | | Update Contents | Previous | Next | Print | ? | |
Information Map | |
This chapter explains how to develop custom-defined macros and how to use them in calculation scripts and formulas. Custom-defined macros are written with calculator functions and special macro functions. Macros enable you to combine multiple calculation functions into a single function.
For more details about the macro language syntax and rules, and examples of the use of the macro language, see the Technical Reference.
This chapter includes the following sections:
Note: The information in this chapter is not relevant to aggregate storage databases.
Custom-defined macros use an internal macro language that enables you to combine calculation functions and operate on multiple input parameters.
When developing and testing custom-defined macros, make sure to create and test new macros locally within a test application. You should register custom-defined macros globally only after you have tested them and are ready to use them as part of a production environment.
Analytic Services requires that you have a security level of database designer or higher to create and manage custom-defined macros.
View a custom-defined macro to determine whether a macro has been successfully created or whether a custom-defined macro is local or global.
To view a custom-defined macro, use either of the following methods:
Tool |
Topic |
Location |
---|---|---|
When you create a custom-defined macro, Analytic Services records the macro definition and stores it for future use. Create the macro once, and then you can use it in formulas and calculation scripts until the macro is updated or removed from the catalog of macros.
Use these sections to understand more about creating custom-defined macros and to find instructions for creating them:
You can create custom-defined macros locally or globally. When you create a local custom-defined macro, the macro is only available in the application in which it was created. When you create a global custom-defined macro, the macro is available to all applications on the server where it was created.
When you create a global custom-defined macro, all Analytic Services applications can use it. Be sure you test custom-defined macros in a single application (and create them only in that application) before making them global macros.
Caution: When testing macros, do not create the macros as global (using a macro name without the AppName. prefix), because global macros are difficult to update. For information about the methods for updating custom-defined macros, see Updating Custom-Defined Macros.
For rules about naming custom-defined macros, see Naming Custom-Defined Macros.
Remember these requirements when you create macro names:
To create a custom-defined macro, use either of the following methods:
Tool |
Topic |
Location |
---|---|---|
Be sure to add the application name plus a period (.) as a prefix before the name of the local macro. In this example, Sample is the prefix for the local macro name. This prefix assigns the macro to an application, so the macro is only available within that application.
For example, use the following MaxL statement to create a local macro named @COUNTRANGE used only in the Sample application:
create macro Sample.'@COUNTRANGE'(Any) AS '@COUNT(SKIPMISSING, @RANGE(@@S))' spec '@COUNTRANGE(MemberRange)' comment 'counts all non-missing values';
For example, use the following MaxL statement to create a global macro named @COUNTRANGE:
create macro'@COUNTRANGE'(Any) AS '@COUNT(SKIPMISSING, @RANGE(@@S))' spec '@COUNTRANGE(MemberRange)' comment 'counts all non-missing values';
To refresh the catalog of custom-defined macros for all applications on a server, restart the server.
To refresh the catalog of custom-defined macros for a single application, use the refresh custom definitions MaxL statement.
For example, use the following MaxL statement to refresh the catalog of custom-defined macros for the Sample application:
refresh custom definition on application sample;
After creating custom-defined macros, you can use them like native calculation commands. Local macros-created using the AppName. prefix on the macro name-are only available for use in calculation scripts or formulas within the application in which they were created. Global macros-created without the AppName. prefix-are available to all calculation scripts and formulas on the server where they were created.
For a comprehensive discussion of creating custom-defined macros, see Creating Custom-Defined Macros.
To use a custom-defined macro follow these steps:
CountMbr = @COUNTRANGE(Sales, Jan:Dec);
Use this calculation script with the Sample Basic database, or replace "Sales, Jan:Dec" with a range of members in a test database.
For information about creating and running calculation scripts, see Developing Calculation Scripts. For information about creating and running formulas, see Developing Formulas.
When you update a custom-defined macro, you must determine whether the macro is registered locally or globally. Local custom-defined macros are created using an AppName. prefix in the macro name and can be used only within the application where they were created. For a review of the methods used to determine whether a custom-defined macro is local or global, see Viewing Custom-Defined Macros. For a review of the methods used to update the catalog of macros after you change a custom-defined macro, see Refreshing the Catalog of Custom-Defined Macros.
To update a custom-defined macro, use either of the following methods:
Tool |
Topic |
Location |
---|---|---|
For example, use the following MaxL statement to change the local macro @COUNTRANGE which is used only in the Sample application:
create or replace macro Sample.'@COUNTRANGE'(Any) as '@COUNT(SKIPMISSING, @RANGE(@@S))';
For example, use the following MaxL statement to change the global macro @COUNTRANGE:
create or replace macro '@COUNTRANGE'(Any) as '@COUNT(SKIPMISSING, @RANGE(@@S))';
You can copy custom-defined macros to any Analytic Server and application to which you have appropriate access.
To copy a custom-defined macro, use either of the following methods:
Tool |
Topic |
Location |
---|---|---|
When removing a custom-defined macro, you must first determine whether the macro is registered locally or globally. The procedure for removing global custom-defined macros is more complex than that for removing local custom-defined macros and should only be performed by a database administrator. For a review of methods used to determine whether a custom-defined macro is local or global, see Viewing Custom-Defined Macros.
Before removing custom-defined macros, verify that no calculation scripts or formulas are using them. Global custom-defined macros can be used in calculation scripts and formulas across a server, so verify that no calculation scripts or formulas on the server are using a global custom-defined macro before removing it.
For a review of methods used to update the catalog of macros after you remove a custom-defined macro, see Refreshing the Catalog of Custom-Defined Macros.
To remove a custom-defined macro, use either of the following methods:
Tool |
Topic |
Location |
---|---|---|
For example, use the following MaxL statement to remove the local macro @COUNTRANGE which is used only in the Sample application:
drop macro Sample.'@COUNTRANGE';
For example, use the following MaxL statement to remove the global macro @COUNTRANGE:
drop macro '@COUNTRANGE';
![]() |