Chapter 5: Building Your Data Model

Metabase has several features that allow you to create helpful layers of metadata on top of your database to make it more user-friendly to work with. Metadata is data about data. If you have ever struggled with ugly or oddly formatted fields in a database, or been perplexed by a certain data type, you know this pain point. You can imagine that these pain points are all the worse for the less technical users in your organization. By investing a little time upfront to properly define your columns, data types, table relationships, descriptions, and permissions, you can greatly improve comprehension and usability for others in your organization.

In addition to adding metadata to enhance your database, Metabase also allows you to hide parts of your data that are either irrelevant or highly sensitive. This will all be covered in this chapter.

Specifically, in this chapter, we're going to cover the following main topics:

• Editing your...

Technical requirements

For those getting tired of AWS, I have good news: this chapter has no AWS requirements. It is fully focused on Metabase. You will need a running instance of Metabase with a connection to the pies database we learned about in Chapter 4, Connecting to Databases. The link to the data for that database is https://github.com/PacktPublishing/Metabase-Up-and-Running/blob/master/data/create_pies_db.sql.

Editing your data model

In Chapter 4, Connecting to Databases, we created a database with data about our fictional business and connected it to Metabase. If you followed along with everything in Chapter 4, Connecting to Databases, you should have two databases connected to Metabase, just like in Figure 5.1:

Figure 5.1 – The two databases connected to Metabase

In this section, we will learn how to customize our database's data model. Before we do that, it might be helpful to take a quick tour of our database in Metabase so that we gain some familiarity with it. Let's get started.

Touring the Pies database in Metabase

In Chapter 4, Connecting to Databases, we only spent a little time exploring the actual data in our Pies database. Before we go ahead and refine our data model, let's get to know the tables and contents of this database. To get started, have Metabase open:

1. Click Browse Data on the top blue bar.
2. Select...

Specifying foreign key relationships

Recall in Chapter 4, Connecting to Databases, how we learned that the relational part of a relational database comes from how records in one table relate to other records. One of the benefits of this is to save space. To see an example of this, let's have another look at our Reviews table, or simply turn back to Figure 5.5. Notice that aside from the review ID and star rating, all other columns in our Reviews table relate to other tables:

• The ID Order column relates to ID Order in the Orders table.
• The ID Menu column relates to ID Menu in the Menu table.
• The ID User column relates to ID User in the Users table.

Recall that when we first viewed our metadata for the Menu table, we saw that Metabase had determined that ID Menu was an Entity (or Primary) key. The same is true for ID Order in the Orders table and ID User in the Users table – they are all Entity keys. When we see an Entity key in a different table, as...

Defining segments and metrics

In the last section, we learned how to edit the metadata for our columns and define our foreign key relationships. The main purpose of all of that work was to make for an easier and more intuitive user experience for people less technical than us. In this section, we'll expand on that, using two useful features that Metabase offers: segments and metrics. Let's learn about segments first.

Defining segments

Simply put, a segment in Metabase is a named filter on a table. They can be quite powerful, though, as often, a name is much easier to remember than the filtering logic behind it. Let's explain this with an example.

Consider our fictional business, Pickles and Pies. Internally, we may have teams that only focus on the pickle part of the business, and other teams that handle the pie side of things. When looking up stats about sales, review ratings, and repeat orders, people in our organization may only care about the stats for either...

Building your data dictionary

To discover all the metadata, table descriptions, and column descriptions we've added, Metabase uses a feature called the data reference. In addition to showing most of the metadata we've added, the data dictionary has room to enrich user comprehension about our database, tables, segments, and metrics even more. The idea behind the data reference is that if a new user creates a Metabase account but has no context about what any of the databases or tables are, they should be able to easily look up key information to help them get started.

Our Pies database is relatively easy to understand, but you can imagine how this is an exception rather than the rule. Organizations store all kinds of data, often in strange ways. For example, I once worked at a company that stored all of its most important analytical data in a database called temp. They had a difficult time explaining to new hires that the temp database was the one with the actual important...

Creating permissions

Permissions either allow or prevent groups of users from accessing certain data. Metabase has a simple philosophy around permissions, which they outlined in a blog post back in 2016 when they introduced the feature (https://www.metabase.com/blog/Permissions/). Unlike some analytics products, which give extremely fine-grained and complex permission capabilities down to the individual record, Metabase keeps it high level. They do, however, offer more fine-grained permissions in their Enterprise version.

In Metabase, permissions are not applied at the user level, but rather at the group level. So, before we learn about permissions, let's learn about groups.

Creating user groups

Groups allow you to organize sets of users. The purpose of creating groups is to control permissions around the viewing of certain databases, tables, or columns. For example, a good practice is to restrict the access of Personally Identifiable Information (PII), such as full names...

Summary

In this chapter, we finally got to explore our data in Metabase. After a brief tour, we spent a lot of the chapter in the Admin Panel learning how to enhance the metadata of our database and its tables. By adding descriptions, curating our field settings, and specifying foreign key relationships, we give users in our organization a better chance of finding what they want – and help them avoid making mistakes. All they need to do to get started is open the data reference!

We also learned how to restrict access to sensitive databases and data tables using permissions and groups.

Aside from all that, we got several glimpses of what Metabase actually does. Inadvertently, by browsing data, filtering it, and making metrics and segments, we introduced ourselves to some of the basic capabilities Metabase has to offer.

In the next chapter, we'll learn how to create questions – the main building blocks for everything in Metabase. Much of it will look familiar...