Create a Logical View for Your Semantic Model

Learning Objectives

After completing this unit, you’ll be able to:

• Describe what a logical view is.
  
• Understand when and how to create a logical view with a join
  
• Understand when and how to create a logical view with a union
  

Explore Logical Views

Connecting tables with relationships keeps each table distinct as its own object. If you instead want to combine two or more tables into a single new object, you can use a logical view. This can be useful when the data should be more tightly coupled than with a relationship. The logical view is a separate object within the semantic model, with its own fields and it’s queried separately as its own object by the query service.

There are two ways to combine tables in a logical view: joins and unions.

• Join: Combines multiple tables into a single table based on a specific join type, adding columns to widen the data.
  
• Union: Stacks multiple tables into a single table based on a shared structure, adding rows to lengthen the data
  

Relationships are eventually resolved into joins. A logical view with a join should only be used when you need the behavior of a specific join type.

Unions can't be created with relationships. If your data is in multiple tables that need to be appended, a logical view with a union is required.

Create a Logical View Using a Join

If there's a reason to force a specific join type between two objects, you can use a logical view and hard-code the join type. This ensures the exact behavior of the join every time the data is queried.

• Left join: A left join returns all records from the left DMO and the matched records from the right DMO.
  
• Inner join: An inner join returns all records that have matching values in two DMOs.
  
• Right join: A right join returns all records from the right DMO and the matched records from the left DMO.
  
• Outer join: An outer join, also called a full join, returns all records from both DMOs.
  

For more information about the types of joins and examples, see Using the Join Clause for Insights in Salesforce Help.

Joins look at the value of the fields in the join clause and bring in additional columns of data when the join clause is true. This changes the structure of the joined table by adding more columns of data across the existing rows or records.

Imagine you're interested in products that were purchased. You have a data object for Product and data object for Sales, and both contain a Product ID field. A logical view with an inner join of these tables would contain only purchased products.

1. In the Semantic Model Builder, from the New dropdown menu, select Logical View.
   
   
2. In the Create a Logical View window enter a unique name, API name, and optional description.
   
   
3. Click Save.
   
4. Next, choose the first data object for the logical view (Products).
   
   • You can select a DMO, DLO, or Calculated Insight.
     
   • You can include all ‌of the fields, or select specific ones to include.
     
     
5. Click Save.
   
6. To add a join, select the object, and then click the plus sign.
   
   
7. From the dropdown menu, select Join Object.
   
8. Select the object that you want to join and then click Save.
   
   
9. Select the join type using the join icons. For this example, select Inner Join.
   
   
10. Under Select Matching Data Object Fields, click the plus sign and select which fields should be used in the join clause. In this case, connect Product and Sales, by Product ID.
    
11. Under the left object, search for the field name.
    
12. Under the right object, search for the corresponding field name.
    
    
13. Click Add. After you add your fields, the bottom pane shows a preview of the logical view and a line connecting the two objects indicates that the join is successful.
    
    
14. Click Apply.
    

The logical view is added to your semantic model and appears with its own icon.

You can now use this logical view just like any other data object in the model.

Create a Logical View Using a Union

As we just saw, joins bring together more columns of data for each record, making the data wider. This is the same final behavior as a relationship.

In contrast, unions combine records by stacking the tables according to their shared column structure, essentially adding more rows to the new combined table. For example, you may have marketing data that tracks lead generation information across three different channels: email, social, and digital ads. Each channel captures the same information (the same columns) but from different systems, so there's a different table per channel. A union would append all three tables together based on their shared column structure.

1. In the Semantic Model Builder, from the New dropdown menu, click Logical View.
   
2. In the Create a Logical View window enter a unique name, API name, and optional description. Click Save.
   
3. Next, choose the first data object for the union and then click Save.
   
4. To add a union, select the object, and then click the plus sign.
   
5. From the dropdown menu, select Create Union.
   
   
6. In the Select Data Objects for Union dialog, select the first object for the union and then click Save. You can select up to nine objects. The merged fields in the union receive metadata from the first added object.
   
   A preview of the selected object and fields appears.
   
   
7. When all data objects have been added, click Apply to add the new union to the logical view.
   

The union appears in the Semantic Model Builder Canvas.

In this unit, you learned about what logical views are and their different use cases, how to create a logical view with a join, and how to create a logical view with a union. In the next unit, you learn about the different ways you can create calculated fields in your semantic model.