atoti.Cube.query()#
- Cube.query(*measures: Measure, context: Mapping[str, object] = {}, explain: Literal[False] = False, filter: QueryFilter | None = None, include_empty_rows: bool = False, include_totals: bool = False, levels: Sequence[Level] = (), mode: Literal['pretty'] = 'pretty', scenario: str = None, timeout: timedelta = datetime.timedelta(seconds=30)) MdxQueryResult #
- Cube.query(*measures: Measure, context: Mapping[str, object] = {}, explain: Literal[False] = False, filter: QueryFilter | None = None, include_empty_rows: bool = False, include_totals: bool = False, levels: Sequence[Level] = (), mode: Literal['pretty', 'raw'] = 'pretty', scenario: str = None, timeout: timedelta = datetime.timedelta(seconds=30)) DataFrame
- Cube.query(*measures: Measure, context: Mapping[str, object] = {}, explain: Literal[True], filter: QueryFilter | None = None, include_empty_rows: bool = False, include_totals: bool = False, levels: Sequence[Level] = (), mode: Literal['pretty', 'raw'] = 'pretty', scenario: str = None, timeout: timedelta = datetime.timedelta(seconds=30)) Sequence[QueryPlan]
Execute and MDX query.
In JupyterLab with
atoti-jupyterlab
installed, query results can be converted to interactive widgets with the Convert to Widget Below action available in the command palette or by right clicking on the representation of the returned Dataframe.- Parameters:
measures – The measures to query.
context – Context values to use when executing the query.
explain – When
True
, execute the query but, instead of returning its result, return an explanation of how it was executed containing a summary, global timings, and the query plan and all its retrievals.filter –
The filtering condition.
Example
>>> df = pd.DataFrame( ... columns=["Continent", "Country", "Currency", "Year", "Month", "Price"], ... data=[ ... ("Europe", "France", "EUR", 2023, 10, 200.0), ... ("Europe", "Germany", "EUR", 2024, 2, 150.0), ... ("Europe", "United Kingdom", "GBP", 2022, 10, 120.0), ... ("America", "United states", "USD", 2020, 5, 240.0), ... ("America", "Mexico", "MXN", 2021, 3, 270.0), ... ], ... ) >>> table = session.read_pandas( ... df, ... keys={"Continent", "Country", "Currency", "Year", "Month"}, ... table_name="Prices", ... ) >>> cube = session.create_cube(table) >>> del cube.hierarchies["Continent"] >>> del cube.hierarchies["Country"] >>> cube.hierarchies["Geography"] = [ ... table["Continent"], ... table["Country"], ... ] >>> del cube.hierarchies["Year"] >>> del cube.hierarchies["Month"] >>> cube.hierarchies["Date"] = [ ... table["Year"], ... table["Month"], ... ] >>> cube.measures["American Price"] = tt.where( ... cube.levels["Continent"] == "America", ... cube.measures["Price.SUM"], ... ) >>> h, l, m = cube.hierarchies, cube.levels, cube.measures
Single equality condition:
>>> cube.query( ... m["Price.SUM"], ... levels=[l["Country"]], ... filter=l["Continent"] == "Europe", ... ) Price.SUM Continent Country Europe France 200.00 Germany 150.00 United Kingdom 120.00
Combined equality condition:
>>> cube.query( ... m["Price.SUM"], ... levels=[l["Country"], l["Currency"]], ... filter=((l["Continent"] == "Europe") & (l["Currency"] == "EUR")), ... ) Price.SUM Continent Country Currency Europe France EUR 200.00 Germany EUR 150.00
Hierarchy condition:
>>> cube.query( ... m["Price.SUM"], ... levels=[l["Country"]], ... filter=h["Geography"].isin(("America",), ("Europe", "Germany")), ... ) Price.SUM Continent Country America Mexico 270.00 United states 240.00 Europe Germany 150.00
Inequality condition:
>>> cube.query( ... m["Price.SUM"], ... levels=[l["Country"], l["Currency"]], ... # Equivalent to `filter=(l["Currency"] != "GBP") & (l["Currency"] != "MXN")` ... filter=~l["Currency"].isin("GBP", "MXN"), ... ) Price.SUM Continent Country Currency America United states USD 240.00 Europe France EUR 200.00 Germany EUR 150.00 >>> cube.query( ... m["Price.SUM"], ... levels=[l["Year"]], ... filter=l["Year"] >= 2022, ... ) Price.SUM Year 2022 120.00 2023 200.00 2024 150.00
Deep level of a multilevel hierarchy condition:
>>> cube.query( ... m["Price.SUM"], ... levels=[l["Month"]], ... filter=l["Month"] == 10, ... ) Price.SUM Year Month 2022 10 120.00 2023 10 200.00
include_empty_rows –
Whether to keep the rows where all the requested measures have no value.
Example
>>> cube.query( ... m["American Price"], ... levels=[l["Continent"]], ... include_empty_rows=True, ... ) American Price Continent America 510.00 Europe
include_totals –
Whether to query the grand total and subtotals and keep them in the returned DataFrame. Totals can be useful but they make the DataFrame harder to work with since its index will have some empty values.
Example
>>> cube.query( ... m["Price.SUM"], ... levels=[l["Country"], l["Currency"]], ... include_totals=True, ... ) Price.SUM Continent Country Currency Total 980.00 America 510.00 Mexico 270.00 MXN 270.00 United states 240.00 USD 240.00 Europe 470.00 France 200.00 EUR 200.00 Germany 150.00 EUR 150.00 United Kingdom 120.00 GBP 120.00
levels – The levels to split on. If
None
, the value of the measures at the top of the cube is returned.mode –
The query mode.
"pretty"
is best for queries returning small results.>>> cube.query( ... m["Price.SUM"], ... levels=[l["Continent"]], ... mode="pretty", ... ) Price.SUM Continent America 510.00 Europe 470.00
"raw"
is best for benchmarks or large exports:A faster and more efficient endpoint reducing the data transfer from Java to Python will be used.
The Convert to Widget Below action provided by
atoti-jupyterlab
will not be available.
>>> cube.query( ... m["Price.SUM"], ... levels=[l["Continent"]], ... mode="raw", ... ) Continent Price.SUM 0 America 510.0 1 Europe 470.0
scenario – The name of the scenario to query.
timeout – The duration the query execution can take before being aborted.
See also