Academy

How to use SQLite as a datasource for icCube

7th September 2022

When it comes to reading data from files, icCube already has .csv, .json and .xml formats. For more complex usecases, however, these formats are not sufficient. CSV does not support types or multiple tables, and all of them don’t support SQL SELECT queries. SQLite is a library that solves these issues with an SQL database engine that writes and reads the data to a single file.

In this article we show how to read an SQLite database using icCube. We use the mystore.db database as an example:

The entity relationship diagram of the mystore database.

SQLite JDBC Driver Installation

icCube does not package the JDBC driver for reading SQLite databases. You have to download and setup the java classpath yourself. If you’re on Windows, follow these steps to install the JDBC-driver:

  1. Download the SQLite JDBC driver here.
  2. Move the downloaded .jar file to C:\Program Files\icCube\jdbc.
  3. In `\bin\icCube.ini`, replace the contents in [Class Path] with Class Path=C:\Program Files\icCube\8.2.1\lib\*.jar;C:\Program Files\icCube\jdbc\*.jar; This makes icCube load the sqlite-jdbc file which we moved to the jdbc folder in step 2.
  4. Now you can restart icCube and continue to the next section.

On Linux, you can update the JVM classpath in the icCube.sh file you are using for starting icCube.

Creating a SQLite Datasource

icCube supports generic JDBC datasources that allow for connecting to any database that has a JDBC driver. In the previous step, we installed the driver to read SQLite database files. Now, let’s use this driver to read the mystore example database. In the Builder:

  1. Create new datasource SQL > Generic JDBC.
  2. Set JDBC Driver > JDBC Driver Classname to org.sqlite.JDBC.
  3. In the server tab, set the URL to jdbc:sqlite:<full-path-to-sqlite-db>.
  4. In advanced, set Disable Read Only Mode to true.
  5. Now you can create & view all the tables in the database.

SQLite does not support date/time formats, they recommend storing dates as TEXT, REAL or INTEGER. The mystore database has a date column in the purchases table, which is a TEXT column. To read it as a date, we set Advanced > Date Converter Pattern in the datasource to `yyyy-MM-dd`. Now, you can change the column type to Date and icCube converts the text values to actual date values.

Schema

In this section, we discuss the schema based on the mystore database. You can download the finished schema here.

First, we add all the tables from the datasource in the Data Tables section.

In the purchases table, we set the date column to use the Date type. Remember the converter has been defined at the datasource level.

Next, in the semantic layer, we add the customer and product dimensions. Both have a single hierarchy and level with the id-columns used as keys. Lastly, we add a time dimension based on the date column.

For the measures, we make a measure group based on the purchases table with items as a measure. This group links to the customers and product dimensions using the foreign-key relations in the database.

Now we can deploy and load the schema.

Dashboard

In the dashboard editor, we create a new dashboard and select mystore as the schema.

Next, we add a table, a line chart and a donut chart to get the following result:

mystore dashboard

Next steps

You should now be ready to load your own SQLite datasources into icCube.

For more questions or help, please do not hesitate to contact us.

Resources

The files mentionned in this article are available as a ZIP file here.

By Tom van den Berg