Database View Scripts

Databases store data in Tables. The design of these Tables, and how they structure data, follows rules. Third normal form is a common method used to structure Application databases. Those systems that sit behind platforms, such as web and mobile. Business Intelligence Analytical systems tend to use Dimensional modelling .

While these data modelling rules provide guidance they do not specify a uniform structure for the database. Database modelling is a task performed by the engineering team. As a result, each database tends to be unique.

It is unlikely that your Databases will have data structured in a way that meets the requirements as laid out in Datasets. We are going to have to transform the data into a form that Database Sync understands

Database Queries are used to extract data from a database. Structured Query Language (or SQL) is the most common way of doing this. SQL scripts pull data from one or more Tables and return the results as a new Table. The SQL determines the attributes that are contained in this new Table.

SQL Queries can be saved to the Database, and given a name. This is known as creating a View.

The following section describes how you can Views transform the bespoke structure of your database into a set of common Views that Database Sync will understand.

In building our Customer Data Layer we are concerned with extracting data as described in Datasets. Namely Customer, Product and Purchase History Data. The following sections describe the rules for creating Views for each of the Datasets:

Pulling the data into Database Sync monitors your Database Schema looking for Views that match the prescribed naming conventions.

The Views that have been found are listed under the connection to the database. See Database Security and Authentication to setup database connections.

Pressing the Refresh List button asks the Database Sync service to look again for new Views. This will ask the Database Sync service to reach out to your database and refresh the list of compatible datasources.

Enable the syncing of data from your View by selecting it from the list. Switch it on via the slider in the top right of the dialogue.

You can also rename the Data Source, provide a Description and assign friendly names to each of the Attributes using this Dialogue.

Viewing Customer Data

The Customers and the attributes provided by your new Database View will merged into the Customer Data Layer. As a result you will now be able to see your new Customer records, and their attributes, alongside any Customer records you have added from any other system, both other Databases and Integrations.

Here you can see an example of the Customer View showing a Customer tab with data pulled in from an Amazon Redshift (circled in red). The other tabs in the Customer View shows data from MailChimp, pulled in via a built-in Integration. These are circled in blue.

Non-relational Database systems

The description above describes the most common form of Database system, the Relational, or RDBMS. This type includes platforms such as MySQL, PostgreSQL, Microsoft SQL Server, Oracle DB, Maria DB.

An alternative breed of databases, known collectively as NoSQL Systems, is becoming more relevant. These are systems like MongoDB, Neo4j, Cassandra, etc. If you are using any of these please get in touch with us via the Supported Onboarding. We are familiar with these systems and can help you onboard data from these platforms with ease.