Deep Dive: What is SQL Modeling?

While using SAP Data Warehouse Cloud, you may have noticed the letters “SQL” in the Data Builder. SQL – structured query language – is a computer language that lets you talk to a database. For example, if you want to send a birthday promotion to your customers, SQL will let you quickly find any customers that have a birthday in August. With SQL, you can also create and modify tables in a database.

Most data warehouses consist of two things: A database to collect all of the copied data from the production system that stores all the raw data, and a query engine so you can ask questions. For example, “How many customers do I have with the first name Sue?” is a question you may have. With SAP Data Warehouse Cloud, you can ask these questions in the Data Builder via a graphical view or via SQL. They both retrieve information from your database; the differences are in how you ask the questions. SQL retrieves information with text-based commands and the graphical view lets you drag and drop elements into your model and visually build your connections. Behind the scenes, the same things are happening.

The fact that SQL is a computer language can make it intimidating. To begin using it, you will need to know the language’s capabilities, grammar, and vocabulary. Because of this initial learning curve, most companies will have data analysts to help you retrieve the information you need from the database. However, if you are interested in learning more, we can go back to the question of how many customers we have with the first name “Sue.”

SQL – HOW MANY CUSTOMERS ARE CALLED SUE?

To find how many of our customers have the first name, “Sue,” let’s take a look at an example shoe shop called Best Run Shoes. The data model for Best Run Shoes has a customer_table. This table has 100 unique customers, with information like customer_id, customer_firstname, email_address, cutomer_birthday, and the customer’s last_purchase_date. SQL can search through this database to find how many customers are called Sue. The SQL query to do this could look like this:

SELECT

COUNT(customer_table.customer_firstname)

FROM

customer_table

WHERE

customer_table.customer_firstname = ‘Sue’

This SQL query would return only the total number of customers with the first name “Sue.”

While knowing the number of customers with the first name “Sue” is great, it’s even better to understand what products they are buying. So, let’s use SQL to calculate how many Nike shoes our “Sues” are buying. For this, we need to add another table into the mix.

To make use of two different tables, they need to have a structure that lets them connect with each other. In our example, Best Run Shoes also has a sales_table that has all the shop’s sales. Things like the transaction_id, brand_item_sold (for example, “Nike”), and the date would all be in there. However, in this table, Best Run Shoes has also included the “customer_id” from the customer_table, so they know which sale belongs to which customer.

The connection between these two tables is made possible by something called primary and foreign keys. All tables in a data model will have a primary key. In our case, this is the “transaction_id” from the sales_table and the “customer_id” from our customer_table. They are primary keys because every primary key entry in the table will be unique, and none of the values will be blank or NULL. In this example, only our sales_table has a foreign key, which is the “customer_id” from the customer_table. Unlike the primary key, the foreign key won’t always be unique. It may also be blank or NULL (for example, not all sales have a customer, so they don’t have a sales customer_id).

To get insights into the relationship between these two tables, we can join them together. To do this with SQL, we need to join these two tables on a shared key (i.e., “customer_id” from sales_table on “customer_id” from customer_table). Once joined, we can count the number of Nike shoes bought by customers with the first name “Sue.” The following is the SQL query for this:

SELECT

COUNT(sales_table.brand_item_sold)

FROM

sales_table

INNER JOIN

customer_table

ON

sales_table.customer_id = customer_table.customer_id

WHERE

customer_table.customer_firstname = ‘Sue’

AND

sales_table.brand_item_sold = ‘Nike’

This SQL query would return the total number of Nike shoes bought by customers with the first name “Sue.” This sort of information could be used to better target our product promotions to our customers called Sue.

If you would like to learn more about SQL, you can find additional information and courses online.