Do you have data sources that are located on-premises and you want to access them in the cloud without moving or copying them? Do you want to query and visualize your data in real time without compromising performance or security? If you answered yes to these questions, then you might be interested in Qlik Data Gateway Direct Access, a feature of Qlik Cloud Data Integration that allows you to do just that.
Qlik Data Gateway Direct Access is a feature that enables you to access data from on-premises sources such as databases, files, or applications without having to move or copy the data to the cloud. This way, you can leverage the benefits of cloud computing while keeping your data where it is. You can also query your data in real time and get the most up-to-date insights from your data sources.
In this blog post, I will show you how to use Qlik Data Gateway Direct Access to connect to a SQL Server database and create a data flow that uses direct access to query the data. I will also show you how to use Qlik Sense to create an app that visualizes the data from the direct access data flow.
Setting up a connection to a SQL Server database
The first step is to set up a connection to a SQL Server database that is located on-premises. To do this, you need to install and configure Qlik Data Gateway on the machine where the database is hosted. Qlik Data Gateway is a software component that acts as a bridge between your on-premises data sources and Qlik Cloud Data Integration.
Once you have installed Qlik Data Gateway, you need to register it with your Qlik Cloud account and configure it with your SQL Server credentials.
After you have registered and configured Qlik Data Gateway, you can create a connection to your SQL Server database in Qlik Cloud Data Integration. To do this, you need to go to the Connections tab and click on Create Connection. Then, you need to select Qlik Data Gateway as the connection type and choose the gateway that you registered earlier. Next, you need to select SQL Server as the source type and enter the name of your database. You can also specify a schema or a table if you want to limit the scope of your connection. Finally, you need to click on Test Connection and Save if everything is working fine.
Creating a data flow that uses direct access
The next step is to create a data flow that uses direct access to query the data from your SQL Server database. A data flow is a graphical representation of the steps and transformations that are applied to your data before it is loaded into Qlik Sense. To create a data flow, you need to go to the Data Flows tab and click on Create Data Flow.
Then, you need to drag and drop your SQL Server connection from the left panel onto the canvas. This will create a source node that represents your connection. Next, you need to click on the source node and enable the Direct Access option in the right panel. This will tell Qlik Cloud Data Integration to use direct access instead of loading the data into memory or storage.
Next, you can add other nodes and transformations to your data flow as needed. For example, you can add filters, joins, aggregations, calculations, or custom SQL statements to manipulate your data. You can also add other connections or sources if you want to combine your SQL Server data with other data sources.
Finally, you need to add a destination node that represents where you want to load your data. In this case, we want to load our data into Qlik Sense, so we need to select Qlik Sense SaaS as the destination type and enter the name of our app. You can also specify a table name if you want to load your data into a specific table in Qlik Sense.
Creating an app that visualizes the data from the direct access data flow
The last step is to create an app in Qlik Sense that visualizes the data from the direct access data flow. To do this, you need to go to Qlik Sense and open the app that you specified in your destination node. Then, you need to click on Add Data and select Qlik Cloud Data Integration as the source type. Next, you need to select your direct access data flow from the list and click on Add Data.
This will load your data into Qlik Sense and create an associative model that links your fields and values together. You can then use Qlik Sense’s powerful analytics and visualization capabilities to explore and analyze your data; create charts, tables, maps, or other types of visualizations to display your data in different ways. You can also use Qlik Sense’s smart search and natural language processing features to ask questions and get answers from your data.
One of the benefits of using direct access is that you can query your data in real time and get the most up-to-date insights from your data sources. Whenever you interact with your app or refresh your data, Qlik Cloud Data Integration will send a query to your SQL Server database and return the results to Qlik Sense. This way, you can always see the latest state of your data without having to reload or refresh your data flow.
Conclusion
In this blog post, I showed you how to use Qlik Data Gateway Direct Access to access data from on-premises sources without moving or copying it to the cloud. I also showed you how to create a data flow that uses direct access to query the data in real time and how to create an app that visualizes the data in Qlik Sense. I hope you found this blog post informative and helpful. If you want to learn more about Qlik Data Gateway Direct Access or Qlik Cloud Data Integration, you can check out the following video. Thank you for reading and happy data integration! 😊 Do you want to know deeper? Stay tuned!