Tuesday 18 February 2020

Nprinting- (ver -16) Server Certificate Update Process


Certificate Update Process for QLIK Nprinting

Import the certificate to QV Nprinting Server

1. Copy the certificate to the Server that will host QlikView Nprinting Server(.pfx format)
2. Launch an elevated Microsoft Management Console (MMC)


3. Add the certificates snap-in > Local Computer Account

4. Expand Certificates > Personal > Certificates
5. Right Click > All Tasks > import


6. Click Next


7. Browse to the certificate then click next

8. Type the password that was created when the certificate was created and click next



9. Import the certificate in the personal store and click next



10.  Review the information and click Finish





Pre-requisite steps

Generate the Thumbprint

1. From the MMC console double click on the certificate in the Personal store > select the details Tab and scroll down to thumbprint


2. Copy the thumbprint into Notepad > Find/Replace <space> replace with <no spaces>
a.    Example 21 7b 64 d2 3a… should become 217b64d23a
3. Download Guidgen.exe from Microsoft to generate a unique GUID
a.    http://www.microsoft.com/en-au/download/details.aspx?id=17252
4. Execute Guidgen.exe
a.    Create a registry format GUID


b.    Copy the GUID to Notepad

 

Bind http to SSL using netsh



1. Stop Nprinting Services
2.Launch command prompt

    Enter the following:-

3. netsh http delete sslcert ipport= 0.0.0.0:9000
4. netsh http delete sslcert ipport= 0.0.0.0:9001
5. 
netsh http add sslcert ipport=0.0.0.0:9000 certhash=0000000000003ed9cd0c315bbb6dc1c08da5e6 appid={00112233-4455-6677-8899-AABBCCDDEEFF}

netsh http add sslcert ipport=0.0.0.0:9001 certhash=0000000000003ed9cd0c315bbb6dc1c08da5e6 appid={00112233-4455-6677-8899-AABBCCDDEEFF}

a.    ipport parameter specifies the IP address and port (ensure the ipport = 0.0.0.0 (do not replace with the actual IP Address)
b.    certhash parameter specifies the thumbprint of the certificate (previously copied to notepad).
c.    appid parameter is the GUID (previously copied to notepad)


6. Start Nprinting Services.

Thursday 25 April 2019

How to clear the cache used by QlikView Server

How to clear the cache used by QlikView Server


Description
The cache is handled automatically by QlikView Server and should not be cleared manually. However in some situations there might be a reason to clear the cache on a regular basis, typically once a day.


Resolution
The first and obvious way to clear the cache is to restart the QlikView Server service. This can be donemanually or through a scheduled stop & start of the service using the command netstop & netstart.

net stop QlikViewServer
net start QlikViewServer

Another option is to add the following entry in the [Settings 7] entry in the settings.ini file for QlikView Server,located in %ProgramData%\QlikTech\QlikView Server:
[Settings 7]
ClearCacheTimesPerDay=1

The value set is the number of times per day the cache will be cleared, syncronized at midnight. A value of 1will clear the cache once per day at midnight.

Attention: Clearing the cache may lead to increased response times from the user's perspective. Use withcaution!

Qlikview Server Clustering

Qlikview Server Clustering

Prerequisites:

Following requirements have to be met before starting the QlikView Server cluster configuration:
  • A QlikView Server license allowing QlikView Server clustering.  That means that the QlikView Server LEF must consist of an entry “NUMBER_OF_CLUSTER_NODES;X;;” where X is 2 or higher
  • AccessPoint (based on IIS or QlikView Web Server), Management Service, Qlikview Distribution Service, Directory Service Connector are already installed on QlikView system in the network
  • A domain user to run the QlikView services on every machine is available.
  • A Windows Network-Share
  • All QlikView Server cluster nodes need read /write access to the following data centrally stored:
  • Server status files aka .pgo
  • QlikView Documents aka QVWs
  • QlikView Server Logs aka audit, session, performance, event logs

Step-by-Step Installation

1. Prepare the shared device:
Create folders for the files accessed by every QlikView Server cluster node.:
  • QvRoot for the .pgo-Files
  • QvMount for a Mounted Folder
  • QvLogs for the Logfiles
  • QvExtensions for the extensions
  • QvTemp for the temp files

2. Prepare the cluster nodes:

On each planned QlikView Server cluster node perform the following configuration
  •   Login as Administrator
  •   Turn Off Windows Firewall 
 User-added image
  •   Deactivate Internet Explorer Enhanced Security Configuration for Administrators
User-added image
  • Add Domain User the QlikView services should use to Local Administrators Group
  • Start QlikView Server setup and install feature “QlikView Server” only
NOTE: Do not install any other services such as QlikView Management Service on multiple nodes in the cluster!
User-added image
  • During the setup check that you will specify the QlikView service account later 
User-added image
  • Do not restart the system immediately after install is completed, but
      o   Add QlikView service user to “QlikView Administrator Group”
      o   Change Logon setting of the QlikView Server Service to the QlikView Service user
  • Restart the system

3. Configure QlikView Server-Cluster in QlikView Enterprise Management Console
  • Open QlikView Enterprise Management Console and register QlikView Server licence with activated cluster nodes
User-added image
  • Add first QlikView Server cluster node below QlikView Servers
User-added image
  • Switch Root,Mount,Logs,Extensions and Temp Folders to Shared Device folder paths using UNC-syntax (i.e \\Fileshare\QvRoot)
User-added image
  • Press Apply and let QlikView Enterprise Management Console initiate restart of QlikView Server service
  • Add each additional QlikView Server cluster node and activate them by entering control number of the registered QlikView Server license
User-added image
  • Press Apply and let QlikView Enterprise Management Console initiate restart of QlikView Server service on all nodes
  • Verify all QlikView Server nodes are running
User-added image
  • Configure Load Balancing feature (Functionality see QlikView Server Reference Manual)
User-added image

Thursday 1 November 2018

How to Change the Qlik Sense Service Account - Considerations when changing the Sense Service account



Work to Prepare for the activity:-


  • Record the Share Path:
    • Navigate in the QMC > Service Cluster and record the Root Folder
User-added image

Changing Qlik Sense dependencies

  • Stop all Qlik Sense services
  • Ensure permissions on the Program Files path (this should be provided by Local Administrator rights):
    • Navigate to the installation path (default: C:\Program Files\Qlik)
    • Select the Sense folder > Right Click > Properties > Security > Edit > Add
      • Lookup the new service account
      • Ensure that the account has Full control over this folder
  • Ensure permissions on the %ProgramData% path (this should be provided by Local Administrator rights):
    • Navigate to the installation path (default: C:\ProgramData\Qlik)
    • Select the Sense folder > Right Click > Properties > Security > Edit > Add
      • Lookup the new service account
      • Ensure that the account has Full control over this folder
  • Ensure access to the certificates used by Qlik Sense
    • Start > MMC > File > Add/Remove Snap-In > Certificates > Computer Account > Finish
      • Go into Certificates (Local Computer) > Personal > Certificates
      • For the Qlik CA server certificate
        • Select > Right Click > All Tasks > Manage Private Keys > Ensure that the new service account has control
      • If using a third party certificate, do the same
  • Ensure access to the Service Cluster path used by Qlik Sense
    • Start > Computer Management > Shared Folders > Shares > Select the Share path
    • Right click on the Share Path > Properties > Share Permissions > Add the new service account to have full control
    • Open Windows File Explorer and navigate to the folder (e.g. C:\Share) > Right click on the folder > Security > Edit > Add the new service account to have full control
  • Start the Qlik Sense Repository Database
  • Ensure that the AppImport folder used by Qlik Sense for staging app imports will be accessible by the new service account:
    • Open a connection to the QSR database:
      • Open a Command Prompt
      • C:\"Program Files"\Qlik\Sense\Repository\PostgreSQL\9.6\bin\psql.exe -h localhost -p 4432 -U postgres -d QSR -e
        • Note: If Qlik Sense is installed in another location, then the above path will need to be adjusted
        • Note: If using Qlik Sense 3.x Shared Persistence, then the above path may need to be adjusted to the 9.3 directory rather than the 9.6 directory
    • Type in \x to enable extended mode on the terminal
    • For builds X-Y: SELECT "AppImportFolder" FROM "RepositoryServiceSettings";
    • For builds X-Y: SELECT "Temporaryfilepath" FROM "ServerNodeConfigurations";
    • If this path points to a user's profile or other path which the new service account does not have access to, then run this style of command:
      • UPDATE "public"."RepositoryServiceSettings" SET "AppImportFolder"='C:\foo' WHERE "AppImportFolder"='C:\Users\....';
      • UPDATE "public"."ServerNodeConfigurations" SET "Temporaryfilepath"='C:\foo' WHERE "AppImportFolder"='C:\Users\....';
      • Note: Ideal location is a path like C:\ProgramData\Qlik\Sense\Apps which is not tied to an individual user's account
  • Ensure membership in the Local Groups that Qlik Sense requires:
    • Start > Computer Management
    • Navigate to Local Users and Groups > Local Groups
    • Add the new service account as a member of:
      • Administrators (if using this configuration option)
      • Performance Monitor Users
      • Qlik Sense Service Users
  • Now for all Qlik service (except the Qlik Sense Repository Database Service) swap the account over by using the windows services control panel
  • Start the remaining Qlik Sense Services
  • Access the QMC to validate functionality, preferably as a previously configured RootAdmin
  • Access the Data Connections section of the QMC
  • Toggle the User ID field and change the data connections used by the License and Operations Monitor apps to use the new user ID and password:
User-added image
  • Execute the License Monitor reload task
  • Add the RootAdmin role to the new service account
    • QMC > Users
    • Filter on the new UserID > Edit
    • Add RootAdmin role
  • Inspect the configured User Directory Connectors and change the User ID and password combination if previously configured

External Dependencies:

  • Go into the QMC > Data Connections section
  • Inspect all Folder data connections and determine all network shares that the service account needs access to. Either change them yourself or alert the necessary teams to provide both Share and NT level access to these shares.
  • Inspect all Data Connections and ensure that none use the old Service account and password
    • Follow up with necessary teams to provision access to data sources which used the old credentials.

Thursday 25 October 2018

QLIK-SENSE MOBILE APP CONFIGURATION

================================================



Description

  • This is the process of configuring Qlik Sense mobile app on iPad  / iPhone
  • The Qlik Sense Mobile app allows you to securely connect to your Qlik Sense Enterprise deployment from your supported mobile device
  • Environment
    • Qlik Sense September 2017 or later

Resolution

  1. Pre-requirements (Client side)
  • QS mobile app: supported devices running iOS 11 or later

       2. Pre-requirements (Server side)
  • Qlik Sense Enterprise Server September 2017 or later deployment.
  • (only for versions prior to September 2018) Qlik Sense license that is not a test license. Evaluation licenses and other special editions are fine. Support for test licenses was introduced in Qlik Sense Enterprise September 2018.


       3. Configuration (Server side)
  • Configure Proxy service listener port:  
    • *For this demonstration, port 80 is enabled so then client devices can access HUB over HTTP, however it's strongly reccomended to install 3rd party certificate and associate to the defaulth port - 443*
User-added image
  • Generate authentication link in QMC:
    • Generated link needs to be sent to the device where Qlik Sense mobile app will be used:
 User-added image
  • Associate User access pass(CAL):
    • Necessary CAL for QS mobile app
User-added image
 

       4. Configuration (Client side)
  • Install Qlik Sense mobile app from AppStore.
  • Provide authentication link generated in QMC
    • Open the link from your device (this can be also done by going to the Hub, clicking on the menu icon at the top right and selecting "Client Authentication"), the installed application will be triggered automatically, and the configuration parameters will be applied.
      •  Enter user credentials for QS server

Sunday 7 October 2018



QLIK Function


PEEK() VS PREVIOUS() FUNCTIONS

Introduction

Qlikview has many useful and sometimes underused functions that can make your life as a developer a lot easier.

Peek and Previous are two of those functions. The definitions below are taken from QlikView Help.

Previous (expression)

Returns the value of expression using data from the previous input record that was not discarded due to a where clause.

In the first record of an internal table the function will return NULL.

The previous function may be nested in order to access records further back.

Data are fetched directly from the input source, making it possible to refer also to fields which have not been loaded into QlikView, i.e. even if they have not been stored in its associative database.

Examples:

Load *, Sales / previous (Sales) as Increase from …;

Load A, previous (previous (A)) as B from …;

Peek (fieldname [ , row [ , table name ] ] )                                     

Returns the contents of the fieldname in the record specified by row in the internal table tabe name.

Data are fetched from the associative QlikView database.

Fieldname must be given as a string (e.g. a quoted literal).

Row must be an integer. 0 denotes the first record, 1 the second and so on.

Negative numbers indicate order from the end of the table. -1 denotes the last record read.

If no row is stated, -1 is assumed.

Table name is a table label, see Table Labels, without the ending colon. If no table name is stated, the current table is assumed.

If used outside the load statement or referring to another table, the table name must be included.

Examples:

Peek (‘Sales’)

Returns the value of Sales in the previous record read ( equivalent to previous(Sales) ).

Peek (‘Sales’, 2)

Returns the value of Sales from the third record read from the current internal table.

Peek (‘Sales’, -2)

Returns the value of Sales from the second last record read into the current internal table.

Peek ( ‘Sales’, 0, ‘Tab1’ )

Returns the value of Sales from the first record read into the input table labeled Tab1.

Load A, B, numsum (B, peek( ‘Bsum’ ) ) as Bsum…;

creates an accumulation of B in Bsum.

In its simplest form, Peek () and Previous () are used to identify specific values within a table. All the examples in this document are based upon the simple table below:

Set the Scene

Company ABC wants to be able to keep track of monthly employee totals.

Currently they only collect data for month, hires and terminations.

They would like to add fields for Employee Count and Employee Var to see the monthly difference in total employees.

TAB.

FUNCTIONS, FEATURES AND TRICKS USED

To create the Employee Count and the Emloyee Var fields in QlikView, the following QlikView functions, features and/or tricks were used:

Use of Peek() function
Use of Previous() function
Use of RecNo() function
This application assumes that the current date is April 1, 2013.

Peek () Function

As mentioned earlier, in its simplest form the peek() function lets you identify any value loaded for a defined field.

If(row no()=1, Hired-Terminated, peek([Employee Count], -1)+(Hired-Terminated)) as [Employee Count]

In this expression we first look to see if the rowno () is equal to 1. If it is equal to 1, no Employee Count will exist so we will just populate the field with the difference of Hired minus Terminated. If the rowno() is greater than 1, we know that there will be and Employee Count field so we will look at last month’s Employee Count and use that number to add to the difference of that month’s Hired minus Terminated employees. Notice too that in the Peek() function we are using a (-1). This tell QlikView to look at the record above the current record. If the (-1) isn’t specified, QlikView will assume that you want to look at the previous record.

Two important things to remember about the Peek () function are:

When using the Peek() function you should think about using the order by clause to ordering the file
When using the Peek() function, you can specify the exact row that you want to reference.
When using the Peek() function, you can reference a field that was not previously loaded. In this example, Employee Count was not part of the original file and we are creating the field on the fly in the script.
[Employees Init]:
LOAD
rowno() as Row,
Date(Date) as Date,
Hired,
Terminated,
If(rowno()=1, Hired-Terminated, peek([Employee Count], -1)+(Hired-Terminated)) as [Employee Count]

From
[Peek vs Previous.xlsx]
(ooxml, embedded labels);

After loading the file, you can see the Employee Count field was correctly calculated using the Peek() function.

TAB2

Previous () Function

As mentioned earlier, in its simplest form the Previous() function lets you identify the last value loaded for a defined field.

If(rowno()=1,0,[Employee Count]-Previous([Employee Count])) as [Employee Var]

In this expression we first look to see if the rowno() is equal to 1. If it is equal to 1, we know that there will be no Employee Var because there is no record for the previous month’s Employee Count so we simply enter 0 for the value. If the rowno() is greater than 1, we know that there will be and Employee Count field so we will look at last month’s Employee Count and use that number to subtract that number from the current month’s Employee Count to create a field named [Employee Var].

Two important things to remember about the Previous() function are:

When using the Previous() function you should think about using the order by clause to ordering the file.
You can only reference a previously loaded field when using the Previous() function.
Anytime that you use the Previous() function, you could also use Peek() instead.
[Employee Count]:
LOAD
Row,
Date,
Hired,
Terminated,
[Employee Count],
If(rowno()=1,0,[Employee Count]-Previous([Employee Count])) as [Employee Var]
Resident [Employees Init] Order By Row asc;

After loading the file, you can see the Employee Var field was correctly calculated using the Previous() function.

Tab3

Summary

Peek () and Previous () allow users to target defined rows within a table.

The biggest difference between the two functions is that the Peek() function allows the user to look into a field that was not previously loaded into the script whereas the Previous() function can only look into a previously loaded field. Previous() operates on the Input to the Load statement, whereas Peek() operates on the Output of the Load statement. (Same as the difference between RecNo() and RowNo().)  This means that the two functions will behave differently if you have a Where-clause.

So the previous () function would be better suited for when a user needs to show the current value versus the previous value. In the example we calculated the employee variance from month to month.

The peek () function would be better suited when the user is targeting either a field that has not been previously loaded into the table or if the user needs to target a specific row. This was shown in the example where we calculated the Employee Count by peeking into the previous month’s Employee Count and adding the difference between the hired and terminated employees for the current month. Remember that Employee Count was not a field in the original file.



TAB.
TAB2

Tab3

Thursday 27 September 2018

QlikView


Processing with QLIK


Issues With CPU in QLIK

--> Not able to utilize full performance of CPU

1) Description:-

The number of CPU affinity for the QVS does not match the CPU cores in the Server.

   Cause:-

This issue comes from a new BIOS setting introduced in the Generation 9 of HP servers.
A new setting has been added in BIOS that needs to be modified to allow the QlikView services properly detect all the CPU Cores in the machine.

  Resolution:-


This issue can be resolved by changing the BIOS setting "NUMA Group Size" to "Flat".
The default setting for "NUMA Group Size" disables QlikView from properly detecting all
CPU cores in the hardware, and thereby
From HPs manual on the subject: "Use this option to configure how the system ROM reports
  the number of logical processors in a NUMA (Non-Uniform Memory Access) node. The
  resulting information helps the operating system group processors for application use. To set             

NUMA Group Size Optimization:

   From the System Utilities screen,
   select System Configuration →
   BIOS/Platform Configuration (RBSU) →
   Performance Options →
   Advanced Performance Tuning Options →         
   NUMA
  Group Size Optimization and press Enter.
  Select a setting and press Enter:

Clustered (default)—Optimizes groups along NUMA boundaries, providing better performance.
Flat—Enables applications that are not optimized to take advantage of processors spanning                  multiple groups to utilize more logical processors."


======================================================================


2) Description


NUMA, Non-Uniform Memory Access, is a computer memory design used in Multiprocessing, where the memory access time depends on the memory location relative to a processor. Under NUMA, a processor can access its own local memory faster than non-local memory, that is, memory local to another processor or memory shared between processors.

QlikView generally performs better with NUMA disabled. The NUMA setting is controlled by the computer BIOS, and the recommendation is to disable it for best possible performance.

Some hardware does not allow disabling NUMA. For these scenarios QlikView 11.20 SR2 and later can automatically detect the presence of NUMA and if present adjust the QVS.exe to adapt to a NUMA environment. While not as efficient as disabling NUMA in BIOS, it restores some of the performance lost when NUMA is enabled.

The automatic detection only works in 2 or 4-socket systems. For 8 socket machines the NUMA detection fails, leaving the QVS.exe to believe itself to exist in a non-NUMA environment.

The software NUMA adaption is controlled with the EnableNumaRoundRobin variable in the QlikView Server settings.ini file.

From the tests which Performance & Scalability team is doing on new releases they have not seen any NUMA specific changes in QV 12.10 and 12.20.

The recommendations can be found on community: https://community.qlik.com/docs/DOC-2362 they are being updated if Performance & Scalability team sees that changes in server configurations impacts the performance. So it worth checking them from time to time.

Please keep in mind that Performance & Scalability team tries to test many hardware configurations, but it is still a very small subset of possible server configurations seen in customer deployments. Due to this, in case of doubt they suggest to test the settings impact. For example one customer might on average see that for their deployment disabling NUMA in BIOS is beneficial, while other might not notice any difference.



Resolution:-



EnableNumaRoundRobin

0 (default): Automated state where QlikView Server at startup detects whether NUMA is enabled or disabled at certain hardware and adapts to it. If NUMA is enabled QlikView Server will ignore NUMA nodes when allocating memory.
1: QlikView Server  is acting as if NUMA is enabled from BIOS (without any attempts to detect it)
2: QlikView Server will not ignore NUMA nodes if present (i.e. NUMA enabled)

The Settings.ini file can be found in the following location:

Windows Server 2003
C:\Document and Settings\All Users\Application Data\QlikTech\QlikViewServer
Windows Server 2008 and later
C:\ProgramData\QlikTech\QlikViewServer for Windows 2008 and later





========================================================================

3)  QlikView and NUMA 



NUMA technology typically provides performance gains over other multiprocessing
architectures especially if a software application is designed to take advantage of NUMA. However, extensive
tests at our Scalability Center prove that NUMA-enabled servers have a negative impact on QlikView Server
performance. This impact is less noticeable on systems with fewer CPU sockets as the need to access remote
memory is reduced. Therefore, for NUMA architectures, it is beneficial to disable NUMA in the BIOS settings of
the hardware running the QlikView Server in order to achieve optimum performance. Some vendors have removed
the ability to turn off NUMA in the BIOS. If that is the case, a soft-switch has been implemented into the QlikView
configuration which circumvents NUMA memory allocation. Since QlikView 11.2 SR2, QlikView will automatically
detect if NUMA has been disabled and adapts appropriately for optimal performance. (For QlikView releases prior
to QV11.2 SR1, see this document for more information on this setting: http://community.qlikview.com/docs/DOC-23
62) If a QlikView application (.QVW) is opened and loaded into memory on a NUMA-enabled system with the
soft-switch logic disabled, the operating system will use the local memory for one CPU at a time. When necessary,
it will use remote memory within another NUMA node (See Figure 1). (CPU access time to its local memory is
much faster than access to remote memory). QlikView scales well over multiple cores resulting in those cores
needing access to the same memory if “everything” is stored in one place. Since QlikView is not optimized for
NUMA, please take note of the soft-switch setting for QlikView versions prior to 11.2 SR2 or disable NUMA in
the BIOS if applicable.
========================================================================

Servers running QIX engine benefit from the following settings on average:


Setting/Configuration
Value
Hyper-threading
Enabled 4 socket /Disabled 2 socket

Note: For 4-socket solutions, having hyper-threading enabled is beneficial on average. There are, however, cases when disabling it leads to improved performance.
Power Management (Power profile)
Max performance (BIOS and Windows setting)
Node Interleaving
Enabled (meaning that NUMA is disabled)

Note: Disabling NUMA has no significant impact on the performance as the product adapts its behavior if NUMA is enabled.
Intel Turbo Boost
Enabled
Memory configuration
Configured for best performance

Note: The DIMM slots for every CPU should be populated in accordance to the hardware manufacturer’s specification for best performance.
Hardware pre-fetch
Enabled

=======================================================================
Test done by users:-

Scheduled 40 concurrent QVW reloads in the QDS (the QVW's generated data, wrote QVDs, read QVDs, and then logged stats).

The test hardware was a HP DL580 G7 with 4 X Intel Xeon Processor E7-4870 (4 CPU X 10 cores) and 256GB RAM.  By disabling hyperthreading, the average document refresh time went from 02:53 to 01:50.

=============================================================

Conclusions:-



-->Preferably we need to have utmost 2 sockets of CPU cores at the Max for QLIK.
-->Disabling NUMA is Advised if performance is not good.
-->If you don't have option to disable then do soft switch enable.
( if in case NUMA cannot be disabled, "EnableNumaRoundRobin=0" has to be written in "setting.ini" under "QlikViewServer" folder.
EnableNumaRoundRobin=0 is defaulted and means disabled functionality.
EnableNumaRoundRobin=1 means enable functionality which should be used for servers where NUMA cannot be disabled in the hardware)

-->Hyper-threading should be turned off to achieve maximum CPU Utilization.


Nprinting- (ver -16) Server Certificate Update Process

Certificate Update Process for QLIK Nprinting Import the certificate to QV Nprinting Server 1. Copy the certificate to the Server th...