Creating a relational database
You'll learn how to model and create a relational database schema on SlicingDice using our Control Panel.
When to use relational databases
SlicingDice is built to be schemaless, so every data that you have is stored only in a single table (called dimension in SlicingDice) based on a denormalized (non-relational) model. But, in some situations, you may think about using a relational model.
When some attributes of your data are updated constantly, you may consider using a relational model. An example is when a product is related to a client code. If you need to update for example the product color, in denormalized models you'll need to update several rows, but in a relational model, you can just update the product in its table.
You also need to think about using relational modelling when your dataset is complex, like Entities having their own varied attributes. As an example, let's think about a movie. A movie has its own attributes, like the director. But the director itself may have a whole range of attributes. Makes more sense to have information about a director in a separate dimension.
Support to Star Schemas
Actually SlicingDice only supports relational models based on Star Schemas
Types of dimensions
Take a look at the following relational database model based on a Star Schema , that will be used on this tutorial.
The image above shows four dimensions:
Fact_Sales. The three first
dimensions have a connection with
Fact_Sales. Using the above example, we can see
that we have two types of dimensions.
- Dimension Table: The dimension where you'll normally store data not related to time (non-time-series), and this data will be referenced by the Fact dimension.
- Fact Table: The dimension where you normally store events/transactions that have a lot of repetition and, because of that, will reference the other dimensions through a Foreign Key.
In this particular case, every sale will have a date and time (timestamp), a product and a store that executed
the sale. Every date, product, and store has its own row in their own dimensions. When a sale transaction is
registered, all the data of that sale will be referenced and related in the
Foreign Key Restrictions
In SlicingDice, a foreign key can only be set in a Fact Table Dimension.
So, a Dimension Table can't have a Foreign Key column type, but can be referenced.
Creating a database
To be able to model a new relational database you'll need to create a database first. On Control Panel, click on Create New Database and a 5-step wizard will be shown. The Step 1 is where you'll need to provide basic database information, like name, type and pricing model.
|Database Name||Yes||The name of your database. Can be edited at any time.|
|Database Labels/Tabs||No||Possible labels/tags you might want to associate to a database, in order to organize it. Can be edited at any time.|
|Database Description||No||The description of your database. Can be edited at any time.|
|Cloud Provider/Region||No||The cloud provider and its supported region that will be used to store and query your data. Cannot be edited after creation.|
|Database Type||Yes||Can either be Test or Production. It's only possible to change a Test database to Production, not the other way around.|
|Pricing Model||Yes||The pricing model you want to use for your database. Can be changed at any time.|
|Insertion Load||Yes||The insertion throughput you want your database to have. Can be changed at any time.|
|Query Load||Yes||The query throughput you want your database to have. Can be changed at any time.|
These parameters in the first step are the basic ones that you need to create a default database.
We're setting this database name to be
SalesDB and to be a Test
Notice that in the following steps you'll be able to define other parameters, but they aren't necessary in this tutorial, so you can ignore the steps 2, 3 and 4 by clicking on Save & Continue until completion.
On the next tutorial step we'll start modeling our database to be relational, creating all the dimensions we need.
Creating a dimension
You'll now need to create the
Fact_Sales dimension. Go to Databases and click on the
Edit icon on the database you have just created. Then go to Schema Definition.
Click on Create
New Dimension. You'll need to insert the following information for each of the dimensions
you're going to create:
- Dimension Name: The dimension name that will be shown on control panel.
- Dimension API Name: The dimension API name.
- Relational Model Type: Defines if you want to set the relational model type on your dimensions (it's the case for this tutorial). If the checkbox is selected, you can select one of these options: Fact Table (supports Foreign Key) or Dimension Table (doesn't support Foreign Key).
Remember that for the
Fact_Sales dimension you need so set the relational
model type as Fact Table and for the other dimensions you should set this parameter as
|Name||Relational model type|
After all items are set, click on Save Dimension. In order to follow this tutorial, we expect you to create the following dimensions.
Creating a column
Now, you'll need to relate the Fact table with the Dimension tables you have created through a definition of Foreign Keys that should be created as columns for the Fact table. For this example you'll need to create three Foreign Key columns and reference each one with each dimension table you have. In the Schema Definition, click on Create New Column. You'll need to insert the following information:
- Name: Column name that will be used and shown in Control Panel.
- API Name: Column API name.
- Description (optional): Description of this column, used to add more information about it.
- Dimension: Dimension that will contain this column (in this case Fact_Sales).
- Type: Data type supported by this column (in this case Foreign Key).
- Data Storage Type: Select if your column will store only the latest inserted value or all inserted values.
- Referenced Dimension (only for Foreign Key Data Type): Select which dimension will be referenced by the Foreign Key (select the Dimension Table that will be related to the Fact through this column).
Note about Foreign Key Reference
A Foreign Key will always refer to the
entity-id of the referenced
With everything defined, you can click on Save Column. Notice that your column will be successfully created and it will be shown on the schema tree for the database and dimension chosen. So now, you'll be able to use this database as a relational one.