Advanced Transformations

The DataStudio allows you to perform some additional complex calculation on data fetched from Logilica's semantic data layer. These advanced transformations are performed on data once they are fetched from the semantic layer.

Transformations are applied to data using SQL, where the data retrieved from the semantic layer is treated as a single table.

Applying Advanced Transformations

To apply an advanced transformation, select the "Show Advanced Transformations" button at the bottom of Chart Options within the DataStudio. Pressing this will open a code editor where you can input SQL.

To begin, construct your query as normal in the DataStudio, selecting all dimensions and measures you like to use in your advanced transformation. Once you have data available in the Query Results section, you can apply your transformation.

An example of a simple transformation is the following:

select
[JiraSprint.completedStoryPoints]+[JiraSprint.leftoverStoryPoints] as [Story Point Sum]
from ?

The above transformation adds together the two measures Completed Story Points and Left Over Story Points. Note the ? in the from section of the query refers to the data that has been fetched from the semantic data layer.

Data fields are referenced by the convention <cube-name>.<field-name>. They are wrapped in [...] to escape any special characters. Please refer to the complete list of available cubes and measures. The Advanced Transformations editor can auto-complete these fields based on the original DataStudio query.

Any new fields you create should be named using the as [<name>] syntax, so that the newly created field can be referenced within the DataStudio. Use the [...] to escape special characters.

While the Advanced Transformations editor does not cover full SQL, common features are supported including: JOIN, VIEW, GROUP BY, UNION, ANY, ALL, IN, ROLLUP(), CUBE(), GROUPING SETS(), CROSS APPLY, OUTER APPLY and sub-queries.

Supported Aggregators Functions

The following aggregator functions are supported:

• SUM()

• AVG()

• COUNT()

• MAX()

• MIN()

• FIRST()

• LAST()

Data Types of Created Fields

By default, any new field introduced by one of your queries will be considered to be of type number. In order to specify a different type, one of the following suffixes must append to the field name:

• _STRING - for fields that are strings

• _TIMESTAMP - for fields that represent time

• _DURATION - for fields that represent duration (e.g. how long a ticket takes to complete)

Using these suffixes ensures that the DataStudio can correctly render the data fields.