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 used in GoodData reporting. An extension of MAQL, MAQL DDL (MAQL Data Definition Language) is used for building and modifying data models.

Most MAQL DDL changes require updates of the underlying physical data model with the SYNCHRONIZE command. Exceptions:

  • Changing display values
  • Managing folders

Changes to the LDM can have cascading effect throughout the project. For example, if you remove attributes or facts and other objects reference them, these objects are also removed. To prevent deletions, use the PRESERVE DATA keyword. See Synchronize.

In some cases, execution of a required SYNCHRONIZE command means that you must reload the project’s data.

Contents:

Getting Started

The DDL syntax is simple and familiar to database users. There are the three core 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 consisting 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 dat, such as prices and amounts.
  • 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.

In CloudConnect Designer, you work with visual tools to create the DDL commands. In the LDM modeler, click the MAQL tab to display the MAQL DDL that is being executed. The model validation tools in CloudConnect Designer make it a preferred way to modify logical data models (LDMs).

For more information, see CloudConnect LDM Modeler Guide.

MAQL DDL via API

To use the MAQL commands directly via the REST API interface, use the following URL:

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

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

Identifiers

In 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 cannot be changed. You can select your own naming conventions for identifiers. Identifiers can contain alphanumeric characters, underscore and dot (i.e. [A-Za-z0-9_.]).

 In the examples below identifiers are often prefixed with “folder.”, “fact.” etc. 

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

All commands 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 applying changes through MAQL DDL, call the SYNCHRONIZE command to apply the corresponding changes to the physical data model. Visual changes (names, descriptions, membership in folders) do not need to be synchronized to the physical model.

Whenever you use the SYNCHRONIZE keyword, add the PRESERVE DATA keyword This attempts to preserve data in your project from being removed.

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

Dataset

Dataset is a named container for attributes and facts.

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 must belong to one dataset. Otherwise, validation of the project fails.

Date Dimension

GoodData offers a prepared date dimension dataset. 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.

To use multiple date dimensions, 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, 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, update all date dimensions in your projects that have been created from that template.

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

Attribute

Attributes are units that allow you to specify how to aggregate (or slice) your data, such as Assignee, City, Day, ID, Group.

Attributes can optionally have additional labels. These are alternate string representations 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. 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 multiple labels, the first label from the list is assigned as the default one.

To change the default label, use the Alter attribute syntax.

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

To display labels with a links in reports, add hyperlinks to the labels:

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

You can sort attributes by labels. To create and order labels, use the following codeand 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 within the project. Folders are types; they can only contain objects of one kind.

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

Folders are populated during the creation and modification of attributes, metrics, and facts. To modify the folder object name:

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

Performance Optimization

Use the following 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. For details, contact GoodData Customer Support.

Specify a DATATYPE

By default, the datamart automatically stores all facts as decimals (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

 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, you can add an identical column in f_quotes2:

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