Person: -- In Looker you can use SQL derived tables to create new custom tables using manually written SQL queries. For example, imagine that you want to create a table that contains the following details for each order in your database. Order ID, user ID, the number of items ordered, and the total price of the order. To create an SQL table with this information you need to manually write an SQL query that selects the data attributes and aggregations that you want to include in the table. Let's walk through an example creating an SQL derived table that summarizes the desired information for each order. Begin by ensuring that you are in development mode. You can enable development mode by toggling the button for development mode on the bottom left corner of the Looker homepage. Then click develop on the left side navigation menu to see the develop options. In Looker, the easiest way to write an SQL derived table is through SQL Runner, which provides an interface for you to test and write SQL queries to your available database connections. To open this interface, click SQL Runner under the develop options. In SQL Runner, you can type out the desired query and click run to get the results. For this example, the desired query selects the order ID and user ID from the order items table. Counts the number of items associated with each order and then sums the price of those items. Specifically, the count clause is counting the number of individual order item IDs. The primary key of the order items table, and the sum clause is totally in the sale price of the order item IDs. The group by clause is used to group the results by order ID and user ID, and the limit clause is used to limit the results returned, as you only need to review a subset of records to ensure that the query is working successfully. Next take a moment to review the results of the query to ensure that your syntax is valid and returns the desired results. In this example the query is indeed returning the order ID, user ID, and the number of items associated with each order, as well as the total revenue from each order. After confirming that the query returned the desired results, click on the gear icon in the top right corner, and choose add to project. Select a project name, training_ecommerce, from the drop down menu, and give you direct table a descriptive name, such as order_facts. Then click add. You will be redirected to the Looker IDE to review the newly created view file for your derived table. Notice that Looker auto-generates a dimension for each column in the select clause of the SQL query as well as a default count measure. Also notice that the new view file for the order_facts view has been created outside of the views folder. It is a best practice in Looker to keep the view files organized in the views folder of a LookML project. To move the order_facts.view file, simply click on the order_facts.view file in the file browser, and drag the file under the folder named views. Another best practice is to remove the limit clause that you use for testing, since you do not actually want to limit the rows accessible by business users. To remove the limit, you could simply delete the line for limit 10 from the SQL parameter of the derived table. As highlighted previously, Looker auto-generates a count measure along with the dimensions used in the derived table. Sometimes this auto-generated count measure is not valuable if you already have a count in another view that provides the same number. In this example, the auto-generated count measure is counting the order IDs, and there is already a count of orders in the order items view. So you can either delete the measure or hide it using the hidden colon yes parameter. Hiding the measure is a good idea if you'd like to retain it for validation, but do not want to expose it to users. A final best practice is to ensure that the new view has a primary key. In this example, you can add the primary_key colon yes parameter to the order_ID dimension, which is the core organizing Id of this view that provides details about each individual order. And with that the new SQL derived table called order_facts is ready for you to create new dimensions and measures, join it to the explorer and the model file, and/or finish out the Git workflow to send your changes to production. Pretty easy, right? To conclude this example, let's review the best practices used to create the new SQL derived table. After generating the new view file from the query in SQL Runner, you move the new view file for order_facts to the folder named views, where all of the other view files are stored. Next, you remove the limit clause that was only needed for testing, and then hid the auto-generated count measure for orders because it already exists in the order items view. Last, you established order_ID as the primary key for the order_facts view. With these best practices, you are now ready to create new SQL derived tables in your organization's Looker instance.
