MAQL DDL

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.

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 data, such as prices and amounts.
• Folders are used to organize attributes and facts visually for users within a GoodData workspace

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 workspaces. When you make changes through CloudConnect Designer, they are converted to MAQL DDL commands when they are executed against your GoodData workspaces.

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 Data Modeling Using the CloudConnect Tool.

To build your data model using the new modeler, see Data Modeling in GoodData.

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 workspace ID (project is a legacy term for workspace). You can type multiple commands into the text field. They all execute together as part of a single transaction. If any of them fail, none of them are applied (roll 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. 

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.

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");

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 workspace.

Import Date Dimension

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

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

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};

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 workspaces 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.

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 workspace. 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:

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:

 The DATE datatype automatically maps with the GoodData-provided date dimension, if you have previously included it into the workspace:

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};