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

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.

Looking for API?

If you prefer you can follow this same guide using our APIs.

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.
  • Using our Workbench.

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'll facilitate your learning and understanding.

Please select one:

Use case description:
<% useCase.description %>


Creating a database

For the purpose of this quickstart, you'll need to create a database.

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


How to use the JDBC and ODBC Drivers

In order to use the JDBC or ODBC driver, we kindly ask you to go to the Installing JDBC/ODBC guides page.

Creating columns

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

We'll use a SQL command for this, using our JDBC/ODBC drivers. As we'll explain ahead, SlicingDice can be enabled to auto-create columns if users try to insert data into a column that doesn't exist.

You probably noticed that we used a parameter called Dimension - Dimension - Dimension is a concept used in SlicingDice to describe a way to group columns for a database. Each database can contain multiple dimensions and each dimension can contain multiple columns. On SlicingDice every database has at least one dimension (created by default), that is similar to the concept of a table, on relational databases. Dimensions are normally used to insert the same kind of data you inserted on another dimension, but associating it for a different type of entity. But it's also possible to use a dimension like a table on a relational database, where you normally create tables and build relations between them. with the default value when creating the columns. This means that all these columns were created within the default dimension.

You don't need to learn it now, but if you're 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, SQL or our API.


Inserting data for columns

As you've 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 so you 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 are 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 section, 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 - <% useCase.attributeColumns().length %> attribute column(s) (such as <% useCase.attributeColumns()[0] %>) and <% useCase.eventColumns().length %> 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 examples presented below are focused 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'll 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 the following:

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'll be using a filter and a metric on a date histogram aggregation. This basically means that, we'll 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've executed 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's also other type of queries supported by SlicingDice, such as top values, exists and total, and we talk more about other query types ahead.


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.


4. SQL Quickstart Guide


Suggested Edits are limited on API Reference Pages

You can only suggest edits to Markdown body content, but not to the API spec.