Datamart Query Data

The datamart_query_data tool retrieves filtered and sorted data from a Datamart, up to the specified row limit. The tool can join other Datamarts to add their columns. It returns either row-level data or aggregated data grouped by selected columns.

The datamart_query_data tool has been deprecated in MCP Server CD6 Iteration 6.

Tool Name – datamart_query_data

Title – Datamart Query Data

Type – Private

Access Profile – Operator

Permissions

The datamart_query_data tool uses your Pricefx permissions. You can query only Datamarts that you have permission to query. These are the same Datamarts that the List Data Sources tool shows you.

Before the tool runs the query, it checks your access to the queried Datamart and to every Datamart in joins. If you do not have access to one of them, the tool does not run the query and returns the following error:

'<Datamart>' is not a Datamart you're entitled to query. Call list_data_sources to see the ones available to you.

The same error appears when the Datamart does not exist, so the error does not show whether a Datamart exists that you cannot access.

Parameters

Parameter

Required

Description

datamart

Yes

The technical name of the Datamart to query, for example, Standard_Sales_Data. The name is case-sensitive and uses underscores. The tool only accepts a Datamart the user has access to.

columns

Yes

The exact column names to return. Every column referenced in filters must also be included (except aggregate columns, see below). * is not supported. When aggregates is set, these columns become the group-by dimensions instead of a plain column list. columns can then be empty ([]), which returns a single grand-total row. When aggregates is set, columns can also include a joined column (its alias) as a group-by dimension.

filters

No

List of filter conditions, combined with AND, applied before aggregation. Each entry is an object: {column, operator, value}.

Default: No filter.

filters[].column

-

The column to filter on. Must be one of columns, one of the aggregates[].column values when aggregates is set, or a joined column (its alias).

filters[].operator

-

Supported operators:

  • equals

  • notEqual

  • greaterThan

  • greaterOrEqual

  • lessThan

  • lessOrEqual

  • inSet

  • notInSet

To specify a range, use two filters on the same column: greaterOrEqual and lessOrEqual.

filters[].value

-

The comparison value. Use a non-empty list with inSet or notInSet; otherwise, use a single string, number, or boolean value.

sort_by

No

A single column to sort by, applied after aggregation. Must be one of columns, or, when aggregating, one of the aggregate result columns (for example SUM_NetRevenue or a custom alias). Prefix with - for descending, for example,  -NetRevenue. When aggregates is not set, sort_by can also be a joined column (its alias).

Default: No specific order.

max_rows

No

Maximum rows to return, 1 – 500. When aggregating, the limit applies to the number of groups returned.
Default: 50.
Requests below 1 or above 500 are automatically adjusted to the nearest allowed value.

aggregates

No

A list of aggregations, grouped by columns. Each entry is an object: {column, method, alias}. Leave it empty for a plain row-level read. Each entry produces one output column.
Default: No aggregation.

aggregates[].column

-

The column to aggregate. It is required for every method except COUNT_ALL. It must not also be listed in columns. It does not need to be listed in columns to be used in filters. It can be a joined column (its alias).

aggregates[].method

-

Supported methods are: SUM, AVG, MIN, MAX, COUNT_ALL (counts every row, nulls included, like SQL COUNT(*)), COUNT_NON_NULL and COUNT_DISTINCT_NON_NULL.

aggregates[].alias

-

Optional name for the output column.

Default:  {method}_{column}, for example SUM_NetRevenue. A COUNT_ALL with no column defaults to COUNT_ALL. If you give COUNT_ALL a column, the column only changes the output name (COUNT_ALL_NetRevenue). It does not change the count.

joins

No

List of other Datamarts to join. The tool applies the joins in the listed order. The default is no join.

joins[].datamart

Yes, for each join

Technical name of the Datamart to join, for example Standard_Customer_Data. You must have access to this Datamart.

joins[].type

Yes, for each join

Join type: INNER (keeps only rows with a match) or LEFT_OUTER (keeps all rows, and leaves the joined columns empty when there is no match).

joins[].keys

Yes, for each join

One or more {left, right} column pairs. The tool matches rows where left equals right. With several pairs, all of them must match.

joins[].keys[].left

Yes

Column that already exists before this join: one of columns, or a joined column from an earlier join. It does not need to be listed in columns.

joins[].keys[].right

Yes

Column of the joined Datamart.

joins[].columns

Yes, for each join

Columns of the joined Datamart to add to the result. Each entry is an object: {column, alias}. Can be empty ([]) to use the join only to keep or drop rows, for example an INNER join that keeps only rows with a match.

joins[].columns[].column

Yes

Column of the joined Datamart.

joins[].columns[].alias

No

Output name of the joined column. The default is the column name. Set it when the name is already used by another column.

Output

Returns an object with the following fields.

Field

Description

datamart

The Datamart that was queried.

columns

Columns present in each row, in order. When aggregating, the group-by columns come first, followed by the aggregate result columns.

rows

Matching rows, keyed by column.

row_count

Number of rows returned.

max_rows

The row limit actually applied after adjustment.

applied_filters

The filter conditions actually applied, echoed back.

aggregates

The aggregates applied, one entry per requested aggregate: {column, method, result_column}. result_column is the column in columns/rows that holds the aggregate's value. The list is empty for a plain read.

truncated

true when row_count equals max_rows. Additional matching rows may exist beyond the row limit. When aggregating, more groups may exist.

joins

The joins applied, in order, one entry per requested join: {datamart, type, keys, columns}. Each entry in columns shows the output name that was actually used. The list is empty when no join was requested.

Limitations

The tool queries Datamarts only. It does not query Data Sources.

  • The datamart_query_data tool queries Datamarts only. It does not query Data Sources.

  • The tool returns either row-level data or, when aggregates is set, aggregated data grouped by columns.

  • The tool can join other Datamarts to the queried Datamart. The joins match rows only by equal column values. Other join conditions are not supported.

  • The tool supports only INNER and LEFT_OUTER joins.

  • Join key columns that are not listed in columns are used for the join, but the tool does not return them.

  • When one row matches several rows in a joined Datamart, the result has one row for each match. This also affects aggregates: for example, a SUM can count the same value more than once.

  • The tool applies filters after the joins and before aggregation. You cannot filter on an aggregated result, for example to keep only customers whose total revenue is above a threshold. To find the top or bottom groups, sort by the aggregate result column instead.

  • max_rows limits the number of rows after the joins and aggregation.

  • A date filter can fail when the type of the filter value does not match the type of the Datamart column. To filter by date, use a derived date field such as …Year or …Month, if the Datamart has one.

Errors

The tool rejects a request with an error when:

  • The Datamart does not exist or you do not have access to it. The error recommends calling List Data Sources.

  • A filters[].column is not in columns (or, when aggregates is set, not in aggregates[].column).

  • The sort_by column is not in columns (or, when aggregates is set, not an aggregate result column).

  • An inSet or notInSet filter has no list or an empty list, or a filter with any other operator has a list or no value.

  • The Query API itself rejects the query (for example, a wrong value type in a date filter). The error message starts with Query API rejected the request:.

  • A joined Datamart does not exist or you do not have access to it. The error is the same as for datamart, and it recommends calling List Data Sources.

  • Two joined columns have the same output name. The message starts with Duplicate joined result column(s):. Set alias on one of them to fix it.

  • A join has no keys.

When aggregates is set, the tool also rejects a request with an error when:

  • An aggregates entry uses a method other than COUNT_ALL and has no column.

  • The same column is in both columns and aggregates[].column.

  • Two aggregates have the same result column name, for example SUM on the same column twice. Set alias on one of them to fix it.

  • An aggregate result column name matches a name in columns. Set alias to fix it.

See Also