STT - Troubleshooting Qlik Data Gateway - Direct Access

[Troy_Raney]

Environment

• Qlik Cloud

Transcript

Hello everyone and welcome to the January 2024 edition of Techspert Talks. I'm Troy Raney and I'll be your host for today's session. Today's presentation is Troubleshooting Qlik Data Gateway Direct Access with our own Nadia Butler. Nadia, why don't you tell us a little bit about yourself?
Hello, my name is Nadia. I'm an Escalations Engineer been Qlik for five years. I have worked with Qlik products for about 10 years. I'm currently a part of the Escalations team and my main focus for the past year has been Data Gateway, but also been working with Qlik Cloud issues.
Fantastic, thanks. So, today we're going to talk about how Data Gateway for Direct Access works; Nadia is going to take us through a demonstration of how to set it up and go through some best practices with that; and we're definitely going to do a little bit of troubleshooting and talk about what to do when things don't go as expected. Now Nadia, for those of us who aren't that familiar, what is Direct Access Gateway and how does it work?
Basically, the Qlik Data Gateway is a technology solution.
Okay.
And facilitates secure access to organizational data for analytics purposes. This Data Gateway operates behind the organization's firewall; that eliminates the need to open an inbound firewall port. It enables Qlik Sense Cloud application to establish a secure connection to that a store behind the firewall.
Okay. So, it basically opens up a door so you can securely load data from on-premise databases to your Qlik Cloud tenant. Is that right?
That’s correct. The connection is a strictly outbound, encrypted, and mutually authenticated; ensuring a high level of security.
Awesome. All right. So, where can people find more documentation about Direct Access?
We have a section under our Qlik Help site.
Okay.
So, here is where we have all the information available for Direct Access.
And what are some best practices for setting this up?
Yes. So, we have some outlined by R&D; for now, we have three there, that are more coming. The three that we have documented as of now: try to use a separate Data Gateway for development purposes. Just to make sure that we have production on a separate server and avoid overloading or impact production if something happen in their Dev server.
Okay.
The second one is: for performance, we suggest to install the Data Gateway as close as possible to your data source. So, just to avoid any latency.
That makes sense.
And we need to install the Data Gateway on a Windows server that is completely dedicated. We listed some of the Qlik products here because these Qlik products use connectors; and might be a conflict with the Data Gateway. But when we say dedicated, is dedicated. Like no Qlik products, no other products. It's a must to have a dedicated server in order for us to provide support.
That makes sense.
Also, we have system prerequisites in here.
Sure.
We suggest to go over those.
Can you show us how to set it up and actually use these best practices?
Sure. I'll show a section in the QMC from where we can download.
So, this is your Qlik Cloud tenant?
That’s correct. There are two things that we need to do: we need to download the installer. Select Deploy, select that Gateway - Direct Access.
Okay, you downloaded directly from your Qlik Cloud and since that's constantly being updated (I see we're on version 1.6.2) that will set up the latest release?
That’s correct. The one that we have available to download is always going to be the latest release. We suggest to make sure that they have the latest version, because it has most of the fixes. It also has new features.
Fantastic.
The other thing that we need to do is to create a Space. This is used with the sole purpose to manage the access. So, I'm going to go ahead and create one. It has to be a Managed or a Shared Space; and I'm going to put “DGTest2,” I’m going to be the owner for this Space.
Okay. So, you're the owner of the Space with the Data Gateway. What rights would another user need to use that Direct Access Gateway?
Yeah, in order to use that Data Gateway, they need Can Consume Data privilege on the Space where the Data Gateway resides.
All right. Okay. So, what's next?
So, now I'm going to go ahead and install.
Okay. So, we're on a machine where we're going to install the Data Gateway; and is that the installer already downloaded?
That’s correct.
Run as administrator.
Yeah. Now these usually install some .NET dependencies. If those are not already installed; it might require to restart the computer. I already have those, so it's not going to go through that process.
Okay. So, if it requires any additional .NET features that aren't already installed, it might require a restart?
Yeah, you will be guided through the process to install them one by one. Okay, leave the default path.
And it's finished!
That's correct. So, now what we need to do is configure the Data Gateway.
Okay.
First go to the path that Data Gateway was installed.
Okay, we start configuring from here. What's first?
Set which Qlik Cloud tenant I'm going to connect to.
Okay, and where can you find the commands that you're pasting in here?
In our Qlik Help site.
Okay.
Looking through the process it’s going to guide you a step by a step.
Great. Okay. So, now the Data Gateway has the tenant address?
That’s correct. Now we're going to generate the Registration Key. This is used to establish an authenticated connection between the Direct Access Gateway and the Qlik Cloud tenant.
Okay.
And then finally, put that key into a text file. And this is the key that we're going to use to configure the Data Gateway in the QMC.
Okay. So, it wrote all that information to a text file?
Correct.
All right. So, you copied that?
Yes.
All right, we're back in the QMC. Okay. So, we've kind of configured everything on the On-prem side; and now we're back on the Cloud, and we're configuring the connection from the Cloud side?
Yep. Provide the key. Don't forget to select the Space that we just created for that Data Gateway.
Okay.
And then we can create it. It's a straightforward process. Now it shows it’s connecting; and it's just because we haven't started the service.
All right. So, when we installed the Data Gateway on the server, it set up a service?
Yeah. That’s correct. It's a service. We finally can see a where the Data Gateway here.
So, then we can start it.
Great.
And now we go back, and now it's connected.
Fantastic. So, now it's actually connected to the Gateway. Now that it's installed, configured, and connected; what's the next step?
Now we can create an app.
Okay.
And the Space that I created for testing.
Is this the Space with the Data Gateway?
No, the Data Gateway is tied to a different Space.
Does the actual app that users using, does that need to be in the same Space as the Data Gateway?
No. No, that's why we have the Data Gateway tied to a specific Space; because the same way that we handle Spaces for apps is kind of like the same thing for the Data Gateway. So, the role that you have will give you or not access to the Data Gateway.
Okay. So, you've got a new app; and what will you be connecting to?
I'm going to do a test to a SQL Server.
Okay, we have all these connections and we have this that said Direct Access gateways.
Okay. So, this is the list of all the data connectors that are available, but if you search for “Direct Access,” it'll sort it on just the ones for Direct Access Data Gateway?
That’s correct.
Awesome.
So, I'm going to use SQL Server. One of the first things that we're going to select here is going to be the Data Gateway that we're going to use.
Okay.
If this was disconnected, it's going to show indicating that is disconnected.
Like if we hadn't started it yet or something like that?
Yep. Okay, put a server here. I'm using a named instance, so I'm going to put zero my port; and then put my User and Password.
Now those are the user credentials for the SQL database?
That’s correct. SQL Server authentication.
Okay.
If we are okay with the naming…
And this is just the name of the connection from the app’s perspective?
Yeah. I'm just going to put that is a Qlik Connector that I'm using.
Okay.
And then, I can just do a preview of my data. Select a database; and then something to bring; then we insert it and then…
Great. Awesome. So, that was so quick and easy.
Yep.
You've securely loaded data from an On-prem SQL database to your Qlik Cloud tenant app. That's Awesome. What if we're trying to connect to an on-prem data source that doesn't already have a custom connector?
Yes. So, we now have the feature to use generic ODBC drivers. It requires version 1.52 of the Data Gateway or later. We only support 64bit connection connectors.
Okay, what do we need to set up on the server first to use an ODBC driver connection?
So, we need to install the driver. I already installed it here; but this was the driver that I got from Microsoft web page.
Okay.
And once that we have that, we can create a System DSN or directly through the UI. If you're going to use third-party driver, make sure that the connection is successful from the server.
Okay.
I create this one just to make sure that my connection works and that everything is fine here.
And again, we're setting up this DSN connection and you're testing that it will connect from this machine to the SQL database?
That's correct.
Okay, successful, perfect.
Yes, it was successful. It tells me that I am able to connect to my data source.
Okay. So, now it's just creating a connection from the tenant side, right?
Yep. Create a new connection.
Aas that also listed there as a Direct Access - oh there it is: ODBC.
So, it's also listed through the Direct Access Gateway.
Okay.
We need to select the Data Gateway that we want to use. If we select System DSN, it’s going to list whatever we have already configured.
Okay.
The driver, and it shows the driver. The difference is for this one, you'll have to put the connection string here; if you have this already configured, then to just provide User and Password.
All right; where do you enter the User and Password?
Put it under Encrypted Properties; although you can put it in the connection string, for security purposes is preferable to put it here.
Okay. And testing the connection?
It's successful; and then I'm going to create it.
Okay, see it there.
We just verified that it was a successful connection; however…
The preview is giving an error.
Yeah, this error is because we forgot to set up a section in the connection. Let me go ahead and close it. User and Password again, because it's going to require those. With third-party drivers, we need to fill out this Database Specific Options.
Okay.
Select Custom for this test. We're going to select Detect. And the only information that is not showing is the Statement Template.
And that's something specific for SQL?
It’s specific for the preview. This is the syntax that SQL Server usually uses.
Yeah, where did you find all that?
Yes. So, we do have some documentation under this section. Some of the drivers that we tested and the version.
Uh-huh.
If you scroll, you'll see the syntax.
Okay.
That is basically the one that we are configuring in our connection.
Okay. So, that's where you copied all that syntax from?
Yep.
Great.
So, let me go back. I'm going to test my connection again. Make sure everything is fine, and then save.
Okay. So, let's try it out now.
Didn't give me an error.
Awesome. So, the ODBC connection is basically connecting to the same database, just with a different connector?
Yes. It's a third-party driver that we installed on that server. Reload. The first part is load from the same database using a Qlik connector; the second part is loading information using the third-party driver.
Awesome. All right. Well, you've shown us two different types of connections through the Gateway; and how to get that set up. Everything's been moving smoothly, but if something does become disconnected or are there any issues; how can we troubleshoot that?
If you're looking at your task and you see that something is failing…
Yeah.
You're going to be checking first the Reload Analyzer.
So, this is the Reload Analyzer and if the admin had a Reload failed, and if the reload failed because of Data Gateway, how could they go about doing that?
Yes. So, we don't have a current way to know if it failed through the Data Gateway.
Okay.
There is a work around to identify what applications are using the Data Gateway.
All right.
Just keep in mind that some tasks are perhaps loading QVDs or other files and then having connections with the Data Gateway. So, the fact that it uses a connection on the Data Gateway doesn't necessarily mean that fail due to a Data Gateway issue.
Right.
For example: if we see this one “Direct Access,” potentially that’s a Gateway issue, but to identify what applications are using a connection that goes through the Data Gateway, there is a filter in this app.
Okay.
This one.
Awesome. So, that's actually a field for selecting connections that use Direct Access?
Yep. I have here two apps.
Okay, can you walk us through a scenario if an admin is trying to troubleshoot what happened with a Reload Fail?
Sure. So, let me just copy this one. There was a reload that failed; and we have the date and the time. We also have the error: “Unable to connect to the Gateway.” I'm going to go back to my server.
Yeah. Where would you find the log files for that?
Go to C:, ProgramFiles, Qlik, ConnectorAgent, Data, and Logs.
Okay. So, I see a lot of logs there; how do you know which log file to go to?
What I have is the Reload ID. We have three logs that we usually look at: the ODBC Connector, Connector-Agent_logs, and the DirectAccessAgent. You might find something in each of these. We're going to just go through the process for this basic example.
Okay. So, you've opened up those log files in Notepad++ and are searching across all of them using that Reload ID?
Yep. I have the Reload ID; just going to go and try to find anything I can get for that Reload ID.
And what did we find?
So, we're going to have INFO, WARN and ERROR; and we also focus on those that look suspicious. If we go all the way to the ODBC log, we don't see much, but we have a WARN here in the Connect Agent; and the error that shows me here it says “session not found for reload.”
Okay.
It also tells me that this happened on the 12th at 12:43.
Okay. So, that was kind of vague, but at least we have a time exactly when the - something happened - there was a warning.
Yep. If I go now to my Direct Access Agent log, there was a connection issue here.
Okay.
It was 12:43.
That same time.
Yep. Now I can go to my Windows logs and see what happened that day at 12:43. It says that my Data Gateway was stopped. That is correct. I actually stopped the service to generate this error; and then of course it failed.
Well, that's very cool. I like how you identified the Reload ID; searched through the logs to find relevant messages about that reload; the time that it happened; and using the time, you found what caused the issue. In this case, it was a problem you created yourself to give us something to troubleshoot, but that's so cool. Now we've been investigating a specific error using the Reload Analyzer and the log files. Is there anything that can be done to notify the admin things that fail like this?
We do have an option in the QMC.
Okay.
To my Data Gateway section, and then I have this Notification option. So, you can get an alert here; or through the Qlik Sense mobile; or to your email. If you want to get email notifications when the Data Gateway state has changed, meaning that it got connected or disconnected; to enable this one. To get notifications that the Data Gateway version requires an update, then you can select this one.
What would that email look like?
If you get a disconnection, you'll get an email like this it just tells you which Data Gateway got disconnected.
And it's even got a link there. Thanks for showing us that. Well, now it's time for Q&A. Please submit your questions using the Q&A tool on the left hand side of your On24 console. I see some questions have already come in. So, I'll just start from the top. All right Nadia, first question: can you Help me understand why the regional date and value fields are being incorrectly read as text when using the DBC connector? That sounds very specific. Do you have any ideas about that?
I think that what we're trying to say is that the field in the data source is a Date, but it is transformed somehow to a text or when it's loaded are getting Text.
Yeah, that's how I would interpret that, yeah.
Yeah, but - so we need more details about that. We need to get the information about the Connector that is in use; and I mean, all those details about how does the field look in the data source side. I suggest to go over our article to see the necessary information for a Data Gateway.
Oh, okay. So, what is this article?
We have it in Community. Doesn't have specifics for connectors; but I'll say since it's probably an app, we need to have at least a script that show us what are you trying to bring; also a specific of the connector that's been used. So that way, we can have like at least a baseline, and say well: what's the data field in the data source side? What connector is been used? What is script? And then, we can look at that one. Now if it's our connector, we probably going to try to reproduce. If it's a third party connector, then at that point there might not be much that we can do; because third party connectors are out of the scope of support.
Sure. So, this would be if somebody needed to investigate further and open up a Qlik Support case. This is outlining the detailed information we would need to actually troubleshoot that for them?
Correct.
Fantastic. Nice. I'll definitely include the link to this along with the recording. I guess we can move on to the next question: what is the difference between Qlik Data Gateway, Qlik Data Movement, and Qlik Replicate? Oh yeah, Data Movement, we saw as one of the options when we were looking at the Gateway. I know that's not - that's a little outside of the scope, but it was mentioned. So, do you have any insight on that or?
Yes. So, Data Gateway is a product for the Qlik Data Analytics. And Data Movement and Replicate is for Qlik Data Integration. So, both products have different capabilities. I think that as general pointers that R&D has provided is that: if you're accessing on-prem database and your developers want to have ad-hoc querries then the Data Gateway might be a good option. For the Data Movement, there are other features that are more about moving, transforming data, and detect changes. So, for that one, if you have an specific scenario where you want to explore both products, we suggest to contact your Customer Success Manager or your Customer Success Engineer to have a further discussion about that, on which scenarios will be best for Data Gateway and which ones for Data Movement.
Okay, great, thank you. Moving on. Next question: we have set up our spaces based on user access needs. If a connection is set up in a specific Space, is that data available to users in that Space only?
Users can list and consume connections from a Space if they have Can Consume Data access. So, they should be able to list those connections from a different Space if they have the correct rights.
Okay. So, it's really just having the Data Gateway in a specific Space is really just to have user control over it, or that's one of the reasons anyway?
Yep. Yeah. It's going to be the Space where the Data Gateway resides; also the Space where the connection resides, so…
Right.
Spaces, we also still go through the privilege to each of them.
Okay, great. Next question: which connector should be used to connect to SAP Hana via Direct Access?
For that one, there was one connector that was tested as a part of the ODBC connector capability. It’ss listed in our Qlik Help Site on the same section where we have the sample connection strings and syntax. We have here –
Oh, SAP Hana.
That we tested. So, that's something that we can try out for that specific purpose.
Okay. So, is this the third-party connector, right?
Yes. So, we can try to create a connection.
Sure.
We put SAP; this is what we have available, and these are not through the Direct Access Gateway.
I see.
So, therefore the one that was tested is the one that we have listed in our Help site.
Okay, is that using the ODBC connector?
That’s correct. An ODBC connector, yep. And this is the version that was tested.
Awesome. Well, that's great to know, and to be able to see where to find those others that were listed and tested?
Yes. So, we have only the ones that were tested here; and we have the configurations that they need predefined. If we're going to use a connector that is not here, we'll have to work through getting this configurations to be able to use a connector.
Okay, great. Okay, next question: is there any white listing necessary when setting this up?
Yes. So, it's also listed in the system requirements for the Data Gateway. So, it'll be to White list the Tenant and the Tenant alias.
Ah.
For connectivity and we have, we think that this should be next to this one. This is what our documentation outlines.
Okay, great. It's nice that all these details are actually documented clearly there. All right; next question (and this one sounds kind of specific): we're using the ODBC driver connection and getting the connection error “Invalid argument…bad address,” any tips on troubleshooting this?
This is going to be also one that we're going to need more details about: where are we getting this error? Is it testing connection? Were we previewing data? When we are loading? And also what driver is being used? So, if we can get those details, we might be able to provide more help. So, best option: go through the support portal and provide the necessary information so we can help to investigate that one.
Okay, yeah that sounded very specific. So, I guess the best option is to create a support case with the necessary details to help us troubleshoot that. All right, next question: is the use of Direct Access an add-on feature that requires any extra license?
No, it doesn't require a license.
Great. Just as long as you have a Qlik Cloud tenant, then that's enough?
That's correct.
Okay, are there any best practices for using Data Gateway concerning performance?
R&D is working on outlining this. I think that soon we're going to see some information that is going to be added to the Help site.
Okay.
For now, when we talk about performance, if you're seeing your Data Gateway actually struggling a lot with resources; definitely consider to increase resources. Also, follow the best practices that we mentioned before; like: put the Data Gateway close to the data source to avoid latency; verify that you have a good connection to your tenant; and perhaps consider also how are you going to set that up. Perhaps you have a lot of Pre-Loads, and then you have critical apps that that you want to be more stable than the rest. So, perhaps for those, have a separate Data Gateway. Again, those are roughly some of the ones that I have heard. There are some details and more documentation that is going to become available soon.
All right, great. It's nice to know that R&D is doing some more testing in that regard.
Okay, next question: in your experience does the ODBC connection perform any better or worse than a custom connector, like the SQL connector you demonstrated?
I'm not aware that of any performance testing that was done during the ODBC generic driver new feature. If you want to try that out and see how/where connectors performs compared with one third-party; that'll be the way to go. Also make sure some connectors have any specific configurations for fast performance. So, if you are aware that you're using a third-party connector, and it has to have specific configurations; make sure that those were enabled.
Great, and last question we have time for today: are there any issues with setting up multiple connections in different Spaces to the same database/Data Gateway?
As far as the Data Gateway handling all the connections, there is no concerns. Things should work fine. Whether we want to have multiple Connections in separate places, that's something different. But at least of capabilities of the Data Gateway it should work fine.
Great. All right Nadia, this has been super informative and I'm sure very helpful for anybody that's getting started using Direct Access. Thank you so much for all the work you put into preparing this demo for us.
Thank you everybody for attending. If you have further questions, feel free to post them in Qlik Community. We'll go through them and provide an answer as soon as we can. Thank you.
Great! Thank you everyone. We hope you enjoyed this session. And special thanks to Nadia for presenting. We always appreciate getting experts like Nadia to share with us. Here's our legal disclaimer. And thank you once again. Have a great rest of your day.