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 |
|---|---|---|
|
|
Yes |
The technical name of the Datamart to query, for example, |
|
|
Yes |
The exact column names to return. Every column referenced in |
|
|
No |
List of filter conditions, combined with AND, applied before aggregation. Each entry is an object: Default: No filter. |
|
|
- |
The column to filter on. Must be one of |
|
|
- |
Supported operators:
To specify a range, use two filters on the same column: |
|
|
- |
The comparison value. Use a non-empty list with |
|
|
No |
A single column to sort by, applied after aggregation. Must be one of Default: No specific order. |
|
|
No |
Maximum rows to return, 1 – 500. When aggregating, the limit applies to the number of groups returned.
|
|
|
No |
A list of aggregations, grouped by |
|
|
- |
The column to aggregate. It is required for every method except |
|
|
- |
Supported methods are: |
|
|
- |
Optional name for the output column. Default: |
|
|
No |
List of other Datamarts to join. The tool applies the joins in the listed order. The default is no join. |
|
|
Yes, for each join |
Technical name of the Datamart to join, for example Standard_Customer_Data. You must have access to this Datamart. |
|
|
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). |
|
|
Yes, for each join |
One or more |
|
|
Yes |
Column that already exists before this join: one of |
|
|
Yes |
Column of the joined Datamart. |
|
|
Yes, for each join |
Columns of the joined Datamart to add to the result. Each entry is an object: |
|
|
Yes |
Column of the joined Datamart. |
|
|
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 |
|---|---|
|
|
The Datamart that was queried. |
|
|
Columns present in each row, in order. When aggregating, the group-by columns come first, followed by the aggregate result columns. |
|
|
Matching rows, keyed by column. |
|
|
Number of rows returned. |
|
|
The row limit actually applied after adjustment. |
|
|
The filter conditions actually applied, echoed back. |
|
|
The aggregates applied, one entry per requested aggregate: |
|
|
|
|
|
The joins applied, in order, one entry per requested join: |
Limitations
The tool queries Datamarts only. It does not query Data Sources.
-
The
datamart_query_datatool queries Datamarts only. It does not query Data Sources. -
The tool returns either row-level data or, when
aggregatesis set, aggregated data grouped bycolumns. -
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
columnsare 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_rowslimits 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[].columnis not incolumns(or, whenaggregatesis set, not inaggregates[].column). -
The
sort_bycolumn is not incolumns(or, whenaggregatesis set, not an aggregate result column). -
An
inSetornotInSetfilter 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):. Setaliason 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
aggregatesentry uses a method other thanCOUNT_ALLand has nocolumn. -
The same column is in both
columnsandaggregates[].column. -
Two aggregates have the same result column name, for example
SUMon the same column twice. Setaliason one of them to fix it. -
An aggregate result column name matches a name in
columns. Setaliasto fix it.