Search or browse our knowledge base to find answers to your questions ranging from account questions to troubleshooting error messages. The content is curated and updated by our global Support team
Featured Content
-
Optimizing Performance for Qlik Sense Enterprise
This Techspert Talks session addresses: Understanding Back-end Infrastructure Measure Monitor Troubleshoot Environment Qlik Sense Enterprise on Wi... Show MoreThis Techspert Talks session addresses:
- Understanding Back-end Infrastructure
- Measure
- Monitor
- Troubleshoot
Environment
- Qlik Sense Enterprise on Windows
00:00 - Intro
01:22 - Multi-Node Architecture Overview
04:10 - Common Performance Bottlenecks
05:38 - Using iPerf to measure connectivity
09:58 - Performance Monitor Article
10:30 - Setting up Performance Monitor
12:17 - Using Relog to visualize Performance
13:33 - Quick look at Grafana
14:45 - Qlik Scalability Tools
15:23 - Setting up a new scenario
18:26 - Look QSST Analyzer App
19:21 - Optimizing the Repository Service
21:38 - Adjusting the Page File
22:08 - The Sense Admin Playbook
23:10 - Optimizing PostgreSQL
24:29 - Log File Analyzer
27:06 - Summary
27:40 - Q&A: How to evaluate an application?
28:30 - Q&A: How to fix engine performance?
29:25 - Q&A: What about PostgreSQL 9.6 EOL?
30:07 - Q&A: Troubleshooting performance on Azure
31:22 - Q&A: Which nodes consume the most resources?
31:57 - Q&A: How to avoid working set breaches on engine nodes?
34:03 - Q&A: What do QRS log messages mean?
35:45 - Q&A: What about QlikView performance?
36:22 - Closing
Resources:
LogAnalysis App: The Qlik Sense app for troubleshooting Qlik Sense Enterprise on Windows logs
Qlik Help – Deployment examples
Using Windows Performance Monitor
PostgreSQL Fine Tuning starting point
Qlik Sense Shared Storage – Options and Requirements
Qlik Help – Performance and Scalability
Q&A:
Q: Recently I'm facing Qlik Sense proxy servers RAM overload, although there are 4 nodes and each node it is 16 CPUs and 256G. We have done App optimazation, like delete duplicate app, remove old data, remove unused field...but RAM status still not good, what is next to fix the performace issue? Apply more nodes?
A: Depends on what you mean by “RAM status still not good”. Qlik Data Analytics software will allocate and use memory within the limits established and does not release this memory unless the Low Memory Limit has been reached and cache needs cleaning. If RAM consumption remains high but no other effects, your system is working as expected.
Q: Similar to other database, do you think we need to perform finetuning, cleaning up bad records within PostgresQL , e.g.: once per year?
A: Periodic cleanup, especially in a rapidly changing environment, is certainly recommended. A good starting point: set your Deleted Entity Log table cleanup settings to appropriate values, and avoid clean-up tasks kicking in before user morning rampup.
Q: Does QliKView Server perform similarly to Qlik Sense?
A: It uses the same QIX Engine for data processing. There may be performance differences to the extent that QVW Documents and QVF Apps are completely different concepts.
Q: Is there a simple way (better than restarting QS services)to clean the cache, because chache around 90 % slows down QS?
A: It’s not quite as simple. Qlik Data Analytics software (and by extent, your users) benefits from keeping data cached as long as possible. This way, users consume pre-calculated results from memory instead of computing the same results over and over. Active cache clearing is detrimental to performance. High RAM usage is entirely normal, based Memory Limits defined in QMC. You should not expect Qlik Sense (or QlikView) to manage memory like regular software. If work stops, this does not mean memory consumption will go down, we expect to receive and serve more requests so we keep as much cached as possible. Long winded, but I hope this sets better expectations when considering “bad performance” without the full technical context.
Q: How do we know when CPU hits 100% what the culprit is, for example too many concurrent user loading apps/datasets or mutliple apps qvds reloading? can we see that anywhere?
A: We will provide links to the Log Analysis app I demoed during the webinar, this is a great place to start. Set Repository Performance logs to DEBUG for the QRS performance part, start analysing service resource usage trends and get to know your user patterns.
Q: Can there be repository connectivity issues with too many nodes?
A: You can only grow an environment so far before hitting physical limits to communication. As a best practice, with every new node added, a review of QRS Connection Pools and DB connectivity should be reviewed and increased where necessary. The most usual problem here is: you have added more nodes than connections are allowed to DB or Repository Services. This will almost guarantee communication issues.
Q: Does qlik scalability tools measure browser rendering time as well or just works on API layer?
A: Excellent question, it only evaluates at the API call/response level. For results that include browser-side rendering, other tools are required (LoadRunner, complex to set up, expert help needed).
Transcript:
Hello everyone and welcome to the November edition of Techspert Talks. I’m Troy Raney and I’ll be your host for today's session. Today's presentation is Optimizing Performance for Qlik Sense Enterprise with Mario Petre. Mario why don't you tell us a little bit about yourself?
Hi everyone; good to be here with everybody once again. My name is Mario Petre. I’m a Principal Technical Engineer in the Signature Support Team. I’ve been with Qlik over six years now and since the beginning, I’ve focused on Qlik Sense Enterprise backend services, architecture and performance from the very inception of the product. So, there's a lot of historical knowledge that I want to share with you and hopefully it's an interesting springboard to talk about performance.
Great! Today we're going to be talking about how a Qlik Sense site looks from an architectural perspective; what are things that should be measured when talking about performance; what to monitor after going live; how to troubleshoot and we'll certainly highlight plenty of resources and where to find more details at the end of the session. So Mario, we're talking about performance for Qlik Sense Enterprise on Windows; but ultimately, it's software on a machine.
That's right.
So, first we need to understand what Qlik Sense services are and what type of resources they use. Can you show us an overview from what a multi-node deployment looks like?
Sure. We can take a look at how a large Enterprise environment should be set up.
And I see all the services have been split out onto different nodes. Would you run through the acronyms quickly for us?
Yep. On a consumer node this is where your users come into the Hub. They will come in via the Qlik Proxy Service and consume applications via the Qlik Engine Service, that ultimately connects to the central node and everything else via the Qlik Repository Service.
Okay.
The green box is your front-end services. This is what end users tap into to consume data, but what facilitates that in the background is always the Repository Service.
And what's the difference between the consumer nodes on the top and the bottom?
These two nodes have a Proxy Service that balances against their own engines as well as other engines; while the consumer nodes at the bottom are only there for crunching data.
Okay.
And then we can take a look at the backend side of things. Resources are used to the extent that you're doing reloads, you will have an engine there as well as the primary role for the central node, active and failover which is: the Repository Service to coordinate communication between all the rest of the services. You can also have a separate node for development work. And ultimately we also expect the size of an environment to have a dedicated storage solution and a dedicated central Repository Database host either locally managed or in one of the cloud providers like AWS RDS for example.
Between the front-end and back-end services where's the majority of resource consumption, and what resources do they consume?
Most of the resource allocation here is going to go to the Engine Service; and that will consume CPU and RAM to the extent that it's allocated to the machine. And that is done at the QMC level where you set your Working Set Limits. But in the case of the top nodes, the Proxy Service also has a compute cost as it is managing session connectivity between the end user's browser and the Engine Service on that particular server. And the Repository Service is constantly checking the authorization and permissions. So, ultimately front-end servers make use of both front-end and back-end resources. But you also need to think about connectivity. There is the data streaming from storage to the node where it will be consumed and then loading from that into memory. And these are three different groups of resources: you have compute; you have memory, and you have network connectivity. And all three have to be well suited for the task for this environment to work well.
And we're talking about speed and performance like, how fast is a fast network? How can we even measure that?
So, we would start for any Enterprise environment, we would start at a 10 Gb network speed and ultimately, we expect response time of 4 MS between any node and the storage back end.
Okay. So, what are some common bottlenecks and issues that might arise?
All right. So, let's take a look at some at some examples. The Repository Service failing to communicate with rim nodes, with local services. I would immediately try to verify that the Repository Service connection pool and network connectivity is stable and connect. Let's say apps load very very slow for the first time. This is where network speed really comes into play. Another example: the QMC or the Hub takes a very very long time to load. And for that, we would have to look into the communication between the Repository Service and the Database, because that's where we store all of this metadata that we will try to calculate your permissions based on.
And could that also be related to the rules that people have set up and the number of users accessing?
Absolutely. You can hurt user experience by writing complex rules.
What about lag in the app itself?
This is now being consumed by the Engine Service on the consumer node. So, I would immediately try to evaluate resource consumption on that node, primarily CPU. Another great example for is high Page File usage. We prefer memory for working with applications. So, as soon as we try to cache and pull those results again from disk, performance we'll be suffering. And ultimately, the direct connectivity. How good and stable is the network between the end users machine and the Qlik Sense infrastructure? The symptom will be on the end user side, but the root cause is almost always (I mean 99.9% of the time) will be down to some effect in the environment.
So, to get an understanding of how well the machine works and establish that baseline, what can we use?
One simple way to measure this (CPU, RAM, disk network) is this neat little tool called iPerf.
Okay. And what are we looking at here?
This is my central node.
Okay. And iPerf will measure what exactly?
How fast data transfer is between this central node and a client machine or another server.
And where can people find iPerf?
Great question. iPerf.fr
And it's a free utility, right?
Absolutely.
So, I see you've already got it downloaded there.
Right. You will have to download this package, both on the server and the client machine that you want to test between. We'll run this “As Admin.” We call out the command; we specify that we want it to start in “server mode.” This will be listening for connection attempts.
Okay.
We can define the port. I will use the default one. Those ports can be found in Qlik Help.
Okay.
The format for the output in megabyte; and the interval for refresh 5 seconds is perfectly fine. And then, we want as much output as possible.
Okay.
First, we need to run this. There we go. It started listening. Now, I’m going to switch to my client machine.
So, iPerf is now listening on the server machine and you're moving over to the client machine to run iPerf from there?
Right. Now, we've opened a PowerShell window into iPerf on the client machine. Then we call the iPerf command. This time, we're going to tell it to launch in “Client Mode.” We need to specify an IP address for it to connect to.
And that's the IP address of the server machine?
Right. Again, the port; the format so that every output is exactly the same. And here, we want to update every second.
Okay.
And this is a super cool option: if we use the bytes flag, we can specify the size of the data payload. I’m going to go with a 1 Gb file (1024 Mb). You can also define parallel connections. I want 5 for now.
So, that's like 5 different users or parallel streams of activity of 1 Gb each between the server machine and this client machine?
Right. So, we actually want to measure how fast can we acquire data from the Qlik Sense server onto this client machine. We need to reverse the test. So, we can just run this now and see how fast it performs.
Okay. And did the server machine react the same way?
You can see that it produced output on the listening screen. This is where we started. And then it received and it's displaying its own statistics. And if you want to automate this, so that you have a spot check of throughput capacity between these servers, we need to use the log file option. And then we give it a path. So, I’m gonna say call this “iperf_serverside…” And launch it. And now, no output is produced.
Okay.
So, we can switch back to the client machine.
Okay. So, you're performing the exact same test again, just storing everything in a log file.
The test finished.
Okay. So, that can help you compare between what's being sent to what's being received, and see?
Absolutely. You can definitely have results presented in a way that is easy to compare across machines and across time. And initial results gave us a throughput per file of around 43.6, 46, thereabouts megabytes per second.
So, what about for an end user who's experiencing issues? Can you use iPerf to test the connectivity from a user machine on a different network?
Yep. So, in in the background we will have our server; it's running and waiting for connections. And let's run this connection now from the client machine. We will make sure that the IP address is correct; default port; the output format in megabytes; we want it refreshed every second; and we are transferring 1 Gb; and 5 parallel streams in reverse order. Meaning: we are copying from the server to the client machine. And let's run it.
Just seeing those numbers, they seem to be smaller than what we're seeing from the other machine.
Right. Indeed. I have some stuff in between to force it to talk a little slower. But this is one quick way to identify a spotty connection. This is where a baseline becomes gold; being able to demonstrate that your platform is experiencing a problem. And to quantify and to specify what that problem is going to reduce the time that you spend on outages and make you more effective as an admin.
Okay. That was network. How can admins monitor all the other performance aspects of a deployment? What tools are available and what metrics should they be measuring?
Right. That's a great question. The very basic is just Performance Monitor from Windows.
Okay.
The great thing about that is that we provide templates that also include metrics from our services.
Can you walk us through how to set up the Performance Monitor using one of those templates?Sure thing. So, we're going to switch over first to the central node. So, the first thing that I want to do is create a folder where all of these logs will be stored.
Okay. So, that's a shared folder, good.
And this article is a great place to start. So, we can just download this attachment
So, now it's time to set up a Performance Monitor proper. We need to set up a new Data Collector Set.
Giving it a name.
And create from template. Browse for it, and finish.
Okay. So it’s got the template. That's our new one Qlik Sense Node Monitor, right?
Yep. You'll have multiple servers all writing to the same location. The first thing is to define the name of each individual collector; and you do that here. And you can also provide subdirectory for these connectors, and I suggest to have one per node name. I will call this Central Node.
Everything that comes from this node, yeah.
Correct. You can also select a schedule for when to start these. We have an article on how to make sure that Data Collectors are started when Windows starts. And then a stop condition.
Now, setting up monitors like this; could this actually impact performance negatively?
There is always an overhead to collecting and saving these metrics to a file. But the overhead is negligible.
Okay.
I am happy with how this is defined. Now, this static collector on one of the nodes is already set up. There is an option here that's called Data Manager. What's important here to define is to set a Minimum Free Disk. We could go with 10 Gb, for example; and you can also define a Resource Policy. The important bit is Minimum Free Disk. We want to Delete the Oldest (not the largest) in the Data Collector itself. We should change that directory and make sure that it points to our central location instead of locally; and we'll have to do this for every single node where we set this up.
Okay. So, that's that shared location?
Yep.
And you run the Data Collector there. And it creates a CSV file with all those performance counters. Cool.
So, here we have it now. If we just take a very quick look inside, we'll see a whole bunch of metrics. And if you want to visualize these really really quick, I can show you a quick tip that wasn't on the agenda but since we're here: on Windows, there is a built-in tool called Relog that is specifically designed for reformatting Performance Monitor counters. So, we can use Relog; we'll give it the name of this file; the format will be Binary; the output will be the same, but we'll rename it to BLG; and let's run it.
And now it created a copy in Binary format. Cool thing about this Troy is that: you can just double click on it.
It's already formatted to be a little more readable. Wow! Check that out.
There we go. Another quick tip: since we're here, first thing to do is: select everything and Scale; just to make sure that you're not missing any of the metrics. And this is also a great way to illustrate which service counters and system counters we collect. As you can see, there's quite a few here.
Okay. So, that Performance Monitor is, it's set up; it's running; we can see how it looks; and that is going to run all the time or just when we manually trigger it?
You can definitely configure it to run all the time, and that would be my advice. Its value is really realized as a baseline.
Yeah. Exactly. That was pretty cool seeing how that worked, using all the built-in utilities. And that Relog formatting for the Process Monitor was new to me. Are there any other tools you like to highlight?
Yeah. So, Performance Monitor is built-in. For larger Enterprises that may already be monitoring resources in a centralized way, there's no reason why you shouldn't expect to include the Sense resources into that live monitoring. And this could be done via different solutions out there. A few come to mind like: Grafana, Datadog, Butler SOS, for example from one of our own Qlik luminaries.
Can we take a quick look at Grafana? I’ve heard of that but never seen it.
Sure thing. This is my host monitor sheet. It's nowhere built to a corporate standard, but you can see here I’m looking at resources for the physical host where these VMs are running as well as the domain controller, and the main server where we've been running our CPU tests. And the great part about this is I have historical data as far back I believe as 90 days.
So, this is a cool tool that lets you like take a look at the performance and zoom-in and find the processes that might be causing some peaks or anything you want to investigate?
Right. Exactly. At least come up with a with a narrow time frame for you to look into the other tools and again narrow down the window of your investigation.
Yeah, that could be really helpful. Now I wanted to move on to the Qlik Sense Scalability Tools. Are those available on Qlik community?
That's right. Let me show you where to find them. You can see that we support all current versions including some of the older ones. You will have to go through and download the package and the applications used for analysis afterwards. There is a link over here. So, once the package is downloaded, you will get an installer. And the other cool thing about Scalability Tools is that you can use it to pre-warm the cache on certain applications since Qlik Sense Enterprise doesn't support application pre-loading.
Oh, cool. So, you can throttle up applications into memory like in QlikView. Can we take a look at it?
Yes, absolutely. This is the first thing that you'll see. We'll have to create a new connection. So, I’ll open a simple one that I’ve defined here and we can take a look at what's required just to establish a quick connection to your Qlik Sense site.
Okay, but basically the scenario that you're setting up will simulate activity on a Qlik Sense site to test its performance?
Exactly. You'll need to define your server hostname. This can be any of your proxy nodes in the environment. The virtual proxy prefix. I’ve defined it as Header and authentication method is going to be WebSocket.
Okay.
And then, if we want to look at how virtual users are going to be injected into the system, scroll over here to the user section. Just for this simple test, I’ve set it up for User List where you can define a static list of users like so: User Directory and UserName.
Okay. So, it's going to be taking a look at those 2 users you already predefined and their activity?
Exactly. We need to test the connection to make sure that we can connect to the system. Connection Successful. And then we can proceed with the scenario. This is very simple but let me show you how I got this far. So, the very first thing that we should do is to Open an App.
So, you're dragging away items?
Yep. I’m removing actions from this list. Let's try to change the sheet. A very simple action. And now we have four sheets, and we'll go ahead and select one of them.
Okay, so far, we have Opening the App and immediately changing to a sheet?
Yep. That's right. This will trigger actions in sequence exactly how you define them. It will not take into consideration things like Think Time. I will just define a static weight of 15 seconds, and then you can make selections.
But this is an amazing tool for being able to kind of stress test your system.
It's very very useful and it also provides a huge amount of detail within the results that it produces. One other quick tip: while defining your scenario, use easy to read labels, so that you can identify these in the Results Application. Let's assume that the scenario is defined. We will go ahead and add one last action and that is: to close, to Disconnect the app. We'll call this “OpenApp.” We'll call this “SheetChange.” Make sure you Save. The connection we've tested; we've defined our list of users. First, let's run the scenario. There is one more step to define and that is: to configure an Executor that will use this scenario file to launch a workload against our system. Create a New Sequence.
This is just where all these settings you're defining here are saved?
Correct. This is simply a mapping between the execution job that you're defining and which script scenario should be used. We'll go ahead and grab that. Save it again; and now we can start it. And now in the background if we were to monitor the Qlik Sense environment, we would see some amount of load coming in. We see that we had some kind of issue here: empty ObjectID. Apparently I left something in the script editor; but yeah, you kind of get the idea.
So, all this performance information would then be loaded into an app that is part of the package downloaded from Qlik community. How does that look?
So, here you will see each individual result set, and you can look at multiple-exerciser runs in the single application. Unfortunately, we don't have more than one here to showcase that, but you would see multiple-colored lines. There is metrics for a little bit of everything: your session ramp, your throughput by minute, you can change these.
CPU, RAM. This is great.
Exactly. CPU and RAM. These are these are not connected. We don't have those logs, but you would have them for a setup run on your system. These come from Performance Monitor as well, so you could just use those logs provided that the right template is in place. We see Response Time Distribution by Action, and these are the ones that I’ve asked you to change and name so that they're easy to understand.
Once your deployment is large enough to need to be multi-node and the default settings are no longer the best ones for you, what needs to be adjusted with a Repository Service to keep it from choking or to improve its performance?
That's a great question Troy. So, the first thing that we should take a look at is how the Repository communicates with the backend Database and vice versa. The connection pool for the Repository is always based on core count on the machine. And the best rule of thumb that we have to date is to take your core count on that machine, multiply it by 5, and that will be the max connection pool for the Repository Service for that node.
Can you show us where that connection pool setting can be changed?
Yes. So, we will go ahead and take a look. Here we are on the central node of my environment. You'll have to find your Qlik installation folder. We'll navigate to the Repository folder, Util, QlikSenseUtil, and we'll have to launch this “As Admin.”
Okay.
We'll have to come to the Connection String Editor. Make sure that the path matches. We just have to click on Read so that we get the contents of these files. And the setting that we are about to change is this one.
Okay. So, the maximum number of connections that the Repository can make?
Yes. And this is (again) for each node going towards the Repository Database.
Okay.
Again, this should be a factor of CPU cores multiplied by 5. If 90 is higher than that result, leave 90 in place. Never decrease it.
Okay, that's a good tip.
Right. I change this to 120. I have to Save. What I like to do here is: clear the screen and hit Read again; just to make sure that the changes have been persisted in the file.
Okay.
Once that's done, we can close this. We can restart the environment. We can get out of here.
So, there you adjusted the setting of how many connections this node can make to the QSR. Then assuming we do the same on all nodes, where do we adjust the total number of connections the Repository itself can receive?
That should be a sum of all of the connection strings from all of your nodes plus 110 extra for the central node. By default, here is where you can find that config file: Repository, PostgreSQL, and we'll have to open this one, PostgreSQL. Towards the end of the file…
Just going all the way to the bottom.
Here we have my Max Connections is 300.
Okay. One other setting you mentioned was the Page File and something to be considered. How would we make changes or adjust that setting?
Right. So, this is a Windows level setting that's found in Advanced System Settings; Advanced tab; Performance; and then again Advanced; and here we have Virtual Memory.
Okay.
We have to hit Change. We'll have to leave it at System Managed or understand exactly which values we are choosing and why. If you're not sure, the default should always be System Managed.
Now, I want to know what resources are available for Qlik Sense admins; specifically, what is the Admin Playbook?
It's a great starting place for understanding what duties and responsibilities one should be thinking about when administering a Qlik Sense site.
So, these are a bunch of tools built by Qlik to help analyze your deployment in different ways. I see weekly, monthly, quarterly, yearly, and a lot of different things are available there.
Yeah. So, we can take a look at Task Analysis, for example. The first time you run it, it's going to take about 20 minutes; thereafter about 10. The benefits: it shows you really in depth how to get to the data and then how to tweak the system to work better based on what you have.
Yeah, that's great.
Right? So, not only we put the tools in your hands, but also how to build these tools as you can here. See here, we have instructions on how to come up with these objects from scratch. An absolute must-read for every system admin out there.
Mario, we've talked about optimizing the Qlik Sense Repository Service, but not about Postgres? Do larger Enterprise level deployments affect its performance?
Sure. The thing about Postgres is again: we have to configure it by default for compatibility and not performance. So, it's another component that has to be targeted for optimization.
The detail there that anything over 1 Gb from Postgres might get paged - that sounds like it could certainly impact performance.
Right, because the buffer setting that we have by default is set to 1 Gb; and that means only 1 Gb of physical memory will be allocated to Postgres work. Now, we're talking about the large environment 500 to maybe 5,000 apps. We're talking 1000s of users with about 1000 of them peak concurrency per hour.
So, can we increase that Shared Buffer setting?
Absolutely. And in fact, I want to direct you to a really good article on performance optimization for PostgreSQL. And when we talk about fine-tuning, this article is where I’d like to get started. We talk about certain important factors like the Shared Buffers. So, this is what we define to 1 Gb by default. Their recommendation is to start with 1/4 of physical memory in your system. 1 Gb is definitely not one quarter of the machines out there. So, it needs tweaking.
And again these are settings to be changed on the machine that's hosting the Repository Database, right?
That's correct. That's correct.
Now, is there an app that you're aware of that would be good to kind of look at all these logs and analyze what's going on with the performance?
Absolutely. This is an application that was developed to better understand all of the transactions happening in a particular environment. It reads the log files collected with the Log Collector either via the tool or the QMC itself.
Okay.
It's not built for active monitoring, but rather to enhance troubleshooting.
Sure. So, basically it's good for looking at a short period of time to help troubleshooting?
Right. The Repository itself communicates over APIs between all the nodes and keeps track of all of the activities in the system; and these translate to API calls. If we want to focus on Repository API calls, we can start by looking at transactions.
Okay.
So, this will give us detail about cost. For example, per REST call or API call, we can see which endpoints take the most, duration per user, and this gives you an opportunity to start at a very high level and slowly drill in both in message types and timeframe. Another sheet is the Threads Endpoints and Users; and here you have performance information about how many worker-threads the Repository Service is able to start, what is the Repository CPU consumption, so you can easily identify one. For example, here just by discount, we can see that the preview privileges call for objects is called…
Yeah, a lot.
Over half a million times, right? And represents 73% of the CPU compute cost.
Wow, nice insights.
And then if we look here at the bottom, we can start evaluating time-based patterns and select specific time frames and go into greater detail.
So, I’m assuming this can also show resource consumption as well?
Right. CPU, memory in gigabytes and memory in percent. One neat trick is: to go to the QMC, look at how you've defined your Working Set Limits, and then pre-define reference lines in this chart. So, that it's easier to visualize when those thresholds are close to being reached or breached. And you do that by the add-ons reference lines, and you can define them like this.
That's just to sort of set that to match what's in the QMC?
Exactly.
Makes a powerful visualization. So, you can really map it.
Absolutely. And you can always drill down into specific points in time we can go and check the log details Engine Focus sheet; and this will allow us to browse over time, select things like errors and warnings alone, and then we will have all of the messages that are coming from the log files and what their sources.
Yeah. That's great to have it all kind of collected here in one app, that's great.
Indeed.
To summarize things, we've talked about to understand system performance, a baseline needs to be established. That involves setting up some monitoring. There are lots of options and tools available to do that; and it's really about understanding how the system performs so the measurement and comparisons are possible if things don't perform as expected.
And to begin to optimize as well.
Okay, great. Well now, it's time for Q&A. Please submit your questions through the Q&A panel on the left side of your On24 console. Mario, which question would you like to address first?
We have some great questions already. So, let's see - first one is: how can we evaluate our existing Qlik Sense applications?
This is not something that I’ve covered today, but it's a great question. We have an application on community called App Metadata Analyzer. You can import this into your system and use it to understand the memory footprint of applications and objects within those applications and how they scale inside your system. It will very quickly illustrate if you are shipping applications with extremely large data files (for example) that are almost never used. You can use that as a baseline for both optimizing local applications and also in your efforts to migrating to SaaS, if you feel like you don't want to bother with all of this Performance Monitoring and optimization, you can always choose to use our services and we'll take care of that for you.
Okay, next question.
So, the next question: worker schedulers errors and engine performance. How to fix?
I think I would definitely point you back to this Log Analysis application. Load that time frame where you think something bad happened, and see what kind of insights you can you can get by playing with the data, by exploring the data. And then narrow that search down if you find a specific pattern that seems like the product is misbehaving. Talk to Qlik support. We'll evaluate that with you and determine whether this is a defect or not or if it's just a quirk of how your system is set up. But that Sense Log Analysis app is a great place to start. And going back to the sheet that I showed: Repository and Engine metrics are all collected there. And these come from the performance logs that we already produce from Qlik Sense. You don't need to load any additional performance counters to get those details.
Okay.
All right. So, there is a question here about Postgres 9.6 and the fact that it's soon coming end of life. And I think this is a great moment to talk about this. Qlik Sense client-managed or Qlik Sense Enterprise for Windows supports Postgres 12.5 for new installations since the May release. If you have an existing installation, 9.6 will continue to be used; but there is an article on community on how to in-place upgrade that to 12.5 as a standalone component. So, you don't have to continue using 9.5 if your IT policy is complaining about the fact that it's soon coming to the end of life. As we say, we are aware of this fact; and in fact, we are shipping a new version as of the May 2021 release.
Oh, great.
So, here's an interesting question. If we have Qlik Sense in Azure on a virtual machine, why is the performance so sluggish? How do you fine-tune it? I guess first we need to understand what would you mean by sluggish? But the first thing that I want to point to is: different instance types. So, virtual machines in virtual private cloud providers are optimized for different workloads. And the same is true for AWS, Azure and Google Cloud platform. You will have virtual machines that are optimized for storage; ones that are optimized for compute tasks or application analytics; some that are optimized for memory. Make sure that you've chosen the right instance type and the right level of provisioned iOps for this application. If you feel that your performance is sluggish, start increasing those resources. Go one tier up and reevaluate until you find a an instance type that works for you. If you wish to have these results (let's say beforehand), you will have to consider using the Scalability Tools together with some of your applications against different instance types in Azure to determine which ones work best.
Just to kind of follow up on that question, if we're looking at that multi-node example from Qlik help, what nodes would you consider would require more resources?
Worker nodes in general. And those would be front and back-end.
So, a worker node is something with an engine, right?
Exactly. Something with an engine. It can either be front-facing together with a proxy to serve content, or back-end together with a scheduler a service to perform reload tasks. These will consume all the resources available on a given machine.
Okay.
And this is how the Qlik Sense engine is developed to work. And these resources are almost never released unless there is a reason for it, because us keeping those results cached is what makes the product fast.
Okay.
Oh, here's a great one about avoiding working set breaches on engine nodes. Question says: do you have any tips for avoiding the max memory threshold from the QIX engine? We didn't really cover this this aspect, but as you know the engine allows you to configure memory limits both for the lower and higher memory limit. Understanding how these work; I want to point you back to that QIXs engine white paper. The system will perform certain actions when these thresholds are reached. The first prompt that I have for you in this situation is: understand if these limits are far away from your physical memory limit. By default, Qlik Sense (I believe) uses 70 / 90 as the low and high working sets on a machine. With a lot of RAM, let's say 256 - half a terabyte of RAM, if you leave that low working set limit to 70 percent, that means that by default 30 of your physical RAM will not be used by Qlik Sense. So. always keep in mind that these percentages are based on physical amount of RAM available on the machine, and as soon as you deploy large machines (large: I’m talking 128 Gb and up) you have to redefine these parameters. Raise them up so that you utilize almost all of the resources available on the machine ,and you should be able to visualize that very very easily in the Log Analysis App by going to Engine Load sheet and inserting those reference lines based on where your current working sets are. Of course, the only way really to avoid a working set limit issue is to make sure that you have enough resources. And the system is configured to utilize those resources, so even if you still get them after raising the limit and allowing the - allowing the product to use as much RAM as it can without of course interfering with Windows operations (which is why you should never set these to like 99, 98, 99). Windows needs RAM to operate by itself, and if we let Qlik Sense to take all of it, it will break things. If you've done that and you're still having performance issues, that means you need more resources.
Yeah. It makes sense.
Oh, so here is another interesting question about understanding what certain Qlik Repository Service (QRS) log messages say. There is a question here that says: try to meet the recommendation of network and persistence the network latency should be less than 4 MS, but consistently in our logs we are seeing the QRS security management retrieved privileges in so many milliseconds. Could this be a Repository Service issue or where would you suggest we investigate first? This is an info level message that you are reporting. And it's simply telling you how long it took for the Repository Service to compute the result for that request. That doesn't mean that this is how long it took to talk to the Database and back, or how long it took for the request to reach from client to the server; only how long it took for the Repository Service to look up the metadata look up the security rules and then return a result based on that. And I would say this coming back in 384 milliseconds is rather quick. It depends on how you've defined these security rules. If these security rules are super simple and you are still getting slow responses, we would definitely have to look at resource consumption. But if you want to know how these calls affect resource consumption on the Repository and Postgres side, go back to that Log Analysis App. Raise your Repository performance logs in the QMC to Debug levels so that you get all of the performance information of how long each call took to execute. And try to establish some patterns. See if you have calls that take longer to execute than others; and where are those coming from any specific apps, any specific users? All of these answers come from drilling down into the data via that app that I demoed.
Okay Mario, we have time for one last question.
Right. And I think this is an excellent one to end. We talked a whole bunch here about Qlik Sense, but all of this also applies to QlikView environments. We are always looking at taking a step back and considering all of the resources that are playing in the ecosystem, not just the product itself. And the question asks: is QlikView Server performance similar to how it handles resources Qlik Sense? The answer is: yes. The engine is exactly the same in both products. If you read that white paper, you will understand how it works in both QlikView and Qlik Sense. And the things that you should do to prepare for performance and optimization are exactly the same in both products. Excellent question.
Great. Well, thank you very much Mario!
Oh, it's been my pleasure Troy. That was it for me today. Thank you all for participating. Thank you all for showing up. Thank you Troy for helping me through this very very complicated topic. It's been a blast as always. And to our customers and partners, looking forward to seeing your questions and deeper dives into logs and performance on community.
Okay, great! Thank you everyone! We hope you enjoyed this session. Thank you to Mario for presenting. We appreciate getting experts like Mario to share with us. Here's our legal disclaimer and thank you once again. Have a great rest of your day.
-
Migrating to Qlik Cloud from Client-Managed Qlik Sense or QlikView
Qlik Cloud is a modern analytics and data platform built on the same software engine as QlikView and Qlik Sense Client-Managed and adds significant va... Show MoreQlik Cloud is a modern analytics and data platform built on the same software engine as QlikView and Qlik Sense Client-Managed and adds significant value to empower everyone in an organization to make better decisions daily. Qlik Cloud allows you to use one common platform for all users – executives, decision-makers, and analysts.
Migrating to Qlik Cloud can help your organization:
- Expand your analytics possibilities.
- Reduce infrastructure costs and the total cost of ownership of your Qlik solutions.
- Automate entitlement management and upgrades.
Migrating from Client-Managed Qlik Sense
This site provides you the tools to monitor, manage, and execute a migration from Client-Managed Qlik Sense to Qlik Cloud.
No two client-managed Qlik Sense Enterprise deployments are the same. And no two migrations will be the same. The processes, procedures, and instructions in this section shouldn’t be considered a cookbook. Rather, they’re meant to guide you.
The Qlik Cloud Migration Center provides a general approach to migration along with sequencing, strategy, and best practice recommendations. It also includes tools such as a Qlik Sense app, scripts, and worksheets to aid in planning elements of the migration.
If your organization has a complex deployment with custom tooling, or sophisticated or complicated data integration pipelines, consider contacting your Qlik Customer Support representative.Migrating from QlikView
This site provides comparisons of QlikView and Qlik Cloud, as well as best practices on how to move content, including information about migration assessments and QlikView document conversions.
Recent Documents
-
How to run an automation once a month
It is not possible to schedule an automation to run once a month through the Scheduled run mode alone. This article explains how this can be achieved... Show MoreIt is not possible to schedule an automation to run once a month through the Scheduled run mode alone. This article explains how this can be achieved by leveraging the Date formulas.
Environment
- Qlik Sense Enterprise SaaS
- Qlik Application Automation
Steps
- Create new automation and set the run mode to Scheduled and configure it to schedule every day.
- Add a Condition block to the Start block. Click the Condition block's input field to configure the first argument of the Condition block. Select 'Add formula' and search for the Date formula in the Formula picker that pops up. Configure the Date formula as follows:
- Now select "equals" as the condition block's operator and specify "1" as the second argument.
- Now place your automation in the YES output of the condition block. This will only be executed on the first day of every month.
Bonus
- Instead of executing the whole automation in the YES output of the Condition block, execute a Stop block in the NO output of the block. Place your automation after the Condition block.
- Execute an Update Job Title block when it's the first day of the month. This will make it easy to search for only relevant automation runs in the automation's history.
The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.
-
QlikSenseUtil as a backup tool
Qlik Sense Util comes bundled with Qlik Sense Enterprise on Windows. It is able to assist in the backing up process to help with the manual process. K... Show MoreQlik Sense Util comes bundled with Qlik Sense Enterprise on Windows. It is able to assist in the backing up process to help with the manual process.
Key elements that need to be backed up are:
- the certificates used by Qlik Sense for internal communication
- the Repository Database used to store meta-data about the Qlik Sense site
- the applications used by the Qlik Sense site
- the Extensions, thumbnail images, etc used by Qlik Sense apps
Other options components can include:
- Logs of activity on the site
- Supplemental material used by the Qlik Sense site (e.g. QVDs, Excel files, etc)
By using the QlikSenseUtil tool, an administrator can back all of these up and configure the tool to be integrated in a silent, incremental backup policy.
How to get started with QlikSenseUtil
- Locate the QlikSenseUtil tool
- Default location: C:\Program Files\Qlik\Sense\Repository\Util\QlikSenseUtil\QlikSenseUtil.exe
- Custom path example: Drive:\InstallationPath\Repository\Util\QlikSenseUtil\QlikSenseUtil.exe
- In Notepad or any text editor of your choice, construct the desired command line syntax for the backup.
- A sample command:
C:\"Program Files"\Qlik\Sense\Repository\Util\QlikSenseUtil\QlikSenseUtil.exe -backup -databaseHostname="FQDNofTheDatabase" -databasePassword="SuperUserPassword" -path="Drive:\BackupLocation" -rootPath="\\ServiceCluster\Path" - For a full listing of the flags possible, consult later in this article or the Readme.txt in the QlikSenseUtil directory.
Note: Executing a backup will stop Qlik Sense services, so only execute this tool when downtime is acceptable
Note: Executing as the Qlik Sense service account is recommended since the client certificate used by Qlik Sense is only accessible to the service account
Note: For silent backups, do remember to use the -f flag
Note: Logs for this tool will be written to %USERPROFILE%\AppData\Local\Temp\logs for the account executing the tool
- A sample command:
- Once the command is written out, launch a command prompt and paste in the command
- Observe the files backed up to the path specified in the path flag
Video Walk-through:
(video from https://www.youtube.com/watch?v=uuF4FMnaD1I?)
Flags:
backup: specifies a backup operation (mandatory)
path: path to the folder where to store the backup (mandatory)
certificatePassword: password for protecting the certificates. If not specified then the certificates will not be backed up
databaseHostname: Hostname where the database is running (mandatory)
databasePassword: Superuser or postgres account password (mandatory, if password was configured)
databasePort: port number used by the database)
f: silent backup
h: helpTo restore your environment, see Backup and restore Qlik Sense Enterprise on Windows.
Related Content:
-
Upgrading Qlik Sense Repository Database from PostgreSQL 9.6 to 12.5 - New tool ...
This Support article covers the functionality available in the new Qlik PostgreSQL Installer (QPI), which allows you to upgrade an existing Qlik Sens... Show MoreThis Support article covers the functionality available in the new Qlik PostgreSQL Installer (QPI), which allows you to upgrade an existing Qlik Sense Repository Database (QRD) from PostgreSQL 9.6 to PostgreSQL 12.5, or install a brand new QRD based on PostgreSQL 12.5.
PostgreSQL 9.6 has reached End of Life Reference: PostgreSQL Versioning Policy.
Index
- Supported Scenarios
- Considerations
- Installing anew Qlik Sense Repository Database using PostgreSQL 12.5
- Upgrading anexisting QRD to PostgreSQL 12.5 - Central Node
- Final steps
- Compatibility with PostgreSQL installers
- Important Note onpostgresql-x64-12
- Qlik PostgreSQL Installer - Download Link
- To revert/undo
- Troubleshooting
- Related Content
Environment
- New Qlik Sense Repository Database (QRD) setups for, compatible with Qlik Sense Enterprise Client Managed since November 2019 - Reference: Qlik Sense's compatibility with PostgreSQL for the... - Qlik Community - 1716510
- Existing QRD setups running Qlik Sense embedded PostgreSQL 9.6 (Qlik Sense February 2022 and higher)
Supported Scenarios
- Prior to using QPI, upgrade to the latest supported version of Qlik Sense first and then use QPI to upgrade PostgreSQL. Qlik Sense Enterprise on Windows February 2022 and above are supported versions for QPI.
Note: If you are looking to only upgrade your PostgreSQL to 12.5 and do not plan to upgrade Qlik Sense at this point, please wait for the next available release of QPI. - Upgrading an existing QRD from PostgreSQL 9.6 to 12.5. Upgrading earlier versions of QRD is not supported via this method. Instead, upgrade to a version of Qlik Sense that supports 9.6 to automatically migrate through this minor version - List of supported PostgreSQL and early Qlik Sense versions
- Installing a new QRD cluster on a new server, for a new environment or to migrate an existing Database to a separate host.
Considerations
- This installer will not detect a standalone PostgreSQL database installed by any other means, other than through an official Qlik installer package
- The installer itself does not provide a rollback feature, this is described below
- The user who runs the installer has sufficient permissions to execute it and install services
- The user who runs the installer has sufficient permissions to create folders and files in the backup destination
- The backup destination has sufficient free disk space to dump the existing database
- The backup destination is not a network path or virtual storage folder. It is recommended the backup is stored on the main drive
- There will be downtime during this operation, please plan accordingly
Installing a new Qlik Sense Repository Database using PostgreSQL 12.5
- All services on rim nodes must be stopped
- Run the Qlik PostgreSQL Installer
- Click on "Install"
- Accept the Qlik Customer Agreement
- Set your "Local database settings" and click "Next". You will use these details to connect other nodes to the same cluster
- Set your "Database superuser password" and click "Next"
- Review your settings and click "Install", then click "Finish"
- Restore your existing QRD backup OR
- Start installing Qlik Sense Enterprise Client Managed, connecting to an existing cluster
- The tool will display information on the actions being performed. Once installation is finished, you can close the installer.
If you are migrating your existing databases to a new host, please remember to reconfigure your nodes to connect to the correct host. How to configure Qlik Sense to use a dedicated PostgreSQL database
A new service named postgresql-x64-12 is now installed. It is run by the Network Service account. Do not change the name of the service and do not modify the account running it.
The PostgreSQL package can be removed via the Control Panel in case you need to uninstall the database at one point.
Upgrading an existing QRD to PostgreSQL 12.5 - Central Node
It is required to first upgrade Qlik Sense to the latest supported version with QPI. Qlik Sense Enterprise on Windows February 2022 and above are supported versions for QPI.
Note: If you are looking to only upgrade your PostgreSQL to 12.5 and do not plan to upgrade Qlik Sense at this point, please wait for the next available release of QPI.Installations other than in C:\Program Files\Qlik\Sense are not currently supported.
In this scenario, the new QRD will be using the same port as the old service, and rim nodes do not need any further reconfiguration.
- Stop all services on rim nodes
- On your Central Node, stop all services but Qlik Sense Repository Database
- Run the Qlik PostgreSQL Installer, an existing Database will be detected
- Click on "Upgrade"
- Accept the Qlik Customer Agreement
- Provide your existing "Database superuser password" and click "Next"
- Set your desired backup location and click "Next"
- Click "Upgrade", then exit by clicking "Finish"
- Start all other services on Central Node
- Start all services on all other nodes
A new service named postgresql-x64-12 is now installed. It is run by the Network Service account. Do not change the name of the service and do not modify the account running it.
Final steps
At this point, it is important to validate that all services and nodes are operating as expected.
If the upgrade seems to have been unsuccessful and you are missing data in the Qlik Management Console or elsewhere, see Revert/Undo on steps to undo the swap to the new database and ever to the old instance. We also recommend contacting Qlik Support.
Once the upgrade and all validation are complete, the old Qlik Sense Repository Database service should be removed from the server.
Perform the following steps to delete the Qlik Sense Repository Database Service:
- Open a command prompt and run the command:
c:\cd ProgramData\Package Cache - From there, run:
c:\ProgramData\Package Cache\dir /s PostgreSQL.msi - The folder containing PostgreSQL.msi will be revealed.
- Right-click on the PostgreSQL.msi file and select uninstall from the menu.
Compatibility with PostgreSQL installers
This version of the upgrade tool is fully compatible with the official PostgreSQL installers from https://www.enterprisedb.com/downloads/postgres-postgresql-downloads.
If you need to upgrade to a higher release than Qlik provides you can install the same major PostgreSQL release 12.x or up to version 13.
Important Note on postgresql-x64-12
Do not change the name of the new postgres Service postgresql-x64-12 back to e.g. Qlik Sense Repository Database. Renaming the service will impact all of your future upgrades.
Qlik PostgreSQL Installer - Download Link
Download the installer here.
Note: The executable was temporarily removed. A new one will be provided as soon as testing is concluded.To revert/undo
- Stop the newly installed postgresql-x64-12 service
- Set the startup type to Manual.
- Start the original Qlik Sense Repository Database service, along with all other Qlik Sense services.
Troubleshooting
If the installation crashes, the server reboots unexpectedly during this process, or there is a power outage, the new database may not be in a serviceable state. Installation/upgrade logs are available in your temporary files location, for example:
C:\Users\Username\AppData\Local\Temp\2
A backup of the original database contents is available in your chosen location, or by default in:
C:\ProgramData\Qlik\Sense\Repository\PostgreSQL\backup\9.6
The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support. The video in this article was recorded in a earlier version of QPI, some screens might differ a little bit.
Related Content
Techspert Talks - Upgrading Qlik Sense Repository Service
Backup and Restore Qlik Sense Enterprise documentation
Migrating Like a Boss
Optimizing Performance for Qlik Sense Enterprise
Qlik Sense Enterprise on Windows: How To Upgrade Standalone PostgreSQL
How-to reset forgotten PostgreSQL password in Qlik Sense
How to configure Qlik Sense to use a dedicated PostgreSQL database
Troubleshooting Qlik Sense Upgrades -
Qlik Enterprise Manager Upgrade Guide
Introduction This is a guide with instructions and best practices surrounding a Qlik Enterprise Manager upgrade. Qlik Enterprise Manager provides a... Show MoreIntroduction
This is a guide with instructions and best practices surrounding a Qlik Enterprise Manager upgrade. Qlik Enterprise Manager provides a single point of control for designing, executing, and monitoring Qlik Replicate and Compose tasks.
Steps
To upgrade Enterprise Manager:
1. Keep the current QEM service running on the server. The upgrade process will handle stopping the service.
2. Back up the Enterprise Manager data folder to a location outside the product folder. The default data folder location is C:\Program Files\Attunity\Enterprise Manager\data.
In this example, I back up to C:\temp.
3. Download the software
4. Extract the files to a location you can find
5. Run the Enterprise Manager as Administrator
Select 'Yes' for the question prompt.
6. Follow the steps in the setup wizard to install the new version.
The QEM service will be running post upgrade. You can open by searching for 'console' in windows search bar.
Troubleshooting
If you notice an issue with the upgrade, please try the following first:1. Stop all the Enterprise Manager services.
2. Overwrite the data folder with the data folder that you backed up earlier.
3. Start all the Enterprise Manager services.
If there are still problems, and you need to revert to a previous version, then do:1. Uninstall the product
2. Reinstall the previous QEM version (that you upgraded from) to the same directory.
2. Once the installation is complete, stop all Enterprise Manager services.
3. Overwrite the data directory with the data directory that you backed up earlier.
4. Start all Enterprise Manager services.
If you are still experiencing issues with the software post upgrade or after reverting, please contact Support.Best Practices
1. We recommend users to keep Replicate and QEM on same release for best performance. Here is more information on compatibility with other Qlik products.
2. We recommend testing a QEM upgrade on a lower DEV or TEST environment first before attempting on a Production environment.
3. You do not need to stop Replicate tasks when upgrading QEM. QEM is a monitoring application, and the task are running on Replicate server not QEM.
4. If you encounter high memory usage on QEM server, try disconnecting Replicate or Compose servers and adding them back one at a time to see where it could be coming from.
Environment
The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.
-
Qlik Sense upgrade to November 2022 fails with "An existing standalone PostgresS...
Having used the Qlik PostgreSQL installer version 1.1 (QPI) to upgrade an existing 9.6 PostgresSQL to 12.5 in the May 2022 or August 2022 release. Aft... Show MoreHaving used the Qlik PostgreSQL installer version 1.1 (QPI) to upgrade an existing 9.6 PostgresSQL to 12.5 in the May 2022 or August 2022 release. After the upgrade of the Database you removed the Qlik Sense Repository Database Service. If you then upgrade to the Qlik Sense Enterprise on Windows November 2022 release might end in the following error message.
An existing standalone PostgreSQL v 12 database has been
detected in: C:\ProgramData\Qlik\Sense\Repository\PostgreSQL
\12.5 with the same installation path as the embedded Qlik Sense
PostgreSQL database. This will cause problems when upgrading
the current installation. Please cancel the setup and install the
PostgreSQL v 12 standalone database with a different path or
remove the database before installing Qlik Sense.Environment
- Qlik Sense Enterprise on Windows November 2022
Resolution
Disclaimer: The following code provided below is just an example and might need to be re-written to meet your expectation in your environment.
We need to move the upgraded database and the binary files for the database into a different folder. The newer version of the QPI (version 1.2 and above) support custom folders and will prevent this from happening.
To do so:
- Stop the postgresql-x64-12 service
- Open Regedit.exe as administrator
- HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\postgresql-x64-12
- Modify the image path to "C:\Program Files\PostgreSQL\12\bin\pg_ctl.exe" runservice -N "postgresql-x64-12" -D "C:\Program Files\PostgreSQL\12\data" -w
- Move the folder "C:\Program Files\Qlik\Sense\Repository\PostgreSQL\12.5" to "C:\Program Files\PostgreSQL\12\"
- Move the folder "C:\ProgramData\Qlik\Sense\Repository\PostgreSQL\12.5" to "C:\Program Files\PostgreSQL\12\data"
- Start the the postgresql-x64-12 service
- Start the upgrade to November 2022 again and the warning message is gone.
An PowerShell script for the same can be found here
Cause
The Qlik Postgres Installer in the early version could only install into the same directory which the Qlik Sense installation would use for an embedded PostgreSQL database. This lead to problems on the upgrade to the November release.
-
App and storage size for Qlik Sense Business and Enterprise SaaS
Information about app and storage size for Qlik Cloud for Qlik Sense Enterprise SaaS and Qlik Sense Business can be found in Qlik Sense capacity. No... Show MoreInformation about app and storage size for Qlik Cloud for Qlik Sense Enterprise SaaS and Qlik Sense Business can be found in Qlik Sense capacity.
Note: An app on disk is typically 4-6 times bigger in memory, although this is a rule of thumb and exceptions can and do occur, and an app within the "on-disk" size limit may balloon past the "in memory" limit.
App Objects have a memory limit of 6 GB, except when run on dedicated capacity.
If you are looking to compare other features between Qlik Sense Business and Qlik Sense Enterprise SaaS, see Compare product features and also Help> SaaS editions of Qlik Sense>Specifications.
Environment:- Qlik Sense Business edition
- Qlik Sense Enterprise on Cloud Services edition
Reference: Pricing -
Google Sheets - How to get started with Google Sheets in Automations
This article gives an overview of the available blocks in the Google Sheets connector in Qlik Application Automation. It will also go over some basic ... Show MoreThis article gives an overview of the available blocks in the Google Sheets connector in Qlik Application Automation. It will also go over some basic examples of creating a new spreadsheet, adding new sheets to it, adding some information to the sheet as well as removing information from it.
You will find the mentioned example attached to this article in JSON format so you can upload it to your workspace and get started with it easily.
This connector supports operations to manage spreadsheets, adding/removing sheets from spreadsheets, manipulating sheet data, and a few other support operations.
Authentication for this connector is based on the oAuth2 Protocol.
Let's start with the example prepared:
- Create an automation;
- From the left menu, select the Google Sheets connector;
- Drag and drop the "Create Spreadsheet" block and fill in a title for the spreadsheet we will be adding
- Let's go ahead and drag and drop the "Add Sheet to Spreadsheet" block and fill in the input parameters. (input parameter for the spreadsheet id can be filled manually by clicking the input and using the do lookup function for adding a new sheet to an existing spreadsheet or filled in from the output of the block before this one like shown in the picture)
- Next step in our example was to append a couple of rows and columns to our newly added spreadsheet in one go by using the 'Append Rows to Sheet' block:
For text values you can encase them between double quotes and for number you can simply send them as they are. The end result should look like the following picture in you document: - Now in order to get the data from the spreadsheet we've used the block 'Get data from sheet with Headers' and the resulting output from that block will be a list object ( if there were multiple lines ) with the headers used as keys of the object and the values taken from the following lines.
- Lastly we used the 'delete rows' block in order to delete 1 line from our sheet. Just as you can see on the block description, you are not allowed to delete all rows in a sheet.
- In the presented JSON example, you will notice a disabled 'Delete Spreadsheet' block as well. That block is only available for accounts whose administrator has allowed the app beforehand in their G Suite administration panel, so not every user might have access to that blocks functionality. If you want to delete a spreadsheet and don't have the possibility from our Google Sheet connector, manually deleting the spreadsheets from your account is the only way to do it.
The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.
-
How to upgrade the Qlik SAP connector
For general installation instructions and backwards compatibility information, see SAP Connector installation. Qlik SAP Connector versions 8.0.1 or ea... Show MoreFor general installation instructions and backwards compatibility information, see SAP Connector installation.
Qlik SAP Connector versions 8.0.1 or earlier include the SAP libraries.
Qlik SAP Connector versions 8.0.2 and later do not include the SAP libraries. You can download the SAP libraries from the SAP support download portal in accordance with SAP regulations after the upgrade of the Connector was performed. The Connector can be upgraded without issues first.
Index:
- Qlik SAP Extractor Connector
- Not using Qlik SAP Extractor Connector:
- Downloading and installing the SAP Libraries for connector 8.0.2 or later:
Qlik SAP Extractor Connector
Step 1:
For the SAP Extractor connector, follow the below steps before upgrading:
- Open Services.MSC, find Qlik SAP Network Service, and then click "Stop."
- Open cmd as an administrator and change the directory to C:\Program Files\Common Files\QlikTech\Custom Data\QvSAPConnector
- Run the SrvService Console.bat file.
- Choose option 3 to uninstall the Qlik SAP Network Service.
Step 2:
Once the service has been uninstalled, please proceed as follows:
- Uninstall the SAP connector.
- Install the new version SAP connector.
Step 3:
After installing the updated version of the Sap connector, follow the steps below to install the Qlik SAP extractor connector service "Qlik SAP Network Service."
- Open cmd as an administrator and change the directory to C:\Program Files\Common Files\QlikTech\Custom Data\QvSAPConnector
- Run the SrvService_Console.bat file.
- Choose option 2 to install the Qlik SAP Network Service.
- Open Services.MSC, find Qlik SAP Network Service, and then click "Start."
Note: Restart all the Qlik Sense Services if the installation is on a Qlik Sense system. The SAP Connector is not available in Qlik Sense until all the services are restarted.
Not using Qlik SAP Extractor Connector:
Examples: SQL, Bex, Info provider, Report, BW etc.
Once the service has been uninstalled, please proceed as follows:
- Uninstall the SAP connector.
- Install the new version SAP connector.
Downloading and installing the SAP Libraries for connector 8.0.2 or later:
The "SAP Connector for Microsoft.NET" and "SAP NW RFC SDK" versions will change for each connector upgrade based on SAP security patch releases, refer to the help page for additional information. Installing the connectors - Windows
- Log into the SAP Support Portal with an S-user ID.
- Go to SAP Connector for Microsoft .NET and search for: SAP Connector for Microsoft .NET, 64-bit, version 3.1 or later. This library is used by the Connectors with file extension .exe and contains sapnco.dll and sapnco_utils.dll.
- Download these library files. Double-click the SapNCox64Setup.exe file. The installation wizard starts, and the files are installed in Global Assembly Cache (GAC).
- Go back to the SAP Support Portal and search for: SAP NW RFC SDK, 64-bit, version 7.50 or later. This library is used by the legacy connectors with file extension .dll.
- Download and unzip the library. The library contains the following files:
- icudt50.dll
- icuin50.dll
- icuuc50.dll
- libicudecnumber.dll
- libsapucum.dll
- sapnwrfc.dll
- Place the files in this folder: C:\Program Files\Common Files\QlikTech\externals
-
How to convert Excel reports to PDF using Qlik Application Automation
Environment Qlik Sense Enterprise SaaS Qlik Application Automation Problem Currently, it is not possible to use Qlik Application Automation to send ... Show MoreEnvironment
- Qlik Sense Enterprise SaaS
- Qlik Application Automation
Problem
Currently, it is not possible to use Qlik Application Automation to send emails with Excel reports as attachments. Instead, we can create a public SharePoint link to the Excel report file and share the link via email.
You can find this approach described here.
Solution
We use Qlik Application Automation to convert the Excel report to PDF and send it as an attachment via email.
Automation Structure
The creation of an Excel report from a Qlik Sense straight table is explained in the first part of this article.
Let's walk through the blocks used in the automation workflow to convert the Excel file into a PDF file.
-
Use the 'Get File Download Url' block from Microsoft SharePoint Connector. You should set pdf as a value in the 'Format' parameter to convert the Excel file into a PDF file. This block returns the download URL of the PDF file in the response.
-
Open the PDF file using the 'Open File' block and select HTTP as the connector. Map the PDF file URL received from the above step in the 'URL' parameter.
- Save the PDF file on Microsoft SharePoint using the 'Copy File on Microsoft SharePoint' block.
- Open the PDF file created in Microsoft SharePoint using the 'Open File on Microsoft SharePoint' block.
- Use the 'Send Mail' block from Mail Connector to send the PDF file as an attachment via email.
A JSON file containing the above automation is attached to this article.
Please follow the steps provided in the How to import automation from a JSON file article to import the automation.
Related Content
-
How to Decrypt Qlik Replicate Verbose Task Log Files
Environment Qlik Replicate November 2021 (Version 2021.11 and later) What is Replicate log file encryption? Starting from Qlik Replicate version "... Show MoreEnvironment
- Qlik Replicate November 2021 (Version 2021.11 and later)
What is Replicate log file encryption?
Starting from Qlik Replicate version "November 2021", when the logging level is set to Verbose, sections in a Replicate log file that might contain customer data will be encrypted while the rest of the log will remain in clear text, this is implemented by the enable_data_logging configuration parameter set to true by default in order to prevent customer data snippets from appearing in verbose logs.
The sample line of the task log file looks like this:
2021-11-05T15:45:11:390848 [AT_GLOBAL ]I: Logging of database data is disabled (at_logger.c:2702)
However, in some support cases, R&D may require the ability to decrypt the log in order to analyze the problem. In such cases, Qlik Support will ask you to provide the task or environment-level encryption key and corresponding task log files:
- Replicate task diagnostics package file, and
- log.key file for decrypting a task's log file, location is <REPLICATE_INSTALL_DIR>\data\tasks\<task-name>
- log.key file for decrypting non-task log files (e.g. server logs), location is <REPLICATE_INSTALL_DIR>\data
Below is the detailed steps how to enable the database data logging and decrypt a task log file with corresponding log.key files.Steps
- Stop the Replicate Server service.
- Open <REPLICATE_INSTALL_DIR>\bin\repctl.cfg and set the enable_data_logging parameter to true. (add the line if it does not exist). This is set to true by default in version 2021.11 and later, even if you do not see this line in your configuration file.
{
"port": 3552,
"plugins_load_list": "repui",
... ...
"enable_data_logging": true
} - Save the repctl.cfg file and start the Replicate Server service.
- Now the task log files contain encrypted database data, sample lines like:
2021-11-05T15:59:07:969146 [AT_GLOBAL ]I: Log file encryption base "755opF/9oGw7YOx+2ma0dA==" (at_logger.c:2695)
2021-11-05T15:59:07:969146 [INFRASTRUCTURE ]I: The log level for 'TARGET_APPLY' has been changed from 'INFO' to 'VERBOSE'. (at_logger.c:2927)
2021-11-05T15:59:07:969146 [INFRASTRUCTURE ]I: The log level for 'SOURCE_CAPTURE' has been changed from 'INFO' to 'VERBOSE'. (at_logger.c:2927)... ...
2021-11-05T16:02:21:346746 [SOURCE_CAPTURE ]V: Event of captured table 'SCOTT.KIT' (data object id '93719', object id '93719') (oradcdc_parse.c:303)
The latest line is the encrypted database data (BeforeImage of an update).
2021-11-05T16:02:21:346746 [SOURCE_CAPTURE ]V: xid [000008fb00100007] (oradcdc_parse.c:166)
2021-11-05T16:02:21:346746 [SOURCE_CAPTURE ]V: BI col 2 [5]
~uAAAABvF/duclauaOYlVdTbsCpbAiSZGsq3DIp8XyImAZjY984KVgc62Lir20Mnw1VU1V/54j/vXa4BXpyQ7rjcEPR1MtjoYnpPSeu4QSwfiUx3l}~ (oradcdc_parse.c:340) - Run "Qlik Replicate Command Line" prompt "as administrator", and change to directory <REPLICATE_INSTALL_DIR>\bin
- Run the following command to decrypt task logs:
repctl dumplog <log-file-path> <log.key-path> [> <path-to-output-file>]
A sample command:
repctl dumplog "C:\Program Files\Attunity\Replicate\data\logs\reptask_Ora-to-SQL-2.log" "C:\Program Files\Attunity\Replicate\data\tasks\Ora-to-SQL-2\log.key" > "C:\Program Files\Attunity\Replicate\data\logs\reptask_Ora-to-SQL-2-dump.log"
Now we see the decrypted data in the task log files. The latest line is the BeforeImage of an update in clear text (in this sample it is text string "xxxxx"):
2021-11-05T16:02:21:346746 [SOURCE_CAPTURE ]V: Event of captured table 'SCOTT.KIT' (data object id '93719', object id '93719') (oradcdc_parse.c:303)
2021-11-05T16:02:21:346746 [SOURCE_CAPTURE ]V: xid [000008fb00100007] (oradcdc_parse.c:166)
2021-11-05T16:02:21:346746 [SOURCE_CAPTURE ]V: BI col 2 [5]
25a058b66d8: 7878787878 | xxxxx
(oradcdc_parse.c:340)
Related Instructions
The encryption key file (log.key file) can be used to decrypt any log that was encrypted with it, not just the log for which Qlik Support requested the encryption key file. If this is a concern, you can generate a log.key file that will only be valid for the specific log requested by Qlik Support.
To do this:- Delete the log.key file.
- Restart the task or Replicate Server service (depending on the log type required) to generate a new log.key file.
- Send the requested log file and the log.key file to Qlik Support.
- Delete the log.key file.
- Repeat steps 2-4 if you need to provide additional encrypted logs.
If you do not want verbose logs encrypted, open <REPLICATE_INSTALL_DIR>\bin\repctl.cfg and set the enable_data_logging parameter to false then restart the Replicate server service. Add the line if you do not see it in the file.
Products: Qlik Replicate
-
Replicate - Generate a log.key file manually
When working with Qlik Replicate, log.key file(s) are used for Decrypt Qlik Replicate Verbose Task Log Files , the log.key file can be re-created by r... Show MoreWhen working with Qlik Replicate, log.key file(s) are used for Decrypt Qlik Replicate Verbose Task Log Files , the log.key file can be re-created by restarting tasks, or restarting Replicate services if the log.key file is missed/deleted. However sometimes we need the file creation prior to the first time task run eg
(1) Set proper file protection manually by DBA
(2) Task movement among different environment eg UAT and PROD
(3) In rare cases the file auto-creation failed due to some reasons
This article provide some methods to generate file "log.key" manually.
Environment
- Qlik Replicate Version 2021.11 and later
Resolution
There are several methods to get a "log.key" file manually.
1. Copy an existing "log.key" file from UAT/TEST task folder;
It's better to make sure the "log.key" uniqueness, so below method (2) is recommended:
2. Run "openssl" command on Linux or Windows
openssl rand -base64 32 >> log.keyThe command will return a 44-chars random unique string (the latest char is "=") in "log.key" file. For example
n1NJ7r2Ec+1zI7/USFY2H1j/loeSavQ/iUJPaiOAY9Y=Related Content
Support cases, #00059433
-
CEF rendering errors in NPrinting. Troubleshooting step: use benchmarks to test ...
The benchmark application replicates the steps NPrinting follows to import images form Qlik Sense. It can be used to import many copies of the same im... Show MoreThe benchmark application replicates the steps NPrinting follows to import images form Qlik Sense. It can be used to import many copies of the same image quickly and verify if there is any problems during the process.
This must be used any time a report generation fails with "CEF rendering" error messages in the NPrinting Engine log files.
The benchmark creates specific log files in the NPrinting log folder and saves the imported images in C:\tmp.
Environment- NPrinting 18 April release and higher
Cause:
CEF rendering errors became quite common and the logs are not detailed enough to understand the cause of the problem.
The test described here gives useful information to troubleshoot the problem.Resolution:
Here are the steps to configure the Benchmark:
1. Download the zip file from here and extract it in C:\Benchmark\ on the machine where NPrinting Engine is installed.
2. Open the Engine.Navigator.QlikSense.Benchmarks.exe.config file in C:\Benchmark\Engine.Navigator.QlikSense.Benchmarks with a text editor program.
3. It is necessary to re-configure the connection to Qlik Sense. Follow these steps:In <!-- Sense user identity -->
<add key="bench-user-identity" value="domain\UserName" />
Add the user domain and name as it is in the NPrinting connection
In <!-- Sense url -->
<add key="bench-proxy-url" value="https://qlikserverName" />
Add the Qlik Sense Server Proxy address as it is in the NPrinting connection.In <!-- Sense app name -->
<add key="bench-app-name" value="PxP training Executive Dashboard" />Add the Sense app name (not the app ID).
In <!-- Sense object ids to print in the app, each navigator will render in the specified sequence the ids -->
<add key="bench-obj-ids" value="OjectID" />
Add the ID of the image that you are importing in the report. The image ID can be seen in the NPrinting template.In <!-- number of NPrinting printing identical cloned request per navigator process call -->
<add key="bench-request-number" value="10" />
Specify how many times the image must be imported. For troubleshooting purposes, 10 can be considered as the minimum.
4. Stop the NPrinting Engine and the NPrinting Scheduler services.
5. Double click on the Engine.Navigator.QlikSense.Benchmarks.exe to start the application.
Collect the logs in the NPrinting logs folder and check if the images are generated in C:\tmp.
Verify that all the images are there and how is the image quality.
-
Qlik NPrinting Engine May 2022 SR2 issue: Missing columns data
Due to a defect discovered in the Qlik NPrinting Engine (May 2022 SR2) in combination with Qlik Sense client-managed connections, we advise holding on... Show MoreDue to a defect discovered in the Qlik NPrinting Engine (May 2022 SR2) in combination with Qlik Sense client-managed connections, we advise holding on upgrading Qlik NPrinting to the May 2022 SR2 version.
Impact
Only connections to Qlik Sense client-managed are impacted on the following Qlik NPrinting version:
Qlik NPrinting May 2022 SR2
Resolution
The Qlik NPrinting team is treating this as the highest priority and working on the Qlik NPrinting May 2022 SR3 release to provide a permanent solution.
Qlik NPrinting May 2022 SR3 is expected to be delivered on the week of the 21st of November 2022.
Workaround
We strongly recommend upgrading to Qlik NPrinting May 2022 SR3 once available, rather than to the SR2 release. In case you have already upgraded to Qlik NPrinting May 2022 SR2, follow the recommended workaround.
- Uninstall Qlik NPrinting Engine May 2022 SR2
- Install Qlik NPrinting Engine May 2022 SR1
- If the engine is offline on the Qlik NPrinting Web console interface:
- Remove the Qlik NPrinting Engine
- Readd the Qlik NPrinting Engine
- Upgrade the Qlik Nprinting Server and Qlik Nprinting Engine to May 2022 SR3 as soon as the SR is released.
Internal Investigation ID:
OP-95779
Environment
Qlik NPrinting May 2022 SR2
-
How-to reset forgotten PostgreSQL password in Qlik Sense
During the installation of Qlik Sense, it is recommended to provide a password for the PostgreSQL database.Should you forget this password, there's no... Show More
During the installation of Qlik Sense, it is recommended to provide a password for the PostgreSQL database.
Should you forget this password, there's no way to retrieve it; however, it is possible to reset the password by following the steps below.
If after the procedure was completed errors are logged of a wrong password, see Qliksenserepository password error in the event logs after changing the database service user password for further information.
Resolution:
1. Stop the service called: "Qlik Sense Repository Database" (note: this will stop most Qlik Sense Services, as seen below)
Before:
After:
2. Open Notepad as an administrator (right-click on Notepad and select Run as Administrator)
3. From Notepad, click File > Open
4. Browse to C:\ProgramData\Qlik\Sense\Repository\PostgreSQL\9.6 (if "9.6" cannot be found then it's likely "12.5" which is what newer Qlik versions use)
5. Click on All Files (*.*) in the lower right corner dropdown of the dialog box
Note: Backup the files pg_hba.conf and postgresql.conf before making any changes!
5. Open the file pg_hba.conf
6. Change the METHOD from "md5" to "trust" in the both the IPv4 and IPv6 entries. There are four methods listed in total - only the first two need to be changed to trust:
Original:# TYPE DATABASE USER ADDRESS METHOD # IPv4 local connections: host all all 127.0.0.1/32 md5 # IPv6 local connections: host all all ::1/128 md5
Updated:
# TYPE DATABASE USER ADDRESS METHOD # IPv4 local connections: host all all 127.0.0.1/32 trust # IPv6 local connections: host all all ::1/128 trust
NOTE: "trust" must be lowercase!
If you use uppercase ("TRUST"), then the service will not start and attempts to make file backups will result in 0kb files when attempting to generate a database dump/backup.
7. Save the file and close it.
8. Start the service called: "Qlik Sense Repository Database" (example below). If this happens to start other dependent services as well, that is fine.
9. Launch a Command Prompt and go to C:\"Program files"\Qlik\Sense\Repository\PostgreSQL\9.6\bincd "C:\Program files\Qlik\Sense\Repository\PostgreSQL\9.6\bin"
10. Enter the following command
psql -h 127.0.0.1 -p 4432 -U postgres
The following message should be displayed
11. From the postgres=# prompt, enter the following commands:
11a. Adjust the postgres user account (you must include the single quotes and the semicolon):alter user postgres with encrypted password 'EnterYourNewPasswordHere';
The following message should be displayed
11b. If v3.0.x and above, you must also adjust the qliksenserepository user account (you must include the single quotes and the semicolon):alter user qliksenserepository with encrypted password 'EnterYourNewPasswordHere';
The following message should be displayed
The passwords must be the same for both, when making changes. Once complete, close the Command Prompt window.
12. The following is required after changing the database passwords for postgres and/or qliksenserepository. You must inspect the Connection String afterwards to change the password listed inside the file(s) below to the new password that was just created. Also, verify that the rest of the entire connection string is correct. The settings in these files are what the Qlik Sense Repository Service uses to connect to the underlying database. Navigate to the following and update as needed:
When opening the Connection String Editor -- Right Click and RUN AS Administrator (or the changes will not save)
C:\Program Files\Qlik\Sense\Repository\Util\QlikSenseUtil13. Stop the Qlik Sense Repository Database service, and the "Repository Service" as well if it's also running (so they appear as below)
or
C:\Program Files\Qlik\Sense\Repository\Util\ConnectionStringEditor
NOTE 2: If using version 2.4 or older, these files may not exist, and as such no editing is required.
14. Navigate to C:\ProgramData\Qlik\Sense\Repository\PostgreSQL\9.6 and open pg_hba.conf and change the METHOD back from "trust" to "md5"
15. Start all Qlik services. It's common practice to start the "Repository Database", "Repository Service", and the "Service Dispatcher" services first before starting other services.
16. Download and open pgAdmin4 in order to connect to the Qlik database locally in order to verify that the password has been updated successfully.For instructions, refer to articles:- How To Connect To And Modify The PostgreSQL Database Used For Qlik Sense Repository
- Installing and Configuring PGAdmin 4 to access the PostgreSQL database used by Qlik Sense or NPrinting
Verification must be done to ensure the password was updated successfully (in both the command prompt and within the connection string of those files) before moving forward or else Qlik will begin to have issues and future upgrades will fail.
NOTE: after the change is made, you may find you cannot get into the QMC. Please review this Article Qliksenserepository password error in the event logs after changing the database service user password for the possible solution.Related content:
- Qliksenserepository password error in the event logs after changing the database service user password
- How To Connect To And Modify The PostgreSQL Database Used For Qlik Sense Repository
- Installing and Configuring PGAdmin 4 to access the PostgreSQL database used by Qlik Sense or NPrinting
-
Qlik QDBC Connector package from QlikView got installed on a Qlik Sense system b...
Qlik Sense comes with a bundled version of the ODBC connector packages. There is no need to install the Qlik ODBC connector package in those installat... Show MoreQlik Sense comes with a bundled version of the ODBC connector packages. There is no need to install the Qlik ODBC connector package in those installations. If that happened here is a way to role back the change.
The Qlik Sense bundled drivers are installed in
C:\Program Files\Common Files\Qlik\Custom Data\QvOdbcConnectorPackage\
while the Qlik ODBC package is installed in
C:\Program Files\Common Files\QlikTech\Custom Data\QvODBCConnectorPackage\
Environment
- Qlik Sense all versions
Resolution
1.) Uninstall the Qlik ODBC connector package for Qlikview.
2.) The Registry entries under
Computer\HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI
and
Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Qlik
need to point to the correct folder for the drivers again. Otherwise you will see the message [Error IM002][Microsoft][ODBC Driver Manager] Data Source name not found and no default driver specified.
3.) Open Windows Explorer and navigate to
C:\ProgramData\Package Cache\
4.) Search for ODBC.msi.
5.) Right click the ODBC.msi and select "Repair".
6.) When completed all is fixed and you can continue work, no service restart is needed.
Cause
Qlik Sense comes with the ODBC connector Package bundled in the installation. If you install the Qlik ODBC package on top of it you will have two issues.
- an upgrade might not replace the internal drivers, since the checksums are different
- your configuration files for e.g. Oracle need to be added in different folders.
-
Qlik Sense May 2021 - Upgrade bundled PostgreSQL to 12.5 version
Starting at Qlik Sense Enterprise on Windows May 2021, the bundled PostgreSQL has been upgraded to version 12.5. However, PostgreSQL 12.5 will only b... Show MoreStarting at Qlik Sense Enterprise on Windows May 2021, the bundled PostgreSQL has been upgraded to version 12.5.
However, PostgreSQL 12.5 will only be deployed during a fresh installation of Qlik Sense Enterprise on Windows May 2021. This means that, if you are upgrading to Qlik Sense Enterprise on Windows May 2021, PostgreSQL will remain on 9.6.
For many organizations, running on the latest supported PostgreSQL version is a security requirement. To achieve this:
- Move Qlik Sense database to standalone PostgresSQL database in which case you will have control of which PostgreSQL version is deployed as long as it is supported by the system requirements
- Continue to use the bundled PostgreSQL with version 12.5 (see the process below)
Environment
- Qlik Sense Enterprise on Windows May 2021
The information in this article is provided as-is and to be used at your own discretion. Depending on the tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.
This process requires a full reinstallation of the product. Despite the fact that a backup is being taken as part of the process, it is highly recommended to have a second backup plan in place such as a Virtual Machine snapshot and/or a Server Backup.
It is highly recommended to test this process in a test environment to get familiar with it.
Step 1: Upgrading your Qlik Sense site
The first step is to upgrade your environment to Qlik Sense Enterprise on Windows May 2021 following the steps and recommendations provided on this link.
Once the upgrade is done make sure your Qlik Sense site is completely operational.
Step 2: Backing up your Qlik Sense site
At this point, you have an operational Qlik Sense site running on Qlik Sense Enterprise on Windows May 2021, however, the bundled PostgreSQL is still on version 9.6.
The next step will be to backup your entire Qlik Sense site following these instructions (including the certificates) . The backup is essential as it will need to be restored later on.
Step 3: Uninstall Qlik Sense on the central node
You will now need to uninstall Qlik Sense from the central node following these instructions.
During the uninstall:
- Have all rim nodes stopped.
- Don't check the box Remove Qlik Sense demo apps, certificates, and data folders.
- After uninstall and before reinstall rename C:\programdata\qlik to C:\programdata\qlik.old and rename C:\program files\qlik to C:\program files\qlik.old (if you changed it on another drive adjust accordingly)
Step 4: Install Qlik Sense Enterprise on Windows May 2021 on the central node
Now that Qlik Sense Enterprise on Windows May 2021 is uninstalled on the central node, you will reinstall it as a result will deploy a bundled PostgreSQL 12.5. Instructions available here.
- select the same host name and database settings as before
- The file share can point at a temporary one since you will take the original one again after restoring the QSR database in step 5.
Do not check the box Start theQlik Senseservices when the setup is complete at the end of the installer.
Step 5: Restoring your Qlik Sense site
You now have a clean Qlik Sense Enterprise on Windows May 2021 deployed on your central node with PostgreSQL 12.5. You now need to restore your environment to retrieve all your configurations and apps.
Please follow the Restoring a Qlik Sense site (including the certificates).
Note: While restoring the database backup you will most likely receive the following error which can be ignored. This error is caused by the version discrepancies between the pg_dump that made the dump file and the pg_restore that's restoring it - in example files coming from PostgreSQL version 9.6 vs files coming from PostgreSQL version 12.5. QSR backup taken and restored with the same version of PostgreSQL will not produce that error.
pg_restore: while PROCESSING TOC: pg_restore: from TOC entry 3; 2615 2200 SCHEMA public postgres pg_restore: error: could not execute query: ERROR: schema "public" already exists Command was: CREATE SCHEMA public; pg_restore: warning: errors ignored on restore: 1Once the environment is restored, you should be able to start your central node and rim nodes successfully.
-
Qlik Sense : Reloads failing with error "General Script Error in statement handl...
Many reloads are failing on different schedulers with General Script Error in statement handling. The issue is intermittent.The connection to the data... Show MoreMany reloads are failing on different schedulers with General Script Error in statement handling. The issue is intermittent.
The connection to the data source goes through our Qlik ODBC connectors.The Script Execution log shows a “General Script Error in statement handling” message. Nevertheless, the same execution works if restarted after some time without modifying the script that looks correct.
The engine logs show:
CGenericConnection: ConnectNamedPipe(\\.\pipe\data_92df2738fd3c0b01ac1255f255602e9c16830039.pip) set error code 232
Environment
- Qlik Sense Enterprise on Windows May 2021
- Qlik Sense Enterprise on Windows September 2020 and higher (less frequent occurrence)
Resolution
Change the reading strategy within the current connector:
Set reading-strategy value=engine in C:\ProgramFiles\Common Files\Qlik\CustomData\QvOdbcConnectorPackage\QvODBCConnectorPackage.exe.config
reading-strategy value=connector should be removed.Internal Investigation ID(s):
QB-8346
-
Qlik Sense Enterprise SaaS: Third-party extensions cannot be exported
Third-party extensions cannot be exported in Qlik Sense Business/Qlik Sense Enterprise SaaS. Environments: Qlik Sense Business Qlik Sense Enterprise... Show More -
What does each CQvXmlInterfaceRequestHandler mean?
CQvXmlInterfaceRequestHandler is normally seen when the QlikView Management Service sends admin commands to the QlikView Server. Example message: ... Show MoreCQvXmlInterfaceRequestHandler is normally seen when the QlikView Management Service sends admin commands to the QlikView Server.
Example message:
300 Error SE_LOG: CQvXmlInterfaceRequestHandler - Catch: errId(-3). Ino(-1). Fno(90)"
Fno(xx) narrows down what a particular message each error indicates.
Environment:
We can provide a list of possible message IDs and their meanings. Based on the ID received, additional troubleshooting may be necessary.Note that this article does not include solutions to varying issues related to the listed IDs.
10: GetVersionCrc
11: AbortAll
12: GetProgress
13: HasHTMLExport
14: OpenDoc
15: QvVersion
16: OSVersion
17: OSName
18: QTProduct
21: SheetWidth_OBSOLETE
22: SheetHeight_OBSOLETE
23: GetDocList
26: GetTextSize
27: GetProperties
39: GetMajorVersionDate
40: OpenDocEx
41: GetEmptyRect
42: GetSolidColorArea
44: CustomProtocolRequest
60: AcquireUserCal
61: AcquireSessionCal
65: XmlUpdate
67: GetEfsToc
69: ReadEfsFile
71: LoadNewerDoc
72: GetProcessId
73: GetDocListEx
74: GetQvAdminData_OBSOLETE
75: AddGroupUser_OBSOLETE
76: DeleteGroupUser_OBSOLETE
77: CreateGroup_OBSOLETE
78: SetQvAdminGroup_OBSOLETE
85: GetTicket
86: GetTicketForMe
87: GetInteract
88: InteractDone
89: GetPendingRequestIds
90: GetAdminDocList
91: GetAdminDocListForUser
92: GetPhysicalPath
93: FileUpload
94: FileDownload
95: FileRead
96: FileWrite
97: FileClose
98: FileCloseWithMetaData
99: GetDocumentMetaData
100: GetAuthenticatedUser
101: GetSupportInfo
102: GetDocPathsForSource
103: GetCollaborationData
104: SetDocumentMetaData
105: GetIniInfo
106: GetDocumentThumbNail
107: SetFavoriteForUser
108: SetLicense
109: GetDocumentInfo
110: GetDocumentThumbNailEx
111: GetUserDisplayNames
112: GetServerStatus
113: DeleteCollaborationObject
114: TakeCollaborationObject
115: UpdateLef
116: getQVPXImages
117: GetXmlLayout_OBSOLETE
118: GetCollaborationMetaDataForUser
119: GetCalInfoForUser
120: CustomProtocol2Request
121: FileFetch
122: RemoveLicense
123: InstallArchive
124: InstallArchiveFromFile
125: GetExtensionInfo
126: RemoveExtension
127: GetFolderNodes
128: RemoveLastKnownState
129: GetUtilizationData
130: GetServerId
131: ExtensionUpload
132: CloseAndInstallExtension
133: GetDocumentExtensionInfo
134: GetAdminDocumentThumbNail
139: GetWebServerName
142: GetInterfaceLanguage -
Deprecation of basic authentication for Exchange Online and Gmail
Google and Microsoft announced basic authentication deprecation for their email services in 2022. Both companies consider Basic authentication as an ... Show More
