TM1 supports the following TM1-specific MDX expressions. You can apply these expressions while developing MDX applications to run against the TM1 server or when creating/editing dynamic subsets in the Expression Window of the Subset Editor.

• TM1FILTERBYPATTERN(set, pattern_str)
• TM1FILTERBYLEVEL(set, level_number)
• TM1DRILLDOWNMEMBER(set1, set2 {|ALL [,RECURSIVE]} )
• TM1Member
• TM1SORT(set, ASC|DESC )
• TM1SORTBYINDEX(set, ASC|DESC )
• TM1SUBSETALL(dimname)
• TM1SubsetToSet
• TM1TupleSize

TM1FILTERBYPATTERN(set, pattern_str):
This TM1-specific MDX function returns all the members in set with names matching the pattern pattern_str.
The syntax of pattern_str is the same used for the Select By Regular Expression option on the Subset Editor.

TM1FILTERBYLEVEL(set, level_number):
This TM1-specific MDX function returns all the members in of the specified level_number.
level_number is a number specifying a TM1 level number not an MDX level number.

TM1DRILLDOWNMEMBER(set1, set2 {|ALL [,RECURSIVE]}):
This TM1-specific MDX function is similar to the DRILLDOWNMEMBER function from Microsoft , but it has been adjusted to match the functionality of the Expand button {bmct expand_button.bmp} on the Subset Editor.

ALL means drilldown all the members in set1.
RECURSIVE means that when one member from set1 is being drilled down upon, every consolidated member resulting from that expansion will also be recursively drilled down until level 0 ( TM1 level 0 ) is reached.

TM1Member:
This function returns a member from a specified tuple. A null member reference is returned when any of the following conditions are encountered:

• A null Tuple parameter
• An out-of-range numeric Index parameter
• A dimension or hierarchy parameter not found in the passed tuple.

Syntax:
TM1Member(Tuple, MemberSpecifier);

Arguments:

Argument	Description
Tuple	An expression that resolves to a tuple.
MemberSpecifier	This parameter can be either a 0-based numeric index into the tuple or the name of a dimension/hierarchy associated with the tuple. See below for examples showing both parameter types.

Example:
TM1Member ( [model].Members.Item(23) ,0 ) ] This example uses a numeric index into the tuple as the MemberSpecifier argument.

TM1Member( [model].Members.Item(23), [Model] ) ] This example uses the name of a dimension associated with the tuple as the MemberSpecifier argument.

TM1SORT(set, ASC|DESC):
This TM1-specific MDX function sorts set alphabetically.
ASC sorts A-Z
DESC sorts Z-A

TM1SORTBYINDEX(set, ASC|DESC):
This TM1-specific MDX function sorts set by the index value of the members.

ASC sorts by ascending index value.
DESC sorts by descending index value.

TM1SUBSETALL(dimname):
This TM1-specific MDX function returns the TM1 subset All of dimname.

TM1SubsetToSet:
This function returns the members of a TM1 subset.

TM1SubsetToSet is equivalent to the dimension.subsetname expression, but does not require string literals. Instead, TM1SubsetToSet lets you use expressions that resolve to the appropriate dimension and subset.

Syntax: TM1SubsetToSet(Dimension_exp, Subet_exp);
Arguments:

Argument	Description
Dimension_exp	An expression that resolves to a valid TM1 dimension name.
Subset_exp	An expression that resolves to a valid subset of the dimension returned by Dimension_exp.

When resolving an expression for a subset, the TM1 server searches first in the private subset list and then in the public list.

TM1TupleSize:
This function returns the number of members in a tuple.

Syntax: TM1TupleSize(Tuple);

Arguments:

Argument	Description
Tuple	An expression that resolves to a tuple. The function returns 0 if the Tuple argument does not resolve to a valid tuple, or of the tuple is null or empty.

TM1-Specific MDX expressions:
TM1 supports the following TM1-specific MDX expressions. You can apply these expressions while developing MDX applications to run against the TM1 server or when creating/editing dynamic subsets in the Expression Window of the Subset Editor.

• dimension.subsetname
• member.ANCESTORS

dimension.subsetname
This TM1-specific MDX expression returns members of subsetname in dimension.

Since the same syntax (dimension.IDENTIFIER)is used for members and levels, a subset with the same name of a member or a level will never be instantiated.

When searching for a subset, the TM1 server searches first in the private subset list and then in the public list.

member.ANCESTORS: This TM1-specific MDX expression returns the ancestors of member.
For example, assuming the following hierarchy of the Month dimension:

Year
• 1 Quarter
• Jan
• Feb
• Mar

the expression

month.jan.ANCESTORS
returns the set
{ 1Quarter, Year }.

If the member has more than one immediate parent, the expression returns the set containing the first parent in the default hierarchy. Consider a hierarchy of a Region dimension, where the member Belgium has more than one immediate parent, being Benelux and Europe. In this case, the expression

region.belgium.ANCESTORS
returns the set
{ Benelux, Europe }.

Read this aritcle completely...

Support for Microsoft-defined MDX expressions and functions:

TM1 supports the following Microsoft-defined MDX expressions and functions. Cognos TM1 implementation of these functions and expressions is based on the definitions in the Microsoft MSDN library, which is available at http://msdn.microsoft.com/library/

• List of Supported Member expressions
• List of Supported Member functions
• List of Supported Numeric functions
• List of Supported Set expressions
• List of Supported Set functions
• List of Supported Tuple expressions

List of Supported Member expressions:

• (dimension).CURRENTMEMBER
• (member).FIRSTCHILD
• (member).FIRSTSIBLING
• (member).LAG
• (member).LASTCHILD
• (member).LASTSIBLING
• (member).LEAD
• (member).NEXTMEMBER
• (member).PARENT
• (member).PREVMEMBER

List of Supported Member functions :

• ANCESTOR(...)
• COUSIN(...)
• OPENINGPERIOD(...)
• PARALLELPERIOD(...)

List of Supported Numeric functions :

• AGGREGATE(...)
• AVG(...)
• CORRELATION(...)
• COUNT(...)
• COVARIANCE(...)
• LINREGINTERCEPT(...)
• LINREGPOINT(...)
• LINREGR2(...)
• LINREGSLOPE(...)
• LINREGVARIANCE(...)
• MAX(...)
• MEDIAN(...)
• MIN(...)
• RANK(...)
• STDDEV(...)
• SUM(...)
• VAR(...)

List of Supported Set expressions :

• (dimension).MEMBERS
• (level).MEMBERS
• (member). CHILDREN
• (member).SIBLINGS

List of Supported Set functions :

• ADDCALCULATEDMEMBERS(...)
• BOTTOMCOUNT(...)
• BOTTOMPERCENT(...)
• BOTTOMSUM(...)
• CROSSJOIN(...)
• DESCENDANTS(...)
• DISTINCT(...)
• DRILLDOWNLEVEL(...)
• DRILLDOWNLEVELBOTTOM(...)
• DRILLDOWNLEVELTOP(...)
• DRILLDOWNMEMBER(...)
• DRILLDOWNMEMBERBOTTOM(...)
• DRILLDOWNMEMBERTOP(...)
• DRILLUPMEMBER(...)
• DRILLUPLEVEL(...)
• EXCEPT(...)
• EXTRACT(...)
• FILTER(...)
• GENERATE(...)
• HEAD(...)
• HIERARCHIZE(...)
• INTERSECT(...)
• LASTPERIODS(...)
• ORDER(...)
• PERIODSTODATE(...)
• TOPCOUNT(...)
• TOGGLEDRILLSTATE(...)
• TOPPERCENT(...)
• TOPSUM(...)
• SUBSET(...)
• UNION(...)

List of Supported Tuple expressions :

• (set).CURRENTMEMBER
• (set)[.ITEM](...)

Read this aritcle completely...

TurboIntegrator Editor:

The TurboIntegrator Editor lets you define processes for importing data or metadata from several possible sources. The editor is comprised of five tabs, several of which are dynamic or contain sub-tabs. You define a process by completing each tab in sequential order.

File Menu:

Menu Item	Description
Save	Saves the current process definition.
Save As	Saves the current process definition with a new name.
Run	Runs the current process.
Exit	Closes the TurboIntegrator Editor.

Edit Menu:

Menu Item	Description
Undo	Undoes the last typing action that was performed on the Prolog, Metadata, Data, or Epilog procedure sub-tab.
Cut	Cuts the selected text to the Clipboard.
Copy	Copies the selected text to the Clipboard.
Paste	Pastes the contents of the Clipboard to the current field or cell.

Tabs:

• Data Source Tab
• Variables Tab
• Maps Tab
• Advanced Tab
• Schedule Tab

Data source Tab:
Use the Data Source tab to identify and access the source from which you want to import data.

Note: When defining a process from a TM1 client, the path to an ASCII or ODBC data source may differ from the path used by the server. If this happens, the process will fail. To ensure that your processes work correctly:

• Define processes involving ODBC data sources on the actual TM1 server where the process is to reside. Do not use a remote client to define such a process.
• Use the Windows Network Neighborhood to define the path to ASCII data sources. This ensures that the path is unambiguous to both clients and servers.

The fields and options available on the Data Source tab vary according to the Datasource Type you select. The following table describes the required fields and options for each source.

Datasource Type	Required Fields and Options	Description
ODBC	Data Source Name	The full path to the ODBC data source.
	UserName	Your user name on the source.
	Password	Your password.
	Query	An SQL query to extract data from the source.
ASCII	Data Source Name	The full path to the source ASCII file. To ensure that this path is recognizable to both client and server, click the Browse button and use the Network Neighborhood to define the path.
	Data Source Name On Server	When you create a new process, TurboIntegrator assumes that the data source name on the TM1 server is identical to the data source name used to create the process. If the data source name on the server is different from the local data source used to create the process, enter the full path to the data source file on the server.
	Delimiter Type	Select the method the source uses to separate columns, either Delimited or Fixed Width.
	Delimiter	This option becomes available when you select a Delimited type. Specify the character used to delimit columns in the data source.
ASCII (cont.)	Set Field Width	This button becomes available when you select a Fixed Width type. Click the button, then use the Data Preview dialog box to set column widths.
	Quote Char	Specify the quote character used in your source data.

Example Grid:
The example grid displays the first ten records in your data source. Use this grid to confirm that the source is correct and to help determine the structure of records.
If you change your data source, click Update to refresh the display of the grid.

Variables Tab:
The Variables tab includes a grid and two buttons.

Grid:
Use the Variables grid to assign variables and identify the contents of each column in your data source. The Variables grid includes the following columns.

Column	Description
Column ID	Lists each unique field or column identified in your data source. Cells in this column cannot be edited.
Variable Name	Contains an automatically generated variable for each column in your data source. All generated variables are named Vn, where n is 0 for the first column and is incremented by 1 for each subsequent column in the source. To assign a different variable, click the appropriate cell and enter the new variable.
Variable Type	Contains a drop-down list for each column in your data source. Use the list to specify whether a variable is string or numeric.
Sample Value	Contains sample values from the first record of your source. These sample values help you identify the contents of each column of your source. Cells in the Sample Value column cannot be edited.
Contents	Contains a drop-down list for each column in your data source. Use the list to specify the type of value contained in each column of your source.
Formula	This column is grayed-out for all fields in your source, and becomes available only when you create a new variable. When you create a new variable, double-click the associated Formula cell to open the Process Variable Formula dialog box, from which you can define a formula for the variable.

Buttons:

Button	Description
New variable	Click to create a new variable.
Delete	Click to delete a user-created variable.

Maps Tab:
Use the Maps tab to specify how source data maps to cubes, dimensions, data, consolidations, and attributes in the TM1 database.

The Maps tab consists of a series of sub-tabs, each containing options that let you map variables for your source data to existing TM1 metadata structures. The sub-tabs that are available vary according to the type of values contained in your source data, as specified in the Contents column of the Variables tab.

The Maps tab contains the following sub-tabs.

Cube:
Use the Cube sub-tab to specify how TurboIntegrator maps imported data to TM1 cubes. The Cube sub-tab includes the following options.

Option	Description
Cube Action	Select an option to create, update, recreate, or apply no action to a cube.
Cube Name	Specify the cube to which the action applies. If creating a new cube, type the cube name in the entry field. Otherwise, select an existing cube from the drop-down list.
Zero Out Portion	This option becomes available when you select the Update Cube action. Select this box if you want to set all data points in a cube view to zero.
View Name	This option becomes available when you select the Update Cube and Zero Out Portion options. Select or define the view that encompasses the data points you want to zero out.
Data Action	Select an option that determines how processed data is stored in the cube. Store Values overwrites existing cube values with values imported by the process. Accumulate Values adds values imported by the process to existing cube values.
Enable Cube Logging	Fill this check box to write cube changes to the Tm1s.log file. Clear this box to process cubes without recording changes in Tm1s.log.

Dimensions:
Use the Dimensions sub-tab to map element variables to dimension elements.

The sub-tab includes a grid you use to map individual variables to dimensions in the TM1 database. The grid includes the following columns.

Column	Description
Element Variable	Contains the name of each variable for which you specified a Contents value of Element. The Contents value is specified in the Variables tab. This column also contains the label (Data Variables) for any variables with a Contents value of Data.
Sample Value	A sample value from the first record of your data source. Use this value to help identify the dimension to which the element variable maps.
Dimension	Contains a drop-down list of all dimensions available on the server. Select the dimension to which the element variable maps. To map the element variable to a new dimension, type the new dimension name in the entry field.
Order in Cube	This option becomes available when the Cube Action is Create. Use the drop-down list to specify the order of each dimension in the cube you are creating.
Action	Contains a drop-down list of available dimension actions. Select an action. To create a new dimension, you must specify an action of Create.
Element Type	Select an element type for the variable, either Numeric or String.
Element Order	Select an option for ordering elements in any dimensions you are creating or updating. There are four sort orders: Input - Sorts elements in the order they are created in the dimension. Name - Sorts elements in alphabetical order, either ascending or descending. Level - Sorts elements by hierarchy level, either ascending or descending. Hierarchy - Sorts elements as they exist in the dimension hierarchy.

Data:
Use the Data sub-tab to map data variables to specific elements.

The sub-tab includes a grid you use to map individual variables to elements in the TM1 database. The grid includes the following columns.

Column	Description
Data Variable	Contains the name of each variable for which you specified a Contents value of Data. The Contents value is specified in the Variables tab.
Element	Click the right arrow button to open the Subset Editor, where you can choose the element to which the variable maps. To map the variable to a new element, type the element name in the entry field.
Element Type	Click the drop-down list to select an element type.
Sample Value	A sample value from the first record of your data source. Use this value to help identify the element to which the data variable maps.

Consolidations:
Use the Consolidations sub-tab to map children to consolidated elements.

The sub-tab includes a grid you use to map individual variables to dimensions in the TM1 database. The grid includes the following columns.

Column	Description
Cons. Variable	Contains the name of each variable for which you specified a Contents value of Consolidation. The Contents value is specified in the Variables tab.
Dimension	Contains a drop-down list of dimensions to which the consolidation can map.
Child Variable	Contains a list of variables from which you select the immediate child of the consolidation.
Weight	Assigns a weight to the specified child variable.
Sample Value	A sample value from the first record of your data source. Use this value to help identify the element to which the consolidation maps.

Attributes:
Use the Attributes sub-tab to map attribute variables to specific attributes.

The sub-tab includes a grid you use to map individual variables to dimensions in the TM1 database. The grid includes the following columns.

Column	Description
Attribute Variable	Contains the name of each variable for which you specified a Contents value of Attribute. The Contents value is specified in the Variables tab.
Sample Value	Displays a sample value from the data source. Use this sample to help map the attribute.
Dimension	Contains a drop-down list of all dimensions available on the server. Select the dimension to which the attribute applies.
Element Variable	Contains a drop-down list of element variables. Select the variable for the element to which the attribute variable applies.
Attribute	Contains a drop-down list of attributes to which the variable can map. Select the appropriate attribute from this list.
Action	Choose to either Create a new attribute or Update an existing one.
Attribute Type	Identifies the type of attribute selected in the Attribute column.

Advanced Tab:
The Advanced tab contains several sub-tabs that display statements generated by TM1 based on the options you select elsewhere in the TurboIntegrator Editor. The Advanced tab also includes a sub-tab where you can define parameters for the process.

Parameters:

Item	Description
Insert	Click to insert a new parameter.
Delete	Click to delete a selected parameter.
Parameter Column	Type a name for each new parameter.
Type Column	For each parameter, select a type from the drop-down list.

Prolog:

Item	Description
Statement text box	Displays generated statements that define a series of actions to be executed before the data source is processed. You can enhance a process by creating additional statements with rules or TurboIntegrator functions.
Goto Line button	Click this button, enter the line you want to go to, then click OK to go directly to a line of code in the statement text box.

Metadata:

Item	Description
Statement text box	Displays generated statements that define a series of actions to be executed on TM1 metadata before the data source is processed. You can enhance a process by creating additional statements with rules or TurboIntegrator functions.
Goto Line button	Click this button, enter the line you want to go to, then click OK to go directly to a line of code in the statement text box.

Data:

Item	Description
Statement text box	Displays generated statements that define a series of actions to be executed when the data source is processed. You can enhance a process by creating additional statements with rules or TurboIntegrator functions.
Goto Line button	Click this button, enter the line you want to go to, then click OK to go directly to a line of code in the statement text box.

Epilog:

Item	Description
Statement text box	Displays generated statements that define a series of actions to be executed after the data source is processed. You can enhance a process by creating additional statements with rules or TurboIntegrator functions.
Goto Line button	Click this button, enter the line you want to go to, then click OK to go directly to a line of code in the statement text box.

Schedule Tab:
Use this tab to schedule a process to execute at regular intervals

Item	Description
Schedule this Process as a Chore Named	Fill this check box to enable the process to be executed as a chore at regular intervals. By default, the chore bears the same name as the process. If you want to assign the chore a different name, type it in the entry field.
Chore Start Date and Time	Select a start date on the calendar and specify a start time in the Time field.
Chore Execution Frequency	Fill the appropriate fields to establish the interval at which the chore should be executed.

Read this aritcle completely...

The triggers feature provided by Essbase Analytic Services enables efficient monitoring of data changes in a database.

If data breaks rules specified in a trigger. Analytic Services can log relevant information in a file, or, for some triggers, can send an e-mail alert (to a user or system administrator). For example, you might want to notify the sales manager if, in the western region, sales for a month fall below sales for the equivalent month in the previous year
There are two types of triggers:
1.On-update triggers
2.After-update triggers

On-update triggers are activated during the update process, when the block containing the data referenced by the trigger is brought into memory.

After-update triggers are activated after the update transaction is complete.

Note:On-update triggers are supported only on block storage database.

To administer triggers, a user must have Database Designer security privillege. Analytic Services monitors and potentially activates triggers during the following activities:
->Data load
->Calculation
->Lock and Send from Essbase Spreadsheet Add-in (does not apply to aggregate storage databases)

You can specify whether all trigger data values are stored in the spool file or whether only the most current values are stored (for example, use the a log_value parameter on the Maxl create trigger statement or create and replace trigger statement). If the log_value parameter is set to ON, both the new value and old value are logged to the spoolfile. If the log-value parameter is set to OFF, values are not logged to the spool file. the log_value parameter is active only for data load and lock-and-send activities.
Example:
Tracking Inventory Level
Tracks the inventory level for the following product, region and months:
->Colas (product 100)
->Eastern region (market East)
->January, February, and March (the children of Qtr1)

The trigger is activated after the update action is complete. If the inventory of coals in the eastern region falls below 500,000, the example logs an entry in the file Inventory-East.

Create after update trigger Sample.basic.Inventory_east
Where "(crossjoin ({children([qtr1])},
{([Market].[East],[Product].[100],[Inventory].[Ending Inventory])}))"
when [Ending Inventory]<500000 then
spool Inventory_East
end;

Read this aritcle completely...

Setting the Default Calculation:

By default, the calculation for a database is a CALC ALL of the database outline. CALC ALL consolidates all dimensions and members and calculates all formulas in the outline.

You can, however, specify any calculation script as the default database calculation. Thus, you can assign a frequently-used script to the database rather than loading the script each time you want to perform its calculation. If you want a calculation script to work with calculation settings defined at the database level, you must set the calculation script as the default calculation.

To set the default calculation, use a tool:

Tool	Topic	Location
Administration Services	Setting the Default Calculation	Essbase Administration Services Online Help
MaxL	alter database	Essbase Technical Reference
ESSCMD	SETDEFAULTCALCFILE	Essbase Technical Reference

Calculating Databases:
If you have Calculation permissions, you can calculate a database.
When you use Administration Services to calculate a database, you can execute the calculation in the background so that you can continue working as the calculation processes. You can then check the status of the background process to see when the calculation is complete.

To calculate a database, use a tool:

Tool	Topic	Location
Administration Services	Calculating Block Storage Databases	Essbase Administration Services Online Help
MaxL	execute calculation	Essbase Technical Reference
ESSCMD	CALC, CALCDEFAULT, and CALCLINE	Essbase Technical Reference
Essbase Spreadsheet Add-in for Excel	Calculating a Database	Essbase Spreadsheet Add-in for Excel Online Help

Canceling Calculations:
To stop a calculation before Essbase completes it, click the Cancel button while the calculation is running.

When you cancel a calculation, Essbase performs one of the following operations:

• Reverts all values to their previous state.
• Retains any values calculated before the cancellation.

How Essbase handles the cancellation depends on the Essbase Kernel Isolation Level settings.

Parallel and Serial Calculation:
Essbase supports parallel and serial calculations:

• Serial calculation (the default): All steps in a calculation run on a single thread. Each task is completed before the next is started.
• Parallel calculation: The Essbase calculator can analyze a calculation, and, if appropriate, assign tasks to multiple CPUs (up to 4).

Security Considerations:
To calculate a database, you must have Calculate permissions for the database outline. With calculate permissions, you can calculate any value in the database and you can calculate a value even if a security filter denies you read and update permissions. Careful consideration should be given to providing users with calculate permissions.

Read this aritcle completely...

Multidimensional Calculation Concepts:

The below screenshot (Fig. A), which is based on a simplified database, illustrates the nature of multidimensional calculations:

Fig. A: Calculating a Multidimensional Database

The database has three dimensions—Accounts, Time, and Scenario.
The Accounts dimension has four members:

• Sales and COGS are input values
• Margin = Sales - COGS
• Margin% = Margin % Sales (Margin as a percentage of Sales)

The Time dimension has four quarters. The example displays only the members in Qtr1—Jan, Feb, and Mar.
The Scenario dimension has two child members—Budget for budget values and Actual for actual values.

An intersection of members (one member on each dimension) represents a data value. The following example has three dimensions; therefore, the dimensions and data values in the database can be represented as a cube, as shown in below screenshot Fig. B:

Fig. B:Three-Dimensional Database
As shown in the below screenshot (Fig. C), when you refer to Sales, you are referring to a slice of the database containing eight Sales values.

Fig. C:Sales, Actual, Budget Slice of the Database

As shown in the below screenshot (Fig. D), when you refer to Actual Sales, you are referring to four Sales values:

Fig. D:Actual, Sales Slice of the Database

To refer to a specific data value in a multidimensional database, you need to specify each member on each dimension. A data value is stored in a single cell in the database. In the below screenshot (Fig. E), the cell containing the data value for Sales, Jan, Actual is shaded.
In Essbase, member combinations are denoted by a cross-dimensional operator. The symbol for the cross-dimensional operator is ->. So Sales, Jan, Actual is written as:

Sales -> Jan -> Actual

Fig. E:Sales, Jan, Actual Slice of the Database

When Essbase calculates the formula “Margin% = Margin % Sales,” it takes each Margin value and calculates it as a percentage of its corresponding Sales value.

Essbase cycles through the database and calculates Margin% as follows:

1. Margin -> Jan -> Actual as a percentage of Sales -> Jan -> Actual. The result is placed in Margin% -> Jan -> Actual.
2. Margin -> Feb -> Actual as a percentage of Sales -> Feb -> Actual. The result is placed in Margin% -> Feb -> Actual.
3. Margin -> Mar -> Actual as a percentage of Sales -> Mar -> Actual. The result is placed in Margin% -> Mar -> Actual.
4. Margin -> Qtr1 -> Actual as a percentage of Sales -> Qtr1 -> Actual. The result is placed in Margin% -> Qtr1 -> Actual.
5. Margin -> Jan -> Budget as a percentage of Sales -> Jan -> Budget. The result is placed in Margin% -> Jan -> Budget.
6. Essbase continues cycling through the database until it has calculated Margin% for every combination of members in the database.

Read this aritcle completely...

Database Calculation:

A database contains two types of values:

1. Values that you enter, which are called input data
2. Values that are calculated from input data

For Example:

• You enter regional sales figures for a variety of products. You calculate the total sales for each product.
• You enter the budget and actual values for the cost of goods sold for several products in several regions. You calculate the variance between budget and actual values for each product in each region.
• The database contains regional sales figures and prices for all products. You calculate what happens to total profit if you increase the price of one product in one region by 5%.

Small differences in the precision of cell values may occur between calculations run on different platforms, due to operating system math library differences.

Note: Most computers represent numbers in binary, and therefore can only represent real numbers approximately. Because binary computers cannot hold an infinite number of bits after a decimal point, numeric fractions such as one third (0.3333...), cannot be expressed as a decimal with a terminating point. Fractions with a denominator of the power of two (for example, 0.50) or ten (0.10) are the only real numbers that can be represented exactly. See IEEE Standard 754 for Floating-Point Representation (IEEE, 1985).

Essbase offers two methods for calculating a database:

1. Outline calculation
2. Calculation script calculation

The method you choose depends on the type of calculation that you want to perform.

Outline Calculation:
Outline calculation is the simplest method of calculation. Essbase bases the calculation of the database on the relationships between members in the database outline and on any formulas that are associated with members in the outline.

For example, the below screenshot (Fig. A) shows the relationships between the members of the Market dimension in the Sample Basic database. The values for New York, assachusetts, Florida, Connecticut, and New Hampshire are added to calculate the value for East. The values for East, West, South, and Central are added to calculate the total value for Market.

Fig A:Relationship Between Members of the Market Dimension

Fig. B shows the Scenario dimension from the Sample Basic database. The Variance and Variance % members are calculated by using the formulas attached to them.

Fig. B:Calculation of Variance and Variance %
It may be more efficient to calculate some member combinations when you retrieve the data, instead of calculating the member combinations during the regular database calculation. You can use dynamic calculations to calculate data at retrieval time.

Calculation Script Calculation:
Calculation script calculation is the second method of calculation. Using a calculation script, you can choose exactly how to calculate a database. For example, you can calculate part of a database or copy data values between members.
A calculation script contains a series of calculation commands, equations, and formulas. For example, the following calculation script increases the actual marketing expenses in the New York region by 5%.

FIX (Actual, "New York")
    Marketing = Marketing *1.05;
ENDFIX;

Read this aritcle completely...

This post explains the basic concepts of multidimensional database calculations and provides information about how to calculate an Essbase block storage database.

For more information about Calculating Essbase Databases, click on the below appropriate links:

• About Database Calculation
• About Multidimensional Calculation Concepts
• Setting the Default Calculation
• Calculating Databases
• Canceling Calculations
• Parallel and Serial Calculation
• Security Considerations

Read this aritcle completely...

Cube Manipulation TurboIntegrator Functions

These functions pertain to manipulating cubes.

CellGetN:
This is a TM1 TurboIntegrator function, valid only in TurboIntegrator processes.
This function retrieves a value from a numeric cube cell.

Syntax:
CellGetN(Cube, e1, e2 [,...en]);

Arguments:

Argument	Description
Cube	The name of the cube from which you want to retrieve a value.
e1,...en	Dimension element names that define the intersection of the cube containing the value to be retrieved. Arguments e1 through en are sequence-sensitive. e1 must be an element from the first dimension of the cube, e2 must be an element from the second dimension, and so on. These arguments can also be the names of aliases for dimension elements or TurboIntegrator variables.

Example:
CellGetN ('y2ksales', 'Actual', 'Argentina', 'S Series 1.8L Sedan', 'Sales', 'Jan');
This example retrieves the numeric value at the intersection of the Actual, Argentina, S Series 1.8L Sedan, Sales, and Jan elements in the y2ksales cube.

CellGetS:
This is a TM1 TurboIntegrator function, valid only in TurboIntegrator processes.
This function retrieves a value from a string cube cell.

Syntax:
CellGetS(Cube, e1, e2 [,...en]);

Arguments:

Argument	Description
Cube	The name of the cube from which you want to retrieve a string.
e1,...en	Dimension element names that define the intersection of the cube containing the value to be retrieved. Arguments e1 through en are sequence-sensitive. e1 must be an element from the first dimension of the cube, e2 must be an element from the second dimension, and so on. These arguments can also be the names of aliases for dimension elements or TurboIntegrator variables.

Example:
CellGetS('Personnel', 'Rep', 'Europe', 'Product');
This example retrieves the string value at the intersection of the Rep, Europe, and Product elements in the Personnel cube.

CellIsUpdateable:
This is a TM1 TurboIntegrator function, valid only in TurboIntegrator processes.
This function lets you determine if a cube cell can be written to. The function returns 1 if the cell can be written to, otherwise it returns 0.

Syntax:
CellIsUpdateable(Cube, e1, e2 [,...en]);

Argument	Description
Cube	The name of the cube to which you want to write a value.
e1,...en	Dimension element names that define the cell to which you want to write a value. Arguments e1 through en are sequence-sensitive. e1 must be an element from the first dimension of the cube, e2 must be an element from the second dimension, and so on. These arguments can also be the names of aliases for dimension elements or TurboIntegrator variables.

Example:
CellIsUpdateable ('y2ksales', 'Actual', 'Argentina', 'S Series 1.8L Sedan', 'Sales', 'Jan');
This example determines if the cell defined by the elements Actual, Argentina, S Series 1.8L Sedan, Sales, and Jan in the y2ksales cube can be written to. If the cell can receive a value, the function returns 1, otherwise it returns 0.

CellPutN:
This is a TM1 TurboIntegrator function, valid only in TurboIntegrator processes.
This function sends a numeric value to a cube cell.

Syntax:
CellPutN(x, Cube, e1, e2 [,...en]);

Argument	Description
x	A numeric value.
Cube	The name of the cube to which you want to send the value.
e1,...en	Dimension element names that define the intersection of the cube to receive the value. Arguments e1 through en are sequence-sensitive. e1 must be an element from the first dimension of the cube, e2 must be an element from the second dimension, and so on. These arguments can also be the names of aliases for dimension elements or TurboIntegrator variables.

Example:
CellPutN(12345, 'y2ksales', 'Actual', 'Argentina', 'S Series 1.8L Sedan', 'Sales', 'Jan');
This example sends the value 12345 to the intersection of the Actual, Argentina, S Series 1.8L Sedan, Sales, and Jan elements in the y2ksales cube.

CellPutProportionalSpread:
This is a TM1 TurboIntegrator function, valid only in TurboIntegrator processes.
This function distributes a specified value to the leaves of a consolidation proportional to existing cell values. CellPutProportionalSpread replaces existing cell values; it cannot be used to add to or subtract from existing cell values.
The function is analogous to the Proportional Spread data spreading method, which is described in detail in the IBM Cognos TM1 Users Guide. If you must add to or subtract from existing cell values, use the Proportional Spread method, which can be executed through the user interface or through data spreading syntax.
Note: When using CellPutProportionalSpread to distribute a value to the leaves of a consolidation, only those leaves already containing non-zero values are changed. This is because zero values cannot be incremented or decremented proportionally; any proportion of zero is still zero.

Syntax:
CellPutProportionalSpread( value, cube, e1, e2, e3...,en);

Arguments:

Argument	Description
value	The value you want to distribute.
cube	The name of the cube into which you want to distribute the value.
e1...en	The names of the elements that identify the consolidation whose leaves will accept the distributed value. Arguments e1 through en are sequence-sensitive. e1 must be an element from the first dimension of the cube, e2 must be an element from the second dimension, and so on. These arguments can also be the names of aliases for dimension elements or TurboIntegrator variables.

Example:
CellPutProportionalSpread(7000,'SalesCube', 'Actual', 'North America', 'S Series 1.8L Sedan','Sales', 'Jan');
This example distributes the value 7000 to the children of the consolidation in the SalesCube identified by the elements Actual, North America, S Series 1.8L Sedan, Sales, and Jan.

CellPutS:
This is a TM1 TurboIntegrator function, valid only in TurboIntegrator processes.
This function sends a string value to a cube cell.

Syntax:
CellPutS(String, Cube, e1, e2 [,...en]);

Argument	Description
String	A String.
Cube	The name of the cube to which you want to send the string.
e1,...en	Dimension element names that define the intersection of the cube to receive the string. Arguments e1 through en are sequence-sensitive. e1 must be an element from the first dimension of the cube, e2 must be an element from the second dimension, and so on. These arguments can also be the names of aliases for dimension elements or TurboIntegrator variables.

Example:
CellPutS('jones', 'Personnel', 'Rep', 'Europe', 'Product');
This example sends the string 'jones' to the intersection of the Rep, Europe, and Product elements in the personnel cube.

CubeCreate:
This is a TM1 TurboIntegrator function, valid only in TurboIntegrator processes.
This function creates a cube from specified dimensions. The order of dimensions specified in the function will be the order of dimensions in the cube definition.
After execution, CubeCreate automatically saves the resulting .cub file to disk.

Syntax:
CubeCreate(Cube, d1, d2 [,...dn]);

Arguments:

Argument	Description
Cube	The name you want to assign to the cube.
d1,...dn	The names of dimensions that comprise the cube. You must specify at least two, but no more than 16, dimensions.

Example:
CubeCreate('y2ksales', 'Actvsbud', 'Region', 'Model', 'Account1', 'Month');
This example creates a cube named y2ksales using the dimensions Actvsbud, Region, Model, Account1, and Month.

CubeDestroy:
This is a TM1 TurboIntegrator function, valid only in TurboIntegrator processes.
This function deletes a specified TM1 cube.

Syntax:
CubeDestroy(Cube);

Arguments:

Argument	Description
Cube	The name of the cube you want to delete.

Example:
CubeDestroy('y2ksales');
This example deletes the cube named y2ksales.

CubeExists:
This is a TM1 TurboIntegrator function, valid only in TurboIntegrator processes.
Use CubeExists to determine if a specific cube exists on the server from which a TurboIntegrator process is executed. The function returns 1 if the cube exists on the server, otherwise it returns 0.

Syntax:
CubeExists(CubeName);

Arguments:

Argument	Description
CubeName	The name of the cube whose existence you want to confirm.

Example:
CubeExists('Inventory');
This example determines if the Inventory cube exists on the TM1 server.

CubeGetLogChanges:
This is a TM1 TurboIntegrator function, valid only in TurboIntegrator processes.
This function returns the Boolean value of the Logging property for a specified cube. The Logging property is set in the TM1 Security Assignments dialog box and stored in the "}CubeProperties" control cube.
If Logging is turned on for a cube, the function returns 1.
If logging is turned off the function returns 0.

Syntax:
CubeGetLogChanges(CubeName);

Arguments:

Argument	Description
CubeName	The cube for which you want to return the value of the Logging property.

Example:
Assuming that Logging is turned on for the 2002sales cube, the function CubeGetLogChanges('2002sales');
returns 1.

CubeSetLogChanges:
This is a TM1 TurboIntegrator function, valid only in TurboIntegrator processes.
This function sets the LOGGING property for a cube.

Syntax:
CubeSetLogChanges(Cube, LogChanges);

Arguments:

Argument	Description
Cube	The name of the cube for which you want to set the LOGGING property.
LogChanges	The Boolean value you want to assign to the property. 1= LOGGING on, 0 = LOGGING off.

CubeUnload:
This is a TM1 TurboIntegrator function, valid only in TurboIntegrator processes.
This function unloads a specified cube, along with all associated cube views, from memory.

Syntax:
CubeUnload(CubeName);

Arguments:

Argument	Description
CubeName	The cube you want to unload from memory.

Example:
CubeUnload('ManufacturingBudget');
This example unloads the ManufacturingBudget cube, and any associated views, from server memory.

Read this aritcle completely...

Chore Management TurboIntegrator Functions:
These functions pertain to managing chores.

ChoreQuit: This is a TM1 TurboIntegrator function, valid only in TurboIntegrator processes.
This function causes the immediate termination of a chore. It can be called from any process within a chore.
When a process encounters the ChoreQuit function, the current chore is terminated with an error status, and a message is written to the server log file indicating that ChoreQuit was called to terminate the chore.

Syntax:
ChoreQuit;

Arguments:
None.

SetChoreVerboseMessages: This is a TM1 TurboIntegrator function, valid only in TurboIntegrator processes.

Use this function to turn on (or off) more verbose reporting of messages to the Tm1s.log file.
This function is best used as an aid to debugging chores in which several processes call one another through use of the ExecuteProcess function.
Passing a zero value turns off the output of these messages, passing a non-zero value enables the output of more verbose messages. By default this flag is off.

Syntax:
SetChoreVerboseMessages(Flag);

Arguments:

Argument	Description
Flag	Set to a non-zero value to enable more verbose messaging. Set to zero (default) to turn off verbose messaging.

Read this aritcle completely...

Partitions in Essbase
Partitioning is licensed separately from Analytic Services.
The Partitioning option must be licensed for every server that contains a database partition. A partition is a piece of a database that is shared with another database.

There are three types of partitions available.

1. Replicated
2. Transparent and
3. Linked.

A replicated partition is a copy of partition of the data source that is stored in the data target.
A transparent partition allow 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 another application, in another Analytic Services database, or on another analytic server.

A linked partition sends users from a cell in one database to a cell in another database. Linked partitions give users a different perspective on the data.

Steps to create a replicated partitions:

• Take any existing database. Ex:sales1(source database)
• Create another application salepart and create a database under that application.
• Open sales1 outline and select 'save as'. Click Analytical server tab and select sale part application, select the database sale part and replace the file. Now salepart and sales1 outlines are same.
• In this example we are going to create a partition to replicate only year 2007 dat into salepart cube.
• Right click on partition under sales1 or salepart. By default the replicated partition type is Replicated. Click connection tab. Select source as sales1,select the server. Select salepart as target and select the server.Click on areas tab. Select "show all count" check box.Click on "Double click here" under source and select the appropriate dimension. In this example select year 2007. Under target also select year 2007.
• Click validate. Replication partition is created.
• Right click on the target or source and click on replicate data from the source

Read this aritcle completely...

A linked reporting object is an object associated with a specific data cell in an Analytic Services database.

Linked reporting object can enhance data analysis capabilities
by providing additional information on a data point. LRO can
be created by Excel add-in, Hyperion analyzer. Where as LRO can
be edited in EAS console,Excel add-in and Hyperion Analyzer.

A linked reporting object can be
->A paragraph of descriptive text (a 'cell note")
->A seperate file that contains text, audio, video,or graphics.
->A Uniform Resource Locator (URL) for a web site.
-> Alink to data in another Analytic Services database.

Read this aritcle completely...

Attribute Manipulation TurboIntegrator Functions

These functions facilitate the manipulation of attributes.

AttrDelete:
This is a TM1 TurboIntegrator function, valid only in TurboIntegrator processes.
This function deletes an element attribute from the TM1 database.

Syntax:
AttrDelete(DimName, AttrName);

Arguments:

Argument	Description
DimName	The dimension for which you want to delete an element attribute.
AttrName	The name of the attribute you want to delete.

Example:
AttrDelete('Model', 'InteriorColor');
This example deletes the InteriorColor element attribute for the Model dimension.

AttrInsert:
This is a TM1 TurboIntegrator function, valid only in TurboIntegrator processes.
This function creates a new element attribute for a dimension. The function can create a string, numeric, or alias attribute.

Syntax:
AttrInsert(DimName, PrevAttr, AttrName, Type);

Arguments:

Argument	Description
DimName	The dimension for which you want to create an element attribute.
PrevAttr	The attribute that precedes the attribute you are creating.
AttrName	The name you want to assign to the new attribute.
Type	The type of attribute. There are three possible values for the Type argument: N - Creates a numeric attribute. S - Creates a string attribute. A - Creates an alias attribute.

Example:
AttrInsert('Model', 'Transmission', 'InteriorColor', 'S');
This example creates the InteriorColor string attribute for the Model dimension. This attribute is inserted after the Transmission attribute.

AttrPutN:
This is a TM1 TurboIntegrator function, valid only in TurboIntegrator processes.
This function assigns a value to a numeric element attribute.

Syntax:
AttrPutN(Value, DimName, ElName, AttrName);

Arguments:

Argument	Description
Value	The value you want to assign to an element attribute.
DimName	The parent dimension of the element for which you want to assign an attribute value.
ElName	The element for which you want to assign an attribute value.
AttrName	The attribute whose value you want to assign.

Example:
AttrPutN(2257993, 'Model', ' S Series 1.8L Sedan ', 'ProdCode');
This example assigns the value 2257993 to the ProdCode attribute of the S Series 1.8L Sedan in the Model dimension.

AttrPutS:
This is a TM1 TurboIntegrator function, valid only in TurboIntegrator processes.
This function assigns a value to a numeric element attribute.

Syntax:
AttrPutS(Value, DimName, ElName, AttrName);

Arguments:

Argument	Description
Value	The value you want to assign to an element attribute.
DimName	The parent dimension of the element for which you want to assign an attribute value.
ElName	The element for which you want to assign an attribute value.
AttrName	The attribute whose value you want to assign.

Example:
AttrPutS('Beige', 'Model', 'S Series 1.8L Sedan', 'InteriorColor');
This example assigns the string Beige to the InteriorColor attribute of the S Series 1.8L Sedan in the Model dimension.

Read this aritcle completely...

MDX is a language-based data analysis mechanism to Analytic Services

databases.

MDX exhibits all of the following characteristics:

->Provides advanced data extraction capability.
->Provides advanced reporting capability.
->Includes functions for identifying and manipulatig
very specific subsets of data.

Is a data-manipulating language, complementing MaxL DDL

(the data-definition language for Essbase)

Every query using the SELECT statement has the following basic format.
Items in [brackets] are optional.

[with_section]
SELECT [axis_specification
[,axis_specification..]]
[FROM[cube_specification]]
[WHERE[slicer_specification]]

A tuple is a collection of mkember(s) with the restriction that
no two members can be from the same dimension. For example, (actual,
Sales) is a tuple. (actual,Budget0 is not a tuple, as both members are
from the same dimension.

A tuple represents a single data cell of all dimensions are represented.
For example, this tuple from sample basic is a single data value:

([qtr1],[sales],[cola],[florida],[Actual])

Set specification

A set is a collection of tuples. In each tuple of the set,
members must represent the same dimension as do the members of
other tuples of the set. additionally, the dimensions must be
represented in the same order.

Select
Filter([product].levels(0).members,
[product].CurrentMember.Caffeinated and
[product].CurrentMember.[Pkg Type]="Can")
Dimension Properties
[caffeinated],[Pkg Type] or columns

Read this aritcle completely...

Using Maxl script we can create substitution variable.

Script to create Substitution variable

*****************************************************************

spool on to 'C:\\batch\subvar.log';
login 'admin' 'password' on 'sales';

alter database 'sales2'.'sales2' drop variable curmon;
alter database 'sales2'.sales2' add variable 'curmon' 'jan2009';
logout;
exit;

*****************************************************************

If the variable is not there it just giving the error and rest of the

part will get executed. Save the file as subvar.mxl and create a batch file

and edit this.

ESSMCH C:\\batch\subvar.mxl '"jan2009"' '"june2009"'

Read this aritcle completely...

In real time export all data is not recommended.Export Level0
data is recommended, because calculation takes less time than export.

Script to export data

******************************************************************

spool on to 'C:\\BATCH\Prodlog.lpg';
login 'admin' 'password' on 'sales';

//alter database 'sales2'.'sales2' begin archive to file 'C;\\batch\arch1.txt';

Export database 'sales2'.'sales2' level0 data to data_file 'C:\\batch\exp.txt';

//alter database 'sales2'.'sales2' end archive;
logout;
exit;
******************************************************************

Read this aritcle completely...

MaxL is a flexible way to automate Analytic Services administration
and maintenance tasks. The MaxL data definition language is an interface for making
administrative requests to Analytic Services using statements. You can Write MaxL
scripts with variables to make them customizable and re-usable.

Overview of Statements

MaxL statements always begin with a verb, such as create or alter. Verbs
indicate what type of operation you want to perform.

Follow the below steps to build dimensions:

Before working on MaxL commands, make sure the source file is ready and rules
file is working. Test the rules file manually.
Open a notepad and type the below MaxL commands, save the file with .msh
extention.


***********************************************************************

spool on to 'c:\\batch\prodlog.log';
login selfbitech on sree;

import database sales2.sales2 dimensions from data_file
'c:\\batch\prod.txt' using server Rules_file prod on error write to
'c:\\batch\proderr.err';

logout;
exit;

************************************************************************

Open another notepad and write the below commands. Save the below file with .bat
extension if it is windows and save the file with .sh extension if the file is unix.

*************************************************************************
ESSMSH c:\\BATCH\PROD.msh
*************************************************************************

MAXL commands can also be used to create the application, database, users etc.

Steps to use this script

->Create Source file
->Create rules file.
->Manually update and test the outline.
->write a maxl script.
->Test the Maxl manually.
->open another note pad and write ESSMSH MAXL filename
->Run batch file manually.
->Schedule the batch file.
Executing calculation

Execute calculation default on 'sales2'.'sales2';
If any calculation script is there like calc1
Execute calculation 'sales2'.'sales2'.calc1;

Read this aritcle completely...