Excel

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.

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

​

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.

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.

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.

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

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
• Mac: Simba Trino 2.2.dmg

In addition, you should receive the SimbaTrinoODBCDriver.lic license file over email.

​

Windows

​

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.

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.

​

macOS

​

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: <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.
   • 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 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.
6. Click Return Data to load the data into Excel.