Person: As a LookML developer, you can modify existing dimensions and define new ones as needed for your business users. For example, imagine you're working with marketing business users, they appreciate that they can see the first name and last name of each user and the order items explore. But they've asked if you can create a single dimension that contains the users' full names. Let's walk through an example to see how you can create a new dimension for full name by combining existing dimensions for first name and last name in a user's view file. To begin, 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. Within the develop options, click on projects. Click on the training_ecommerce project under project. In the file browser, navigate to the users.view file in the training_ecommerce project. To make a new dimension to represent a user's full name, scroll down to find the last_name dimension. Then add a new blank line to make space for the new dimension. Now using the same indentation as the existing dimensions, start typing the word dimension. Notice that as you write code, the Looker IDE will check your syntax and make suggestions in real time. Enter a colon after the word dimension. You will see two curly braces appear. Be sure to leave these curly braces. You will add code within these curly braces to tell Looker how this dimension should be defined. After the colon, following the word dimension but before the curly braces, you need to provide a name for the new dimension. We recommend using all lowercase letters, because LookML is case sensitive. For names containing multiple words, we recommend using underscores to represent spaces between words. For example, full_name would work well as the name for the new dimension, that combines first_name and last_name. Next, between the curly braces on the following line, define the type of dimension you want with the key word type, followed by a colon. Now because you're combining two other string dimensions, first_name and last_name to create this new dimension, you would choose the dimension type string. On another new line between the curly braces, add the SQL parameter by typing SQL followed by a colon. Just like before, Looker will suggest options and add syntax, such as the two auto generated semicolons, at the end of the SQL parameter. Be sure to leave these semicolons, as this syntax is how Looker knows where the SQL expression ends for a given field. For the SQL, there are two options to select existing dimensions, such as first_name. The first way that we can specify first_name is to type ${TABLE}.first_name. This substitution syntax hard codes the first_name column in the database, and will plug in the views SQL_table_name at run time. However, because there is an existing dimension in our view file, it is best practice to use ${first_name} for the SQL parameter so we can reference the existing LookML dimension. So you can replace ${TABLE}.first_name with ${first_name}. To finish the SQL statement, you need to combine first_name and last_name. To do this you can use the SQL function concat. It's important to note that anything you write in the SQL parameter needs to be valid for your database dialect. For this example that uses data and BitQuery, start the SQL expression with concat, followed by an open set of parenthesis. Within the parenthesis include the first_name dimension followed by a comma. Then include an empty space with double quotes followed by another comma and then the last_name dimension. Notice again that you are referencing the existing dimension names. So the final code for the SQL parameter is concat (${first_name}, " " , ${last_name}). Now click save changes. Then click validate LookML to check that you have written valid LookML to define your new dimension. If you have the permission to make changes to production, you will see a button to commit changes and push to production. For now you know that while you can choose to commit and push the changes after validating the LookML, it is always a good idea to review your changes in explore first. Within the explore tab, right click on order items and select to open the explorer in a new tab. When developing LookML, we recommend keeping one tab open on the explorer so you can switch to it, refresh, and quickly validate what you've just built. Notice that full name is now a dimension under users. You can select full name along with first name and last name and click run to see the result. Congratulations, you've successfully defined and tested a new dimension in Looker.
