Calculated measures and members

eazyBI.com eazyBI for Confluence eazyBI for Jira Private eazyBI

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

Calculated members in 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 expression [2].

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.

User-defined calculated measures

If you would like to define a new calculated measure, then expand Measures → User defined section and choose to define a new calculated measure (for a shared measure) or report-specific measure (for just a specific report)

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 creating a new calculated measure 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 when used in calculated measure 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.

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 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 a shortcut ⌘ / or Ctrl / 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 issues, the calculation will be executed over all issues imported into the account, ignoring any report context. This could significantly slow down the drill-through issues output!

-- 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 
Add the annotation if you use the Total option of report rows. This annotation forces the Total row and aggregates results as a sum of individual row values.

-- 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.