How to Integrate Freshdesk with SQL Server for Advanced Analytics
Every organization is unique. A customer service solution that works for one company may not work for another. Integrations help widen the scope of use cases that a customer service solution can handle, in addition to existing features. And Freshdesk has a Marketplace full of integration solutions to satisfy every customer need.
Now let’s get a little more specific and ask a related question – what if your organization uses software that utilizes Microsoft SQL Server? Can Freshdesk integrate with SQL Server?
SQL Server is one of the major relational database management systems (RDBMS). Its primary function is to store and retrieve data. But aside from the database engine, there are services for reporting, integration, and data analysis. By name, they are identified as SQL Server Reporting Services (SSRS), SQL Server Integration Services (SSIS), and SQL Server Analysis Services (SSAS), respectively. These are the family of tools that are useful for integration and analytics.
But Why Microsoft SQL Server?
Companies of various industries and sizes have trusted SQL Server for their database management needs. This is evident from the reviews in Gartner Peer Insights. Among the industries mentioned are Services, Finance, Manufacturing, and Healthcare.
Availability of various toolsets from Microsoft and third-party providers is also a deciding factor in acquiring this database product, as these toolsets support the design, development, and management of databases.
Aside from what it can do and its reputation, integrating with an SQL Server is an obvious choice because you may have built or bought software that uses SQL Server.
It is also important because your users and customers rely on it daily for information storage and retrieval. For example, if you are a human resource company involved in job placements, you may depend on it to manage information about:
- Applicant registration and profile
- Hiring Companies – the paying clients for the human resource company
- Job applications
- Interviews and interview schedules
- Examinations
- Acceptance and hiring
- Contracts
- Salaries and benefits
And then a time came, where you needed to have a customer service solution that was ready-to-use and can integrate into your system. And this is when Freshdesk emerged, giving you an option to combine the two systems. The end goal is a 360-degree customer view, which includes customer or client information coming from Freshdesk and your system or key performance indicators from various departments or regional groups.
To achieve this end goal, integration is the first step, which is followed by data analysis. And to begin with, integration, let’s check out the roadmap below.
Roadmap to Integrate Freshdesk with SQL Server
Below is a high-level roadmap to give you an idea of how this integration will happen. This holds true no matter what your industry and company size is.
To make this happen, you will need a project team. This team includes a select group of users as stakeholders and your technology experts or a service provider.
1. Design Your Staging Area
As simple as it may sound, you cannot just combine Freshdesk and your system. You need a staging area where the two systems will meet. And that staging area can also be a SQL Server database.
After defining your report requirements, the next thing to do is to design the staging area. If you have a team of experts within your company or you have a trusted service provider, they can do this for you. They will produce fact tables and dimension tables that will feed on the automated data integration later.
2. Map the Data in Freshdesk and Your System
In this stage, your project team’s goal is to align the same information from Freshdesk and your system. For example, if you have a client named ABC Company, it is then identified in Freshdesk as a company with an ID of 1. But in your system, 001 is used as an identifier. In the staging area, the ABC Company will have an ID of 001 and a Freshdesk ID of 1 in one table.
The technical aspects of this mapping can be done by your technical team. To visualize this mapping, see Figure 1 below.
Figure 1. Data mapping sample of Freshdesk Companies table and your system’s Clients table.
To expand it further, Freshdesk also includes information for Contacts, Agents, and Groups, to name a few. What if there are any counterparts in your system? You may call it differently in your system, but the description or purpose can be the same. For example, Groups in Freshdesk can be called Departments in your database.
If you get the idea, then map all the data in Freshdesk with a corresponding table in your SQL Server database, and get all the relevant fields to your staging area from both Freshdesk and your system. And don’t forget to note the primary keys of each because that’s your key in linking Freshdesk data to your system’s data.
Mapping data can be a long process. But it’s worth the time in your integration efforts. Besides, you don’t want to make the wrong data analysis.
3. Import the Tickets and Other Relevant Data from Freshdesk to the Staging Area
The reason you had Freshdesk in the first place is because of customer support and tickets. You also don’t have this information from your own system. So, you import all the relevant data like Tickets, Groups, Agents, and all their dependencies.
Tickets can be one of your fact tables in your data analysis. Agents and Groups can be dimension tables. Remember, the more rich and granular the data from Freshdesk is, the better the inputs for data analysis.
4. Import All Relevant Data from Your System to the Staging Area
What information do you need in your SQL Server database that is relevant in your data analysis? Import them too into the staging area.
Does your system deal with sales? That can be another fact table. And the Products list can be another dimension table.
5. Clean and Validate your Data
Depending on your system’s database, you may also need to perform data cleansing. This involves removing duplicates and replacing null values. Sometimes, this is the hardest part.
6. Use an Integration Tool to Automate the Integration Process
You can use a variety of tools to integrate Freshdesk with SQL Server. And that’s the topic of the next section. With these tools, you can automate the integration, so it happens while you sleep or do some other work.
Options to Integrate Freshdesk with SQL Server
Your options to integrate can be on-premise or a cloud solution.
SQL Server has its integration tool called SQL Server Integration Services (SSIS). If you have some talented staff who knows this, it is an on-premise solution to integrate. But to make this happen, you need a data connection manager for Freshdesk. And then, data components that will act as a source and a destination. In our case, the source is Freshdesk, and the destination is your on-premise SQL Server database. One of the vendors who specialize in SSIS and Freshdesk is KingswaySoft. They have the SSIS Productivity Pack for Freshdesk. And you can also integrate both ways: Freshdesk to SQL Server and the reverse.
Another option is a cloud solution and data replication from Freshdesk. There are several vendors for this, like Blendo, xPlenty, etc. But today, you are going to learn how this is done in Skyvia.
To configure replication, first, you need to create connections to Freshdesk and your SQL Server database. To connect to Freshdesk, you need to specify the URL to connect to, the API version to use, and the API key. See Figure 2 for an example.
Figure 2. Creating a connection to Freshdesk in Skyvia.
To configure an SQL Server connection, you need to specify the corresponding connection parameters – the server’s IP address, user name, password, and database name. See Figure 3 for a sample.
Figure 3. Creating a connection to a SQL Server database from Skyvia.
Figure 3 shows an example of an SQL Server database connection over the Internet. For cases when it’s not available from the Internet, Skyvia provides an Agent application and has an option to create agent connections.
After the connections are configured, the rest is straightforward. You create a new replication package:
If necessary, you can also configure a schedule to run the package automatically and keep the database data up to date.
What’s Next?
After your integration is up and running like clockwork, the next level is doing the dashboards and reports. The staging area, which is a SQL Server database, is ready with its fact and dimension tables.
You may get insights from the increasing number of tickets of a client company from Freshdesk and the downward trend of sales from your system. There might be a connection. You can analyze it per period, per group, or region.
But which tool will you use for data analysis? Like I said earlier, SQL Server is a family of tools that include data analysis. Since you have a license to SQL Server already, you can use SQL Server Analysis Services (SSAS) to create OLAP cubes. Then you can display it in either SQL Server Reporting Services (SSRS) or Power BI.
If SSAS is too complex for you, Power BI can get the data directly from your staging area. Then, inspect the data model and relate the tables. Finally, design the reports you need.
Takeaways
Here are the highlights of the post:
- Integration is very possible if your company uses Freshdesk and has a customized system for daily operations with a SQL Server database.
- After getting the report requirements for analysis, you need to design a database called a staging area. This is where Freshdesk and your system will meet.
- You need to map the data from Freshdesk and your system. This is how the two systems will meet.
- Import the necessary data from Freshdesk and your system for the fact and dimension tables.
- Automate the integration using an on-premise or cloud solution.
- You learned about Skyvia and how easy it is to integrate Freshdesk with SQL Server.
- After a working automated integration, you can analyze your data from the staging area using data analysis tools like SQL Server Analysis Services or Power BI.
If you like this blog post, then please feel free to share it on your preferred social media platforms.
