QlikSense Server Guide

Qlik Help for Server issues

NPrinting Migration Doc 16ver - 19ver

NP 16 is 32 bit Client server program and NP 17 and higher versions are 64 bit Web based console deployment.*

https://community.qlik.com/t5/Knowledge-Base/NPrinting-16-to-NPrinting-19-Migration-Playbook/ta-p/1780278

https://help.qlik.com/en-US/nprinting/September2020/pdf/Qlik%20NPrinting%20help%20documentation.pdf

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

How To Recover Document CALs Assigned To A Deleted QVW

Information Description

The number of Document CALs assigned does not match the number believed to be assigned.

In other words if the user looks at each document and counts the number of Document CALS assigned to each, this number does not match what QlikView is showing in the Management Console. The user may also notice that under Documents > User Documents there may be a QVW listed all in caps (MYQVW.QVW) that does not appear in the folder indicated. This is a good indicator that this is the culprit. When deleting a QVW it is best to remove the assigned Document CALs first, then delete the QVW. By doing this the Document CALs are released and put back into the pool.

For example: Scenario: You have total 30 Doc Cals available at the beginning. You assign 3 Doc Cals to a file named film.qvw. So you suppose to have 27 doc cals left. When you rename the file name to film_1.qvw or remove to another folder. You will find the doc cals available on the server is still 27. No matter how many times you restart the services or even delete .shared file, .pgo files in the user documents folder. When you rename the file name back or restore the file to the folder, you will find the file is still contain 3 doc cals.

Resolution

There are 3 ways to resolve this problem, Based on the CALs quantity, you can apply the best possible solution:

Resolution 1:

1. Stop the QlikView Server service.

2. Add PgoAsXmlAlso=1 under [Settings 7] in the Settings.ini file located in %ProgramData%\QlikTech\QlikViewServer (default location)

3. Restart the QlikView Server service

4. Navigate to Root Directory and Open the CalData.pgo.xml file to determine to which document the CALs were assigned

5. Create a new blank QVW file named the same as document listed in CalData.pgo.xml & save it in the Root directory

6. In Management Console, navigate to Documents > User Documents and locate QWV created in the previous step

7. Click Document CALs tab and set Number of CALs allocated to this Document to zero and click Apply

Note: This resolution can be applied where the assigned CALs are HIGH in number.

Resolution 2:

1. Stop QVS and QMS as when you remove files it might still remain in the memory,

2. And then delete both caldata.pgo file under the "user documents" and also "ProgramData\QlikTech\QlikViewServer"

3. Restart the services

Note:This resolution can be applied where the assigned CALs are in LOW in number.

Resolution 3:

Clear the license information and reapply again Note:This resolution can be applied where the assigned CALs are in LOW in number.

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

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 with caution!

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

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."

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

Changing of Hostname after installation of Qliksense

The hostname in Qlik Sense is determined by the content of host.cfg, to change the hostname, you must change the value in this file. This will also result in all certificates being invalidated. They need to be recreated.

In a single node site this is sufficient, in a multi-node system there are further steps that will be required.

Single Node site

Qlik Sense any version

Single Node

1) Log in as the service account

2) Change hostname in Windows as usual.

3) Restart the server as required by Windows.

4) Stop all the Qlik Sense services, except for the Qlik Sense Repository Database

5) Use Microsoft Management Console(MMC) to delete all the Qlik Sense related certificates.

6) Delete %ProgramData%\Qlik\Sense\Repository\Exported Certificates\.Local Certificates

7) Make a copy of %ProgramData%\Qlik\Sense\Host.cfg and rename the copy to Host.cfg.old

8) Host.cfg contains the hostname encoded in base64. Generate this string for the new hostname using a site such as base64encode.

9) Open Host.cfg and replace the content with the new string

10) Finalize:

• In Qlik Sense 3.2.5 or earlier, start all Qlik Sense services to regenerate certificates.

• In Qlik Sense June 2017* or later follow the instructions below to regenerate certificates:

Instead of starting the Repository Service directly, run Windows Command Prompt as Administrator.

Execute "C:\Program Files\Qlik\Sense\Repository\Repository.exe" -bootstrap -iscentral -restorehostname

*This is required in order to re-create local Qlik certificates in newer versions of sense, as this is due to changes in this version that introduces the floating central node concept.

Keep in mind: The central certificate is used to encrypt password strings being used in connectors, so changing this certificate means that you will need to recreate all connectors that include password information.

Multi-Node Site

There are two possible scenarios in a multi-node environment changing the hostname for a Rim node and for a Central node. Certificates are always generated by the central node and are based on hostname so changing hostnames here is more time consuming and will require redistribution and in some cases also the deletion and readding of node information.

Changing Rim Node Hostname

• On the Rim node:

1) Change hostname in Windows as usual.

2) Restart the server as required by Windows.

3) Stop all the Qlik Sense services

4) Use Microsoft Management Console(MMC) to delete all the Qlik Sense related certificates.

5) Delete %ProgramData%\Qlik\Sense\Repository\Exported Certificates\.Local Certificates

6) For backup purposes, make a copy of %ProgramData%\Qlik\Sense\Host.cfg and rename the copy to Host.cfg.old

7) Host.cfg contains the hostname encoded in base64. Generate this string for the new hostname using a site such as base64encode.

8) Open Host.cfg and replace the content with the new string

9) Start up Qlik Sense, when it fails to find the certificates it will enter set up mode

10) Unfortunately it is not possible to adjust the hostname of a node in QMC, so a when a Rim node's hostname is changed you have to then delete and readd it to do this

• On the Central server:

1) Open Qlik Management Console (QMC)

2) Click Nodes

3) Select the node whose hostname has changed

4) Click Delete

5) Confirm your decision

6) Click Create New

7) Fill in the node details as appropriate

8) You will need to recreate and/or modify any rules that specifically named the old node

Changing Central Node Certificate

All certificates used by Qlik Sense are created and signed by the central node and are based on its hostname, changing the hostname on the central node breaks this chain of trust and thus all certificates on all nodes will need to be recreated and redistributed.

• On every rim node:

1) Stop all Qlik Sense services

2) Use Microsoft Management Console (MMC) to delete all the Qlik Sense related certificates

3) Start Qlik Sense services

4) Upon starting without certificates they will enter Setup mode, meaning they will be listening for new certificates

• On the central node:

1) Change hostname in Windows as usual.

2) Restart the server as required by Windows.

3) Stop all the Qlik Sense services, except for the Qlik Sense Repository Database

4) Use Microsoft Management Console(MMC) to delete all the Qlik Sense related certificates.

5) Delete %ProgramData%\Qlik\Sense\Repository\Exported Certificates\.Local Certificates

6) Copy %ProgramData%\Qlik\Sense\Host.cfg to Host.cfg.old

7) Host.cfg contains the hostname encoded in base64. Generate this string for the new hostname using a site like https://www.base64decode.org/

8) Open Host.cfg and replace the content with the new string

9) Finalize:

• In Qlik Sense 3.2.5 or earlier, start all Qlik Sense services to regenerate certificates.

• In Qlik Sense June 2017* or later follow the instructions below to regenerate certificates:

Instead of starting the Repository Service directly, run Windows Command Prompt as Administrator.

Execute "C:\Program Files\Qlik\Sense\Repository\Repository.exe" -bootstrap -iscentral -restorehostname

1)Confirm the new certificates are created and QMC is accessible

2) In the QMC, click Nodes

3) Select each rim node and click Redistribute

4) Follow the instructions displayed

Keep in mind: The central certificate is used to encrypt password strings being used in connectors, so changing this certificate means that you will need to recreate all connectors that include password information.

In addition to the above, the relevant tables in the repository database that contain the host name information are:

LocalConfigs ( Hostname column )

ServerNodeConfigurations ( Host Name )

Note: Please make sure that the above table hostname column should contain the correct server FQDN ( network host name and not the individual machine host name ) this should be exactly the hostname that is in the host.config file.

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

Migrating Qlik Sense from One Server to Another

On original server

• In QMC, go to Certificates section and export new certificates using the FQDN of the new server.

• Be sure to include the Private key

• We need the certificates to be in the Windows format

• Record the UNC root path specified in the Service Cluster section in the QMC

• Stop all services except for the Qlik Sense Repository Database

• Create a new folder for the backup, e.g. C:\backup

• Open MMC and Export the Trusted Root Certificate used by Qlik Sense from the Local Computer / Computer Account snap-in. And place it into the backup folder

• Be sure to include the Private key

• Take a backup of the database (relevant help link)

• Sample command:

"C:\Program Files\Qlik\Sense\Repository\PostgreSQL\9.6\bin\pg_dump.exe" -h localhost -p 4432 -U postgres -b -F t -f "c:\backup\QSR_backup.tar" QSR

• Copy all subfolders in the UNC root specified in the Service Cluster Section in the QMC to the backup folder

• Copy the Server certificate from the Exported Certificates folder to the backup folder (default: C:\ProgramData\Qlik\Sense\Repository\Exported Certificates)

• Optional:

• Zip the Backup folder for ease of transfer

New Server

• Install Qlik Sense of the same version

• During the installation process uncheck the check box to start services after installation. We will want to do extra configuration before Qlik Sense initializes

• Transfer the backup from the old server to local disk on the new server

• While the installer is working:

• Import the Trusted Root and Server certificate into the Local Computer / Computer Account snap-in section in their appropriate areas:

• Server > Personal

• Trusted Root > Trusted Root Certification Authorities

• Ensure that you mark the certificates as exportable

• Once the installation has finished, start the Qlik Sense Repository Database

• Restore the backed up copy of the database

• Sample command: "C:\Program Files\Qlik\Sense\Repository\PostgreSQL\9.6\bin\pg_restore.exe" -h localhost -p 4432 -U postgres -d QSR "c:\backup\QSR_backup.tar"

• If using a different share path for the Qlik Sense content

• Copy all subfolders from the UNC root specified in the Service Cluster Section in the QMC on the old server into a newly configured UNC path for the new server

• Open QlikSenseUtil

• Default path C:\Program Files\Qlik\Sense\Repository\Util\QlikSenseUtil\QlikSenseUtil.exe

• Click Connect to database

• Enter superuser credentials

• Click OK

• Go to the Service cluster section

• Click OK to get the current values

• Make the appropriate edits

• Click Save

• To validate that the changes have been recorded:

• Click Clear

• Click OK

• To launch Qlik Sense with the new hostname:

• Open cmd.exe with administrative rights

• Change the directory to the Repository install path

• Default path C:\"Program Files"\Qlik\Sense\Repository

• Execute the following command: Repository.exe -standalone -restorehostname

• Allow the Repository.exe to run for a while

• Key log elements in C:\ProgramData\Qlik\Sense\Log\Repository\Trace\ > System_Repository

• Since no other services are running, a successful result from this restore command is the Repository to fully initialize but be unable to connect to the other services

• End the Repository.exe process in Task Manager

• Start all Qlik Sense services

Access the QMC and/or Hub to confirm that all previous working functionality is present

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

Master FISCAL Calender

LET vMinDate = NUM('01/04/2008');

LET vMaxDate = NUM(YEAREND(TODAY(),0,4));

LET vFiscalMonthStart = '4';

LET vFiscalMonthAdjust = $(vFiscalMonthStart)-1;

Calendar0:

LOAD

DATE($(vMinDate)+ROWNO()-1,'DD/MM/YYYY') AS Link_Date

AUTOGENERATE $(vMaxDate)-$(vMinDate)+1;

Calendar1:

LOAD

DATE(Link_Date) AS Link_Date,

DAY(Link_Date) AS [Day Number],

WEEKDAY(Link_Date) AS [Week Day],

WEEK(Link_Date) AS [Week Number],

MONTH(Link_Date) AS Month,

NUM(MONTH(Link_Date)) AS [Calendar Month Number],

MONTHNAME(Link_Date) AS [Month Name],

CAPITALIZE(MONTH(Link_Date)) &'-'& RIGHT(MONTHNAME(Link_Date),2) AS [Month Name2],

YEAR(Link_Date) AS [Calendar Year],

CEIL(MONTH(Link_Date)/3) AS [Calendar Quarter Number],

'Q' & CEIL(MONTH(Link_Date)/3) AS [Calendar Quarter],

DATE(MONTHSTART(Link_Date)) AS [Month Start],

DATE(MONTHEND(Link_Date)) AS [Month End],

DATE(QUARTERSTART(Link_Date)) AS [Quarter Start],

DATE(QUARTEREND(Link_Date)) AS [Quarter End]

RESIDENT Calendar0;

DROP TABLE Calendar0;

Calendar_Final:

LOAD *,

[Fiscal Quarter]&' FY'&[Fiscal Year] AS [Fiscal Quarter Year],

CEIL([Fiscal Month Number]/3) AS [Fiscal Quarter Number],

DUAL(Month,[Fiscal Month Number]) AS [Fiscal Month];

LOAD *,

YEAR(YEARNAME(Link_Date,1,$(vFiscalMonthStart))) AS [Fiscal Year],

IF(Month<=$(vFiscalMonthAdjust),Month+(12-$(vFiscalMonthAdjust)),Month-$(vFiscalMonthAdjust)) AS [Fiscal Month Number],

DUAL(IF(Month>=1,'Q')& CEIL(IF(Month<=$(vFiscalMonthAdjust),Month+12-$(vFiscalMonthAdjust),Month-$(vFiscalMonthAdjust))/3),

CEIL(IF(Month<=$(vFiscalMonthAdjust),Month+12-$(vFiscalMonthAdjust),Month-$(vFiscalMonthAdjust))/3)) AS [Fiscal Quarter],

YEARNAME(Link_Date,0,$(vFiscalMonthStart)) AS [FY Year Name],

YEAR(YEARNAME(Link_Date,0,$(vFiscalMonthStart)))*100+(IF(Month<=$(vFiscalMonthAdjust),Month+(12-$(vFiscalMonthAdjust)),

Month-$(vFiscalMonthAdjust))) AS [Fiscal YYYYMM]