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
Power BI can query Honeydew directly from the newly created connection. However, fields will lack associated metadata.
With Metadata Sync, Honeydew synchronizes the following metadata to Power BI:
- 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
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:
Power BI Referential Integrity | Power BI Cross-Filtering | Honeydew Join Type (from many to 1) | Honeydew Cross-Filtering |
---|---|---|---|
No (Power BI default) | Single (Power BI default) | LEFT | One-to-many |
No | Both | LEFT (Honeydew default) | Both (Honeydew default) |
Yes | Single | INNER | One-to-many |
Yes | Both | INNER | Both |
Note that that the default relationship settings in Honeydew are different than default Power BI settings.
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.