Modelling a database schema

You'll learn how to model a database schema on SlicingDice managing dimensions and columns using our Control Panel or API.


Modeling your schemas using Control Panel

Creating a dimension

Dimension is a way to group columns for a database, similarly how a table works in a database. Each database can contain multiple dimensions and each dimension can contain multiple columns. Check more details about the dimension concept in the references.

You can create a dimension using the control panel during the schema definition step while editing your databases. Click on Create New Dimension and define a name and an API name to your dimension. After that click on Create dimension.

. Schema definition step
    that allows you to create dimensions

Your dimension will be successfully created and it will be shown on the schema tree for your database.

Notice that while creating your dimension, there's a checkbox so you can select if you want to create it considering a relational model type. You can read more about dimension relationships here so you can check if this can be your use case.

Creating a column

A Column works exactly like regular columns in a database or on a spreadsheet. Every column on the database is always associated with a dimension and a data type. Check more details about columns concept and the supported data types in the references.

You can create a column using the control panel during the schema definition step while editing your database. Click on the Create New Column button and define the following properties:

  • Name: Column name that will be used and shown in Control Panel.
  • API Name: Column API name used by SlicingDice to perform operations using this column. You can see the applied restrictions for API names on the warning above.
  • Description: Description of this column, mostly used to add more details to it.
  • Dimension: Dimension that will contain this column.
  • Type: Data type supported by this column. Check our supported data types.

You should also define if you want to store a list of values for this column or just the most recent value inserted.

With everything defined you can click on Create column.

. Schema definition step
    that allows you to create columns

After that your column will be successfully created and it will be shown on the schema tree for the database and dimension chosen.

Deleting a dimension

You can delete dimensions using the control panel during the schema definition step while editing your database.

Select the dimension that you want to delete on the schema tree and then click on Delete Dimension.

Schema definition step
    that allows you to delete dimensions

After this, your dimension will be successfully deleted from your database and dimension, disappearing from your schema tree and all data stored on this dimension will no longer be available to be queried.

Deleting a column

You can delete a column using the control panel during the schema definition step while editing your database.

You should choose and click on the column that you want to delete on the schema tree and then click on the Delete column red button that will appear.

Schema definition step
    that allows you to delete columns

After this, your dimension will be successfully deleted from your database, disappearing from your schema tree.

Editing a dimension

You can edit a dimension using the control panel during the schema definition step while editing your database.

You should choose and click on the dimension that you want to edit on the schema tree and then click on the Save dimension button that will appear. Notice that you can only edit the Name parameter that is shown on control panel.

Schema definition step
    that allows you to edit dimensions

After this, your dimension will be successfully edited and you'll be able to see your changes on the schema tree and clicking on your dimension name.

Editing a column

You can edit a column using the control panel during the schema definition step while editing your database.

You should choose and click on the column that you want to edit on the schema tree and then click on the Save column button that will appear. Notice that you can only edit the following parameters:

  • Name: Column name that will be used and shown in Control Panel.
  • Description: Description of this column, mostly used to add more details to it.
Schema definition step
    that allows you to edit columns

After this, your column will be successfully edited and you'll be able to check your changes accessing your schema tree.


Creating a column relation

Every column within a Dimension in SlicingDice is completely separated and unrelated to other columns by default, so the data inserted into one column has no relation to the data inserted on other columns, being just related with the defined entity.
This can be a challenge according to your data model while dealing with event columns. You can solve this by creating columns relations between these columns: any data inserted within a group of associated columns will be always stored together, maintaining data consistency between all the event columns of the group.

You can create a column relation using the control panel during the schema definition step while editing your database. Click on the Create New Relation button and define the following properties in the new window:

  • Name: Name of the column relation.
  • Dimension: Dimension that contains the columns to be related.
  • Columns: The columns to be associated (you need to check at least two).
Fields to
    be defined on the column relation creation

To finish the relation creation, click on Create. Now you will be able to see it in your database schema tree.


Deleting a column relation

You can delete a column relation using the control panel during the schema definition step of your database edition. Click on the relation in the database tree that you want to delete, check if these are the columns to become 'unrelated' and click on Remove Relation to finish the operation.

Pop-up to
    confirm the column relation deletion

Creating a dimension relation

If you want to model your database as a relational database in SlicingDice, it is possible through dimension relations. SlicingDice supports the creation of a Fact 'Table' and Dimension 'Tables'(which, again, are dimensions in SlicingDice).
Reminder: in SlicingDice, Dimensions are a group of columns of your database. Check more details about the dimension concept in the references.

You can create a dimension relation using the control panel during the schema definition step while editing your database. First you will need to create a dimension as a Fact Table by selecting it on the dropdown menu during the dimension creation.

Creating a dimension
    relation in the control panel

After your Fact Table was created, you can now create foreign key columns in it (columns that stores values referring to other entities from a different dimension), to reference the data in your Dimension "Tables". To do it, create a column and select the Foreign Key option on the Column Data Type dropdown menu.
Also specify the dimension to be referenced by this foreign key on the Referenced Dimension field.

Creating a foreign key
    in the control panel


Modeling your schemas using API

Creating a dimension

Dimension is a way to group columns for a database, similarly how a table works in a database. Each database can contain multiple dimensions and each dimension can contain multiple columns. Check more details about the dimension concept in the references.

You can create a dimension using our API through your own code using your favorite programming language. To do it, you'll need to define an API POST request using the /dimension endpoint as you can see on the example below.

Notice that you can create more than one dimension per request.

$ curl -X POST https://api.slicingdice.com/v1/dimension \
    -H 'Authorization: MASTER_API_KEY' \
    -H 'Content-Type: application/json' \
    -d '[
	{
		"name": "users",
		"api-name": "users"
	},
	{
		"name": "sales",
		"api-name": "sales"
	}
]'

The following parameters can be defined in your API request:

Parameter Mandatory Description
name Yes Column name as it'll show in Control Panel, up to 80 characters. All ASCII characters are allowed, except " , " and " _ ".
api-name Yes Dimension name, up to 80 characters, as it'll be referenced in API requests. The api-name should be lowercase containing just letters, numbers and dashes.

Creating a column

A Column works exactly like regular columns in a database or on a spreadsheet. Every column on the database is always associated with a dimension and a data type. Check more details about column concept and the supported data types in the references.

You can create a column using our API through our SDK clients or through your own code using your favorite programming language. To create a column you'll need to define an API POST request using the /column endpoint as you can see on the examples below.

Notice that you can create more than one column per request.

$ curl -X POST https://api.slicingdice.com/v1/column \
    -H 'Authorization: MASTER_API_KEY' \
    -H 'Content-Type: application/json' \
    -d '[
        {
            "name": "User State",
            "api-name": "state",
            "type": "string",
            "description": "State where the user lives",
            "storage": "latest-value"
        }, {
            "name": "User Age",
            "api-name": "age",
            "type": "integer",
            "storage": "latest-value"
        }
    ]'
from pyslicer import SlicingDice
slicingdice = SlicingDice(master_key='MASTER_API_KEY', uses_test_endpoint=True)

insert_data = [
    {
        "type": "string",
        "storage": "latest-value",
        "api-name": "state",
        "name": "User State",
        "description": "State where the user lives"
    },
    {
        "type": "integer",
        "storage": "latest-value",
        "api-name": "age",
        "name": "User Age"
    }
]
print(slicingdice.create_column(insert_data))
import com.slicingdice.jslicer.SlicingDice;
import java.io.IOException;
import org.json.JSONObject;
import org.json.JSONArray;

public class Example {
    public static void main(String[] args) throws IOException {
        SlicingDice slicingdice = new SlicingDice("MASTER_API_KEY", true);

        JSONArray insertData = new JSONArray()
        .put(new JSONObject()
            .put("type", "string")
            .put("storage", "latest-value")
            .put("api-name", "state")
            .put("name", "User State")
            .put("description", "State where the user lives"))
        .put(new JSONObject()
            .put("type", "integer")
            .put("storage", "latest-value")
            .put("api-name", "age")
            .put("name", "User Age"));
        JSONObject result = slicingdice.createColumn(insertData);
        System.out.println(result.toString());
    }
}
require 'rbslicer'
slicingdice = SlicingDice.new(master_key: 'MASTER_API_KEY', uses_test_endpoint: true)

insert_data = [
    {
        "type" => "string",
        "storage" => "latest-value",
        "api-name" => "state",
        "name" => "User State",
        "description" => "State where the user lives"
    },
    {
        "type" => "integer",
        "storage" => "latest-value",
        "api-name" => "age",
        "name" => "User Age"
    }
]
puts slicingdice.create_column(insert_data)
const SlicingDice = require('slicerjs');

const usesTestEndpoint = true;
const slicingdice = new SlicingDice({masterKey: 'MASTER_API_KEY'}, usesTestEndpoint);

const insertData = [
    {
        "type": "string",
        "storage": "latest-value",
        "api-name": "state",
        "name": "User State",
        "description": "State where the user lives"
    },
    {
        "type": "integer",
        "storage": "latest-value",
        "api-name": "age",
        "name": "User Age"
    }
];

slicingdice.createColumn(insertData).then((resp) => {
    console.log(resp);
}, (err) => {
    console.error(err);
});
<?php
use Slicer\SlicingDice;
$usesTestEndpoint = true;
$slicingdice = new SlicingDice(array("masterKey" => "MASTER_API_KEY"), $usesTestEndpoint);

$insertData = array(
    array(
        "type" => "string",
        "storage" => "latest-value",
        "api-name" => "state",
        "name" => "User State",
        "description" => "State where the user lives"
    ),
    array(
        "type" => "integer",
        "storage" => "latest-value",
        "api-name" => "age",
        "name" => "User Age"
    )
);

print_r($slicingdice->createColumn($insertData));
?>
using System.Collections.Generic;
using Slicer;
using Newtonsoft.Json;

namespace SlicerTester.Console
{
    class Program
    {
        static void Main(string[] args)
        {
            var slicingdice = new SlicingDice(masterKey: "MASTER_API_KEY", usesTestEndpoint: true);

            var insertData = new List<dynamic>{
                new Dictionary<string, dynamic>{
                    {"type", "string"},
                    {"storage", "latest-value"},
                    {"api-name", "state"},
                    {"name", "User State"},
                    {"description", "State where the user lives"}
                },
                new Dictionary<string, dynamic>{
                    {"type", "integer"},
                    {"storage", "latest-value"},
                    {"api-name", "age"},
                    {"name", "User Age"}
                }
            };

            var result = slicingdice.createColumn(insertData);
            System.Console.WriteLine(JsonConvert.SerializeObject(result).ToString());
        }
    }
}
package main
import (
    "fmt"
    "github.com/SlicingDice/slicingdice-go/slicingdice"
)
func main() {
    keys := new(slicingdice.APIKey)
    keys.MasterKey = "MASTER_API_KEY"
    slicingdice := slicingdice.New(keys, 60)
    slicingdice.Test = true

    insertData := []interface{}{
        map[string]interface{}{
            "type": "string",
            "storage": "latest-value",
            "api-name": "state",
            "name": "User State",
            "description": "State where the user lives",
        },
        map[string]interface{}{
            "type": "integer",
            "storage": "latest-value",
            "api-name": "age",
            "name": "User Age",
        },
    }

    fmt.Println(slicingdice.createColumn(insertData))
}

The following parameters can be defined in your API request:

Parameter Mandatory Description
name Yes Column name as it'll show in Control Panel, up to 80 characters. All ASCII characters are allowed, except " , " and " _ ".
type Yes The actual type of the column according to the supported data types.
api-name No Column name, up to 80 characters, as it'll be referenced in API requests. Usually, the api-name is a lowercase version of the name, replacing spaces and non-alphanumeric characters by dashes. If you don't inform an api-name, one will be automatically generated for you.
description No Describe what this column is about, useful when the name may introduce ambiguity.
dimension No The dimension that will contain the column created. If you don't specify a dimension the column will be created on the default dimension.
storage No Defines whether the column stores only the latest value inserted (latest-value) or all inserted values (list-of-values).

If everything goes fine in your API request, you'll receive a success message.

Deleting a dimension

You can delete a dimension using our API using your favorite programming language. To delete a dimension you'll need to define an API DEL request using the /dimension endpoint as you can see on the example below.

Notice that you can only delete one dimension per request and that you should specify just an existent dimension api-name.

$ curl -X DEL https://api.slicingdice.com/v1/dimension \
    -H 'Authorization: MASTER_API_KEY' \
    -H 'Content-Type: application/json' \
    -d '{
	"api-name": "users-dimension"
}'

If everything goes ok, you'll receive a success message.

Deleting a column

You can delete a column using your favorite programming language. To delete a column you'll need to define an API DEL request using the /column endpoint as you can see on the example below.

Notice that you can only delete one column per request and that you should specify just an existent column api-name.

$ curl -X DEL https://api.slicingdice.com/v1/column \
    -H 'Authorization: MASTER_API_KEY' \
    -H 'Content-Type: application/json' \
    -d '{
	"api-name": "age"
}'

If everything goes fine, you'll receive a success message.

Editing a column

You can edit a column using our API using your favorite programming language. To edit a column you'll need to define an API PUT request using the /column endpoint as you can see on the example below.

$ curl -X PUT https://api.slicingdice.com/v1/column \
    -H 'Authorization: MASTER_API_KEY' \
    -H 'Content-Type: application/json' \
    -d '{
	"api-name": "age",
    "name": "new-age",
    "description": "New column name"
}'

The following parameters can be defined in your API request:

Parameter Mandatory Description
api-name Yes Valid and existent api-name so SlicingDice can identify and edit it.
name No Column name to be edited as it'll show in Control Panel, up to 80 characters. All ASCII characters are allowed, except " , " and " _ ".
description No Describe what this column is about, useful when the name may introduce ambiguity.

If everything goes fine, you'll receive a success message.


Creating a column relation

Every column within a Dimension in SlicingDice is completely separated and unrelated to other columns by default, so the data inserted into one column has no relation to the data inserted on other columns.
You can solve this by creating columns relations: any data inserted within a group of associated columns will be always stored together, mantaining data consistency between all the columns of the group.

You can create a column relation by defining an API POST request using the /column/relation endpoint as shown on the examples below.

curl -X POST https://api.slicingdice.com/v1/column/relation \
    -H 'Authorization: MASTER_API_KEY' \
    -H 'Content-Type: application/json' \
    -d '{
            "name": "association1",
            "columns": ["column1", "column2"],
            "dimension": "default"
        }'

The following parameters can be defined in your API request:

Parameter Mandatory Description
name Yes Column relation name to be edited as it'll show in Control Panel, up to 80 characters. All ASCII characters are allowed, except " , " and " _ ".
dimension No Dimension that contains the columns to be related. If you don't specify a dimension the default dimension will be used.
columns Yes The columns to be associated in the relation (you need to list at least two).

If you want to check the current relations in your database, you can define an API GET request using the column/relation endpoint.


Deleting a column relation

You can delete columns from a column relation by defining an API DELETE request using the /column/relation endpoint as shown on the example below.

curl -X DEL https://api.slicingdice.com/v1/column/relation \
    -H 'Authorization: MASTER_API_KEY' \
    -H 'Content-Type: application/json' \
    -d '{
            "name": "association1",
            "columns": ["column1", "column2"],
            "dimension": "default"
        }'

Creating a dimension relation

If you want to model your database as a relational database in SlicingDice, it is possible through dimension relations. SlicingDice supports the creation of a Fact 'Table'(which is actually a dimension in SlicingDice) and Dimension 'Tables'(which, again, are dimensions in SlicingDice).
Reminder: in SlicingDice, Dimensions are a group of columns of your database. Check more details about the dimension concept in the references.

You can create a dimension relation by defining a special parameter relational-model-type ("fact" or "dimension") during the creation of your dimensions for each of the dimensions of your relational schema, as shown on the example below.

curl -X POST https://api.slicingdice.com/v1/dimension \
            -H 'Authorization: MASTER_API_KEY \
            -H 'Content-Type: application/json' \
            -d '[
           {
              "name":"Fact_Sales",
              "api-name":"fact_sales",
              "relational-model-type":"fact"
           },
           {
              "name":"Dim_Product",
              "api-name":"dim_product",
              "relational-model-type":"dimension"
           },
           {
              "name":"Dim_Store",
              "api-name":"dim_store",
              "relational-model-type":"dimension"
           },
           {
              "name":"Dim-Date",
              "api-name":"dim_date",
              "relational-model-type":"dimension"
           }
        ]'

After your Fact and Dimension "Tables" were created, you can relate them by creating the foreign key type of columns in the Fact Table and referencing the appropriate Dimension "Table" with it.

To create a foreign key in your Fact Table, create a column defining the parameters: type as 'foreign-key', referenced-dimension as the dimension name to be referenced by this key and dimension as the dimension name of your Fact Table (as well as other parameters needed during any column creation).

You can check the example below to understand how it can be done:

curl -X POST https://api.slicingdice.com/v1/column
            -H 'Authorization: MASTER_API_KEY \
            -H 'Content-Type: application/json' \
            -d '[
          {
            "name": "date_id",
            "api-name": "date_id_fact",
            "type": "foreign-key",
            "referenced-dimension": "dim_date",
            "storage": "latest-value",
            "dimension": "fact_sales"
          },
          {
            "name": "store_id",
            "api-name": "store_id_fact",
            "type": "foreign-key",
            "referenced-dimension": "dim_store",
            "storage": "latest-value",
            "dimension": "fact_sales"
          },
          {
            "name": "product_id",
            "api-name": "product_id_fact",
            "type": "foreign-key",
            "referenced-dimension": "dim_product",
            "storage": "latest-value",
            "dimension": "fact_sales"
          }
        ]'