Edit Looker-on Mark with airSlate SignNow

Fax initial looker-on

OLIVIA MORGAN: Hi, guys. I'm Olivia Morgan, and I'm a Looker CE at Google. And today's session is going to be a technical deep dive on Looker, the enterprise BI solution for Google Cloud. To start off with a lightweight agenda, we're going to go over three key pieces. I want to start with a Looker architecture overview. Then we'll touch on some of the key points that I'll be going over in today's technical deep dive. And we'll spend most of the session on the technical deep dive itself. And so to kick off the Looker architecture overview, I wanted to start with a statement that explains the BI landscape and how we experience data. And so what this says is how we experience data is not what it used to be. An effective dashboard is great, but the Looker platform opens up a world of possibilities. And by large and in part, the Looker platform opens up these possibilities because of the way that we're architected and because of the way that we can sit on top of a SQL database or a SQL warehouse. So if you look at this slide, at the very bottom, you're going to see a bunch of different data sources, things like Google Ads, Shopify, HubSpot. And all of those sources are pushed into your data warehouse or your data lake. Now, Looker, as a BI platform, sits on top of that database or data warehouse, and it speaks SQL to that database or data warehouse. And in this case, in today's demo, we'll be sitting on top of BigQuery. And you can think of Looker as a powerful API layer that brings a lot of different benefits to the surface for your end users and for your technical users. One of the key pieces here is that we have governed metrics in Looker. So being that we're sitting right on top of the database, Looker offers a one-stop shop version controlled modeling layer, where you're defining your business logic, and you're iterating on that business logic in a version controlled way. We also offer best-in-class APIs. So we not only have API endpoints for data, we also have API endpoints for managing your Looker instance, managing users, groups, or user attributes for those users. We also have powerful API SDKs and embed SDKs, if you're building custom applications. Really, a core tenant of Looker and the way that it's architected is that it's in database. So one analogy I like to use is, when you put Looker on top of your database, it's like chipping your car. Whatever the engine of that car is-- in this case, we're sitting on top of BigQuery. Looker is enhancing all of the features and functionalities from that database and will benefit from any features and functionality that then is brought upon that database. So on top of those features, of course, we're version controlled. That allows for additional security. And we are cloud first, so Looker does take a multi-cloud approach, whether it's GCP or any other cloud. And from there, we're able to operate in four different modes, if you're thinking of the BI in different ways that you can serve these analytics to different end users. The first one on the left is, in the red box, Modern BI and Analytics. And that's your traditional BI data consumption-- your reports, your dashboards. A use case there might be that you're building a customer 360 dashboard to view customers across your channels and media. The green box, Integrated Insights, is more along the lines of bringing the data to where your users already consume it. So if you have sales folks that operate in Salesforce, don't ask them to log into another application. Use the application to embed those insights into the tools they already know and love. The yellow box, Data-driven Workflows-- this essentially automates the first two parts. The idea is that your BI tool is going to help you uncover those insights. So Looker will help you uncover, perhaps, customers that are close to churning, based off of a customer health score. And then within the Looker application, you can use that application to actually trigger the action you'd like to take-- maybe based off of a low customer health score, we're sending emails to those customers. And then the last one, Custom Applications-- a custom application you can spin up in any way, shape, or form. A large reason why Looker opens this up in such a powerful way is because, not only we're in-database, but we do have a powerful suite of APIs. So when you have a version controlled and governed layer to pull metrics from, you can create a slew of custom applications. You can use our APIs to render visualizations or data points to make any sort of custom workflow you can dream up. Now, for the demo today, I wanted to go over a few key points of the pieces that I wanted to touch on. I really want to highlight the fact that this instance is hosted on GCP, and we are going to be sitting on top of BigQuery. So what you'll see within the demo is that we're going to use Google Cloud Functions and the search API for things like product image searches. You'll notice that we have BQML laced within our Looker models for item sales and stock forecasting. We're also going to use the BigQuery weather public data set. So you'll see examples where we can start to interact with public data sets to make the analysis richer. We're also leveraging nested tables for things like faster performance on transaction analysis, in this case. And then lastly, we're able to utilize faster and more efficient queries with BI engine and Looker's aggregate awareness. So now I'd like to transition into the technical deep dive, where I can show you Looker on top of GCP and BigQuery. In the Looker demonstration, we are hosted on GCP, and we're sitting on top of a BigQuery database. We're working off of a retail data set. And so our goal with our models and view files and our LookML is to be able to support end user experiences, like the merchandising VP or regional VP logging in to look at performance. Most of our time today is going to be spent here in this Develop tab. But what we'll do is we'll start in the Admin tab by creating a database connection. So once that database connection is created, the Develop tab is where we build our Looker Symantec model, or the LookML. For those of us that are developing in LookML, we're exposing those models to end users via this Explore tab. And for those end users, whatever they're building in this Explore tab then populates the Browse tab, which then also populates these boards and folders for end users. Now, starting in the Admin panel, I want to take you all to our database connections and establish a database connection. And we'll open this in a new tab. Now, when you're establishing a database connection, you can go ahead and add the new connection button up here at the top. For our demo, we already have a connection established with Looker data. So I'm going to go ahead and edit this connection so we can see how this is set up. So we have our Looker data connection. The dialect we are connected to, as I mentioned, is BigQuery. But if I open up this panel, you can see all of the different dialects-- or a variety of the different dialects-- that Looker connects to. On the Google front, we've got Legacy, Standard, PostgreSQL-- and also Cloud SQL and Cloud Spanner. Once we've selected the dialect, you can select the project name and data set name that you're pointing Looker to. But let's say your use case calls for a little bit more dynamism. You can also use user attributes in Looker to dynamically set a value for a user or a group, if you're pointing them to separate project names or data sets. Now, within Google, many of you might use OAuth. And so Looker does integrate with OAuth. And we can use a OAuth for IM integration. We support service accounts. So that's another way that you can support authentication and connection settings within Looker. Add your usernames and your passwords. And then down here at the bottom, another interesting feature here is that max billing gigabyte. So if you wanted to set a maximum number of bytes billed allowed on a single BigQuery query, you could do that. And then similar to those host and ports up there at the top, if we wanted to dynamically set this value for users or groups, we could. Now that the connection's been established, what we need to do is we need to create a LookML project. Now, the LookML projects in Looker are essentially a house that contains all of your model and your view files. Before we move into that project, what we'll want to do is jump into development mode. And this allows me, as a developer, to start editing or creating my own projects. So to walk you guys through the flow, I'm going to click on Manage LookML Projects, and I'll show you how to create a new project. Up in the right-hand corner, we'll select New LookML Project. And what we'll do is we can give our project a name, a starting point, and a connection. So we can name the project whatever we'd like. The starting point really depends on how you would like to begin building the logic for your analysis. So in many cases, folks will generate a model from a database schema. In this case, Looker scans the database and brings back or automatically generates the models in the view files. We can select generate from SQL, clone a public Git repository-- which this demo is based off of one of our blocks. So if you decided to point Looker to one of our public Git repos that we have hosted for a particular source or analytic analysis, we can point Looker to that repository, and it will automatically generate or pull those files for us as well. Now, in our case, we already have a project defined. So in the Development tab, I want to navigate you guys to the retail demo. And within the retail data set, what you'll see here in the upper left-hand corner is, we are version controlled. So when I jumped into development mode, I essentially put myself into my own code branch. We have look LookML validation here on the left side as well before you push changes to production. But the main components I want to focus on here are models and views. So you'll see we have a model folder and a view folder that contains a variety of different views. For Looker, a model file points to a database connection. So here, we've pointed it to our Looker data connection. And within this connection, what the file tells Looker is how to think about the schema of that database. And so in this case, we have our transactions table or transaction view file right after an explore parameter. Now, the file name that comes after the explore parameter populates the from clause in the SQL query. And so the way we read this is from transactions, join transaction line items, join customers, join customer facts, and so on and so forth. When we're establishing join relationships, we use the relationship parameter and the SQL parameter to help Looker protect against symmetric aggregates. So once we define these parameters, Looker will automatically detect over-counting, and it will adjust to protect from any symmetric aggregates. Now, in this case, we have a transactions table, and we have a transaction line items. This is nested data within BigQuery. And so Looker works seamlessly with nested data. All we have to do in the SQL parameter, or what Looker will do when it detects a nested field, is it will do a left join unnest-- in this case, on our line items table-- so that we can start to use this for analysis and unpack that data. Now, another important thing here is our always filter. In Looker, you can use always filter to apply a filter restriction on this explore section, so that when users are running queries, they're protected, A, against running large historical queries, but B, that we are pointing them to a BigQuery partition. So if we actually go to the Explore section and we type in our retail, our transaction detail explore right here, you'll notice, at the top, we have a filter for transaction date. And it is required. So no matter what I pull-- if I go here to the left-hand side, and I decide to pull channel name, that SQL parameter will always have a where clause pointing us to either the partition time or the partition in BigQuery. Now, going back to our model file-- if our model is where we point to the database connection and we explain to Looker the schema, the view files are how we define the tables. Now, you'll notice, when we spun up the project, Looker will automatically create a list of views for us. Let's say there are views that I would like to add. There's two different ways that we can start to add additional files. I can go ahead and create another model, if I'd like to point to another database connection. Or I can create views from table or create views outright. If I create a view from table, what that workflow looks like, as I'm iterating in my project, Looker will bring you to a schema viewer where I can simply select a schema. I can select a table. Maybe I'm selecting my Zillow data here. And at the bottom, I can bring these files or create views, which will create a new view file here in my project. Now, for the view files in this demonstration today, we have a few base views and a few examples of derived tables or derived views in Looker. So if I open up my views dropdown, a couple of the core tables that we have here are things like our transactions table, our store dimension table, our customer Dim, our product Dim. If I look at our base transactions table, you can see the view name and the SQL table name, which points us to that table sitting in the database, the schema dot table name. And every column coming from the database is now its own dimension. Now, you'll notice, underneath each dimension, we have a couple different parameters. In this case, we've got a type number. This is the primary key. And in the SQL parameter, it's pointing Looker to table dot transaction ID. Now, this table is injecting that single table name, retail demo dot transaction detail dot transaction ID. Now, this injection becomes very important when we are deriving new fields in Looker. So if we come down here to our derived dimensions, you'll notice a couple different examples. One example is a dimension group. So we've got since first customer transaction. What we're doing is we've derived a new field. In this case, it's a mentioned group of type duration. And we've given Looker a SQL start and a SQL end date to derive the months since our customer first transacted. Now, why this is significant is, instead of pointing to a table dot column name, a field that exists in the database, I'm pointing us to another field in order to derive a new field. And so SQL is a declarative language, but it doesn't always lend itself well to the benefits of object-oriented programming. And so this is an example where LookML really brings in those values of object oriented programming, in the sense of reusability and extensibility. So when I continue to scroll down here, and I'm looking at my measures-- number of transactions, number of customers-- I can start to pull counts, and sums, and averages on particular fields that are coming from the Looker data model, customer ID, and store ID. Or we can become a little more complex with our aggregations by notating a measure of type number and actually doing the aggregation or doing the calculation in the SQL itself. Now, to make it really rich for end users, you'll notice on all of these measures we have these drill detail fields. These drill details will specify a list of fields that is pulled back when a user clicks on that aggregation. And so you'll see drill details defined up here. So if a user defines or clicks on one of those metrics or measures, Looker is going to uncover the date, the store name, the product area, the product name so that the user can see the real level detail related to that metric. Now, if our base transaction table is an example of a core table coming from our database connection, a couple of examples of derive tables in Looker are our customer facts or our customer transaction sequence table. So if we look at the customer facts table, what you'll quickly notice is we have the view name, but instead in the SQL table name, we've invoked a derived table. And under the derived table, it allows you to drop in a simple SQL statement or a complex SQL statement so that you can start to derive or prototype schema changes. What's powerful about this is you don't need to physically copy the data on the database side or create a fact table on the database side in order for Looker to leverage or for you to prototype what type of analysis this would give you. You can simply create that analysis in Looker. And if you'd like to speed it up, we can start to persist these pieces of data. So you'll notice here we've got data group triggers. So this derived table is governed by a caching policy. That's a daily caching policy. And a shortcut in Looker, if I hold down the Option key, I'll get this plus button. I can click on that data group trigger and Looker will navigate me to where that's been defined. You'll notice it took me back to my retail block modeling file, where our data groups have been defined. In this case, our daily data group is triggered by a select current date. And the contingency here is a max cache age. Why this is really powerful is, if we want to sync Looker up with your data pipeline-- let's say we want to sync Looker with your ETL processes, we can define a SQL trigger that detects changes in your ETL files. So maybe when a new ETL process is completed, we can signify a rebuild of that derived table, or a re-caching of that table. So going back to the customer facts table, you'll also notice a few pieces here-- things like partition keys and cluster keys. Now, when Looker is sitting on top of a database, BigQuery or otherwise, it is enhancing what is coming from the database. So given that we can optimize BigQuery by utilizing partition keys and cluster keys, Looker inherently has parameters that we can use to leverage or define these partition keys and cluster keys for folks that are defining derive tables. Now, for the derived table, we can create this derived table by, instead of creating view from table, creating a view outright. The other way we can do it is we can use Looker to generate the derived table for us. So a really powerful work flow in Looker is to use the Explore section to generate a SQL query. Now, for the end user, it will always look like a result set. But for the more technical developer, that SQL query is something that you can use for your projects and models. So let's say this represented a derived table that I would like to use for a project. I can open in SQL runner down here in the bottom right-hand corner. And Looker will move my query back to the SQL runner. SQL runner can be navigated to here in our development tab. But once that query is run, and I'll just take a look at the results set here, I can go into the upper right-hand corner, and I can derived table LookML. So I can already derive that table and create the view file for myself. You'll see that every column coming from that view file or coming from that SQL query has now been its own measure in dimension. And all I need to do is add it back to my project. So you can use Looker to expedite the SQL writing process for you. Even if you're writing a derived table or a native derived table, you can use Looker to help you generate that SQL and generate that view file. Now, aside from the customer facts table, we also have examples of BQML being utilized in Looker. Our stock forecasting tables and our customer clustering tables are two examples here. So if we start with the stock forecasting table, you'll notice a little different flavor of a derived table. Instead of a SQL specific derived table, we're using something called a native derived table in Looker. Why native drive derived tables are powerful is, as you will notice, they're not dialect specific. So LookML, as a modeling layer, it is a SQL translation layer. The value there is that with any point and click of an end user, Looker is translating those clicks into optimize SQL for whatever database we're sitting on top of. So in the case of native derived tables, given that these are not dialect specific, it makes it really easy if I'm going through a data warehouse modernization project, or I'm moving back ends and using Looker as the front end to help me with that migration. I can simply swap out the connection name in my model file, and I can reuse these derived tables. I don't need to rewrite them into a different dialect. So the stock forecasting shows a nice example of that. Within the stock forecasting view file-- and forgive me for my scrolling. I'm going to have to scroll down to the bottom here-- we'll be able to see examples of our BQML model. So with the BQML model here, we did feed in about 200 inputs, just from our transactions table. And ultimately, what we're doing is we're using BQML linear regression to forecast sales by store, by item, and by week. And so if we get down here to the regression, you can see here that we're using our SQL create statement on this derived table to create or replace the name of that derived table-- and using linear regression here. Now, they're defined in our LookML model, so if I want to change our linear regression or iterate on some of these BQML models, we can easily iterate on those models, validate them, and test them on the front end. You'll also notice down here that we're also doing some sort of prediction on our stock forecasting as well. Now, for customer clustering, this is a really good example of using clustering to create customer segments. So again, we have our customer clustering input up here at the top, where we're inputting a native derived table coming from Looker. And then we can define the clustering model here with k-means clustering. And we've defined the clusters of four. And so with those clusters, then on the front end, we can use our customers explore to start to explore those different customer segments, whether they are millennials, Gen Xers-- whether they're net new customers. And you'll see down here at the bottom that we also have a customer clustering prediction. And so what this is doing is-- this is out of the box with our block to help cluster our customers into those buckets, whether they're millennials, retirees, or Gen Xers. Again, this can be used right out of the box with this retail block. Or, if you'd like to iterate on this model or customize it in any way, shape or form, you can. Now, the last piece I wanted to touch on before we moved into the Explore section was our public data sets that we're working with. So if we look at our store weather table here in Looker, this is an example where we're using the BigQuery global daily weather public data set to map a retailer stores to the nearest weather stations. We want to get each store's daily weather out of the box. And so you can see the BigQuery public data set being leveraged here for all different years and the mapping that's happening on Looker. So I do like to describe the LookML model in Looker as kind of a lightweight transformation layer. So you can see that some of these models are very complex. You can host a lot of complex logic. But more importantly, I'm able to leverage, really, any function or functionality that is coming from BigQuery or GCP to make this really rich for our end users. Now, with all of those pieces-- and I'll go ahead and stay logged in-- we want to navigate to the explorers, right? So all of the work we've done with our store-- weather views, all of our transaction and our clustering. What we've done is we've gone into our model file. And we've defined different explores-- transaction detail, customer-- and we have all of the joins defined. And so for myself as an end user, what's really easy to do is to now move into the Explore section and build any analysis that I'd like. So if we go ahead into the Explore section, I'm going to navigate back to our transaction detail. And I'm actually going to jump out of development mode. And so now, on my transaction detail explorer, on the left-hand side, everything that was joined are now tables that I can point and click from. So if I look at our transactions table, I can see I can pivot, filter, or add some sort of a custom measure. If I'm a developer, I can move backwards into the LookML. This is really important, that Looker allows you to move seamlessly from the front end to the back end. The architecture being simplified as it is, given that all of your metrics are governed under one develop tab-- it's very easy to understand, not only how things are defined, but to update metrics and to update dimensions for end users, to deliver the analysis that they want. Now, along with those dimensions, there are my dimension groups with all of my type time. And down here at the bottom in orange are all of my measures. Now, we do have our access filter up here at the top, or our filter for our partitions up here at the top. And so let's say I want to do a year over year sample. Let's say I would like to look at data for the past four years. So I'd like to do a year over year for the past four years. I can go ahead and add my month number into the analysis. I might want to look at my total sales year over year. And I will pivot that by my year bucket. And we can give that a run. Now, you might have noticed in the upper right-hand corner, as I'm querying, Looker will tell you whether it's pulling from cache. Or it will tell you how large the results set-- it will predict the size of the query before you run it. Now, when it returns a visualization, I've got my visualization, along with the data table. I can go ahead and start to play with how this is visualized. So let's say I want to not plot null values. And I'd like to add an outline to my year over year analysis. Again, this is all being generated off of that core model. So there are all of those timezone conversions, the filters that I've added. In our SQL statement for the end user, it's a simplified results set. As soon as it's rendered, I can drill. If I want to look at all 354k on this data point, Looker will also automatically handle time hierarchies. So if I want to continue to drill down into different time hierarchies here, I can. But to support that data analysis workflow, if I want to then add this back to my dashboard or save it as a report, I can. I can also save and start to schedule this out. So when we're talking about integrated insights or data driven workflows, what I can do is I can set those data driven workflows here in the Explorer and start to push those out to different users or groups-- or set those workflows in motion that are going to be most beneficial to me. Now, if I go to save this back to my dashboard, I'll go into my folder here. And we can save it back to our group overview that we started on. And I'm going to call this year over year sales. Now, we can head back to the group overview dashboard. And this is where the end users are going to come to consume all of the stock forecasting, customer clustering, the public data sets that we've laced into this demo. But now it's in easy to consume format. It's visual. I can see my basket size. If I'm a VP of retail, I can see the percent of customers that are transacting, from my loyalty customers. I can see geographically where my stores are located. And if I want to dig into any sort of top movers, I can look at my change by store and change by category. And I can easily start to take action on these. So now if I want to integrate with Google App Engine and create a text or a call function to contact that store manager, we can send a text quickly to that store manager. I can go ahead and grab the link that would hit their inbox. And when I open that link, I'll be taken to a store deep dive that's already been updated for my Los Angeles store. And so I can easily start to take a look at why this store might be under-performing-- and then look at different examples of that work we did on the back end and how it's displaying on the front end. So if we're looking at our peer store comparison, I can see how I'm doing versus my peer stores. I can look at the weather trends to see if my transactions decreasing has anything to do with the weather. And then when we get down to stock forecasting here, I can easily see how much of a product I have in stock versus what's forecasted and what the stock difference might be. And then if I need to take action on any of these pieces, I can go ahead and explore from here and move backwards into the report building interface, where I can start to alert when my stock difference, in this case, might be greater than or equal to a particular value. And of course, this is just the BI and analytics workflow. So if we want to get more advanced, certainly, this could become an iframe that I then embed into a custom application. Any of these points become API endpoints or query endpoints that I can pull into a custom application or custom workflow. But that's the end of my technical deep dive on Looker. I hope that it was informative, and thanks so much for joining.