This post will show you how to configure or add a new datasource configuration to Saiku Community Edition. A datasource is essentially the name given to the connection setup between a database and a server. One of the most frustrating parts of Saiku’s datasource configuration is writing the datasource definitions for cubes.

In this post, we will guide you through the process of adding any datasource to Saiku’s datasource definitions. Saiku supports a wide range of data sources, enabling you to explore data in real-time directly from the source.

Additionally, you can add more than one datasource at a time in Saiku, depending on your requirements. Some of the data sources supported by Saiku are listed below:

1.Microsoft SQL Server
2.Microsoft Analysis Services
3.Oracle Database
4.Oracle Essbase
5.MongoDB
6.MySQL
7.PostgreSQL
8.Cloudera Impala
9.Actian Vectorwise
10.Amazon Redshift
11.Teradata
12.Vertica

Now, let’s walk through how to add new datasource configurations for some of the most frequently used databases in Saiku:

1. How to Add PostgreSQL DB Datasource to Saiku (Community Edition)

type=OLAP
name=dsname
driver=mondrian.olap4j.MondrianOlap4jDriver
location=jdbc:mondrian:Jdbc=jdbc:postgresql://192.212.12.132:5432/databasename;
Catalog=../webapps/saiku/Example/Demo.xml;;
JdbcDrivers=org.postgresql.Driver;
username=user
password=pass

2. How to Add Oracle DB Datasource to Saiku (Community Edition)

type=OLAP
name=dsname
driver=mondrian.olap4j.MondrianOlap4jDriver
location=jdbc:mondrian:Jdbc=jdbc:oracle:thin:@192.132.11.124:1521:XE;
Catalog=../webapps/saiku/Example/Demo.xml;
JdbcDrivers=oracle.jdbc.driver.OracleDriver;
username=user
password=pass

3. How to Add MSSQL Datasource to Saiku (Community Edition)

type=OLAP 
name=dsname 
driver=mondrian.olap4j.MondrianOlap4jDriver 
location=jdbc:mondrian:Jdbc=jdbc:jtds:sqlserver://localhost:1433/databasename;
Catalog=../webapps/saiku/Example/Demo.xml;
JdbcDrivers=net.sourceforge.jtds.jdbc.Driver;
username=user 
password=pass

4. How to Add MySQL Datasource to Saiku (Community Edition)

type=OLAP
name=dsname
driver=mondrian.olap4j.MondrianOlap4jDriver-
location=jdbc:mondrian:Jdbc=jdbc:mysql://localhost:3306/databasename;
Catalog=../webapps/saiku/Example/Demo.xml;
JdbcDrivers=com.mysql.jdbc.Driver;
username=user
password=pass

Configuration Explanation:

• type: This specifies that it is an OLAP engine.
• name: You can specify any name of your choice for your datasource.
• driver: This specifies the Mondrian driver.
• location: This property contains several parts separated by semicolons. For example, jdbc:mondrian:Jdbc=jdbc:mysql://localhost:3306/databasename is the JDBC convention for a MySQL database. You can use a similar JDBC convention for other databases. Here, “localhost” represents the host, and “databasename” is the name of the database to which you want to connect.
• Catalog: The catalog defines the path to the Mondrian XML file, which is typically found in the web-app folder of Tomcat.
• username & password: These will vary depending on the database you’re connecting to.

Default port number of the different database are as follow.

1. PostgreSQL:5432
2. Oracle:1521
3. MSSQL:1433
4. MYSQL:3306

JDBC Drivers for Various Databases

1. PostgreSQL: com.mysql.jdbc.Driver.
2. Oracle: net.sourceforge.jtds.jdbc.Driver
3. MSSQL: oracle.jdbc.driver.OracleDriver
4. MYSQL: org.postgresql.Driver

Conclusion:

In conclusion, this is a brief introduction to how you can configure or add new datasource configurations to Saiku Community Edition. By following the steps outlined above, you will be able to connect to various databases with ease. For more in-depth learning about Saiku Analytics, click here.