MAQL versus SQL

Despite similarities in syntax construction and predefined argument references, MAQL is not the same as SQL.

SQL is a general-purpose relational database language for reading and writing data. MAQL is multidimensional (see MAQL and Multidimensionality) and data is always evaluated based on the context.

This section describes some elementary yet important distinctions.

At the technical level, the GoodData Platform separates the way data is stored in the database (physical data model) from the way the data objects related to each other (logical data model). Workspace developers create the logical data model through a simple, graphical interface, and when the model is published to a workspace, the physical data model is created or updated accordingly.

MAQL vs SQL

MAQL allows you to write complex and powerful report metrics in a fraction of the time compared to SQL. Just take a look below:

As you will see in the following examples, MAQL usage of syntax is more efficient than SQL.

Data Querying Differences

In this section, you will explore the differences in how MAQL and SQL approach to querying data.

Suppose you are interested in sales data from your database. In SQL, the query might be the following:

SELECT SUM(Amount) FROM sales_data;

Results in:

You have sold a $1,000,000 worth of products. Now, suppose you need to know how much of that data is generated from shoe sales. In SQL:

SELECT SUM(amount) FROM sales_data JOIN product_data ON
sales_data.product_type_id=product_data.product_type_id WHERE
product_data.name = "shoes";

Results in:

To understand how to build this second query, you must have command over the following:

• Understanding of table and column as database concepts.
• Familiarity with relational algebra.
• Understanding how and where your data is stored in the database in order to query it.

MAQL takes on a different approach. You don’t need to know where and how your data is stored. The equivalent of the first query above is the following in MAQL:

SELECT SUM(Amount)

Note that FROM sales_data is not necessary. All relevant amounts are summed from the appropriate table in the datastore. Through the GoodData Portal, you can execute queries without understanding the details of how the data is actually stored in the database.

The equivalent of the second query is the following in MAQL:

SELECT SUM(Amount) WHERE Product Type = shoes

There is no need for remembering table names or figuring out table joins.

Examples

The following examples show how creating a metric for profit differs when written in SQL vs MAQL:

Reusability Sample

SELECT
(SELECT SUM(Cost_of_Shipping)
FROM Shipping)
+
(SELECT SUM(Cost)
FROM Campaigns)
AS Cost_of_Sales

SELECT SUM(Cost of Shipping) + SUM(Cost)

Combining Multiple Metrics Sample

SELECT 100*(1 - 
(
(SELECT SUM(Cost_of_Shipping)
 FROM Shipping)
+
(SELECT SUM(Cost)
 FROM Campaigns)
)
/
(SELECT SUM(Amount) 
FROM Sales)
) AS Net_Profit_Margin

SELECT 1 - Cost of Sales/Revenue