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;
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";
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:
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.
The following examples show how creating a metric for profit differs when written in SQL vs MAQL:
Combining Multiple Metrics Sample
One off Query