Looker 101 Chapter 2
Creating a project and getting to know LookML
In this chapter, we are going to learn how to create a new project that will have the connection to our database (that we created in the previous chapter), then we will get to know and experience with Looker’s unique language for data – LookML!
Creating a Project
- Go to the Looker Homepage
- Press on Develop
- From there, Select Projects
- Create a new project by clicking on New LookML Project
- Choose a name for the project
- Make sure the Starting Point is on Generate Model from Database Schema.
- Select the connection to the datasource that you’ve created during the previous chapter of this tutorial by looking up it’s name on the list.
- Import all of the tables by choosing All Tables.
- Finally, Click on Create Project.
- You should now get a similar screen as the following:
- * “model” means it’s a dataset
- * “explore” and “views” = the tables
LookML
- Go to one of your tables in the dataset by choosing a view from the list under views (in this example “test_table”)
- Now you’ll see the LookML (Looker’s programming language for data) code of that view that Looker automatically created.
- Let’s get to know the foundations of it by going over the generated LookML code of our “test_table”.
- Writing LookML requires declaring Parameters and assigning values to them.
In this example the parameter is sql_table_name
and it dictates your table name
The value assigned to it is
yuval_test.test_table
so the view references the table called “test_table” from the dataset called “yuval_test”.
Dimensions are parameters that represent columns in the dataset – each contains parameters that describe it:
In this example the dimension is named country and inside the following curly braces there are all the parameters that describe the dimension:
type: string – means the data type of the field is string
map_layer_name: countries – related to a specific feature of Looker where it can map the countries and make a dashboard with them
sql: ${TABLE}.Country – a SQL statement that indicates which field we are referring to in our actual database table – Country in our case. ${TABLE} is a dynamic parameter that contains the sql_table_name
*Notice that each sql statement ends with a pair of semicolons.
- Notice that sometimes Looker might give a confusing name to a dimension.
so be aware to check from what column on your table Looker extracts the data into the dimension and change the name if necessary to prevent possible future mistakes.
In this example Looker gave the dimension the name “order”
However, we can see that the dimension references to the column “Order_Date”- a column of dates, not orders…
In that case we should change the name to “order_date”
- LookML has it own unique data types.
- For a timestamp column, like “Order_date”, a dimension group is created and it’s type would be time.
- The datatype parameter will contain the sql data type that as we know, is date.
- The dimension_group for “Oredr_date” creates a set of individual dimensions for different timeframes.
- The timeframes parameter contains all of the possibilities for those different timestamps expressions of the data, which here is the dates.
The dates can be expressed and queried in forms of raw – as it is, date, only by the month or only by the year and so on…
*The convert_tz parameter set to no, means we want to prevent automatic time zone conversion of the data
- Another LookML data type is number which is used to represent numeric columns in a table.
- In this example the “order_id” column’s sql data type is Integer, so the dimension’s type in LookML is number.
- Measures in LookML are objects that represent aggregate functions like sums, counts, and averages that will be based on our table’s fields and columns.
*In here the measure was created defaultly by Looker.
We can add our own measures later on.
- Creating measures is very similar to creating dimensions.
- We state a measure parameter and define it by adding parameters that describe it such as the type and a sql parameter.
In this example we created a measure named “avg_order”.
It’s type is average which means it calculates the average cost
The SQL statement indicates which field from the table we want to make the measure on – in our case “Total_Cost”
- That’s it for this time.
- Hoping to see you in the next chapter- Dashboards and Visualizations
If you have Questions regarding looker, feel free to contact us through the website or via Linkedin
1 thought on “Creating a project and getting to know LookML”