Analytic functions

Overview

Functions are calculations that use input fields to provide data to the function; the function's result (the value it returns) may appear in one or more cells in an analysis or other resource, it can also be used in a custom formula, a calculated member or other resource, or it can be nested in (become an input to) another function.

Various items in your cube (such as measures, levels, and members) can be used to assign values to input fields. The function's result will be of a particular data type, depending on the function. It may be a value, set, member, or a boolean expression.

• Use functions to calculate values and perform operations based on selected dimensions members and measures (e.g. ordering), fetch values based on selected criteria and much more.
• Configure functions via a dialog box that is accessed after the function is added to an analysis or other resource.Configure functions via a dialog box that is accessed after the function is added to an analysis or other resource.

Input fields

Input fields (also referred to as arguments, inputs, or parameters) are items used within a function to provide the information required to calculate a result. Some input fields are optional but are always identified. A "top 5" function needs to be given criteria (e.g. sales) so it can calculate the top 5 sales.

In Data Hub, each input field in the function dialog box has the following interface elements:

• A placeholder or another area where information is directly added to determine the function calculation.
• A short description of the purpose of the information assigned to the input field (i.e. how the function uses the assigned information to complete its calculation).
• Placeholder-type input fields only. An icon representing the data type of the input field.

Two main input actions for functions:

• Drag cube elements into placeholder fields.
• Direct or text input.

Example

In the following example, the Parallel Member function is shown. The function uses placeholder fields and a number input field. Items from the Dimension Tree are dragged into the placeholder fields, and free text is typed in the number input.

• The Lag input field (1) takes a number, which is entered directly in the corresponding input.
• The Member placeholder field (2) takes a member data type.
• The Ascendant level placeholder field (3) takes a level data type.
• Using the information assigned via the input fields, the function, as defined below, will return the parallel member for one calendar year before the Apr 2012 member, that is, the Apr 2011 member.

Input fields data types

Functions use input fields to return a result. Each input field and the function's result have a data type. Input field data types control cube elements that are used to assign values to a function's calculation. The data type of a function result controls how it displays in Data Hub and what can be done with it.

Several data types are available; however, three common data types for function input fields and results are:

• Value - A numerical or text value (e.g. a measure).
• Set - A collection of individual members (e.g. a level).
• Member - An individual member from a level or attribute.

Cube elements

It is necessary to provide a cube element (or enter a direct input) of the correct data type for each input field.

Each cube element has a data type. For example: levels are sets; hierarchies, attributes, and dimensions are members; and measures, calculated members, and member properties are values.

Data Hub will automatically convert an argument from one type to another,usually. For example, a member dragged to a set placeholder is converted to a set with one member.

note

• Use the function tool tips to quickly check the data types of a function's input fields and result.
• Some functions require data types that do not have icons. These data types are shown as text in square brackets.

Time context members input fields

Relative time functions require a time context. Without an explicit time context, relative time functions use the current date as the default context.

Sources of the relative time context:

• A parameter within the function.
• The opposite axis (e.g. for Rows, the opposite axis would be Columns).
• A single member filter.
• The End member of a Date range slicer.

Required and optional fields

• Required - Most functions have one or more input fields that must be assigned for the function to work.
• Optional - Some functions have optional input fields, which may be assigned a value if desired but need not be filled in. If an optional field is not filled in, a default behavior occurs. The default behavior depends on the function and is explained in the function's dialog box or its help listing. Every optional input field is identified with the text "(optional)" after the field name.

tip

If you are using the Months to Current function, you can use the optional Count input field to indicate how many months to include.

A value of 6 directs the function to return a set of the last six months including the current month. If the Count input field is left blank, the Months to Current function returns a set containing all the months in the cube, up to and including the current month.

note

Use the enhanced function tool tips to quickly check if a function has any optional input fields.

Function result data types

The execution of the function will return a result. In Data Hub, each function's result has a data type.

System function results use one of the following five data types:

• Number - The function's result is a numeric value, either an integer or a decimal.
• Set - The function's result is a set of members.
• Member - The function's result is a single member.
• Boolean - The function's result is either TRUE or FALSE.
• Tuple - The function's result is a tuple (a cell or section in the cube).

Some functions, particularly custom functions, may return other data types.

note

Use the symbols on functions, as well as the function tool tips, to quickly check the return types of functions.

Return types help you determine how a function can be used:

• A function with a Boolean return type might be used as a true or false condition in other functions or calculated members.
• A function with a Member or Set return type can be dragged directly onto analysis placeholders to display the member or set in the analysis.
• A function with a Numeric return type might be used within a calculated member or custom formula, or it can be dragged directly onto an analysis placeholder to display its value in the analysis.

For example, if you are using the Sum function, the result data type is a number. In other words, the Sum function returns a single value which is the sum of all of the items in the specified set. The example below shows a Sum function that returns a number representing the total sales for the set of all customers.

Using analytic functions

Add analytic functions

The four techniques for adding functions are outlined below.

• Right-click on a placeholder - When you right-click on a blank part of a placeholder, the context menu lists all the functions that can be added to that placeholder.
• Right-click an item in a placeholder - When you right-click a placeholder item, the context menu allows you to apply a function to an existing placeholder item. This is also known as "wrapping" the item in a function.
• Right-click and drag a placeholder item - When you right-click an existing placeholder item and drag it to an empty area of a placeholder, a menu appears that allows you to make a copy of the existing placeholder item and apply a function to the copy.
• Drag on a function - When you drag a function directly from the Dimension Tree, you add a item containing the function directly to the placeholder.

Nesting analytic functions

Nested functions are functions within functions (known as nesting) to help with simplifying complex calculations and logic that may require several steps.

This most outer function is referred to as the nesting function to distinguish it from the inner or nested function. Nested functions can be several levels deep.

The calculation order that is used when nesting various functions may influence the final functions' result. This is described in Calculation order.

Three different methods exist for nesting functions:

• Method 1 - Creating a nested function within a new function

  The traditional method for creating a nested function is to create a nesting function that will hold the nested function, and then create the nested function on a placeholder within the nesting function's dialog box. This method creates a single, complex calculation, and is recommended if the nested function is only going to be used once or if only the result of the nesting function (and not the nested function) is to be displayed in the analysis.

  note

  If you plan to nest the same function within more than one nesting function on the same analysis, or you require the result of the nested function to be displayed in addition to the result of the nesting function that uses it, it is more efficient to create the nested function as a separate, standalone function on a placeholder of the analysis and reference the nested function in one or more nesting functions instead.

• Method 2: Creating a nested function by referencing an existing function item

  If a standalone version of the function to be nested is required in the analysis, regardless of its use in a nested function, you can create a "referenced" nested function. When this method is used, the standalone version of the function to be nested is configured first. Then, a reference to this function is nested within another function.

  note

  This method is recommended if the nested function is going to be used inside multiple functions, or if you think the nested function's settings may be changed at some point. Since the nested function is referenced in the nesting function, changing the "standalone" version of the function automatically updates all references to the function, and allows you to avoid having to manually change each nesting function.

• Method 3: Creating a nested function by right-click-dragging an existing function item

  This method achieves the same result as Method 2, described above, in that you create a "referenced" nested function. However, this method can be considered a shortcut, since it allows for quicker creation of a nested function (with only a few mouse clicks). This technique is sometimes known as "wrapping" an item in a function.

  note

  This method is recommended if the nested function is going to be used inside multiple functions, or if you think the nested function's settings may be changed at some point. Since the nested function is referenced in the nesting function, changing the "standalone" version of the function automatically updates all references to the function, and allows you to avoid having to manually change each nesting function.

Unwrapping analytic functions

You can "unwrap" a configured function, replacing it with the items on the function's default field placeholder.

1. Locate the function you want to unwrap.
2. Right-click the function's placeholder item.
3. Select Unwrap default field from the menu that appears, and notice that the function's default field (Set) is listed adjacent to the option.

The function is unwrapped, and the placeholder items from its default field is now standalone placeholder items on the corresponding resource placeholder.

Drill-through override sets

Creating a detail report as a result of the drill through action on individual cells of an Analysis Report produces a list of the constituent records used to create the cell values.

The Drill through override set placeholder is used to define a custom filter to be used when a Drill through is performed, instead of an automatically-generated set

There are circumstances where a Drill through cannot be produced automatically by Data Hub, because the constituent records cannot be unambiguously determined. In these situations, when a user attempts to create a Drill through on a calculated cell, Data Hub will return the following error message:

When this error occurs, the Drill through override set can be used to define a custom filter for Drill through Resources created for this function.

Add a drill-through override set to a function

1. Within the function, click Options to access the Drillthrough override set placeholder.
2. Add elements to be used as filters for Drill through Resources from the Dimension Tree.
3. Once the Drillthrough override set has been defined, click Apply to add the Drillthrough override to the function.

How Data Hub handles functions when a drill-through is performed

This is an example analysis of the TransactionHistory measure group

The Custom Formula being applied is Quarter 1 + Quarter 2 + Quarter 3 of the financial year 2013/2014.

When a detail report is created from the Custom Formula field, the members in the Custom Formula are added to the Filters placeholder as a set, similar to the example below.

If operators other than addition are used in a function, drilling through is disabled.

Where Data Hub requires a drill-through override set

1. Within the function, click Options to access the Drillthrough override set placeholder.
2. Add elements to be used as filters for Drill through Resources from the Dimension Tree.
3. Once the Drillthrough override set has been defined, click Apply to add the Drillthrough override to the function.

tip

If you want to customize the columns displayed in a drill-through, create a custom drill-through report and add it to the analysis's Drill-through targets placeholder. The set is used to filter the rows that appear in the drill-through report (in conjunction with any filtering on the Rows, Columns, Filters, and Slicers placeholders on the resource). The columns in the Drill through are generated automatically based on the measure groups used in the source analysis.

note

• The Drill-through override set placeholder does not accept: measures, functions that return a number, or calculated members.
• The Drill-through override set placeholder does not accept the Current Member function applied to a hierarchy. For date hierarchies, a similar result may be obtained by using the Context Period function.

Calculation order

The order that calculations are performed in an OLAP cube can lead to incorrect totals and other problems. These issues can be addressed by manually specifying the calculation order. Fortunately, most calculations do not require any changes to the calculation order.

The classic symptom of an incorrect calculation order in an analysis is a displayed column or row total of a custom formula or other calculation that doesn't agree with the correct total of the individual values. Similar issues may occur with averages and other aggregations.

The detail calculations themselves are correct – they just don't add up.

Problems can be fixed by explicitly specifying the calculation order for the detail calculations.

In Data Hub, calculation precedence can be made explicit in the definition of a function, custom formula or calculated member, using the Calculation Order combo box.

The following possibilities are available as drop-down values:

• Pre-Aggregation - Calculation takes place before members are aggregated (e.g. a currency conversion involving a multiplication).
• Aggregation - Calculation itself performs aggregation and is therefore a reference point for the other pre- and post-aggregation calculations. Simple formulas using non-precedence operators can use this type. For example, values might be aggregated using a Sum template, or members might be added together in a custom formula (e.g. X + Y + Z).
• Post-Aggregation - Calculation takes place after members have been aggregated (e.g. ratios involving division).
• Blank - If the drop-down box is blank (the default), the behavior is the same as the Post-Aggregation setting, above. This means that calculations involving division will total or average correctly; calculations involving multiplication may need to be changed to Pre-Aggregation for totaling or averaging to work correctly. To return the drop-down box to the blank setting, simply select the text or number within it and press delete or backspace.

note

If finer control of calculation order is needed, a numbered calculation order value can be manually entered in the combo box. Calculations are executed in ascending order as specified by the calculation order assigned to each one. Note that the three pre-defined orders are:

• 1000 = Pre-Aggregation
• 2000 = Aggregation
• 3000 = Post-Aggregation

For example, a calculation assigned a calculation order value of 1500 will be executed after calculations assigned to Pre-Aggregation (1000) and before calculations assigned to Aggregation (2000).

Complex calculations may sometimes result in anomalous results due to calculation order issues. In these cases, contact support for assistance.

Helpful links

How to use analytics function