atoti_query.query_cube module

class atoti_query.QueryCube

Query cube.

property hierarchies: atoti_core.base_levels.HierarchiesT

Hierarchies of the cube.

Return type

TypeVar(HierarchiesT, bound= BaseHierarchies[BaseHierarchy[BaseLevel]], covariant=True)

property levels: atoti_query.query_levels.QueryLevels

Levels of the cube.

Return type

QueryLevels

property measures: atoti_core.base_cube.MeasuresT

Measures of the cube.

Return type

TypeVar(MeasuresT, bound= BaseMeasures[BaseMeasure], covariant=True)

property name: str

Name of the cube.

Return type

str

query(*measures, filter=None, include_totals=False, levels=(), mode='pretty', scenario='Base', timeout=datetime.timedelta(seconds=30), context={}, **kwargs)

Query the cube to retrieve the value of the passed measures on the given levels.

In JupyterLab with the atoti-jupyterlab plugin 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 (BaseMeasure) – The measures to query.

• filter (Optional[BaseCondition]) –

  The filtering condition. Only conditions on level equality with a string are supported.

  Examples

  >>> df = pd.DataFrame(
  ...     columns=["Continent", "Country", "Currency", "Price"],
  ...     data=[
  ...         ("Europe", "France", "EUR", 200.0),
  ...         ("Europe", "Germany", "EUR", 150.0),
  ...         ("Europe", "United Kingdom", "GBP", 120.0),
  ...         ("America", "United states", "USD", 240.0),
  ...         ("America", "Mexico", "MXN", 270.0),
  ...     ],
  ... )
  >>> table = session.read_pandas(
  ...     df,
  ...     keys=["Continent", "Country", "Currency"],
  ...     table_name="Prices",
  ... )
  >>> cube = session.create_cube(table)
  >>> del cube.hierarchies["Continent"]
  >>> del cube.hierarchies["Country"]
  >>> cube.hierarchies["Geography"] = [
  ...     table["Continent"],
  ...     table["Country"],
  ... ]
  >>> session = tt.QuerySession(f"http://localhost:{session.port}")
  >>> cube = session.cubes[cube.name]
  >>> h, l, m = cube.hierarchies, cube.levels, cube.measures
  
  >>> 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
  
  
  >>> 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
  
  >>> 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
  

• include_totals (bool) –

  Whether the returned DataFrame should include the grand total and subtotals. 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 (Iterable[BaseLevel]) – The levels to split on. If None, the value of the measures at the top of the cube is returned.

• scenario (str) – The scenario to query.

• timeout (timedelta) – The amount of time the query execution can take before aborting it.

• mode (Literal[‘pretty’, ‘raw’]) –

  The query mode.

  • "pretty" is best for queries returning small results:

    • A atoti_query.QueryResult will be returned and its rows will be sorted according to the level order.

    Example:

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

  • A classic pandas.DataFrame will be returned.

  • include_totals="True" will not be allowed.

  • The Convert to Widget Below action provided by the atoti-jupyterlab plugin will not be available.

  Example:

  >>> cube.query(
  ...     m["Price.SUM"],
  ...     levels=[l["Continent"]],
  ...     mode="raw",
  ... )
    Continent  Price.SUM
  0    Europe      470.0
  1   America      510.0
  

• context (Mapping[str, Any]) – Context values to use when executing the query.

Return type

DataFrame