Demo from Power BI
These scenarios are based upon Examples of the use of the semantic hub and semantic model catalog capabilities in the product.
One example is the ability for the intelligent feature of the semantic model catalog to merge various models with related metadata which are derived from a reporting tool (e.g., Tableau) and:
- Include various tables, columns, metrics and joins from each of the complementary source report models
- Identify and isolate differences in definitions between these, such as two metrics with the same name but with different expressions and thus meeting.
Self Service Power BI Reverse Engineering
Overview
The demo is based on the Power BI Project: Demo Semantic Hub
This project contains 2 Snowflake data sources and 2 workbooks.
| Workbook | Data Source |
|---|---|
| ● MiniDWSnowflakeRelational-Date | ● MINI_RETAIL_DW Data Snowflake |
| ● MiniDWSnowflakeRelational-Customer | ● MINI_RETAIL_DW Customer Snowflake |
In this scenario we reverse engineer the two workbooks from Self Service BI sources (initially Tableau and Azure Power BI) and demonstrate how metrics (defined in the BI tools) are merged in several cases.
| Use Case | Metric Name | Query / Formula |
|---|---|---|
| Identical metrics in each workbook | Net Sales Amount | [Sales Amount]-([Tax Amount]+[Discount Amount]) |
| Metric only in Workbook One (Date or John) | Churned Customers | ({Effective To] < Today) |
| Metric only in Workbook Two (Customer) | High-Value Customer | ([Is Current] AND [[Customer Status]="GOLD") |
| Different metrics with same name in each workbook | Discount | ([Sales Amount]-[Discount Amount])/100 [Discount Amount]/100 |
Case 1: Identical metrics in each workbook
Metric Name: Net Sales Amount
These metrics are defined identically in the two workbooks and their reports in the two Tableau reports:
"Date" Workbook
Net Sales Amount metric and report (worksheet)
Result of Merge: They are identical and in the same central fact, then merging the models should mean this metric is represented once.
Case 2: Metric only in Workbook One (Date)
Metric Name: Defunct Customers
This metric is defined only in the Date workbook and used in the Customer Churn Count By Year:
but this metric is not defined in the Customer workbook:
Result of Merge: As this metric is only defined in the DIM_CUSTOMER table in the "Date" workbook, then merging the models should mean this metric is represented once, but available for reporting with the the fields from either of the merged workbooks.
Case 3: Metric only in Workbook One (Date)
Metric Name: High-Value Customer
This metric is defined only in the Customer workbook and used in the Customer Churn Count By Year:
but this metric is not defined in the Customer workbook:
Result of Merge: As this metric is only defined in the DIM_CUSTOMER table in the "Customer" workbook, then merging the models should mean this metric is represented once, but available for reporting with the the fields from either of the merged workbooks.
Case 4: Identically named metrics in each workbook with different expressions
Metric Name: Net Sales Amount
This metric is defined in each of the two workbooks. However, they are defined differently, and thus reports using each one produces conflicting results.
"Customer" Workbook
Discount
"Date" Workbook
Discount
Result of Merge: They have the same name and in the same central fact, yet they have different expressions:
- "Date" workbook: ([Discount Amount])/100
- "Customer" workbook: ([Sales Amount] - [Discount Amount])/100
Thus, the semantic model cannot be merged such that there is only one metric named "Discount". Instead, Discount and Discount_1 are created, one for each of the expressions.
Merging These Two Reports in the Semantic Model Catalog
Steps
Open the Semantic Model Catalog
Click the BI Tool reverse engineering
Search or Browse for the reports to import
You must already have a model imported from Power BI from which to pick.
Click IMPORT
Once completed go to the Editor tab.
Example
Open the Power BI Based Semantic Model and click BI Semantic Layers in the import method section.
Then select the two reports imported from Power BI and click OK.
Go to the Editor tab.
Right-click the MITI_RETAIL_DW CUSTOMER Snowflake (internal semantic) model and select Open Diagram.
Let's now analyze the Metrics
Expression Translations from Power BI (DAX) to Snowflake
CUSTOMER -> CHURNED CUSTOMERS
CASE WHEN CUSTOMER.EFFECTIVE_TO < CURRENT_DATE() THEN 'YES' ELSE 'NO' END
CUSTOMER -> CUSTOMER_SK Unhide this field
CUSTOMER -> HIGH_VALUE_CUSTOMER CASE WHEN CUSTOMER.CUSTOMER_STATUS = 'Gold' AND CUSTOMER.IS_CURRENT = 1 THEN 'Yes' ELSE 'No' END
FACT_SALES -> CustomerCount count(distinct CUSTOMER.CUSTOMER_SK)
FACT_SALES -> DISCOUNT
FACT_SALES -> DISCOUNT 1 (FACT_SALES.SALES_AMOUNT-FACT_SALES.DISCOUNT_AMOUNT)/100
FACT_SALES -> NET_SALES_AMOUNT FACT_SALES.SALES_AMOUNT-(FACT_SALES.DISCOUNT_AMOUNT+FACT_SALES.TAX_AMOUNT)
CUSTOMER > DImensions -> CHURNED CUSTOMERS
CASE
WHEN CUSTOMER.EFFECTIVE_TO < CURRENT_DATE() THEN 'YES'
ELSE 'NO'
END
CUSTOMER -> HIGH_VALUE_CUSTOMER
CASE
WHEN CUSTOMER.CUSTOMER_STATUS = 'Gold'
AND CUSTOMER.IS_CURRENT = 1
THEN 'Yes'
ELSE 'No'
END
Facts > CUSTOMER -> CUSTOMER_SK
Unhide this field
FACT_SALES > Metrics -> CustomerCount
count(distinct CUSTOMER.CUSTOMER_SK)
FACT_SALES > Facts -> DISCOUNT
Delete this field
FACT_SALES -> DISCOUNT 1
(FACT_SALES.SALES_AMOUNT-FACT_SALES.DISCOUNT_AMOUNT)/100
FACT_SALES -> NET_SALES_AMOUNT
FACT_SALES.SALES_AMOUNT-(FACT_SALES.DISCOUNT_AMOUNT+FACT_SALES.TAX_AMOUNT)