Inline load is a type of data load that can be used when you do not have a data source. The data can be entered manually or by using the wizard within QlikView or Qlik Sense.Here are a couple of key things to remember:Data entered through aninlineclause must be enclosed by square brackets [ ]The columns of the table are defined in the first line of the inline load statement, separated by comas.A semi colon (;) is required after the closing square bracketHere is an example of a simple table using inline load:Load * Inline [ID, Name, Age, Title1, Wladimir, 31, Team Lead2, Paul, 22, Technical Support Engineer3, Mark, 40, Customer Support Engineer4, Janne, 27, IT Specialist];In the example inline load above, the columns for the table are:IDNameAgeTitleThe values starting with 1, are all data values that will populate the table. After loading the data, and looking at the data model, it will look like a normal table:As with any other dataset, two inline load tables can be created and be connected as can be seen below. You can also connect your inline table to an already existing table:Inline load using the QlikView wizard:You can accomplish the same result using the QlikView Inline load wizard.In the edit script window, go to Insert > Load Statement > Load Inline. A window with a structure of a table will appear. Select the top cell indicated by F1 and change its value to the name of your first column. Repeat for the other columns of your inline table. Insert the data to create the table. At the end, your table should look like this:Inline load using the Qlik Sense wizard:In the Data Manager, select the ‘Manual Entry’ option located at the top left corner of the screen.Add the table name and double click on Field_1 cell to insert your first column name. Repeat this for the other columns. After naming all the columns, you should have something like in the image below.Note that at the bottom right you can see the column count = 4 and number of rows = 0. If you Insert data, you see that the number of rows is no longer 0.For more detail information on using the Qlik Sense Wizard, please see the following resources:Manual Entry - Qlik SenseInline load DelimiterPlease give this post a like if you found it helpful! Also please let us know if you have any questions or leave your feedback in the comments.
...View More
Security rules are a very powerful tool intended to assist Qlik Sense administrators in managing access to Qlik Sense features. Using these rules administrators can manage not just the ability to access or create applications, but almost all features of the product including data connections, streams and even individual components of the Qlik Management Console.This allows administrators to define user and administration roles that closely match unique organization needs rather than merely rely on an out of the box user model. Unfortunately, this comes at a cost, when a user logs in and access the system all the relevant security rules need to be evaluated to determine the correct access rights for the current screen. This can potentially lead to long load times and poor performance in especially in large systems.This blog post will cover a summary of the default user model Qlik Sense ships with, along with a closer examination of how security rules are evaluated and cached and in doing so will provide a framework for administrators to ensure that they can get the most out of their system and where possible reduce unintentional performance impacts on users.The Default Security RulesQlik Sense ships with a set of default security rules that provide a set of administration roles and user roles for organisations to use and modify to meet their own needs.When you first log in to a fresh installation of Qlik Sense September 2019, you will be greeted with a set of 63 rules which all work together to create that initial user model.Broadly speaking the model works as follows (Items in bold are unchangeable rules and are always true):In the Hub:Users can create appsOwners of items can see those itemsIf you can read a stream, you can read all apps published to the streamIf you can read an app, you can read the content of that appIf you can update an app, you can update the contents of that appIf you can read an unpublished app, you can create objects that are part of that appIf you can read a published app, you can create some objects on that appEveryone has access to the Everyone StreamAdministrators have access to the Monitoring Apps streamIn the QMC:The Service Account has access to everythingThe Root Administrator has access to everythingAudit Administrators have access to audit sections and entitiesContent Administrators have access to content section, entities and connection related security rulesDeployment Administrators have access to deployment sections and entities, apps (to manage load balancing) and license rulesSecurity Administrators have access to security sections and entitiesAs you can see there are two separate contexts for rules and that, for the most part administrator roles only impact the Qlik Management Console. This is because all administration activity is expected to happen in the QMC and not the hub. It is possible, and in some cases desirable, for your root administrator to operate entirely without an assigned license, though this does prevent them from using the monitoring applications.One final note about Security rules in Qlik Sense. Rules can only ever add privileges, they can never take them away. This is very important when trying to design your access model, try to think in terms of what users can do, rather than in terms of what they cannot do and the structure of your rules will flow much more easily.Security Rule EvaluationLet’s go a little deeper and focus on how the system evaluates security rules.Every rule has a set of attributes that are used during evaluations:Context (Does this rule apply in the QMC, the Hub or both)Resource Filter (The parts of the system the rule covers)Actions (The privileges the rule grants)Conditions (The set of conditions that ultimately determine who is granted the actions)A Score (A dynamically generated metric indicating how useful the rule is, the moreWhen a user accesses Qlik Sense, the system pulls only rules relevant to the current context and resources (for example, when accessing the hub, we need to evaluate the rules for a user’s apps, as well as the rules for streams).The system also will retrieve the relevant resources (in this case all published applications, applications the user owns, and all streams) to evaluate the combination of rules, user and resources.It runs through these rules in score order, these scores are stored in the Security Rule Evaluation Cache, which means the values are lost every time the system restarts.Once it has finished evaluating the rules, the results are stored in the Security Rule Evaluation Cache so that the next time this user accesses the same page the system can simply retrieve the result of the calculation from the cache and if necessary only evaluate the rules on new apps.The system will exit the evaluation early if the user has already been granted all the requested rights to a resource. For example: the Root Administrator will generally find most of the QMC will load very quickly because the rule evaluation is very simple. One of the oldest rules in any given system grants them all privileges on everything in the QMC, that rule will generally score very highly and so be evaluated very early and then there is no need to perform any further checking as rules can only add and not take away.On the other hand, a limited user who has been granted access to only the Apps Section of the QMC and a small handful of applications may find that interface takes a very long time to load as the system will first need to evaluate enough rules to give access to the requested resource (i.e. in a system that has no previous evaluation, the less access the user has the longer the evaluation will take.Finally, security rule evaluation takes place on a mixture of different nodes, some evaluations will take place on the node with the proxy you are connecting to, some will take place on the node with the engine, and some will take place on the central node.In the case of QMC activity, all evaluations will take place on the central node, and in the case of very limited users, those evaluations will be relatively lengthy and complex. On larger systems this can lead to a rather slow user experience on the QMC. In extreme cases this can lead to the system becoming effectively unavailable on all nodes. In Qlik Sense September 2019 we have implemented 2 feature flags to increase the scalability of the QMC at the cost of reduced functionality.See How to enable QMC Limits in Qlik Sense (KB 000086580) for details.The Security Rule Evaluation CacheIn the previous section I mentioned the Security Rule Evaluation Cache Cache, which stores the results of security rule evaluations in order to speed up future access. Why then does your system seem to slow down as more users and apps are added? After all the cache should eventually cover all common system activities.The cache is deliberately designed to be short lived, there are many circumstances under which it is either partially or wholly invalidated or discarded, meaning that a full rules evaluation needs to be completed once more. This is broadly because we need to know that the cache is correct, anytime something changes (or could have changed) we cannot rely on the cache and must revert to the slower approach.There are basically 3 types of cache invalidation:Complete invalidation (The entire cache is discarded)User based invalidationEntity based invalidation1. Complete InvalidationAny change to security rules, load balancing or license rulesAny change to custom properties values (i.e. the potential possible values a custom property can be assigned)When the system is restarted (the cache is never written to disk, and rules may have changed since the service last started)If the connection to the database is disruptedAs these changes could have a wide ranging impact, the entire cache is discarded and must be rebuilt by user behavior.When this cache is invalidated, the scores for every rule are also reset to 0, meaning that we also lose any optimizations gained since the session started.2. User based invalidationAny change to a user attribute (e.g. the user is added or removed from a group)A user’s custom properties are changedRules are often based on user attributes, changes to that user mean we cannot ensure the cached results still apply, so we discard the cached results linked to that user.3. Entity based invalidationAlmost any change to a single entity. (e.g. changing an app’s name or owner, approving a community sheet, renaming a data connection)Much like with users, changing any aspect an entity or resource that can be addressed in a rule means that we cannot ensure the cached results still apply for that entity, so we discard the cached results linked to it.In Qlik Sense February 2019, additional logging was implemented to record when a cache invalidation occurs along with its type (Complete, User, Entity)Recommendations/Best Practices:If you want to provide the best performance for your users try to make rule changes near scheduled downtime, when the cache is already going to be invalidated due to a service restart.To reduce the number of evaluations that need to take place, try to ensure you have a regular clean up process where you export and delete old apps that are no longer in use.Limited users in the QMC should also have a very limited scope, i.e. only get access to one or two sections. Where possible try to centralise QMC activities to a smaller number of administrators rather than expand access to the entire user base. In a large organization it is better to have a small number of administrators and a ticket system than to grant every developer access to the QMC for small scale task management.
...View More
QlikView is installed with the AccessPoint (http://YourWebServer/qlikview/index.htm) and Management Console (http://YourQMCServer:4780/qmc/) accessible through HTTP only. Understandably, you might want to switch these over to HTTPS.
If you’ve just installed Qlik Sense Enterprise, then this image probably looks familiar. Alternatively, Chrome might display The site's security certificate is not trusted, while Firefox may report This Connection is Untrusted.By default, Qlik Sense uses a self-signed certificate to enable HTTPS access across both the Hub (https:// YourSenseServer/hub) and the Management Console (https://YourSenseServer/qmc). But self-signed certificates cannot be validated or trusted by web browsers and tend to prompt a warning message.That's alright though. All we need is the following:An additional (signed) certificate.A brief (5 minute) downtime of the Qlik Sense Proxy.And, I suppose, this blog post for the instructions.So, let’s get started.What is the current certificate used for?During the initial install, the Qlik Sense Repository Service creates a set of certificates. Their purpose is to:Secure Service Communication and Service Authentication.Qlik Sense uses certificates to authenticate its service across all nodes. See the Qlik Sense Online Help for details. In addition, other products (such as Qlik NPrinting) require these certificates to be establish a connection.HTTPS enabled access for the Hub the QMC.Note: We will not modify, replace, or remove the originally created certificates. Doing so will break service communication.What we’ll do instead is to add an additional one.Certificate options, or: What type of certificate is right for me?There are three possible types of certificates for us to use.A certificate purchased from and signed by a trusted CA (Certificate Authority) such as VeriSign, Thawte, Geotrust, etc.A certificate provided and signed by your own Enterprise CA (Certificate Authority).And, of course, a self-signed one. Those can be created by any number of applications, such as Microsoft IIS, but is generally only recommended for test environments. More so, it has the potential side effect of teaching users to ignore browser warnings, which we do not want.Requirements, or: What to look out for when getting your cert.The certificate itself must contain a private keyHas an up to date valid from / valid to date rangeIs signed by a valid and OS or browser configured Certificate AuthorityWhen support gets questions, they are most often related to a certificate missing the private key. Always verify the certificate comes bundled with one when you install it.It’ll look like this:As far as formats and algorithms are concerned, the following are confirmed to work with Qlik Sense:Certificates that are x509 version 3Use signature algorithm sha256RSAUse signature hash algorithm sha256Where to get a certificate and how to do a CSR?The Certificate Authority you chose will have instructions for this, and if you are looking to get a self-signed one or one from your corporation's CA, then a local administrator can provide the certificate to you.Either way, you are going to need to generate a Certificate Signing Request (CSR) to pass on to your CA. There are tools out there to get that done with, such as certreq from Microsoft (found here), and SSLhopper has a great article on that, which I often send to customers when they ask us about CSRs and how to do them.Once you obtain the certificate, we'll move on to installing it and activating it in Qlik Sense. This will be done in three quick steps:ImportGet the ThumbprintProvide Thumbprint to the ProxyImporting the CertificateAs mentioned before, we are not replacing certificates. The already existing ones will not be deleted. Doing so would break service authentication between the individual Qlik Sense services and render the system… broken.Step 1: On the Qlik Sense node running the Qlik Sense Proxy, log on with the user running the Sense services. This is important since the certificate needs to be accessible for this account.Step 2:If the certificate was saved in the .pfx format, then all you need to do is double click the file. Follow the prompt to import the certificate into the Personal store.Longer Step 2:If you want to import it manually or verify if it was correctly installed, then we'll need to do a little more work.Launch the Microsoft Management Console (mmc.exe) on the Proxy nodeIn the MMC, go to File > Add / Remove Snap-in...Select Certificates and click AddSelect Computer account, click Next, select Local computer and click FinishIn the MMC, go to Certificates (Local Computer)/PersonalIn the MMC, go to Actions > All Tasks > Import...Browse to the certificate file provided to you from your CAFollow the instructions on the screen to import the certificate, including the private keyVerify the new certificate has been imported into Certificates (Local Computer) > Personal > Certificates and that it contains a private keyDouble-click the Certificate > Certification Path and confirm it shows "This certificate is OK"Getting the ThumbprintWell, since we are already in the MMC, let's open the freshly installed certificate again.Switch to the Details tab and scroll down until you find ThumbprintMark the entire thing and copy it into, for example, Notepad.Configuring the Qlik Sense ProxyAlmost done!Open the Qlik Sense Management Console (QMC)Go to ProxiesDouble click the Proxy you want to use (or select and choose Edit)Enable the Security options in the Properties panel on the rightPaste the certificate Thumbprint into the SSL browser certificate thumbprint text box.Click Apply.The Sense Proxy will now restart. During the restart, it will be using Windows API calls to correctly bind the new certificate to its SSL ports.Verification, or: How to prove the certificate was accepted.In the web browser:When opening the Qlik Sense Hub or QMC, the certificate will now be displayed in the browser. This may look different depending on the web browser, but in Google Chrome you can click the padlock to the left of the URL to verify what certificate is used.The information displayed needs to match the properties of the certificate you installed.In the log files:If you’d rather see what the Qlik Sense Proxy service is doing, then you can directly check up on that, too.On the Proxy node, go to C:\ProgramData\Qlik\Sense\Log\Proxy\Trace and open the Security log file from just after the last start.It will now print a slightly different message than before:Security.Proxy.Qlik.Sense.Common.Security.Cryptography.LoggingDigester DOMAIN\_service Setting crypto key for log file secure signing: successSecurity.Proxy.Qlik.Sense.Common.Security.Cryptography.SecretsKey DOMAIN\_service retrieving symmetric key from cert: successSecurity.Proxy.Qlik.Sense.Common.Security.Cryptography.CryptoKey DOMAIN\_service setting crypto key: successSecurity.Proxy.Qlik.Sense.Communication.Security.CertSetup 'CN=localhost' (08C871933A58E072FED7AD65E2DB6D5AD3EAF9FA) as SSL certificate presented to browser, which is a 3rd party SSL certificateAnd that's it!There isn't much more to it in a standard Qlik Sense Enterprise installation, but if you have more questions, then maybe a few of these articles can help:I applied my certificate and it seems to be using it correctly, but browsers are still saying the Common Name is Invalid?ERR_CERT_COMMON_NAME_INVALID when using 3rd party certificateQlik Sense keeps reverting to the default and complains it can't find a valid ssl certificate with the thumbprint.Qlik Sense: Couldn't find a valid ssl certificate with thumbprint in Proxy logs, the third party certificate is not used correctlyThe certificate may not have a Private key or the service account does not have access to it.How to: Manage Certificate Private KeyThe Qlik Sense Service account doesn't have admin privileges and the certificate is not accepted.How to: Change the Qlik Sense Proxy certificate if the service account does not have local administrative permissionsI hope that this was useful 😊 Stay tuned for an upcoming post where we’ll focus on QlikView and how to enable HTTPS for its AccessPoint, and don’t forget to subscribe to this blog for more content delivered by #QlikSupport. We’ll be watching for your comments and questions and we’ll to get back to you as soon as possible. Your feedback is always appreciated.
...View More
Hi everyone,
For various and valid reasons, you might need to migrate your entire Qlik Sense environment, or part of it, somewhere else.
In this post, I’ll cover the most common scenario: a complete migration of a single or multi-node Qlik Sense system, with the bundled PostgreSQLdatabase (Qlik Sense Repository Database service) in a new environment.
So, how do we do that?
Introduction and preparationBackup your old environmentDeploy and restore the new central environmentWhat about my rim nodes? Finalizing your migrationData ConnectionLicensingBest practices
If direct assistance is needed and you require hands-on help with a migration, engage Qlik Consulting. Qlik Support cannot provide walk-through assistance with server migrations outside of a post-installation and migration completion break/fix scenario.
Introduction and preparation
Let’s start with a little bit of context: Say that we are running a 3 nodes Qlik Sense environment (Central node / Proxy-Engine node / Scheduler node).
On the central node, I also have the Qlik shared folder and the bundled Qlik Sense Repository Database installed.
If you have previouslyunbundledyour PostgreSQL install,seeHow To migrate a Qlik Sense Enterprise on Windows environment to a different host after unbundling PostgreSQLfor instructions on how to migrate.
This environment has been running well for years but I now need to move it to a brand new hardware ensuring better performance. It’s not possible to reinstall everything from scratch because the system has been heavily used and customized already. Redoing all of that to replicate the environment is too difficult and time-consuming.
I start off with going through a checklist to verify if the new system I’m migrating to is up to it:
Do I meet the system requirements? Qlik Sense System Requirements
Am I following virtualization best practices? Virtualization Best Practices In QlikView And Qlik Sense
And then I move right over to…
Backup your old environment
The first step to migrate your environment in this scenario is to back it up.
To do that, I would recommend following the steps documented on help.qlik.com (make sure to select your Qlik Sense version top left of the screen).
https://help.qlik.com/en-US/sense-admin/May2024/Subsystems/DeployAdministerQSE/Content/Sense_DeployAdminister/QSEoW/Deploy_QSEoW/Backing-up-a-site.htm
Once the backup is done you should have:
A backup of the database in .tar format
A backup of the content of the file share which includes your applications, application content, archived logs, extensions,…
Backups of any data source files that need to be migrated and are not stored in the shared folder like QVDs
Then we can go ahead and…
Deploy and restore the new central environment
The next steps are to deploy and restore your central node. In this scenario, we will also assume that the new central node will have a different name than the original one (just to make things a bit more complicated 😊).
Let’s start by installing Qlik Sense on the central node. That’s as straightforward as any other fresh install.
You can follow our documentation.Before clicking on Install simply uncheck the box “Start the Qlik Sense services when the setup is complete.”
The version of Qlik Sense you are going to install MUST be the same as the one the backup is taken on.
Now that Qlik Sense is deployed you can restore the backup you have taken earlier into your new Qlik Sense central node following Restoring a Qlik Sense site.
Since the central node server name has also changed, you need to run a Bootstrap command to update Qlik Sense with the new server name. Instruction are provided in Restoring a Qlik Sense site to a machine with a different hostname.
The central node is now almost ready to start.
If you have changed the Qlik Share location, then the UNC path has also changed and needs to be updated.
To do that:
Go to C:\Program Files\Qlik\Sense\Repository\Util\QlikSenseUtil
Run QlikSenseUtil.exe as Administrator
Click on Connect to the database and enter the credential to connect to the new PostgreSQL database
Click on Service Cluster and press OK. This should display the previously configured UNC Path
You simply need to update each path, save and start/restart all the Qlik sense services.
At this point make sure you can access the Qlik Sense QMC and Hub on the central node. Eventually, check that you can load applications (using the central node engine of course). You can also check in the QMC > Service Cluster that the changes you previously made have been correctly applied.
Troubleshooting tips:If after starting the Qlik Sense services, you cannot access the QMC and/or Hub please check the following knowledge article How to troubleshoot issue to access QMC and HUB
What about my rim nodes?
You’ve made it here?! Then congratulations you have passed the most difficult part.
If you had already run and configured rim nodes in your environment that you now need to migrate as well, you might not want to remove them from Qlik Sense to add the new ones since you will lose pretty much all the configuration you have done so far on these rim nodes.
By applying the following few steps I will show you how to connect to your “new” rim node(s) and keep the configuration of the “old” one(s).
Let’s start by installing Qlik Sense on each rim node like it was a new one.
The process is pretty much the same as installing a central node except that instead of choosing “Create Cluster”, you need to select “Join Cluster”
Detailed instructions can be found on help.qlik.com:Installing Qlik Sense in a multi-node site
Once Qlik Sense is installed on your future rim node(s) and the services are started, we will need to connect to the “new” Qlik Sense Repository Database and change the hostname of the “old” rim node(s) to the “new” one so that the central node can communicate with it.
To do that install PGAdmin4 and connect to the Qlik Sense Repository Database. Detailed instruction in Installing and Configuring PGAdmin 4 to access the PostgreSQL database used by Qlik Sense or NPrinting knowledgearticle.
Once connected navigate to Databases > QSR > Schemas > public > Tables
You need to edit the LocalConfigs and ServerNodeConfigurations table and change the Hostname of your rim node(s) from the old one to the new corresponding one (Don’t forget to Save the change)
LocalConfigs table
ServerNodeConfigurations table
Once this is done, you will need to restart all the services on the central node.
When you have access back, login to the QMC and go to Nodes. Your rim node(s) should display the following status, “The certificate has not been installed”
From this point, you can simply select the node, click on Redistribute and follow the instruction to deploy the certificates on your rim node. After a moment the status should change and you should see the services being up and running.
Do the same thing on the remaining rim node(s).
Troubleshooting tips: If the rim node status is not showing “The certificate has not been installed” it means that either the central node cannot reach the rim node or the rim node is not ready to receive new certificates.Check that the port 4444 is opened between the central and rim node and make sure the rim node is listening on port 4444 (netstat -aon in command prompt).Still no luck? You can completely uninstall Qlik Sense on the rim node and reinstall it.
Finalizing your migration
At this point, your environment is completely migrated and most of the stuff should work.
Data Connection
There is one thing to consider in this scenario. Since the Qlik Sense certificates between the old environment and the new one are not the same, it is likely that data connections with passwords will fail. This is because passwords are saved in the repository database with encryption. That encryption is based on a hash from the certs. When the Qlik Sense self-signed cert is rebuilt, this hash is no longer valid, and so the saved data connection passwords will fail. You will need to re-enter the passwords in each data connection and save. This can be done in the QMC -> Data Connections.
See knowledge article: Repository System Log Shows Error "Not possible to decrypt encrypted string in database"
Licensing
Do not forget to turn off your old Qlik Sense Environment once you are finished. While Qlik's Signed License key can be used across multiple environments, you will want to prevent accidental user assignments from the old environment.
Note: If you are still using a legacy key (tokens), the old environment mustbe shut down immediately, as you can only use a legacy license on one active Qlik Sense environment. Reach out to your account manager for more details.
Best practices
Finally, don’t forget to apply best practices in your new environment:
Qlik Sense Folder And Files To Exclude From AntiVirus Scanning
Recommended practice on configuration for Qlik Sense
...View More
This is now my second blog post! yeahhh!!!! And today I wanted to talk about a topic that is generating a lot of discussions in the Qlik Sense Deployment & Management space:
After installing Qlik Sense Enterprise, you probably have noticed that you have been provided with two applications respectively called Operation Monitor and License Monitor. But wait there are more…
Generally speaking, we call those applications Monitoring Applications
Today I would like to introduce you to those applications and how we setup and configure them.
When the QlikView AccessPoint starts showing No Server, or end users are reporting that they are being kicked out of their application while they’re working in them, it’s often the QlikView server (our QIX engine) that’s to blame.
It might have crashed. Or might have just run out of resources, ramping up RAM and CPU usage until the entire system eventually crashes.
.png "Former Employee"){kind=icon}
