XAE Product Introduction

The GoodData platform combines the power of highly customizable and understandable dashboards based on our powerful multi-dimensional analytical query language.

Contents:

GoodData Extensible Analytics Engine

The Extensible Analytics Engine leverages our proprietary MAQL/XAE Language, which offers context-dependent metric dimensionalities, allowing users to apply metrics in many contexts without changing the metric’s definition. You can even nest one metric in another to build more complex KPIs.

The advantage of the GoodData platform becomes apparent when you begin manipulating data without impacting the underlying physical data model. GoodData logical data models abstract from the complexities of a physical data model, allowing users to focus on business use cases, rather than how a project’s data is stored.

With the new architecture and all-new features, XAE provides more reporting value, tackling more complex use cases and improving existing ones. In its most advanced form including better query optimization and execution and more.

A number of XAEs new offerings are owed to input from our business users. Exception reporting (comfortable NULL handling), advanced ranking (including percentage ranks, ranking within group, etc.), and complex conditional reporting (IF-ELSE and CASE) are all enabled by the new XAE. We’re also introducing new running total aggregation functions that return cumulative aggregations of a product fact and can be introduced in tables, charts, or nested within other metrics.

The XAE is designed to help streamline the way organizations analyze and make data driven decisions on a daily basis. 

Absolute Value, Signum and Square Root

ABS( )

Description
Returns the absolute value of a number. Negative values are returned positive. Positive values are unchanged.

Syntax
SELECT ABS(number)
SELECT ABS(metric)

Examples
SELECT ABS(-5)
…returns the value “5”

SELECT ABS(10)
…returns the value “10”

SELECT ABS((Total_Amount)-(SELECT Total_Amount WITHOUT PF))

SIGN( )

Description
The signum function returns the sign of a number. If the number is positive, sign returns 1. If the number is negative, sign returns -1. If the number is zero, sign returns 0.

Syntax
SELECT SIGN(number)
SELECT SIGN(metric)

Examples
SELECT SIGN(-3.87)
…returns the value -1

SELECT SIGN(33)
…returns the value 1

SELECT SIGN(0)
…returns the value 0

SQRT( )

Description
The square root function returns the square root of a number or metric input. Negative or complex inputs result in a NULL output.

Syntax
SELECT SQRT(number)
SELECT SQRT(metric)

Examples
SELECT SQRT(25)
…returns the value “5”

SELECT SQRT(33.6)
…returns the value “5.796550698”

SELECT SQRT(SELECT SUM(Sales))
…returns the sqrt of the Total Sales metric

Running Totals

Previously, running totals could be added to GoodData tables using the secondary-click contextual menu. This created a new column to the right of the original that automatically totaled the selected column’s cells. However, the actual metric used to compute the column’s values was inaccessible.

Now you can use the RUNSUM, RUNAVG, RUNMIN, and RUNMAX functions in MAQL to create running total metrics that can be reused in other metrics – where they can be manipulated with other MAQL functions – or added to any number of other reports.

RUNSUM / RUNAVG / RUNMIN / RUNMAX

Description
Takes the running total of a project fact. In a table broken down by a date attribute, the running total value on any given day would be calculated by aggregating values from all prior days along with that of the current day.

Syntax
RUNSUM(fact)
RUNAVG(fact)
RUNMIN(fact)
RUNMAX(fact)
RUNSUM(metric)
RUNAVG(metric)
RUNMIN(metric)
RUNMAX(metric)

Examples
SELECT RUNSUM(Sales)

SELECT RUNAVG(Leads) WHERE Year={ this }
SELECT RUNMIN(Probability) WHERE Amount > 500000
SELECT RUNMAX(Opportunities) BY Quarter/Year
SELECT RUNSUM(Won Opportunities) WITHOUT PARENT FILTER

The current limitation is that the dimensionality can be only (and precisely) one date/time attribute.

Conditional Statements

IF THEN ELSE

Description
IF THEN ELSE statements return one of two possible values, or perform one of two possible computations, depending on whether some condition is met. The condition that needs to be met follows the keyword IF and can be constructed using any of the filtering keywords or relational operators (IN, NOT IN, BETWEEN, NOT BETWEEN, =, < , <=, >, >=, <>).

The first possible outcome follows the keyword THEN, and the second possible outcome follows the keyword ELSE. The outcomes that are returned can be numerical values or arithmetic operations. All IF THEN ELSE statements conclude with the END keyword.

If there are three or more conditions (in addition to an else state), use the CASE statement described below.

Syntax 

SELECT IF … THEN … ELSE … END
SELECT IF condition THEN number ELSE number END
SELECT IF condition THEN arithmetic_operation ELSE arithmetic_operation END

Examples
SELECT IF SUM(Amount) >= AVG(Amount) THEN 10 ELSE 0 END
SELECT IF SUM(Duration) - AVG(Duration) > 2000  THEN 0 ELSE 1 END
SELECT IF AVG(Probability) > 0.5 THEN SUM(Amount) * 10 ELSE SUM(Amount) / 10 END

CASE

Description
CASE is used for complex conditional statements that contain three or more conditions. Following the CASE keyword, conditions and outcomes follow the WHEN and THEN, respectively. If none of the WHEN conditions are met, the outcome following ELSE is returned. Outcomes can be numerical values or arithmetic operations. All CASE statements conclude with the END keyword.

Syntax
SELECT CASE WHEN … THEN …, WHEN… THEN… ELSE… END
SELECT CASE WHEN condition1 THEN outcome1, WHEN condition2 THEN outcome2 ELSE outcome3 END

Examples

SELECT CASE WHEN ACTIVITY_TYPE IN(Email,Web_Meeting) THEN 1, 
WHEN ACTIVITY_TYPE IN(Phone_Call,In_Person_Meeting) THEN 2 
ELSE 0 END
SELECT CASE WHEN SUM(Amount) > SUM(Lost) AND SUM(Amount) - SUM(Lost) > 100000 THEN 2, 
WHEN SUM(Amount) > SUM(Lost) AND SUM(Amount) - SUM(Lost) < 100000 THEN 1 
ELSE 0 END

IFNULL

Description
IFNULL allows you to predefine the way GoodData deals with any missing values that are returned by a metric expression (metric, fact aggregation, or arithmetic operation). If a metric expression that is wrapped within an IFNULL statement ever returns a null value, the replacement number specified in the second parameter of the IFNULL function will be inserted in place of null.
IFNULL is especially useful in cases where a sub metric is referred to by another metric’s definition. Wrapping the sub metric in an IFNULL statement keeps the encompassing metric from becoming null due to one of its components returning a null value.

If you use a metric as a replacement value, this report will be not computable due to an improper metric definition.

Syntax
SELECT IFNULL(…,…)
SELECT IFNULL(metric_expression,replacement_value)

Examples
SELECT IFNULL(SUM(Amount), 0)
SELECT IFNULL(SUM(FB_Cost + TW_Cost), 0)
SELECT IFNULL(SUM(Amount) + 100, 1)
SELECT IFNULL((SELECT SUM(Payments)
       WHERE Product IN (Explorer, Educational)), 0)

Ranking Functions

XAE now supports the RANK function in addition to basic TOP(n) and BOTTOM(n) ranking (e.g. TOP(5)sales reps).

In combination with various formulations of WITHIN statements, RANK allows you to rank values within sub-groups associated with a report’s attribute values. This would be useful for ranking the top 5 sales reps within each region, in a report containing sales rep data across all sales regions.

When accompanied by various formulations of WITHIN statements, RANK can be used to carry out the following:

  • dense rankings
  • ranking by percentage
  • cumulative distribution

When RANK is used without an accompanying WITHIN statement (e.g. SELECT RANK(Avg. Won, the function sequentially ranks all metric values across the rows in a report, resulting in an identical output as RANK(...) WITHIN(ALL IN ALL OTHER DIMENSIONS). When a WITHIN statement is appended to a RANK function, it can be used with either of two parameter formulations, resulting in different outputs. These are explored in the table below.

WITHINOutput
WITHIN ( attr, …, ALL attr, … ALL OTHER EXCEPT … )Ranks within sub-groups that are specified by the attributes contained in the WITHIN statement’s parameter.
Ranks within clusters of current dimensionality altered using specified by rules, i.e. ranking within group. Respecting the report dimensionality specified in HOW. Any statement which can be in BY clause may be used here.
WITHIN (CURRENT)Ranks within current dimensionality ignores the report dimensionality from HOW. Every row of the report is handled as a separate group and so the ranking metric will provide only one value, here 1, as a results at all rows. Hardly useful with TOP/BOTTOM ranking.

The full syntax of the RANK function is described in following text.

RANK

Description
The ranking function returns a numerical rank for each of a report’s metric values in ascending [ASC] order (smallest value ranked first) or descending [DESC] order (largest value ranked first). Ascending order is the default, for cases where an order is not explicitly specified.
A key feature of the RANK function is the WITHIN keyword, which allows you to carry out rankings of multiple attribute sub-groups within a single report.

Syntax
SELECT RANK(…) [ASC|DESC] [WITHIN(…)]

SELECT RANK(metric)
SELECT RANK(metric) WITHIN( CURRENT )
SELECT RANK(metric) WITHIN( Attribute_1, Attribute_2, [...] )
SELECT RANK(metric)WITHIN( any BY statement )

Examples
SELECT RANK(Avg. Won)
SELECT RANK(Amount) ASC WITHIN(Year(Closed))
SELECT RANK(Amount[SUM])WITHIN(CURRENT)
SELECT RANK(Amount[SUM])WITHIN(ALL OTHER)

As with RANK, TOP and BOTTOM ranking functions can be used without a WITHIN statement for ranking that disregards sub-groups within a report. Appending formulations of WITHIN to TOP and BOTTOM allows for rankings that include only the top or bottom n values within each sub-group in the final report output.

WHERE TOP|BOTTOM (n) IN

Description
This TOP and BOTTOM ranks are a specific version of ranking functions. It will rank and filter you the result of a specific metric based on TOP or BOTTOM key words. Its syntax requires a SELECT keyword preceding the metric that follows IN. It interprets the ranking metric as a sub-report.

Now supports percent parameters in place of n.

Syntax
SELECT … WHERE TOP(…) IN … WITHIN …
SELECT metric1 WHERE TOP(n) IN (SELECT Metric2 BY A1, A2...) WITHIN (...)

Examples
SELECT Amount WHERE TOP(5) IN (SELECT Avg. Won BY Product)

But we also support the OF keyword instead of IN. The syntax then becomes easier and also the usability increases. The difference is also how the metrics are interpreted in the backend infrastructure. The OF allows ranking by more than one metric and interprets the metric as a submetric, in contrast to IN which requires a SELECT and interprets the ranking metric as a subreport.

WHERE TOP|BOTTOM (n) OF

Description
This TOP and BOTTOM ranks are a specific version of ranking functions. It will rank and filter you the result of a specific metric based on TOP or BOTTOM key words. Allows ranking of more than one metric and interprets “the metric” as a sub-metric.

Syntax
SELECT … WHERE TOP(…) OF … [WITHIN …]
SELECT metric1 WHERE [TOP|BOTTOM](n) OF ( M1, ... )
SELECT metric1 WHERE [TOP|BOTTOM](n) OF ( M1, ... ) WITHIN ( ... )

Examples
SELECT Amount WHERE TOP(5) OF (Avg. Won)
SELECT Won WHERE TOP(10) OF (Won) WITHIN(Region)

SELECT Amount WHERE BOTTOM(5) OF (Lost) 
                            WITHIN(Region, Year (closed))

WHERE TOP|BOTTOM (n%) OF

Description
The percentage rankings are similar to all other TOP and BOTTOM but using the % sing in the specification of the ranking TOP|BOTTOM parameter n.

Syntax
SELECT … WHERE [TOP|BOTTOM](n%)
SELECT metric1 WHERE [TOP|BOTTOM](n%)

Examples
SELECT Amount WHERE TOP(5%) OF (Amount) AND Product=Explorer
SELECT RANK(Top 5% parent) WHERE TOP(50%) OF (Top 5% parent)