Calculated measures and members

What is a calculated member and when to use calculated members

This tutorial explains how to define new calculated members and measures using MDX expressions (or formulas). If those words do not make sense to you, we advise getting to know the main eazyBI concepts and learn how to create reports, and then come back to this section again.

What is MDX?

MultiDimensional eXpressions (MDX) is a query language for querying and manipulating the multidimensional data stored in OLAP (online analytical processing) data cubes. MDX syntax is similar to SQL syntax or spreadsheet formulas. Below is an example of a very simple MDX query:

SELECT
   { [Measures].[Salesfact] } ON COLUMNS,
   { [Date].[2004], [Date].[2005] } ON ROWS
FROM Sales

MDX was initially developed by Microsoft for its SQL Server analysis products, though it has since become an independent standard. It's been around long enough now that there are many MDX tutorials and references, most notably:

• http://msdn.microsoft.com/en-us/library/ms145506.aspx
• http://en.wikipedia.org/wiki/Multidimensional_Expressions

MDX implementations vary, and many MDX documentation resources are specific to certain niche products or standards.

MDX Used in eazyBI

Calculated members and calculated measures can be used to add business logic to a report and a data cube. The most common use cases for calculated members and measures are to group particular items of interest, dynamically change time according to the current date or selected period, and, most importantly, derive new measurements from existing data. Calculated members and measures are defined using the MDX (multidimensional expressions) query language which is the main query language implemented by Mondrian (https://mondrian.pentaho.com/documentation/mdx.php)

What you should know

Mastering how to write MDX expressions is initially quite difficult. Don't worry; you will get along quite well if you follow some ground rules. MDX is a language and to use it for calculations it is enough to understand some base words (data types and functions) and how to use a dictionary (list of available functions).

Functions

A function is a mathematical or logical action that transforms data and then returns a modified version of data. In other words, some data goes IN, then the function transforms them, and other data comes OUT. You can recognize a function by the red color of the text and symbols defining the start and end of the function, brackets (), curly brackets {}, dot . For example, the function DateDiffDays() calculates how many days are between two given dates. In this function goes IN two dates and comes OUT one number representing a count of days.

Data types

A data type is a particular kind of data item defined by the values it can take. In MDX you can use the following data types:

Ground rules

• Calculations that refer to members from several dimensions or numerical measures should be created as new user-defined measures in Measures. We call them calculated measures.
• A measure binds data together and allows you to represent data from different points of view (Dimensions). Therefore, each calculated measure should contain at least one already existing measure or fixed value. Measure is a treasure!
• When creating a calculated member in a dimension, use only members from one dimension where you create them. Don't mix dimensions in calculated members! We recommend using an aggregate function for calculated members and do not perform arithmetical operations, like, multiplying, dividing, and subtracting in those calculations.
• Use tuples when possible. 
• Start simple and build calculated members and measures step by step adding complexity gradually.

Naming conventions

Each dimension [1] contains one or more hierarchies with one or more hierarchy levels [2], and each hierarchy level contains dimension members. When creating new calculated measures and members, we usually refer to the specific members with which we want the code to work. So we should show the full path on how to find that particular member. One way to identify a member is to start with the dimension's name and work downwards, specifying the members at each level in the hierarchy [3] until we reach the required member.

For example, the Time dimension contains three hierarchies: default, Weekly, and Fiscal. Going further down the default hierarchy it has five levels: All Times (default level), year, quarter, month, day [2]. To represent a specific month of the year, the path to May 2019 would look like this:

[Time].[2019].[Q2 2019].[May 2019]

In MDX query language, all names of measures, dimensions, dimension hierarchies, hierarchy levels, and dimension members are enclosed in square brackets []. To reference a dimension, you just enclose its name in square brackets, e.g., [Customers], [Measures], [Time]. To reference a particular member, enclose each name of the path in square brackets, e.g., [Customers].[USA].[CA],  [Time.Weekly].[2019].[W31, Jul 29 2019] or [Measures].[Store Sales].

• If a dimension has just one hierarchy, it automatically is also the primary hierarchy. And you can refer to the primary hierarchy in the same way as a dimension by mentioning just a dimension name, e.g. [Customers] or [Measures].
• If a dimension has two or more hierarchies (like Time dimension), then you should refer dimension name and hierarchy name. For example, a reference to the Time dimension Weekly hierarchy is [Time.Weekly]. In Time dimension primary hierarchy (year, quarter, month day) does not have a specific name, and you can refer to it by mentioning just a dimension name [Time].

Each dimension typically will have a default All member which can be used to get totals of measures by this dimension. If the Customers dimension has a default All member named All Customers, then you can reference it by its name [Customers].[All Customers] or [Customers].CurrentHierarchy.DefaultMember to get the same result. In Measures, all measures are at the top level, and you can reference them with as [Measures].[Store Sales], [Measures].[Store Cost] etc.

How to define a new calculated member

User defined measures

If you would like to define a new shared calculated measure then expand Measures → User defined section and choose to define new calculated measure.

Calculated member in other dimensions

If you would like to define a new calculated member then expand the corresponding dimension (e.g. Time) → Select the individual members section and choose to define new calculated member.

If you would like to edit (or delete) an existing calculated measure or member, click on the edit link. If you do not have the right to edit calculated members in the current account, then you will see just the show link which will allow you to open the calculation of an existing calculated member and see the formula. eazyBI will open a separate screen Calculated member formula for showing, editing or defining a calculated measure or member.

Each calculated member should have a name [1] (unique within the dimension and without any special characters like "&" etc.) and a calculation formula [2] (the rest of the tutorial will teach how to write calculation formulas). In addition, you can specify how the calculated member value should be formatted (e.g. as an integer, decimal, date, or using default formatting). From the right sidebar, you can quickly select other members, dimensions, operators or functions to insert them in the calculation formula.

After defining a calculated member formula press Update to save it, Delete to delete an existing calculated member, or Cancel to discard any changes. A corresponding error message will be displayed if a calculated member formula is invalid.

Do not modify or delete pre-defined calculated members that were created by source application import – their original definition will be recreated the next time when you will perform the source application import. If you need to create a modified version of an existing pre-defined calculated member then copy its calculation formula and create a new calculated member with a different name and modify the copied calculation formula for the new calculated member.

Report specific measures

It is possible to create the user-defined measures that will not be visible in the section of all user-defined measures but are present in a specific report only. This option is useful if the measure is not intended for use in any other context than in a specific report. The report specific measures behave the same way as any other user-defined measure, the difference is in the visibility of such measures. See user roles that can create new report-specific calculated measures.

The report specific measures are located in a separate section of the Measures dimension. This section is empty once the user starts designing a new report.

The measure edit dialogue shows the information in how many reports and how many other measures this measure is referenced. There is an option to convert a common (shared) user-defined measure to a report specific measure if the user-defined measure is not used in any report. Once you convert the measure, it is stored together with the report and not visible in the user-defined section.

Member properties

Dimension members have some default properties like .Name and .Key as well as they can have additional custom properties. eazyBI source application import (e.g. from Basecamp, Highrise or Jira) is also importing additional dimension fields from source systems. Note that a property holds information about a particular member and could be anything that describes that member details. 

For example, a User dimension member can be a person (user) and the property of each user can be an address (string), age (number), date of birth (date). In reports, properties are displayed only at a particular user level.

To access and retrieve property values, we recommend using MDX function get(). This function will return property value if a member has such property or an empty result if no property is defined for the current dimension level.

For example, Jira import adds the Created at the property for all imported issues. The following expression returns the Created at property value for the Issue dimension current member:

[Issue].CurrentMember.get('Created at')

To get a list of all available properties and their values for each member, you may use MDX function AllProperties.

Default values

Sometimes you might want to return a default value if some measure or function will return an empty value. In these cases, you can use a function CoalesceEmpty(), for example:

-- returns zero if no value for store sales
CoalesceEmpty([Measures].[Store Sales], 0)

-- returns default text if user has no such property as Email
CoalesceEmpty([User].CurrentMember.get('Email'),"no public email available")

How to work with the formula editor

Function and member library

Formula editor has a library of MDX functions and available measures and members. When you start writing a calculation, eazyBI provides you an autocomplete list of functions or measures and members.

To get a list of available dimensions and Measures, start typing name with square brackets [ (refresh your knowledge on naming conventions). To get a list of available MDX functions, start typing a function name or entre dot . after a member or another function. Formula editor will show you only those MDX function which could be used in the current syntax.

Comments

It is possible to write comments in calculation formulas. Use comments either to describe some non-obvious complex calculations or for during debugging of the calculation formulas. When something is not working as expected, then comment all formula lines and leave uncommented just some parts of the formula that you would like to debug. You may also mark an area of a formula and use shortcut ⌘ / to comment it.

-- one line comment 

expression -- comment until end of line 

/* multi 
  line 
  comment */

Special comments with annotations

You can use annotations to limit or change some report interaction features.

-- annotations.disable_drill_into=true
Do not allow the Drill into action for this calculated member.

-- annotations.disable_drill_across=true 
Do not allow the Drill across action for this calculated member.

-- annotations.disable_drill_through=true 
Do not allow the Drill-through action for this calculated member.

-- annotations.disable_drill=true 
Disable all drill actions for this calculated member.

-- annotations.drill_through_non_empty=false 
During drill through issue the calculation will be executed over all issues imported in the account, ignoring any report context. It could slow down the drill through issues output significantly!

-- annotations.group=...
Put the calculated member in the specified group in the Calculated member's section of the dimension in the report builder.

-- annotations.total=sum 
Force the total calculation as a sum of row calculations when the total for rows is selected from toolbars

-- annotations.copy_as_user_defined=true
Enable the option to edit template measures in the recipient account. Changes made in those measures won't be overwritten during data imports. For more details read about template accounts.

Shortcuts

You can use the following keyboard shortcuts in the formula editor:

• Cmd Return (⌘⏎) — save or update the calculated member.
• Cmd Shift M (⌘⇧M) — maximize/minimize the formula editor dialog.
• Cmd / (⌘/) — comment or uncomment selected lines.

Invalid calculated members

eazyBI does not allow saving calculated measures addressing nonexisting dimensions. The error Formula is not valid: MDX object '[Non existing dimension]' not found in cube 'Issues'  indicates there is no such dimension Non existing dimension. You would like to make sure the dimension is imported and available in the cube where you are creating the reports. Use autocomplete to avoid any typo errors. 

The error Calculated member formula is not valid: Formulas are not valid for <N> calculated members <list of meausres>. Please fix them or delete those members indicates there are some calculated measures with invalid formulas. Open an individual formula to see the exact reason for the error. You can fix the formulas individually by updating them. 

eazyBI might mark measures as invalid if the dimension is removed from a cube with the import. If you need to use those measures, import the dimension back into the account.

On the Server and DC version before 7.0, adding back the dimension will not remove the invalid marker. You can update all formulas at once. From the Analyze tab, create a new empty report. Modify the URL and add /revalidate at the end of the URL. Run the modified URL.

The URL could look like this: [JIRA HOME]/plugins/servlet/eazybi/accounts/[ACCOUNT_ID]/cubes/[cube_name]/revalidate

Training videos on calculated measures and members

We have a collection of Training videos on calculations with MDX that might help you get started. Some videos are designed for beginners and explain the data structure, and how to filter data, while other videos cover more specific methods used in MDX calculations, like tuples and sets. There are also several videos on different use cases.