Skip to main content
Excel can use Honeydew to directly query metrics in your cloud warehouse using the Pivot Table functionality. For non-interactive tables, Excel can also load data with ODBC.

​
Pivot Table

A Pivot Table lets business users explore Honeydew data in Excel without writing SQL — slice by any attribute, drill down, and iterate on follow-up questions. Metrics and attributes come from the semantic layer, so the numbers stay consistent across reports and users.
Use the XMLA endpoint where available — it is typically faster and more efficient than Power BI Service. Power BI Service is required for Pivot Tables on Excel Online and Excel on Mac.

​
Connection - XMLA Endpoint

The XMLA endpoint is in Beta. To enable for your organization, please contact support@honeydew.ai.
Honeydew exposes a native XMLA endpoint that Excel connects to directly, without going through Power BI Service. Excel sees Honeydew as a multidimensional cube. The full Pivot Table experience — drill-down, grand totals, slicer filters, and right-click drill-through — works against the live semantic layer.
The XMLA endpoint does not require a Power BI license.

​
Connecting Excel

In Excel Desktop on Windows:
  1. Open the Data ribbon and choose Get Data → From Database → From Analysis Services.
  2. In the Server field, enter the XMLA endpoint URL: https://xmla.honeydew.cloud/xmla.
    If your organization uses a custom hostname for the XMLA endpoint, you can locate it in the Honeydew UI, under the XMLA section in Settings.
  3. Set Log on credentials to Use the following User Name and Password:
    • User Name: a Honeydew username (or an API key)
    • Password: the matching Honeydew password (or API secret)
    Using an API Key is strongly recommended over a username and password.
  4. Click Next and choose a Catalog for the workspace you want to query. Use <workspace> to connect to the workspace’s production state, or <workspace>__<branch> to connect to a working branch. For example, for workspace tpch, the catalog is tpch for production, or tpch__dev for the dev working branch.
  5. Choose a domain from the cube list and click Finish.
  6. Insert a Pivot Table on the connected model.

​
Semantic Model Mapping

Honeydew presents itself to Excel as a multidimensional cube:
HoneydewExcel Pivot Table
DomainCube
EntityDimension
AttributeAttribute hierarchy
MetricMeasure
Calculated attribute or metricSame as a source attribute/metric
Excel builds nested groupings at runtime from the order in which the user drags attributes onto Rows or Columns. For example, dragging Year then Quarter onto Rows shows Year as the outer level; dragging in the opposite order shows Quarter outer. No design-time hierarchy is required in Honeydew — every attribute is independently drillable in any combination.

​
Drill-through (Show Details)

Right-click a metric cell in the Pivot Table and choose Show Details (also called drill-through) to see the rows behind the value. Honeydew returns rows at the underlying entity grain of the metric, expanded with attributes from any related entities the metric depends on — including calculated-attribute dependencies that cross entity boundaries via the semantic layer’s joins. Excel inserts the result as a new sheet, one row per source record.
Excel caps Show Details at 1,000 rows by default. Increase the limit via PivotTable Options → Data → Maximum number of records to retrieve.

​
Filtering large dimensions

Excel pivot tables offer two ways to filter: the per-field Filter dropdown, and Slicers (Insert → Slicer). They differ in how they load values from the cube. The Filter dropdown fetches all distinct values up front and caps out at 10,000 — beyond that, Excel truncates the list and tells the user the remaining values are not available for selection. Slicers load values progressively as the user scrolls and can render up to 1,000,000 distinct values.
For attributes with many distinct values — such as customer ID or product SKU — prefer a Slicer over the Filter dropdown. The Slicer loads values progressively as needed, which is both faster to open and lets the user reach values beyond the Filter dropdown’s 10,000 cap.

​
Grand Totals

Grand-Total rows and columns are controlled per domain via the excel.emit_grand_total_tuples metadata key. The default is true. Setting it to false disables Grand Totals for the cube regardless of the user’s Excel pivot setting: 
type: domain
# ... domain configuration
metadata:
  - name: excel
    metadata:
      - name: emit_grand_total_tuples
        value: false
Turning off Grand Totals reduces query cost for pivots over very large fact tables, where computing the totals row/column adds extra work on top of the per-attribute breakdown.

​
Performance

Honeydew compiles each Excel pivot into a single data warehouse query. A typical pivot needs values at multiple roll-up levels — per-leaf, per-row subtotal, per-column subtotal, and Grand Total. Honeydew runs all of these in one query with shared scans and joins, rather than issuing one query per roll-up level. The same performance techniques that apply to other Honeydew workloads also apply here: See Pivot Table Best Practices for general tips that apply to both pivot connection methods (XMLA and Power BI Service).

​
Limitations

  • The XMLA endpoint is supported only in Excel Desktop on Windows. Excel Online and Excel on Mac do not support XMLA — use the Power BI Service connection instead.
  • Rows where an attribute value is NULL are filtered out before reaching Excel — NULL rows are not shown on any axis or in any total.
    To display NULL rows, add a calculated attribute that substitutes a non-NULL value (for example, COALESCE(<attr>, 'Unknown')), and use the calculated attribute on the pivot.
  • Calculated pivot table filters are not yet supported over the XMLA endpoint.
  • Hiding Grand Totals from the Pivot Table options only affects display — Honeydew still computes the totals. Set excel.emit_grand_total_tuples to false on the domain to skip computing them and reduce query cost.

​
Connection - Power BI Service

To connect Excel to Honeydew, use the Power BI Service integration. This method allows to use Excel with Honeydew without any additional setup for each user.
Using Excel with Honeydew requires a Power BI Pro license, either as standalone license or part of a bundle such as the Office 365 E5 license.
A Honeydew domain published to Power BI will be accessible from Excel as a Power BI semantic model.
  • In Excel Online, choose the Data ribbon -> Data from Power BI -> Choose a Honeydew-managed semantic model -> Insert Pivot Table
  • In Excel Desktop, choose the Data ribbon -> Get Data -> From Power Platform -> From Power BI -> Choose a Honeydew-managed semantic model -> Insert Pivot Table
“Insert Pivot Table” allows ad-hoc dynamic reporting, and is recommended for most use cases.“Insert Table” is a simpler non-interactive table without pivoting.

​
Performance with Power BI Service

Following steps can improve the performance of any Excel Pivot Table:
  1. Use Power BI Data Caching for small dimensions typically used as rows or columns in Excel.
  2. Use one directional (one-to-many) cross filtering for small dimensions typically used as rows or columns in Excel.
  3. Enable Short Term Automatic Aggregate Caching for the domain used with Excel.
These techniques can lead to faster Pivot Tables over large and complex data.
General performance optimization techniques also apply to any workload, including Excel:
  1. For faster metrics, use pre-aggregation with Aggregate Awareness. This allows to pre-compute common metrics to common levels of granularity (e.g. per customer or per date). Honeydew will roll up metrics to chosen columns/rows in the Pivot Table (i.e. per customer zip code or per year).
  2. For faster calculated attributes, use Entity Caching. This allows to pre-compute complex calculated dimensions.

​
Best Practices

When using Excel Pivot Table over large data (i.e. billions of rows or dozens of tables) it is recommended to follow these best practices for the best user experience.
Excel Pivot Table refreshes all its data for every change. The best practices below help build it in a way where every step is quick with minimal refresh time.
  1. Remove total and subtotals if they are not necessary for the analysis.
  2. Filter data as a first step. For example, if there is data for 10 years but analyzing only last month, first add a filter for the last month.
  3. Build rows and columns (and apply their filters) second, before adding metrics.
  4. Add metrics only once all filters and dimensions are built - metric calculations are usually heavier than other steps, so keep them last.
  5. Use the “Defer Layout Update” checkbox to batch long actions together, such as adding multiple metrics (Desktop only)

​
Load data with ODBC

Excel can also connect to Honeydew via an ODBC driver to load non-interactive tables — query results generated from the Honeydew SQL interface and loaded into Excel as a flat table. This method requires each end-user to setup their own connection to Honeydew.
Connecting Excel to Honeydew via the ODBC driver allows only the use of non-interactive tables, which are based on queries from the Honeydew SQL Interface. For example:
SELECT
    "orders.o_orderdate",
    AGG("lineitem.revenue")
FROM world.world
WHERE "customer.c_mktsegment" = 'MACHINERY'
HAVING AGG("orders.count") < 10
GROUP BY 1
ORDER BY 1 DESC
LIMIT 30
Dynamic and ad-hoc queries from an Excel interface, without requiring SQL knowledge, can be achieved through a Pivot Table. Using an ODBC connection is not recommended for these purposes.
In order to complete the installation, you should have access to the Trino ODBC driver (please reach out to support@honeydew.ai to obtain access):
  • Windows: Simba Trino 2.1 64-bit.msi
  • MacOS: Simba Trino 2.2.dmg
In addition, you should receive the SimbaTrinoODBCDriver.lic license file over email.

​
Installation

Follow these installation steps:
  1. Install the Simba ODBC driver (Simba Trino 2.1 64-bit.msi). By default, it will be installed in C:\Program Files\Simba Trino ODBC Driver\.
  2. Place the SimbaTrinoODBCDriver.lic license file in the following folder: C:\Program Files\Simba Trino ODBC Driver\lib\.

​
Setting up the ODBC data source

On Windows it is necessary to register your database and give your database configuration a unique Data Source Name (DSN) before using it from Excel.
It is recommended to use an API Key for setting up the connection.
To register an ODBC database DSN for Honeydew:
  1. From the Windows Control Panel, double-click the ODBC Data Source Administrator icon. If this icon is not listed, double-click the Administrative Tools icon and then double-click the ODBC Data Source Administrator icon.
  2. This will open the ODBC Data Source Administrator dialog.
  3. On either the User DSN or System DSN tab, click Add.
  4. In the Create New Data Source dialog, select the Simba Trino ODBC Driver and click Finish.
  5. In the dialog that opened up, set a name for the data source (e.g. Honeydew), as well as the following parameters:
    • Authentication Type: LDAP Authentication
    • Username: the API key (or a Honeydew username)
    • Password: the API secret (or to the user’s password in Honeydew)
    • Host: jdbc.honeydew.cloud
      If your organization uses a custom hostname for the JDBC connection, you can locate it in the Honeydew UI, under the JDBC section in Settings.
    • Port: 443
    • Catalog: <workspace>__<branch> value, based on the Honeydew workspace and branch you would like to connect to. Omit the branch value if connecting to prod. For example, for workspace tpch, branch prod, the value would be: tpch. And for workspace tpch, branch dev, it would be tpch__dev.
  6. Click Test to test the connection, and then click OK to save the connection.
  7. Click OK to close the ODBC Data Source Administrator dialog.

​
Using the ODBC data source

  1. In Excel, go to the Get Data menu, choose From Other Sources and then From ODBC.
  2. In the dialog that opens up, choose the Honeydew connection you created.
  3. Click Advanced Options, and enter a Honeydew SQL interface query in the SQL Statement field to query the data. For more information on how to use the Honeydew SQL interface, refer to this section.
  4. In the dialog that opens up with the query results, click Load to load the data into Excel.