Person: In Looker, Explores can be thought of as a set of tables with predefined join logic. As a LookML developer, you define and curate Explores for users at your organization to analyze data and answer their business questions. Within the model file of a project, you can define Explores similarly to how you define dimensions and measures by typing the word "Explore" followed by a colon and giving it a name such as order_items. Unlike dimensions and measures, which can be named however you like, the name of the Explore must be the name of an actual view file. For example, in the training_ecommerce.model file, you can define an Explore called order_items that references the order_items.view file. To understand how to define Explores, let's review the key parameters within the Explore definitions in a model file. First, the Explore name establishes the base view, meaning that is the central view for analysis in the Explore. Other views joined to this Explore provide additional or supplemental analysis capabilities. For example, the base view of an e-commerce company could be named order_items, which would provide details on individual orders. Since all ordered items are associated with a user, your business users may also want to pull details for the users to track their engagement and order history. Now, to support this, you need to define a join in the Explore definition using the join parameter. In this example, you can join the user's view to the order_items view by typing the word "join" followed by a colon and giving it a name such as users to indicate that the join is to the user's view. Then, you need to provide a value for the type parameter to identify the join type between order_items and users. The four types of joins available on Looker are left_outer, inner, full_outer and cross. If no type is provided, the default type applied is a left_outer. Left_outer join means that all records in the left side view of the join will be retained, even if there's no match with any records in the right side of the join. Inner join means only matched records between the two views will be retained, while full_outer means all records in both views will be retained, whether or not there is a match in the other view. Cross join generates a paired combination of all rows across both views, independent of whether records match or not. In this example, left_outer results in all records in the order_items view to be retained, even if there is not a match in the user's view. After defining the join table and type, you need to provide the data field for the join using the SQL_on parameter. The fields provided in the SQL_on parameter identify how to actually join or connect the two tables based on a shared value. Typically, you join two tables using a dimension from one view that is the same as a dimension in the other view. For example, the order_items view contains a user_ID field that is the same as the ID field in the user's view. You can join the tables on these two fields. However, depending on your use case, you could join on multiple dimensions, perform an inequality join and more, using any regular SQL query syntax that you need. Last, the relationship parameter describes the cardinality between the two views, meaning how many records could potentially be matched between the views based on the fields identified in the SQL_on parameter. In Looker, the options for relationships are one-to-one, one-to-many, many-to-one or many-to-many. If no relationship parameter is provided, the default relationship applied is many_to_one. One-to-one indicates that each record in both views has only one matching record in the other table. Many-to-one indicates that there may be many records from the first view that are connected to only one record in the view table and vice versa for one to many. Many-to-many means that there could be multiple records in the left view that match multiple records in the right view. For example, in the order items Explore, there may be many ordered items connected to the same user. So the relationship is many_to_one. As the name of the Explore is also the base view, it is always in the from clause of any SQL query generated in the Explore. Joins defined directly to the base view of an Explore are referred to as standard joins. Here, we have a different data set that contains flight information. In this example, the join for aircraft is considered a standard join because it joins directly to the base view of flights in a SQL_on parameter. Sometimes, you may need to join the same table twice in an Explore. In the world of manually writing SQL queries, you would give the two tables different aliases. In LookML, you can do this by providing a unique name for the join and then referencing the actual view name to be joined in the from parameter. In this example, the joins named aircraft_origin and aircraft_destination are both joining the same views named flights and airports to provide different information, one join for the origin location and another join for the destination location. The names of the joins use alias names, while the front parameter both specify airports. Notice that the SQL_on parameter uses the alias name as well. Of course, you can also write indirect joins that do not join to the base view. Instead, they join to another view in the Explore that's already joined to the base view. Indirect joins can be useful when there's no shared join key with the base view but can potentially impact performance. In this example, the join aircraft_flight_facts does not join to the base view called flights. Instead, it joins to aircraft, which is referenced in the second join in this Explore. Any time a business user has an inquiry involving aircraft_flight_facts, the generated SQL query will first select from flights and then join to aircraft, even if no fields or filters are needed from aircraft itself so that it can then join to aircraft_flight_facts. So due to the extra join, we recommend that you aim to use a join key from the base view when possible. At this point, it's useful to review how Looker generates SQL to better understand the role of joins defined in the Explore. You should recall that a typical select statement grabs one or more columns from a table, possibly with a join or two on some columns where some filter condition is met. In Looker, as users run queries in an Explore, their chosen dimensions and measures are added as the columns to select statement of the generated SQL query. The from clause uses the base view, and any required joins are added from the provided SQL_on logic in the join definition. Then, if the user adds a filter on a dimension, such as greater than or equal to January 1st, 2020, or country equal to USA, this becomes the where clause. Here is an example SQL query you might have needed to write by hand before using Looker. Imagine that you want to get the number of canceled flights whose aircraft originated in the state of California as well as some additional details about these canceled flights, like flight destination, aircraft name and aircraft origin. In the SQL query, you are selecting three columns from flights with left joins to aircraft and airport, using count to get the number of flights, applying where clauses to identify aircrafts originating in the state of California and flights that have been canceled and then grouping by flight destination, aircraft name and aircraft origin. By defining the flights Explore, you can allow Looker to write the SQL query for your business users. They can simply select the three dimensions for flight destination, aircraft name and aircraft origin, the measure for count and two filters for canceled and aircraft_origin, all selected from the three views, flights, aircraft and airports. The type and SQL_on parameters in the aircraft and aircraft_origin joins would identify how those joins should work in the Explore to provide the requested results. No interaction with SQL needed. By leveraging your pre-defined Explore logic, Looker empowers self-serve analysis and exploration by your business users, which saves you time and resources as a LookML developer. In summary, as a LookML developer, you define the Explores for business users within the model file of a project. The name of the Explore must be the name of an actual view file, and you can define joins to other views in your model to provide additional details in the Explore. To define a new join, use the join parameter to name the join, typically the same name as the actual view file. Then, use the type parameter to identify how the two views should be joined, such as left_outer. The SQL_on parameter identifies the shared column is that being used to join the tables while the relationship parameter is used to identify the cardinality or how the records in the views are matched, such as many_to_one. After this overview of Explores and join logic, you can now curate Explores for your business users to analyze and visualize data in your organization's Looker instance.
