This session addresses:
-Installtion tips
-Using SharePoint Connector
-Difference with SaaS Connectors
-Troubleshooting
00:00 – Intro
01:04 - What are Qlik Web Connectors
03:30 - Install Web Connectors as a Service
04:22 - Important Configuration Settings
07:18 - Setting up SharePoint connection
08:35 - Retrieving Lists
11:17 - Creating new connection in Qlik Sense
12:29 - Loading an empty list
13:24 - Loading a file
14:12 - Troubleshooting failed reloads
16:00 - Log Files
16:31 - Web Connectors in SaaS
17:14 - Basic Troubleshooting steps
17:45 - Links to more information
18:19 - Q&A
Environment
- Qlik Web Connectors December 2020
Related Content
Advanced configuration of Qlik Web Connectors
Qlik Web Connectors Documentation
Qlik Web Connector articles
Web Connector download site
Q&A:
Q: When getting permission for our Sharepoint tenant site - you mention you allready got it approved. Was this done inside of Sharepoint itself or your Office 365 Azure AD portal ?, it seems other connector seem to suggest needing to do this to approve and allow third party apps to send requests. Is this correct?
A: There is nothing to be done from the Azure portal. If it's the first time using the connector, it's mandatory to use the Azure tenant admin when authenticating. This will prompt and authorization approval for Qlik's sharepoint app (the reason why appears as a third party app it's because is not developed by microsoft).
Q: What is the purpose of the "Access Token"?
A: The access token will grant access to connector
Q: Is there a connector to Azure?
A: Yes, both in SaaS and on-premise (december 2020)
Q: When you say 'Azure Tenant,' is that the same as Auzre AD admin portal to approve the app?
A: Yes
Q: We are using a proxy to connect to outside enterprise connections (Sharepoint Online).But it's possible to activate proxy only for some connections types ?
A: Unfortunately not, that's a current limitation, I would suggest creating a feature request in Qlik Community's Ideation.
Q: Are there any qlik imposed limits on the amount of data that this connection can load?
A: Qlik does not impose limits on the amount of data that can be loaded. API restrictions and data load restrictions or bottlenecks may occur based on the connector and data source. Example, see Working with the SharePoint REST API quota limits.
Transcript:
Hello everyone and welcome to the March edition of Support Techspert Thursdays. I’m Troy Raney and I’ll be your host for today's session. Today's presentation is Best Practices for Using Qlik Web Connectors with Francisco Fernandez. Francisco, why don't you tell us a little bit about yourself?
Hello, my name is Francisco and I’m a Technical Support Engineer here at Qlik. I’ve been working at Qlik since 2019 and my main focus are Qlik Web Connectors and Qlik Sense integrations, such as APIs and mashups.
Okay, and what are we going to be going through today?
We are going to go through installation tips; how to use SharePoint Connector; difference between SaaS and on-premise connectors; and a little bit of troubleshooting.
Okay, and for those of us who are kind of new to Qlik Web Connectors, what are Qlik Web Connectors and how do they work?
The Qlik Web Connectors package is a lightweight web service that helps you fetch data from many web-based data sources, and also social media. It contains 42 different connectors with tons of functionalities. Also, it can be used in a separate server, so it's a completely isolated package that doesn't depend on Qlik Sense or QlikView; and that's the main reason it can be used with both.
So Qlik Web Connectors works with both Qlik Sense and QlikView?
Yes. Here in the example architecture. You can see that the Qlik Web Connectors is being run in a different server. That's why it doesn't have a dependency.
Okay, that's cool. Now why should someone start using these connectors? How are they useful?
So, first of all, because it's free of charge with a Qlik Sense license.
That’s always nice.
It's free. It's simple to install and configure. Auto-generate the scripts. It can be used as a Windows service or run as an application. As I previously mentioned, it can be used both with Qlik Sense and QlikView.
Okay, I think the initial question for a lot of people is: how to install it and how to get started?
Yep, you can download the Web Connector from our Support page. As a demo, I already downloaded the package. This is what you get from the download page. I already uncompressed it, so we are ready to go.
Okay, and what version of Web Connectors is this? Is this the latest version?
Yeah, this is the latest version. This is December 2020.
Francisco, what are we looking at here?
We are looking at the connector. It's basically a library of DLLs, as you can see here. Each DLL is a connector. And we also have some folders that contains the connector configuration. Scroll down. We can see the deploy.config file, which contains all the connector configuration. Scroll further, we can see here: application executable and the application service executable.
Yeah, you mentioned there's two different ways of running connectors: one as a service or as an executable application.
Yeah, if we want to use it as an application; we only need to double Qlik this window will pop up and we are ready to go. The connector is already working.
Okay.
If we want to run it as a service, we have to go here to batch files. And we have two batch files here. The first one contains the installer service, and the second one contains the uninstall service.
Can I ask you, what are the advantages of running it as a service versus just clicking on the application?
Running the Web Connectors as a service is especially useful for server admins. It allows them to automate tasks, such as restart all the services at once using a script.
Okay, can you show us the details of running the service installer?
Yeah, if we open it using the Notepad++, we can see here that it contains a path. This path should be where our Web Connector is located. In our case, it is in the desktop. So, we should change the path here. Save it and install after that. And it will be available in the services tab. If we want to start using the connector, we have to check the configuration in the deploy.config file. Notepad++ and edit it. Here we have an XML configuration file which contains lots of tags, as you can see.
Yes, these are kind of difficult to read with the human eye sometimes if you're not used to editing XML files. What settings here are important that we should take a look at?
First of all, the port where we want to run the connector. This is by default: 5555. You can change it to whatever port you want to use. Secondly, one of the most important tags is: the Allow Remote Access, which basically allows access to the Web Connector from remote machines.
Okay, so if like I have Qlik Sense running on a totally different machine, this is the major setting that allows that connection?
Yeah. Otherwise, you will see an error message saying that you have to enable it.
Okay.
We have to change it to True. After that, we should look at the File System Allow Path. This tag is quite important, especially for those connectors who download files, because the connector needs access to the file system. You can either allow full access to any path by using the wildcard (*) or you can specify which paths you want to use for the downloads. You have to use the semi-colon (;) to enumerate more than one path.
I see that's a little confusing having the examples right above the setting which is initially by default: blank.
Yeah, usually people tend to mistake this with actual configuration parameters, but the parameter starts after the arrow (->). This is basically not being used by the connector.
Okay.
Lastly, we have Default Allow IP Addresses. This is also very important if you are using the Web Connector in a multi-node environment because it will allow all the nodes access to the Web Connector. You have two options: use any to allow all the nodes including the new ones if you want to add more nodes. Or you have the other option is to specify the IP Addresses of those nodes. Usually, I tend to prefer to use any. We have some other advanced features.
Yeah, so I see a lot of settings there about security and SSL. And if someone wanted to take advantage of all those security settings, would they go to the Help page to find all that information?
Yeah, they can find it in the Help page in the section called Advanced Qlik Web Connectors Configuration.
How do we access the console here? Could you show us that again?
Now, we are going to run the Web Connector as an application for demonstration purposes. The land page is this one. In case that you are running it as a service, it's the same page. This is the main page. Here you can find all the connectors available.
That's a long list. Would you mind walking us through the steps to set up a connection?
Yeah, we can start by using SharePoint which is here. First of all, what we have to do is authenticate to our SharePoint site using the Can Authenticate table and pressing Parameters.
Okay.
We can see that we have here the base URL. In this case, we are using Qlik’s SharePoint site. In your case, you will have to use your company SharePoint site. We also have Prompt and Consent level. If you were already using the SharePoint connector, you will have to use None. But if this is the first time that you're using the connector, you should use Consent and using an Azure tenant admin, consent the application. Since we are already allowed access to the application, we are going to use None and continue with the authentication process, which generates this authentication code. Copy to clipboard. Go back to Authentication Code here and paste it and then save. We can run to see if we are authenticated. The Web Connector is allowed to retrieve data.
Okay so we've authenticated ourselves from the connector to our company SharePoint site?
Yes.
Okay, what's next?
Now we can start by trying to retrieve a list. I already set up a list for the demonstration. It's in here.
Okay, so this is our SharePoint site, and you've got a list there I see?
Yeah. Here in the URL, we can see that we have all this. So, the first part is the site Sub Path.
Okay.
So here is our site and site Sub Path. So, it usually is always Sites followed by the name of the group that we want to use. After that, we usually find what's the table that we need to use. In our case, since we want to retrieve a list as you can see, we should use the table ‘List.’ But if we want to retrieve document let's say, you we will have to go to the Documents as you can see here. I already put the Site and the Subsite path. And using list. We can run the table, and we can see all the lists that are available.
Okay so, this is a preview of everything it reads from that Subsite?
Yeah, it's reading metadata. So, it's not only reading our list, but everything that gets pulled by the connector.
Okay.
We already know the name of the list that we want to retrieve. And this is the identifier that we have to use.
Okay, do you need to copy that?
Yeah, we are going to copy this. If you want to retrieve data from a list, it's very important to use one of these tables, because lists are not files; are actually metadata. If you want to use the Get File table, it will fail. So, for example, as you can see: everything is correctly set up and we are going to paste. Bear in mind that this might contain spaces, so you have to delete them, and run the table. And as you can see, we have a Not Found. This might be also a 403 - Not Authorized.
That didn't work because the command was Get File, but you actually pasted the ID of a List, so it doesn't match?
Yeah.
Okay.
Instead, we are going to use the correct table, which is: List Items from ID. So, the Connector stores what was your last search, so basically that's why we didn't have to paste it again. If we run the table again, we can see that we are able to retrieve the list.
Okay, so it previews the actual list right there.
And if we go to the list, we can see that the information matches.
Okay, how do we get this data in Qlik Sense?
Here, we can see that we have all the instructions to create a connection in Qlik Sense.
Francisco, would you demonstrate for us creating this connection now that you've prepared everything in Web Connectors?
Just following the instructions, we have to copy the URL, and we have to create a web file connection in Qlik Sense. So, we go to the Data Load Editor. Create New Connection. Scroll down until we see Web File. And we have to paste the URL that we copied, and username.
Any name, right?
Any name and create the connection. Also, here in instructions it's mentioned that you have to create a variable with a connection string that you are using. I already done that before, so everything is correctly set up now.
Great.
And we only have to copy the script to the clipboard and paste it and make a reload. As you can see, the reload was successful and we are able to retrieve the list.
Francisco, that all seemed to go very smoothly. Just following the instructions, copying, and pasting from Qlik Connectors to Qlik Sense; what should you do when things go wrong?
I prepared an example actually.
Great.
We're going to do the same; but in this case, I purposely made an empty list with no data in it. As you can see here, we are going to make the same procedure as we did before.
Okay.
We are going to look for Empty List. We are going to copy the ID and follow the same procedure as we did before. Delete this, paste. As we mentioned before, we have some spaces. We have to delete them and run the table. The connector is basically saying that there is no data to pull. So usually when this happens, we have to verify in SharePoint that there is actually data in what we are trying to retrieve. There is no data in here, so the connector cannot achieve anything.
How would it be different, Francisco if you're trying to load a file from SharePoint instead of a list?
So, we have here this folder that contains this file. We have to go to List Files. And since we already know the folder: Save Inputs and run the table. We can see here that we have our Excel file and we just need to copy the unique ID. Go back. We have two options: either the Get File or Download File.
Okay.
We're going to use Get File. Paste the unique ID. Run the table. As you can see, we are able to generate a string.
Okay.
In case that we want to use it in Qlik Sense, we just need a new connection using this URL.
Would that be a web file connection?
Yeah, it's a web file connection in this case.
What happens if admins encounter an error while trying to set up that connection?
One of the common problems that we are seeing from Support is that sometimes Qlik Sense is failing to reload the data with a 500 error.
Okay.
In order to troubleshoot that problem, we usually go to the localhost. In this case, we already are here. Try to open the URL. So, as you can see, we are able to download the data. If we open this, we will see the Excel file and all the data that contains. So just copying this URL and going to the node that is failing, pasting in the browser should be the same result as the in the localhost.
That data file that you downloaded, is that the actual XLS file?
Yeah, it doesn't have an extension because it's ready to be used with Qlik Sense.
Okay, that's a great step to test the access to the data. What troubleshooting steps would you take if it doesn't have access?
What we need to do is: go to the home page. Go to Settings. Double check if the allowed IP addresses matches with the deploy.config file. In here, we have ‘Any,’ and also, we have ‘Any’ here. It's not likely that we are going to face this problem, but in case that you have ‘Any’ here and ‘localhost’ here, you will have to change it and then press Save. If you change any parameter here and press Save, there is no need to restart services.
Okay, but it's very important that the settings in the main page here need to match what's in the deploy.config file?
Yeah. Also, if you're using more than one administrator, all the administrators have to have the same allowed IP addresses.
Okay, so that's a very important setting there.
Yep.
And where would you find the log files if you needed to investigate further?
Go into the home page. Here in the Log Files tab, you can see all the logs. If we open one, for example this one. We can see that it's an XML file. So, for example, when we tried to retrieve the Empty List, we receive ‘No Rows Return’ error message. We can look for the exact same error here in the logs. We can see the error message.
What’s the difference between Qlik Web Connectors for Qlik Sense on Windows versus Web Connectors for Qlik Sense Enterprise SaaS?
The Qlik Web Connectors is standalone. It’s a package that you have to install in the server. As a kindly reminder: always use the latest version. It contains 42 connectors. On the other hand, the Qlik Web Connectors for SaaS it's integrated in Qlik Sense. It's always using the latest version. It contains 33 connectors. It's still growing. Soon it will be available the SharePoint File Connector, so I’m sure that many customers will be happy knowing this.
Yeah definitely. So just to clarify, that whole installation process, configuration that we saw; that's not really necessary in SaaS because it's already built in?
Yeah. Qlik is taking care of that.
To review, what are some basic troubleshooting steps when things go wrong?
First of all, always double check in if you are using the latest version. Then you can check the logs for any error messages.
Right.
And lastly, you can check the API limitations of the connector. You can check that in the About button under the Useful Links page.
Okay, because basically the Web Connectors are using the APIs of each individual web source and like Google or Facebook their APIs; and they may change?
Yeah, that's correct.
Where are some resources where admins can find more information?
They can find more information in the help page, and also in the community articles that Support creates.
And again, where can we find the Web Connector download that we started with?
You can go to Qlik Support download site. Look for Qlik Connectors and under the Qlik Connector site, just go to the latest version available.
Okay, now it's time for Q and A. Please submit your questions through the Q&A panel on the left side of your on24 console. Francisco, which question would you like to address first?
So, first we have: do we need to upgrade to the latest version of Qlik Sense to use them?
No, but we strongly suggest to upgrade to the latest version of Qlik Web Connectors.
Moving on to the second one, are all the connectors free along with the Qlik Sense license? Some of the connectors on our site show as premium.
Ah yeah, if you are using a previous version, let say from March 2020 or maybe May 2019 it will appear that they are still premium, but if you upgrade to December 2020 you will see that all appear as free. Also, it’s not only Qlik Sense. If you have a QlikView license and you use the December 2020 version, you will see that the connectors are also free.
That’s great.
Uh, we’ve had trouble connecting to the SharePoint site. What administrative privileges need to be granted to what user?
Probably this question might be related to the when you first start using the SharePoint connector, you have to use an Azure tenant admin to authenticate to the connector; so an application will be created in the tenant site. This might solve the problem that you’re facing.
And we have another question. Does the account running Qlik Sense need to be an administrator on the SharePoint site?
Uh, no, there is no need.
Uh, are all the 42 connectors free to use including SharePoint 365 connector?
Well, I think I already answered this one, yeah. All of them are free to use.
I see some questions that we already answered regarding the license. If you are still seeing the connector is not free to use, you basically need to upgrade to the latest version of the connector.
Does the version of Sense or QlikView matter?
Uh, no. They are not related. As we mentioned during the presentation, the connector is a separate package. It is isolated.
For example, this one is a good one. In a multi-node setup, do I need to install of each node or only on the central node?
You can install the Web Connector only on one node. There is no need to install the Web Connector on each node. This is not like the ODBC package. And you can even install the Web Connector on a different server, as long as it has connectivity to the – to the Qlik Sense Server.
What is the difference between QS Standard and Legacy mode when obtaining the load script?
This is a change regarding to Qlik Sense actually. It’s not related to the Web Connector. The Legacy Mode has some differences when loading the connection strings. You can go to the QMC, after you go to Engines and you can enable or disable the Legacy mode.
We are using a Proxy to connect to object enterprise connections, uh like SharePoint, but it’s possible to activate the Proxy only for some connection types. Example: ActivateProxy for SharePoint online.
Unfortunately, this is a limitation that the connector has. You can only setup one proxy and you cannot select which connectors are using that proxy.
Uh, can Web Connectors be used with Qlik Sense Desktop?
Yeah, they can used in Qlik Sense Desktop. And you can also try them in your Desktop if you want to.
Uh, how can we fetch multiple files from a folder in a SharePoint site?
So unfortunately, this is a current limitation that the connector has. I strongly suggest creating a feature request in the community to raise awareness within the product management.
I saw there’s a couple questions asking about the difference between the REST connector and Web Connectors. You want to address that?
Yeah. Basically, the REST Connector has the same capabilities as the SharePoint connector for example when retrieving data. But the thing is that using the SharePoint Connector is much easier than using the REST connector because all the authentication part is handled by the – by the SharePoint Connector, so it is much easier to use.
A question just came in. Are Web Connectors free for QlikView users as well?
Yes, as long as you are using the latest version.
Is there a connector to Azure?
Yes, we have once connector to Azure Block Storage.
Okay Francisco, we’ve got time for one last question.
Those web connectors have limitation on large volume of data?
Not specifically, since each connector uses a different API. There might exist API limitations, but not the connector itself.
Well thank you very much Francisco.
You're welcome and thanks for everyone for listening. I hope this has helped many people and good luck with your web connections. Feel free to post any questions you might have in the Qlik community. I’m there quite often, and I have written several of the articles on Web Connectors.
Okay great! Thank you everyone! We hope you enjoyed this session. Thank you to Francisco for presenting. We appreciate getting experts like Francisco to share with us. Here is our legal disclaimer. Thank you once again and have a great rest of your day.
