atoti.Session.query_mdx()

Session.query_mdx(mdx, *, keep_totals=False, timeout=datetime.timedelta(seconds=30), mode='pretty', context={})

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.

  Example

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

  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]"
  ... )
  

  Returns this DataFrame:

  >>> session.query_mdx(mdx, keep_totals=True)
                     Price.SUM
  Date       Country
  Total               3,510.00
  2020-01-01            840.00
  2020-02-02          1,860.00
  2020-03-03            810.00
             China      410.00
  2020-01-01 China
  2020-02-02 China
  2020-03-03 China      410.00
             France   1,380.00
  2020-01-01 France     480.00
  2020-02-02 France     500.00
  2020-03-03 France     400.00
             India      760.00
  2020-01-01 India      360.00
  2020-02-02 India      400.00
  2020-03-03 India
             UK         960.00
  2020-01-01 UK
  2020-02-02 UK         960.00
  2020-03-03 UK
  

  But, if it was displayed into a pivot table, would look like this:

  Country	Price.sum
  	Total	2020-01-01	2020-02-02	2020-03-03
  Total	3,510.00	840.00	1,860.00	810.00
  China	410.00			410.00
  France	1,380.00	480.00	500.00	400.00
  India	760.00	360.00	400.00
  UK	960.00		960.00

• 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 (timedelta) – The duration the query execution can take before being aborted.

• mode (Literal['pretty', 'raw']) –

  The query mode.

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

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

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

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

Return type:

DataFrame