MaxL Key Phrases

The key phrases listed below are possible constructions using the MaxL grammar. The phrases are parts of MaxL statements you construct to perform administrative tasks. This section explains what the phrases in each statement group mean in relation to Essbase database administration. For syntax information on constructing statements, see MaxL Grammar.

alter application
alter database
alter group
alter system
alter user
create application
create calculation
create database
create filter
create function
 create group
create location alias
create macro
create partition
create user
display application
display calculation
display database
display disk volume
display filter
 display filter row
display function
display group
display location alias
display macro
display partition
display privilege
display session
display system
display user
 display variable
drop application
drop calculation
drop database
drop filter
drop function
drop group
drop location alias
drop macro
drop partition
 drop user
execute calculation
export data
export lro
grant
import data
import dimensions
import lro
refresh custom definitions
refresh outline
refresh replicated partition

ALTER APPLICATION

You can change the following application-wide settings using alter application.

Key Phrase Explanation
set lock_timeout Change the maximum time interval that locks on data blocks can be held by Spreadsheet Add-in users. When a client data-block lock is held for more than the time out interval, Essbase removes the lock and the transaction is rolled back. The default interval is 60 minutes. This setting affects all databases in the application.
set max_lro_file_size Specify a maximum file size for Linked Reporting Objects (LRO) attachments. There is no default. There is no minimum or maximum value, excepting limitations imposed by your system resources.
set minimum permission Grant all users a minimum level of permission to all databases in the application. Users with higher permissions than this minimum are not affected.
set variable Assign a string value to an existing substitution-variable name. If the variable does not exist, first create it using add variable. Substitution variables may be referenced by calculations in the application.
load database Start (by loading into memory) an idle database. The statement will fail if you do not have at least read privilege for the database.
unload database Stop (by unloading from memory) an active database. The statement will fail if you do not have at least read privilege for the database.
enable startup Permit all users to load (start) the application. This only applies to users who have at least read privilege for the application. Startup is enabled by default.
disable startup Prevent all users from loading (starting) the application. Startup is enabled by default.
enable autostartup Start the application automatically when Essbase starts. By default, autostartup is disabled.
disable autostatup Do not start the application automatically when Essbase starts. By default, autostartup is disabled.
enable commands Allow all users with sufficient permissions to make requests to databases in the application. Use to reverse the effect of disable commands. The disable commands setting remains in effect only for the duration of your session. By default, commands are enabled.
disable commands Prevent all requests to databases in the application, including non-data-specific requests, such as viewing database information or changing database settings. All users are affected, including other supervisors. Supervisors are affected by this setting as a safety mechanism to prevent accidental updates to databases during maintenance operations. This setting remains in effect only for the duration of your session. The setting takes effect immediately, and affects users who are currently logged in, as well as users who log in later during your session.
Caution: If performing maintenance operations that require disabling commands, you must make those maintenance operations within the same session and the same script as the one in which commands were disabled.
By default, commands are enabled.
enable updates Allow all users with sufficient permissions to make requests to databases in the application. Use to reverse the effect of disable updates. Disabling updates remains in effect only for the duration of your session. By default, updates are enabled.
disable updates Prevent all users from making requests to databases in the application. Use before performing update and maintenance operations. The disable updates setting remains in effect only for the duration of your session.
Caution: If performing maintenance operations that require updates to be disabled, you must make those maintenance operations within the same session and the same script as the one in which updates were disabled. By default, updates are enabled.
enable connects Allow all users with sufficient permissions to make connections to databases in the application. Use to reverse the effect of disable connects. By default, connections are enabled.
disable connects Prevent any user with a permission lower than Application designer from making connections to the databases that require the databases to be started. This includes starting the databases or performing the ESSCMD SELECT command on the databases. Database connections remain disabled for all databases in the application, until the application setting is re-enabled by the administrator.
By default, connections are enabled.
enable security When security is disabled, Hyperion Essbase ignores all security settings in the application and treats all users as Application Designers. By default, security is enabled.
disable security When security is disabled, Hyperion Essbase ignores all security settings in the application and treats all users as Application Designers. By default, security is enabled.
comment Enter an application description (optional). The description can contain up to 80 characters.
clear logfile Delete the application log located in the application directory. A new log is created for entries recording subsequent application activity.
add variable Create an application-level substitution variable by name, and optionally assign a string value for the variable to represent. You can assign or change the value later using set variable. A substitution variable acts as a global placeholder for information that changes regularly. Substitution variables may be referenced by calculations and report scripts.
drop variable Remove a substitution variable and its corresponding value from the application.
rename to Rename the application. When you rename an application, the application and the application directory (ARBORPATH\App\application_name) are renamed.

ALTER DATABASE

You can change the following database-wide settings using alter database.

Key Phrase Explanation
enable
two_pass_calc		 Recalculate (after a default calculation) database outline members tagged as Two Pass, so they will be recalculated after other database members have been consolidated. This setting is enabled by default.
Members that usually require a two-pass calculation are those members of the Accounts dimension that are calculated by a formula rather than by hierarchical consolidation. These members are typically ratios, such as "Profit % Sales" (profit percentage of sales), which has a member formula.
This setting is ignored during a calculation script; it is used only during a default calculation. To use two-pass calculation in a non-default calculation, use the CALC TWOPASS command in the calculation script.
disable
two_pass_calc		 Do not recalculate database outline members tagged as Two Pass after a default calculation. Two-pass calculation is enabled by default.
enable
aggregate_missing		 Consolidate #MISSING values along with the regular database consolidation. If you never load data at parent levels, aggregating #MISSING values can improve calculation performance, depending on the ratio between upper level blocks and input blocks in the database.
If this setting is enabled and you load values directly at the parent level, these parent-level values will be replaced by the results of the consolidation, even if the results are #MISSING values. The aggregate missing setting is disabled by default.
disable
aggregate_missing		 Do not not consolidate #MISSING values. This is the default. Data that is loaded at parent levels is not overwritten by #MISSING values of children below it. However, if any of the child data values are not #MISSING, these values are consolidated and overwrite the parent values.
enable startup Enable users to start the database directly or as a result of requests requiring the database to be started. Startup is enabled by default.
disable startup Prevent all users from starting the database directly or as a result of requests that would start the database. Startup is enabled by default.
enable autostartup Automatically start the database when the application to which it belongs starts. Autostartup is enabled by default. This setting is applicable only when startup is enabled.
disable autostartup Prevent automatic starting of the database when the application to which it belongs starts. Autostartup is enabled by default.
enable compression Enable data compression. By default, Bitmap compression is enabled. To switch to a different compression type, use alter database.
disable compression Disable data compression. By default, Bitmap compression is enabled.
enable
create_blocks

Allow Essbase to create a data block when you assign a non-constant value to a member combination for which a data block does not already exist. Block creation on equation is disabled by default, because it can result in a very large database.

When you assign a constant to a member on a sparse dimension, you do not need to enable Create Blocks on Equation, because Essbase would create a data block anyway. For example, "West = 5;" would result in the creation of data blocks, with or without the Create Blocks on Equation setting enabled.

You do need to check this option if you want blocks created when you assign anything other than a constant to a member on a sparse dimension for which a data block does not already exist. For example, if no data exists for Actuals, a member of a sparse Scenario dimension, then you need to enable Create Blocks on Equation in order to perform the following allocation:
2002Forecast = Actuals * 1.05;.

disable
create_blocks		 Turn off the Create Blocks on Equation setting. The setting is disabled by default.
enable
committed_mode		 Set the database isolation level to committed access, meaning that only one transaction at a time can update data blocks. Essbase holds read/write locks on all data blocks until the transaction and the commit operations are performed. If pre-image access is enabled, users (or transactions) can still have read-only access to data at its last commit point. For more information, see the enable pre_image_access setting. The default isolation-level mode is Uncommitted.
disable
committed_mode

Turn off the Committed Mode setting, reverting to the default isolation level of Uncommitted for the database. Note: Spreadsheet Add-in lock and send operations are always in committed mode.

In uncommitted mode, Essbase allows transactions to hold read/write locks on a block-by-block basis. Essbase releases a block after it is updated, but does not commit blocks until the transaction is completed, or until a specified number of blocks or rows (a "synchronization point") has been reached. You can set this limit using the implicit_commit settings.

enable
pre_image_access		 Allow users (or other transactions) read-only access to data at its last commit point, when the database is in committed mode (meaning that data blocks may be locked for the duration of a concurrent transaction). Pre-image access is enabled by default when the database is in committed mode.
See also the enable committed_mode setting.
disable
pre_image_access		 Disable pre-image access, disallowing read-only access to locked blocks of data at their last commit point (this setting is only applicable while the database is in committed mode). Pre-image access is enabled by default when the database is in committed mode.
enable
cache_pinning

Enable cache memory locking, which locks the memory used for the index cache, data file cache, and data cache into physical memory, giving the Essbase kernel priority use of system RAM. Cache memory locking improves performance for an Essbase database because the system memory manager does not need to swap the memory used by the caches when swapping the memory used by the Essbase OLAP Server. The setting takes effect after you restart the database.

By default, cache memory locking is disabled. To use cache memory locking, you must be using direct I/O (buffered I/O is the default for Release 6.2). For more information, see the Technical Reference documentation for the DIRECTIO setting for essbase.cfg.
disable
cache_pinning		 Disable cache memory locking, reverting to the default.
begin archive
to file

Prepare the database for backup by an archiving program, and prevent writing to the files during backup. This statement requires the database to be started.

Begin-archive achieves the following outcomes:

  • Commits any modified data to disk.
  • Switches the database to read-only mode. The read-only state persists, even after the current session ends, until it is changed back to read-write using end archive.
  • Reopens the database files in shared, read-only mode.
  • Creates a file containing a list of files that need to be backed up. Unless a different path is specified, the file is stored in the database directory.

Begin-archive and end-archive do not perform the backup; they simply protect the database during the backup process.
end archive

Return the database to read-write mode after backing up the database files.
This statement requires the database to be started.

End-archive achieves the following outcomes:

  • Returns the database to read-write mode.
  • Re-opens database files in exclusive, read-write mode.
set note Create an informational note about the database that Spreadsheet Add-in users can see from the Essbase System Login dialog box. For example, 'Calc in progress: do not update.' Database notes can be up to 64 kilobytes long.
set
retrieve_buffer_size		 Change the database retrieval buffer size. This buffer holds extracted row data cells before they are evaluated by the RESTRICT or TOP/BOTTOM Report Writer commands. The default size is 10 KB. The minimum size is 2 KB. Increasing the size may improve the retrieval performance of Essbase.
set retrieve_sort
_buffer_size		 Change the database retrieval sort buffer size. This buffer holds data until it is sorted. The Report Writer and Essbase Query Designer use the retrieval sort buffer. The default size is 10 KB. The minimum size is 2 KB. Increasing the size may improve the retrieval performance of Essbase.
set
data_cache_size		 Change the data cache size. The data cache is a buffer in memory that holds uncompressed data blocks. Essbase allocates memory to the data cache during data load, calculation, and retrieval operations as needed. The default and minimum size is 3072 KB.
set data_file
_cache_size		 Change the data file cache size. The data file cache is a buffer in memory that holds compressed data files (.PAG files). Essbase allocates memory to the data file cache during data load, calculation, and retrieval operations as needed. The data file cache is not used when buffered I/O is used; you must use direct i/o to use the data file cache. The default size is 32 MB.
set index
_cache_size		 Change the index cache size. The index cache is a buffer in memory that holds index pages. When a data block is requested, Essbase looks at the index pages in the index cache to find its location on disk. The default size is 1 MB when buffered I/O is used, and 10 MB when direct I/O is used. Buffered I/O is the default for this release.
set index
_page_size		 Change the size of index pages. Index pages hold index entries; an index entry exists for every combination of sparse dimensions in the database. A change to the index page size takes effect the next time you start the database, provided that the database is empty. The database must be empty for the change to take effect. The minimum size (and the default when using buffered I/O) is 1 KB. This setting is ignored when direct I/O is used, and Essbase always uses a default index page size of 8 KB.
set currency
_database		 Link the database with a currency database. A currency database enables you to convert currency values in a database from one currency into another currency.
set currency
_member		 Specify the member to use as a default value in currency conversions. You can specify any valid member of the dimension defined as "Currency Type" in the currency database.
set currency
_conversion		 Specify whether during currency conversion, the calculation method muliplies the currency database exchange rates with the main database values, or that the currency database exchange rates are divided by the main database values.
set minimum
permission		 Set a level of permission that all users or groups can have to the database. Users or groups with higher granted permissions than the minimum permission are not affected.
set compression
rle		 Set the database to use run-length encoding (RLE) compression. Essbase compresses repetitive, consecutive values, including zeros and #MISSING values. The default compression type is bitmap.
When a compressed data block is brought into the data cache, Essbase expands the block to its full size, regardless of the scheme that was used to compress it.
set compression
bitmap		 Set the database to use bitmap compression, the default. Essbase stores only non-missing values and uses a bitmapping scheme.
When a compressed data block is brought into the data cache, Essbase expands the block to its full size, regardless of the scheme that was used to compress it.
set compression
zlib		 Set the database to use ZLIB compression.
When a compressed data block is brought into the data cache, Essbase expands the block to its full size, regardless of the scheme that was used to compress it.
set lock_timeout Change the interval to wait for blocks to be unlocked when the database is in committed mode. If a transaction request is made that cannot be granted in the allotted time, the transaction is rolled back until a lock can be granted. Note: Spreadsheet Add-in lock and send operations are always in committed mode.
set implicit_commit
after <number> blocks		 When uncommitted access is enabled, set the frequency at which Essbase commits data blocks (after the specified number of blocks has been reached).
set implicit_commit
after <number> rows		 When uncommitted access is enabled, set the frequency at which Essbase commits data blocks (after the specified number of rows has been reached).
set io_access_mode

Change the input/output setting you wish to use for the database. The change takes effect the next time the database is started.

Buffered I/O uses the file system's buffer cache, and is the default.

Direct I/O bypasses the file system's buffer cache, and is able to perform asynchronous, overlapped I/Os, providing faster response time and more potential to optimize cache sizes for Essbase databases.

If you set a database to use direct I/O, Essbase will attempt to use direct I/O each time the database is started. If direct I/O is not available on your platform at the time the database is started, Essbase will use buffered I/O, which is the default.

For important information about how I/O settings affect database migration and cache sizes, please see the Hyperion Essbase Installation Guide.
set variable Change the value of an existing subsitution variable on the database. The value must not exceed 256 bytes. It may contain any character except a leading ampersand (&).
set default
calculation		 Change the default calculation (which, by default, is CALC ALL;) to the stored calculation script you specify.
reset Clear all data and linked-reporting objects from the database, but preserve the outline.
reset all Clear all data, linked reporting objects, and the outline.
reset data Same as using reset.
validate data
to local logfile...		 Create a local log file with all index combinations for which blocks contain invalid block headers.
repair
invalid_block_headers		 Delete all blocks that have invalid headers.
add disk
volume		 Add a disk volume definition if you want to allocate storage across multiple volumes, or restrict space used on a volume. After adding a disk volume definition, use set disk volume to place restrictions on files stored on the disk volume.
drop disk
volume		 Remove a disk volume definition. If no disk volume is defined, data and index files are stored in the database directory (for example, $ARBORPATH/app/sample/basic).
set disk
volume		 Specify what types of files should be stored on the disk volume. You can allocate storage for index files, data files, or both. You can specify the maximum file size and partition size allowed on the disk volume.
add variable Create a database-level substitution variable by name, and optionally assign a string value for the variable to represent. You can assign or change the value later using set variable. A substitution variable acts as a global placeholder for information that changes regularly. Substitution variables may be referenced by calculations and report scripts.
drop variable Remove a substitution variable and its corresponding value from the database.
rename to Rename the database. When you rename a database, the database directory is also renamed.
comment Create a description of the database. The maximum number of characters is 80. This description is available to database administrators. To annotate the database for Spreadsheet Add-in users, use set note.

ALTER GROUP

You can change the following settings using alter group. See also alter user.

Key Phrase Explanation
rename to Rename the group.
comment Create a description of the group.

ALTER SYSTEM

You can change the following system-wide settings using alter system.

Key Phrase Explanation
load application Start an application, or start all applications on the OLAP Server.
unload application Stop an application, or stop all applications on the OLAP Server.
set session_idle_limit Set the interval of time permitted for a session to be inactive before Essbase logs off the user. The minimum limit that you can set is five minutes (or 300 seconds). When the session idle limit is set to none, all users can stay logged on until the OLAP Server is shut down. The default limit is 60 minutes.
set session_idle_poll Set the time interval for inactivity checking and security-backup refreshing. The time interval specified in the session idle poll tells Essbase both of the following:
  • How often to check whether user sessions have passed the allowed inactivity interval indicated by session_idle_limit in the alter system statement.
  • How often to refresh the security backup file. If session_idle_poll is set to zero, the security backup file is still refreshed every five minutes.
set invalid_login_limit Set the number of unsuccessful login attempts allowed by any user before the user account becomes disabled. When you change this setting, the counter resets to 0. When the invalid login limit is set to none, there is no limit. By default, there is no limit.
set inactive_user_days Set the number of days a user account may remain inactive before being disabled by the system. The counter resets when the user logs in, is edited, or is activated by a supervisor. When the inactive days limit is set to none, user accounts remain enabled even if they are not used. By default, there is no limit.
set password_reset_days Set the number of days users may retain passwords. After the allotted number of days, users are prompted at login to change their passwords. The counter resets for a user when the user changes the password, is edited, or is activated by a supervisor. When the password reset days limit is set to none, there is no built-in limit for password retention. By default, there is no limit.
set variable Change the value of an existing subsitution variable on the system. The value must not exceed 256 bytes. It may contain any character except a leading ampersand (&).
clear logfile Clear accumulated entries from the OLAP Server log located in the Essbase directory. New log entries are created to record subsequent activity.
delete export_directory Delete directories created for linked-reporting objects exported from a database to a directory created in $ARBORPATH\app. Use this grammar after the exported LROs have already been migrated into a database using import lro, and the directories containing the exported LRO information are no longer needed.

Note: this process only works for directories created in $ARBORPATH\app using the DBS-EXPORT-DIR option of the export lro statement. It does not work for directories created elsewhere using the FULL-EXPORT-DIR option of the export lro statement.

add variable Create a system-level substitution variable by name, and optionally assign a string value for the variable to represent. You can assign or change the value later using set variable. A substitution variable acts as a global placeholder for information that changes regularly. Substitution variables may be referenced by calculations and report scripts.
drop variable Remove a substitution variable and its corresponding value from the system.
logout session all Terminate all user sessions currently running on the Essbase OLAP Server.
logout session...force Terminate a session (or sessions) even if it is currently processing a request. The request is allowed to proceed to a safe point, and then the transaction is rolled back.
logout session <session-id> Terminate a session by its unique session ID number. To find out the session ID number, use display session.
logout session by user Terminate all current sessions by a particular user, either across the entire OLAP Server, or limited to a specific application or database.
logout session by user on application Terminate all current sessions by a particular user across a specific application.
logout session by user on database Terminate all current sessions by a particular user across a specific database.
logout session on application Terminate all current user sessions across a specific application.
logout session on database Terminate all current user sessions across a specific database.
shutdown Shut down the Essbase OLAP Server.
kill request all Terminate all current requests on the Essbase OLAP Server.
kill request <session-id> Terminate the current request indicated by the session ID. You can obtain session IDs using display session.
kill request by user Terminate all current requests by the specified user on the Essbase OLAP Server.
kill request on application Terminate all current requests on the specified application.
kill request on database Terminate all current requests on the specified database.
sync security_backup Check whether the security backup file is the same as the security file, and if not, synchronize the security backup file to the current state of Essbase security. The effect is to refresh the backup file with any additions, changes, or deletions related to applications, databases, users, groups, filters, permissions, subsitution variables, locked objects, or system settings.

If sync security_backup is not issued directly as described above, the security backup file is checked/refreshed automatically at the same frequency with which session inactivity is checked globally. The default inactivity check interval is every five minutes. To change the interval, see the documentation for Server Settings (Application Manager), OLAP Server Properties (Administration Services), or SetGlobalState (API).

ALTER USER

You can change the following user information using alter user.

Key Phrase Explanation
add to group Add the user to a group.
remove from group Remove the user from a group.
rename to Rename the user.
enable Reactivate the user if the user's permission to log in has been terminated.
disable Disable the user's permission to log in to Essbase.
set password Change the user's password.
set password_reset_days Change or apply password expiration rules for this user.
comment Create a description of the user.

CREATE APPLICATION

You can create an application in the following ways using create application.

Key Phrase Explanation
create application Create a new application. Application names are case sensitive.
create or replace application Create an application, or replace an existing application of the same name. Application names are case sensitive.
create application as Create an application as a copy of another application. Application names are case sensitive.
comment Create an application description (optional). The description can contain up to 80 characters.

CREATE CALCULATION

You can create a calculation in the following ways using create calculation.

Key Phrase Explanation
create calculation Create a calculation script, the body of which is specified by CALC-STRING.
create or replace calculation Create a calculation script, the body of which is specified by CALC-STRING. If a calculation script of that name alreay exists, it is replaced.
create calculation as Create a calculation as a copy of another stored calculation.

CREATE DATABASE

You can create a database in the following ways using create database.

Key Phrase Explanation
create database Create a new database. Database names are case sensitive.
create or replace database Create a database, or replace an existing database of the same name. Database names are case sensitive.
create database as Create a database as a copy of another database. Database names are case sensitive.
create currency database Create or replace a database for currency conversion. Linking a currency database to a main database enables you to convert currency values in a database from one currency into another currency.
comment Create a database description (optional). The description can contain up to 80 characters.

CREATE FILTER

You can create a filter in the following ways using create filter.

Key Phrase Explanation
create filter Create a security filter to restrict or permit access to specified database cells.
create or replace filter Create a security filter or replace an existing security filter of the same name.
create filter ... no_access on <member-expression> Create a filter blocking access to a specified member combination.
create filter ... read on <member-expression> Create a filter providing read-only access to a specified member combination.
create filter ... write on <member-expression> Create a filter providing write access to a specified member combination.

CREATE FUNCTION

You can create a custom-defined function in the following ways using create function.

Key Phrase Explanation
create function as Register with Essbase a custom-defined function developed in Java, either as a global function usable by the entire OLAP Server, or as a local function available to an application. To register a global (server-wide) function, use one token for FUNC-NAME. To register a local (application-wide) function, use two tokens for FUNC-NAME.
create or replace function as Register with Essbase a global or local custom-defined function. If a function with that name already exists in the Essbase custom-defined function and macro catalog, it is replaced.
spec Enter, for the custom-defined function, an optional Essbase calculator-syntax specification string, such as in the following example: @COVARIANCE (expList1, expList2). Use a specification string if you wish the function to be returned by the output string of the EssListCalcFunctions API function.
Note: if you do not specify a calculation specification string, you cannot specify a comment either.
with property runtime

Designate the custom-defined function as a runtime function. Normally, Essbase pre-executes functions whose arguments are available at compilation time. The Runtime property prevents that optimization, executing functions that have constant values as operands (or no operands at all) for every block in the function range. If the built-in Essbase @CALCMODE(CELL) function is used, a custom-defined function declared as Runtime can execute on every cell in the range.

Note: No built-in Essbase calculator functions have the Runtime property.

The Runtime property should be applied only in special circumstances, as it can seriously affect performance. The runtime property might be desirable for any custom-defined function whose return value depends on something besides its arguments; for example, the current date, or values in a rapidly changing relational table. If you created a runtime function @RANDOM() that returns a new random number each time it executes, then a member formula such as "Mem1 = @RANDOM();" would return different values for each block. At compilation time, the Runtime property prevents the pre-execution of functions that are applied to constants.
comment Create a description of the function (optional). You cannot create a comment without also using spec to create a calculator-syntax specification string. The optional calculator-syntax specification string and the comment are used as the output string of the EssListCalcFunctions API function.

CREATE GROUP

You can create a group in the following ways using create group.

Key Phrase Explanation
create group Create a security group to assign users to, so that they can share identical minimum permissions assigned at the group level.
create or replace group Create a security group. If a group of that name already exists, it is replaced.
create group as Create a group as a copy of an existing group.
comment Create a description of the security group.

CREATE LOCATION ALIAS

You can create a location alias in the following ways using create location alias.

Key Phrase Explanation
create location alias Create a location alias, identifying a remote host name, database, user name, and password. The location alias can be used by the @XREF function as an abbreviated login to a remote database.
create or replace location alias Create a location alias, replacing any existing location alias of the same name on the same database.
...from <dbs-name> Specify the name of the current database (the database on which the location alias is being created).
...to <dbs-name> Specify the name of the remote database to log in to.
...at <host-name> Specify the remote host name on which the remote database resides.
...as <user-name> identified by <password> Specify a user name and password with which to log in to the remote database.

CREATE MACRO

You can create a custom-defined macro in the following ways using create macro.

Key Phrase Explanation
create macro as Create and register with Essbase a custom-defined macro as your chosen combination of existing calculation functions or macros. Register the macro either as a global macro usable by the entire OLAP Server, or as a local macro available to an application. To register a global (server-wide) macro, use one token for MACRO-NAME. To register a local (application-wide) function, use two tokens for MACRO-NAME.
create macro... <macro-signature> Enter for the macro an optional signature defining the syntax rules for macro arguments. A macro signature describes the style in which arguments (or input parameters) to the macro may be passed. One example of a macro signature is (SINGLE, SINGLE, GROUP), meaning that the macro must be passed two comma-separated arguments followed by a list of arguments. For more information, see Custom-Defined Macro Input Parameters.
create or replace macro Register with Essbase a global or local custom-defined macro. If a macro with that name already exists in the Essbase custom-defined function and macro catalog, it is replaced.
spec Enter for the macro an optional Essbase calculator-syntax specification string, as in the following example: @MYMACRO (mbrName, rangeList). Use a specification string if you wish the macro to be returned by the output string of the EssListCalcFunctions API function.
Note: if you do not specify a calculation specification string, you cannot specify a comment either.
comment Create a description of the macro (optional). You cannot create a comment without also using spec to create a calculator-syntax specification string. The optional calculator-syntax specification string and the comment are used as the output string of the EssListCalcFunctions API function.

CREATE PARTITION

You can create a partition in the following ways using create partition.

Key Phrase Explanation
create transparent partition Create a transparent partition. A transparent partition enables users to access data from the data source as though it were stored in the data target. The data is, however, stored at the data source, which can be in another application, in another database, or on another OLAP Server.
create replicated partition Create a replicated partition. A replicated partition is a copy of a portion of the data source that is stored in the data target.
create linked partition

Create a linked partition. A linked partition connects two different databases with a data cell. The databases can contain largely different dimensions, and still be connected by a small, mapped data region.

With linked partitions, the spreadsheet that a user first views is connected to the target, and the spreadsheet that opens when the user drills across is connected to the source.
create or replace ...partition Create a partition definition, or replace an existing partition definition.
area... Define the partition areas to share with the other database. Optionally nickname the area using an area-alias.
to <dbs-name> Create a partition definition between the current database source and the second database (the target).
from <dbs-name> Create a partition definition between the current database target and the second database (the source).
at <host-name> Specify the remote computer name, if you are creating a partition definition between the current database and one residing on a remote OLAP Server host.
as <user-name> identified by <password> To create the partition, you must provide the name and password of a user who can connect to both databases. Essbase uses the login information to:
  • Transfer data between the source and the target for replicated and transparent partitions. Database security filters can be applied to prevent end users from seeing privileged data.
  • Synchronize database outlines for all partition types.
mapped... Define the member-name mapping for shared sections of both databases, if member names for sections that map are different in the two databases.
outline... Specify the direction in which outline synchronization should proceed, if necessary. The default direction is the same as the data-refresh direction.
update... Allow or disallow the updating of data in a replicated-type partition target.
default login as Specify a default user name and password with which to provide generic access to the linked-partition data source. When accessing a linked partition, Essbase attempts to use the end user's login information to connect to the source database. If the user does not have access to the source database, Essbase looks for the linked-partition default user name and password.
comment Create a comment to describe the partition definition between the two databases.
validate only Validate the existing partition definition described by this statement, without actually creating it.

CREATE USER

You can create a user in the following ways using create user.

Key Phrase Explanation
create user Create a new Essbase user.
create or replace user Create a new Essbase user. If a user of that name already exists, it is replaced.
create user as Create a user as a copy of an existing user. The new user has an identical security profile to the user that was copied.
member of group Create a user and assign membership to a security group.
comment Create an optional comment to describe the user.
type external with protocol... Create a user that must log in using an external security authentication method, such as LDAP, instead of the standard Essbase login security.

DISPLAY APPLICATION

You can display application information in the following ways using display application.

Key Phrase Explanation
all Display all applications on the system.
<app-name> Display the named application.

DISPLAY CALCULATION

You can display calculations in the following ways using display calculation.

Key Phrase Explanation
all Display all stored calculations on the system.
<calc-name> Display the named calculation.
on application Display all calculations on the specified application.
on database Display all calculations on the specified database.

DISPLAY DATABASE

You can display database information in the following ways using display database.

Key Phrase Explanation
all Display information for all databases on the system.
<dbs-name> Display information about the specified database.
on application Display information about all databases on the specified application.

DISPLAY DISK VOLUME

You can display disk volume information in the following ways using display disk volume.

Key Phrase Explanation
all Display all disk-volume definitions on the system.
<unique-vol-name> Display a disk-volume definition by name.
on database Display all disk-volume definitions associated with the specified database.

DISPLAY FILTER

You can display filters in the following ways using display filter.

Key Phrase Explanation
all Display all filters on the system.
<filter-name> Display a filter by name.
on database Display all filters associated with the specified database.

DISPLAY FILTER ROW

You can display filter contents in the following ways using display filter row.

Key Phrase Explanation
all Display all filters (and their contents) defined on the system.
<filter-name> Display a filter and its contents by name.
on database Display all filters (and their contents) associated with the specified database.

DISPLAY FUNCTION

You can display custom-defined functions in the following ways using display function.

Key Phrase Explanation
all Display all custom-defined functions, including those registered on the application level (local) or on the system level (global).
on system Display all custom-defined functions registered on the system (global). Does not include locally defined functions.
on application Display all custom-defined functions registered with the specified application (local). Does not include globally defined functions.
<func-name> Display a custom-defined function by name.

DISPLAY GROUP

You can display groups in the following ways using display group.

Key Phrase Explanation
all Display all security groups on the system.
<group-name> Display a security group by name.

DISPLAY LOCATION ALIAS

You can display location aliases in the following ways using display location alias.

Key Phrase Explanation
all Display all location aliases defined on the system.
<location-alias-name> Display a location alias by name.
on database Display all location aliases defined for the specified database.

DISPLAY MACRO

You can display custom-defined macros in the following ways using display macro.

Key Phrase Explanation
all Display all custom-defined macros, including those registered on the application level (local) or on the system level (global).
on system Display all custom-defined macros registered on the system (global). Does not include locally defined macros.
on application Display all custom-defined macros registered with the specified application (local). Does not include globally defined macros.
<macro-name> Display a custom-defined macro by name.

DISPLAY PARTITION

You can display partition information in the following ways using display partition.

Key Phrase Explanation
all Display all partitions defined on the system.
on database Display all partitions associated with the specified database.
advanced Display full information including areas and member mappings for local and remote pieces of partitions.

DISPLAY PRIVILEGE

You can display security permissions in the following ways using display privilege.

Key Phrase Explanation
user... Display security permissions for all users, or for a specified user.
group... Display security permissions for all groups, or for a specified group.

DISPLAY SESSION

You can display login and request information in the following ways using display session.

Key Phrase Explanation
all Display information about all current user sessions and active requests.
<session-id> Display information about a particular user session, indicated by the numeric session ID.
by user Display information about all current sessions by a particular user.
by user on application Display information about all current sessions by a particular user on the specified application.
by user on database Display information about all current sessions by a particular user on the specified database.
on application Display information about all current sessions on the specified application.
on database Display information about all current sessions on the specified database.

DISPLAY SYSTEM

You can display server-wide information in the following ways using display system.

Key Phrase Explanation
display system Display current connections and system-wide settings.
display system export_directory Display names of directories created for linked-reporting objects exported from a database to a directory created in $ARBORPATH\app.

If you used export lro and gave a full path to a directory for export files, those directories are not listed. Only export directories created in the ARBORPATH\App directory using the following export lro method are listed:

export database DBS-NAME lro to <server or local> directory DBS-EXPORT-DIR;

where DBS-EXPORT-DIR is a suffix (for example, dir1) for the name of a directory created by MaxL in $ARBORPATH\App. MaxL creates the directory with a prefix of appname-dbsname-. For example, display system export_directory would list the following directories existing under $ARBORPATH\App:
sample-basic-dir1
sample-basic-dir2

but it would not list export directories created elsewhere by providing a full directory path when using the export lro statement, such as:
c:\MyExports\MyExportDir

DISPLAY USER

You can display user information in the following ways using display user.

Key Phrase Explanation
all Display information about all users on the system.
<user-name> Display information about the specified user.
in group all Display membership information for all groups on the system.
in group <group-name> Display membership information for the specified group.

DISPLAY VARIABLE

You can display substitution variables in the following ways using display variable.

Key Phrase Explanation
all Display all substitution variables defined on the system.
<variable-name> Display a substitution variable by name.
on application Display all substitution variables defined on the specified application.
on database Display all substitution variables defined on the specified database.

DROP APPLICATION

You can delete applications in the following ways using drop application.

Key Phrase Explanation
cascade Delete an application along with its constituent databases.
force Delete an application that may have locked objects in a constituent database.

DROP CALCULATION

You can delete calculations using drop calculation.

Key Phrase Explanation
drop calculation <calc-name> Delete the specified calculation.

DROP DATABASE

You can delete databases using drop database.

Key Phrase Explanation
force Delete a database that may have locked objects.

DROP FILTER

You can delete filters using drop filter.

Key Phrase Explanation
drop filter <filter-name> Delete a filter by name.

DROP FUNCTION

You can delete custom-defined functions using drop function.

Key Phrase Explanation
drop function <func-name> Delete a custom-defined function by name.

DROP GROUP

You can delete security groups using drop group.

Key Phrase Explanation
drop group <group-name> Delete a security group by name. Members of the group are not deleted, but their membership to the group becomes obsolete.

DROP LOCATION ALIAS

You can delete location aliases in the following ways using drop location alias.

Key Phrase Explanation
drop location alias <location-alias-name> Delete a location-alias definition.

DROP MACRO

You can delete custom-defined macros in the following ways using drop macro.

Key Phrase Explanation
drop macro <macro-name> Delete a custom-defined macro.

DROP PARTITION

You can delete partition definitions in the following ways using drop partition.

Key Phrase Explanation
drop...partition...from Remove a transparent, replicated, or linked partition definition between the current target database and a source database.
drop...partition...to Remove a transparent, replicated, or linked partition definition between the current source database and a target database.
at <host-name> Optionally specify the host computer name, if removing a partition definition associated with a remote server.

DROP USER

You can delete users using drop user.

Key Phrase Explanation
drop user <user-name> Delete an Essbase user account by user name.

EXECUTE CALCULATION

You can run calculations in the following ways using execute calculation.

Key Phrase Explanation
execute calculation <calc-name> Run the specified stored calculation script.
<calc-name> on database Run the specified stored calculation script against the specified database.
<calc-string> on <dbs-name> Run an anonymous calculation, whose body is contained in<calc-string>, against the specified database.
default on <dbs-name> Run the default calculation against the specified database.

top EXPORT DATA

You can export data from a database in the following ways using export data.

Key Phrase Explanation
export database <dbs-name> all data... Export all data in the specified database to the $ARBORPATH/app directory on the server. Note: Exporting data does not clear the data from the database.
export database <dbs-name> level0 data... Export level-0 data blocks only (blocks containing only level-0 sparse member combinations). A level-0 block is created for sparse member combinations when all of the members of the sparse combination are at the bottom of dimension branches.
Note: Exporting data does not clear the data from the database.
export database <dbs-name> input data... Export only blocks of data where the block contains at least one data value that was loaded (imported), rather than created as the result of a calculation.
export database <dbs-name> ... data in columns

Export data in columns, to facilitate loading the exported data into a relational database. In each row, the columnar format displays a member name from every dimension. Names can be repeated from row to row.

Columnar format provides a structure to the exported data, so that it can be used for further data processing by applications other than Essbase tools. In non-columnar format, sparse members identifying a data block are included only once for the block. Because the export file in non-columnar format is smaller than in columnar format, reloading a file in non-columnar format is faster.
export database <dbs-name> ...using...report_file... Run a stored report script, exporting a subset of the database.

top EXPORT LRO

You can export LRO information from a database in the following ways using export lro.

Key Phrase Explanation
to server directory Export the LRO information to a directory you specify on the OLAP server to which you are connected.
to local directory Export the LRO information to a directory you specify on the current computer.

GRANT

You can grant permissions to users and groups in the following ways using grant.

Key Phrase Explanation
create_application to... Grant Create/Delete Applications permission to a user or group.
create_user to... Grant Create/Delete Users/Groups permission to a user or group.
no_access to... Revoke any permissions the user or group may have.
supervisor to... Grant Supervisor permission to a user or group.
no_access on application...to... Revoke any permissions the user or group may have on the specified application.
designer on application...to... Grant Application Designer permission to a user or group for the specified application.
no_access on database...to... Revoke any permissions the user or group may have on the specified database.
read on database...to... Grant Read permission to a user or group for the specified database.
write on database...to... Grant Write permission to a user or group for the specified database.
designer on database...to... Grant Database Designer permission to a user or group for the specified database.
filter <filter-name> to... Assign a filter to a user or group that grants or denies permissions to the specified database at a data-value level of detail.
execute <calc-name> to... Grant the user or group permission to run the specified stored calculation script.
execute any on system to... Grant the user or group permission to run any calculation against any database on the OLAP Server.
execute any on application...to... Grant the user or group permission to run any calculation against any databases in the specified application.
execute any on database...to... Grant the user or group permission to run any calculation against the specified database.
execute default on system to... Grant the user or group permission to run the default calculation against any database on the OLAP Server.
execute default on application...to... Grant the user or group permission to run the default calculation against any databases in the specified application.
execute default on database...to... Grant the user or group permission to run the default calculation against the specified database. The default calculation is typically 'CALC ALL;', but it can be changed using alter application set default calculation.

top IMPORT DATA

You can import data to a database in the following ways using import data.

Key Phrase Explanation
import database <dbs-name> data from... Specify whether the data import is from a local or server file, and what type of file to import data from.
...using ... rules_file Import data into the database using a specified rules file.
...<data error spec> (on error...) Required. Tell Essbase what to do in case of errors during the data load: abort the operation, or write or append to a specified error log.
...<SQL connect spec> (connect as...) If you are importing data from an SQL source, provide your SQL user name and password. You must always use a rules file when you load SQL data sources.

top IMPORT DIMENSIONS

You can import dimensions to a database in the following ways using import dimensions.

Key Phrase Explanation
import database <dbs-name> data from... Specify whether the dimension import is from a local or server file, and what type of file to import the dimension from.
...using ... rules_file Import dimensions into the database outline using a specified rules file.
...preserve all data If you need to preserve all data when importing dimensions, specify that here.
...on error... Tell Essbase what to do in case of errors during the dimension build: abort the operation, or write or append to an error log.
...<SQL connect spec> (connect as...) If you are importing dimensions from an SQL source, provide your SQL user name and password. You must always use a rules file when you load SQL data sources.
...<preserve spec alt> (preserve...data) If you need to preserve level-0 or input data when importing dimensions, specify that here.

top IMPORT LRO

You can import exported LRO information to a database using import lro.

import database <dbs-name> lro... Import linked reporting objects (LROs) from the specified export directory on the local computer or on a remote server where the Essbase OLAP Server resides.

REFRESH CUSTOM DEFINITIONS

You can update Essbase's record of custom-defined function and macro definitions using refresh custom definitions.

refresh custom definitions on application... Refresh the definitions of custom-defined functions or macros associated with the specified application, without restarting the application. To refresh global definitions, issue the statement separately for each application on the Essbase OLAP Server.

REFRESH OUTLINE

You can synchronize the outlines between partitioned databases using refresh outline.

...to... Use the current source outline to refresh the remote target outline.
...from... Refresh the current target outline using the remote source outline.
purge outline change file Clear any source outline changes that have already been applied to the target outline or have been rejected. Source outline changes that have not been applied or rejected are not deleted from the outline change file.
apply all Refresh all aspects of the target outline, including dimension changes, member changes, and member property changes made to the source outline. This is the recommended method for refreshing outlines, because if you choose to omit some changes, those changes cannot be applied later.
apply nothing Do not apply source outline changes to any aspects of the target outline. The target outline will be considered synchronized to the source, and the timestamp will be updated, although source changes were not actually applied to the target.
apply on dimension... Refresh the target outline with all or some dimension changes made to the source outline.
  • add:
    • Refresh with added dimensions.
  • delete:
    • Refresh by deleting dimensions.
  • rename:
    • Refresh with renamed dimensions.
  • update:
    • Refresh with dimensions that have member updates (required if the statement will also use apply on member).
  • move: Refresh the order of dimensions in the outline.
Use commas to separate the types of source dimension changes to refresh on the target. For example, to refresh only with added or moved dimensions, use the following phrase: apply on dimension add, move.
apply on member... Refresh the target outline with all or some physical member changes made to the source outline. Requires apply on dimension update.
  • add: Refresh dimensions with added members.
  • delete: Refresh dimensions by deleting members.
  • rename: Refresh dimensions with renamed members.
  • move: Refresh the order or hierarchy of members in the dimension.
Use commas to separate the types of source member changes to refresh on the target. For example, to refresh only with added or moved members, use the following phrase: apply on dimension update, apply on member add, move.
apply on member_property... Refresh the target outline with all or some member property changes made to the source outline. Requires apply on dimension update.
  • account_type: Refresh with changes in account type.
  • alias: Refresh with changes to aliases.
  • calc_formula: Refresh with changes to member formulas.
  • consolidation: Refresh with changes to consolidation tags.
  • currency_conversion: Refresh with changes to currency conversion flags.
  • currency_category: Refresh with changes to currency categories.
  • data_storage: Refresh with changes to data storage tags.
  • uda: Refresh with changes to UDAs.
Use commas to separate the types of source member-property changes to refresh on the target. For example, to refresh only with updated member formulas, use the following phrase: apply on dimension update, apply on member_property calc_formula.

REFRESH REPLICATED PARTITION

You can update a replicated-partition database using refresh replicated partition.

...to... Use the current replicated-partition database source to refresh the remote target partition.
...from... Refresh the current replicated-partition database target from the remote source partition.
...updated data Refresh a replicated-partition database only with data that has been updated since the last refresh.
...all data Refresh a replicated-partition database with all data, regardless of the last refresh.

Copyright 1991-2002 Hyperion Solutions Corporation. All rights reserved.