Client:
The client is a Europe based goods manufacturing company. They have a large number of user base and wanted our help in visualizing their data so that they can track their progress.
Challenges:
As the client is very popular in the Europe region and their user base is also huge. So sales are also very high. In their production databases, there were more than 50 million rows for half of the year only. So processing that much of data was beyond the scope of the Klipfolio. Also, their production database was behind the firewall and only accessible from the company premises. The Client need was to draw the relevant graphs by retrieving the data from the production database server running on the premises of the company. So we had two challenges.
- How to establish a connection with the database server outside company premises.
- How to process that much data inside Klipfolio.
Solution:
First challenge resolution:
Our first problem was the connection. Although we had a solution i.e. access the database by using VPN and we were able to get connected with the database server by installing VPN on local system but Klipfolio runs AWS cloud, therefore, it was not possible in Klipfollio. Klipfolio can access only those database servers which are publicly accessible.
So we thought about another approach that was port forwarding and allowing the Klipfolio IP address for accessing the data. Klipfolio provides this method to access these types of databases.
We ask the client to whitelist Klipfolio IP addresses and enable port forwarding and we were able to access client’s database. So now we are successfully connected with the MSSQL database server and our first challenge has been solved.
Second challenge resolution:
Now, processing huge amount of data was a big task as Klipfolio can handle a maximum of 10 MB data.
Klipfolio limitation was an obstacle in our way to fetch data from the database server, therefore, we left the thought to fetch the data into Klipfolio and then process it.
The solution was to do processing at the server end and fetch only the result into Klipfolio and then use that data to build the charts. So we have written the queries in the way that all the processing was done at the server and we were getting only thousands of rows or sometimes hundreds of rows only. For this, we had used SQL aggregation functions.
By following this approach we were able to get most of the data directly from the server using the standard database connector of Klipfolio. But still, there was a minor problem. As there were millions of records so the processing of queries was taking a lot of time. Klipfolio standard connector has a limit on response time. If the response time is greater than 60 seconds, the request will be timed out.
But thanks to Klipfolio email connector and Microsoft SQL Server Management Studio(community edition). By using MSSM, we have executed the queries from our local system and then exported the query result into a CSV file. These queries were those which were taking more than 60 seconds to execute. Then, with the help of the Email Connector, we were able to send that data into Klipfolio.
So, by using the above-noted steps, we were able to deliver what the client needed.
Result:
In the starting, the client was not sure whether the problem will be solved or not as data was very huge but at the end, we were able to solve the client’s problem. The client is very happy with our services and after this project, we have worked for 3 more similar kinds of project. Till now, we have developed more than 40 dashboards having more than 120 charts and these numbers will increase as the client is still using our services.