> ## Documentation Index
> Fetch the complete documentation index at: https://honeydew.ai/docs/llms.txt
> Use this file to discover all available pages before exploring further.
# Excel
Excel can use Honeydew to directly query metrics in your cloud warehouse using the [Pivot Table](#pivot-table) functionality. For non-interactive tables, Excel can also [load data with ODBC](#load-data-with-odbc).
## Pivot Table
A [Pivot Table](https://support.microsoft.com/en-us/office/create-a-pivottable-to-analyze-worksheet-data-a9a84538-bfe9-40a9-a8e9-f99134456576) 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](#connection-xmla-endpoint) where available —
it is typically faster and more efficient than [Power BI Service](#connection-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](mailto: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](/docs/access-control/api-keys))
* **Password**: the matching Honeydew password (or API secret)
Using an [API Key](/docs/access-control/api-keys) is strongly recommended over a username and password.
Organizations with
[Windows Authentication (Kerberos)](/docs/access-control/windows-authentication)
configured can select **Use Windows Authentication** instead — domain users
connect with their Windows login, without entering credentials.
This requires the organization's custom XMLA hostname in the **Server**
field, not the shared endpoint.
4. Click **Next** and choose a **Catalog** for the workspace you want to query.
Use `` to connect to the workspace's production state,
or `__` 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:
| Honeydew | Excel Pivot Table |
| ------------------------------ | --------------------------------- |
| Domain | Cube |
| Entity | Dimension |
| Attribute | Attribute hierarchy |
| Metric | Measure |
| Calculated attribute or metric | Same 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:
```yaml theme={null}
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:
* [Aggregate Awareness](/docs/performance/aggregate-awareness) for pre-computed metric roll-ups.
* [Entity Caching](/docs/performance/entity-caching) for pre-computed calculated attributes.
* [Short Term Automatic Aggregate Caching](/docs/performance/aggregate-awareness#automatic-aggregate-caching-in-snowflake)
(Snowflake-only) for repeated pivot interactions.
See [Pivot Table Best Practices](#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-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](/docs/calculations/attributes)
that substitutes a non-NULL value (for example, `COALESCE(, '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`](#grand-totals) 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](/docs/integration/bi-tools/powerbi) 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](/docs/integration/bi-tools/powerbi#data-caching-in-powerbi) for small dimensions typically used as rows or columns in Excel.
2. Use one directional (one-to-many) [cross filtering](/docs/modeling/relations#cross-filtering) for small dimensions typically used as rows or columns in Excel.
3. Enable [Short Term Automatic Aggregate Caching](/docs/performance/aggregate-awareness#automatic-aggregate-caching-in-snowflake) 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](/docs/performance/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](/docs/performance/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.
* With the [Power BI Service](#connection-power-bi-service) connection,
hide totals in the Pivot Table options. Honeydew still computes them.
* With the [XMLA endpoint](#connection-xmla-endpoint), set
[`excel.emit_grand_total_tuples`](#grand-totals) to `false` on the
domain to skip computing totals and reduce query cost.
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](/docs/integration/sql-interface).
For example:
```sql theme={null}
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](#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](mailto: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](/docs/access-control/api-keys) 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**: `__` 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](/docs/integration/sql-interface).
4. In the dialog that opens up with the query results, click **Load** to load the data into Excel.
### Installation
1. Install the **Simba ODBC driver** (`Simba Trino 2.2.dmg`).
#### Setting up the ODBC data source
1. In **Excel**, go to the **Data** menu, and choose **From Database (Microsoft Query)**.
2. **iODBC Data Source Chooser** will open up.
3. Click on either **User DSN** or **System DSN** tab,
depending on whether you want to add the Honeydew connection for all users or just for the current user.
4. Click **Add** to add a new Data Source for Honeydew.
5. Choose the **Simba Trino ODBC Driver** from the list of drivers and click **Finish**.
6. In the dialog that opened up, set a name for the data source (e.g. **Honeydew**), as well as the following parameters:
* Keyword: `Host`, Value: `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**.
* Keyword: `Port`, Value: `443`
* Keyword: `SSL`, Value: `1`
* Keyword: `AuthenticationType`, Value: `LDAP Authentication`
* Keyword: `Catalog`, Value: `__` 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`.
* Click **OK** to save the connection.
7. Click **Test** to test the connection. In the dialog that opens up, enter your Honeydew username and password, and click **OK**.
Set **Username** to the API key (or a Honeydew username), and **Password** to the API secret.
It is recommended to use an [API Key](/docs/access-control/api-keys) for setting up the connection.
8. If the test is successful, click **OK** to close the dialog.
### Using the ODBC data source
1. In Excel, go to the **Data** menu, and choose **From Database (Microsoft Query)**.
2. In the dialog that opens up, choose the **Honeydew** connection you created.
3. Enter your Honeydew username and password, and click **OK**.
4. The **Query Wizard** will open. Choose the tables and columns you want to query, and click **Next**.
5. Use the Honeydew SQL interface syntax to query the data. For more information on how to use the Honeydew SQL interface, refer to [this section](/docs/integration/sql-interface).
6. Click **Return Data** to load the data into Excel.