Demo from Power BI
These scenarios are based upon several 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., Power BI) 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 source Power BI semantic models and 2 reports.
| report | Data Source |
|---|---|
| ● MiniDWSnowflakeRelational-Date-John | ● MINI_RETAIL_DW Data Snowflake |
| ● MiniDWSnowflakeRelational-Customer-Bill | ● MINI_RETAIL_DW Customer Snowflake |
In this scenario we reverse engineer the two reports from Self Service BI sources (initially Azure Power BI) and demonstrate how metrics (defined in the BI tools) are merged in several cases.
| Use Case | Metric Name | Query / Formula |
|---|---|---|
| Metric only in report Two (Customer - Bill) | High-Value Customer | ([Is Current] AND [[Customer Status]="GOLD") |
| Metric only in report One (Date - John) | Churned Customers | ({Effective To] < Today) |
| Identical metrics in each report | Net Sales Amount | [Sales Amount]-([Tax Amount]+[Discount Amount]) |
| Different metrics with same name in each report | Discount | ([Sales Amount]-[Discount Amount])/100 [Discount Amount]/100 |
Open MiniDwSnowflakeRelational-Customer-Bill. Here are the reports:
Scenarios
Case 1: Metric only in report (MiniDwSnowflakeRelational-Customer-Bill)
Metric Name: High-Value Customer
This metric is only defined in the MiniDwSnowflakeRelational-Customer-Bill report. Open the MiniDwSnowflakeRelational-Customer-Bill report:
Click the "High Value Customers" table and click Edit and expand the CUSTOMER table in the Data tab (far right).
As the expressions are defined in the associated Power BI semantic model, click the Open Semantic Model menu item, click Editing in the upper right and select High Value Customer **.
You can see the expression now.
Now, open another browser tab in Power BI and go to the Demo Semantic Hub project:
Open the MiniDwSnowflakeRelational-Date-John report, click Edit and expand the CUSTOMER table in the Data tab (far right).
Note that this metric is not defined in the MiniDwSnowflakeRelational-Date-John report, then merging the models should mean this metric is represented once, but available for reporting with the the fields from either of the merged reports.
RESULT OF THE MERGE WILL BE: As this metric is only defined in the DIM_CUSTOMER table in the MiniDwSnowflakeRelational-Bill report, then merging the models should mean this metric is represented once, but available for reporting with the the fields from either of the merged reports.
Case 2: Metric only in report (MiniDwSnowflakeRelational-Date-John)
Metric Name: CHURNED_CUSTOMERS
This metric is defined only in the MiniDwSnowflakeRelational-Date-John report and used in the Customer Churn Count By Year worksheet.
In the second browser tab where you opened the MiniDwSnowflakeRelational-Date-John report:
To see t his metric, click on the ** Customer Churn Count By Year table, click Edit, expand the CUSTOMER in the Data tab (far left) and Right-click on CHURNED_CUSTOMERS**
As the expressions are defined in the associated Power BI semantic model, click the Open Semantic Model menu item, click Editing in the upper right and select CHURNED_CUSTOMERS.
You now have the CHURNED_CUSTOMERS metric expression:
Now, return to the first tab (MiniDwSnowflakeRelational-Customer-Bill) and see that this metric is not defined in the MiniDwSnowflakeRelational-Customer-Bill report:
RESULT OF THE MERGE WILL BE: As this metric is only defined in the CUSTOMER table in the MiniDwSnowflakeRelational-Date-John report, then merging the models should mean this metric is represented once, but available for reporting with the the fields from either of the merged reports.
Case 3: Identically named metrics in each report with same expressions and value in reports
Metric Name: Net Sales Amount
This metric is defined in each of the two reports. In addition, they are defined identically, and thus reports using each one produces identical results.
In the first browser tab where you opened the MiniDwSnowflakeRelational-Customer-Bill report:
Click the "Net Sales by Customer Status" table, click Edit and expand the FACT_SALES table in the Data tab (far right):
As the expressions are defined in the associated Power BI semantic model, go to the browser tab with the Open Semantic Model, click Editing in the upper right and select NET_SALES_AMOUNT.
You now have the NET_SALES_AMOUNT metric and the report (worksheet):
Here the expression is
NET_SALES_AMOUNT = 'FACT_SALES'[SALES_AMOUNT]-('FACT_SALES'[DISCOUNT_AMOUNT]+'FACT_SALES'[TAX_AMOUNT])
Returning to the second browser tab where you opened the MiniDwSnowflakeRelational-Date-John report:
Click the "Net Sales by Customer Status" table, click Edit and expand the FACT_SALES table in the Data tab (far right):
As the expressions are defined in the associated Power BI semantic model, go to the browser tab with the Open Semantic Model, click Editing in the upper right and select NET_SALES_AMOUNT.
You now have the NET_SALES_AMOUNT metric and the report (worksheet):
Here the expression is
NET_SALES_AMOUNT = 'FACT_SALES'[SALES_AMOUNT]-('FACT_SALES'[DISCOUNT_AMOUNT]+'FACT_SALES'[TAX_AMOUNT])
Which is the same.
RESULT OF THE MERGE WILL BE: They have the same name and in the same central fact, as well as the same expressions. Thus, the semantic model will simply show only one Net Sales Amount.
Case 4: Identically named metrics in each report with different expressions and values in reports
This metric is defined in each of the two reports. However, they are defined differently, and thus reports using each one produces conflicting results.
In the first browser tab where you opened the MiniDwSnowflakeRelational-Customer-Bill report:
Click the "Avg Customer Discount"" text report, click Edit and expand the FACT_SALES table in the Data tab (far right) and click on the DISCOUNT metric:
As the expressions are defined in the associated Power BI semantic model, go to the browser tab with the Open Semantic Model, click Editing in the upper right and select DISCOUNT.
You now have the DISCOUNT metric and the report (worksheet):
Here the expression is
DISCOUNT = ('FACT_SALES'[SALES_AMOUNT]-'FACT_SALES'[DISCOUNT_AMOUNT])/100
Returning to the browser tab where you opened the Semantic model for the MiniDwSnowflakeRelational-Date-John report and click the DISCOUNT metric there:
Here the expression is
DISCOUNT = 'FACT_SALES'[DISCOUNT_AMOUNT]/100
RESULT OF THE MERGE WILL BE: They have the same name and in the same central fact, yet they have different expressions:
- "Date - John" report:
DISCOUNT = 'FACT_SALES'[DISCOUNT_AMOUNT]/100 - "Customer - Bill" report:
DISCOUNT = ('FACT_SALES'[SALES_AMOUNT]-'FACT_SALES'[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
-
Import the model from Power BI
-
Open the semantic model catalog named Power BI Based Semantic Model:
-
Click BI Semantic Layers
-
Click Power BI
-
Search or Browse for the reports to import
You must already have a model imported from Power BI from which to pick.
-
Click OK
-
Once completed go to the Editor tab.
Example
Create and Populate the Semantic Model
We already have the Power BI Model imported, providing the semantic model catalog with connection information and browsing of reports.
Open the semantic model catalog named Power BI Based Semantic Model:
Click BI Semantic Layers:
Click Power BI and then select the two Power BI semantic models (data set connections) imported from Power BI and click OK.
After the import and merge finishes you will have:
Go to the Editor tab.
Right-click on the Power BI Based Semantic Model in the root of the Catalog and select Open Diagram:
Click START EDITING.
Update properties for this model
Go to the model at the bottom of the Catalog panel:
And be sure to specify the Connection that is in the pick list.
Then go to the Connection itself at the top of the Catalog panel:
Scroll down to the Database Deployment Parameters and enter the following:
- Name: FATBFRW-CM67523.SNOWFLAKECOMPUTING.COM
-
i.e., remove the "/" and everything after it.
-
Deployment Database: SEMANTIC_HUB
- Deployment Schema: SEMANTIC_VIEWS_TESTING
Under BI Deployment pick the following (from the pick list and browse):
- Microsoft Azure Power BI Service (Repository)
- Demo Semantic Hub
Analyze the Metrics
Now, the Case 1 and Case 2 scenarios are properly merged:
So that CHURNED_CUSTOMERS and HIGH VALUE CUSTOMER are merged in (when then only existed in one of the two reports)
Case 3 also was properly merged:
So that there is only one NET_SALES_AMOUNT even though it was defined (identically) in both reports.
Also, for Case 4, there was no merging possible as the expressions for DISCOUNT were different, thus DISCOUNT and DISCOUNT 1 are produced and must be cleaned up (see below).
Expression Translations from Power BI to Snowflake
As one might imagine, the expressions are currently defined as Power BI expressions as {{ MIMM }} imported from Power BI and then merged. Since the source database is Snowflake, we need to translate the DAX expressions to a format supported by that database. Click on the connection to see the database type:
You may simply follow the red warning circles to locate and expression which needs translation to Snowflake. Go to the Churned Customers dimension:
Click on Edit next to the two expression dialects presented, Snowflake and Power BI:
The expression for Power BI shows to be imported and is presented here. Also, you may use the pick list at the right to edit your expression for the current dialect for Snowflake.
Manual Expression Translation
{{ MIMM }} provides an expression translation editor. In the editor, you may drag and drop any table or column name that you need to create the expression that is equivalent for Snowflake. E.g., for CHURNED CUSTOMERS, you should use:
IFF("EFFECTIVE_TO" < CURRENT_DATE, 'YES', 'NO')
So, you type "IIF (" and then drag and drip the EFFECTIVE_TO column:
and so forth for the expression:
Here are the results of the expression translation:
CUSTOMER > CHURNED CUSTOMERS
IFF("EFFECTIVE_TO" < CURRENT_DATE, 'YES', 'NO')
CUSTOMER -> HIGH_VALUE_CUSTOMER
CASE
WHEN CUSTOMER.CUSTOMER_STATUS = 'Gold'
AND CUSTOMER.IS_CURRENT = 1
THEN 'Yes'
ELSE 'No'
END
Facts > CUSTOMER -> CUSTOMER_SK
Rename to CUSTOMER_SK and Unhide this field
FACT_SALES > Facts -> DISCOUNT
(FACT_SALES.SALES_AMOUNT-FACT_SALES.DISCOUNT_AMOUNT)/100
FACT_SALES -> DISCOUNT 1
Delete this field
FACT_SALES -> NET_SALES_AMOUNT
FACT_SALES.SALES_AMOUNT-(FACT_SALES.DISCOUNT_AMOUNT+FACT_SALES.TAX_AMOUNT)
Clean Up Duplicate Facts
Now, recall that there was a metric named DISCOUNT that was in each original source Power BI report. They have the same name and in the same central fact, yet they have different expressions:
- "
Date - John" report: ([Discount Amount])/100 "Customer - Bill" report: ([Sales Amount] - [Discount Amount])/100
Thus, the metric 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.
Now, we need to choose. The one that is most correct is DISCOUNT. So, we delete DISCOUNT 1.
Click SAVE and STOP EDITING.
Deployment to Snowflake
Now it is time to deploy the semantic model to Snowflake. In this case, {{ MIMM }} will connect to Snowflake using connection information from an already imported model (from Snowflake) and generate a semantic view to query against.
Go to Deploy as an option on the Editor tab and pick the model to deploy.
Then click Snowflake Database > Deploy.
You may look at the log to be sure it worked:
Then connect to Snowflake. Go to the location specified earlier when updating the semantic model properties and list the Semantic Views:
Click on the model name you created:
The Semantic View Definition is deployed here.
Chat with Cortex AI on Snowflake Semantic Views
Now, click in the upper right options icon and select Open in Cortex Analyst:
Click Explain the Data Set, and Cortex will provide details and suggest queries. Click on the first query and note the result:
Deployment to Power BI
Now it is time to deploy the semantic model back to Power BI. In this case, {{ MIMM }} will connect to Power BI using connection information from an already imported model (from Power BI) and generate a connection in Power BI to the semantic view that we already deployed to Snowflake to query against.
Go to Deploy as an option on the Editor tab and pick the model to deploy.
Then click Power BI Server and Cloud > Deploy.
You may look at the log to be sure it worked:
Then connect to Power BI. Go to the location specified earlier when updating the semantic model properties and list the Connections:
Then click the connection name to open and then click New > report Using This Data Source:
Sign in to the database if needed.
Create one of the reports and compare numbers.
