With our new function “SQL Data Warehouse” you can easily export all your stored infos from zistemo to a SQL database. This gives you the full control of your data and you can create every reports or chart you can imagine. Optimal for BI analysis with Tableau or Google Data Studio. This feature is exclusively for our “enterprise” users. We automatically update every hour the database informations. How the hourly sync works:

Important notes:

1. Zistemo supports MySQL Version 5.6. or 5.7. See HERE, how to connect with MySQL 8.0;
2. Host field value is valid IP address or domain name
3. Setup your firewall. Add IP 159.100.250.182; 194.182.189.139; 194.182.188.162; 91.92.154.198; 91.92.155.52 to allowed list

4. If you have any issues with the permissions, please run this command:
   GRANT ALL PRIVILEGES ON `yours_db_name`.* TO `yours_user_name`@`159.100.250.182`;
   GRANT ALL PRIVILEGES ON `yours_db_name`.* TO `yours_user_name`@`194.182.189.139`;
   GRANT ALL PRIVILEGES ON `yours_db_name`.* TO `yours_user_name`@`194.182.188.162`;
   GRANT ALL PRIVILEGES ON `yours_db_name`.* TO `yours_user_name`@`91.92.154.198`;
   GRANT ALL PRIVILEGES ON `yours_db_name`.* TO `yours_user_name`@`91.92.155.52`;

   If you use MySQL 8 then run this sql command:
   ALTER USER `yours_user_name`@`159.100.250.182` IDENTIFIED WITH mysql_native_password BY ‘password’;
   ALTER USER `yours_user_name`@`194.182.189.139` IDENTIFIED WITH mysql_native_password BY ‘password’;
   ALTER USER `yours_user_name`@`194.182.188.162` IDENTIFIED WITH mysql_native_password BY ‘password’;
   ALTER USER `yours_user_name`@`91.92.154.198` IDENTIFIED WITH mysql_native_password BY ‘password’;
   ALTER USER `yours_user_name`@`91.92.155.52` IDENTIFIED WITH mysql_native_password BY ‘password’;

How to add the Integration

To start, go to the “Integration Tab”

Click “Add Integration”

and press “connect” at the Data Warehouse Integration

Insert your Host, Port, DB-Name, User Name and Password. These credentials you can create in your own MySQL Database or you can use any MySQL provider.

Use SSL Encryption

To use SSL encrypted connection to a database you just need to add the SSL certificates. To do so, activate “Use SSL”. After that you can upload your certificates.

Find also more infos HERE

Permissions for Users:
If you have any issues with the permissions, please run this command:GRANT ALL PRIVILEGES ON ‘zistemo’.* TO ‘user_name’@’159.100.250.182’;Change ‘zistemo’ to your given database name’user_name’ please change to your used username.

Set up your firewall and permissions

Make sure you add zistemo’s IP address to your firewall and add permission. You can run this command:

Cloud Application

We use Google Cloud Platform for our tutorials. If you don’t want to use it, feel free to use any Cloud Application you want to use. You can find here a step-by-step guide to connect your Google Cloud Platform to zistemo warehouse

Bring the Data to Life

The next step is to get the Data into an BI (Business Intelligence) Solution. We recommend Tableau or Google Data Studio. You can also do whatever you want with your data This is just an recommendation how to use it.

Here is a quick setup on the software “Tableau”:

1. Choose “MySQL” at the sidebar
2. Input your MySQL credentials as shown above
3. Inout your User credentials
4. Before you Login, you have to download the “MySQL” Driver. Please follow the instructions on the site.

After the successful installation/login, you need to restart tableau and connect/login again. SSL is not supported. In the next step you need to choose your Database:

As soon as your Database is loaded, you are ready to go. You can create any reports or charts you wish.

Enjoy!

We’re using the MySQL Workbench in the lessons.

Customers and Invoices

Learn how to create a custom query to get the customer ID, name, invoice number and invoice amount. Also we sort customers by name and group data by currency.

Push data immediately, filter by date and export to Excel

Learn how to immediately push data into the SQL Warehouse, filter data by date and export everything to Excel. Make sure you watched the video above first, since it uses the query from that lesson.

Build a report with approved timesheets on Google Data Studio

Learn how to build a query to get approved timesheets only, including project name, customer name, task and staff. After that we build a report on Google Data Studio. If you try it by yourself, please make sure that you using approve timesheet feature.