Excel can use Honeydew to directly query metrics in Snowflake using the Pivot Table functionality.

​
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 recommmended for most use cases.“Insert Table” is a simpler static dataset without pivoting.

​
Best Practices - Pivot Table

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 (in Pivot Table options) 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)
Make sure the data model is optimal for Excel - see next section.

​
Performance - Pivot Table

Following steps can significantly 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.
Using these techniques can lead to up to 100x 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.

​
Connection - ODBC Driver

It is possible to connect Excel to Honeydew using an ODBC driver. 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 static datasets, 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 connected to Power BI Service. 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.