What are Data Sources and Schemas
Datasources or Databases are libraries of tables that are stored together, those tables are made up of columns and rows as you might expect. These columns hold specific metrics and the rows are individual records in the tables. As we discussed earlier the databases are often (if not most of the time) relational and are either a production database or a replica database. In most cases you will be querying replica databases for information when creating the tables that you will be using to make charts.
Tables in Databases are very likely related to each other, hence the name relational databases. That relationship is assigned by the Database Architect in your organization when these libraries are built. Understanding those relationships is important, as it will help you most effectively use the database when planning and building your queries.
Tables within data sources are related to each other based on a relationship structure of foreign and primary keys. To get a better understanding of the relationship structure we must first understand the characteristics of a primary key and a foreign key.
Primary keys first rule is that they must be unique within the table. No two rows can have the same exact piece of information in the Primary Column. Think of this as the table is data on the houses that are on the street you live on, the mailing address would be the primary key. Or, in the united states your Social Security number is your primary key in the table of data on the population of taxpayers in the country.
Foreign Keys are column(s) in a table that are primary keys in other tables within the same database. There can be more than one per table, and there are no restrictions on duplication or definition.
This primary key/foreign key relationship is what you can use in Chartio to set up and use the ever important Join Path. We will discuss Joins and Join paths and how Chartio uses them in more detail later on, but at least a basic understanding of how these are used is important to know how Schemas are used.
A database’s schema os the organization of the tables within the database, or the blueprint of how the database is constructed. Also the schema might be referred to as the definition of how the tables in a database are mapped with regard to each other.
In the example below, the “activity” table and the “users” table both have a column “user_id”. In the “users” table “user_id” is the primary key and we want to relate these two tables on that set of like columns. Seeing as though the user_id column will not necessarily be unique in the activity table, it can be the foreign key linking these two tables.
Now in Chartio’s Visual SQL you can search from these two tables at the same time and the Join Path that will be automatically built into the query will be on the user_id column. You can now essentially query these two tables as one big table, and Visual SQL will do that coding for you.
How Data Sources are used in Visual SQL
When you want to create a table, using Visual SQL, the first thing you will want to know is what data you will want to be viewing. Do you want to know operating budget information? How about sales data? If you want to know how your company’s sales team is doing, you will want a column in your table for sales dollars. Companies call sales metrics by many different names, but for our purposes here we will refer to Monthly Recurring Revenue (MRR), a standard metric for subscription or SaaS companies.
Now that you know what you are looking for, you want to know where to look. This is where the database comes in. Databases, or structured sets of data held in a computer’s memory, once connected to Chartio as data sources, can be visualized in the “Schema” view in the Visual SQL creator in Chartio’s Chart Editor screen.
The area in the red box outlines the individual data tables that are in the database that is called “Digisign Demo Data”. The tables are Account Users, Accounts, Cohort, Documents, Events, Invoice, Signature, and Users. These tables are what make up the database “Digisign Demo Data” and the columns within those tables are what we are going to use to make the table we want to build.
We want the MRR column from the Accounts table. Using the Visual SQL editor I can grab the column from the Schema display and drag it to the dimensions box and then click run query. This will show me all of the fields in the MRR Column from our Accounts table.
In the next tutorial, we will explain how to query and aggregate values from database columns into measures and dimensions. If you would like to use Visual SQL, you can try it out with a Chartio free trial.