Customer Drill Down View

Oftentimes, your data is scattered across multiple sources. It can be stored in a csv file, an excel sheet, Google Sheets, or a database.

DataSiv's Data Queries and Transform components enable you to build an admin panel where a non technical user can query and drill down into all of these sources using SQL, without requiring to store them in a database or a single data warehouse.

As an example, let's build an admin panel on top of an inner join command on a Google Sheet where we store customers, and a SQL database query where we fetch invoices for each customer.

📘

This quickstart requires you to signup for DataSiv

If you haven't already, please sign up here

Create a new project.

We'll start off by using one of Datasiv's data query components to load in data into our system.
Data Queries enable you to run queries on top of your data sources. For example, you could run a SQL query, insert data into Google Sheets, Salesforce, or issue a REST API call.

Start off by selecting Add Data Query from the top menu. Choose Sql Api for your data source. We will be using our default database for this example.

Set the Resource to the default Chinook DB (builtin) (readonly) resource from the dropdown menu, and the query to Select * from Invoices. Make sure to set Automatic Execution to On Document Load.

Next, click Add Data Query and choose the Google Sheets Api.

Set the Spreadsheet Id to 1QjZsz6zcswvusaYLI3LlN5PA013IqupNRH05ZdpqAd8 and set the range to Customers!A1:M60. This will allow us to load the data into the document.

As before, set Automatic Execution to On Document Load. Congratulations! You've connected both data sources.

Next, we will be joining them together using a transform called Sql.

📘

What are transforms good for?

Transforms, such as the one we're about to create, allow you an easy way to mix and merge data sources together, filter them, or write code to get it into the right format before inserting it into a database. You can write raw javascript, client-side SQL, or other transforms to get started quickly.

Click Add Transform and choose Sql as your datasource.

📘

How to reference DataSiv components (such as queries)

All component properties are stored in a global key value store that other components in the app are able to access. You are able to reference other components using javascript dictionary terminology.

In this instance, we would like to reference the data* property from the sql0 SQL component. All query components are stored under the global queries** key, hence, we type in queries.sql0.data

For the SQL Query, write

Select * from {{queries.sql0.data}} q 
INNER JOIN {{queries.googleSheetsApi0.data.values}} g ON 
q.CustomerId = g.CustomerId

This {{}} syntax will allow you to pipe in the Google Sheets and SQL queries into the sql transform component.

Save the query as sql0.

📘

What is {{}} good for?

Based on Django's templated {{}} syntax, the special {{}} expression allows you to substitute in values from one set of objects in your application to another. For example, in the transform query , {{queries.sql0.data}} gets replaced by the data property of the sql0 query. The {{queries.googleSheetsApi0.data.values}} property of first entry in the transform result.

Next, add a Table frontend component to the grid.

Click the Edit Data property on the right hand sidebar and choose data.sql0.result as your datasource. This will connect the table to the outputs of your inner join between Google Sheets and SQL.

📘

Frontend component properties

By editing component properties, such as the Table component's data property, we can connect our frontend to backend queries, transforms, or other frontend components.

Congratulations! You're done. You've created a new panel where you've loaded in two distinct data sources, ran sql on them, and displayed it in the frontend (table).