When it comes to reading data from files, icCube already has
.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:
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:
- Download the SQLite JDBC driver here.
- Move the downloaded
- In `\bin\icCube.ini`, replace the contents in
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.
- 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:
- Create new datasource SQL > Generic JDBC.
- Set JDBC Driver > JDBC Driver Classname to org.sqlite.JDBC.
- In the server tab, set the URL to
- In advanced, set Disable Read Only Mode to true.
- 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.
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.
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:
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.
The files mentionned in this article are available as a ZIP file here.
By Tom van den Berg