Power BI

​

Direct Query

Power BI can connect 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.

​

Power BI Service

Power BI Service can connect to Honeydew in Direct Query mode using the Power BI Gateway.

​

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.

• Field hidden property.
• Field descriptions.
• Field format and semantic role (through metadata configuration).

​

Power BI-specific Metadata

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

​

Cross-Filtering and Referential Integrity

Honeydew supports cross filtering similar to Power BI.

The following table helps translate Power BI settings to Honeydew:

​

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.

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

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

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.

​

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.

​

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.