Excel
Excel can use Honeydew to directly query metrics in Snowflake using the Pivot Table functionality.
Connection
To connect Excel to Honeydew, use the Power BI Service integration.
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
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.
- Remove total and subtotals (in Pivot Table options) if they are not necessary for the analysis.
- 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.
- Build rows and columns (and apply their filters) second, before adding metrics.
- Add metrics only once all filters and dimensions are built - metric calculations are usually heavier than other steps, so keep them last.
- 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
Following steps can significantly improve the performance of any Excel Pivot Table:
- Use Power BI Data Caching for small dimensions typically used as rows or columns in Excel.
- Use one directional (one-to-many) cross filtering for small dimensions typically used as rows or columns in Excel.
- 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:
- 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).
- For faster calculated attributes, use Entity Caching. This allows to pre-compute complex calculated dimensions.