​
Connection Setup

Power BI connects to Honeydew via ODBC driver using the Trino protocol.

​
Power BI Desktop

Honeydew uses the Trino protocol to connect to Power BI. In order to connect, an ODBC driver needs to be installed.

​
Driver installation

In order to complete the installation, you should have the following (please reach out to support@honeydew.ai to obtain access):
  1. Access to a shared folder containing the following files:
    • Simba Trino 2.1 64-bit.msi
    • Honeydew.mez
  2. Received SimbaTrinoODBCDriver.lic license file over email
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\.
  3. Place the Honeydew.mez file in the custom connectors directory: C:\Users\<user>\Documents\Power BI Desktop\Custom Connectors. Create the folder if it doesn’t exist.
If you are syncing your documents with Microsoft OneDrive, the file path may vary. If the Honeydew connector is not appearing in the list of available connectors in Power BI Desktop, consider checking the following paths. You may need to create the folders in your OneDrive directory if they do not already exist:
  • C:\Users\<user>\OneDrive\Documents\Power BI Desktop\Custom Connectors
  • C:\Users\<user>\OneDrive - XXXX\Documents\Power BI Desktop\Custom Connectors

​
Initial Setup

It is recommended to use an API Key for setting up the connection.
  1. Open Power BI Desktop
  2. In case you get a notification about “Uncertified Connectors”:
    • In order to be able to use the connector, please go to Options and Settings > Options > Security, and set the Data Extensions setting to “Allow any extension to load without validation or warning”: Data Extensions
    • Restart Power BI Desktop
  3. In Honeydew, go to the menu of an existing workspace, and click Power BI > Download datasource. A *.pbit file will get downloaded.
  4. Open the downloaded *.pbit file in Power BI Desktop.
  5. Go to File > Options and settings > Data source settings. The following connect dialog will open up: Data Source Settings
  6. Click “Edit Permissions…”. The following dialog will open up: Edit
  7. Click Edit. The following dialog will open up: Connect
  8. Choose the LDAP option, set Username to the API key (or a Honeydew username) and Password to the API secret (or to the user’s password in Honeydew), and click Connect (or Save).
  9. You can now use the datasource downloaded from Power BI - it has a direct connection to Snowflake.

​
Power BI Service

​
Power BI Gateway Installation

Honeydew connects to Power BI Service by using the Power BI gateway. See the installation manual on how to install an on-premise gateway. If you are unable to have a Power BI gateway in your organization, please contact support@honeydew.ai.

​
Driver Installation & Power BI Gateway Setup

In order to complete the installation, you should have the following (please reach out to support@honeydew.ai to obtain access):
  1. Access to a shared folder containing the following files:
  • Simba Trino 2.1 64-bit.msi
  • Honeydew.mez
  1. Received SimbaTrinoODBCDriver.lic license file over email
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\.
  3. Place the Honeydew.mez file in the custom connectors directory that can be accessible by the Power BI Gateway. By default that is C:\Windows\ServiceProfiles\PBIEgwService\Documents\Power BI Desktop\Custom Connectors. Create the folder if it doesn’t exist.
  4. Complete the steps as described in the Enable and use custom connectors section.

​
Publishing a model

  1. Load a semantic model on Power BI Desktop (as described here), that is connected to Power BI Service.
  2. Save the model as a *.pbix file.
  3. Publish the saved model to the Power BI Service.
  4. Open the model in the Power BI Service.
  5. You can now build reports on top of the published model or use the model directly from Power BI Service.

​
API Integration

Honeydew integrates with the Power BI API through a service principal authentication, using a Microsoft Entra ID App.
You will need your Cloud Application Administrator or Application Administrator to complete these steps — you may not have access yourself. This will be required if the creation of registered applications is not enabled for the entire organization.
1

Create an Entra ID App

You will need your Cloud Application Administrator or Application Administrator to complete these steps — you may not have access yourself. This will be required if the creation of registered applications is not enabled for the entire organization.
  1. Open the Azure Portal
  2. In the search bar, search for App registrations. Open it and start a new registration by clicking New registration
  3. On the Register an application page, enter the following details:
    • Name: Honeydew Power BI Integration
    • Supported account types: Accounts in this organizational directory only (Single tenant)
    • Redirect URI: Leave blank
  4. Click Register
  5. On the homepage of your newly created application, from the Overview screen, copy the values for the following fields and store them in a secure location:
    • Application (client) ID
    • Directory (tenant) ID
  6. From the left menu of your newly created application page, open Manage > Certificates & secrets
  7. Click New client secret
  8. In the Add a client secret dialog, enter the following details:
    • Description: Honeydew Power BI Integration
    • Expires: Choose an appropriate expiry date
  9. Click Add
  10. Copy the value of the newly created client secret and store it in a secure location
2

Create a security group in Microsoft Entra ID

You will need your Cloud Application Administrator or Application Administrator to complete these steps — you may not have access yourself.
  1. Open the Azure Portal
  2. In the search bar, search for Microsoft Entra ID and select it from the drop-down list
  3. In the left menu, expand the Manage section, and click Groups
  4. At the top of the page, click New group and enter the following details:
    • Group type: Security
    • Group name: Honeydew Power BI Integration
    • Membership type: Assigned
    • Under Members, click No members selected, in the Add members dialog that opens up search for the application you created in the previous step (e.g. Honeydew Power BI Integration) and click Select to add it.
  5. Click Create to create the group
3

Assign security group and role in Microsoft Power BI workspace

You will need to be at least a member of the Microsoft Power BI workspace to which you want to add the security group to complete these steps — you may not have access yourself. Ensure that you add the security group from the homepage and not the admin portal.
  1. Open the Microsoft Power BI Service Homepage
  2. Click on Workspaces in the left menu bar
  3. Click on the workspace you want to add Honeydew access to (repeat these steps for each workspace)
  4. In the top bar, on the right, click Manage Access
  5. Click Add people or groups and search for the security group you created in the previous step (e.g. Honeydew Power BI Integration)
  6. In the role drop-down, choose Member as the role
  7. Click Add to add the group
4

Enable extra admin API settings in Microsoft Power BI

You will need your Power BI Administrator to complete these tasks — you may not have access yourself.
To enable the Microsoft Power BI admin API:
  1. Log in to the Power BI Admin Portal
  2. In the left menu under Admin Portal, click Tenant settings
  3. Under the Developer settings heading, click to expand Service principals can use Fabric APIs and ensure it is Enabled
    • Under Specific security groups (Recommended), add the security group created above (e.g. Honeydew Power BI Integration)
    • At the bottom of the expanded section, click the Apply button
  4. Under the Admin API settings heading, configure the following:
    • Click to expand the Service principals can access read-only admin APIs expandable and ensure it is Enabled
      • Under Specific security groups, add the security group created above (e.g. Honeydew Power BI Integration)
      • At the bottom of the expanded section, click the Apply button
    • Click to expand the Enhance admin APIs responses with detailed metadata expandable and ensure it is Enabled
      • Under Specific security groups, add the security group created above (e.g. Honeydew Power BI Integration)
      • At the bottom of the expanded section, click the Apply button
    • Click to expand the Enhance admin APIs responses with DAX and mashup expressions expandable and ensure it is Enabled
      • Under Specific security groups, add the security group created above (e.g. Honeydew Power BI Integration)
      • At the bottom of the expanded section, click the Apply button
5

Provide access to Honeydew connection in Power BI

You must have at least User with resharing permissions in Power BI for the Honeydew connection to complete these steps.
  1. Open the Microsoft Power BI Service Homepage
  2. In upper right corner click the gear icon for Settings and then click on Manage connections and gateways
  3. In the Connections tab, identify the connection used for Honeydew (note that it should have a Gateway cluster name configured).
  4. Click on the three dots next to the right of the connection name, and then click Manage users in the popup menu.
  5. In the Manage users dialog, search for the security group created above. Provide the group with the User permission and click Share.
6

Configure Honeydew

Only users with the Admin role in Honeydew can complete these steps.
  1. In Honeydew, go to the Settings page
  2. Click on Power BI in the left menu
  3. Fill in the following fields:
    • Application ID: The Application (client) ID from the Entra ID App you created
    • Tenant ID: The Directory (tenant) ID from the Entra ID App you created
    • Client Secret: The client secret from the Entra ID App you created
  4. Click Save

​
Direct Query

Power BI connects directly to Honeydew in Direct Query mode by using the Honeydew ODBC driver. A Honeydew domain will be presented to the data model as a single virtual table.
Excel on Honeydew runs using Power BI connection. See Excel for details.

​
Metadata Sync

With Metadata Sync, Honeydew synchronizes the following metadata to Power BI:
  • Table display names (based on domain and entity display names). When no display name is set, Honeydew creates an automatic user friendly name.
  • Field type (metric or attribute) - attributes have default aggregation set to “none” and are presented as fields. regardless to data type, while metrics have a default aggregation set and are presented as measures.
  • Field display names. When no display name is set, Honeydew creates an automatic user friendly name.
  • Field folders. When no folder is set, Honeydew uses entity names as folder names.
You can create multi-level nested folders in Power BI by placing / separators in the folder value in Honeydew metadata (e.g. folder/subfolder)
  • Field hidden property.
  • Field descriptions.
  • Field format and semantic role (through metadata configuration).
Attributes have “none” as a default aggregation to distinguish them from metrics. But aggregating on attributes is possible! Any specific Power BI widget that needs to aggregate can do so by choosing an aggregation in the widget “field well”.

​
Power BI-specific Metadata

Attributes and Metrics: You can add Power BI-specific metadata, that specifies the default formatting or the semantic role, to an attribute or a metric in the schema: 
...
metadata:
  - name: powerbi
    metadata:
      - name: formatstring
        value: yyyy-mm-dd
      - name: datacategory
        value: Latitude
Some examples for formatstring values (Power BI DAX documentation):
  • yyyy-mm-dd
  • #,##0;-#,##0
Some examples for datacategory values:
  • Latitude
  • Longitude
  • PostalCode
  • City
  • StateOrProvince
  • Continent
  • Place
  • WebUrl
  • ImageUrl
  • Barcode
Domain: By default, Honeydew metadata sync creates a separate default folder for each entity within the domain. You can add Power BI-specific metadata to disable this behavior, so only attributes and metrics explicitly placed in folders appear in the folder structure. 
...
metadata:
  - name: powerbi
    metadata:
      - name: create_default_entity_folder
        value: false

​
Cross-Filtering and Referential Integrity

Honeydew supports cross filtering similar to Power BI. The following table helps translate Power BI settings to Honeydew:
Power BI Referential IntegrityPower BI Cross-FilteringHoneydew Join Type (from many to 1)Honeydew Cross-Filtering
No (Power BI default)Single (Power BI default)LEFTOne-to-many
NoBothLEFT (Honeydew default)Both (Honeydew default)
YesSingleINNEROne-to-many
YesBothINNERBoth
Note that that the default relationship settings in Honeydew are different than default Power BI settings.

​
Data Caching in Power BI

Honeydew supports caching the attributes of entities within Power BI in order to speed up queries over those entities. Caching is done by exposing cached entities as separate “virtual tables” in Power BI with a storage mode.
Power BI entity caching is helpful to keep in-memory small dimensions (up to 1GB in total size, up to 1M rows per each entity).For general purpose attribute caching in Snowflake see Entity Caching
Data caching in Power BI is supported for:
  1. Attributes only.
  2. Dimensions, not facts: Entities that are not the top granularity, and have a single join path to a higher granularity.
  3. With one-to-many or none cross filtering
Metrics are not cached in Power BI - use features such as Aggregate Awareness to speed up aggregations.
To set up Power BI data caching add the following to an entity YAML 
type: entity
# ... entity configuration
metadata:
  - name: powerbi
    metadata:
      - name: semantic_model_as_separate_table
        value: yes
      - name: semantic_model_storage_mode
        value: dual
Dual Storage Mode enables Power BI Service to cache entity data.Attribute queries using that entity will be answered in-memory. Other queries (such as metrics or combining the entity with other entities) will be translated in Honeydew with a direct mode.
Supported storage modes:
  • Not set: entity will appear as a folder under the main domain virtual table.
  • Set: entity will appear as a separate virtual table.
    • directQuery: No performance impact on queries. The entity appears as a separate virtual table.
    • dual: Speeds up queries that only need entity attributes. There is no performance impact on other queries. Use for dimensions with 1M rows or less.
    • import: Can negatively impact the performance of queries that join across entities. Not recommended - when in doubt, always prefer dual.
Refreshing data in Power BI caches is the user’s responsibility. Scheduled refresh can be set up either with Honeydew, or in Power BI Service.To refresh using Honeydew, use Metadata Sync APIs. Every metadata sync triggers a data refresh.

​
Limitations

DAX limitations on a Direct Query apply to a Honeydew connection. Logic that would otherwise require advanced DAX can be built directly in Honeydew instead. That includes complex multi-step calculations, filtered or partial metrics, calculated fields, time intelligence and other advanced features. Every Honeydew metric is consumable with Power BI in a Direct Query mode. Refer to relation modeling on how to model 1:1 or many:many relations which are not directly supported. A 1:1 Power BI relationship with a preset cross-filtering direction typically maps to a Honeydew 1:many relation with same direction.

​
Case Sensitive Data

Power BI is case-insensitive when handling data values. This means that if a column contains values with different capitalizations (e.g., name, Name, and NAME), Power BI may randomly choose one spelling and align all other values to that spelling. When used as a filter, it might only send the selected spelling, potentially omitting other matching data.To prevent this, the best practice is to standardize the column’s case (e.g., converting all values to uppercase) within the data definition in the Honeydew source.

​
Import

Power BI can import data from dynamic datasets in Snowflake.

​
With a VIEW or a TABLE

Dynamic datasets can be set up deployment to Snowflake. The resulting VIEW or TABLE can be used for a Data Import.

​
With Snowflake Native Application

Create a blank query, and open advanced query editor to write the query below: To read from a dynamic dataset: 
let
    Source = Value.NativeQuery(
            Snowflake.Databases(
                "SnowflakeHost.snowflakecomputing.com",
                "VirtualDataWarehouse",
                [Role="Role"]
            ){[Name="Database"]}[Data],
            "
            CALL SEMANTIC_LAYER_ENTERPRISE_EDITION.API.SELECT_FROM_DYNAMIC_DATASET(
                'workspace',
                'branch',
                'dataset');
            ",
            null,
            [EnableFolding=false]
    )
in
    Source
Or create an ad-hoc semantic query: 
let
    Source = Value.NativeQuery(
            Snowflake.Databases(
                "SnowflakeHost.snowflakecomputing.com",
                "VirtualDataWarehouse",
                [Role="Role"]
            ){[Name="Database"]}[Data],
            "
            CALL SEMANTIC_LAYER_ENTERPRISE_EDITION.API.SELECT_FROM_FIELDS(
                -- workspace / branch
                'workspace', 'branch',
                -- domain (can be NULL)
                'domain',
                -- dimensions - can be ad-hoc expressions
                ['entity.attr', ...],
                -- metrics - can be named or ad-hoc expressions
                ['entity.metric', 'SUM(entity.measure)', ...],
                -- filters - can be named or ad-hoc expressions
                ['entity.filter', 'entity.attr > 0', 'entity.name like ''%cheese%''', ...]
            );
            ",
            null,
            [EnableFolding=false]
    )
in
    Source
Given Snowflake connection parameters (host, warehouse, role and database) and Honeydew workspace, a working branch in it (usually would be prod), and a dynamic dataset dataset. This calls a stored procedure to retrieve dynamic dataset data to Power BI.