After a Qlik Replicate task was stopped to update parameters, the task will not start. A new task referencing the same source fails to fetch its tables and errors out with the following:

SYS-E-HTTPFAIL, SYS-E-HTTPFAIL, Command get_owner_list failed when creating the stream component...
SYS,GENERAL_EXCEPTION,SYS-E-HTTPFAIL, Command get_owner_list failed when creating the stream component..,SYS,GENERAL_EXCEPTION,Command get_owner_list failed when creating the stream component.,Failed getting stream handle create_stream_handle failed Command create_stream_handle failed when preparing component. ORA-12170: TNS:Connect timeout occurred

Resolution

ORA-12170 is an Oracle connection error and is not caused by Qlik Replicate. Qlik Replicate will send a connection request through the Oracle client, which will establish a connection to the database based on the sqlnet.ora settings. ORA-12170: TNS:Connect timeout occurred indicates a timeout.

To resolve the error, update sqlnet.ora and increase the available timeouts.

1. Navigate to (default location) C:\app\client\Administrator\product\12.1.0\client_1\network\admin
2. Open sqlnet.ora
3. Modify or add the below:
   
   SQLNET.INBOUND_CONNECT_TIMEOUT_LISTENER=360
   SQLNET.INBOUND_CONNECT_TIMEOUT=360
   SQLNET.RECV_TIMEOUT=160
   SQLNET.SEND_TIMEOUT=160
   BREAK_POLL_SKIP=10000
   DISABLE_OOB=on
   
   
4. Stop and resume task task

Environment

Qlik Replicate 

A Qlik Replicate Log Stream task fails with the error: 

Stream component 'st_0_T03ERP_TGT_QLK_LSS' terminated Cannot initialize subtask
Failed while preparing stream component 'st_0_T03ERP_TGT_QLK_LSS'. Error reading audit batch
Timeout while waiting to get data from audit file

Resolution

Verify that sufficient disk space is available. Clear up space or increase the available quota, then:

1. Remove the Log Stream directory defined on the Log Stream Target
   This will remove all current files. A backup to a different location is optional.
   
   
2. Start the task from timestamp as far back as the retention period on the Source Transactions logs.
   
   
3. This will create a new LogStream directory and new sub-directory audit_files.

Cause

A corrupted metastore.sqllite file. The file is created in the Log Stream target location (LogStream/audit_services directory) when a task is started. Most commonly disk space issues cause the file's corruption.

Environment

• Qlik Replicate

With the new inclusion of the Get Chart Image block in the Qlik Reporting connector in Qlik Application Automation, you now have more options to notify a group of users with more in-depth data and charts using Slack, Microsoft Teams, and email.

This article will guide you in sending your first chart image to Slack with Qlik Application Automation.

It explains a basic example of a template configured in Qlik Application Automation for this scenario.

You can make use of the template which is available in the template picker. You can find it by navigating to Add new -> New automation -> Search templates and searching for 'Send a Chart Image to Slack' in the search bar, and clicking the Use template option.

For guidance on sending charts via Microsoft Teams and mail, go to the "Next Steps" section at the end of this article.

You can download examples of the automations from this article: Send-chart-image-to-slack.json, Send-chart-image-to-outlook.json, Send-chart-image-to-mail.json, Send-chart-image-to-microsoft-teams.json

Full Automation

Configure the Automation

1. Open the automation editor and search for the Qlik Cloud Services connector on the left-hand menu.
   
   
2. Drag the blocks “Select Field Value” and “Create Bookmark” to the canvas and fill in the required inputs. You can add multiple Select Field Value blocks to apply selections to various fields. The Create Bookmark block is then used to create a temporary bookmark that can pass the selections to the Get Chart Image block.
   
   
   
   
   
   
   

   Warning: Whenever the “Get Chart Image” block is to be used, we advise you to only use it with temporary bookmarks or pre-existing persistent bookmarks.

2. Search for the Qlik Reporting connector on the left-side menu, then drag the "Get Chart Image" block to the editor and attach it below the previous block.
   
   Choose the same app you chose when creating the temporary bookmark and use the ID value received from the “Create Bookmark” block. 
   
   
   
   The height and width inputs are not required and default to 1000 if the fields are empty.
   
   Manually insert the Object ID input; it can not yet be retrieved through an API call.
   
   To find the chart ID from the app, open the app, then click Edit Sheet, right-click the chart, and open the Share/Embed menu from the menu dropdown that appears. You will see the object ID of the chart under the chart preview section as shown below:
   
   
   

    

3. Add the 'Condition' block to check whether the "Get Chart Image" block has returned a successful response.
   
   
   
   
   1. If the condition block outcome evaluates to true:
      
      
      • Search for Slack connector from the left-side menu, add the "Upload File To Channel" block, and fill in all the required inputs.
        
        
        
        
      • The chart image gets posted on the Slack channel as shown below:
        
        
         

   2.  If the condition block outcome evaluates to false:

      • Add the "Update Run Title" block to specify that the "Get Chart Image" block failed due to an error.
        
        
        
        
      • Add an "Error" block to throw an error message from the "Get Chart Image" block and set the current automation run to be in a failed state.
        
        
        

Configure and send the Output

Embed the chart in the email

1. To embed the image in an email, you'll need to replace the Slack block with Send Mail block from the Microsoft Outlook 365 connector or the Send Mail block from the Mail connector and configure the block's Type parameter to HTML.
2. In the body of the HTML, start adding a <img> tag and set the src parameter of the img tag to "data&colon;image/png;base64,".
   

   
   

3. Add the Get Chart Image block output at the end of the src parameter.
   

   
   

4. Click the Get Chart Image label in the input parameter, and pick the Add formula option; then search for the "base64 encode" formula to apply it to the output from the Get Chart Image block.
   
   
   
   
   

   
   

5. Finalize the img tag with an optional width and height to style the image.
   
   

Send the chart to a channel in Microsoft Teams

1. To send the image to Microsoft Teams, replace the Slack block with the Send Html Message With Base 64 Image block from the Microsoft Teams connector.
2. Configure the "Base 64" parameter of the block by mapping it to the output of the Get Chart Image block.
   

   
   

3. Click the field mapping for the Get Chart Image again and open the formula picker. Search for the base64 encode formula and apply it.
   
   
   
   
    
4. Set the Image Content Type parameter to image/png and (optionally) provide additional HTML content that will be inserted before or after the image.
   

   
   

The information in this article is provided as-is and will be used at your 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.

Environment

• Qlik Cloud 
• Qlik Application Automation 

Talend v8 Big Data EMR task execution in HDFS configuration(Based on EMR 5.29) is hitting below issue with China region after doing migration to v8 from v7.

==Log==
org.apache.hadoop.fs.s3.S3Exception: org.jets3t.service.S3ServiceException: Service Error Message. -- ResponseCode: 403, ResponseStatus: Forbidden, XML Error Message: <?xml version="1.0" encoding="UTF-8"?><Error><Code>InvalidAccessKeyId</Code><Message>The AWS Access Key Id you provided does not exist in our records.</Message><AWSAccessKeyId>AKIATAYZAWDCxxx</AWSAccessKeyId><RequestId>HRZ7WJJFEREGFNGX</RequestId><HostId>2wOWmpLVjpjPfmclmQF8sZ6t3+QVjC1K8zzyyHbgphS==</HostId></Error>
 ==Log==

Resolution

1. As there is no a new version of jets3, Qlik Talend R&D suggest using Universal Distribution as a replacement solution.
2. When run Spark Jobs on YARN cluster using Amazon EMR distribution, you need to manually distribute the libraries, as Amazon EMR does not have the same classpath on main and subordinate nodes.
   Copy from master node /usr/lib/spark/jars/*.jar  to subordinates nodes  /usr/lib/spark/jars/path is a must.
    
3. The customer needs to make the HADOOP_HOME, SPARK_HOME,  HIVE_HOME,etc enabled for EMR environment which guarantees job execution could pick up the right jars under XXX_HOME/lib folder.
    

Cause

The current library jets3t used in Hadoop does not support the China region (cn-north-1). Due to some compatibility issues, even though the signature of S3 has been upgraded to V4, other regions of AWS are still using V2 version for avoiding these compatibility issues.

As the China region is a new region without such compatibility issues, so only V4 has been added in it.

Related Content

defining-amazon-emr-connection-parameters-with-spark-universal

Internal Investigation ID(s)

Internal defect ID: TBD-16745

Environment

Talend Big Data 

It is possible that the following error may occur when executing a Job in Talend Studio version 8 that references a custom user routine.

java.lang.NoClassDefFoundError: routines/my-custom-routine

Cause

The error occurs due to the custom routine not being found at runtime, which obstructs the execution of the Job.

Resolution

To resolve this issue, please uncheck the "Offline" checkbox in Talend Studio's Maven preferences (Window -> Preferences -> Maven). This enables Talend to download the required dependencies, thereby resolving the classpath conflict and enabling successful Job execution.

Environment

• Talend Data Integration 8.0.1

Asking questions using Insight Advisor Chat in the Hub on Qlik Sense Enterprise on Windows, may result in the message "Unable to get data" being returned. See Fig 1.

Fig 1

Resolution

Verify the LEF file includes any of the following two attributes to be entitled for Insight Advisor Chat:

• INSIGHT_ADVISOR_CHAT; YES
• INSIGHT_BOT

Then do below:

1. Enable the apps for Insight Advisor Chat for Hub by referring to Insight Advisor Chat showing No apps were found or some apps are missing
2. Open Windows Task Manager and make sure nl-app-search and nlparser_r.exe processes are running. See Fig 2. 
   
   Fig 2
   
   
   
   
3. If these two processes do not exist in Task Manager, please enable it in C:\Program Files\Qlik\Sense\ServiceDispatcher\services.conf file. 
   
   

   [nl-parser]
   //Disabled=true
   Identity=Qlik.nl-parser

   [nl-app-search]
   //Disabled=true

   Identity=Qlik.nl-app-search

4. After this, restart Qlik Sense dispatcher service.   Note, for multi nodes environment, steps 2-4 need to be done on each node.
5. Reload the app which was enabled for Insight Advisor Chat.  

Environment

Qlik Sense Enterprise on Windows 

Executing tasks or modifying tasks (changing owner, renaming an app) in the Qlik Sense Management Console and refreshing the page does not update the correct task status. Issue affects Content Admin and Deployment Admin roles.

The behaviour began after an upgrade of Qlik Sense Enterprise on Windows. 

Fix version:

This issue can be mitigated beginning with August 2021 by enabling the QMCCachingSupport Security Rule.

Solution for August 2023 and above:

Enable QmcTaskTableCacheDisabled.

To do so:

1. Navigate to C:\Program Files\Qlik\Sense\CapabilityService\
2. Locate and open the capabilities.json
3. Modify or add the QmcTaskTableCacheDisabled flag for these values to true
   
   {"contentHash":"CONTENTHASHHERE","originalClassName":"FeatureToggle","flag":"QmcTaskTableCacheDisabled","enabled":true}
   
   Where CONTENTHASHHERE matches the number in all other features listed in the capabilities.json.
   
   Example:
   
   
   
   This will disable the caching on the tasks table only, leaving the overall QMC Cache intact to gain performance. If you had previously set QmcCacheEnabled, QmcDirtyChecking, QmcExtendedCaching to false, please set it to true again.
   
   
4. Restart the Qlik Sense services

Workaround for earlier versions:

Upgrade to the latest Service Release and disable the caching functionality: 

To do so:

1. Navigate to C:\Program Files\Qlik\Sense\CapabilityService\
2. Locate and open the capabilities.json
3. Modify the flag for these values to false
   • QmcCacheEnabled
   • QmcDirtyChecking
   • QmcExtendedCaching
4. Restart the Qlik Sense services
   
   

NOTE: Make sure to use lower case when setting values to true or false as capabilities.json file is case sensitive.

Should the issue persist after applying the workaround/fix, contact Qlik Support.

Internal Investigation ID(s): 

QB-2096
QB-5168
QB-7655
QB-17238

Environment

Qlik Sense Enterprise on Windows 

This is a problem which on first impressions should not (and you would think logically cannot) happen. Therefore it is important to understand why it does, and what can be done to resolve it when it does.

The situation is that Replicate is doing a Full Load for a table (individually or as part of a task full loading many tables). The source and target tables have identical unique primary keys. There are no uppercasing or other character set issues relating to any of the columns that make up the key which may sometimes cause duplication problems. Yet as the Full Load for the table progresses, probably nearing the end, you get a message indicating that Replicate has failed to insert a row into the target as a result of a duplicate. That is there is already a row in the target table with the unique key for the row that it is trying to insert. The Full Load for that table is terminated (often after several hours); and if you try again the same error, perhaps for a different row, will often occur.

Logically this shouldn’t happen, but it does. The likelihood of it doing so depends on the source DBMS type, the type of columns in the source table, and you will find it is always for a table that is being updated (SQL UPDATEs) as Replicate copies it. The higher the update rate and the bigger the table, the more likely it is to happen.

Note: This article discussed the problems that are related to duplicates in the TARGET_LOAD and not the TARGET_APPLY, that is during Full Load and before starting to apply the cached changes.

Analysis:

To understand the fix we first need to understand why the problem occurs, and this involves understanding some of the internal workings of most conventional Relational Database Management Systems.

RDBMS’s tend to employ different terminology for things that exist in all of them. I’m going to use DB2 terminology and explain each term the first time I use it. With a different RDBMS the terminology may be different, but the concepts are generally the same?

The first concept to introduce is the Tablespace. That’s what it’s called in DB2, but it exists for all databases and is the physical area where the rows that make up the table are stored. Logically it can be considered as a single contiguous data area, split up into blocks, numbered in ascending order.

This is where your database puts the row data when you INSERT rows into the table. What’s also important is that it tries to update the existing data for a row in place when you do an UPDATE, but may not always be able to do so. If that is the case then it will move the updated row to another place in the tablespace, usually at what is then the highest used (the endpoint) block in the tablespace area.

The next point concerns how the DBMS decides to access data from the tablespace in resolving your SQL calls. Each RDBMS has an optimiser, or something similar that makes these decisions. The role of indexes with a relational database is somewhat strange. They are not really part of the standard Relational Database model, although in practice they are used to guarantee uniqueness and support referential integrity. Other than for these roles, they exist only to help the optimiser come up with faster ways of retrieving rows that satisfy your SELECT (database read) statements.

When any piece of SQL (we’ll focus on simple SELECT statements here) is presented to the optimiser, it decides on what method to use to search for and retrieve any matching rows from the tablespace. The default method is to search through all the rows directly in the tablespace looking for rows that match any selection criteria, this is known as a Tablespace Scan.

A Tablespace Scan may be the best way to access rows from a table, particularly if it is likely that many or most of the rows in the table will match the selection criteria. For other SELECTs though that are more specific about what row(s) are required, a suitable matching index may be used (if one exists) to go directly to the row(s) in the tablespace.

The sort of SQL that Replicate generates to execute against the source table when it is doing a Full Load is of the form SELECT * FROM, or SELECT col1, col2,  … FROM. Neither of these has any row specific selection criteria, and in fact this is to be expected as a Full Load is in general intended to select all rows from the source table.

As a result the database optimiser is not likely to choose to use an index (even if a unique index on the table exists) to resolve this type of SELECT statement, and instead a Tablespace Scan of the whole tablespace area will take place. This, as you will see later, can be inconvenient to us but is in fact the fastest way of processing all the rows in the table.

When we do a Full Load copy for a table that is ‘live’ (being updated as we copy it), the result we end up with when the SELECT against the source has been completed and we have inserted all the rows into the target is not likely to be consistent with what is then in the source table. The extent of the differences is dependent on the rate of updates and how long the Full Load for that table takes. For high update rates on big tables that take many hours for a Full Load the extent of the differences can be quite considerable. 

This all sounds very worrying but it is not as the CDC (Change Data Capture) part of Replicate takes care of this. CDC is mainly known for Replicating changes from source to target after the initial Full Load has been taken, keeping the target copies up to date and in line with the changing source tables. However CDC processing has an equally important role to play in the Full Load process itself, especially when this is being done on ‘live’ tables subject to updates as the Full Load is being processed.

In fact CDC processing doesn’t start when Full Load is finished, but in fact before Full Load starts. This is so that it can collect details of changes that are occurring at the source whilst the Full Load (and it’s associated SELECT statement) are taking place. The changes collected during this period are known as the ‘cached changes’ and they are applied to the newly populated target table before switching into normal ongoing CDC mode to capture all subsequent changes.

This takes care of and fixes all of the table row data inconsistencies that are likely to occur during a table Full Load, but there is one particular situation that can occur and catch us out before the Full Load completes and the cached changes can be applied. This results in Replicate trying to insert details for the same row more than once in the target table; triggering the duplicates error that we are talking about here.

Consider this situation:

• We obtain a copy of a row with a particular key as the Tablespace Scan satisfying our SELECT statement passes by its location in the source tablespace. 
• Subsequently that row is updated in the source table. After update it cannot fit into its original place in the tablespace. So the row for that key is moved to a location in a empty block at the current high point in the tablespace.
• As the tablespace continues it comes across the new copy of the row for that same key and  returns it as part of the result set for the SELECT statement. Now we have 2 rows for the same key that we are going try to insert into the target table.

That is how the problem occurs. Having variable length columns, and binary object columns in the source table make this (movement of the row to a new location in the tablespace) much more likely to happen and the duplicate insert problem to occur.

Resolution:

So how to fix this, or at least how to find a method to stop it happening.

The solution is to persuade the optimiser in the source database to use the unique index on the table to access the rows in the table’s tablespace rather than scanning sequentially through it. The index (which is unique) will only provide one row to read for each key as the execution of our SELECT statement progresses. We don’t have to worry about whether it is the ‘latest’ version of the row or not because that will be taken care of later by the application of the cached changes.

The optimiser can (generally) be persuaded to use the unique index on the source table if the SELECT statement indicates that there is a requirement to return the rows in the result set in the order given by that index. This requires having a SELECT statement with a order clause matching the columns in the unique index. Something of the form SELECT * FROM ORDER BY  col1, col2, col3, etc. Where col1, col2, col3 etc. are the columns that make up the tables unique primary index.

But, how can we do this. Replicate has a undocumented facility that allows the user to configure extra text to be added to the end of the generated SQL for a particular table during Full Load processing specifically to add a WHERE statement to determine which rows are included and excluded during a Full Load extract.

This is not exactly what we want to do (we want to include all rows), but this ‘FILTER’ facility also provides the option to extend the content of the SELECT statement that is generated after the WHERE part of the statement has been added. So we can use it to add the ORDER BY part of the statement that we require.

Here is the format of the FILTER statement that you need to add.

—FILTER: 1=1) ORDER BY col1, col2, coln —

This is inserted in the ‘Record Selection Condition’ box on the individual table filter screen when configuring the Replicate task. If you want to do this for multiple tables in the Replicate task then you need to set up a FILTER for each table individually.

To explain, the —FILTER: keyword indicates the beginning of filter information that is expected to begin with a WHERE clause (which is generated automatically).

The 1=1)) component completes that WHERE clause in a way that all rows are selected (you could put in something to limit the rows selected if required, but that’s not what we are trying yo achieve here)

It is then possible to add other clauses and parameters before terminating the additional text to be added with the final —

In this case an ORDER clause is added that will guarantee that rows are returned in the order selected. This causes the unique index on the table to be used to retrieve rows at the source; assuming that you code col1, col2, etc. to match the columns and their order in the index. If the index has some columns in descending order (rather than ascending) make sure that is coded in the ORDER BY statement as well.

If you code things incorrectly the generated SELECT statement will fail and you will be able to see and debug this through the log.

Qlik Replicate tasks using Oracle as a Source Endpoint fail after installing the Oracle July 2024 patch. 

All Qlik Replicate versions older than the 2024.5 SP03 release are affected.

Environment

• Qlik Replicate 2024.5 SP02 and below
• Oracle Source Endpoint with Oracle 2024 July patch (version 19.24)

Resolution

Upgrade to Qlik Replicate 2024.5 SP03 or later once available. 

In the meantime, Qlik has made an early build available for 2024.5: 

2024.5 SP03: https://files.qlik.com/url/idgdr2nxshgpkij3
password: cygie73l

Cause

The Oracle July 2024 patch introduced a change to redo events. Qlik has since provided a fix for Qlik Replicate which parses the redo log correctly. 

Internal Investigation ID(s)

RECOB-8698

Reference:

Oracle Database 19c Release Update July 2024 Known Issues

As a general reminder, all changes to the environment such as operating system patches, endpoint and driver patches, etc. should be tested in lower environments before promoting to production.

Qlik ODBC connector package (database connector built-in Qlik Sense) fails to reload with error Connector reply error:

Executing non-SELECT queries is disabled. Please contact your system administrator to enable it.

The issue is observed when the query following SQL keyword is not SELECT, but another statement like INSERT, UPDATE, WITH .. AS or stored procedure call.

Environment:

• Qlik Sense Enterprise on Windows
• Qlik Sense Desktop
• QlikView Server
• QlikView Desktop

Cause

See the Qlik Sense February 2019 Release Notes for details on item QVXODBC-1406.

Resolution

By default, non-SELECT queries are disabled in the Qlik ODBC Connector Package and users will get an error message indicating this if the query is present in the load script. In order to enable non-SELECT queries, allow-nonselect-queries setting should be set to True by the Qlik administrator. 


To enable non-SELECT queries:

1. Modify the QvOdbcConnectorPackage.exe.config found in the locations mentioned below.
   Set the parameter allow-nonselect-queries to True  
   
   This is case-sensitive. true will not work.
   
   In a multi node environment, the changes need to be applied to all nodes.
   Configuration file QvOdbcConnectorPackage.exe.config locations:
   
   
   • Qlik Sense Enterprise: C:\Program Files\Common Files\Qlik\Custom Data\QvOdbcConnectorPackage
   • Qlik Sense Desktop: C:\Users\user-name\AppData\Local\Programs\Common Files\Qlik\Custom Data\QvOdbcConnectorPackage
   • QlikView: C:\Program Files\Common Files\QlikTech\Custom Data\QvOdbcConnectorPackage
     

     As we are modifying the configuration files, these files will be overwritten during an upgrade and will need to be made again.

     
     
     
2. Non-select statements are now enabled and generally this is sufficient to resolve the issue.
   
   If, however, you need to run SQL statements without any data table returned (INSERT/ UPDATE/ DELETE/ DROP), you can optionally add the keyword !EXECUTE_NON_SELECT_QUERY at the end of the query. Other queries that return data (such as WITH ... AS in PostgreSQL) do not need this keyword.
   
   The EXECUTE_NON_SELECT_QUERY signals to the script processing engine that the query may not return any data.
   

   Only apply !EXECUTE_NON_SELECT_QUERY if you use the default connector settings (such as bulk reader enabled and reading strategy "connector"). Applying !EXECUTE_NON_SELECT_QUERY to non-default settings may lead to unexpected reload results and/or error messages.

More details are documented in the Qlik ODBC Connector package help site.

Related Content:

Feature Request Delivered: Executing non-SELECT queries with Qlik Sense Business 
Execute SQL Set statements or Non Select Queries

The Qlik Download page or Qlik Ideation app do not show their expected content. The web page and browser console display error messages referring to HTTP 401 Unauthorized access, which may look similar to the below examples. 

• {"errors":[{"title":"State verification failed","detail":"State not valid, missing request forgery protection","code":"STATE-1","status":"401"}],"traceId":"{ID}"}
• VM624:1 GET https://download.us.qlikcloud.com/api/v1/users/me 401
• VM624:1 GET https://download.us.qlikcloud.com/api/v1/csrf-token?qlik-web-integration-id={ID} 401

Resolution

The Qlik Download page and Ideation App on Qlik Community require 3rd party cookies as part of the current web integration. The accessing browser must allow 3rd-party cookies while accessing the Qlik Downloads page in order for the page to render successfully. 

The browser does not have to completely allow 3rd party cookies, but can also just allow 3rd cookies for the *.qlik.com or community.qlik.com domain. 

The cookie settings are browser-specific, please consult browser help for more details. 

Cause

The Qlik Download page and Ideation App in Qlik Community are composed by an embedded object hosted in Qlik Cloud. This means cookies for the user session are associated with two different domains, community.qlik.com and qlikcloud.com. The browser refer to the parent page (community.qlik.com) as a 1st party cookie, while the embedded content from a different domain (qlikcloud.com) is referred to as a 3rd party cookie. 

3rd party cookies may be blocked in a browser as a mechanisms to block user tracking and advertisement. Browser incognito mode may also block 3rd party cookies by default as part of keeping the user more anonymous. 

Related Content

Clear, allow & manage cookies in Chrome 
Third-party cookies and Firefox tracking protection 
Microsoft Edge, browsing data, and privacy 
Clear cookies in Safari on Mac 

This article provides step-by-step instructions for implementing Azure AD as an identify provider for Qlik Cloud. We cover configuring an App registration in Azure AD and configuring group support using MS Graph permissions.

It guides the reader through adding the necessary application configuration in Azure AD and Qlik Sense Enterprise SaaS identity provider configuration so that Qlik Sense Enterprise SaaS users may log into a tenant using their Azure AD credentials.

Content:

• Prerequisites
• Helpful vocabulary
• Considerations when using Azure AD with Qlik Sense Enterprise SaaS
• Configure Azure AD
• Create the app registration
• Create the client secret
• Add claims to the token configuration
• Add group claim
• Collect Azure AD configuration information
• Configure Qlik Sense Enterprise SaaS IdP
• Recap
• Addendum
• Related Content (VIDEO)

Prerequisites

• An Microsoft Azure account
• A Microsoft Azure Active Directory instance
• A Qlik Sense Enterprise SaaS tenant
• The BYOIDP feature in your Qlik license is set to YES. Contact customer support to find out if you are entitled to bring your own identity provider to your tenant.

Helpful vocabulary

Throughout this tutorial, some words will be used interchangeably.

• Qlik Sense Enterprise SaaS: Qlik Sense hosted in Qlik’s public cloud
• Microsoft Azure Active Directory: Azure AD
• Tenant: Qlik Sense Enterprise SaaS tenant or instance
• Instance: Microsoft Azure AD
• OIDC: Open Id Connect
• IdP: Identity Provider

Considerations when using Azure AD with Qlik Sense Enterprise SaaS

• Qlik Sense Enterprise SaaS allows for customers to bring their own identity provider to provide authentication to the tenant using the Open ID Connect (OIDC) specification (https://openid.net/connect/)
• Given that OIDC is a specification and not a standard, vendors (e.g. Microsoft) may implement the capability in ways that are outside of the core specification. In this case, Microsoft Azure AD OIDC configurations do not send standard OIDC claims like email_verified. Using the Azure AD configuration in Qlik Sense Enterprise SaaS includes an advanced option to set email_verified to true for all users that log into the tenant.
• The Azure AD configuration in Qlik Sense Enterprise SaaS includes special logic for contacting Microsoft Graph API to obtain friendly group names. Whether those groups originate from an on-premises instance of Active Directory and sync to Azure AD through Azure AD Connect or from creation within Azure AD, the friendly group name will be returned from the Graph API and added to Qlik Sense Enterprise SaaS.

   

Configure Azure AD

Create the app registration

1. Log into Microsoft Azure by going to https://portal.azure.com.
2. Click on the Azure Active Directory icon in the browser Or search for "Azure Active Directory" in the search bar on the top. The overview page for the active directory will appear.
   
   
   
   
3. Click the App registrations item in the menu to the left.
   
   
   
   
4. Click the New registration button at the top of the detail window. The application registration page appears.
   
   
   
   
5. Add a name in the Name section to identify the application. In this example, the name of the hostname of the tenant is entered along with the word OIDC.
   
   
   
   
   
   
6. The next section contains radio buttons for selecting the Supported account types. In this example, the default – Accounts in this organizational directory only – is selected.
   
   
   
   
7. The last section is for entering the redirect URI. From the dropdown list on the left select “web” and then enter the callback URL from the tenant. Enter the URI https://<tenant hostname>/login/callback.
   
   
   

   The tenant hostname required in this context is the original hostname provided to the Qlik Enterprise SaaS tenant.

   
   
   Using the Alias hostname will cause the IdP handshake to fail.
8. Complete the registration by clicking the Register button at the bottom of the page.
   
   
   
   
9. Click on the Authentication menu item on the left side of the screen.
   
   
   
   
10. On the middle of the page, the reference to the callback URI appears. There is no additional configuration required on this page.
    
    

Create the client secret

1. Click on the Certificates and secrets menu item on the left side of the screen.
   
   
   
   
2. In the center of the Certificates and secrets page, there is a section labeled Client secrets with a button labeled New client secret. Click the button.
   
   
   
   
3. In the dialog that appears, enter a description for the client secret and select an expiration time. Click the Add button after entering the information.
   
   
   
   
4. Once a client secret is added, it will appear in the Client secrets section of the page.
   

   Copy the "value of the client secret" and paste it somewhere safe.

   After saving the configuration the value will become hidden and unavailable.
   
   

Add claims to the token configuration

1. Click on the Token configuration menu item on the left side of the screen.
   
   
   
   
2. The Optional claims window appears with two buttons. One for adding optional claims, and another for adding group claims. Click on the Add optional claim button.
   
   
   
   
3. For optional claims, select the ID token type, and then select the claims to include in the token that will be sent to the Qlik Sense Enterprise SaaS tenant. In this example, ctry, email, tenant_ctry, upn, and verified_primary_email are checked. None of these optional claims are required for the tenant identity provider to work properly, however, they are used later on in this tutorial.
   
   
   
   
4. Some optional claims may require adding OpenId Connect scopes from Microsoft Graph to the application configuration. Click the check mark to enable and click Add.
   
   
   
   
5.  The claims will appear in the window.
   
   

Add group claim

1. Click on the API permissions menu item on the left side of the screen.
   
   
   
   
2. Observe the configured permissions set during adding optional claims.
   
   
   
   
3. Click the Add a permission button and select the Microsoft Graph option in the Request API permissions box that appears. Click on the Microsoft Graph banner.
   
   
   
   
4. Click on Delegated permissions. The Select permission search and the OpenId permissions list appears.
   
   
   
   
   

   In the OpenID permissions section, check email, openid, and profile. In the Users section, check user.read.

5. In the Select permissions search, enter the word group. Expand the GroupMember option and select GroupMember.Read.All. This will grant users logging into Qlik Sense Enteprise SaaS through Azure AD to read the group memberships they are assigned.
   
   
   
   
6. After making the selection, click the Add permissions button.
   
   
   
   
7. The added permissions will appear in the list. However, the GroupMember.Read.All permission requires admin consent to work with the app registration. Click the Grant button and accept the message that appears.
   
   
   
   

Failing to grant consent to GroupMember.Read.All may result in errors authenticating to Qlik using Azure AD. Make sure to complete this step before moving on.

Collect Azure AD configuration information

1. Click on the Overview menu item to return to the main App registration screen for the new app. Copy the Application (client) ID unique identifier. This value is needed for the tenant’s idp configuration.
   
   
   
   
2. Click on the Endpoints button in the horizontal menu of the overview.
   
   
   
   
3. Copy the OpenID Connect metadata document endpoint URI. This is needed for the tenant’s IdP configuration.
   
   

Configure Qlik Sense Enterprise SaaS IdP

1. With the configuration complete and required information in hand, open the tenant’s management console and click on the Identity provider menu item on the left side of the screen.
   
   
   
   
2. Click the Create new button on the upper right side of the main panel.
   
   
   
   
3. Select OIDC from the Type drop-down menu item, and select  Microsoft Entra ID (Azure AD) from the Provider drop-down menu item.
   
   
   
   
4. Scroll down to the Application credentials section of the configuration panel and enter the following information:
   1. ADFS discovery URL: This is the endpoint URI copied from Azure AD.
   2. Client ID: This is the application (client) id copied from Azure AD.
   3. Client secret: This is the value copy and pasted to a safe location from the Certificates & secrets section from Azure AD.
   4. The Realm is an optional value used if you want to enter what is commonly referred to as the Active Directory domain name.
      
      
      
      
5. Scroll down to the Claims mapping section of the configuration panel. There are five textboxes to confirm or alter.
   
   
   
   
   1. The sub field is the subject of the token sent from Azure AD. This is normally a unique identifier and will represent the UserID of the user in the tenant. In this example, the value “sub” is left and appid is removed. To use a different claim from the token, replace the default value with the name of the desired attribute value.
      
      
      
      
   2. The name field is the “friendly” name of the user to be displayed in the tenant. For Azure AD, change the attribute name from the default value to “name”.
      
      
      
      
   3. In this example, the groups, email, and client_id attributes are configured properly, therefore, they do not need to be altered.
      
      
      

      In this example, I had to change the email claim to upn to obtain the user's email address from Azure AD. Your results may vary.

      
      
      
      
6. Scroll down to the Advanced options and expand the menu. Slide the Email verified override option ON to ensure Azure AD validation works. Scope does not have to be supplied.
   
   
   
   
7. The Post logout redirect URI is not required for Azure AD because upon logging out the user will be sent to the Azure log out page.
8. Click the Save button at the bottom of the configuration to save the configuration. A message will appear confirming intent to create the identity provider. Click the Save button again to start the validation process.
   
   
   
   
9. The validation procedure begins by redirecting the person configuring the IdP to the login page for the IdP.
   
   
   
   
10. After successful authentication, Azure AD will confirm that permission should be granted for this user to the tenant. Click the Accept button.
    
    
    
    
11. If the validation fails, the validation procedure will return a window like the following.
    
    
    
    
12. If the validation succeeds, the validation procedure will return a mapped claims window. If the validation states it cannot map the user's email address, it is most likely because the email_verified switch has not been turned on. Go ahead and confirm, move through the remaining steps, and update the configuration as per the previous step. Re-run the validation to map the email.
    
    
    
    
13. After confirming the information is correct, the account used to validate the IdP may be elevated to a TenantAdmin role. It is strongly recommended to do make sure the box is checked before clicking continue.
    
    
    
    
14. The next to last screen in the configuration will ask to activate the IdP. By activating the Azure AD IdP in the tenant, any other identity providers configured in the tenant will be disabled.
    
    
    
    
15. Success.
    
    
    
    
16. Please log out of the tenant and re-authenticate using the new identity provider connection. Once logged in, change the url in the address bar to point to https://<tenanthostname>/api/v1/diagnose-claims. This will return the JSON of the claims information Azure AD sent to the tenant. Here is a slightly redacted example.
    
    
    
    
17. Verify groups resolve properly by creating a space and adding members. You should see friendly group names to choose from.
    
    
    
    
    
    
    
    

Recap

While not hard, configuring Azure AD to work with Qlik Sense Enterprise SaaS is not trivial. Most of the legwork to make this authentication scheme work is on the Azure side. However, it's important to note that without making some small tweaks to the IdP configuration in Qlik Sense you may receive a failure or two during the validation process.

Addendum

For many of you, adding Azure AD means you potentially have a bunch of clean up you need to do to remove legacy groups. Unfortunately, there is no way to do this in the UI but there is an API endpoint for deleting groups. See Deleting guid group values from Qlik Sense Enterprise SaaS for a guide on how to delete groups from a Qlik Sense Enterprise SaaS tenant.

Related Content (VIDEO)

Qlik Cloud: Configure Azure Active Directory as an IdP

After migrating a Spark Job from version 731 to version 801, the migrated Spark task execution generated an application log with a DEBUG level log. For some large Spark task executions, this generated up to 10GB of logs. The Spark Job design showed that the log4jLevel was unchecked by default. 

Cause

The log configuration for both the spark.driver and spark.executor is not set by default, resulting in the Spark batch Job executing with DEBUG level by default.

Resolution

In Run -> Spark Configuration ->Advanced properties (or in the wizard if using repository)

Add the property "spark.driver.extraJavaOptions" with value "-Dlog4j.configuration=/etc/spark/conf.cloudera.spark_on_yarn/log4j.properties"

Add the property "spark.executor.extraJavaOptions" with value "-Dlog4j.configuration=/etc/spark/conf.cloudera.spark_on_yarn/log4j.properties"

Note: /etc/spark/conf.cloudera.spark_on_yarn/log4j.properties is the default value provided on CDP, and you have the flexibility to customize the log levels as per your preference. This will result in altering the logger value when executed on Yarn.

Environment

• Talend Data Integration 8.0.1

This article provides a list of the best practices for Qlik Sense configuration. It is worth implementing each item, especially for a large environment so that your database can handle the volume of requests coming from all its connected nodes. 

Max Connections

For basic information, see Max Connections.

Specifies the maximum number of concurrent connections (max_connections) to the database. The default value for a single server is 100.

In a multi-node environment, this should be adjusted to the sum of all repository connection pools + 20. By default, this value is 110 per node. 

Assuming two nodes and assuming the default value of 110 per node, the value would be 240.

1. Stop all of the Qlik Sense services
2. Open C:\ProgramData\Qlik\Sense\Repository\PostgreSQL\X.X\postgresql.conf
3. Set max_connections = 110
4. Save the file
5. Restart all of the Qlik Sense Services

How to determine the Database Max Pool

The value of 110 above is a default example. You can further refine the value.

The connection pool for the Qlik Sense Repository is always based on core count on the machine. To date, our advise is to take the core count of your machine and multiply it by five. This will be your max connection pool for the Repository Service for that node.

This should be a factor of CPU cores multiplied by five.

If 90 is higher than that result, leave 90 in place. Never decrease it.

For more information about Database Max Pool Size Connection, see https://wiki.postgresql.org/wiki/Number_Of_Database_Connections

Related Content:

Optimizing Performance for Qlik Sense Enterprise  
PostgreSQL: postgresql.conf and pg_hba.conf explained  
Database connection max pool reached in Qlik Sense Enterprise on Windows 

This article explains how the Reporting connector in Qlik Application Automation can be used to generate multi-app reports. It also explains how the generated report can be stored on a cloud storage tool, like Microsoft SharePoint.

Multi-app reports

A multi-app report is a report that contains sheets from multiple apps. This type of report can be created in Qlik Application Automation with the Create Multi App Report and the Add Sheet to Multi App Report blocks.

To add selections to these sheets, you can still use the Add Selection To Sheet block. To add selections to the report, you can use the Add Selection To Report block.

Example

In this example, we'll create an automation that generates a report containing two sheets from two different apps, with selections applied to the second sheet.

Before you continue, please create a new automation and search for the reporting connector in the Block Library: 

1. Search for the Create Multi App Report in the Reporting connector, drag it to the automation editor, and attach it to the Start block. Configure it by specifying a name for the report.
   
2. Add the Add Sheet To Multi App Report block to the automation and configure it to use the output from the Create Multi App Report block and select an app and sheet to add as the first page to the report. 
   
3. Add a second Add Sheet To Multi App Report block to the automation and configure it for a different app and sheet. 
   
4. Add an Add Selection To Sheet block to the automation to add a selection to the second sheet. Configure it to use the output from the second Add Sheet To Multi App Report block and select a field to apply the selection. Provide one or multiple values for the selection.
   

    

5. Add the Generate Report block to the automation and configure it to use the report we've been building. When this block is executed in the automation, it will trigger the actual report generation and its output will be the report PDF file.
   

    

6. Find the Cloud Storage connector in the Block Library and add the Copy File block to the automation. Configure it to use the output from the Generate Report block as the Source File and set the Destination connector to Microsoft SharePoint (or another destination of your choosing). Include the Path from the output of the Generate Report block in the Destination Path parameter to use the generated filename. Use the do lookup functionality to find a Drive Id or a Site Id,  but do not specify both.
   

    

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.

During the installation of Qlik Sense with Shared Persistence, an administrator encounters an error message indicating that the database could not be reached during the Shared Persistence Connection Settings section of the installer:

This error message is a generic response when the installer cannot reach the database for a variety of reasons. In order of probability:

(1) The install is unable to communicate over the Database Port

This is the most common roadblock for this portion of the installer. To determine whether this cause is at play:

1. Start PowerShell
2. Run a Test-NetConnection command against the remote database host and port to validate connectivity. Example:
   
   

    

3. For more reading about this PowerShell command: How To Check Open Ports Using PowerShell

(2) PostgreSQL is not configured to allow the connection

The test in (1) purely connects for TCP connectivity and does not check for whether authentication is allowed. Please reference article PostgreSQL: postgres.conf and pg_hba.conf explained for how to configure PostgreSQL / Qlik Sense Repository Database to allow for connections from RIM nodes. The likely misconfigurations here would be in the listen_address section of the postgres.conf as well as the IP address(es) allowed in the pg_hba.conf files.

(3) FIPS is enabled

The next most common scenario is that the server which Qlik Sense is installed is configured to enforce FIPS policy. Due to a dependency in the install, additional steps are needed to allow the installer to operate in a FIPS environment. Reference article Unable to install Qlik Sense to a remote PostgreSQL database with FIPS enabled for more details on the resolution for this variant of blockers.

(4) Other issues

This can be a broad category but the best method for determining the cause here is by directly testing the connection to the database using the dependency that the installer use (npgsql). Attached to this article is npgsql.zip which contains the compiled .EXE which can be used to test.

Example:

1. Uncompress the ZIP to C:\npgsql
2. Run in PowerShell: .\npgsql_test.exe Host="foo.domain.local" port=5432 Username="foo@bar" Password="foo" Database=QSR
3. Result: Npgsql.PostgresException (0x80004005): 28000: SSL connection is required. Please specify SSL options and retry.
      at Npgsql.NpgsqlConnector.<DoReadMessage>d__148.MoveNext()

This implies that exclusive SSL is configured on the PostgreSQL side. Removing this configuration and allowing standard connectivity bypasses this section of the installer.

Reload fails in QMC even though script part is successfull in Qlik Sense Enterprise on Windows November 2023 and above.

When you are using a NetApp based storage you might see an error when trying to publish and replace or reloading a published app.

In the QMC you will see that the script load itself finished successfully, but the task failed after that.

ERROR QlikServer1 System.Engine.Engine 228 43384f67-ce24-47b1-8d12-810fca589657
 Domain\serviceuser QF: CopyRename exception:
 Rename from \\fileserver\share\Apps\e8d5b2d8-cf7d-4406-903e-a249528b160c.new 
to \\fileserver\share\Apps\ae763791-8131-4118-b8df-35650f29e6f6 
failed: RenameFile failed in CopyRename

ExtendedException: Type '9010' thrown in file
 'C:\Jws\engine-common-ws\src\ServerPlugin\Plugins\PluginApiSupport\PluginHelpers.cpp'
 in function 'ServerPlugin::PluginHelpers::ConvertAndThrow'
 on line '149'. Message: 'Unknown error' and additional debug info:
 'Could not replace collection 
\\fileserver\share\Apps\8fa5536b-f45f-4262-842a-884936cf119c] with
[\\fileserver\share\Apps\Transactions\Qlikserver1\829A26D1-49D2-413B-AFB1-739261AA1A5E],
 (genericException)'

<<< {"jsonrpc":"2.0","id":1578431,"error":{"code":9010,"parameter":
"Object move failed.","message":"Unknown error"}}

ERROR  Qlikserver1  06c3ab76-226a-4e25-990f-6655a965c8f3
  20240218T040613.891-0500  12.1581.19.0  
Command=Doc::DoSave;Result=9010;ResultText=Error: Unknown error
  0  0  298317  INTERNAL&
emsp; sa_scheduler  b3712cae-ff20-4443-b15b-c3e4d33ec7b4
  9c1f1450-3341-4deb-bc9b-92bf9b6861cf  Taskname  Engine  Not available
  Doc::DoSave  Doc::DoSave  9010  Object move failed.
  06c3ab76-226a-4e25-990f-6655a965c8f3

Resolution

Qlik Sense Client Managed version:

• May 2024 Initial Release
• February 2024 Patch 4
• November 2023 Patch 9

Potential workarounds

• Change the storage to a file share on a Windows server

Cause 

The most plausible cause currently is that the specific engine version has issues releasing File Lock operations. We are actively investigating the root cause, but there is no fix available yet.

Internal Investigation ID(s) 

QB-25096
QB-26125

Environment

• Qlik Sense Enterprise on Windows November 2023 and above

Click here for Video Transcript

Note: The concept ’ UPSERT MODE’ and 'MERGE MODE' is not documented in the User Guide. i.e. it is not a word you can search for in the User Guide and is not a key word in the Replicate UI.

What they do:

UPSERT MODE: Change an update to an insert if the row doesn't exists on the target
MERGE MODE: Change an insert to an update if the row already exists on the target

How to set

Use MERGE MODE: i.e. configure the task under: task setting --> Error Handling --> Apply Conflicts --> ‘Duplicate key when applying INSERT:’ UPDATE the existing target record

Use UPSERT MODE: i.e. configure the task under: task setting --> Error Handling --> Apply Conflicts --> ‘No record found for applying an UPDATE:’ INSERT the missing target record

How they work:

Batch Apply and Transactional Apply modes:

There is a big difference in how these Upsert/Merge settings work depending of whether the task is in 'Batch' or 'Transactional' Apply mode.

Batch Apply mode:

Either option (Upsert/Merge) does an unconditional Delete of all rows in the batch, followed by an Insert of all rows.

Note: The other thing to note is that with this setting the actual update that fails is inserted in a way that may not be obvious and could cause issue with downstream processing. In batch apply mode the task will actually issue a pair of transactions (1st a delete of the record and then 2nd an insert) this pair of transactions is unconditional and will result in a "newly inserted row every time the record is updated on the source.

Transactional Apply mode:

Either option (Upsert/Merge) - the original statement is run and if it errors out then the switch is done (try and catch). 

Insert in transactional apply mode,  the insert statement will be performed in a "try / catch" fashion. The insert statement  will be run and only if it fails will it be switched to an update statement .

In transactional apply mode, the update will be performed in a "try / catch" fashion. The update will be run and only if it fails will it be switched to an insert statement . 

Gotchas:

1. If the table has added fields that are used/modified by a downstream process then there is a good chance that this will cause a problem.
2. Oracle table must have supplemental logging all column set
3. Masking of possible underlying issue - why was the record not there for an update or why was it there already for and insert
4. I want to caution you that there may be other missing records (from a failed insert) that were not captured and this setting will only bring over missing records if they are updated on the source. There could be many records still missing on the target.

Possible use case:

1. Missing values in some fields/missing rows on target. With Upsert/Merge set a "fake" update can be issued on the source (Update field1 = field1) without a where clause will force every row in the source to refreshed to the target ( without losing all the target data like a reload would)
2. Prevent a task from switching out of bulk apply due to errors 'Duplicate PK or Record not found (appliance targets could benefit from this)
3. Could help the CDC only task of split table environment - Separate Full Load / CDC design.

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.

Related Content

• Transformation: Target Lookup

Environment

Qlik Replicate