Building a local data warehouse has been a huge help to our team. We’ve become so much more efficient and significantly boosted our response time to data trends. In this post I’ll walk through the requirements and step-by-step process to build your own data warehouse with a Zendesk connector.

Our team at Cobalt is proud to have Zendesk as our long-standing partner for providing customer support ticketing to our clients. We thrive on a data-driven culture that values real-time analysis across multiple platforms. To provide this, we utilize various data sources such as Dynamics 365 and Replicon (for time tracking) and monitoring platforms such as Pingdom, Solarwinds, and Azure Metrics for our infrastructure.

We blend these sources using Microsoft Power BI, a powerful business intelligence program, to simplify our modeling and reporting experience. With Power BI, we can deliver various deliverables such as TV dashboards, weekly reports, customer summaries, billing reconciliation, quarterly reports, forecasting, and more. Utilizing tools like Power BI, we can integrate that expertise into our products and provide the best possible service to our clients.

The problem with ODBC for online services

For years, we had relied on ODBC drivers, which are local system files that establish database connections to connect to some of these online sources, including Zendesk. We found that this limited the more advanced functions from Power BI, most notably query folding, which would allow for incremental refresh of Zendesk data.

Incremental Refresh in Power BI allows for efficient data refresh by only updating the changes in data rather than reloading the entire dataset, significantly improving performance and reducing resource consumption.

Not having this option was a limitation that resulted in data refreshes taking as long as an hour, limiting our responsiveness to data and exacerbating the development time on the semantic models. A new approach was needed.

Comparing CRM systems?

Our compact 2024 CRM Buying Guide has price and feature comparisons for the most popular CRM solutions, questions to ask CRM vendors, and more. Take a copy with you now.

GET YOUR COPY

Why we chose Kingswaysoft for Zendesk data replication to SQL Server

Another tool at Cobalt’s disposal is KingswaySoft SSIS (SQL Server Integration Services). This software is a data integration tool that simplifies the creation of complex data migration and ETL (Extract, Transform, Load) for blending various Microsoft Dynamics applications and other data sources.

We recently implemented Kingswaysoft for Dynamics data replication to enhance the performance of our portals and e-commerce experience. As a result, we gained access to additional integrations that could be used to improve Power BI reporting. One of these tools was a Kingswaysoft Zendesk connector, which we utilized to build a local data warehouse that could store replicated Zendesk tables. Here is how we did it.

Building a local data warehouse

Requirements

  • A Kingswaysoft SSIS Productivity Pack
  • Microsoft Power BI Premium License and Premium Workspace
  • Microsoft Power BI On-Premises Data Gateway
  • Microsoft Visual Studio
  • Microsoft SQL Server 2016 or higher

Set Up the Environment

Install Kingswaysoft SSIS Productivity Package on a system where Microsoft Visual Studio is installed.

Ensure you have access to both Zendesk credentials and your SQL Server instance. The SQL Server instance should have a blank database created and ready for this project.

Create a Visual Studio Project

Open Visual Studio.

Create a new Integration Services Project.

Create a Data Flow Task

Drag and drop a Data Flow Task from the SSIS Toolbox into your Control Flow area.

Configure the Data Flow

Double-click the Data Flow Task to switch to the Data Flow tab.

Drag and drop the “REST Source” from the SSIS Toolbox to the Data Flow Area

Set Up Zendesk Data Source

Double-click the “REST Source” to configure it. On the “Connection Manager” dropdown, select “<New…>”

In the newly opened window, select the “Service Name” dropdown and look for “Zendesk”

Enter your Zendesk domain and your Zendesk credentials. Test the connection and click “OK” when successful.

Choose the “Support” API and the desired table, e.g., Tickets. For Tickets, opt for Incremental load if applicable, as it will load all historical data.

Later, a section for a ticket import based on a time filter will be explained. That method will turbo the refresh time.

Optionally inspect and select the columns you wish to import. You can also rename this step to describe which source table is being extracted. You must repeat this setup for each table you want to include.

Add and Configure Destination Database

Drag and drop a “Premium ADO.NET Destination” component into the Data Flow area.

Connect the Zendesk Source to this destination by dragging the bottom arrows to the “Premium ADO NET Destination.”

Double-click the ADO.NET Destination and select “<New…>” for the connection manager, then enter your SQL Server credentials.

Select the destination database for storing the data.

Customize Table Mapping and Data Types

In the ADO.NET destination settings, choose the Insert or Upsert option and set the batch size (e.g., Row by Row). You must select the destination table that has the same column(s) as the source. If you do not have this, refer to the “Create table” section below.

Review and adjust the column mappings. Change data types where necessary (e.g., from nvarchar (255) to nvarchar (MAX) or NTEXT) to accommodate longer text fields.

Auto-create SQL Table

Review screen in the auto-created SQL table step of setting up a local data warehouse.

Use the “Create Table” option within the destination component to generate the SQL table automatically based on your mappings. This will automatically detect the source columns that you linked to this destination with the flow arrows. Later, you may use the “Update” button to sync changes.

Test the Data Flow

Execute the package by pressing the play button to test the data flow. Check for errors and ensure data is loaded correctly into your SQL Server database.

Repeat for Additional Tables

For each additional Zendesk table you wish to replicate, repeat steps 3 to 9. Create a new Data Flow for each table to maintain clarity and organization.

Finalize and Deploy the SSIS Package

Save your SSIS project.

Next, you must deploy the package to your SQL Server Integration Services to have this run on a schedule.

Create a Scheduled Job in SQL Server

In SQL Server Management Studio (SSMS), connect to your SQL Server instance.

Navigate to the SQL Server Agent and create a new job.

Add an “Execute SQL Server Integration Services Package” step in the job steps.

Configure this step to run the deployed SSIS package.

Schedule the job according to your desired frequency (e.g., daily or weekly).

Your job should be similar to the above screenshot.

Create Dataflow

Connect your Power BI semantic model to this SQL Server Database. We recommend using a Dataflow in a Premium Workspace, as it will be available to other semantic models across the organization. You will leverage the On-Premises Data Gateway to connect to your SQL Server instance for data refreshes. 

Benefits of Implementing Our New Data Warehouse

Our experience with KingswaySoft SSIS and Power BI at Cobalt has significantly boosted our data management and analysis capabilities. By switching from ODBC drivers to a more robust system with KingswaySoft for Zendesk data replication, we’ve created a more real-time and efficient environment for data warehousing.

This change has optimized our data refresh processes by limiting the data synced from Zendesk to SQL Server and significantly boosting our response to data trends. Our dedication to utilizing state-of-the-art technology, such as KingswaySoft SSIS in conjunction with Power BI, highlights our commitment to providing exceptional business intelligence expertise. 

Get Our 2024 CRM Buying Guide

Our CRM Buying Guide is a compact resource with essentials you can use:

  • Price and feature comparisons for major CRM options
  • Insider questions to ask CRM vendors and your team
  • A great first look at Dynamics 365 and Cobalt
Download Your Copy Now