Master SQL Databases with 3 Bits of Knowledge and Vero

Are you a data driven Marketing Pro or Product Manager thirsty for data? Have you hit a wall with the many tools you are constantly switching between? If so, like many professionals in your industry, you have realized the limitations of single purposes data tools and canned reports. The only way to take your analytics to the next level is to get hands on with data.

What to do? If you're a busy Pro, then you likely don’t have the luxury to learn a new programming language like SQL (Structured Query Language). In many popular databases, SQL is the primary way you can create custom datasets and reports. At first blush it may look easy enough, but it can quickly become time consuming. This is where Vero can help you. With just the following 3 bits of knowledge, you can master any SQL database with an assist from Vero:

(1) Databases store Data in Tables

Okay, you probably already knew this but I wanted to make it concrete and add some more detail. A table consists of columns and rows and every column has a name by which you can reference it. You can query tables by choosing a subset of columns in the result and you can filter tables by applying a condition on its columns.

Example Tables

Both of the tables above have descriptive data sometimes referred to as Lookup or Dimension data. These tables could be combined into one bigger table, but there many good reasons to keep them separate: 1) Efficient storage and faster queries, 2) Joining to tables summarized at different levels (more on this later).

(2) Tables can be Joined with Simple Formulas

What if it wasn’t obvious that Apples are Fruits? I kid, I kid :). Notice the CategoryID column is available in both tables. In this case the names of the columns you want to join two tables by are the same, but it doesn’t matter. You can join any two tables on any set of columns. The above table can be joined by this very simple formula:

Categories.CategoryID = Products.CategoryID

Your joined result might look like this:

Products Joined to Categories Table

Now you can know with certainty Apples are indeed Fruits.

(3) Tables Store Different Types of Data

There are two primary types of tables in databases: 1) those that store descriptive data as seen above and 2) those that store transactional or event data. In the Vero Designer, we refer to the descriptive data tables as Dimension tables and transactional/event data tables as Fact tables.

Dimension tables contain data on Countries, Users, Customers, Employees, Dates, Products, Projects etc. The data columns of these tables might include columns like Name, Description, Sign Up Date, First Name, Last Name etc.

Fact tables on the other hand contain data on Orders Details, Page Visits, Events, Inventory, Sales Opportunities etc. They may have data columns like Order Date, Shipped Date, Sale Amount, On Hand Units, Opportunity Value etc.

Lastly, while you can join any two tables together you almost never want to join two fact tables directly together. For one, your DBA/IT guys are going to be mad because these queries are expensive and can take down a database. And two, you will get incorrect results. Take a look at the these two fact tables:

Example Web Impressions Fact Table and Order Details Fact Table

You can certainly join these tables on Date (Date = OrderDate) or ProductID. As you can see from the impressions table, if you sum up the impressions for ProductID 1 you will get 250. However, if you joined these two tables and then summed up impressions you will get 500. So wrong! Joining these two tables on ProductID could be visualized like this:

Notice that there is one ProductID 1 record for each combination of Impressions and Sale Amount. Applying a sum on Impressions here will give you the wrong number of 500.

Armed with the above knowledge you can point the Vero Designer at your SQL database and let Vero handle the rest. You only need to tell Vero if a table should be treated as a Dimension table (descriptive) or Fact table (transactional) and how joins should be created between two tables. In the above scenario, if you wanted a report that gives you ProductID, Sum of Impressions, and Sum of Sale Amount, follow these simple steps:

  1. Create a New Report
  2. Right click on the ProductID column in the Product Impressions Fact table and choose “New Dimension”, then give it the name ProductID
  3. Repeat the above step with the Order Details Fact table. Be sure to give the ProductID dimension the same exact name, ProductID
  4. Drag the SaleAmount column to the Selections drop zone, right click on it and choose Aggregate > Sum
  5. Repeat step 4 for the Impressions column. Vero will create a multi-step query to ensure you get the correct results as seen below:

For more on how Vero can help you get a serious handle on your SQL databases checkout out Tutorial series starting with a Quick Start Guide.