Knowage BI - SQL Server DataSource

Question

I have a question about knowage (opensource BI software - www.knowage-suite.com).

Can anyone tell me how create DataSource with a SQL Server connection?

Regards, Tomasz

Answer 1

Below are given steps for installing Knowage and setting up DB.

Install MySQL 1. sudo apt-get install mysql-server

2. Modified /etc/mysql/mysql.conf.d/mysqld.cnf and changed from

            bind-address            = 127.0.0.1 
            to
            bind-address            = *

3. mysql -u root -p

            ALTER USER 'root'@'%' IDENTIFIED BY ''mypswd ';
            GRANT ALL PRIVILEGES ON * . * TO 'root'@'%' IDENTIFIED BY ''mypswd';
            FLUSH PRIVILEGES;
            service mysql restart

Install Java

            sudo add-apt-repository ppa:webupd8team/java
            sudo apt-get update
            sudo apt-get install oracle-java8-installer
            sudo apt-get install oracle-java8-set-default 

            export JAVA_HOME=/usr/lib/jvm/java-8-oracle
            export PATH=$JAVA_HOME/bin:$PATH

Install Knowage 1. Download Knowage

            wget http://download.forge.ow2.org/knowage/Knowage-6_2_0-RC-CE-Installer-Unix-20180509.zip
            unzip Knowage-6_2_0-RC-CE-Installer-Unix-20180509.zip

2. Create Swap Memory (https://www.digitalocean.com/community/tutorials/how-to-add-swap-space-on-ubuntu-16-04)

            sudo swapon --show
            free -h
            df -h
            sudo fallocate -l 4G /swapfile
            ls -lh /swapfile
            sudo chmod 600 /swapfile
            ls -lh /swapfile
            sudo mkswap /swapfile
            sudo swapon /swapfile
            sudo swapon --show
            free -h  

3. Setup Knowage

           sh Knowage-6_2_0-RC-CE-Installer-Unix-20180509.sh -c 
   
   
           Used existing MySQL DB and it created knowage_ce with following tables.
   

4. Add Database Source in conf/server.xml

           <Resource name="jdbc/knowage" auth="Container"
     type="javax.sql.DataSource" 
     driverClassName="com.mysql.jdbc.Driver"
     url="jdbc:mysql://localhost:3306/knowage_ce"
     username="root"
     password="mypswd" 
     maxActive="20" maxIdle="10"
     maxWait="-1"/>
   

5. Download MySQL Connector and copy to lib dir

           cp mysql-connector-java-5.1.46.jar /home/babbarkrishan/knowage/Knowage-Server-CE/lib/
   

6. start the knowage server in /bin

           sh startup.sh
   

Hope it will resolve your problem.

Regards,

Krishan

Answer 2

0> put MSSQL JDBC driver into `./<your_knowage_installation_directory>/lib` folder

1> log in (on web-UI) as administrator  
2> data source  
3> click on red '+' sign  
4> give a label and choose your dialect (SQL-Server)
5> fill your credential (need SQLSErver auth, not active directory)
6> insert into JDBC this: `jdbc:sqlserver://<YOUR_SERVER_IP>`  
7> insert `com.microsoft.sqlserver.jdbc.SQLServerDriver` as driver

8> create your dataset as a query (remember `use <your_db_name>` at the beginning of your query)

link to MSSQL jdbc drivers