Person: In Looker, dimensions are qualities or attributes of your data. Typically, each dimension represents a column in your underlying database. If you use Looker to generate your model files from a data source, your model's view files will already contain a dimension for each column in the database tables. In the view files, you can also define custom dimensions using LookML. Let's dive into modeling dimensions within view files so that you can create new ones or modify existing ones using LookML. When Looker generates a new view file from a table it automatically creates dimensions for every column or field in the database table. Autogenerated dimensions have an SQL parameter, containing the word table highlighted in blue. With a dollar sign and curly braces, such as sql: ${Table}.first_name. ${table} references the table specified in Sql_table_name parameter at the top of your view file. This lets Looker know which database table to use, as the default table, when pulling the dimensions and measures. Another version of the substitution syntax is using an existing dimension or measure name within curly braces, preceded by a dollar sign. Such as, sql: ${field_name} When defining new dimensions that build on existing ones, it's best practice to use the ${field_name} substitution operator instead of the ${table} reference so that you can reduce the instances of hard-coding column names in your code. In this example, the syntax references existing LookML objects called first_name and last_name. To build a new dimension called full_name. Referencing the Looker object is better than the hard-coded database column so that the new field inherits any transformations or additional logic from the overall dimension. It also reduces the number of times or places you'll need to make updates. If something like the database column name changes. In Looker, there are four common dimension types. The first one is string. String is the LookML term for what in your database dialect could be Vartar, tar, text, or simply string. It's used for text values such as name, and is the default dimension type in Looker. This example shows how a new string dimension called full_name can be created from concatenating two existing string dimensions. First_name, and last_name. The next common dimension type is number. Number-type fields are used for columns of numeric data types such as int, decimal, or float. You should also use the number type to define new dimensions whenever you will use it to store mathematical computations like addition or subtraction. For example, a common use case for number is date difference logic such as the one seen in this example which calculates the number of days since a user signed up on a platform. The third common dimension type is the yes/no field type, which is a Boolean. It produces a yes value if the condition entered in the SQL parameter is met. If the condition is not met, then it produces a no value. An example use case for yes/no field types would be to label new users who have only been users on a platform for a specific length of time such as 90 days or fewer. The last common dimension type are tier dimensions. Which are useful for bucketing values. When defining a new tier dimension you need to use the tiers parameter to indicate which buckets you want to assign. Each comma-separated number will be the start of a range. For example a 0 followed by 30 means that there is a first bin continuing the values from 0 to 29, followed by a second bin beginning at 30, and so on. There's also a style parameter, where you can specify how you want the results to be formatted, for business users. Such as integer to display the bin range as whole numbers or relational, to display a text expression that includes symbols such as less than or greater than. To learn more about the various style options refer to the Looker docs on styles for tiered dimensions. Less commonly used, but still very useful, is the dimension group of type time. For these dimension groups, you use the time frames parameter. To specify the date and time parts required for the data. There are many options for the time frame such as hour, day of the week, month, quarter or year. The number of dimension fields created within a dimension group is dependent on the number of time frames listed in the time frames parameter. For example, including only date, hour, month, and year will result in only these four dimension fields created as part of the dimension group called Created. When generating a new view file from tables Looker will automatically create dimension groups for most date and time columns. Though it is possible for some formats to not be automatically recognized by Looker. When using an existing dimension group time frame to define another dimension for example in a dimension performing a date_diff () or difference between two dates, you can specify the desired date or time unit to identify the date field from the dimension group called Created. To do this, simply append the date or time unit to the name of the dimension group using an underscore. Such as ${created_date} to specify that you want the date field from the dimension group called Created. In this example a new dimension named shipping_days is created, based on the difference between the ${shipped_date} and the ${created_date} in number of days. Another dimension group type that is not as commonly used and can be quite helpful is the dimension group of type duration. Using the intervals parameter you can allow business users to choose from a range of time intervals. When possible a best practice is to create dimension groups of type: duration. Instead of defining dimensions that perform, date_diff functions in the SQL parameter. This allows you to avoid hard-coding the date part so your business users can choose what works best for them. Additionally this prompts Looker to write the function for you, which is easier if you're not as comfortable with SQL. And it will create less work for you in the future in case your company ever decides to change the underlying data source. Similar to time frames the number of dimension fields created for a dimension group of type duration depends on the number of intervals provided in the intervals parameter. To use a dimension group interval to define another dimension you need to write the desired interval before the dimension group names itself. Like ${hours_enrolled}. Or ${days _enrolled}. In conclusion there are many dimension types in Looker, including string, number, yes/no, tier and dimension groups for time and duration. When referencing a column from a database table for the first time in a LookML model use ${table}. When defining new dimensions that build on existing ones, you can reduce the instances of hard coding column names by using the ${field_name} substitution operator to reference the existing LookML object. After this introduction to modeling dimensions, you're now ready to start curating dimensions in your organization's Looker instance.
