atoti.query.session module

class atoti.query.session.QuerySession(url, *, auth=None, name=None)

Used to query an existing session.

Query sessions are immutable: the structure of their underlying cubes is not expected to change.

property cubes

Cubes of the session.

Return type

QueryCubes

property name

Name of the session.

Return type

str

property port

Port of the session.

Return type

Optional[int]

query_mdx(mdx, *, keep_totals=False, timeout=30, **kwargs)

Execute an MDX query and return its result as a pandas DataFrame.

Parameters
  • mdx (str) – The MDX SELECT query to execute. Regardless of the axes on which levels and measures appear in the MDX, the returned DataFrame will have all levels on rows and measures on columns.

  • keep_totals (bool) – Whether the resulting DataFrame should contain, if they are present in the query result, 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.

  • timeout (int) – The query timeout in seconds.

Example

>>> from datetime import date
>>> df = pd.DataFrame(
...     columns=["Country", "Date", "Price"],
...     data=[
...         ("China", date(2020, 3, 3), 410.0),
...         ("China", date(2020, 4, 4), 350.0),
...         ("France", date(2020, 1, 1), 480.0),
...         ("France", date(2020, 2, 2), 500.0),
...         ("France", date(2020, 3, 3), 400.0),
...         ("France", date(2020, 4, 4), 420.0),
...         ("India", date(2020, 1, 1), 360.0),
...         ("India", date(2020, 2, 2), 400.0),
...         ("UK", date(2020, 2, 2), 960.0),
...     ],
... )
>>> store = session.read_pandas(
...     df, keys=["Country", "Date"], store_name="Prices"
... )
>>> cube = session.create_cube(store)

This MDX:

>>> mdx = (
...     "SELECT"
...     "  NON EMPTY Hierarchize("
...     "    DrilldownLevel("
...     "      [Prices].[Country].[ALL].[AllMember]"
...     "    )"
...     "  ) ON ROWS,"
...     "  NON EMPTY Crossjoin("
...     "    [Measures].[Price.SUM],"
...     "    Hierarchize("
...     "      DrilldownLevel("
...     "        [Prices].[Date].[ALL].[AllMember]"
...     "      )"
...     "    )"
...     "  ) ON COLUMNS"
...     "  FROM [Prices]"
... )

would display this pivot table:

Country

Price.sum

Total

2020-01-01

2020-02-02

2020-03-03

2020-04-04

Total

2,280.00

840.00

1,860.00

810.00

770.00

China

760.00

410.00

350.00

France

1,800.00

480.00

500.00

400.00

420.00

India

760.00

360.00

400.00

UK

960.00

960.00

but will return this DataFrame:

>>> session.query_mdx(mdx).sort_index()
                    Price.SUM
Date       Country
2020-01-01 France       480.0
           India        360.0
2020-02-02 France       500.0
           India        400.0
           UK           960.0
2020-03-03 China        410.0
           France       400.0
2020-04-04 China        350.0
           France       420.0
Return type

QueryResult

property url

URL of the session.

Return type

str

visualize(name=None)

Display an atoti widget to explore the session interactively.

Note

This method requires the atoti-jupyterlab plugin.

The widget state will be stored in the cell metadata. This state should not have to be edited but, if desired, it can be found in JupyterLab by opening the “Notebook tools” sidebar and expanding the the “Advanced Tools” section.

Parameters

name (Optional[str]) – The name to give to the widget.