Demo from Tableau
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., 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 Tableau Reverse Engineering
Overview
The demo is based on the Tableau Project: Demo Semantic Hub
This project contains 2 Snowflake data sources and 2 workbooks.
| Workbook | 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 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 |
|---|---|---|
| Metric only in Workbook Two (Customer - Bill) | High-Value Customer | ([Is Current] AND [[Customer Status]="GOLD") |
| Metric only in Workbook One (Date - John) | Churned Customers | ({Effective To] < Today) |
| Identical metrics in each workbook | Net Sales Amount | [Sales Amount]-([Tax Amount]+[Discount Amount]) |
| Different metrics with same name in each workbook | Discount | ([Sales Amount]-[Discount Amount])/100 [Discount Amount]/100 |
Open Dashboard - Bill. Here are the reports:
Scenarios
Case 1: Metric only in Workbook (MiniDwSnowflakeRelational-Customer-Bill)
Metric Name: High-Value Customer
This metric is only defined in the MiniDwSnowflakeRelational-Customer-Bill workbook. Open the MiniDwSnowflakeRelational-Customer-Bill workbook:
Open the "High Value Customers" worksheet
Click Edit and expand the DIM_CUSTOMER in the Data tab (far left).
Right-click on High Value Customer and select Describe.
You now have the High Value Customer metric and the report (worksheet):
Now, open another browser tab in Tableau and go to the Demo Semantic Hub project:
Open the MiniDwSnowflakeRelational-Date-John workbook:
Open the Customer Churn Count By Year worksheet
Click Edit and expand the DIM_CUSTOMER in the Data tab (far left).
Note and see that this metric is not defined in the MiniDwSnowflakeRelational-Date-John 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.
RESULT OF THE MERGE WILL BE: As this metric is only defined in the DIM_CUSTOMER table in the MiniDwSnowflakeRelational-Customer-Bill 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 2: Metric only in Workbook (MiniDwSnowflakeRelational-Date-John)
Metric Name: Churned Customers
This metric is defined only in the MiniDwSnowflakeRelational-Date-John workbook and used in the Customer Churn Count By Year worksheet.
In the second browser tab where you opened the MiniDwSnowflakeRelational-Date-John workbook:
And you opened the ** Customer Churn Count By Year** worksheet
Click Edit and expand the DIM_CUSTOMER in the Data tab (far left).
Right-click on Churned Customers and select Describe.
You now have the Net Sales Amount metric and the report (worksheet):
Now, return to the first tab (MiniDwSnowflakeRelational-Customer-Bill) and see that this metric is not defined in the MiniDwSnowflakeRelational-Customer-Bill workbook:
RESULT OF THE MERGE WILL BE: As this metric is only defined in the DIM_CUSTOMER table in the "MiniDwSnowflakeRelational-Date-John" 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.
They are identical and in the same central fact, then merging the models should mean this metric is represented once.
Case 3: Identically named metrics in each workbook with same expressions and value in reports
Metric Name: Net Sales Amount
This metric is defined in each of the two workbooks. 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 workbook:
Open the "Net Sales by Customer Status" worksheet
Click Edit and expand the FACT_SALES_TXN in the Data tab (far left).
Right-click on Net Sales Amount and select Describe.
You now have the Net Sales Amount metric and the report (worksheet):
Here the expression is
[Sales Amount]-([Tax Amount]+[Discount Amount])
Returning to the second browser tab where you opened the MiniDwSnowflakeRelational-Date-John workbook:
And you open the Net Sales by Customer Status** worksheet
Note already the numbers are the same with the first tab but identically named Net Sales by Customer Status worksheet:
Click Edit and expand the FACT_SALES_TXN in the Data tab (far left).
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 workbook with different expressions and values in reports
This metric is defined in each of the two workbooks. 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 workbook:
Open the "Avg Customer Discount" worksheet
Click Edit and expand the FACT_SALES_TXN in the Data tab (far left).
Right-click on Discount and select Describe.
You now have the Net Sales Amount metric and the report (worksheet):
Here the expression is
([Sales Amount]-[Discount Amount])/100
Returning to the second browser tab where you opened the MiniDwSnowflakeRelational-Date-John workbook:
Open the "Avg Customer Discount" worksheet
Note, these number are already different from the first browser tab:
Click Edit and expand the FACT_SALES_TXN in the Data tab (far left).
Right-click on Discount and select Describe.
You now have the Net Sales Amount metric and the report (worksheet):
Here the expression is
([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" workbook: ([Discount Amount])/100 "Customer - Bill" 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
-
Import the model from Tableau
-
Open the semantic model catalog named Tableau Based Semantic Model:
-
Click BI Semantic Layers
-
Click Tableau
-
Search or Browse for the reports to import
You must already have a model imported from Tableau from which to pick.
Click OK
Once completed go to the Editor tab.
Example
Create and Populate the Semantic Model
We already have the Tableau Model imported, providing the semantic model catalog with connection information and browsing of reports.
Open the semantic model catalog named Tableau Based Semantic Model:
Click BI Semantic Layers:
Click Tableau:
Then select the two reports imported from Tableau and click OK.
After the import and merge finishes you will have:
Go to the Editor tab.
Right-click on the Tableau 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:
- Deployment Database: SEMANTIC_HUB
- Deployment Schema: SEMANTIC_HUB_TESTING
Under BI Deployment pick the following (from the pick list and browse):
- Tableau Server (Repository) and Cloud
- Demo Semantic Hub John
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 workbooks)
Case 3 also was properly merged:
So that there is only one Net Sales Amount even though it was defined (identically) in both workbooks.
Also, for Case 4, there was no merging possible as the expressions for Discount were different, thus Discount and Discount1 are produced and must be cleaned up (see below).
Expression Translations from Tableau to Snowflake
As one might imagine, the expressions are currently defined as Tableau expressions as {{ MIMM }} imported from Tableau and then merged. Since the source database is Snowflake, we need to translate the 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 Tableau:
The expression for Tableau 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.
AI Based Expression Translation
However, {{ MIMM }} provides an AI based expression translation feature.
You must
Have {{ MIMM }} running without a ModelBridgeList.xml file
Apply MetaIntegrationAI.jar to the java folder
Enable The AI Provider
aiProvider = "OPEN_AI"; aiApiKey = "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"; aiModel = "gpt-5";
Be sure to set it as default.
for the feature to work
Simply right-click on the MINI_RETAIL_DW CUSTOMER Snowflake model and select Convert Model Expressions:
The operation will run in the background:
When finished, the expression warnings are missing now:
we may again look at the expressions:
Here are the results of the expression translation:
DIM_CUSTOMER -> Churned Customers
DIM_CUSTOMER -> High Value Customers
FACT_SALES_TXN -> Discount
FACT_SALES_TXN -> Discount1
("Discount Amount")/100
Hide or delete this field
FACT_SALES_TXN -> Net Sales Amount
Set default aggregation to Sum
Clean Up Duplicate Facts
Now, recall that there was a metric named Discount that was in each original source Tableau workbook. They have the same name and in the same central fact, yet they have different expressions:
- "
Date - John" workbook: ([Discount Amount])/100 "Customer - Bill" workbook: ([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 Discount1.
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 Tableau
Now it is time to deploy the semantic model back to Tableau. In this case, {{ MIMM }} will connect to Tableau using connection information from an already imported model (from Tableau) and generate a connection in Tableau 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 Tableau Server and Cloud > Deploy.
You may look at the log to be sure it worked:
Then connect to Tableau. 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 > Workbook Using This Data Source:
Sign in to the database if needed.
Create one of the reports and compare numbers.
