A Information to MAQL Metrics Reusability

Date:


On this weblog put up, we’ll showcase Multidimensional Analytical Question Language (MAQL) reusability capabilities in additional element. Nonetheless, if you’re not but conversant in the ideas and objective of MAQL — the GoodData analytics platform’s proprietary question language  — please first learn the MAQL introduction weblog put up.

We’ll information you thru the reusability metric creation journey by way of concrete examples and photos. For hand-on expertise with MAQL, attempt GoodData.CN and the demo knowledge supplied together with it (whereas considering this text or experimenting by yourself). Merely comply with the Getting Began information to get a kick-start with the Group Version. For this tutorial, it’s essential that you simply full steps one to 4 (i.e., find yourself with a practical logical knowledge mannequin of the demo workspace).

For this weblog put up, under is the mannequin that we’ll work on:

You’ll be able to see that we’ve one reality dataset, Order Strains, with three linked dimension datasets: Prospects, Merchandise, and Date. Additionally proven within the mannequin are the Campaigns and Marketing campaign Channels datasets; nevertheless, we received’t be utilizing them on this tutorial. For extra details about these ideas, have a look right here.

Metric Definition

Our purpose is to create a reusable metric by computing the ratio of order quantity on the given report dimensionality to the whole order quantity for every Area. The reusability of this metric signifies that the tip person ought to have the ability to select any dimensionality (or granularity) of the report and get the anticipated outcomes. For instance, for State dimensionality, one ought to get the order quantity ratio of every State to its corresponding Area. In distinction, for State and Class, the nominator of our ratio ought to characterize the whole order quantity damaged down by State and Class.

Notice: In case you are following with a dwell GoodData.CN occasion, you possibly can create the metric your self within the Metric Editor.

Let’s begin with the definition of our base metric: Order Quantity.

SELECT SUM({reality/worth}*{reality/amount})

After you have your base metric, you possibly can lastly outline the principle metric: Order Quantity Ratio to Area.

SELECT (SELECT {metric/order_amount}) / 
(SELECT {metric/order_amount} BY {label/area}, ALL {label/state})

For those who create the metric your self, be certain that the Quantity format is ready to P.c (rounded).

Earlier than we exhibit its capabilities, let’s first clarify what precisely is going on right here with all these BY and ALL key phrases. The nominator of this metric doesn’t have any BY modifier, so its dimensionality is taken from the context. On this case, the report attributes — as you will notice later — make sure the reusability requirement. The BY modifiers within the denominator be certain that Area will at all times be a part of the dimensionality, whereas State won’t ever be.

Now that we perceive the fundamentals of MAQL mechanics, it’s time to place our metric into motion and use it within the Analytical Designer, the visualization instrument supplied with GoodData.CN. Below totally different attribute combos, we’ll see what occurs. Firstly, observe that as a result of the denominator is BY Area … , the Area ought to at all times be in attribute buckets; in any other case, the outcome wouldn’t make sense. So, let’s first solely put Area into an attribute bucket, resembling Rows.

As you possibly can see, we get a 100% ratio for every Area. It’s because the nominator inherits dimensionality from the context whereas the denominator has a set Area (which we have already got in context) and removes State (which we don’t have in context, so this can be a no-op). Subsequently, each components are successfully the identical, yielding 100%.

Now, we will attempt one thing a bit extra fascinating, resembling including State to rows.

This now provides us some helpful numbers. The nominator is once more computed on the context dimensionality (Area, State); though, this time, the denominator is barely on Area as a result of State is faraway from the context by the use of the ALL STATE assemble. So, we successfully computed a ratio of State order quantity towards the whole order quantity of the corresponding Area.

Reusability in Motion

Lastly, let’s exhibit the reusability side of the metric. To date, we’ve discovered just one mixture of attributes for which our metric provides smart outcomes: Area and State. The distinctive MAQL property, which units it other than the opposite analytical languages, is its capacity to compute a metric beneath numerous totally different contexts. Let’s add an attribute from a distinct dataset to our report, resembling Class from Merchandise.

We now get a ratio between order quantity per Area, State, Class, and order quantity simply per Area, Class. Specifically, the denominator is instantly computed on the Class attribute as a result of any BY modifiers don’t affect it, so it’s merely saved as is. Notice that the corresponding metric column provides as much as 100%.

Equally, we might add extra attributes, this time from the Date dimension (Quarter/12 months) and even some filters (this 12 months). We nonetheless get significant and proper numbers:

However what if we wish to repair the dimensionality of the denominator to Area and ignore all different context attributes? For that, you would need to barely modify the Order Quantity Ratio to Area metric. Really, for future comparability, it’s extra helpful to create a brand new model: Order Quantity Ratio to Area All Different.

SELECT (SELECT {metric/order_amount}) / 
(SELECT {metric/order_amount} BY {label/area}, ALL OTHER)

The newly added ALL OTHER assemble means to disregard all different context attributes besides these from BY. Consequently, we don’t should specify ALL STATE anymore since ALL OTHER covers it. Let’s see the outcomes for the Area, State, Class attributes mixture.

Now, we get Area, State, and Class order quantity divided by complete Area totals.

Notice that if we wish to get 100%, we’ve so as to add up all numbers within the chosen complete Area/Class rectangle under.

Strive it out your self: Even this modified metric nonetheless shows the identical reusability capabilities as proven earlier than.

Be taught Extra About MAQL

Excited to study extra about reusable MAQL metrics? To get additional in-depth data of this highly effective language, you possibly can comply with our GoodData College MAQL course. For extra assist and data, see our MAQL documentation, neighborhood discussion board, and neighborhood Slack channel.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Share post:

Subscribe

spot_imgspot_img

Popular

More like this
Related

Mastermind teams: Setting the desk

As mentioned in my final article, operating a...

Warren to focus on financial institution crypto choices in US regulator question

Massachusetts Democrat Elizabeth Warren is circulating a letter...

Steps Laptop computer Homeowners Should Take to Mitigate Dangers of Information Loss

Information loss is a rising downside, as corporations...