SlicingDice Documentation

SlicingDice - The All-in-One Solution Documentation

Welcome to the SlicingDice documentation. You'll find comprehensive guides and documentation to help you start working with SlicingDice as quickly as possible, as well as support if you get stuck. Let's jump right in!

Get Started    Guides

SQL Quickstart Guide

First time checking our docs? You might want to start with the right foot by reading our Documentation Guide first.

The goal of this guide is to help you get started with SQL on SlicingDice in 20 minutes or less. If you have any trouble following the steps please let us know so we can help you and improve it.

Introduction

At the end of this guide, you can expect to:

  • Know how to create a database.
  • Know how to create columns (attribute and event).
  • Know how to connect and use our JDBC and ODBC drivers or our API-based SQL endpoint.
  • Insert data in a database.
  • Analyze the inserted data with different query types.

SQL access method

There are two ways for using SQL on SlicingDice:

  • Using our JDBC and ODBC drivers.
  • Using our API-based SQL endpoint.

Please, select below which method do you want to use on this Quickstart Guide.

Please select one:

Use case selection

In order to have a better and personalized experience on this guide, please select below the use case that looks like more similar to yours, as it will facilitate your learning and understanding.

Please select one:

Use case description:
<% useCase.description %>


Creating a database

For the purpose of this quickstart, you don't need to create a database, as we will be using an auto-generated Demo Database Key below with a brand new ephemeral test database created exclusively for you.

This is the Database key we will be using in this guide:

You can later check this page to see a step by step guide on how to create a test or a production database using our control panel.

Anyway, don't worry about it for now.


How to use the JDBC and ODBC Drivers

In order to use the JDBC or ODBC driver, we kindly ask you to download SQL Workbench/J, a free, DBMS-independent, cross-platform SQL query tool. Although this guide uses SQL Workbench/J, you are free to use any other SQL query tool you already have installed.

  1. Download and install the SlicingDice's JDBC or ODBC driver:

  2. Download and install the SQL Workbench/J tool:

Note

SQL Workbench/J requires the Java Runtime Environment (JRE) be installed on your system. Ensure you are using the correct version of the JRE required by the SQL Workbench/J client. To determine which version of the Java Runtime Environment is running on your system, do one of the following:

  • Mac: In the System Preferences, click the Java icon.
  • Windows: In the Control Panel, click the Java icon.
  • Any system: In a command shell, type java -version.

For information about installing and configuring the Java Runtime Environment, go to https://www.java.com.

  1. Driver configuration guide:
    Open this page on a new window to see step-by-step how to add SlicingDice's drivers to the Workbench/J tool.

  2. Database connection settings:
    Copy and paste this line below on the URL field:

jdbc:slicingdice:APIKeys=<% apiKey %>

As you can see, this line above contains the ephemeral Demo Database Key that was generated for the purpose of this quickstart guide.

  1. Test the database connection:
    In order to make sure you have successfully connected to the database, copy, paste and execute this SQL query below.
SELECT * FROM default;

Once you run it, you should get one line with the quantity of entities on the database.

Success! Now you are all set to move forward and insert and query data on your SlicingDice database.

If you are having any issues to connect to your database or if your query returned a different result, please, contact us through the online chat.


Creating columns

Before inserting any data, we first need to create the columns that will store our data.

At this moment, the only way to create new columns is to use SlicingDice's API or Control Panel. We are currently working to implement the possibility of creating columns using SQL.

In order to make this guide quick and dynamic, we will use the SlicingDice's API to make a request and add the columns we are going to use in the next section of the guide (below, Inserting data). The difference between the types of columns will also be explained there.

Move forward and click on the "Execute" button above.

You probably noticed that we used a parameter called Dimension with the default value when creating the columns. This means that all these columns were created within the default dimension.

What is a dimension?

A dimension is a way to group columns for a database, similar to a table on relational databases. Each database can contain multiple dimensions and each dimension can contain multiple columns. You can understand more about this concept reading our Dimension section.

You don't need to learn it now, but if you are interested, you can check the page how to create columns to understand the process of creating or modifying columns on SlicingDice, using the control panel or our API.


Inserting data for columns

As you selected the <% useCase.name %> use case, consider you have this data displayed on the spreadsheet below and that you want to insert it on your SlicingDice database.

As you can see above, we have some rows and columns.

Rows

The rows are identifying the entities. As you can read on the SlicingDice Key Concepts page. An entity is basically to "whom" the data will be inserted and stored on SlicingDice. They are like a primary key on other databases.

To make things clear, let's use the data from spreadsheet above. As you can see, there are <% useCase.entities().length %> entities (such as <% useCase.entities()[0] %>, <% useCase.entities()[1] %>, etc). These identifiers are the Entity ID for whom you are going to insert data in the columns <% useCase.columns()[0] %>, <% useCase.columns()[1] %>, etc.

Just for you to know, when you create a database or a dimension, SlicingDice automatically creates an Unique ID attribute column called Entity ID (with API name entity-id) for that dimension. This column will be transparently used by SlicingDice to store the Entity ID of all entities you insert on the dimension.

Columns

There are two types of columns on SlicingDice: Attribute Columns and Event Columns.

Again, take a look on the spreadsheet above, as you can see, the <% useCase.eventColumns()[0] %> column is storing event data (the value and a timestamp), and the other columns are storing attributes, like <% useCase.attributeColumns()[0] %>.

  • Attribute Columns is a column type used to store any data not associated to a date/time. For example: storing the value <% useCase.insertData[useCase.entities()[0]][useCase.attributeColumns()[0]] %> for the <% useCase.attributeColumns()[0] %> column. In this case the column and its value is not associated to a specific date/time.

  • Event Columns is a column type used to store data that is associated to a date/time. For example: storing the value <% useCase.insertData[useCase.entities()[0]][useCase.eventColumns()[0]][0].value %> on the date/time <% useCase.insertData[useCase.entities()[0]][useCase.eventColumns()[0]][0].date %> for the <% useCase.eventColumns()[0] %> event column. In this case it's important to know when the event happened.

You can later check this page for a detailed comparison on attribute and event columns, but for now you are all set to move forward.

SQL Insertion

As you can see below, the data presented in the spreadsheet is now represented as a SQL INSERT statement.

placeholderAPI

Copy, paste and execute this SQL insert commands above on Workbench/J (or any other SQL tool you decided to use).

Once you have done it, we can move to the next sections, that will show you how to make queries on your database.

Pro Tip

If you change any value or add a new row on the spreadsheet above, it will automatically update this SQL INSERT statement below. So before moving forward and executing the command below, play with it, add some values on the spreadsheet or change what is there.


Quick recap

So far, you have successfully created columns using SlicingDice API and inserted data on the database for <% useCase.entities().length %> entities, using <% useCase.columns().length %> columns, which <% useCase.attributeColumns().length %> of them is/are attribute column(s) (such as <% useCase.attributeColumns()[0] %>) and <% useCase.eventColumns().length %> is/are event column(s) (such as <% useCase.eventColumns()[0] %>).

Great! Now you are ready to start making queries on your data.


Querying the inserted data

It's important to remember that SlicingDice is an analytical-focused (OLAP) database service, not a transacional (OLTP) database, so all the query example presented below have focus on effectively analyzing your data.

Retrieving all data

Let's start by making sure the data you inserted above is actually correctly stored on your SlicingDice database.

In order to do that, we will run a query similar to SELECT * FROM default;, that at SlicingDice we categorize as a result (data extraction) query type. We call it result (data extraction) because the main objective of this type of query is not really to make data analysis, but instead simply retrieve the values from columns stored on the database, according to some condition.

Result (data extraction) queries are ideal to answer this kind of questions:

Let's make the following query using your SQL workbench:
<% useCase.resultQueryQuestion %>


Count entities query

Now that we know the data was correctly inserted, we can move to the more analytical-focused queries, such as count entities.

Let's say you want to know how many unique (distinct) entities you have that satisfies the query conditions. At SlicingDice we categorize this type of query as a count entities, because in fact you are simply counting how many entities you inserted on your database, for a specific dimension.

Considering that the entities in this case are <% useCase.entity %> (like <% useCase.entities()[0] %>), the count entities queries are ideal to answer this kind of questions:

Let's make the following query using your SQL workbench:
<% useCase.countEntitiesQuestion %>


Count events query

Counting entities is great, but what if we want to count events, like how many times a specific event happened within a certain period of time?

Count events is the way we categorize this type of query, because you simply want to know the number of times some data was inserted for the event columns.

Considering that in this case the events are <% useCase.events %> (like <% useCase.eventsExample %>), the count event queries are ideal to answer this kind of questions:

Let's make the following query:
<% useCase.countEventsQuestion %>


Aggregation queries

Now it's time to go for more advanced queries, that includes GROUP BY and metrics conditions, such as MIN, MAX, AVG, among others.

Aggregation is how we categorize this type of query, and they are really powerful for generating insights from your data by calculating metrics, such as maximum, minimum, sum and average, as well as crossing data from different columns to generate pivot tables.

Aggregation queries are ideal to answer this kind of questions:

SlicingDice has some subcategories of the aggregation query category, such as: multi-level aggregation, metrics aggregation, filtered aggregation, date histogram aggregation and the combinations of all these queries together.

Below we are going to see many examples of the aggregation query type.

Single level aggregation

Let's start with a simple GROUP BY, that we categorize as a single level query aggregation.

Let's make the following query using your SQL workbench:
<% useCase.singleLevelAggregationQuestion %>

Multi-level aggregation

Now let's move for a query using multiple GROUP BY conditions, that we categorize as multi-level aggregation.

This type of query will basically take all the Top X values for the column A and cross it with all the available values of the column B, in order to find the final Top Z values and its quantity of entities.

Let's make the following query using your SQL workbench:
<% useCase.multiLevelAggregationQuestion %>

Filtered date histogram metrics aggregation

To finish, let's make a really powerful analytical-focused query, that will use the AVG, WHERE, and GROUP BY conditions.

According to the SlicingDice's query classification, in this query below we will be using a filter and a metric on a date histogram aggregation. This basically means that, we will first filter the entities that we want to perform the aggregation on, then we will define the metric we want to see and finally for which period of time we want this metric to be calculated.

Let's make the following query using your SQL workbench:
<% useCase.filteredAggregationQuestion %>


Result (data extraction) with metrics

As a final example, let's imagine you want to retrieve again the data you stored on your SlicingDice database, but now also calculating some metrics for it.

In order to do that, you can again use the query that at SlicingDice we categorize as a result data extraction, as shown in this first example, but now with some additional parameters, that will allow you to perform calculations (metrics) while retrieving data.

Let's make the following query using your SQL workbench:
<% useCase.resultWithMetricsQuestion %>


Quick recap

Considering all the queries you ran so far, you can see that SlicingDice is fully prepared to make really powerful analytics-focused queries on your data.

Above you learned how to retrieve the original data you stored on SlicingDice using the result data extraction type of query, also applying some metrics for columns. You have also learned how to count entities, count events and aggregations.

There is also other type of queries supported by SlicingDice, such as top values, exists and total, but for a short quickstart guide you are really well covered.


Next steps

If you want to review the data insertion and querying flows in a more detailed way, we recommend you to read the how to insert data and how to make queries pages.

If you are comfortable with what you just saw on this guide, you can now go deeper into the column data types.