Page tree
Skip to end of metadata
Go to start of metadata

The Multi-Dimension Analytical Query Language (MAQL) is a simple yet powerful query language that is used extensively in GoodData reporting. An extension of MAQL, MAQL DDL (MAQL Data Definition Language) is used for building and modifying a data model.

Except for changing the display values and managing folders, most MAQL DDL changes require updating of the underlying physical data model, which must be updated using the SYNCHRONIZE command. Making changes to the LDM can have cascading effects throughout the project, including the unexpected deletion of project data. For example, if attributes or facts are removed and other objects reference them, then the secondary objects are also removed. This deletion cascades throughout the project. In some cases, execution of a required SYNCHRONIZE command means that you must reload the project’s data, although you may be able to prevent deletions using the PRESERVE DATA keyword. See Synchronize.

Getting Started

Similar to the core MAQL, DDL syntax is simple and familiar to database users. At its core, there are the three important commands:

  • CREATE
  • ALTER
  • DROP

These commands can be applied to the following objects that form the data model:

  • datasets are single joint sources of data and are comprised of attributes and facts
  • attributes are containers of data that cannot be added like all strings and some numeric columns such as ID or SSN
  • facts are data columns containing computational data like prices, amounts, etc.
  • folders are used to organize attributes and facts visually for users within a GoodData project

MAQL DDL via CloudConnect

Through the LDM Modeler in CloudConnect Designer, you can make modifications to the data model and publish these changes to your GoodData projects. When you make changes through CloudConnect Designer, they are converted to MAQL DDL commands when they are executed against your GoodData projects.

CloudConnect Designer supports the execution of all of these commands, although you make your changes using visual tools that mask the actual commands. In the LDM Modeler, you can click the MAQL tab to see the MAQL DDL that is being executed. Additionally, the model validation tools in CloudConnect Designer make it the preferred way to modify logical data models.

For more information, see CloudConnect LDM Modeler Guide.

MAQL DDL via API

To try the MAQL commands featured in this article directly via the REST API interface, use the following URL:

http://secure.gooddata.com/gdc/md/<project>/ldm/manage2

You will replace the <project> value with your own GoodData project ID. You can enter multiple commands into the text field. They will all execute together as part of a single transaction. If any of them fails, none of them will be applied (rolls back).

Identifiers

Throughout the MAQL script samples below, words enclosed in {curly brackets} denote identifiers. Think of them as human-readable IDs that you can assign to objects and refer to them in other objects.

After an object is created, the identifier is persistent and cannot be changed. You can choose your own naming conventions for identifiers (in the examples below identifiers are often added into “namespace” by prefixing them with “folder.”, “fact.” etc.) Identifiers can contain alphanumeric characters, underscore and dot (i.e. [A-Za-z0-9_.]).

The names for Data column identifiers must follow a specific naming convention. These identifiers reference specific data columns in data files that you upload through the upload API and are structured in 3NF. The identifiers must take the form of file.column. The file part corresponds to the data file, while column corresponds to a specific column. All columns that in 3NF share a common file must also share the same prefix in their identifiers. In examples below, data column identifiers are highlighted.

Synchronize

Whatever commands you perform, they modify the logical data model, which defines the formal representation of your data. Underneath this abstraction layer is a physical data model that GoodData uses to perform computations.

After you finish applying your changes through MAQL DDL, to apply the corresponding changes to the physical data model, you must call the SYNCHRONIZE command. You can think of this as preparing your changes and then committing them. Visual changes (names, descriptions, membership in folders etc.) do not need to be synchronized to the physical model.

Whenever you use the SYNCHRONIZE keyword, you should add the PRESERVE DATA keyword, which will attempt to preserve data in your project from being removed.

SYNCHRONIZE {dataset.one}, {dataset.two} PRESERVE DATA;

Dataset

Since dataset is just a named container for attributes and facts, creating and modifying it is fairly simple.

CREATE DATASET

CREATE DATASET {dataset.quotes} VISUAL (TITLE "Stock Quotes Data");

ALTER DATASET

Add attribute/fact

ALTER DATASET {dataset.quotes} ADD {attribute.sector};


Remove attribute/fact

ALTER ATTRIBUTE {attr.players.age} DROP KEYS {d_players_age.id}, {f_players.age_id};  
ALTER DATASET {dataset.players} DROP {attr.players.age};  
DROP {attr.players.age};  
SYNCHRONIZE {dataset.players} PRESERVE DATA;


Change the name of the dataset

ALTER DATASET {dataset.quotes} VISUAL(TITLE "Internal Quotes Data");

An attribute or fact should belong to exactly one dataset. Otherwise, validation of the project fails.

Date Dimension

GoodData offers a prepared date dimension dataset so you do not have to create your own. You can use them in MAQL DDL when you create or change the logical data model of your project.

Import Date Dimension

If you need only one date dimension in your project, execute the following commands:

INCLUDE TEMPLATE "URN:GOODDATA:DATE";
ALTER ATTRIBUTE {date} ADD KEYS {f_quotes.date};

 

The first command imports the dataset into the project, the second joins it to the fact table f_quotes.

 

If you plan to use more date dimensions you must distinguish them by a special identifier (for technical purposes) and a title (for visual purposes).

INCLUDE TEMPLATE "URN:GOODDATA:DATE" MODIFY (IDENTIFIER "born", TITLE "Born");
ALTER ATTRIBUTE {born.date} ADD KEYS {f_players.dt_born};

To properly join the date dimension to the fact table you must call the SYNCHRONIZE command for the corresponding dataset.

Update Date Dimension

When you use fiscal calendars, new versions of the template can be released intermittently. When this occurs, you will need to update all date dimensions in your projects that have been created from that template.

UPDATE TEMPLATE "URN:FISCALAPR1:DATE" WITH DATA;

Attribute

An attribute is the unit that allows you to specify how to aggregate (or slice) your data. Examples include: Assignee, City, Day, ID, Group, etc.

Attributes can optionally have additional labels. These are alternate string representation of the same semantic value. For example, a person John Doe is the same person, regardless if he is visualized as "J. Doe", "Doe, John", "Johnny" etc.

By default, attributes are 128 characters in length. As needed you can change the length of an attribute to be up to 10000 characters. For more information, see Changing the length of attribute labels.

CREATE ATTRIBUTE

CREATE ATTRIBUTE {attr.quotes.symbol} VISUAL(TITLE "Symbol", FOLDER {folder.quotes.attr}) AS {d_quotes_symbol.nm_symbol};

ALTER ATTRIBUTE

ALTER ATTRIBUTE {attr.quotes.symbol} ADD LABELS {attr.quotes.company} VISUAL(TITLE "Company") AS {d_quotes_symbol.nm_company};

DEFAULT LABEL

If you have additional labels assigned to one attribute, you can specify which one is the default attribute label. When you create an attribute with more labels, the first label from the list is assigned as the default one.

Create attribute syntax does not provide any option allowing explicit specification of default label. Alter attribute syntax is the only option how to change the default label.

CREATE ATTRIBUTE {attr.quotes.attribute} AS LABELS {attr.quotes.label1} VISUAL(TITLE "Label 1"),  {attr.quotes.label2};
ALTER ATTRIBUTE {attr.quotes.attribute} DEFAULT LABEL {attr.quotes.label2};

HYPERLINK LABEL

By using the following code, you can add a hyperlink to a label, which means the label is shown with a link in reports.

CREATE ATTRIBUTE {attr.quotes.attribute} AS LABELS {attr.quotes.label1} VISUAL(TITLE "Hyperlink") HYPERLINK;
ALTER ATTRIBUTE {attr.quotes.attribute} DEFAULT ALTER LABELS {attr.quotes.label1} HYPERLINK;

SORTING BY LABEL

Attribute can be optionally sorted by label. With the following code, the label is created and ordered. You must specify the ordering type: ASC/DESC.

CREATE ATTRIBUTE {attr.quotes.attribute} AS LABELS {attr.quotes.label1} VISUAL(TITLE "Label 1") ORDER {attr.quotes.label1} ASC;
ALTER ATTRIBUTE {attr.quotes.attribute} ORDER BY {attr.quotes.label1} DESC;

Fact

CREATE FACT

CREATE FACT {fact.quotes.open_price} VISUAL( TITLE "Open Price", FOLDER {folder.quotes.fact})
AS {f_quotes.f_open_price};

ALTER FACT

ALTER FACT {fact.quotes.open_price} ADD {f_quotes2.f_open_price};

Folder

Folders are used to visually organize facts or attributes and metrics for the user within the project. Folders are types; they can only contain objects of one kind (hence the TYPE section).

CREATE FOLDER

CREATE FOLDER {folder.quotes.attr} VISUAL ( TITLE "Stock Quotes Data", DESCRIPTION "Stock quotes data obtained from John Doe etc." )
TYPE ATTRIBUTE;

ALTER FOLDER

ALTER FOLDER {folder.quotes.attr} VISUAL(TITLE "Quotes Attributes");

Folders are populated during the creation and modification of attributes, metrics and facts. The only modification to the folder object is its name:

Performance Optimization

There are several useful techniques to optimize the data model for performance:

Performance optimization for your specific model requires data modeling experience and experience with modeling in GoodData, in particular. Feel free to talk to GoodData Customer Support.

Specify a DATATYPE

By default, the datamart automatically stores all facts as decimal(12,2) and all attributes and labels as 128-character strings. For performance reasons or to store other data types, you can redefine your column data type:

ALTER
 DATATYPE {d_quotes_symbol.nm_symbol} VARCHAR(4), 
{d_quotes_symbol.nm_symbol} VARCHAR(80), {f_quotes.f_open_price} 
DECIMAL(10,2);

 

Supported data types are:

data typeformnote
VARCHAR (N) N (1..10000)
DECIMAL (M,D) M min(-1e+15) max(1e+15), D max = 6
INT min(-2147483648) max(2147483647)
BIGINT min(-1e+15) max(1e+15)
DATE‘YYYY-MM-DD’ 
DOUBLE discouraged

 

Please note that the DATE datatype automatically maps with the GoodData-provided date dimension, if you have previously included it into the project:

INCLUDE TEMPLATE "URN:GOODDATA:DATE" MODIFY (IDENTIFIER "my-date", TITLE "quote");

Create Multiple Fact Columns

In the following example, the fact fact.quotes.open_price already has a fact column f_quotes.f_open_price, but for performance reasons, an identical column in f_quotes2 is being added below:

ALTER FACT {fact.quotes.open_price} ADD {f_quotes2.f_open_price};

  • No labels