Pentaho BI Server comes with the HSQLDB (HyperSQL DataBase) embedded database server. This application is an open source database server that runs on the Java platform. With HSQLDB we don't need a separate database server to get the web application up and running.
The three databases that come with CE are hibernate, quartz, and sampledata. They are used for storing Pentaho's server configuration, user security and authorization, job schedules, and data samples used by report samples.
The database's physical file is located in [BISERVER]/data
. Here you can find the .script
, .lck
, .properties
, and .log
files associated with each database. The file with the .script
extension is the datafile, .lck
is the locking file, .log
is the user activities audit file, and .properties
is the configuration for the database.
Let's try to explore what's inside the database using a database manager tool that comes with HSQLDB. Start your console application, and execute the following command:
java -cp [BISERVER]datalibhsqldb-1.8.0.jar org.hsqldb.util.DatabaseManagerSwing
After a while you'll be asked to fill in the connection details to the database. Use the connection details as shown in the following screenshot, and click on the OK button to connect to the hibernate
database. The following screenshot shows the connection settings:
The Database Manager layout consists of a menu toolbar, a menu bar, and three panels: Object Browser Pane, SQL Query Pane, and Result Pane, as shown in the following screenshot:
In the query pane, type a SQL command to query a table, followed by Ctrl + E:
Select * from users;
We will get a list of data that are part of the PUC users.
The following screenshot shows the default Pentaho users listed in the Result Pane:
Try to explore other tables from the Object Browser pane, and query the content in the SQL Query pane. You will soon find out that all PUC settings and session activities are stored in this database.
The other databases include quartz, which stores data that is related to job scheduling, and sampledata that provides data to all the PUC reporting and data-processing examples distributed with the BI Server.