Tutorial: Extend an Explore model with custom columns

When you first start using Explore, the Guidewire-created tables, models, Liveboards, and Answers include fields that Guidewire manually selected for analytics and reporting use cases. They don’t include:

Your InsuranceSuite extension fields and tables
Other out-of-the-box InsuranceSuite fields and tables that are specific to your use case

Those fields are however available in Data Studio, in the replicated data tables and dynamically-generated datasets. If you want to use those fields in Explore, follow this step-by-step tutorial to extend a Guidewire-created model with additional columns from Data Studio, and then add the columns to a Liveboard.

Note: If you aren’t working with Guidewire-created models, see Add datasets from Data Studio to Explore instead.

In this tutorial, you will:

Create a new version of a dataset in Data Studio with your extension columns.
Publish the dataset to Explore.
Create a new version of the Explore model that includes the extended dataset.
Create a new version of a Liveboard and add your extension column to a chart.

Before you begin

You must have access to Data Studio and Explore
You must belong to one or more Explore groups that have these roles:
- Connection Management
- Data Model Management
- Share With All
Read Get started with data sources to understand how datasets are published from Data Studio to Explore and are prepared for visualization.

Instructions

Step 1: Determine what to extend

About this task

First you must determine your goal: You can browse Explore models to determine what's missing for your use case. Then, determine which column you want to add from Data Studio. Since models are made up of multiple tables, you must finally determine which specific table you want to add the column to.

Procedure

Determine what's missing from the Explore model

Use the Explore documentation to identify which pre-configured Explore model is relevant to your reporting and analytics use case.
In this tutorial, we’ll use claims financials as an example use case. The relevant content is Explore for Claims Financial Insights, and specifically the GW - Claim Loss Financial - Calendar Year model.

Look at the existing attributes and measures in the Explore model and consider what might be missing for your use case. You can view a list of the model's columns in one of the following ways:

Option	Description
Model data dictionary	From the Explore documentation, open the data dictionary for the GW - Claim Loss Financial - Calendar Year model.
Explore app	In Explore's top navigation bar, use the workspace selector to go to the Data workspace, then select Data objects > Models > GW - Claim Loss Financial - Calendar Year. Select Columns to see the available fields. To see visualizations that use the model, select Dependents, then select the Calendar Year Loss Analysis Liveboard.

In this tutorial, we’ll decide to add attributes that indicate fraud.

Browse available columns in Explore

Before starting any extension work in Data Studio, double-check that a relevant column isn’t already available in Explore. The dimension tables that make up the model might contain more columns that aren’t included in the final model. And, there are more dimension tables in Explore that aren’t joined to the model at all.
1. In Explore's top navigation bar, use the workspace selector to go to the Data workspace, then select Data objects > Tables.
2. Look for a table that might contain a relevant column, then open it.
3. Browse its Columns. To search the columns, select More > Edit TML > Find. For example, the efr_dim_claim table has additional columns that aren’t in the model, such as tn_weather. But it doesn’t have a column that indicates fraud.
If the column you want is available in Explore, you don’t need to use Data Studio. Skip to Step 6: Create a new version of the model.

Determine which column to add from Data Studio

In Data Studio, look at your attributes and measures from InsuranceSuite. Identify which ones will be useful to add to the Explore model for your use case:
1. Open the Data Studio data dictionary. See the Data Studio guide.
2. Under the Claims category, browse Dimension or Fact datasets. In this tutorial, we’ll select the tn_fraudindicator_ext column from the dd_loss_claim_trv dataset.

Determine which specific table to extend

Open the model: In Explore's top navigation bar, use the workspace selector to go to the Data workspace, then select Data objects > Models > GW - Claim Loss Financial - Calendar Year.
Select Joins.
A diagram shows all dimension tables that are joined to the fact table. For this tutorial, we'll decide to extend efr_dim_claim. It's relevant for the tn_fraudindicator_ext column, since the column originally comes from the dd_loss_claim_trv dimension in Data Studio.

Step 2: Create a new extended dataset in Data Studio

Procedure

Open Data Studio, then go to the Inventory.
Clear any filters, then search for the name of the table from Explore that you want to extend: efr_dim_claim
On the efr_dim_claim dataset card, select the ellipsis, then Save as.
Edit the query name and description, then select Save.
1. Business name: Explore Claim Dimension V1
2. Name: efr_dim_claim_v1
3. Description: Claim dimension extended with fraud indicator
Find the query by filtering by Types > Queries, and States > My Work.

Open the query, then edit its SQL to add the tn_fraudindicator_ext column to the SELECT statement:

SELECT
  ...
  claim.dateformretbyemp AS dateformretbyemp,
  claim.tn_fraudindicator_ext AS tn_fraudindicator_ext,  //Column inserted alphabetically
  claim.is_furthertreatment AS is_furthertreatment,
  ...
FROM
  dd_loss_Claim_trv.dd_loss_claim_trv Claim
  LEFT JOIN dd_loss_user_trv.dd_loss_user_trv User ON claim.assigneduserid = user.id AND   
        claim.updatetime >= user.row_eff_dts AND claim.updatetime < user.row_exp_dts
  LEFT JOIN dd_loss_address_trv.dd_loss_address_trv LossLocationID ON losslocationid.id = 
        claim.losslocationid AND claim.updatetime >= losslocationid.row_eff_dts AND  
        claim.updatetime < losslocationid.row_exp_dts

Some queries include a UNION clause that generates an additional row with 'nokey' and '?' values. These placeholder values are used downstream in case the dimension doesn’t exist. You must add tn_fraudindicator_ext to the UNION clause in order to pass SQL validation, as shown in the following example:

Important: Make sure the columns are in the same order in the UNION clause and in the SELECT statement. Differences between the two clauses cause an error when you run the query.
```
UNION ALL
SELECT
  ...
  NULL AS dateformretbyemp,
  CAST('?' AS STRING) AS tn_fraudindicator_ext,          //Column inserted alphabetically
  CAST('?' AS varchar (1)) AS is_furthertreatment,
  ...
```
Run the query to ensure there are no errors.
Select Actions > Save.

Tip: To learn more about querying in Data Studio, see the Data Studio guide.

Step 3: Publish the query to Explore

About this task

To publish the query to Explore, you can add it to the original publication that Guidewire created.

Procedure

Find the publication name and properties

Go to the Data Studio Inventory, and search for the name of the original table from Explore: efr_dim_claim.
On the efr_dim_claim dataset card, select the ellipsis, then Details.
Take note of the following information about the dataset:
1. Under Publications, note the publication it belongs to.
2. Under Dataset options, note the Time Basis, Primary Keys, and Order By.

Publish the query

Return to the query you created (efr_dim_claim_v1), then select Actions > Publish.
Select Existing publication, then select the publication Name that the original dataset belongs to: efr_loss_v0_1_0.
Configure the other deployment properties so that they match the original dataset:
1. Primary key: skey
2. Time basis: Current
3. Order by: updatetime
Select Publish.

Step 4: Add the new dataset to Explore

About this task

You must update the Explore connection to include the new dataset. Then, it becomes available in Explore.

Before doing these steps, you might have to wait up to an hour for the published dataset to appear in the connection editor. The exact latency depends on volumes and the number of publications.

Procedure

In Explore's top navigation bar, use the workspace selector to go to the Data workspace, then select Connections.
Select the connection name to open it, then select Edit connection.
In the tables list, select efr_dim_claim_v1. It’s usually listed under explore > public.
Select all of the columns to add the entire dataset to the Explore connection.
Select Update connection.
Now the dataset is a table in Explore. Find it in the Data workspace under Data objects > Tables.

Step 5: Join the table

About this task

You must join the new dimension table to the fact table so that Explore understands the relationship. By creating joins on the table level, you only have to create them once; Any Explore models that you or others create will inherit those joins, so they're always accurate. You can also check the column properties in the table and adjust if needed. Again, any models created from the table will inherit the column properties. Usually, it's best to check if the Column type and Aggregation are accurate. Other column properties are best edited later at the model level.

Procedure

In the Explore Data workspace, select Data objects > Tables.
Select the name of the efr_dim_claim_v1 table.
To create a join, select Joins > Add join, then input:
1. Join name: fact_to_claim_dim
2. Table 2: efr_fact_loss_calendaryear
3. Column from Table 1: skey
4. Column from Table 2: fk_claimid_skey
5. Join type: Left Outer
6. Cardinality: Many:1
7. Select Save.
Under Columns, edit the Column type and Aggregation properties if needed. For details, see Column properties.
Select Save changes.

Step 6: Create a new version of the model

About this task

To add the new column to the model, you must create a new version of the model. You don’t have permissions to edit the model that Guidewire created.

Procedure

In new browser tabs, open the following pages to reference later:
1. From the Explore data dictionary documentation, open the dictionary for the GW - Claim Loss Financial - Calendar Year model.
2. Open the original model: In Explore, go to the Data workspace, then select Data objects > Models > GW - Claim Loss Financial - Calendar Year.
In Explore, go to the Data workspace, then select Create new > Model.
Select Build your own with cloud data > Next, then select your connection and select Next.

Add tables

Open the browser tab with the original model. Look at the Joins to see which fact and dimension tables it includes.
Add those fact and dimension tables to the new model. Make sure to add the new table you created in Data Studio, efr_dim_claim_v1, instead of the original, efr_dim_claim.
1. Drag and drop each table. To find a specific table, use the search bar, filter by tag, or sort the list.
2. The joins, which were created on the table level, appear as recommendations in the model diagram. Select the join icon , then select Accept recommended join.
  
  Important: If you join in the model but not in the table, it creates a chasm trap. Chasm traps prevent you from viewing underlying data in an Answer.

Add columns

In the model data dictionary, reference the original model Source table and Source columns. Follow steps 7-8 to add each column to the model, along with your new tn_fraudindicator_ext column.
Select Columns, then in the sidebar, search for a source column name.
1. Click and drag to add the column. To add all of the columns from a table, click and drag the table name.
2. Rename existing columns so they match the Model column name in the dictionary. Give your new column a business-friendly name. For example, Fraud Indicator.
If the dictionary Source Column says Formula, you won’t find it in the column list. Instead, create a formula:
1. Select the Formula tab, then Add Formula.
2. Name the formula so it matches the Model column name from the dictionary. For example, Accounting Period.
3. Copy the Formula expression from the data dictionary, then paste it into the formula editor.
4. Select Save, then the formula appears as a column in the Columns tab.
After adding columns, check for any that are highlighted in red, which means there's a duplicate column name. Remove duplicate columns or give them unique names.
Select Save changes, then name the model Claim Loss Financial - Calendar Year - V1.
Select Exit data model.

Step 7: Share the model

Procedure

With your new model open, select More > Share.

Add users or groups and specify their permissions. Choose from:

Can View	Can view the model’s data.
Can Edit	Can rename, modify, or delete the model. Can add or remove columns.

Results

Anyone you shared the model with can now use it to create Answers and Liveboards.

Step 8: Use the new column in a Liveboard

About this task

To add the new column to an out-of-the-box Liveboard, you must create a new version of the Liveboard. You don’t have permissions to edit the Liveboard that Guidewire created.

Procedure

Create a copy of the Liveboard

Open the original model: In Explore, go to the Data workspace, then select Data objects > Models > GW - Claim Loss Financial - Calendar Year.
Select Dependents, then select the Calendar Year Loss Analysis Liveboard.
Select More > Make a copy, then name the Liveboard Calendar Year Loss Analysis V1.

Point the Liveboard to the new model

On the new Liveboard, select More > TML > Edit.
Select Find > Find and replace. Find all instances of the model name and change them to the new model. For example, there might be ten instances of the following code:
```
tables:
  id: GW - Claim Loss Financial - Calendar Year
  name: GW - Claim Loss Financial - Calendar Year
```
Replace it with:
```
tables:
  id: Claim Loss Financial - Calendar Year - V1
  name: Claim Loss Financial - Calendar Year - V1
```
Select Validate, the Publish.

Add the new column to a visualization

On the new Liveboard, find a visualization where you want to use the tn_fraudindicator_ext column. Select More > Edit.

Add the column to the search query:

Claim Number
Policy Number
Reported Date daily
Loss Cause
Loss Date yearly
Claim State
Fraud Indicator              // The new column
MTD Indemnity Paid
MTD Gross Expense Paid
MTD Total Reserve Change
MTD Total Gross Recovery

Select Go to update the visualization, then Save and Close.

Next steps

Use the new model to create more visualizations or create new Liveboards.