Person: -- as a LookML developer you can define as many dimensions and measures as you like in your view files to curate data. Remember though, for business users to leverage these dimensions and measures, you need to make them available through explorers. To create explorers, it's helpful to first understand how Looker generates SQL before learning how to work with the model file within your project. In this section we'll examine in more detail how SQL query generation works in Looker. All SQL queries in Looker begin with the data and options that the business users select from the explorer. When users click the run button, Looker automatically generates and sends the necessary SQL to the underlying database, and then returns the results to the user. In this example, the business user has requested to see user count by state by selecting this state dimension and the count measure from the user's view. As a LookML developer, you can see the SQL query generated by Looker, and then sent to the underlying database in the SQL window. Let's deconstruct this query to understand how Looker generates the SQL. In the SQL query, the dimensions and measures selected by a user are the columns or fields that follow the word select at the start of a query. Remember that dimensions are the individual data attributes, such as user's state of residence, while measures are aggregate functions applied to dimensions, such as count. Thus, measures will appear as aggregating functions in the select statement. In this example the count measure appears as an aggregating function that counts distinct user IDs. The word from in the SQL query identifies the base view of the explorer as defined in the model file. The base view of the explorer is always part of the generated SQL query. Even if no dimensions or measures are selected from that view. Looker then joins to any other view that is needed based on the dimensions and the measures selected. In this example, the base view of the order items explored is order_items. Tho the query is looking for the count of users by users.state. Looker still selects from order_items first, and then joins to users. However, unnecessary views are not joined in the query as long as no dimensions or measures are selected from those views. When a business user filters their data by a dimension, this adds or modifies the where clause in this SQL query. In this example, the user has added a filter for users.country to equal to USA. Which is then included in a generated SQL query. However, when a user filters their data by a measure, this adds or modifies the having clause in the SQL query. In this example, the user has selected a filter of order items count greater than five, which has been included in a generated SQL query in the having clause. Business users can also set row limits on how much data is returned. In a generated SQL query these limits are reflected in the limit clause. For example, if the user sets the row limit to 10 to only see the top 10 results, the limit clause of the SQL query will update to match the row limit set by the user. To summarize, the dimensions selected by business users are listed in the select statement of the generated SQL query while the selecting measures appear in the select statement as aggregating function, such as count applied to dimensions. In the SQL query the base view of an explorer is always selected first, and other needed views are joined based on user's selections. Unnecessary views in the explorer are not joined in the SQL query. As the users select filters and update limits in the explorer, those are also reflected in a generated SQL query using the wear, having and limit clauses. Now after this deep dive into the SQL queries generated by Looker, you're ready to leverage Looker generated SQL to design explorers for your business users.
