Qlik Compose Setup and User Guide



Qlik Compose

November 2023 and Service Release 1

Last updated: July 02, 2024

HELP.QLIK.COM

trade names, trademarks and/or registered trademarks of the respective owners with which they

are associated.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

1 What's new? 8

1.1 Features and enhancements introduced in Compose November 2023 Service

Release 1 8

Support for bulk generation of data warehouse and workflow tasks 8

Support for basic validations when generating data mart tasks 8

Support for editing data warehouse tasks 8

Other enhancements 9

1.2 Features and enhancements introduced in Compose November 2023 Initial Release 9

Support for choosing the task mode for new data warehouse tasks 9

Support for using a separate schema for data mart tables in Amazon Redshift 10

Azure Synapse Analytics enhancements 10

Snowflake enhancements 11

Other enhancements to Data Warehouse projects 11

Enhancements to Data Lake projects 12

2 Introduction 13

2.1 Data warehouse projects 13

Data warehouse projects architecture 13

Key features 14

2.2 Data lake projects 14

Easy data structuring and transformation 14

Continuous updates 14

Historical data store 14

Data lake project architecture 15

3 Qlik Compose installation and setup 16

3.1 Preparing your system for Compose 16

Hardware prerequisites 16

Software and network prerequisites 17

Required permissions for the Compose service 17

Reserved system names 17

3.2 Installing or upgrading Compose 17

Installation Instructions 18

Upgrade Instructions 18

3.3 Installing and upgrading Compose silently 19

Silently installing Compose 19

Silently upgrading Compose 20

Silently uninstalling Compose 20

3.4 Determining the required number of database connections 21

3.5 Accessing Qlik Compose 21

4 Security considerations 23

4.1 Setting up HTTPS for the Compose console 23

Checking if an SSL certificate is installed 23

Using the self-signed certificate 24

Replacing the self-signed certificate on Windows 26

4.2 Setting the hostname and changing the HTTPS port 27

To set the hostname: 27

To change the HTTPS port: 27

Contents

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

4.3 Setting up HSTS on Compose 28

Enabling HSTS 28

Disabling HSTS 28

4.4 Setting Single Sign-On Authentication with Kerberos 29

4.5 Changing the master user password 29

5 Data Warehouse projects 32

5.1 Defining a Qlik Replicate task 33

Prerequisites 33

Limitations and considerations 33

Setting up the task 34

5.2 Adding and managing data warehouse projects 35

Adding data warehouse projects 35

Managing and monitoring projects 37

Project settings 37

Resetting projects 43

Project deployment 44

Migrating objects as CSV files 46

Exporting and importing projects using the CLI 77

Working with environment variables 87

Generating projects using the CLI 97

Exporting project documentation 98

Viewing and downloading DDL scripts 99

Project versioning 100

Creating a diagnostics package 102

5.3 Getting started with Data Warehouse projects 103

High-level flow 103

Console elements 103

Data warehouse project tutorial 106

5.4 Setting up a data warehouse connection 110

Using Microsoft SQL Server as a data warehouse 111

Using Oracle as a data warehouse 114

Using Snowflake as a data warehouse 117

Using Amazon Redshift as a data warehouse 121

Using Microsoft Azure Synapse Analytics as a data warehouse 124

Using Google Cloud BigQuery as a Data Warehouse 128

Managing databases 131

5.5 Setting up Landing Zone and Data Source connections 131

Reserved column names and suffixes 131

Permissions 132

Data type mappings 133

Defining landing zones 140

Defining Replicate data source connections 147

Managing databases 154

5.6 Creating and managing the model 154

Reserved column names 155

Generating the model 155

Model limitations 163

Contents

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

Validating the model 163

Displaying the model 164

Managing the model 167

Creating expressions 184

Opening the expression builder 185

Defining reusable transformations 191

5.7 Creating and managing the data warehouse 192

Data warehouse tasks 193

Managing tasks 203

Viewing and exporting task statements 219

Modifying task settings 220

Validating the data warehouse 226

Clearing the data warehouse metadata cache 227

5.8 Creating and managing data marts 228

Adding data marts and star schemas 229

Displaying data in a pivot table 235

Managing data marts 237

Example of a Valid Table Creation Modifier 245

Example of a Valid Table Creation Modifier 250

Creating and managing custom ETLs 254

Viewing and exporting task statements 256

Validating and adjusting the data mart 256

Reloading the data mart 258

Modifying data mart settings 260

The "Obsolete" indicator 262

5.9 Creating and managing command tasks 263

Defining command tasks 263

Managing command tasks 264

Controlling and monitoring command tasks 264

5.10 Controlling and monitoring tasks and workflows 265

Viewing information in the monitor 265

Viewing missing references 267

Controlling tasks 269

Notifications 272

Workflows 274

Monitoring and controlling Qlik Replicate tasks 279

6 Data Lake projects 282

6.1 Defining a Qlik Replicate task 282

Prerequisites 282

Limitations and Considerations 283

Setting up the task 283

6.2 Adding and managing Data Lake projects 284

Prerequisites 284

Data Lake project guidelines 286

Adding data lake projects 288

Managing and monitoring projects 290

Project settings 291

Contents

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

Resetting projects 296

Project deployment 297

Exporting and importing projects using the CLI 298

Generating projects using the CLI 306

Viewing and downloading DDL scripts 307

Project versioning 308

Creating a diagnostics package 310

6.3 Getting started with Data Lake projects 311

High-level flow 311

Console elements 311

6.4 Setting up landing and storage connections 314

Defining a Storage Zone 314

Defining Landing Zones 323

Managing Landing and Storage connections 325

6.5 Selecting source tables and managing metadata 325

Reserved column names 326

Selecting and adding the source tables 326

Validating the metadata and storage 330

Managing the metadata 332

Schema evolution 337

Creating transformations 339

Reusable transformations 345

6.6 Creating and Managing Storage Zone Tasks 347

Defining and running data storage tasks 348

Managing task definitions 351

Clearing the metadata cache 361

Viewing and exporting task statements 362

Modifying task settings 363

6.7 Creating and managing command tasks 363

Defining Command tasks 364

Managing Command tasks 365

Controlling and monitoring Command tasks 365

6.8 Controlling and monitoring tasks and workflows 365

Viewing information in the monitor 366

Running and controlling tasks 368

Notifications 370

Workflows 372

Monitoring and controlling Replicate tasks 376

7 Managing Compose 378

7.1 License settings 378

License enforcement 378

Registering a license 378

7.2 Viewing a license 379

7.3 Logging settings 379

Setting the logging level 379

Setting automatic roll over and cleanup 380

Viewing and downloading Compose log files 381

Contents

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

7.4 Mail server settings 382

7.5 Running tasks on a remote Compose server 382

7.6 Replicate Server settings 382

7.7 User permissions 384

Default user permissions according to role 384

Granular access control 386

Managing user and group roles using the Compose CLI 388

Managing user permissions 389

7.8 Audit trails 392

Audit trail information 393

Exporting Audit Trail files 393

Configuring Audit Trail size and retention 394

Decoding an encoded payload 395

8 Setting up Compose on a Windows HA cluster 396

8.1 Step 1: Installing Compose in the cluster 396

Preparation 396

Primary node setup 397

Secondary node setup 397

8.2 Step 2: Adding the Compose service 398

8.3 Step 3: Defining the service dependencies 398

8.4 Step 4: Defining the URLfor the cluster 399

8.5 Upgrading Compose on the cluster 400

A Impact of DST change on Qlik Compose 401

B Support matrix 402

B.1 Supported Windows platforms 402

B.2 Supported browsers 402

B.3 Supported Qlik Replicate and Enterprise Manager versions 402

B.4 Supported Databases for Data Warehouse Projects 402

Supported data sources 402

Supported data warehouses 403

B.5 Supported hive distributions for Data Lake projects 404

C Cron format and examples 405

C.1 Cron format 405

C.2 Special characters 405

C.3 Usage examples 406

D Supported characters 408

E Glossary 409

Contents

1 What's new?

1  What's new?

This section describes the new and enhanced features in Compose November 2023 and Compose

November 2023 SR1.

In addition to these release notes, customers who are not upgrading from the latest GA

version are advised to review the release notes for all versions released since their

current version.

Customers should also review the Replicate release notes in ≤ Qlik Community for information

about the following:

l

Migration and upgrade

l

End of life/support features

l

Newly supported versions and third-party software

l

Resolved issues

l

Known issues

1.1  Features and enhancements introduced in

Compose November 2023 Service Release 1

Support for bulk generation of data warehouse and workflow

tasks

In previous versions, there was no way to generate multiple data warehouse tasks or workflow

tasks in a single operation. Now, you can select the tasks you want to generate in the new Bulk

Generate dialog, as well as the validation level (basic or all) for all selected tasks.

Generating data warehouse tasks (page 196)

Generating workflow tasks (page 278)

Support for basic validations when generating data mart tasks

In the past, data mart tasks were generated with All Validations by default and there was no option

to choose Basic Validations. As All Validations access the database to verify the existence of

columns used in expressions and lookups, they could take a long time to complete are often not

required. From this version, the default task generation has now been changed to Basic Validations

with an option to choose All Validations.

Adding data marts and star schemas (page 229)

Support for editing data warehouse tasks

From this version it is now possible to edit the task type (Full Load or Change Processing) as well as

other task properties.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

1 What's new?

Adding, editing, and duplicating tasks (page 203)

Other enhancements

Compose CLI data mart processing enhancement

Support for the --timeout -1 parameter was added to the Compose CLI mark_reload_

datamart_on_next_run command. This parameter overrides the server call's default timeout in

seconds and can be used to prevent timeouts when processing very large data marts.

UI enhancement

You can now sort columns in the Manage Data Storage Tasks and Monitor Details windows.

Snowflake enhancements

l

To align with the updated behavior of Snowflake on AWS auto-increment columns, newly

added auto-increment columns will use the new ORDERED modifier, as needed.

l

It is now possible to limit the number of data warehouse task runs checked by the data mart

task. To do this, set the following Compose environment variable:

qlk__MissingSatIDsLatestRuns

This might improve performance in certain scenarios.

Using inner joins with Transactional data marts

When working with Transactional data marts, it is now possible to use inner joins for dimensions

instead of sub-queries.

To turn on this feature, set the following environment variables to "true":

qlk__PersistDenormForFctT

qlk__PersistPreselForFctT

1.2  Features and enhancements introduced in

Compose November 2023 Initial Release

Support for choosing the task mode for new data warehouse

tasks

In previous versions, users needed to duplicate the Full Load task in order to configure a Change

Processing task. From this version, you can now choose whether to run the data warehouse task in

Full Load mode or in Change Processing mode.

See:

Adding, editing, and duplicating tasks (page 203)

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

1 What's new?

Support for using a separate schema for data mart tables in

Amazon Redshift

The option to configure a separate schema for data mart tables has been extended to support

Amazon Redshift.

Azure Synapse Analytics enhancements

Some of the enhancements described below require setting a Windows environment

variable. If you set or unset an environment variable, the change will only take effect

after you restart the Qlik Compose service.

l

HEAP staging tables support: Two environmental variables have been added: "qlk__

FullLoadStagingTablesAsHeap" and "qlk__CDC_StagingTablesAsHeap". Set these variables

to 'true' or '1', to create the staging tables as HEAP tables for Full Load or CDC tasks

respectively.

l

Added the ability to set the statistics threshold for data mart ETL: Now there are two

statistics thresholds for Synapse that can be set by the user using the following system

environment variables:

1. For the data warehouse ETL, use "qlk__UpdateStatisticsPercentageDwh"

This is used for updating the statistics of the Hub and satellite tables.

2. For the data mart ETL, use "qlk__UpdateStatisticsPercentageDma"

This is used for updating the statistics for the fact and dimension tables.

Notes:

l

Values should be between 0 and 100. A value less than 0 will be converted to 0; in this

case, the command to update the statistics will be skipped.

l

A value exceeding 100 will be converted to 100.

l

If a value cannot be interpreted as an integer, the default value (20) will be used.

l

If this variable is not present, then the default value (20) will be used.

l

The "UpdateStatisticsPercentage" system environment variable is no longer

supported.

l

The JDBC and ODBC additional properties will no longer be overridden: On the first

deployment, Compose copies all the connection parameters including JDBC and ODBC

additional properties. On subsequent deployments, the parameters will not be overridden in

the target environment.

l

Improved performance: Revised ELT statements to reduce number of statements and

improve performance running against Synapse including:

l

Skipping statements when not needed (based on run-time metadata)

l

Combining multiple statements into a single one

l

Managing Staging table (create/insert/index) based on runtime metadata

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

1 What's new?

Snowflake enhancements

Some of the enhancements described below require setting a Windows environment

variable. If you set or unset an environment variable, the change will only take effect

after you restart the Qlik Compose service.

l

Data mart performance improvement: Each SELECT is replaced by SELECT DISTINCT to

improve Snowflake's performance with data mart tasks.

In some environments, using the 'DISTINCT' keyword for Snowflake might cause

performance degradation. If this is the case, you can suppress the 'DISTINCT'

keyword by setting the environmental variable "qlk__DisableCteDistinct" to either

'1' or 'true'.

l

Reduced Snowflake storage costs by adding support for Transient Tables: In previous

versions, Compose would create TSTG and TTMP objects in Snowflake during ELT

processes, which would increase customers' data storage costs. From this version, Compose

will create Snowflake Transient Tables for temporary data storage during ETL processes,

thereby significantly reducing costs.

l

Key pair authentication: Snowflake key pair authentication is now supported.

Key pair authentication is supported in both standard and advanced mode, and

with both JDBC and ODBC.

See:

Defining the connection parameters (page 119)

Other enhancements to Data Warehouse projects

Some of the enhancements described below require setting a Windows environment

variable. If you set or unset an environment variable, the change will only take effect

after you restart the Qlik Compose service.

l

Data mart obsolete indication:Optimized implementation of the data mart obsolete

indication.

l

Transactional data mart performance:Performance improvements were made to

transactional data marts.

l

Optimized the method for updating Type 2 dimensions: Before generating the ETL for

this, you first need to set the environmental system variable 'qlk__NewPreselectDim' to either

'1' or 'true'.

l

Expressions: Added the option to evaluate NULL when testing an expression.

l

Migration performance: Improved performance with Qlik Compose migration operations.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

1 What's new?

l

Data mart export/import: Exporting and importing data marts now includes the "Table

Creation Modifiers" column. This will enable you to customize the fact or dimension table

creation modifiers.

Notes:

l

If the column value is empty, the project default will be used.

l

The project default value is not included in the export/import.

l

Optimization of dropping and creating tables in an empty schema: From this version,

when a schema does not exist, Compose will try to create it (and return an error if it fails).

Additionally, if the new schema is empty, Compose will not try to drop tables from the

previous schema.

l

Mappings for target columns not mapped to source: A new option has been added to the

Task Settings: When a data warehouse column is unassigned. The new option enables you

to set unassigned columns to NULL or to use a previous column value.

This setting will be ignored if "backdating" is used.

See:

Modifying task settings (page 220)

l

CLItask generation: Added the ability to generate tasks using the Compose CLI at project,

task, data warehouse, and data mart level.

See:

Generating tasks using the CLI (page 270)

l

Logging: The logging (for DWH and Data marts) can now be controlled by the following

environmental system variable:

qlk__LoggingType

The following options are possible:

l

None - No logging at all

l

Deferred - All logging info will be stored in runtime variables, which will be used to add

the logging information in a single statement at the end of the task. When this variable

is not available or when it has other values, the logging will be as usual.

l

Compose CLI in Data Warehouse projects: Added the ability to update custom ETLs in Data

Warehouse projects using the Compose CLI. This functionality can be incorporated into a

script to easily update Custom ETLs.

See:

Creating and managing custom ETLs (page 199)

Enhancements to Data Lake projects

l

Apache Impala views in Data Lakes projects: The header__batch_modified column will now

be cast as varchar(32) for the outbound Apache Impala views. To leverage this

enhancement, you need to set an environment variable.

l

Databricks: Added support for Unity Catalog.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

2 Introduction

2  Introduction

Qlik Compose provides an all-in-one purpose built automation solution for creating an agile data

warehouse and/or ingesting data from multiple sources to your data lake for further downstream

processing. To this end, Qlik Compose offers two project types: Data Warehouse and Data Lake.

This introduction will take a closer look at how these projects can help your organization overcome

the hurdles typically faced when confronted with the challenge of setting up and maintaining an

agile data warehouse, or when faced with challenge of ingesting data from multiple source to a

single analytics-ready storage system.

2.1  Data warehouse projects

Traditional methods of designing, developing, and implementing data warehouses require large

time and resource investments. The ETL stand-up development effort alone – multi-month and

error-prone with prep times of up to 80 percent and expertise from specialized developers – often

means your data model is out of date before your BI project even starts. Plus, the result of a

traditional data warehouse design, development, and implementation process is often a system

that can’t adapt to continually changing business requirements. Yet modifying your data warehouse

diverts skilled resources from your more innovation-related projects. Consequently, your business

ends up with your data warehouse becoming a bottleneck as much as an enabler of analytics.

Qlik Compose data warehouse projects allows you to automate these traditionally manual,

repetitive data warehouse tasks: design, development, testing, deployment, operations, impact

analysis, and change management. Qlik Compose automatically generates the task statements,

data warehouse structures, and documentation your team needs to efficiently execute projects

while tracking data lineage and ensuring integrity. Using Qlik Compose, your IT teams can respond

fast – in days – to new business requests, providing accurate time, cost, and resource estimates.

Then once projects are approved, your IT staff can finally deliver completed data warehouses, data

marts, and BI environments in far less time.

Data warehouse projects architecture

The process is illustrated in the following diagram and described below:

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

2 Introduction

Key features

The comprehensive set of automation features in our Qlik Compose solution simplifies data

warehousing projects. It eliminates the cumbersome and error-prone manual coding required by

legacy data warehouse design and implementations’ many repetitive steps. In addition, our solution

includes the operational features your business needs for ongoing data warehouse and data mart

maintenance.

Automation Features Operational Features

l

Optimized for either model-driven

or data-driven data warehousing

approaches

l

Real-time source data integration

l

Automated ETL generation

l

Physical data warehouse

management

l

Data mart generation

l

Monitoring

l

Workflow designer and scheduler

l

Notifications

l

Data profiling and quality enforcement

l

Lineage and impact analysis

l

Project documentation generation

l

Migration between environments

2.2  Data lake projects

Leverage Qlik Compose data lake projects to automate your data pipelines and create analytics-

ready data sets. By automating data ingestion, schema creation, and continual updates,

organizations realize faster time-to-value from their existing data lake investments.

Easy data structuring and transformation

An intuitive and guided user interface helps you build, model and execute data lake pipelines.

Automatically generate schemas and Hive Catalog structures for operational data stores (ODS) and

historical data stores (HDS) without manual coding.

Continuous updates

Be confident that your ODS and HDS accurately represent your source systems.

l

Use change data capture (CDC) to enable real-time analytics with less administrative and

processing overhead.

l

Efficiently process initial loading with parallel threading.

l

Leverage time-based partitioning with transactional consistency to ensure that only

transactions completed within a specified time are processed.

Historical data store

Derive analytics-specific data sets from a full historical data store (HDS).

l

New rows are automatically appended to HDS as data updates arrive from source systems.

l

New HDS records are automatically time-stamped, enabling the creation of trend analysis

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

2 Introduction

and other time-oriented analytic data marts.

l

Supports data models that include Type-2, slowing changing dimensions.

Data lake project architecture

The flow is as follows:

1. Land: The source tables are loaded into the Landing Zone using Qlik Replicate or other third-

party replication tools.

When using Qlik Replicate to move the source table to the Landing Zone, you can define

either a Full Load replication task or a Full Load and Store Changes task to constantly

propagate the source table changes to the Landing Zone in write-optimized format.

2. Store: After the source tables are present in the Landing Zone, Compose auto-generates

metadata based on the data source(s). Once the metadata and the mappings between the

tables in the Landing Zone and the Storage Zone have been finalized, Compose creates and

populates the Storage Zone tables in read-optimized format, ready for consumption by

downstream applicaitons.

It should be noted that even though setting up the initial project involves both manual and

automatic operations, once the project is set up, you can automate the tasks by designing a

Workflow in Compose and/or utilizing the Compose scheduler.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

3 Qlik Compose installation and setup

3  Qlik Compose installation and setup

This section describes how to install and set up Qlik Compose.

Note that as Qlik Replicate serves as a data (and metadata) provider for Qlik Compose, you also

need to install Replicate in your organization. For a description of the Replicate installation

procedure, refer to the

Qlik Replicate Setup and User Guide.

In this section:

l

Preparing your system for Compose (page 16)

l

Installing or upgrading Compose (page 17)

l

Installing and upgrading Compose silently (page 19)

l

Determining the required number of database connections (page 21)

l

Accessing Qlik Compose (page 21)

3.1  Preparing your system for Compose

Compose should be installed on a Windows Server machine that is able to access the data

warehouse and optionally the source database(s) defined in your Compose project. Note that

Compose only needs to access the source database if you plan to discover the source database

when generating your model. For more information on discovery, see

Discovering the Source

Database or Landing Zone (page 156)

Before installing Compose, make sure that the following prerequisites have been met:

Hardware prerequisites

The following table lists the required hardware for varied deployment scales:

Component

Basic

System

Large

System

Extra-

Large

System

Processor

Additional cores may improve performance

when several ETL processes are running

concurrently.

Quad

core

Quad

core base

8-core

base

Hardware component requirements

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

3 Qlik Compose installation and setup

Component

Basic

System

Large

System

Extra-

Large

System

Memory

Additional memory may improve performance

when several ETL processes are running

concurrently.

8 GB 16 GB 32 GB

Disk requirements

For all configurations, RAID is recommended

for higher system availability in case of disk

failure.

100 GB

SSD

500 GB

10,000

RPM

RAID

500 GB

15,000

RPM

RAID

Network 1 Gb 10 Gb Two 10 Gb

Software and network prerequisites

l

Firewall ports 80/443 should be open on the Compose machine.

l

.NET Framework 4.8 or later installed on the Compose machine.

l

TLS 1.2 or later must be supported in the underlying OS.

On Windows Server 2012 R2, TLS 1.2 should be turned on by default. If it is not,

refer to the Microsoft online help for instructions on how to turn it on.

For information on supported databases and browsers, see

Support matrix (page 402)

Required permissions for the Compose service

Qlik Compose needs to be installed and run as Administrator.

Reserved system names

All database object names (queries, tables, columns, schemas, and indexes) starting with the prefix

qlk__, and regardless of case, are reserved for internal Compose use.

Thus, a table named qlK__MyTable or a column named QLK__MyColumn would not be permitted.

3.2  Installing or upgrading Compose

The following topic describes how to install and upgrade Qlik Compose.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

3 Qlik Compose installation and setup

Installation Instructions

For best performance when using cloud-based databases (such as, Snowflake) as your

data source or data warehouse, it is strongly recommended to install Qlik Compose on a

machine (such as Amazon EC2) located in the same region as your database instance.

To install Compose:

1. Run the Compose setup file (

Qlik_Compose_<version.number>.exe

The Qlik Compose setup wizard opens.

2. Click Next. Select I accept the terms of the license agreement and then click Next again.

3. Optionally change the installation directory and then click Next.

4. Click Next and then click Next again to start the installation.

5. When the installation completes, click Finish to exit the Wizard.

As part of the installation, a new Windows Service called Qlik Compose is created.

6. Open the Qlik Compose console as described in

Accessing Qlik Compose (page 21)

When you first open the Qlik Compose Console, you will be prompted to register

an appropriate license. Register the license that you received from Qlik.

Upgrade Instructions

Depending on your existing Compose version, you may also need to perform additional

version-specific upgrade tasks. It is therefore strongly recommended to review the

release notes for the new version before upgrading.

1. Stop all Compose tasks and services.

2. After the Qlik Compose service has been stopped by the Installer, make sure that all child

processes are also stopped.

Compose runs a check to verify the termination of tasks and processes before

running an upgrade. If any processes are found to be still running, the installation

will be aborted.

3. Run the Qlik Compose setup wizard.

4. Start all Compose tasks and services.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

3 Qlik Compose installation and setup

3.3  Installing and upgrading Compose silently

Compose can be installed silently (i.e. without requiring user interaction). This option is useful, for

example, if you need to install Compose on several machines throughout your organization.

Before commencing the installation, make sure that the prerequisites have been met.

See Preparing your system for Compose (page 16).

The following topics describe how silently install, upgrade, and uninstall Compose:

l

Silently installing Compose (page 19)

l

Silently upgrading Compose (page 20)

l

Silently uninstalling Compose (page 20)

Silently installing Compose

The installation process consists of two stages: creating a response file, and running the silent

install.

Creating a response file

Before starting the installation, you need to create a response file.

To create the response file:

1. From the directory containing the Compose setup file, run the following command(note that

this will also install Compose):

Qlik_Compose_<version.number>.exe /r /f1<my_response_file>

where:

<my_response_file> is the full path to the generated response file.

Example:

Qlik_Compose_<version.number>.exe /r /f1C:\Compose_install.iss

2. To change the default installation directory, open the response file in a text editor and edit

the

first

szDir value as necessary.

3. To change the default data directory, edit the

third

szDir value as necessary.

4. Save the file as <name>.iss, e.g. Compose_install_64.iss.

Running the silent install

To silently install Compose, open a command prompt and change the working directory to the

directory containing the Compose setup file. Then issue the following command (where <response

file> is the path to the response file you created earlier):

Syntax:

<Compose_setup_file> /s /f1<my_response_file> [/f2<LOG_FILE>]

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

3 Qlik Compose installation and setup

Example:

C:\>Qlik_Compose_<version.number>.exe /s /f1C:\temp\1\Compose_install.iss /f2C:\temp\1\silent_

x64_install.log

If the installation was successful, the log file should contain the following rows:

[ResponseResult]

ResultCode=0

Silently upgrading Compose

Before starting the silent upgrade:

1. Create a response file. See Step 1 of "Creating a Response File" in Silently

installing Compose (page 19)

2. It is strongly recommended to back up the Compose "Data" folder.

3. All tasks and java processes must be terminated. Compose runs a check to verify

the termination of tasks and processes before running the upgrade. If any

processes are found to be still running, the upgrade will be aborted.

To silently upgrade Compose:

1. Open a command prompt and change the working directory to the directory containing the

Compose setup file.

2. Issue the following command (where

<my_response_file>

is the path to the response file you

created earlier):

Syntax:

<COMPOSE_KIT> /s /f1<my_response_file> [/f2<LOG_FILE>]

Example:

C:\>Qlik_Compose_<version.number>.exe /s /f1C:\temp\1\Compose_upgrade.iss /f2C:\temp\1\silent_

x64_up.log

If the upgrade was successful, the log file should contain the following rows:

[ResponseResult]

ResultCode=0

Silently uninstalling Compose

Silently uninstalling Compose is also comprised of creating a response file and running the silent

uninstall.

The process is the same as for silently installing Compose. For instructions, see

Silently installing

Compose (page 19)

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

3 Qlik Compose installation and setup

3.4  Determining the required number of database

connections

As a rule of thumb, the higher the number of database connections opened for Compose, the more

tables Compose will be able to load in parallel. It is therefore recommended to open as many

database connections as possible for Compose. However, if the number of database connections

that can be opened for Compose is limited, you can calculate the minimum number of required

connections as described below.

To determine the number of required connections:

1. For each task, determine the number of connections it can use during runtime. This value

should be specified in the Advanced tab in the Manage Data Warehouse Tasks Settings

window (Data Warehouse projects) or in the Manage Storage Tasks Settings window (Data

Lake projects). When determining the number of required connections, various factors need

to be taken into account including the number of tables, the size of the tables, and the

volume of data. It is therefore recommended to determine the required number of

connections in a Test environment.

2. Calculate the number of connections needed by all tasks that run in parallel. For example, in a

Data Lake project, if three data storage tasks run in parallel, and each task requires 5

connections, then the number of required connections will be 15.



Similarly, in a Data Warehouse project, if a workflow contains two data warehouse tasks that

run in parallel and each task requires 5 connections, then the minimum number of required

connections will be 10. However, if the same workflow also contains two data mart tasks

(that run in parallel) and the sum of their connections is 20, then the minimum number of

required connections will be 20.

3. Factor in the connections required by the Compose Console. To do this, multiply the

maximum number of concurrent Compose users by three and then add to the sum of Step 2

above. So, if the number of required connections is 20 and the number of concurrent

Compose users is 4, then the total would be:

20 + 12 = 32

3.5  Accessing Qlik Compose

You can use a Web browser to access the Qlik Compose Console from any computer in your

network. For information on supported browsers, see

Preparing your system for Compose (page

16)

The person logged in to the computer where you are accessing the Console must be an

authorized Qlik Compose user. For more information, see Managing user permissions

(page 389).

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

3 Qlik Compose installation and setup

To access the Qlik Compose Console:

1. To access the Qlik Compose Console from the machine on which it is installed, select All

Programs > Qlik Compose > Qlik Compose Console from the Windows Start menu. To

access the Qlik Compose Console from a remote browser, type the following address in the

address bar of your Web browser

https://<ComputerName>/qlikcompose/

Where

is the name or IP address of the computer on which Compose is

installed.

2. If no server certificate is installed on the Compose machine, a page stating that the

connection is untrusted will be displayed. This is because when Compose detects that no

server certificate is installed, it installs a self-signed certificate. Since the browser has no

way of knowing whether the certificate is safe, it displays this page. For more information,

see

Setting up HTTPS for the Compose console (page 23)

3. When prompted for your password, enter your domain username and password.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

4 Security considerations

4  Security considerations

During normal operation, Qlik Compose needs to access databases and storage systems for the

purpose of reading and writing data and metadata.

This section describes the procedure you should follow to ensure that any data handled by Qlik

Compose will be completely secure.

In this section:

l

Setting up HTTPS for the Compose console (page 23)

l

Setting the hostname and changing the HTTPS port (page 27)

l

Setting up HSTS on Compose (page 28)

l

Setting Single Sign-On Authentication with Kerberos (page 29)

l

Changing the master user password (page 29)

4.1  Setting up HTTPS for the Compose console

Industry-standard security practices dictate that web user interface for enterprise products must

use secure HTTP (HTTPS). Qlik Compose enforces the use of HTTPS and will not work if HTTPS is

configured incorrectly.

As Compose uses the built-in HTTPS support in Windows, it relies on the proper setup of the

Windows machine it runs on to offer HTTPS access. In most organizations, the IT security group is

responsible for generating and installing the SSL server certificates required to offer HTTPS. It is

strongly recommended that the machine on which Compose is installed already has a valid SSL

server certificate installed and bound to the default HTTPS port (443).

Checking if an SSL certificate is installed

To check whether an SSL certificate is installed, you can use the following command:

netsh http show sslcert | findstr /c:":443 "

If an SSL certificate is installed, the output should look like this:

netsh http show sslcert | finds

tr /c:":443 "

IP:port : 192.168.1.13:443

IP:port : 192.168.1.11:443

IP:port : [fe80::285d:599c:4a55:1092%11]:443

IP:port : [fe80::3d0e:fb1c:f6c3:bc52%23]:443

With a valid SSL certificate installed, the Qlik Compose web user interface will automatically be

available for secure access from a web browser using the following URL:

https://<ComputerName>/qlikcompose/

Where <ComputerName> is the name or IP address of the computer on which Compose is installed.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

4 Security considerations

Using the self-signed certificate

Due to the way the HTTPS protocol works, there is no way for Compose to automatically provide

and install a valid SSL server certificate. Still, in the event that no SSL server certificate is installed,

Compose automatically generates and installs a self-signed SSL server certificate (as a temporary

measure). This certificate is generated on the Compose machine and cannot be exported or used

elsewhere.

It should be noted that browsers do not consider the certificate to be valid because it was not

signed by a trusted certificate authority (CA). When connecting with a browser to a server that uses

a self-signed certificate, a warning page is shown such as this one in Chrome:

Or this one in Firefox:

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

4 Security considerations

The warning page informs you that the certificate was signed by an unknown certificate authority.

All browsers display a similar page when presented with a self-signed certificate. If you know that

the self-signed certificate is from a trusted organization, then you can instruct the browser to trust

the certificate and allow the connection. Instructions on how to trust the certificate vary between

browsers and even between different versions of the same browser. If necessary, refer to the help

for your specific browser.

Some corporate security policies prohibit the use of self-signed certificates. In such

cases, it is incumbent upon the IT Security department to provide and install the

appropriate SSL server certificate (as is the practice with other Windows products such

as IIS and SharePoint). If a self-signed certificate was installed and needs to be

removed, then the following command can be used:

composeCtl.exe certificate clean

Note that after the self-signed certificate is deleted, connections to the Qlik Compose

machine will not be possible until a valid server certificate is installed. Should you want

to generate a new self-signed certificate (to replace the deleted certificate), simply

restart the Qlik Compose service.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

4 Security considerations

Replacing the self-signed certificate on Windows

The instructions below are intended for organizations who wish to replace the self-signed

certificate generated by the Compose Server on Windows with their own certificate. The process,

which is described below, involves removing the self-signed certificate and then importing the new

certificate.

See also

Setting up HTTPS for the Compose console (page 23)

Before starting, make sure that the following prerequisites have been met:

l

The replacement certificate must be a correctly configured SSL PFX file containing both the

private key and the certificate.

l

The common name field in the certificate must match the name browsers will use to access

the machine.

To remove the self-signed certificate created by Qlik Compose:

1. Stop the Qlik Compose service.

2. Open a command prompt (using the "Run as administrator" option) and change the path to

the Compose bin directory. The default path is

C:\Program Files\Qlik\Compose\bin

3. Run the following command:

composeCtl.exe certificate clean

To import your own certificate:

1. Run

mmc.exe

to open the Microsoft Management Console.

2. From the File menu, select Add/Remove Snap-in. The Add or Remove Snap-ins window

opens.

3. In the left pane, double-click Certificates. The Certificates snap-in wizard opens.

4. Select Computer account and then click Next.

5. In the Select Computer screen, make sure that Local computer is selected and then click

Finish.

6. Click OK to close the Add or Remove Snap-ins window.

7. In the left pane, expand the Certificates folder. Then, right-click the Personal folder and

select All Tasks > Import.

8. In the File to Import screen, select your PFX certificate file. Note that by default the Open

window displays CER files. In order to see your PFX files, you need to select Personal

Information Exchange from the drop-down list in the bottom right of the window.

9. Click Next and enter the private key password.

10. Continue clicking Next until you reach the Completing the Certificate Import Wizard

screen. Then click Finish to exit the wizard.

11. In the Personal > Certificates folder, double-click the newly imported certificate. The

Certificate window opens.

12. Scroll down the Details tab until you see the Thumbprint details and copy them to the

clipboard.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

4 Security considerations

13. Open a command prompt and run the following commands:

Syntax:

¢ netsh http add sslcert ipport=0.0.0.0:443 certhash=[YOUR_CERTIFICATE_THUMBPRINT_

WITHOUT_SPACES] appid={4dc3e181-e14b-4a21-b022-59fc669b0914}

Example:

netsh http add sslcert ipport=0.0.0.0:443

certhash=5f6eccba751a75120cd0117389248ef3ca716e61 appid={4dc3e181-e14b-4a21-b022-

59fc669b0914}

Syntax:

¢ netsh http add sslcert ipport=[::]:443 certhash=[YOUR_CERTIFICATE_THUMBPRINT_WITHOUT_

SPACES] appid={4dc3e181-e14b-4a21-b022-59fc669b0914}

Example:

netsh http add sslcert ipport=[::]:443 certhash=5f6eccba751a75120cd0117389248ef3ca716e61

appid={4dc3e181-e14b-4a21-b022-59fc669b0914}

14. Close the command prompt and Microsoft Management Console.

15. Start the Qlik Compose service.

4.2  Setting the hostname and changing the HTTPS port

After installing Qlik Compose, you can use the Compose CLI to set the hostname and HTTPS port

for accessing the Qlik Compose server machine.

Under normal circumstances, you should not need to set the hostname. However, on some

systems, connecting using HTTPS redirects to localhost. If this occurs, set the hostname of the

Compose machine by running the command shown below.

To set the hostname:

Run the following command from the Compose bin directory:

Command syntax

ComposeCtl.exe configuration set --address

address

Where:

--address is the hostname of the Compose server machine.

Example

ComposeCtl.exe configuration set --address MyHostName

To change the HTTPS port:

Run the following command from the Compose bin directory:

Command syntax

ComposeCtl.exe configuration set --https_port

port_number

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

4 Security considerations

Where:

--https_port is the HTTPS port number of the Compose server machine. The default HTTPS port is

443.

Example

ComposeCtl.exe configuration set --https_port 442

4.3  Setting up HSTS on Compose

HSTS is a web security policy mechanism that helps to protect websites against man-in-the-middle

attacks such as protocol downgrade attacks and cookie hijacking. It allows web servers to declare

that web browsers (or other complying Dilqam) should automatically interact with it using only

HTTPS connections, which provide Transport Layer Security (TLS/SSL).

You can force the Compose Web UI and/or the Compose REST API connections to use HSTS (HTTP

Strict Transport Security). To do this, run the commands described below.

All commands should be run from as Admin from the product bin folder.

Enabling HSTS

Command syntax

ComposeCtl.exe configuration set --static_http_headers

header_list

--rest_http_headers

header_

list

Parameters

Parameter Description

--static_http_headers The headers required to connect to the Compose Web UI.

--rest_http_headers The headers required to connect using the API.

Headers should be specified using the following format:

ComposeCtl.exe configuration set --static_http_headers "header1:value1" "header2:value2" --

rest_http_headers "header1:value1" "header2:value2"

Example

ComposeCtl.exe configuration set --static_http_headers "Strict-Transport-Security:max-

age=31536000; includeSubDomains;" --rest_http_headers "Strict-Transport-Security":"max-

age=31536000; includeSubDomains;"

Disabling HSTS

You can also revert to regular HTTPS connections.

Command syntax

ComposeCtl.exe configuration set --static_http_headers ""|--rest_http_headers ""

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

4 Security considerations

Parameters

Parameter Description

--static_http_headers Use this parameter to revert the headers required to connect to

the Compose Web UI.

--rest_http_headers Use this parameter to revert the headers required to connect using

the API.

Example

Disable static_http_headers

ComposeCtl.exe configuration set --static_http_headers ""

Disable rest_http_headers

ComposeCtl.exe configuration set --rest_http_headers ""

4.4  Setting Single Sign-On Authentication with

Kerberos

Kerberos is an enterprise authentication protocol that uses the concept of tickets and three-way

authentication to enable users and computers to identify themselves and secure access to

resources.

Using Kerberos SSO, users can seamlessly log into Compose and administrators can completely

externalize and centrally manage users or group memberships using their existing Kerberos

infrastructure.

To set the authentication method to single sign-on with Kerberos, run:

ComposeCtl.exe configuration set --authentication_method sso-kerberos

To revert the authentication method to standard single sign-on, run:

ComposeCtl.exe configuration set --authentication_method sso

If the Kerberos protocol fails, Compose will try to log in using NTLM authentication. If

NTLM authentication is not enabled in the system, an error will be returned.

4.5  Changing the master user password

All passwords are encrypted using a one-time randomly generated master key. The master key is

stored automatically in the root repository of Compose (

<product_

dir>\data\projects\GlobalRepo.sqlite

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

4 Security considerations

The master key is encrypted by a user key, which in turn, is derived from a master password

entered by the user. By default, the Master User Password is randomly generated by Compose. The

best practice, however, is to change the Master User Password, as this will allow Compose projects

and configuration settings to be imported to another machine without needing to re-enter the

project credentials.

It may also be convenient to use the same Master User Password within a trusted environment. In

other words, if the same administrators control both the production and the testing environments,

using the same Master User Password in both environments will facilitate the transfer of projects

with credentials between the testing and production environments.

The user key is stored in the muk.dat file located in

<product_dir>\data\

The Master User Password must be a minimum of 32 characters. You can either use your

own password or run the genpassword utility described below to generate a password for

you. Note also that the password can only contain alphanumeric characters (i.e. it

cannot contain special keyboard characters such as # or @).

All of the commands listed below must be run as Admin from:

<product_dir>\bin

To generate a random 32 character password:

Issue the following command:

ComposeCtl.exe utils genpassword

To change the randomly generated master user password:

1. Issue the following command:

ComposeCtl.exe masterukey set --password <new_master_password>

If you add the --prompt parameter to the command and omit the --password

parameter, the CLI will prompt you for the password. When you enter the

password, it will be obfuscated. This is especially useful if you do not want

passwords to be retained in the command prompt history.

Syntax:

ComposeCtl.exe masterukey set --prompt

2. Restart the Compose service.

To change a user-defined master user password:

1. Issue the following command:

ComposeCtl.exe masterukey set --current-password <current_master_password> --password

<new_master_password>

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

4 Security considerations

If you add the --prompt parameter to the command and omit the --password and --

current-password parameters, the CLI will prompt you for the required passwords.

When you enter the passwords, they will be obfuscated. This is especially useful if

you do not want passwords to be retained in the command prompt history.

Syntax:

ComposeCtl.exe masterukey set --prompt

2. Restart the Compose service.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

5  Data Warehouse projects

This section explains how to set up data warehouse projects.

In this section:

l

Defining a Qlik Replicate task (page 33)

l

Adding and managing data warehouse projects (page 35)

l

Getting started with Data Warehouse projects (page 103)

l

Setting up a data warehouse connection (page 110)

l

Setting up Landing Zone and Data Source connections (page 131)

l

Creating and managing the model (page 154)

l

Creating and managing the data warehouse (page 192)

l

Creating and managing data marts (page 228)

l

Creating and managing command tasks (page 263)

l

Controlling and monitoring tasks and workflows (page 265)

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

5.1  Defining a Qlik Replicate task

In order to work with Compose, you first need to define a Qlik Replicate task that replicates the

source tables from the source endpoint to a landing zone in the data warehouse (defined as the

target endpoint in the Replicate task). The landing zone should then be defined as the data source

for the Compose project.

For information on which endpoints can be used in a Replicate task that lands data for Compose,

see

Supported data warehouses (page 403)

Configuring multiple Replicate tasks with the same landing zone is not supported.

The steps below highlight the settings that are required when using Qlik Replicate with Compose.

For a full description of setting up tasks in Qlik Replicate, please refer

to the

Qlik Replicate Help

Prerequisites

l

When Oracle is defined as the source endpoint in the Replicate task, full supplemental

logging should be defined for all source table columns that exist on the target and any source

columns referenced in filters, data quality rules, lookups, and expressions.

l

When using Replicate November 2023 or later and Amazon Redshift as your data warehouse,

you must define a global transformation rule in Replicate that converts BOOLEAN data types

to VARCHAR(1). Otherwise, an error will occur during the data warehouse task. For

information on defining global transformation rules, see Starting the Global Transformation

Rules wizard in the Replicate help.

Limitations and considerations

l

Replicate allows you to define global transformations that are applied to source/Change

tables during task runtime. The following global transformations, however, should not be

defined (as they are not compatible with Compose tasks):

l

Rename Change Table

l

Rename Change Table schema

l

The Create target control tables in schema option in the Replicate task settings' Control

Table tab is not supported.

l

Support for the JSON and XML data types is limited to the Snowflake VARIANT data type.

Therefore, apart from the Snowflake VARIANT data type, columns that are usually created

with these data types (by the Replicate target endpoint) should be created as STRINGs

instead. Therefore, columns that are usually created with these data types (by the Replicate

target endpoint) should be created as STRINGs instead. This can be done automatically

within Replicate using a data type transformation. For information on which target endpoints

support JSON and XML data types as well as instructions on how to create a data type

transformation, please refer to the Replicate Help.

l

As Compose does not use the before-image for UPDATEoperations, it is recommended to

set On UPDATE in the Store Changes Settings tab of the Replicate task settings to Store

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

after image only. Note that this should only be done if the Replicate task is dedicated for use

with Compose.

l

As Compose requires a full after-image to be able to perform Change Processing, the

following Replicate source endpoints are not directly supported (as they do not provide a full

after-image):

l

SAP HANA (log based)

l

Salesforce

Setting up the task

To define the task:

1. Open Qlik Replicate and in the New Task dialog, do one of the following:

l

To enable Full Load and Change Processing replication, enable the Full Load and

Store Changes options (the Apply Changes option should not be enabled).

l

To enable Full Load only replication, enable the Full Load replication option only.

l

To enable Change Processing replication only, make sure that only the Store Changes

option is enabled. Note that this option should only be selected if the Full Load tables

and data already exist in the landing zone.

l

To enable Change Processing for lookup tables that already exist in the landing zone

and are not part of the Compose model, enable the Apply Changes option only. Note

that such a task should be defined in addition to the Full Load and Store Changes

replication task described above. For more information on updating standalone lookup

tables, see

Using lookup tables that do not have a task for CDC mapping (page 211)

2. Open the Manage Endpoint Connections window and define a source and target endpoint.

The target endpoint must be the database where you want Compose to create the data

warehouse.

3. Add the endpoints to the Qlik Replicate task and then select which source tables to replicate.

4. This step is not relevant if you selected the Apply Changes or Full Load replication option

only. In the Task Settings' Store Change Setting tab, make sure that Store Changes in is set

to Change tables.

5. In the Task Settings’ Target Metadata tab, specify a Target table schema name.

6. If a Primary Key in a source table can be updated, it is recommended to turn on the DELETE

and INSERT when updating a primary key column option in Replicate's task settings'

Change Processing Tuning tab. When this option is turned on, history of the old record will

not be preserved in the new record. Note that this option is supported from Replicate

November 2022 only.

7. Run the task. Wait for the Full Load replication to complete and then continue the workflow in

Compose as described in the

Data warehouse project tutorial (page 106)

below and in

Adding

and managing data warehouse projects (page 35)

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

Replicate allows you to define global transformations that are applied to source/Change

tables during task runtime. The following global transformations, however, should not be

defined (as they are not compatible with Compose tasks):



Rename Change Table



Rename Change Table schema

5.2  Adding and managing data warehouse projects

This section describes how to add and manage a data warehouse project.

In this section:

l

Adding data warehouse projects (page 35)

l

Managing and monitoring projects (page 290)

l

Project settings (page 37)

l

Resetting projects (page 43)

l

Project deployment (page 44)

l

Migrating objects as CSV files (page 46)

l

Exporting and importing projects using the CLI (page 77)

l

Generating projects using the CLI (page 97)

l

Exporting project documentation (page 98)

l

Viewing and downloading DDL scripts (page 99)

l

Project versioning (page 308)

l

Creating a diagnostics package (page 310)

Adding data warehouse projects

Adding a new project is the first task you need to undertake in order to work with Qlik Compose.

There are two types of project:

l

Data Warehouse - for ingesting data from multiple sources and creating analytics-ready data

marts.

l

Data Lake - for ingesting data from multiple sources and moving it to a storage system for

analytics.

This topic guides you through the steps required to set up a data warehouse project. For

instructions on setting up a Data Lake project, see

Adding data lake projects (page 288)

You can set up as many projects as you need, although the ability to actually run tasks is

determined by your Compose license.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

Adding a Data Warehouse project

To add a new Data Warehouse project:

1. Click the New Project toolbar button.

The New Project wizard opens.

2. In the Project Name tab, specify the following and then click Next:

l

Name: The project name.

Project names cannot contain the following characters:

/\,&#%$@=^*+"'`~?<>:;[]{} as well as all non-printable characters (below

0x20). The project name can contain a single dot, but it cannot be the first

or last character.

l

Environment Type: Optionally, change the default environment type.

l

Environment Title: Optionally, specify an environment title.

For information about the environment settings, see

Environment tab (page 40)

The following names are reserved system names and cannot be used as project

names: CON, PRN, AUX, CLOCK$, NUL, COM1, COM2, COM3, COM4, COM5, COM6, COM7,

COM8, COM9, LPT1, LPT2, LPT3, LPT4, LPT5, LPT6, LPT7, LPT8 and LPT9.

3. Select Data Warehouse as your project type and then click Finish.

4. The project panels will be displayed.

5. Add at least one source database and a data warehouse as described in

Setting up Landing

Zone and Data Source connections (page 131)

and

Setting up a data warehouse connection

(page 110)

respectively.

6. Create a model as described in

Creating and managing the model (page 154)

7. Set up the data warehouse as described in

Creating and managing the data warehouse

(page 192)

8. Set up the data mart as described in

Creating and managing data marts (page 228)

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

Managing and monitoring projects

The table below describes the available project management options.

Project management actions are performed in the main Compose window. To switch

from a specific project to the main window, click the downward arrow to the right of the

project name and then select All Projects from the drop-down menu.

To Do this

Edit a project Any of the following:

l

Double-click the project.

l

Right-click the project and select Designer.

l

Select the project and then click the Open toolbar button.

Monitor a project Any of the following:

l

Right-click the project and select Monitor.

l

Double-click the project and select the Monitor tab on the right of

the console.

Create a

deployment

package

Any of the following:

l

Right-click the project and select Create Deployment Package.

l

Select the project and then select Create Deployment Package

from the Deployment toolbar menu.

See also:

Project deployment (page 44)

(Data Warehouse projects) and

Project deployment (page 297)

(Data Lake projects).

Delete a project Any of the following:

l

Right-click the project and select Delete.

l

Select the project and then click the Delete toolbar button.

View or change

user permissions

Right-click the project and select User Permissions.

Relevant for Data Warehouse projects only.

See also:

User permissions (page 384)

Project management procedures

Project settings

You can change the project settings according to your needs.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

To access the project settings:

1. Open your project as described in

Managing and monitoring projects (page 290)

2. Click the downward arrow to the right of the project name and select Settings from the drop-

down menu.

The Settings window opens, displaying the following tabs:

l

General tab (page 38)

l

Naming tab (page 39)

l

Environment tab (page 40)

l

Table creation modifiers tab (page 42)

General tab

In this tab, the following settings are available:

Miscellaneous

l

Generate DDL scripts but do not run them: By default, Compose executes the CREATE,

ADJUST and DROP statements immediately upon user request. When you select this option,

Compose will only generate the scripts but not execute them. This allows you to review and

edit the scripts before they are executed.

For example, if you want your data warehouse/storage tables to contain partitions, you will

need to edit the CREATE statement to create the partitions.

You can view, copy and download the DDL scripts as described in

Viewing and downloading

DDL scripts (page 99)

When this option is selected, you need to do the following to see the results:



After running the scripts, clear the metadata cache as described in Clearing

the data warehouse metadata cache (page 227).



When this option is selected, you need to press [F5] (i.e. refresh the page)

in order for the web console to display the updated list of tables. This can

be done either before running the scripts (recommended) or after running

the scripts. Note that until you refresh the browser, the information in the

web console will only be partially updated.

l

Ignore Mapping Data Type Validation: By default, Compose issues a validation error when a

landing table is mapped to a logical entity with a different data type. You can select this

option to allow the mapping of different data types. Note that you should only select this

option if you need to map landing table data types to compatible (though not identical)

logical entity data types.

l

Write metadata to the TDWM tables in the data warehouse:

When this option is selected (the default unless Amazon Redshift is the data warehouse),

Compose writes the metadata for the data warehouse tables to the following tables:

<schema>.TDWM_Tables and <schema>.TDWM_COLUMNS.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

Centralizing the metadata in two dedicated tables makes it easier for external metadata tools

to analyze the metadata. The metadata is also written to the local Compose repository, so

clearing this option (if performance issues are encountered) will not affect Compose

functionality in any way.

l

Do not display the default workflows in the monitor: Select this option if you want to

prevent the default workflows from being executed.

Dates

l

Lowest Date: The value stored in the "From Date" column. This is the date when the version

started.

l

Highest Date: The value stored in the "To Date" column. This is the date when the version

ended.

Current Time Convention

When a source record’s timestamp cannot be determined, select one of the following to use

instead:

l

Current time in UTC (the default for new projects)

To preserve backward compatibility when upgrading or deploying old projects,

local server time is the default.

Before changing this option, make sure that existing data will not be impacted.

l

Current time in server local time

For existing objects, Compose will not be able to determine a source record's timestamp if both of

the following are true:

l

The "From Date" columns are not mapped

l

The task is set up to perform Incremental Load

Naming tab

In this tab, you can change the default "From Date" and "To Date" column names, as well as the

prefixes and suffixes used to identify tables, views, and columns.

If you change the prefix or suffix of existing tables (e.g. data warehouse tables), you

need to drop and create the data warehouse and data mart tables.

Name Description

Suffix for Replicate

Change Tables

The suffix used to identify Replicate Change Tables in the landing

zone of the data warehouse.

Name management options

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

Name Description

Prefix for data

warehouse tables

The prefix used to identify tables in the Data Warehouse.

Prefix for data

warehouse views

The prefix used to identify views in the Data Warehouse.

Suffix for archived

Replicate Change Tables

The suffix used to identify archived Change Tables in the specified

database.

For more information on archiving Change Tables, see After applying

changes.

Prefix for data mart

tables

The prefix used to identify tables in the data mart.

Suffix for exception mart

tables

The suffix used to identify error tables in the data warehouse. These

tables contain data that was rejected by a data quality rule.

Suffix for hub tables The suffix used to identify hub tables in the Data Warehouse. Hub

tables contain History Type 1 columns. History Type 1 column do not

contain any version history as opposed to History Type 2 columns

that do.

Suffix for satellite tables The suffix used to identify satellite tables in the Data Warehouse.

Satellite tables contain History Type 2 columns. History Type 2

columns keep a history of the data version by adding a new row

whenever the data is updated.

"From Date" column

name

The name of the "From Date" column. This column is added to tables

that contain attributes (columns) with a History Type 2. The column

is used to delimit the range of dates for a given record version.

This name cannot be used in other columns.

"To Date" column name The name of the "To Date" column. This column is added to tables

that contain attributes (columns) with a History Type 2. The column

is used to delimit the range of dates for a given record version.

This name cannot be used in other columns.

Environment tab

In this tab, you can:

l

Specify information about your environment, part of which will be displayed as a banner at

the top of the window when you open the project.

l

Determine the number of database connection to open concurrently.

After providing the following information, click OK to save your settings:

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

l

Environment type:Select one of the following types according to your environment type:

Development, Test, Acceptance, Production, Other. This information will not be displayed

in the banner.

l

Environment title: Specify a title for your environment. The title will be displayed in the

banner at the top of the console.

l

Project title: Specify a title for your project. The project title will be shown in the console

banner. If both an Environment Title and a Project Title are defined, the project title will be

displayed to the right of the environment title.



The Project title option requires Compose August 2021 Patch Release 12 or

later.



When a project is deployed to a new environment, the environment title and

environment type in the new environment will not be overridden.

The following image shows the banner with both an Environment title and a Project title:



The banner text is shown without the Environment title and Project title labels.

This provides greater flexibility as it allows you add any banner text you like,

regardless of the actual label name. For example, specifying

Project

owner: Mike Smith

in the Project title field, will display that text in the banner.

Creating or Dropping Data Warehouse Tables

Limit the number of database connections to: The higher the number of database connections,

the more data warehouse tables Compose will be able to create or drop in parallel. While increasing

the default should improve performance, it might also impact other database applications. It is

therefore not recommended to increase the default unless you encounter performance issues.

The environment properties can be

exported and imported

to a new project, but cannot

be imported to an existing project.

Task recovery

You can set SQL state classes and error codes, on the occurrence of which, a task will be retried.

You can set the following parameters:

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

l

Maximum retry count: The number of times to retry a task before exiting with failure.

Increasing the number of retries will impact system resources. Therefore, only increase the

default value if you expect tasks to recover after the default number of retries.

l

Interval between retry attempts (sec): The time to wait between retry attempts. Increasing

the interval will consume more system resources. Therefore, only increase the default value

if it is critical that the task recover as soon as possible.

l

Retry on these SQL state classes: The default is 08 (connection exceptions). You can add

additional classes as desired. Classes should be separated with a comma.



Example: 08,22,2F



l

Retry also on these error codes: The default is 1205 (which occurs when a table is locked

by another process). You can add additional error codes as desired. Error codes should be

separated with a comma.



Example: 1205,2020,233

Limitations and considerations:

l

ODBC statements comprise a small part of the task execution sequence. However, as the

task retry mechanism is JDBC-based, ODBC statements will not be retried even if the

specified SQL state/error code is encountered.

Task and Workflow Information Retention

You can set the maximum number of runs to keep task and workflow logs and messages. The

default is 100. Task information includes logs, the number of inserted/updated rows per table,

errors, and various other runtime messages. If you find that the number of accumulated logs and

messages is degrading performance, reducing this value might help.

Table creation modifiers tab

By default, Compose creates tables in the data warehouse using the standard CREATE TABLE

statement. However, organizations often need tables to be created with custom properties for

better performance, special permissions, custom collation, and so on. For example, in Microsoft

Azure Synapse Analytics, it’s possible to create a table as a HEAP, which is optimized for smaller

tables. By default, Compose creates tables in Microsoft Azure Synapse Analytics as a CLUSTERED

COLUMNSTORE INDEX, which offers the best overall query performance for large tables.

In the Table creation modifiers tab, you can append table creation modifiers as SQL parts to the

CREATE TABLE statement. You can set table creation modifiers for both data warehouse tables and

for data mart tables. In the data warehouse, separate modifiers can be set for Hub and Satellite

tables while in the data mart, separate modifiers can be set for fact and dimension tables. Once set,

all tables will be created using the specified modifiers, unless overridden at the entity level.

To set table creation modifiers:

1. Select the Custom option for any of the available table types.

2. Click the Edit button to open the Table Creation Modifier editor.

3. Enter the SQL parts you want to append to the CREATE TABLE statement.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

4. Optionally, but strongly recommended, validate the SQL in an external validation tool that

supports your specific database and version.

Compose does not provide any way of validating your SQL. Therefore, make sure

to validate the SQL before deploying in a production environment.

5. Click OK to close the editor and save your SQL.

Limitations

If you change an existing table creation modifier, you will not be prompted to adjust affected tables

when validating the model. If you want to apply the change to existing tables, dropping and

recreating all tables might not be an issue in a development environment. However, in a production

environment (where dropping and recreating all tables might not be a viable option), you will need

to adjust the tables outside of Compose.

Example of a Valid Table Creation Modifier

In the following example, the Compose CREATE TABLE statement (rows 1-5) is appended with an

SQL part instructing Compose to create the table as a HEAP (row 6).

CREATE TABLE MyTable

(

column1 integer,

column2 varchar(50),

)

WITH (HEAP)



For an explanation of how to define table creation modifiers for individual data warehouse tables,

see

Defining Table Creation Modifiers (page 181)

For an explanation of how to define table creation modifiers for individual fact tables, see

Example

of a Valid Table Creation Modifier (page 245)

For an explanation of how to define table creation modifiers for individual dimension tables, see

Example of a Valid Table Creation Modifier (page 250)

Resetting projects

You can reset projects as required. This can be useful in the project development stage as it allows

you to easily delete unwanted project elements.

Be careful not to reset a project and delete data in a production environment.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

To reset a project:

1. Open your project as described in

Managing and monitoring projects (page 290)

2. Click the downward arrow to the right of the project name and select Reset Project from the

drop-down menu.

The Reset Project window opens.

3. Select which elements to reset according to your project type.

l

Model (Entities, Relationships, Attribute Domains), mappings, and data mart

definitions

For more information on models, see

Creating and managing the model (page 154)

l

Reusable transformations

For information on the reusable transformations, see

Defining reusable

transformations (page 191)

l

Global mappings

For more information on global mapping, see

Managing global mappings (page 161)

l

Data warehouse and data mart tables

For more information on data warehouses and data marts, see

Creating and managing

the data warehouse (page 192)

and

Creating and managing the data warehouse (page

192)

respectively.

l

Command tasks

For more information on data warehouses and data marts, see

Creating and managing

command tasks (page 263)

l

DDL Scripts

For more information on DDL scripts, see

Project settings (page 37)

and

Viewing and

downloading DDL scripts (page 99)

l

Drop Archive Tables

For more information on Archive Tables, see

Defining landing zones (page 140)

4. Click Reset Project and then click Yes when prompted to confirm your request.

Project deployment

Project deployment packages can be used to back up projects or migrate projects between

different environments (e.g. testing to production). As a deployment package is intended to be

deployed in a new environment, it contains the Data Warehouse and data source definitions, but

without any passwords. The deployment package also does not contain any data from the Data

Warehouse or data mart, only the metadata. The deployment package also contains the project

metadata and mapping information, which should be consistent with the landing zone tables in the

new environment.

For a complete list of objects contained in the deployment package, see

Exporting a project (page

78)

Project deployment should always be unidirectional (for example, from test to

production), and not the other way around.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

Creating deployment packages

This section explains how to create a project deployment package.

To create a deployment package

1. Choose one of the following methods:

l

In the main Compose window, right-click the desired project and select Create

Deployment Package from the context menu.

l

In the main Compose window, select the desired project. Then, click the Deployment

toolbar button and select Create Deployment Package from the drop-down menu.

l

In the project window, select Deployment > Create Deployment Package from the

project drop-down menu.

The Create Deployment Package - <Project_Name> window opens.

2. Select Exclude environment variable values to exclude all of the environment specific

settings from the deployment package (the default). Leave this option selected if the project

you are exporting includes environment variables and the settling in the target environment

are significantly different from the source environment. The variables will be replaced with

their default values (either empty or the default Boolean value).

See also:

Working with environment variables (page 87)

3. Provide a Version number and a Description in the designated fields and then click OK.

A ZIP file containing a JSON file (i.e. the project settings) and a readme.txt file will be saved to your

browser's default download location. The ZIP file name is in the following format: <Project_Name>_

deployment_<Date>__<Time>.zip

The readme.txt file contains the following information about the deployment package: project

name, export date, exporter user name, deployment version, and description.

Deploying packages

This section explains how to deploy a project deployment package. This section explains how to

deploy a project deployment package. You can only deploy packages to an existing project.

Therefore, before deploying a project, create a new project with the user name and password

required for connecting to the Data Warehouse database and the Landing Zone database (if

defined) in the new environment. In addition, the Landing Zone databases in the target project must

have the same display name (defined in the Compose console) as the corresponding databases in

the source project. Note that as database settings are usually environment specific, the database

settings in the target project will not be overwritten by those of the source project.

Deploying a project between different database types is not supported. For example,

you cannot create a package in SQL server and deploy it to an Oracle database.

When deploying, Compose does not override existing connection parameters. This enables you to

easily migrate projects from test to production, for example, without needing to change user

names, passwords or IP addresses.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

If preferred, you can create an empty project and provide the required credentials after

the deployment completes. In this case, an error message prompting you for the missing

credentials will be displayed after the deployment completes.

To deploy a project:

1. Copy the ZIP file created in

Creating deployment packages (page 45)

to a location that is

accessible from the Compose machine.

2. Open Compose and choose one of the following methods:

l

In the main Compose window, select the desired project. Then, click the Deployment

toolbar button and select Deploy from the drop-down menu.

l

In the project window, select Deployment > Deploy from the project drop-down

menu.

The Deploy window opens.

3. Either drag and drop the file on the window.

Click Select and browse to the location of the deployment package. In the Open window,

either double-click the deployment package ZIP file or select the file and click OK.

The package details will be displayed.

4. Click Deploy to deploy the package. When prompted to replace the existing project, confirm

the operation. The project will be deployed.

When deploying a project defined with multiple Replicate Servers to any of the following:



A project without any Landing Zone databases



A project which is missing one or more Landing Zone databases defined in the

source project

Then the Landing Zone settings from the source project will be used, but the missing

databases will be created without a password and Replicate Server. These will need to

be configured manually.

Migrating objects as CSV files

Relevant to Data Warehouse projects only.

Migrating Compose objects as CSV files, provides a level of granularity that is not available when

using the standard project export and deployment options. Instead of importing the entire project,

you can import specific objects and then apply periodic updates as needed.

The following objects can be migrated:

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

l

Model objects: Entities, Attributes, Attributes Domain, Relationships, Reusable

transformations, and Reusable transformation parameters

l

Mappings and Mapping Metadata

l

Data Mart objects: Fact tables, Fact attributes, Star schemas, Dimensions, and Dimension

attributes

In this section:

l

Overview (page 47)

l

Migrating models (page 49)

l

Migrating mappings (page 55)

l

Migrating tasks (page 58)

l

Migrating data marts (page 63)

l

Migrating reusable transformations (page 69)

l

Commands for exporting/importing objects (page 70)

l

Commands for comparing and applying objects (page 73)

Overview

The ability to export object definitions to a CSV file and then import them to another environment

provides many benefits, enabling:

l

Migration of data from a custom database table and/or Excel sheets to Compose

l

Data to be reviewed by business analysts who are not able to (or do not want to) access

Compose

l

Synching with third-party tools that output data in CSV format

l

Comparison of versions in order to review changes

l

Resolving of object-specific issues in a development environment and then deploying to the

production environment, even when they are not completely in sync

l

Sharing of resources between projects in the same environment

l

Granular version management of specific objects such as mappings

Manually typing these definitions into Compose would be a laborious and time-consuming

undertaking; using the CLI however, this can be done in a matter of minutes. Following the initial

import, customers who need to apply selective updates to the target environment (such as adding

attributes with their descriptions), can do so using the Compare and Apply CLI commands.

A typical workflow would be as follows:

1. Run the export command to output the source object(s) (to CSV files).

2. Run the import command to bring the objects into the target environment.

3. Following changes to the source environment, run the export command to output the source

object(s) to (CSV files).

4. Run the Compare command to see the differences between the exported objects and the

corresponding target objects.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

Alternatively, run the Compare command on the source environment to see the differences

between previous and current source project versions; then determine which changes needs

to be migrated to the target project. This approach is useful if changes were made directly to

the target project as it allows you to retain the custom changes while still applying changes

to other objects.

5. Review the changes and make any edits as necessary.

6. Run the Apply command to apply the changes to the target environment.

7. Periodically repeat steps 3-6 as necessary.

An understanding of the CSV file structure and their impact on the target environment is crucial, not

only for customers who wish to create these files manually, but also for ensuring the import/apply

operations succeed with the expected results. For this reason, this section first discusses the CSV

file structure of the supported objects and only then provides instructions for performing the actual

CLI operations.



Only import of new objects is supported.



UPDATEs and DELETEs will be ignored by the Apply command (which supports

ADD operations only), but can still be applied manually if needed.

Valid CSV file formats

CSV files must be in a valid format; otherwise, the import/compare/apply operation will fail.

CSV formatting rules:

l

Fields must be separated with a comma.

l

The first line must be a header line which contains the column names.

l

The escaping convention is similar to Excel:

l

If a field contains a comma, then the comma must be wrapped with quotation marks, for

example: "a,b"

l

If the value contains a quotation mark, then ithe quotation mark must be doubled. For

example:

"Mike ""The Hammer"" Smith"

l

If the value contains a new line, then it must be wrapped with double quotes. For example:

"Field owned by Mike.

Comment added by Shelley."

Stored objects

Several objects may have multi-lines, commas, and other such complexities.

Expressions, for example, are stored as two fields:

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

First field - expression value:

Example:

${x1}*${x2}

Note: The expression may need to be escaped.

Second field - semicolon parameters mappings:

Example:

x1:unit price;x2:quantity

Migrating models

Migrating a Compose Model allows you to:

l

Import existing column definitions (i.e. definitions stored independently of Compose) to a

Compose project

l

Reuse the same Model across several projects or Compose installations

Model objects

A Compose Model consists of the following objects:

l

Entities (

entities.csv

)

l

Attributes (

attributes.csv

)

l

Attributes Domain (

attributesDomain.csv

)

l

Relationships (

relationships.csv

)

During the export process, each of these objects is exported to a separate CSV file.

You can either import the Model in its entirety or only specific elements, according to your needs.

You can also manually create a CSV file containing a Model element (or edit an existing file) and

then import it to a Compose project.



The Model must be valid before you can export it to or import it from a CSV file.

For details, see Validating the model (page 163).



CSV files must be in a valid format. For details, see

Valid CSV File Formats



Only a user with Model privileges can import the Model.

Non-privileged users can import just the mappings. For details, see the SCOPE

parameter in the

command for importing a model



Replacing an existing object is not supported. For example, if the Products entity

already exists in the Model, you cannot import an entities.csv file that contains an

entity called Products.

Guidelines for exporting a Compose model

Note the following:

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

l

For Boolean fields, accepted values are True/False

l

Data type is the Compose logical type

l

The order of writing the attributes is according to the ordinal in the entity. Primary Keys will

be shown first, even if they were not first in the source model

l

Attribute domain and entity order is alphabetical in the CSV

l

Relationship order is by entity name (alphabetical) and according to their order in the entity

Guidelines for importing a Compose model

Note the following:

l

For boolean fields, accepted values are True/False

l

Column order has no meaning; only column names, which are case insensitive, unless

defined differently

l

If the

entities.csv

file is missing, the entities can be inferred from the

attributes.csv

file (with

no description). In such cases, duplicate objects are verified to be the same and added only

once. For example, if there are several rows with entity name myEntity, the entity will only be

added once

l

Data type is the Compose logical type

l

Relationship details override the underlying attributes information (e.g. history type, is key,

etc.)

l

When importing the

attributes.csv

file, a new entity will not be created if wasn’t already

created in the

entities.csv

file, and its attributes all have relationships to other entities. In

such cases, you should create the entity manually or add it to the

entities.csv

file

l

When importing the

relationships.csv

file, existing attributes will be replaced if:

child attribute prefix+attribute name = relationship_prefix+parent_attribute_name_

prefix+parent_attribute_name

For example, the attribute named CustomerDesignatedID will be replaced by the relationship

where:

l

ID is the name of the attribute in the parent entity and

l

Customer is the prefix of the relationship and

l

Designated is the prefix of the parent attribute.

Note that attributes marked as relationships will be skipped when imported from

attributes.csv

as they must derive their data type from the Attributes Domain.

Entities CSV file format

Column Name Required If column/value is missing Comments

Entity Name Yes Reject Case insensitive

Entity Description No Empty -

Entity CSV mapping rules

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

Attributes CSV file format

If Attribute Domain is missing in the attributes.csv file, there must be a data type.

Attribute domains that differ but have the same name will be appended with the suffix _

01.

Column Name Required

If column is

missing

If value is

missing

Comments

Entity Name Yes Reject Reject Case insensitive

Attribute Name Yes Reject Reject Case insensitive

Description No Empty Accept -

Is Key No No attributes will

be defined as

keys. In such a

case, validation

will fail as at least

one key needs to

be defined.

No Key On import, Compose

does

not

validate that each entity

has at least one key attribute

(required). If you import

entities without key

attributes, then you must

define a key attribute in

Compose after importing the

entity. Otherwise, Model

validation will fail.

Attribute

Domain

No If it is missing,

Data Type must

exist and

attribute

domains will be

built or used the

same as they are

during discovery.

Reject The Attribute Domain name

or the word "Relationship"

Data Type No If it is missing,

the Attribute

Domain must

exist. An error

will be returned if

the data type

does not match

the attribute

domain or if both

exist.

Reject Use combined syntax:

Varchar(50)

Decimal(10,2)

Attribute CSV mapping rules

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

Column Name Required

If column is

missing

If value is

missing

Comments

Prefix No Empty Accept (an

empty feld

means there

will be no

prefix)

History Type No Key - Type 1, Not

key - type 2

Reject Values are Type 1 or Type 2.

Yes (Type 2) and No (Type 1)

are also allowed.

Satellite/Hub No Key is Hub; other

attributes are

SAT 1

Reject Hub/1/2/3

Expression No No expressions

in any attribute

expression in

any attribute

Expresssion

Params

No All attribute-

parameter

mappings are

trivial (same

name)

All attribute-

parameter

mappings

are trivial

(same name)

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

Column Name Required

If column is

missing

If value is

missing

Comments

Add after No Not relevant. Not relevant. Instructs Compose to add

the attribute after a specific

attribute. The column is only

relevant when adding

attributes. When the column

is empty, the corresponding

attributes should be added

at the end (according to their

order in the CSV file).

Note that it may refer to any

attribute that is defined

above the current row.

For example, assuming a

record contains the

attributes address, height,

and weight, specifying the

following:

school, add after=weight

ID, add after=school

email, add after=empty

Will result in the following

order: address, height,

weight, school, ID, email

Attributes Domain CSV file format

Column

Name

Required

If column is

missing

If value is missing Comments

Name Yes Reject Reject Case

insensitive

Description No Empty The specific attribute value may

be empty as well.

Data Type No Reject Reject Use combined

syntax:

Varchar(50)

Decimal(10,2)

Attributes Domain CSV mapping rules

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

Relationships

Column Name Required If column is missing

If value is

missing

Comments

Child Entity Yes Reject Reject This is the more

detailed entity

(e.g.

OrderDetails).

Case insensitive.

Parent Entity Yes Reject Reject This is the less

detailed entity

(e.g. Products).

Case insensitive.

Prefix No Empty

If there are several

relationships from the

same source to the same

target, a prefix must be

added.

If all the

originating

attributes

have the same

prefix, use it

here.

A relationship

may not have

a prefix to its

underlying

attributes.

The specific

attribute value

may be empty

as well.

Position After No Add the relationships to

the end. Relationships are

ordered according to their

order in the file.

Same as

missing

column

The attribute

name or

relationship to

position it after or

”0” to position it

first.

Description No Empty Specific

attribute may

have empty

value as well

Relationships CSV mapping rules

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

Column Name Required If column is missing

If value is

missing

Comments

Is Key No No attributes will be

defined as keys. In such a

case, validation will fail as

at least one key needs to

be defined.

No key -

History Type No Key: Type 1

Non-key: Type 2

Reject Values are Type 1

or Type 2.

Yes (Type 2) and

No (Type 1) are

also allowed.

Satellite/Hub No Key: Hub

Other attributes: SAT 1

Reject Hub/1/2/3

Migrating mappings

Migrating mappings allows you to:

l

Export mapping metadata and mappings from a Compose project to CSV files. Mapping

metadata will be exported to

mappingsMetadata.csv

while mappings will be exported to

mappings.csv

. The former shows the table mappings while the latter show the column

mappings.

l

Import new mappings that do not exist in the current Compose project.

l

Reuse the same mappings across several projects or Compose installations.

Mappings export guidelines

When exporting mappings and mapping metadata from Compose, it is important to note the

following:

l

The export of mappings is allowed for users with the Viewer security role.

l

The order of writing the mapping metadata is according to metadata name alphabetically

(e.g. Map_Orders appears after Map_Customers).

l

The order of writing a mapping is according to target columns (same as Model ordinal).

l

Source columns which are not mapped to anything will not appear in the exported file.

l

All target columns will appear in the mappings even if they were not mapped.

Mappings import guidelines

When importing mappings and metadata to the Model, it is important to note the following:

l

If required, you can import one CSV file at a time:

mappingsMetadata.csv

mappings.csv

l

Column order has no meaning; only column names (case insensitive).

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

l

When importing metadata, Compose validates that the targets exist in the Model. Source

columns are not validated on import.

l

If the source schema, table, view or query don't exist, they will be validated after the import.

l

Importing mappings will fail in the following scenarios:

l

If the target entity doesn't exist in the Model.

l

If the compose database object doesn't exist.

l

If the mapping attribute doesn't exist in the Model.

Valid CSV file formats

The CSV files must be in a valid format. For more detailed information, see the notes in

Valid CSV

file formats (page 48)

Mapping metadata CSV file format

Column

Name

Required

If column is

missing

If value is

missing

Comments

Name Yes Reject Reject Case insensitive

Landing

Zone

Database

Yes Reject Reject The Compose "source database"

name (excluding the word "landing")

Schema No If there is no

Schema

column,

either the

default

schema will

be used or

none (as

some

databases

do not have

separate

schemas)

If no schema

is specified,

either the

default

schema will

be used or

none (as

some

databases

do not have

separate

schemas)

Source Type No Table Reject Table/View/Query

Source

Object

Yes Reject Reject If Source Type is Table or View, the

Source Object is its name.

If Source Type is Query, the rules

and limitations described in

Valid

CSV file formats (page 48)

will be

applied.

CSV metadata mapping rules

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

Column

Name

Required

If column is

missing

If value is

missing

Comments

Target

Entity

Yes Reject Reject -

Filter No No filter No filter



Filter

Params

No Same name

for

attribute-

parameter,

expression

below

Same name

for attribute-

parameter,

expression

below

Example of Semicolon parameters

mappings:

x1:unit >price;x2:quantity

Mapping

Name

Yes Reject Reject Case insensitive

Target

Column

Yes Reject No mapping

if there is

also no

expression

or lookup.

Case insensitive

Mapping

Type

No Field

mapping

Allow the

field to be

empty if

there is no

field

mapping, no

expression,

and no

lookup.

Field Mapping /Expression/Lookup

Field

Mapping

No Reject No mapping

to this

attribute.

The name of the source field, or

empty if this field is not mapped.

Expression No No

expression

expression in

this

attribute.

Example of an expression value:

${x1}*${x2}

Expression

Params

No All attribute-

parameter

mapping is

trivial (same

name)

expression in

that attribute

Example of Semicolon parameters

mappings:

x1:unit >price;x2:quantity

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

Column

Name

Required

If column is

missing

If value is

missing

Comments

Lookup

Landing

Database

No No lookups No

expression in

that attribute

Lookup Landing Database name

Example:

Northwind on MySQL

Lookup

Table

No No lookups No

expression in

that attribute

Lookup table or view name in schema

table format.

Example:

Schema1.Orders

Lookup

Type

No No lookups No

expression in

that attribute

Table or View

Lookup

Condition

Value

No No lookups No

expression in

that attribute

Lookup

Condition

Params

No No lookups No

expression in

that attribute

Include Lookup/Landing.

Example:

x:$Lookup$.a;y:$Landing$.CustomerID

Lookup

Result Value

No No lookups No

expression in

that attribute

Lookup

Result

Params

No No lookups No

expression in

that attribute

Same format as the lookup condition

parameters.

Migrating tasks

You can migrate data warehouse tasks, data mart tasks, and custom ETLs (tasks) from one

environment to another, while preserving custom objects in the target environment. This is

especially useful for customers who wish to incrementally updated production environments with

new versions from the test environment.

The following files will be exported:

For the data warehouse:

l

<specified export folder>/customEtl.csv - Contains details of any (enabled or disabled)

custom ETLs defined for the task.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

l

<specified export folder>/taskCustomEtl.csv - Lists any

enabled

custom ETLs defined for

the data warehouse task.

l

<specified export folder>/taskSettings.csv - Contains details of the task settings defined

for each of the data warehouse tasks.

l

<specified export folder>/tasks.csv - Lists the data warehouse tasks.

l

<specified export folder>/taskDataWarehouseTables.csv - Lists the data warehouse tables

and properties.

l

<specified export folder>/taskMappings.csv - Lists the mappings used in the task.

l

<specified export folder>/SQL/DW_Custom_ETL_<custom ETL name>.SQL - One SQL file

for each custom ETL.

For each data mart:

l

<specified export folder>/<data mart name>/customEtl.csv - Contains details of any

custom pre-loading or post-loading ETLs defined for the data mart task

l

<specified export folder>/<data mart name>/taskSettings.csv - Contains details of the task

settings

l

<specified export folder>/<data mart name>/SQL/DM_Custom_ETL_<custom ETL

name>.SQL - One SQL file for each custom ETL

Considerations

Export considerations

l

Parameters will be written to CSV files in alphabetical order (as they appear in the web

console).

Import considerations

l

Importing tasks or custom ETLs will override any existing objects with the same names.

l

Importing logical entities or mappings that do not exist in the target project will result in

failure.

Valid CSV file formats

The CSV files must be in a valid format. For more detailed information, see the notes in

Valid CSV

file formats (page 48)

Data warehouse CSV file formats

Tasks file

The tasks.csv file consists of one row per task.

Header

name

Mandatory

If column is

missing

If column exists but value

is empty

Comments

Task name Yes Reject Reject Case

insensitive

Tasks

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

Header

name

Mandatory

If column is

missing

If column exists but value

is empty

Comments

Description No Empty Empty -

Type No Full Load Only Reject Full Load Only

Change Tables

Only

Task settings file

The taskSettings.csv file consists of one row per setting.

Header

name

Mandatory

If column is

missing

If column exists but value is

empty

Comments

Task Name Yes Reject Reject Case

insensitive

Setting

Name

Yes Reject Reject Case

insensitive

Setting

Value

Yes Reject Reject -

Data warehouse task settings

Task entities file

The taskDataWarehouseTables.csv file consists of one row per entity.

Header name Mandatory

If column

is missing

If column

exists but

value is

empty

Comments

Task Name Yes Reject Reject Case

insensitive

Entity Name Yes Reject Reject Case

insensitive

Handle duplicates Yes Reject Reject Boolean

Task logical entities

Task mappings file

The taskMappings.csv consists of one row per task for each mapping that is used in the task.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

Header

name

Mandatory

If column is

missing

If column exists but value is

empty

Comments

Task Name Yes Reject Reject Case

insensitive

Mapping

Name

Yes Reject Reject Case

insensitive

Task mappings

Custom ETL file

The customEtl.csv consists of one row for each custom ETL defined for the task (Pre-Loading,

Multi-Table, Single Table, or Post-Loading), regardless of whether or not the ETL is enabled.

Header name Mandatory

If column

is missing

If column

exists but

value is empty

Comments

Name Yes Reject Reject Case insensitive

Description No Empty Empty -

Type Yes Reject Reject Pre Loading ETL, Multi Table

ETL, Single Table ETL or Post

Loading ETL

Entity Yes Reject Reject Relevant only for single table

ETL

Sequence

Number

Yes Reject Reject Valid values are positive integer

numbers

Execute as

Stored

Procedure

Yes Reject Reject Boolean

Data warehouse custom ETL



For each custom ETL, Compose will export/import an SQL file to:

<specified export folder>/SQL



The file name will be DW_Custom_ETL_<custom ETL name>.SQL



If you wish to edit the file name, make sure that it only contains the following

characters: A-Z, 0-9, underscore (_), or space. On import, any other character will

be replaced with an underscore.

Task custom ETL file

The taskCustomEtl.csv file consists of one row for each enabled custom ETL used in the data

warehouse task.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

Header name Mandatory

If column

is missing

If column

exists but

value is

empty

Comments

DWH Task Name Yes Reject Reject Case

insensitive

Custom ETL Name Yes Reject Reject Case

insensitive

Data warehouse task custom ETL

Data mart CSV file formats

Task settings file

The taskSettings.csv file consists of one row per setting.

Header

name

Mandatory

If column is

missing

If column exists but value is

empty

Comments

Setting

Name

Yes Reject Reject Case

insensitive

Setting

Value

Yes Reject Reject -

Data mart task settings

Custom ETL file

The customEtl.csv file consists of one row per custom ETL (Pre Loading ETL or Post Loading ETL).

Header name Mandatory

If column

is missing

If column exists

but value is empty

Comments

Name Yes Reject Reject Case insensitive

Description No Empty Empty -

Type Yes Reject Reject Pre Loading ETL or

Post Loading ETL

Active Yes False False Boolean

Sequence

Number

Yes Reject Reject Valid values are

positive integer

numbers

Execute as

Stored

Procedure

Yes Reject Reject Boolean

Data mart custom ETL

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects



For each custom ETL, Compose will export/import an SQL file to:

<specified export folder>/<data mart name>/SQL



The file name will be DM_Custom_ETL_<custom ETL name>.SQL



If you wish to edit the file name, make sure that it only contains the following

characters: A-Z, 0-9, underscore (_), or space. On import, any other character will

be replaced with an underscore.

Migrating data marts

You can migrate data marts from one environment to another, while preserving custom objects in

the target environment.

The following diagram shows a data mart model:

Exporting data marts

When you export data marts, a separate folder containing the CSV files will be created for each

data mart:

Example:

datamarts.csv

datamart1\facts.csv

datamart1\FactDimensionsLinks.csv

datamart1\dimensions.csv

datamart1\factattributes.csv

datamart1\dimensionattributes.csv

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

datamart2\facts.csv

datamart2\factdimensions.csv

datamart2\dimensions.csv

datamart2\factattributes.csv

datamart2\dimensionattributes.csv

Data marts CSV

The

datamarts.csv

consists of one row per data mart.

Header

name

Required

If column is

missing

If column exists but

value is empty

Comments

Data Mart

Name

Yes Reject Reject Case insensitive

Description No Empty Empty -

Table Prefix No Empty Empty Default for future fact and

dimension creation

View Prefix No Empty Empty Default for future fact and

dimension creation

Data marts CSV metadata mapping

When exporting a data mart, the following objects will not be included: View schema,

database name, and schema name. As these objects are environment-specific, they

need to be set them up manually after importing the data mart to the target environment

(unless you wish to user the defaults from the data warehouse).

Facts CSV

The

facts.csv

consists of one row per fact table.

Header

name

Required

If column is

missing

If column

exists but

value is

empty

Comments

Name Yes Reject Reject Case insensitive

The name corresponds to the fact

dimension name

Description No Empty Empty -

Facts CSV metadata mapping

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

Fact Type No On ADD:

Transactional

On ADD:

Transactional

Transactional, Aggregated, or State

Oriented

Fact Table

Name

Yes Reject Reject -

Fact View

Name

No Empty Empty Empty means that no view is created

Transaction

Date

*No If mandatory,

reject

If mandatory,

reject

Mandatory for transactional and

aggregated facts

Ignored for state-oriented facts

Includes full path with dot notation;

for example, in AdventureWorks it

might be:

OrderDetail.OrderHeader.ModifiedD

ate

Source Filter No No filter No filter Filter on the source columns (which

would eventually translate to an SQL

"where" statement)

Must be formatted as an expression

Source Filter

Params

No Same name

for attribute-

parameter,

similar to

expression

below

Same name

for attribute-

parameter,

similar to

expression

below.

Aggregation

Filter

No No filter No filter Filter on the aggregated columns

(which would eventually translate to

an SQL "having" statement)

Must formatted as an expression

Aggregation

Filter

Params

No Same name

for attribute-

parameter,

expression

below

Same name

for attribute-

parameter,

expression

below

Root entity Yes Reject Reject The root entity used. For example, if

the fact is a denormalization of order

details and orders, it will contain

"orders"

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

Fact As

Type 1

No Enable the

option

Enable the

option

Boolean: Accepts the values TRUE

or FALSE

For an explanation of this option,

see

Editing star schemas (page

241)

Dimensions CSV

The

dimensions.csv

consists of one row per dimension.

Header

name

Required

If column is

missing

If column

exists but

value is

empty

Comments

Name Yes Reject Reject Case insensitive

Description No Empty Empty -

Dimension

Table Name

Yes Reject Reject -

Dimension

View Name

No Empty Empty Empty means that no view is

created

History Type No Type 2 Type 2 Type 1/ Type 2

Source Filter No No filter No filter Filter on the source columns

(which would eventually

translate to an SQL "where"

statement)

Must be formatted as an

expression

Source Filter

Params

No Same name for

attribute-

parameter, like

expression

below

Same name for

attribute-

parameter, like

expression

below

Root entity Yes Reject Reject The root entity used. For

example, if the fact is a

denormalization of order

details and orders, it will

contain "orders"

Dimensions CSV metadata mapping

Fact dimensions CSV

The

FactDimensionsLinks.csv

consists of one row per dimension usage in the fact.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

Header name Required If column is missing

If column exists

but value is

empty

Comments

Fact Name Yes Reject Reject Case

insensitive

Dimension

Name

Yes Reject Reject Case

insensitive

Referenced

data mart

No The data mart does not

contain a referenced

dimension.

Means that it is not

a referenced

dimension.

Referenced

dimension

name

Only for

referenced

dimensions.

The data mart does not

contain a referenced

dimension.

Means that it is not

a referenced

dimension.

Name in the

referencing

data mart.

Fact dimensions CSV metadata mapping

Fact attributes CSV

The

factattributes.csv

consists of one row per attribute and includes the OID attributes as well.

On export, the order is determined by the attributes order. On import, the order is determined by

the read order.

Header name Required

If column is

missing

If column

exists but

value is

empty

Comments

Star schema

Name

Yes Reject Reject Case insensitive

Attribute

Name

Yes Reject Reject Case insensitive

Entity Path No Treat as empty Treat as

empty

Case insensitive.

If the column is directly

mapped to a data

warehouse, the field will

contain the model entity path

(for example:

Orders.Customers).

An empty field means that

the entity path should be

calculated using an

expression

Fact attributes CSV metadata mapping

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

Header name Required

If column is

missing

If column

exists but

value is

empty

Comments

Description No Empty A specific

attribute may

have empty

value as well.

Data Type Yes Reject Reject Use combined syntax:

Varchar(50) Decimal(10,2)

Aggregation No No aggregation

columns (this

will return an

error if the Fact

is aggregated).

aggregation

columns.

Empty or

SUM/COUNT/MAX/MIN/COUNT_

DISTINCT

Expression No No expressions

in any attribute.

No expression

in that

attribute.

See

Stored objects (page

48)

Expression

Params

No All attribute-

parameter

mapping is

trivial (same

name)

All attribute-

parameter

mapping is

trivial (same

name)

See

Stored objects (page

48)

Dimension attributes CSV

The

dimensionattributes.csv

consists of one row per dimension attribute in a fact dimension. These

may also include Date or Time dimensions (e.g. Customer.ModifiedDate).

On export, the order is determined by the attributes order. On import, the order is determined by

the read order.

Header

name

Required

If column is

missing

If value is

missing

Comments

Dimension

Name

Yes Reject Reject Case insensitive

Attribute

Name

Yes Reject Reject Case insensitive

Dimension attributes CSV metadata mapping

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

Header

name

Required

If column is

missing

If value is

missing

Comments

Entity Path No Treat as

empty

Treat as

empty

Case insensitive.

If the column is directly mapped

to a data warehouse, the field will

contain the model entity path (for

example:

Orders.Customers

)

Am empty field, means that the

entity path should be calculated

using an expression

Description No Empty Specific

attribute

may have

empty value

as well

Data Type Yes Reject Reject Use combined syntax: Varchar

(50) Decimal(10,2)

Expression No No

expressions in

any attribute

expression in

that

attribute.

See

Stored objects (page 48)

Expression

Params

No All attribute-

parameter

mapping is

trivial (same

name)

All attribute-

parameter

mapping is

trivial (same

name)

See

Stored objects (page 48)

Migrating reusable transformations

You can migrate reusable transformations from one environment to another, while preserving

custom objects in the target environment.

For information on reusable transformations, see

Defining reusable transformations (page 191)

Reusable transformations

The

ReusableTransformation.csv

file includes a row per reusable transformation parameter and is

described in the table below. Note that some reusable transformations may have no parameters.

Header name Required If column is missing If value is missing Comments

Name Yes Reject Reject Case insensitive

Reusable transformations CSV mapping rules

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

Header name Required If column is missing If value is missing Comments

Category No On ADD: General On ADD: General Case sensitive

Description No Empty Empty

Expression No On ADD: Empty On ADD: Empty

Reusable transformations parameters

The

ReusableTransformationParams.csv

file is described in the table below.

Header

name

Required

If column

is missing

If value is

missing

Comments

Reusable

Transformation

Name

Yes Reject Reject Case insensitive

Parameter Name Yes Reject Reject Case insensitive

Data Type No On ADD:

VarChar

On ADD:

VarChar

According to reusable

transformation data types (i.e. no

length and scale)

Description No Empty Empty

Reusable transformations parameters CSV mapping rules

General guidelines

It's important to take note of the following:

l

On export:

l

The order in which the parameters will be written is determined by their order in the

web console

l

The order of reusable transformations in the CSV file is alphabetical

l

Reusable transformations are considered part of the model, which means users need

the import model permission in order to import them.

l

Column order has no meaning; only column names (case insensitive)

l

All CSVs are optional

l

The CSV files do not contain or rely on internal object IDs, rather, they rely on object

names.

l

On import, existing reusable transformations

will be overridden by the information from the

CSV file

Commands for exporting/importing objects

You can export and import Compose objects using the export_csv and import_csv CLI

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

To import specific entities, use the CSV mechanism described in Migrating models (page

49).

Compose CLI requires Administrator permission. To grant Administrator permission,

select "Run as administrator" when opening the command prompt. All commands should

be run from the Compose

bin

directory (C:\Program Files\Qlik\Compose with a default

installation).

Exporting objects

Run the following command from the Compose bin directory:

Command syntax

ComposeCli.exe export_csv --project project_name --outfolder folder

Parameters

Parameter Description

--project The name of the project.

--outfolder The name of the target folder for the CSV files.

Example

ComposeCli.exe export_csv --project myproject --outfolder c:\MyCFDWProject

Importing objects

As CSV files do not have versions, Compose cannot know which Compose version the

CSV file being imported originated from. If the default behavior has changed between

versions, the rule is that the default of the new version will always be applied. For

example, in the Compose May 2021 version, the option to update the fact with changes

to Type 2 data warehouse entities is now enabled by default. In previous versions, facts

were not updated with changes to Type 2 data warehouse entities and there was no

option to change this behavior. Therefore, continuing with our example, if you want this

option to be disabled by default, you would need to add the column for that setting (Fact

As Type 1) to the fact.csv file and set the value to "FALSE" before importing.

Before performing any import operations, it is strongly recommended to review the topic(s) that

discuss CSV file structure and the impact of missing columns and/or values on the target

environment. For example, before importing Data Marts, review the Migrating Data Marts topic.

Run the following command from the Compose bin directory:

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

Command syntax

ComposeCli.exe import_csv --project

project_name

[--infolder

folder

] [--scope

model|mappings|datawarehouse|DatawarehouseTasks|datamarts] [–-filetype

objecttype

] [–-infile

filename

]

Parameters

Parameter Description

--project The name of the project.

--infolder The full path to the folder containing the CSV files. This parameter

is only required if you want to import all files.

--scope Use this parameter to allow non-privileged users to import specific

objects. When this parameter is omitted, all objects will be

imported.

Possible values are:

l

--scope model - Imports the model objects (Attributes

Domain, Entities, Attributes, Relationships, Reusable

Transformations, and Reusable Transformation Parameters)

l

--scope mappings - Imports the mappings metadata and the

actual mappings.

l

--scope datawarehouse - Imports the mappings as well as all

objects included in the DatawarehouseTasks described below.

l

--scope DatawarehouseTasks - Imports the data warehouse

tasks and related objects (Tasks, Task Settings, Task

Mappings, Task Data Warehouse Tables, Task Custom

ETLs, and Custom ETLs).

l

--scope datamarts - Imports the data mart objects (Data

Marts, Facts, Star Schemas, Fact Attributes, Dimension

Attributes, Task Settings, and Custom ETLs).

The --scope parameter cannot be included in the same

command as the --filetype parameter

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

Parameter Description

--filetype The type of CSV file to import. When this parameter is omitted, all

objects will be imported.

Possible values are:

l

AttributesDomain

l

Entities

l

Attributes

l

Relationships

l

MappingsMetadata

l

Mappings

l

ReusableTransformation

l

ReusableTransformationParams

l

DataMarts

l

Tasks

l

TaskSettings

l

TaskMappings

l

TaskDataWarehouseTables

l

TaskCustomETLs

l

CustomETLs

The --filetype parameter cannot be included in the

same command as the --scope parameter

--infile The full path to the file to import when using non-default file

names. This must be specified together with the --filetype

parameter described above.

Examples

Import all CSV files

ComposeCli.exe import_csv --project myproject --infolder c:\composecfdw_csv

Import a specific CSV file with a custom Name

ComposeCli.exe import_csv --project myproject --filetype AttributesDomain --infile

c:\myattributesdomain.csv

Commands for comparing and applying objects

You can compare and apply Compose objects using the compare_csv and apply_csv CLI. Before

performing any of these operations, it is strongly recommended to review the topic(s) that discuss

CSV file structure and the impact of missing columns and/or values on the target environment. For

example, before applying changes to a Model, review the "Migrating the Model" topic.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

Comparing objects

Comparing the model to be imported with the existing project model allows you to view and

optionally edit the proposed changes before applying them.

When you run the Compare command, the structure of the CSV output files will be identical to the

export output files, but with the addition of Change Type and Action columns at the end. Note that

as the Apply command only supports ADD operations, the only value in the Change Type column

will be ADD.

If a column or value was deleted in the source environment, the Action column will contain the

value "IGNORE". This tells Compose not to delete the corresponding column/value in the target

environment when the Apply command is run. You can delete the "IGNORE" before running the

Apply command to force the deletion of the corresponding column/value in the target environment.

However, as the Apply command

does not support DELETE operation

s, deleting the "IGNORE"

before running the Apply command will have no effect.

Compare guidelines:

l

Unchanged objects will not be written to the output file(s).

l

If there was no changes at all to any of the object types, an empty output file will only be

created if you include the --create_files_even_when_no_diff parameter in the command (see

below).

l

For Boolean fields, values are True/False

l

Data type is Compose's logical type.

l

For model, the attributes writing order is according to the new ordinal in the entity.

l

Mapping metadata, attributes domain and entity order is alphabetical in the CSV file.

l

Relationship order is by entity name (alphabetical) and according to their order in the entity.

l

For mapping, the listed order is:

l

Alphabetical for mapping names (e.g. Map_Orders follows Map_Customers)

l

According to target columns (model ordinal) within a mapping.

l

Within a mapping, FD (From Date) could be one of the rows.

l

Source columns which are not mapped to anything will not be included in the output file.

l

If the model is not valid, the Compare command may fail.

Compose CLI requires Administrator permission. To grant Administrator permission,

select "Run as administrator" when opening the command prompt. All commands should

be run from the Compose

bin

directory (C:\Program Files\Qlik\Compose with a default

installation).

Command syntax

ComposeCli.exe compare_csv --project project_name --infolder folder --changes_folder folder [-

-create_files_even_when_no_diff]

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

Parameters

Parameter Description

--project The name of

the project.

--infolder The folder

containing the

exported CSV

files.

--changes_folder The folder to

where the

change files

will be written.

The file names

will be identical

to the exported

CSV files, but

with a _

changes suffix

(e.g. entity_

changes.csv).

--create_files_even_when_no_diff Use this

parameter if

you want a

template

output file to

be created

when there are

no differences

(e.g. an empty

entity_

changes.csv

file containing

only the header

columns). This

may be useful,

for example, if

you wish to

manually

create a

changes file.

Example

ComposeCli.exe compare_csv --project ProjectEmpty --infolder "C:\1" --changes_folder "C:\2" --

create_files_even_when_no_diff

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

Applying objects

Once you are satisfied with the proposed changes, you can then apply them to your project.

Apply guidelines:

l

If a column is missing on ADD, then the action will be as described in the topic discussing the

object elements.

l

By default, all changed object definitions will be added. To filter out rows or columns, edit the

outputted CSV file as needed. For example, to only apply changes to a specific Data Mart,

delete all of the other Data Marts' rows.

l

Any non-standard field name headings, should be renamed in the CSV to the Compose

standard.

l

Column order is insignificant as the columns will be ordered by name (case insensitive).

l

For Boolean fields, accepted values are Yes/No, True/False, 1/0 (case insensitive)

l

Data type is Compose's logical type.

l

Relationship details override the underlying attributes information (e.g. History Type, Is Key,

etc.).

Attributes that are marked as relationships will be skipped on import as they

derive their data type from the relationship.

l

All CSV files are optional in the folder.

l

For mappings, the apply operation will fail in the following scenarios:

l

The model is not valid.

l

The target does not exist in the model.

l

The mapping attribute does not exist in the model (after applying attributes.csv)

l

The Compose database object does not exist.

l

If the source schema, table, view or query does not exist, it will be validated after the Apply

operation .

Run the following command from the Compose bin directory:

Command syntax

ComposeCli.exe apply_csv --project

project_name

--changes_folder

folder

Parameters

Parameter Description

--project The name of the project.

--changes_folder The folder containing the change files

written by the compare command.

Example

ComposeCli.exe apply_csv --project ProjectEmpty --changes_folder "C:\1"

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

Exporting and importing projects using the CLI

Compose CLI requires Administrator permission. To grant Administrator permission,

select "Run as administrator" when opening the command prompt. All commands should

be run from the Compose

bin

directory (C:\Program Files\Qlik\Compose with a default

installation).

Under normal circumstances, use the deployment options described in

Project deployment (page

44)

to export and import projects. For deployment automation or control by another tool, you can

use the command line interface (CLI) to perform such tasks.

To export or import a project or project configuration, you first need to change the

default Master User Password.

For more information on changing the master user password, see Changing the master

user password (page 29).

See also: Moving projects from the test environment to the production environment

(page 85) and Import/export scenarios - When is a password required? (page 86)

Before running any command, you must run the

Connecting to Qlik Compose server (page 77)

command.

To get help when using the command line, you can run the Help command. For example, for help

about exporting a project, issue the following command:

ComposeCli.exe export_project_repository --help

This brings up a list of help parameters.

In this section:

l

Connecting to Qlik Compose server (page 77)

l

Exporting a project (page 78)

l

Importing a project (page 80)

l

Exporting the project configuration (page 83)

l

Importing the project configuration (page 84)

l

Moving projects from the test environment to the production environment (page 85)

Connecting to Qlik Compose server

Run the Connect command to establish a connection to the Qlik Compose Server. You must run this

command before running any other command.

Command syntax

ComposeCli.exe connect [--url connection_url]

Where:

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

--url is the connection URL to the system where the server is running.

Example

ComposeCli.exe connect --url https://mymachine.mydomain/qlikcompose

Exporting a project

You can use the export_project_repository CLI to export a project.

Exported projects include the following:

l

Databases

l

Model definitions (entities and attributes)

l

Mappings

l

Custom ETLs

l

Data warehouse tasks

l

Data mart definitions

Existing data warehouse tables and generated tasks are not exported. Notifications and

schedules are also not exported as they are considered to be environment-specific.

Command syntax

ComposeCli.exe export_project_repository --project

project_name

--outfile

output_file

[--is_

without_credentials or --without_environment_specifics] [--password

password

] [--master_user_

password

master_user_password

]

Parameters

Parameter Description

--project The name of the project.

--outfile The path to and name of the output file. This file is in

JSON format (e.g.

C:\file.json

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

Parameter Description

--is_without_credentials Use this parameter to specify that you want to export

the project settings without the encrypted fields.

When importing to a new project, you will need to

manually enter the project passwords (in the

Compose database connection settings) after the

import completes. In addition to eliminating the need

to specify a password when exporting or importing

the project, the is_without_credentials parameter

also allows the project to be used in every Compose

installation, regardless of its master user password. It

is also useful in the event that you would like to keep

the existing passwords in the target environment (e.g.

when exporting from a testing environment to an

existing project in the production environment).

--password The password for encrypting the credentials in the

exported project. When used, this parameter must be

used together with the master_user_password

parameter described below. Use the password >

parameter if you want to encrypt the credentials in the

exported project, but do not want the source master

password to be used in a different environment. The

specified password must be at least 32 characters in

length and can either be user-devised or generated

using the genpassword utility described in

Changing the

master user password (page 29)

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

Parameter Description

--master_user_password The master user password defined for the source

machine. When used, this parameter must be used

together with the password parameter. Use the master_

user_password > parameter if you want to encrypt the

credentials in the exported project, but do not want

the source master password to be used in a different

environment. In such a case, when you import the

project to an environment that has a different master

password, you will only need to specify the password

qualifier.

For instructions on changing the master user

password, see

Changing the master user password

(page 29)

See also:

Moving projects from the test environment

to the production environment (page 85)

and

Import/export scenarios - When is a password

required? (page 86)

--without_environment_specifics Use this parameter to exclude all of the environment

specific settings from the export. This is useful if the

project you are exporting includes environment

variables and the settling in the target environment

are significantly different from the source

environment. The variables will be replaced with their

default values (either empty or the default Boolean

value).

See also:

Working with environment variables (page

87)

Example

Export project without a password

ComposeCli.exe export_project_repository --project MyProject --outfile file.json --is_without_

credentials

Export project with a password

ComposeCli.exe export_project_repository --project MyProject --outfile file.json --password

MyPassword --master_user_password MyMasterUserPassword

Importing a project

You can use the import_project_repository CLI to import a project. If you import to an existing

project, all of the project settings, except the project configuration items will be overridden. For

information on the project configuration items, see

Exporting the project configuration (page 83)

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

Imported projects include the following:

l

Databases

l

Model definitions (entities and attributes)

l

Mappings

l

Custom ETLs

l

Data warehouse tasks

l

Data mart definitions

Command syntax

ComposeCli.exe import_project_repository --project

project_name

--infile

input_file

[--

password

] [--is_without_credentials] [--override_configuration] [--dont_backup_

existing_project] [--autogen]

Parameters

Parameter Description

--project The name of the project.

--infile The full path to the input file, including the file name.

This file is in JSON format (e.g.

C:\file.json

--password The password specified with the password parameter

during export.

For instructions on changing the master user

password, see

Changing the master user password

(page 29)

See also:

Moving projects from the test environment

to the production environment (page 85)

and

Import/export scenarios - When is a password

required? (page 86)

--is_without_credentials Use this parameter to specify to import the project

settings without the encrypted fields. In this case, you

will need to manually enter the project passwords in

the Compose database connection settings.

--override_configuration Use this parameter to override the existing project

configuration. When importing a project, the default

setting is not to override the existing project

configuration.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

Parameter Description

--dont_backup_existing_project Use this parameter to specify not to back up the

existing project. By default, existing projects are

backed up to the following location (and automatically

restored if the import fails):

<product_dir>\data\projects\<project_name>_

backup_<timestamp>

--autogen Use this paramter to automatically generate the

imported project. If the project is imported

successfully, Compose will:

l

Validate the model and adjust the data

warehouse if needed

l

Create the data warehouse tables if they do not

exist

l

Validate the data warehouse

l

Adjust the data warehouse if needed.

If the "Adjust" cannot be performed

automatically, the "autogen" process will be

stopped.

l

Generate all data warehouse tasks.

If Compose encounters an error while

generating a data warehouse task, it will skip

the problematic task and continue with the

remaining tasks.

l

Create, adjust and generate all data marts

If an error is encountered during the

"Create" or "Generate" operations, it is

recommended to switch to the Compose

Console and perform these operations

manually. Doing so will generate a more

detailed error message and enable you to

resolve the issue.

For information on validating the data warehouse and

generating the task, see

Creating and managing the

data warehouse (page 192)

. For information on

generating the data mart task, see

Creating and

managing data marts (page 228)

Example

ComposeCli.exe import_project_repository --project MyProject --infile file.json --password

MyPassword --override_configuration --autogen

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

Exporting the project configuration

You can use the export_project_repository_config CLI to export the configuration settings

of an existing project. This includes database definitions, scheduling jobs, and notifications. This is

helpful, for example, when you need to migrate configuration settings from a test environment to

the production environment.

For information about migrating projects, see

Moving projects from the test environment to the

production environment (page 85)

Command syntax:

ComposeCli.exe export_project_repository_config --project

project_name

--outfile

output_file

[--is_without_credentials] [--password

password

] [--master_user_password

master_user_password

]

Parameters

Parameter Description

--project The name of the project.

--outfile The path to and name of the output file. This file is in JSON format

(e.g.

C:\file.json

--is_without_credentials Use this parameter to specify that you want to export the project

settings without the encrypted fields. When importing to a new

project, you will need to manually enter the project passwords (in

the Compose database connection settings) after the import

completes. In addition to eliminating the need to specify a

password when exporting or importing the project, the is_without_

credentials parameter also allows the project to be used in every

Compose installation, regardless of its master user password. It is

also useful in the event that you would like to keep the existing

passwords in the target environment (e.g. when exporting from a

testing environment to an existing project in the production

environment).

--password The password for encrypting the credentials in the exported

project. When used, this parameter must be used together with the

master_user_password parameter described below. Use the password

> parameter if you want to encrypt the credentials in the exported

project, but do not want the source master password to be used in

a different environment. The specified password must be at least

32 characters in length and can either be user-devised or

generated using the genpassword utility described in

Changing the

master user password (page 29)

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

Parameter Description

--master_user_password The master user password defined for the source machine. When

used, this parameter must be used together with the password

parameter. Use the master_user_password > parameter if you want

to encrypt the credentials in the exported project, but do not want

the source master password to be used in a different environment.

In such a case, when you import the project to an environment that

has a different master password, you will only need to specify the

password qualifier.

For instructions on changing the master user password, see

Changing the master user password (page 29)

See also:

Moving projects from the test environment to the

production environment (page 85)

and

Import/export scenarios -

When is a password required? (page 86)

Example

Export project configuration without a password

ComposeCli.exe export_project_repository_config --project MyProject --outfile file.json --is_

without_credentials

Export project configuration with a password

ComposeCli.exe export_project_repository_config --project MyProject --outfile file.json --

password MyPassword --master_user_password MyMasterUserPassword

Importing the project configuration

You can use the Compose CLI to import the configuration settings of an existing project. This

includes database definitions, scheduling jobs, and notifications. This is helpful, for example, when

you need to migrate configuration settings from a test environment to the production environment.

For information about migrating projects, see

Moving projects from the test environment to the

production environment (page 85)

Before you can import the project configuration, you must first run the import_project_

repository command described in Importing a project (page 80).

Command syntax:

ComposeCli.exe import_project_repository_config --project

project_name

--infile

input_file

[--

password

] [--is_without_credentials]

Parameters

Parameter Description

--project The name of the project.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

Parameter Description

--infile The full path to the input file, including the file name. This file is in

JSON format (e.g.

C:\file.json

--password The password specified with the password parameter during

export.

For instructions on changing the master user password, see

Changing the master user password (page 29)

See also:

Moving projects from the test environment to the

production environment (page 85)

and

Import/export scenarios -

When is a password required? (page 86)

--is_without_credentials Use this parameter to specify to import the project settings without

the encrypted fields. In this case, you will need to manually enter

the project passwords in the Compose database connection

settings.

Example

ComposeCli.exe import_project_repository_config --project MyProject --infile file.json --

password MyPassword

Moving projects from the test environment to the production environment

After successfully creating and testing projects in the test environment, you now want to move

those projects to the production environment. You also need to propagate updates from the testing

environment to the production environment as necessary. Although it sounds complicated, moving

new and updated projects from the test environment to the production environment is actually

quite straightforward, as explained below.

See also

Import/export scenarios - When is a password required? (page 86)

The data source and data warehouse display names must be identical in both the testing

and the production environments.

To perform the initial migration from the testing environment to the production environment:

1. Export the project from the test environment as described in

Exporting a project (page 78)

2. Import the test project to the production environment as described in

Importing a project

(page 80)

3. Edit the connection settings to point to the production data source and data warehouse.

For more information, see

Setting up Landing Zone and Data Source connections (page

131)

and

Setting up a data warehouse connection (page 110)

respectively.

4. Configure notifications and scheduling as needed.

For more information, see

Scheduling tasks (page 270)

and

Notifications (page 272)

respectively.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

To propagate updates from the testing environment to the production environment:

1. Export the project from the test environment as described in

Exporting a project (page 78)

2. Import the test project to the production environment as described in

Importing a project

(page 80)

Import/export scenarios - When is a password required?

The following section describes which of the various export/import scenarios require a password to

be specified.

In all scenarios, if you import a project to an existing project, the credentials of the

existing projects are preserved (as they are part of the project configuration).

Example 1: Moving a project or project configuration between two Compose machines without

retaining the project credentials.

This is useful when importing to a new project that will have different project credentials.

In such a scenario, simply add the is_without_credentials parameter to either the export or the

import command.

Example 2: Moving a project or project configuration between two Compose machines that

have the same Master User Password.

In such a scenario, neither the export command nor the import command need to include a

password. If you do not want the source and target projects to have the same credentials (for

database connectivity, etc.), then you also need to specify the is_without_credentials parameter in

either the export or the import command.

Example 3: Moving a project or project configuration between two Compose machines that

have a different Master User Password, but without revealing the Master User Password of

the source machine.

In such a scenario, the export command must include the password and master_user_password

parameters while the import command must include the password parameter. The same password

(specified with the password parameter) must be used for both export and import.

Example 4: Moving a project or project configuration between two Compose machines that

have a different Master User Password.

In such a scenario, the export command does not need to include a password, but the import

command should specify the Master User Password of the source machine (using the password

parameter).

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

Working with environment variables

Requires Compose August 2021 Service Release 03 or later.

Environment variables allow developers to build more portable expressions, custom ETLs, and

Compose configurations, which is especially useful when working with several environments such

as DTAP (Development, Testing, Acceptance and Production). Different environments (for example,

development and production) often have environment-specific settings such as database names,

schema names, and Replicate task names. Variables allow you to easily move projects between

different environments without needing to manually configure the settings for each environment.

This is especially useful if many settings are different between environments. For each project, you

can use the predefined environment variables or create your own environment variables.

Database and schema name variables are supported with the following objects:

l

Reusable transformations

l

Custom ETLs (Data warehouse and Data marts)

l

Mappings lookups

l

Mappings and model expressions

l

Data mart settings

In this section:

l

Limitations and considerations (page 87)

l

Locations and names of predefined variables (page 88)

l

Compose environment_variables CLI guidelines (page 91)

l

Working with predefined variables only (page 91)

l

Working with user-defined variables only (page 92)

l

Working with both user-defined and predefined variables (page 94)

l

Removing environment variables (page 96)

l

Manually editing the JSON file (page 96)

Limitations and considerations

l

Values for predefined variables will be shown in the Compose user interface, but user-

defined variables will be shown in variable format (for example, $$${myVariable}).

l

Variable names are case sensitive and can include alphanumeric characters, underscores,

and periods only.

l

User-defined variable definitions will not be included in projects exported as JSON files or in

projects exported as CSV files. However, user-defined variable names will be included in the

relevant places in the exported files.

See also:

Exporting and importing projects using the CLI (page 77)

and

Migrating objects as

CSV files (page 46)

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

l

Exported CSV objects that are associated with predefined environment variables (for

example, a mapping database and schema) cannot contain user-defined environment

variables.



For instance, this is not allowed:



Name Landing Zone Database Schema

MyMapping $$${MyDatabase} $$${MySchema}

See also:

Migrating objects as CSV files (page 46)

Locations and names of predefined variables

Predefined variables are located in the following places:

Predefined data warehouse variables

The following predefined data warehouse connection variables can be set:

l

$$${database.Data Warehouse.connectionInputModeStandard} - The value can be "True" or

"False". "False" means that Advanced will be used.

l

$$${database.Data Warehouse.serverName}

l

$$${database.Data Warehouse.port}

l

$$${database.Data Warehouse.authenticationMethodSQLServer} - The value can be "True"

or "False".

Relevant for Microsoft SQL Server only.

l

$$${database.Data Warehouse.userName}

l

$$${database.Data Warehouse.encryptedPassword}

l

$$${database.Data Warehouse.odbcString}

Only relevant when the Advanced connection option is set.

l

$$${database.Data Warehouse.jdbcString}

Only relevant when the Advanced connection option is set.

l

$$${database.Data Warehouse.warehouse}

Relevant for Snowflake only.

l

$$${database.Data Warehouse.database}

l

$$${database.Data Warehouse.datawarehouseSchema}

l

$$${database.Data Warehouse.datamartSchema}

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

For an explanation of data warehouse settings, see

Setting up a data warehouse connection (page

110)

Predefined landing zone variables

The following predefined landing zone connection variables can be set:

As there can be several landing zones, the middle part of the variable name (landing1

below) is the landing zone name and should be replaced with the actual name of your

Landing Zone.

l

$$${database.landing1.contentType} - Values can be "Full Load Only", "ChangeProcessing",

or "Full Load and Change Processing"

l

$$${database.landing1.designatedBy} - The value can be "database" or "schema".

l

$$${database.landing1.database}

l

$$${database.landing1.schema}

l

$$${database.landing1.errorMartSchema}

l

$$${database.landing1.afterApplyingChanges} - Values can be "Delete" (the default) or

"Archive".

l

$$${database.landing1.archiveDatabase}

Only relevant if "Archive" is set.

l

$$${database.landing1.archiveSchema}

Only relevant if "Archive" is set.

l

$$${database.landing1.isAssociatedWithReplicateTask} - The value can be "True" or "False".

l

$$${database.landing1.replicateServer}

Only relevant if the Associate with Replicate task option is set.

l

$$${database.landing1.replicateTask}

Only relevant if the Associate with Replicate task option is set.

l

$$${database.landing1.source.isSourceDefined} - The value can be "True" or "False".

l

$$${database.landing1.source.connectionInputModeStandard} - The value can be "True" or

"False". "False" means that Advanced will be used.

l

$$${database.landing1.source.serverName}

l

$$${database.landing1.source.port}

l

$$${database.landing1.source.authenticationMethodSQLServer} - The value can be "True"

or "False".

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

Relevant for Microsoft SQL Server only.

l

$$${database.landing1.source.userName}

l

$$${database.landing1.source.encryptedPassword}

l

$$${database.landing1.source.odbcString}

Only relevant when the Advanced connection option is set.

l

$$${database.landing1.source.jdbcString}

Only relevant when the Advanced connection option is set.

l

$$${database.landing1.source.database}

l

$$${database.landing1.source.datawarehouseSchema}

For an explanation of landing zone settings, see

Defining landing zones (page 140)

Predefined data mart variables

The following predefined data mart connection variables can be set:

As there can be several data marts, the middle part of the variable name (datamart1

below) is the data mart name and should be replaced with the actual data mart name.

l

$$${datamart.datamart1.databaseDefault} - The value can be "True" or "False".

l

$$${datamart.datamart1.database}

l

$$${datamart.datamart1.tablesSchemaDefault} - The value can be "True" or "False".

l

$$${datamart.datamart1.tablesSchema}

l

$$${datamart.datamart1.viewsSchemaDefault} - The value can be "True" or "False".

l

$$${datamart.datamart1.viewsSchema}

For an explanation of data mart settings, see

Modifying data mart settings (page 260)

Predefined lookup variables

The following predefined lookup variables can be set:

As there are usually multiple mappings and columns, the middle part of the variable

name (mapping1.column1 below) are the mapping and column names and should be

replaced with the actual mapping and column name.

l

$$${mapping.mapping1.column1.lookup.schema}

For an explanation of lookup settings, see

Using lookup tables (page 210)

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

Predefined mappings variables

The following predefined mapping variables can be set:

As there are usually multiple mappings, the middle part of the variable name (mapping1

below) is the mapping name and should be replaced with the actual mapping name.

l

$$${mapping.mapping1.schema}

For an explanation of mappings settings, see

Editing column mappings (page 205)

Compose environment_variables CLI guidelines

The procedures below describe how to manage environment variables using the Compose CLI.

When using the Compose CLI, make sure to adhere to the following guidelines:

l

Before running any environment_variables commands, run the Connect command to establish

a connection to the Compose Server. For more information on this command, see

Connecting to Qlik Compose server (page 77)

l

Variable names should be specified in the CLI without dollar signs or curly brackets. So, for

example, assuming the name of your landing database is MyLanding,

$$${database.MyLanding.database} should be specified as database.MyLanding.database.

l

When setting a variable with the Compose CLI, any variable names and values with spaces

(both user-defined and predefined) must be specified in quotation marks. This means, for

example, that all data warehouse variables must be specified with quotation marks as their

names always contain a space (for example, "database.Data Warehouse.serverName").

l

When setting a Boolean value, use --boolVal instead of --val. For example, to set

database.Data Warehouse.connectionInputModeStandard to "True", specify:

--var "database.Data Warehouse.connectionInputModeStandard" --boolVal true

Working with predefined variables only

When working with predefined environment variables only, the flow is as follows:

1. In the source environment:

a. Configure your project as desired.

b. Run the following command to write the predefined environment variables to a JSON

file (replacing

projectName

with the name of your Compose project and

JsonFileLocation

with the full path of your JSON file):

ComposeCli environment_variables --command writePredefined --project

projectName

--jsonFile

JsonFileLocation

For security reasons, Compose never writes encrypted passwords to files.

1. a. Create a deployment package or export the project using the Compose CLI.

See also:

Project deployment (page 44)

and

Exporting a project (page 78)

2. In the target environment:

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

a. Deploy the project if you created a deployment package or import the project if you

exported it using the Compose CLI.

See also:

Project deployment (page 44)

and

Importing a project (page 80)

b. Copy the JSON file created in the source environment to your preferred location.

c. Edit the JSONfile and replace the variable values with the values you want to

propagate to the target environment.

See also:

Working with environment variables (page 87)

d. Run the following command to propagate the JSON file variables to the Compose user

interface (replacing

projectName

with the name of your Compose project and

JsonFileLocation

with the full path of your edited JSON file):

ComposeCli environment_variables --command setALL --project

projectName

jsonFile

JsonFileLocation

Example: 

ComposeCli environment_variables --command setALL --project analytics --jsonFile

C:\compose\compose-variables.json

Running the setAll command will remove any existing environment

variables that are not included in the JSON file. Therefore, if you want to

make sure that such variables are not removed when setAll is run, add them

to the JSON file.

You can also set any variable by running the set command. This is

especially useful if you do not want the JSON file to include passwords. In

this case, you would need to run the following command (shown as an

example):

ComposeCli environment_variables --command set --project MyProject --

var encryptedPassword --val g56g56y563%

When you set a password with the CLI, Compose encrypts the password

first and then sets it.

e. Run the following command to apply the predefined environment variables and

complete the process (replacing

projectName

with the name of your Compose project):

ComposeCli environment_variables --command applyPredefined --project

projectName

Working with user-defined variables only

When working with user-defined environment variables only, the flow is as follows:

User-defined variables should have the following format in the Compose user interface:

$$${myVariable}

You can use user-defined variables in the following places:

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

l

Expressions - in model, mappings (column expressions, data quality and filters) or data mart

l

Lookup conditions and expressions

l

Custom ETLs

To set user-defined variables:

1. In the source environment:

a. In the Compose user interface, include your variable in an object that supports user-

defined variables (for example, a Custom ETL in a data warehouse task or an

expression), and save your settings.

Example: User-defined variable in a Custom ETL

UPDATE

"ROSIE"."DWH2"."TSTG_Employees"

SET

"Title" = $$${myVar}

WHERE "EmployeeID" < 150

b. For user-defined variables, use one of the following methods:



Method 1:

Set each variable individually by running the following command (replacing

projectName

with the name of your Compose project,

varName

with the variable name,

and

value

with the variable value):

ComposeCli environment_variables --command set --project

projectName

--var

varName

--val

value

Example: 

ComposeCli environment_variables --command set --project MyProject --var myVar --

val Manager



Method 2:

Add the user-defined variables directly to the JSON file as described in

Manually

editing the JSON file (page 96)

below.

c. Generate the task(s) with the user-defined variables.

d. If you set the user-defined variables with the CLI, run the following command to write

the user-defined variables to a JSON file (replacing

projectName

with the name of your

Compose project and

JsonFileLocation

with the full path of your JSON file):

ComposeCli environment_variables --command writeCLISet --project

projectName

jsonFile

JsonFileLocation

e. Create a deployment package or export the project using the Compose CLI.

See also:

Project deployment (page 44)

and

Exporting a project (page 78)

2. In the target environment:

a. Deploy the project if you created a deployment package or import the project if you

exported it using the Compose CLI.

See also:

Project deployment (page 44)

and

Importing a project (page 80)

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

b. Copy the JSON file created in the source environment to your preferred location.

c. Edit the JSONfile and replace the variable values with the values you want to appear

in the target environment.

See also:

Working with environment variables (page 87)

d. Run the following command to propagate the JSON file variables to the Compose user

interface (replacing

projectName

with the name of your Compose project and

JsonFileLocation

with the full path of your edited JSON file)::

ComposeCli environment_variables --command setALL --project

projectName

jsonFile

JsonFileLocation

Running the setAll command will remove any existing environment

variables that are not included in the JSON file. Therefore, if you want to

make sure that such variables are not removed when setAll is run, add them

to the JSON file.

e. Generate the relevant tasks as well.

Working with both user-defined and predefined variables

Each time Compose writes to the JSON file, it overwrites the existing content. Therefore,

when working with both user-defined and predefined variables, you need to specify the

path to two different JSON files. For convenience, you can then merge the two files into

a single JSON file while taking care to use the format described in Manually editing the

JSON file (page 96) below.

When working with both user-defined and predefined environment variables, the flow is as follows:

1. In the source environment:

a. Configure your project as desired.

b. Run the following command to write the predefined environment variables to a JSON

file (replacing

projectName

with the name of your Compose project and

predefinedJsonFileLocation

with the full path of the JSON file that you want to contain

your predefined variables):

ComposeCli environment_variables --command writePredefined --project

projectName

--jsonFile

predefinedJsonFileLocation

For security reasons, Compose never writes encrypted passwords to files.

c. In the Compose user interface, add user-defined variables to supported objects (for

example, a Custom ETL in a data warehouse task), and save your settings.

Example: User-defined variable in a Custom ETL

UPDATE

"ROSIE"."DWH2"."TSTG_Employees"

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

SET

"Title" = $$${myVar}

WHERE "EmployeeID" < 150

d. For user-defined variables, use one of the following methods:



Method 1:

Set each variable individually by running the following command (replacing

projectName

with the name of your Compose project,

varName

with the variable name,

and

value

with the variable value):

ComposeCli environment_variables --command set --project

projectName

--var

varName

--val

value

Example: 

ComposeCli environment_variables --command set --project MyProject --var myVar --

val Manager



Method 2:

Add the user-defined variables directly to the JSON file as described in

Manually

editing the JSON file (page 96)

below.

e. Generate the task with the user-defined variable(s).

f. If you set the user-defined variables with the CLI, run the following command to write

the user-defined variables to a JSON file (replacing

projectName

with the name of your

Compose project and

JsonFileLocation

with the full path of the JSON file that you want

to contain your user-defined variables):):

ComposeCli environment_variables --command writeCLISet --project

projectName

jsonFile

userDefinedJsonFileLocation

g. Create a deployment package or export the project using the Compose CLI.

See also:

Project deployment (page 44)

and

Exporting a project (page 78)

2. In the target environment:

a. Deploy the project if you created a deployment package or import the project if you

exported it using the Compose CLI.

See also:

Project deployment (page 44)

and

Importing a project (page 80)

b. Copy the JSON file created in the source environment to your preferred location.

c. Edit the JSONfile and replace the variable values with the values you want to appear

in the target environment.

See also:

Working with environment variables (page 87)

d. Run the following command to propagate the JSON file variables to the Compose user

interface (replacing

projectName

with the name of your Compose project and

JsonFileLocation

with the full path of your edited JSON file)::

ComposeCli environment_variables --command setALL --project

projectName

jsonFile

JsonFileLocation

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

Running the setAll command will remove any existing environment

variables that are not included in the JSON file. Therefore, if you want to

make sure that such variables are not removed when setAll is run, add them

to the JSON file.

e. Generate all tasks with user-defined variables.

f. Run the following command to apply the predefined environment variables and

complete the process (replacing

projectName

with the name of your Compose project):

ComposeCli environment_variables --command applyPredefined --project

projectName

Removing environment variables

Run the "Remove" command if you set a variable that you no longer want to use in the target

environment.

Removing a predefined variable will reset it to its previous value. To prevent errors, make

sure when you remove user-defined variables, to also edit or remove the custom ETL or

expression where the variable is located.

1. Run the following command:

ComposeCli environment_variables --command remove --var

VarName

--project

projectName

Example: 

ComposeCli environment_variables --command remove --var MyVariable --project MyProject

2. Generate any task(s) configured with user-defined variables.

Manually editing the JSON file

Before you propagate the JSON file variables to the Compose user interface in the target

environment, you need to edit the file and replace the source variable values with the target

variable values. It might also be more convenient to create, edit and maintain the JSON file manually

instead of (or in addition to) using the writeCLISet and writePredefined commands described above.

The JSON file is split into two sections: “predefinedVariables” and “userDefinedVariables”. When

you edit the JSON file, make sure to put predefined variables in the “predefinedVariables” section

and user-defined variables in the “userDefinedVariables” section. In addition, make sure to use

standard JSON escaping conventions, as shown in the following example:

{

“predefined”: {

"databases.Data Warehouse.serverName": "myhostname"

“userDefined”: {

"variable": "value",

"var2": "val2"

}

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

Editing and saving the JSON file does not automatically set and apply the variables. To

do this, run the 'SetAll' and 'ApplyPredefined' commands. If the JSON file also contains

user-defined variables, you will need to generate the associated tasks as well. See

below for details.

Propagating the JSON file variables to the Compose user interface

After you have prepared your JSON file and copied it to your preferred location, perform the

following procedure to propagate the variables to the Compose user interface:

1. Run the following command to propagate the JSON file variables to the Compose user

interface (replacing

projectName

with the name of your Compose project and

JsonFileLocation

with the full path of your edited JSON file):

ComposeCli environment_variables --command setALL --project

projectName

--jsonFile

JsonFileLocation

Example: 

ComposeCli environment_variables --command setALL --project MyProject --jsonFile

C:\composeVariables\myVariables.json

Running the setAll command will remove any existing user-defined environment

variables from the user-interface, and reset any modified predefined environment

variables that are not included in the JSON file to their previous values.

2. If the JSON file contains predefined environment variables, run the following command

(replacing

projectName

with the name of your Compose project):

ComposeCli environment_variables --command applyPredefined --project projectName

3. If the JSON file contains user-defined environment variables, generate the associated task

(s).

Generating projects using the CLI

The instruction below explain how to automatically generate projects using the CLI. This can be

especially useful when deploying projects between different environments.

Command syntax

ComposeCli.exe generate_project --project

project_name

[--database_already_adjusted] [--

stopIfDatamartsNeedRecreation]

Parameters

Parameter Description

--project The name of the project.

--database_already_adjusted This should only be included if the data warehouse and

data marts were adjusted outside of Compose.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

Parameter Description

--stopIfDatamartsNeedRecreation Stops the process if any data marts cannot be

automatically adjusted and need to be recreated.

Example

ComposeCli.exe generate_project --project MyProject --stopIfDatamartsNeedRecreation

When the command is run, Compose will:

l

Validate the model.

l

Create any data warehouse tables that do not exist.

l

Validate the data warehouse.

l

Adjust the data warehouse if needed.

If an Adjust script is needed and --database_already_adjusted is included in the

command, the script (DDL) will not be run as it is assumed that the user ran it

manually.

l

Generate all data warehouse tasks.

If Compose encounters an error while generating a data warehouse task, it will

skip the problematic task and continue with the remaining tasks.

l

Create, adjust and generate all data marts.

If the "Adjust" cannot be performed automatically and --

stopIfDatamartsNeedRecreation is included in the command, the process will stop.

Certain limitations apply when adjusting the data mart. For more information, see

Auto-adjust

limitations and considerations (page 258)

Exporting project documentation

Relevant to Data Warehouse projects only.

You can export a project to a zip file for record keeping and sharing offline. The project is exported

as HTML files which can be easily printed to PDF using the print toolbar button in the HTML page.

To export the project documentation:

1. Open the project as described in

Managing and monitoring projects (page 290)

2. Click the downward arrow to the right of the project name and select Generate Project

Documentation from the drop-down menu.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

A zip file with the name of the project and a timestamp of when the documentation was

generated will be created (e.g.

MyComposeProject_documentation_03_22_2016__15_01_

10.zip

). Depending on your browser settings, the file will either be automatically downloaded

to your browser’s Downloads folder or you will be prompted to save it.

3. To view the documentation, extract the contents of the zip file and then open the index.html

file.

A browser tab will open displaying the documentation categories in the left pane.

4. Navigate through the documentation using the tree in the left pane and the breadcrumbs

above the documentation.

Viewing and downloading DDL scripts

In the DDL Script Files window, you can view and download the data warehouse DDL script files.

By default, Compose executes the Create, Adjust and Drop statements immediately upon user

request. However, when the Generate DDL scripts but do not run them option is enabled,

Compose will only generate the scripts but not execute them.

DDL scripts must be run from the data warehouse database and schema or data

warehouse database and data mart schema, depending on the DDL script and the

platform type (for example, in Oracle there are no schemas just the database).

For more information on the Create DDL scripts only option, see

Project settings (page 37)

To open the DDL Script Files window:

1. Open your project as described in

Managing and monitoring projects (page 290)

2. Click the downward arrow to the right of the project name and select Show DDL Scripts

from the drop-down menu.

The DDL Script Files window opens.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

5 Data Warehouse projects

3. To view a script, select the desired script in the Script Files pane on the left. The script will

be displayed on the right.

4. To download a script, select the desired script in the Script Files pane on the left. Then click

the download button in the top right of the window.

5. To search for an element in the script, start to type in the search box. All strings that match

the search query will be highlighted blue.

You can navigate between search query matches using the arrows to the right of the search

box. Use the right and left single arrows to navigate matches sequentially. Use the right and

left double arrows to jump to the last and first match respectively.

6. To reset the search, either delete the search query or click the "x" to the right of the search

box.

Project versioning

Compose provides built-in project version control using the Git engine. Version control enables

Compose developers to commit project revisions to both a local and a remote Git repository. If a

mistake is made, Compose developers can easily roll back to earlier versions of the project while

minimizing disruption to all team members.

Revisions only store metadata and mapping information. After you revert to a saved

revision, you will need to recreate the data warehouse and data mart tables.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

100

5 Data Warehouse projects

Configuring version control settings

To define Version Control Settings:

1. From the project drop-down menu, select Version Control > Settings.

The Version Control Settings - Git window opens.

The Local Commits area shows the local root folder where project revisions are committed.

The first time a project revision is committed, Compose creates a JSON file with the current

project settings. The <project_name>.json file is archived to a ZIP file (<project_name>_

deployment.zip), which is located in a project-specific folder under the source-control folder.

2. To enable commits to a remote Git database, select Enable remote commits and then

provide the following information:

l

URL - The address of the remote Git database.

l

User name - Your user name for accessing the remote Git database.

l

Password - Your password for accessing the remote Git database.

Committing projects

You can commit a project using the console or using the CLI:

To commit a project to Version Control using the web console:

1. From the project drop-down menu, select Version Control > Commit.

The Commit - <Project_Name> window opens.

2. Enter a message in the Message box and optionally select the Remote push check box.

Note that the Remote push check box will be disabled if the Enable remote commits option

described above is not selected.

To commit a project to Version Control using the CLI:

Run the following command from the Compose bin directory:

Command syntax

ComposeCli.exe commit --project

project_name

[--message

message

] [--remote]

Parameters

Parameter Description

--project The name of the project.

--message An optional message to accompany the commit.

--remote This parameter is required if you want to commit the project to a

remote Git repository (see above). By default, the project will be

committed locally to

<product_dir>\data\source-control

Example

ComposeCli.exe commit --project MyProject --remote

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

101

5 Data Warehouse projects

To revert to a saved revision:

1. From the project drop-down menu, select Version Control > Revisions history.

The Revision History - <Project_Name> window opens.

By default, the last 10 revisions are shown. You can change this number by selecting one of

the available options from the Show drop-down list.

2. Optionally, use the Search box to find a specific revision.

3. Select the desired revision and then click the Deploy to Revision toolbar button.

4. When prompted to confirm the operation, click Yes.

The existing project will be replaced.

5. Click Close to close the Revision History - <Project_Name> window.

To download a saved revision:

1. From the project drop-down menu, select Version Control > Revisions history.

The Revision History - <Project_Name> window opens.

By default, the last 10 revisions are shown. You can change this number by selecting one of

the available options from the Show drop-down list.

2. Optionally, use the Search box to find a specific revision.

3. Select the desired revision and then click the Download Revision as Package toolbar

button.

The package will be saved as a ZIP file in your browser's default download location.

Creating a diagnostics package

To assist in troubleshooting esoteric issues, a Qlik Support Engineer may ask you for a diagnostics

package. The diagnostics package contains the following information:

l

The project "data" directory

l

Java logs and workflow logs

l

.NET logs

l

Deployment package file

As a prerequisite to creating a diagnostics package, the project must have at least one

database connection configured.

To create a diagnostics package:

1. From the Project menu, select Create Diagnostics Package.

2. A zip file in the following format will either be downloaded to your computer or you will be

prompted to download it (according to your browser settings):

Compose_Diagnostics_<project_name>_<timestamp>.zip

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

102

5 Data Warehouse projects

5.3  Getting started with Data Warehouse projects

This section provides an overview of the Qlik Compose architecture, familiarizes you with its

interface and ends with a short tutorial.

In this section:

l

High-level flow (page 103)

l

Console elements (page 103)

l

Data warehouse project tutorial (page 106)

High-level flow

Setting up data warehouse project typically consists of the following stages (simplified):

1. In Qlik Replicate, define a task that replicates the source tables to a landing zone in the data

warehouse.

2. In Compose:

a. Configure access to your data warehouse.

b. Configure access to your data sources.

c. Use the "Discover" option to auto-generate a model from the source tables or import

an existing model that was created in ERwin. You can even create the model manually

if you prefer.

d. Once your model is ready, create the data warehouse tables and populate them with

the source data.

e. Create a data mart from the data warehouse tables.

f. Populate the data mart tables.

See also

Introduction (page 13)

Console elements

This section will familiarize you with the elements that comprise the Qlik Compose UI.

To open Qlik Compose, from the Windows Start menu, select All Programs > Qlik Compose > Qlik

Compose Console.

Management view

The Qlik Compose Console opens in Management view.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

103

5 Data Warehouse projects

In Management view, you can perform the following tasks:

l

Create, edit, and delete projects

For more information, see

Adding and managing data warehouse projects (page 35)

l

Access Qlik Compose management options, including:

l

Manage log levels and cleanup options

l

Manage email settings

For more information, see

Managing Compose (page 378)

Designer view

When you add a new project or open an existing project, the console switches to Designer view. If

you are in Monitor view (see below), you can switch back to Designer view by clicking the Designer

tab in the top right of the console.

Designer view comprises the following panels:

l

Databases - Configure access to your source database(s) and data warehouse.

For more information, see

Setting up Landing Zone and Data Source connections (page 131)

and

Setting up a data warehouse connection (page 110)

respectively.

l

Model - Create and edit your model.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

104

5 Data Warehouse projects

For more information, see

Creating and managing the model (page 154)

l

Data Warehouse - Create the data warehouse tables, generate the task statements, and run

data warehouse tasks.

For more information, see

Creating and managing the data warehouse (page 192)

l

Data Mart - Define data marts, create the data mart tables, generate the task statements,

and run data mart tasks.

For more information, see

Creating and managing data marts (page 228)

In Designer view, each of the panels has a bar below the panel name. The bar can be empty, half-

filled or completely filled, according to the current configuration status of the panel properties, as

follows:

l

No fill (gray) - Not configured

l

Half filled - Configuration is not complete

l

Completely filled - Fully configured

Monitor view

To switch to Monitor view, click the Monitor tab in the top right of the console.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

105

5 Data Warehouse projects

In Monitor view, you can view the status of data warehouse and data mart tasks and schedule their

execution, either individually or as a workflow.

For more information, see

Controlling and monitoring tasks and workflows (page 265)

Data warehouse project tutorial

This short tutorial will walk you through each of the stages required to create a data warehouse

project. For simplicity’s sake, we will be using Microsoft SQL Server as both the source database

server and the target Data Warehouse. You can of course use any of the supported source or target

databases, but instructions for doing so are outside the scope of this tutorial.

What you need:

l

Qlik Compose installed according to the instructions in

Qlik Compose installation and setup

(page 16)

l

The Northwind.MDF sample database attached to Microsoft SQL Server.

An easy-to-follow set of instructions for downloading and installing Northwind.MDF can be

found at the following website:

http://businessimpactinc.com/install-northwind-database/

l

Define an empty database on Microsoft SQL Server (e.g. northwind_dwh) and make a note of

its name. This will serve as the target Data Warehouse for the Northwind.MDF source tables.

l

Microsoft SQL Server Native Client 11.0 installed on the Compose machine.

To set up a Compose project:

1. Define and run a replication task in Qlik Replicate as described in

Defining a Qlik Replicate

task (page 33)

2. Open Qlik Compose.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

106

5 Data Warehouse projects

3. Add a data warehouse project as described in steps 1-3 of

Adding data warehouse projects

(page 35)

4. In the Databases panel, perform the following steps to define your data warehouse:

a. Click Manage. The Manage Databases window opens.

b. Click the Add New Database link or the New toolbar button. The New Data

Warehouse window opens.

c. In the New Data Warehouse window:

l

In the Name field, specify a display name for your data warehouse.

l

From the Type drop-down list, select Microsoft SQL Server.

l

In the Server Name field, specify the Microsoft SQL Server name using the

following format:

l

To connect to a named Microsoft SQL Server instance: computer_name\db_

server_name

l

To connect to the default Microsoft SQL Server instance: computer_name

l

In the User Name and Password fields, enter your credentials for logging in to

the server specified in the Server Name field.

l

In the Database Name field, specify the name of the database specified in the

target endpoint of the Qlik Replicate task.

l

In the Data Warehouse Schema field, specify dbo or your preferred schema.

l

In the Data Mart Schema field, specify dbo or your preferred schema.

You can specify different schemas for the data warehouse and data

mart tables, but for the purpose of this quick start, we’ll use the same

schema.

l

Click Test Connection to verify that Compose is able to establish a connection

to the specified database and then click OK to save your settings.

d. Click New. The New Data Source window opens.

e. In the New Data Source window:

l

In the Name field, specify a display name for your data source.

l

From the Content Type drop-down list, choose Full Load and Change

Processing.

l

From the Designate By drop-down list, choose Schema.

l

In the Schema name field, enter the schema name that you specified in the

Target Metadata tab of the Replicate task. For more information, see

Defining a

Qlik Replicate task (page 33)

l

In the Error mart schema name field, specify the schema where you want the

data mart exception tables to be created. Data that is rejected by data quality

rules will be copied to tables in the specified schema.

l

Select the Source database connection check box and then provide the

details for connecting to the source database. For the purpose of this tutorial,

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

107

5 Data Warehouse projects

except for the Schema, these should be the same as the data warehouse

connection details.

l

Click Test Connection to verify that Compose is able to establish a connection

to the specified database and then click OK to save your settings.

l

Click OK to save your settings.

5. In the Model panel, perform the following steps to create the model for data warehouse

generation:

a. From the drop-down menu in the top right corner of the Model panel, select Discover.

The Discover window opens.

b. Select the source database (i.e. the database without the "_landing" suffix). This is the

source endpoint in the Qlik Replicate task. The Source Table/View Selection -

Data_Source_Name

>_Landing window opens.

c. In the Source Table/View Selection window:

l

Select the Tables option.

l

Click the Search button.

l

From the Results list, select which tables to discover and then click OK. The

Generating Model from <db_name> window opens.

d. Wait for the model generation to complete and then click Close.

6. In the Data Warehouse panel, perform the following steps to populate the Data Warehouse

with the source data:

a. Click Create. The Creating Data Warehouse window opens. Wait for the Data

Warehouse to be created and then click Close.

b. Click Manage. The Manage Data Warehouse Tasks window opens.

c. Click Generate. The Generating Statements for Task: <Name> window opens. Wait

for the ETL instruction set to be generated and then click Close.

d. Click Run. The Manage Data Warehouse Tasks window switches to Monitor view and

Qlik Compose starts to populate the Data Warehouse with data (this may take a few

seconds).

e. Wait for the Data Warehouse to be populated and then close the Manage Data

Warehouse Tasks window.

7. In the Data Mart panel, perform the following steps to create a data mart with a star schema:

a. Click New. The New Data Mart window opens. Leave the default name.

b. Make sure the Start New Star Schema Wizard check box is selected, and click

OK.The New Star Schema wizard opens. Leave the default name.

c. Select Transactional as the star schema type and then click Next.

d. In the Facts screen, select Order Details. Then click Next.

e. In the Dimensions screen, clear all the check boxes and then select Customers and

Products only, as shown below.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

108

5 Data Warehouse projects

f. Then click Next.

g. In the Transaction Date screen, select OrderDate and then click Finish. The star

schema is displayed on the right of the Manage Data Marts window.

h. Click Create Tables. The Creating Data Mart: <Data Mart Name> window opens.

Wait for the Data Mart tables to be created and then close the window.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

109

5 Data Warehouse projects

i. Click Generate. The Generating Statements for Data Mart: <Data Mart Name>

window opens. Wait for the generation of the task statements to complete and then

close the window.

j. Click Run.

The Manage Data Marts window switches to Monitor view and Qlik Compose

populates the Data Mart with data. Leave the Manage Data Marts window open in

Monitor view for now (The two buttons at the top right of the window allow you to

switch between Designer and Monitor views).

8. To display the data in a pivot table:

a. Click the Pivot toolbar button. The Select Columns for Pivot Table window opens.

b. From the drop-down list at the top of the window, select the Pivot Table columns as

follows:

l

In the 1Fct_Order Details table, select Quantity.

l

In the 1Dim_Customers table, select Country.

l

In the 1Dim_Products table, select ProductName.

c. Click OK. A Pivot Table is created with your selected columns.

d. Drag the Quantity box to the space above the table and the ProductName box to the

space on the left.

e. Select Heatmap from the drop-down list below the Customize Columns button.

Your pivot table should now look like this:

5.4  Setting up a data warehouse connection

This section explains how to set up data warehouse connectivity in a Qlik Compose project.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

110

5 Data Warehouse projects

The data warehouse contains the landing zone tables (the target of the Qlik Replicate task), the

logical entities, the actual data warehouse tables and the data mart tables.

For more information on the data warehouse structure in an project Qlik Compose project, see

Introduction (page 13)

Note that Qlik Compose will not let you add data sources before you add a data warehouse. This is

because the server connection settings for the source landing zone are derived from the data

warehouse settings.

For all supported data warehouse types, each data warehouse schema (or database if

there are no schemas) should be used exclusively for a single data warehouse. In other

words, using the same schema for different projects, data warehouses and landing

zones is not allowed. Data mart schemas, however, can be shared by different data

marts.

For more information on adding data sources, see

Setting up Landing Zone and Data Source

connections (page 131)

For instructions on adding a data warehouse, see the following according to your data warehouse

type.

l

Using Microsoft SQL Server as a data warehouse (page 111)

l

Using Oracle as a data warehouse (page 114)

l

Using Amazon Redshift as a data warehouse (page 121)

l

Using Microsoft Azure Synapse Analytics as a data warehouse (page 124)

l

Using Snowflake as a data warehouse (page 117)

l

Using Google Cloud BigQuery as a Data Warehouse (page 128)

Using Microsoft SQL Server as a data warehouse

Although the procedures in this section specifically refer to Microsoft SQL Server, they

are equally applicable to Microsoft Azure SQL Managed Instance and Microsoft Azure

SQL Database.

It contains the following topics:

l

Prerequisites (page 111)

l

Working with Windows authentication (page 150)

l

Microsoft SQL Server data types (page 112)

l

Defining the connection parameters (page 113)

Prerequisites

Before you can use Microsoft SQL Server as a data warehouse in a Qlik Compose project, make

sure that the following prerequisites have been met:

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

111

5 Data Warehouse projects

Client

Microsoft SQL Server Native Client must be installed on the Qlik Compose machine.

Permissions

To use Microsoft SQL Server as a Data Warehouse a Qlik Compose project, the Compose user must

be granted the following privileges in the Microsoft SQL Server database:

l

The Qlik Compose user must have at least the db_owner user role on the Microsoft SQL Server

database.

l

The Qlik Compose user must be granted the CREATE VIEW permission on the Microsoft SQL

Server database.

l

A Microsoft SQL Server system administrator must provide this permission for all Qlik

Compose users.

Working with Windows authentication

You can configure the Qlik Compose Microsoft SQL Server source to log in to Microsoft SQL Server

using Windows authentication. If you choose this option, you also need to make sure that:

l

The Microsoft SQL Server instance is set up to allow Windows log on.

l

The Compose user is specified as the "Log on as" user for the Qlik Compose Server service

account.

Microsoft SQL Server is configured to allow login for the Qlik Compose Server service

account.

Microsoft SQL Server data types

The following table shows the Microsoft SQL Server data warehouse data types that are supported

when using Qlik Compose and the default mapping from Qlik Compose data types.

For information on how to view the data type that is mapped from the source, see the section for

the source database you are using.

Qlik Compose data types Microsoft SQL Server data types

Bigint BIGINT

Decimal NUMERIC (p,s)

Integer INT

Date DATETIME2

Datetime DATETIME2

GUID UNIQUEIDENTIFIER

IntAutoInc INT IDENTITY

Byte VARBINARY (Length)

Data type mappings

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

112

5 Data Warehouse projects

Defining the connection parameters

This section describes how to use a Microsoft SQL Server database as a data warehouse in a Qlik

Compose project. You can also use the Microsoft SQL Server data warehouse settings to specify

connection details to a Microsoft Azure SQL Database.

When using Microsoft Azure SQL Database as the data warehouse, the data warehouse

must be located on the same database as the landing zone, although it should use a

different schema.

To define Microsoft SQL Server as a data warehouse:

1. Open your project and click Manage in the bottom left of the Databases panel.

The Manage Databases window opens.

2. Click the New toolbar button or click the Add new database link in the middle of the

window.

The New Data Warehouse dialog box opens.

3. From the Type drop-down list, select the desired data warehouse.

4. Enter the information as described below.

l

Connection input mode: Select Standard or Advanced.

If you selected Standard connection input mode, specify the following:

l

Server Name: The name or IP address of the Microsoft SQL Server server

machine.

l

Port: Optionally, change the default port.

When connecting to a named instance using the instance name, either change

the port to 0 to use the SQL Server Browser service to redirect.

l

Windows authentication / SQL Server authentication: Choose how you want

Compose to log in to the Microsoft SQL Server database. If you choose

Windows authentication, see Working with Windows Authentication below.

l

User Name: The user name for accessing the Microsoft SQL Server database.

The specified user must have read/write privileges on the Microsoft SQL Server

database.

l

Password: The password for accessing the Microsoft SQL Server database.

If you selected Advanced connection input mode, specify the following:

l

Password: The password for accessing the Microsoft SQL Server database.

l

ODBCConnection String: The string of parameters required to connect to the

Microsoft SQL Server ODBCDriver.

l

JDBCConnection String: The string of parameters required to connect to the

Microsoft SQL Server JDBCDriver.

Compose will concatenate the database name to the ODBC/JDBC

connection string.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

113

5 Data Warehouse projects

l

Warehouse Properties: Specify the following:

l

Database Name: The name of the Microsoft SQL Server database.

l

Data Warehouse Schema: The schema in which to create the data warehouse

tables.

l

Data Mart Schema: The schema in which to create the data mart.

5. Click Test Connection to verify that Compose is able to establish a connection with the

specified data warehouse.

6. Click OK to save your settings.

The database is added to the list on the left side of the Manage Databases window.

Working with Windows authentication

You can configure Qlik Compose for Data Warehouses to log in to Microsoft SQL Server using

Windows authentication.

If you choose this option, you also need to make sure that:

l

The Microsoft SQL Server instance is set up to allow Windows log on.

l

The Qlik Compose for Data Warehouses user is specified as the "Log on as" user for the Qlik

Compose for Data Warehouses service account.

-OR-

l

Microsoft SQL Server is configured to allow login for the Qlik Compose for Data Warehouses

service account.

Using Oracle as a data warehouse

This section describes how to set up Oracle as a data warehouse in a Compose project.

When loading a huge number of records (i.e. hundreds of millions), the

UNDO/REDO/TEMP tablespace on the Oracle database must be large enough to hold the

data being loaded.

It contains the following topics:

l

Prerequisites (page 114)

l

Oracle data types (page 116)

l

Defining the connection parameters (page 116)

Prerequisites

Before you can use Oracle as a data warehouse in a Qlik Compose project, make sure that the

following prerequisites have been met:

Client

Before you can use Oracle as a source in a Qlik Compose project, make sure that the following client

prerequisites have been met:

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

114

5 Data Warehouse projects

l

The Oracle database should be configured with the required permissions (see below) and

accessible from the Compose machine.

l

Install Oracle Data Access Components (x64) on the computer where Qlik Compose is

located. Then, add the full path of the Oracle Data Access DLL to the system environment

variables.

The default path should be:

<ORACLE_PRODUCT_CLIENT_DIR>\ODP.NET\bin\4\



The path to the Oracle Data Access DLL also needs to be specified in both

the machine.conf file and the Global Assembly Cache (GAC). In addition,

make sure that the Oracle.DataAccess.dll file exists in the following

location: C:\Windows\Microsoft.NET\assembly\GAC_64.

For more information, see

Oracle Help Center



The Qlik Compose service needs to be restarted after installing the

required components.

l

Install Oracle Instant Client for Microsoft Windows (x64) 19.0 or later on the computer where

Qlik Compose is located.

l

If you want to use an Oracle TNS name in the connection settings, you first need to set the

ORACLE_HOME environment variable.

Example:

<ORACLE_PRODUCT_CLIENT_DIR>\product\<version>\client_1

Permissions

To use Oracle as a Data Warehouse a Qlik Compose project, the Compose user must be granted the

following privileges in the Oracle database:

l

grant create session

l

grant create table

l

grant create view

l

grant connect

l

grant resource

l

grant create sequence

l

grant create any directory

l

grant SELECT on SYS.DBA_REGISTRY

l

grant select any table

l

grant delete any table

l

grant drop any table

l

grant unlimited tablespace

l

grant create any table

l

grant insert any table

l

grant update any table

l

grant drop any table

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

115

5 Data Warehouse projects

l

grant alter any table

l

grant create any view

l

grant drop any view

l

grant create any index

Oracle data types

The Oracle database for Qlik Compose supports most Oracle data types. The following table shows

the Oracle data warehouse data types that are supported when using Qlik Compose and the default

mapping from Qlik Compose data types.

For information on how to view the data type that is mapped from the source, see the section for

the source database you are using.

Qlik Compose data types Oracle data types

Bigint DECIMAL (19,0)

Date DATE

Datetime DATE

Decimal DECIMAL (p,s)

Integer DECIMAL (10,0)

GUID VARCHAR (38)

IntAutoInc DECIMAL (10,0)

Byte raw (${LENGTH})

Data type mappings

Defining the connection parameters

This section describes how to use an Oracle database as a data warehouse in a Qlik Compose

project.

To define Oracle as a data warehouse:

1. Open your project and click Manage in the bottom left of the Databases panel.

The Manage Databases window opens.

2. Click the New toolbar button or click the Add new database link in the middle of the

window.

The New Data Warehouse dialog box opens.

3. From the Type drop-down list, select the desired data warehouse.

4. Enter the information as described below.

l

Connection input mode: Select Standard or Advanced.

If you selected Standard connection input mode, specify the following:

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

116

5 Data Warehouse projects

l

Server Name: The name or IP address of the Oracle server machine.

l

Port: If you specified a TNS name in the Server Name field, make sure this field

is empty. Optionally, change the default port.

l

User Name: The user name for accessing the Oracle database. The specified

user must have read/write privileges on the Oracle database.

l

Password: The password for accessing the Oracle database.

l

SID: If you specified a TNS name in the Server Name field, make sure this field

is empty. Optionally, specify the Oracle SID.

If you selected Advanced connection input mode, specify the following:

l

Password: The password for accessing the Oracle database.

l

ODBCConnection String: The string of parameters required to connect to the

Oracle ODBCDriver.

l

JDBCConnection String: The string of parameters required to connect to the

Oracle JDBCDriver.

Compose will concatenate the database name to the ODBC/JDBC

connection string.

l

Warehouse Properties: Specify the following:

l

Data Warehouse Schema: The schema in which to create the data warehouse

tables.

l

Data Mart Schema: The schema in which to create the data mart.

l

Maximum length of identifier names is 128: Select this check box to limit the

length of Oracle dimension table identifier names to 128 characters. This should

match your Oracle configuration. This option is available for Oracle versions

starting from 12.2.

5. Click Test Connection to verify that Compose is able to establish a connection with the

specified data warehouse.

6. Click OK to save your settings.

The database is added to the list on the left side of the Manage Databases window.

Using Snowflake as a data warehouse

This section describes how to set up Snowflake as a data warehouse in a Compose project.

It contains the following topics:

l

Prerequisites (page 117)

l

Limitations (page 118)

l

Snowflake data types (page 119)

l

Defining the connection parameters (page 119)

Prerequisites

Before you can use Snowflake as a data warehouse in a Qlik Compose project, make sure that the

following prerequisites have been met:

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

117

5 Data Warehouse projects

Client

l

Download and install Snowflake ODBC driver for Windows 2.18.1 or later.

l

The Qlik Compose machine must be set to the correct time (UTC).

Permissions

The user specified in the Snowflake data warehouse settings must be associated with a role that

grants the following privileges:

l

USAGE on the Snowflake warehouse

l

USAGE or OWNERSHIP on the specified database and its schemas

l

SELECT on the INFORMATION_SCHEMA schemas

l

CREATE SCHEMA on the specified database.

Only required for user-specified schemas that do not yet exist on the target.

l

Tables:

l

CREATE

l

SELECT

l

INSERT

l

UPDATE

l

DELETE

l

TRUNCATE

l

REFERENCES (for current and future tables)

l

DROP (for user-initiated Drop and Create operations)

l

Views:

l

SELECT

l

CREATE (for current and future views)

l

DROP (for user-initiated Drop and Create operations)

l

External functions called by user-defined ETLs:

l

USAGE

Limitations

The following limitations apply when using Snowflake as a data warehouse in a Compose project.

l

Variant, object and array columns are not supported when creating the model using the

discovery method. Compose will ignore such columns during discovery and issue a warning.

l

When discovering the landing zone, Snowflake converts

all

numeric data types to NUMBER

(38,0) and is therefore not recommended. For example, discovering a table with INTEGER i

and DOUBLE d columns in the landing zone would return NUMBER (38,0) for both, whereas

discovering these columns in the source would return more accurate data types.

l

When ingesting data from a Replicate source that may have BIT fields (such as Microsoft SQL

Server), it is recommended to define a global data type transformation in Replicate to

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

118

5 Data Warehouse projects

convert BIT to STRING (1). Otherwise, Compose will convert BIT to VARCHAR (1) (as it does

not support BOOLEAN), which may cause a data type mismatch in the landing zone.

Snowflake data types

The following table shows the Snowflake data warehouse data types that are supported when

using Qlik Compose and the default mapping from Qlik Compose data types.

For information on how to view the data type that is mapped from the source, see the section for

the source database you are using.

Qlik Compose data types Snowflake data types

BIGINT INTEGER

INTEGER INTEGER

DECIMAL If column size >38, then:

DOUBLE

If column size <38, then:

DECIMAL (size, scale)

DATE DATE

TIME TIME

DATETIME TIMESTAMP (scale 1-9)

GUID VARCHAR (38)

BIGINTAUTOINC BIGINT IDENTITY

BYTE BINARY

Data type mappings

Defining the connection parameters

This section describes how to add Snowflake as a data warehouse in a Qlik Compose project.

To add Snowflake as a data warehouse:

1. Open your project and click Manage in the bottom left of the Databases panel.

The Manage Databases window opens.

2. Click the New toolbar button or click the Add new database link in the middle of the

window.

The New Data Warehouse dialog box opens.

3. From the Type drop-down list, select the desired data warehouse.

4. Enter the information as described below.

l

Connection input mode: Select Standard or Advanced.

If you selected Standard connection input mode, specify the following:

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

119

5 Data Warehouse projects

l

Server Name: The URL for accessing Snowflake on AWS or Snowflake on

Microsoft Azure.

l

Port: The port through which Snowflake will be accessed (default 443).

l

Authentication method: Choose Username and password or Key pair.

l

Private key: If you selected Key pair authentication, paste the private key file

content into this field.

l

User Name: The user name for accessing the Snowflake database. The

specified user must have read/write privileges on the Snowflake database.

l

Private key password: If you selected Key pair authentication and the private

key file content is encrypted, specify the private key file password in this field.

l

Password: If you selected Username and password authentication, specify

the password for accessing the Snowflake database.

If you selected Advanced connection input mode, specify the following:

l

Authentication method: Choose Username and password or Key pair.

l

Password: If you selected Username and password authentication, enter the

password for accessing the Snowflake database.

l

Private key: If you selected Key pair authentication, paste the private key file

content into this field.

l

User name: If you selected Key pair authentication, specify the user name for

accessing the Snowflake database. The specified user must have read/write

privileges on the Snowflake database.

l

Private key password: If you selected Key pair authentication and the private

key file content is encrypted, specify the private key file password in this field.

l

ODBCConnection String: The string of parameters required to connect to the

Snowflake ODBCDriver.

l

JDBCConnection String: The string of parameters required to connect to the

Snowflake JDBCDriver.

Compose will concatenate the warehouse name to the ODBC/JDBC

connection string.

You can connect to Snowflake with ODBC, using a proxy server and

entering the appropriate ODBC environment parameters. For details,

see

ODBC Configuration and Connection Parameters - Snowflake

Documentation

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

120

5 Data Warehouse projects

You can connect to Snowflake with JDBC, using a proxy server and

entering the appropriate JDBCconnection string:

jdbc:snowflake://<Snowflake server URL>:443/?&user=<snowflake

user name>&warehouse=<Snowflake Warehouse

name>&useProxy=true&proxyHost=<Proxy server

name>&proxyPort=<Proxy server listening port>&proxyUser=<proxy

server user name>&proxyPassword=<proxy server user's password>

l

Warehouse Properties: Specify the following:

l

Warehouse Name: The name of your Snowflake warehouse.

"Warehouse" refers to the Snowflake warehouse and should not be

confused with the data warehouse created by Compose.

l

Database Name: The database in which to create the data warehouse tables.

l

Data Warehouse Schema: The schema in which to create the data warehouse

tables.

l

Data Mart Schema: The schema in which to create the data mart.

5. Click Test Connection to verify that Compose is able to establish a connection with the

specified data warehouse.

6. Click OK to save your settings.

The database is added to the list on the left side of the Manage Databases window.

Using Amazon Redshift as a data warehouse

This section describes how to set up Amazon Redshift as a data warehouse in a Compose project.

It contains the following topics:

l

Prerequisites (page 121)

l

Amazon Redshift data types (page 122)

l

Defining the connection parameters (page 123)

Prerequisites

Before you can use Amazon Redshift as a data warehouse in a Qlik Compose project, make sure

that the following prerequisites have been met:

Driver

l

Install and configure the latest Amazon Redshift 64-bit ODBC Driver.

l

Install the Amazon Redshift JDBC Driver on the Compose machine. Amazon are working to

resolve a known issue with their latest driver. Until the issue is resolved, JDBC driver 4.1

needs to be used instead.

1. Download the JARfile from:

https://mvnrepository.com/artifact/com.amazon.redshift/redshift-jdbc41/1.2.10.1009

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

121

5 Data Warehouse projects

2. Copy it to:

<Compose_Installation_Folder>\java\jdbc

The Qlik Compose service needs to be restarted after copying the driver.

Amazon Redshift Cluster

If you haven't already done so, set up an Amazon Redshift cluster and make sure that following

information about your Amazon Redshift Cluster is readily available:

l

Amazon Redshift Cluster Name

l

Amazon Redshift Cluster Port

l

Amazon Redshift User Name and Password

l

Amazon Redshift Database Name

Permissions

Qlik Replicate performs the following operations on the replicated tables within Amazon Redshift:

l

SELECT, INSERT, UPDATE and DELETE

l

Bulk Load

l

CREATE, ALTER, DROP

l

CREATE VIEW

If the user is the 'DB Owner', these permissions are in place by default. Otherwise, the user must be

granted these permissions to achieve successful replication.

Port

Make sure that port 5439 (the Amazon Redshift Cluster port) is open for inbound connections from

Qlik Compose .

Amazon Redshift data types

The following table shows the Amazon Redshift data warehouse data types that are supported

when using Qlik Compose and the default mapping from Qlik Compose data types.

For information on how to view the data type that is mapped from the source, see the section for

the source database you are using.

Qlik Compose data types Amazon Redshift data types

INTEGER INT4

BIGINT INT8

DECIMAL NUMERIC (p,s)

DATE DATE

Data type mappings

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

122

5 Data Warehouse projects

Qlik Compose data types Amazon Redshift data types

GUID VARCHAR (38)

DATETIME TIMESTAMP

BYTE VARCHAR (Length in Bytes)

TIME VACHAR (20)

Defining the connection parameters

This section describes how to use an Amazon Redshift database as a data warehouse in a Qlik

Compose project.

To define Amazon Redshift as a data warehouse:

1. Open your project and click Manage in the bottom left of the Databases panel.

The Manage Databases window opens.

2. Click the New toolbar button or click the Add new database link in the middle of the

window.

The New Data Warehouse dialog box opens.

3. From the Type drop-down list, select the desired data warehouse.

4. Enter the information as described below.

l

Connection input mode: Select Standard or Advanced.

If you selected Standard connection input mode, specify the following:

l

Server Name: The name or IP address of the Amazon Redshift cluster.

l

Port: Optionally, change the default port.

l

User Name: The user name for accessing the Amazon Redshift database. The

specified user must have read/write privileges on the Amazon Redshift

database.

l

Password: The password for accessing the Amazon Redshift database.

If you selected Advanced connection input mode, specify the following:

l

Password: The password for accessing the Amazon Redshift database.

l

ODBCConnection String: A string of parameters required to connect to the

Amazon Redshift ODBCDriver. After entry, click Test to verify that a connection

was established.

l

JDBCConnection String: A string of parameters required to connect to the

Amazon Redshift JDBCDriver. After entry, click Test to verify that a connection

was established.

You must include the name of the Amazon Redshift database in the

connection string.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

123

5 Data Warehouse projects

l

Warehouse Properties: Specify the following:

l

Database Name: The name of the Amazon Redshift database.

l

Data Warehouse Schema: The schema in which to create the data warehouse

tables.

l

Data Mart Schema: The schema in which to create the data mart.

l

More Options: Click to see or hide the following advanced options:

l

Character column size in bytes: This should be calculated according to the

largest value you are likely to store in a VARCHAR column. Tables in the Landing

Zone will be divided by the specified value (and rounded up).

For example, if the value of Character column size in bytes is 3 (the default),

both VARCHAR (12 bytes) and VARCHAR (10 bytes) will be discovered as VARCHAR (4

characters).

See: Use the smallest possible columnsize - Amazon Redshift

If this value is changed, existing tables will not be affected (i.e. the

change will only take effect if new columns are added to the model

and the data warehouse tables are updated accordingly).

5. Click Test Connection to verify that Compose is able to establish a connection with the

specified data warehouse.

6. Click OK to save your settings.

The database is added to the list on the left side of the Manage Databases window.

Using Microsoft Azure Synapse Analytics as a data warehouse

This section describes how to set up Microsoft Azure Synapse Analytics as a data warehouse in a

Compose project.

By default, Compose creates tables in Microsoft Azure Synapse Analytics as a

CLUSTERED COLUMNSTORE INDEX, which offers the best overall query performance

for large tables. Depending on your environment though, you might want to override the

default to create all tables or specific tables as a HEAP, for example, which is optimized

for smaller tables. For information on how to accomplish this, see Table creation

modifiers tab (page 42) in the Project Settings section.

It contains the following topics:

l

Prerequisites (page 124)

l

Microsoft Azure Synapse Analytics data types (page 125)

l

Defining the connection parameters (page 126)

Prerequisites

Before you can use Microsoft Azure Synapse Analytics as a data warehouse in a Qlik Compose

project, make sure that the following prerequisites have been met:

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

124

5 Data Warehouse projects

Install the Required Client

Install SQL Server Native Client 11 (for connecting to Microsoft Azure Synapse Analytics) on the

Compose machine.

Permissions

The user specified in the Microsoft Azure Synapse Analytics connection settings must be granted

the following permissions.

Permission required for the specified target database:

The Compose must be granted the db_owner user role on the specified target database.

Permission required for the master database:

The Compose must be granted SELECT access (by adding the user to the master database and

then to the db_readers role, for example).

Open the Required Firewall Port(s)

l

When Compose runs on a machine outside Azure - Open port 1433 for outbound traffic.

l

When Compose runs on an AzureVM - Open the following ports for outbound traffic:

l

1433

l

11000-11999

l

14000-14999

Microsoft Azure Synapse Analytics data types

The following table shows the Microsoft Azure Synapse Analytics data warehouse data types that

are supported when using Qlik Compose and the default mapping from Qlik Compose data types.

For information on how to view the data type that is mapped from the source, see the section for

the source database you are using.

Qlik Compose data types Microsoft Azure Synapse Analytics data types

BIGINT BIGINT

DECIMAL DECIMAL (p,s)

INTEGER INTEGER

DATE DATE

DATETIME DATETIME2 (s)

TIME TIME

GUID VARCHAR (38)

BIGINTAUTOINC BIGINT

Data type mappings

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

125

5 Data Warehouse projects

Qlik Compose data types Microsoft Azure Synapse Analytics data types

BYTE VARBINARY (Length)

VARCHAR (Length) VARCHAR (Length)

NVARCHAR (Length) NVARCHAR (Length)

Defining the connection parameters

This section describes how to use a Microsoft Azure Synapse Analytics database as a data

warehouse in a Qlik Compose project.

When using Microsoft Azure Synapse Analytics as the data warehouse, the data

warehouse database must be the same as the database that you will later define for the

landing zone, although it should use a different schema.

To define Microsoft Azure Synapse Analytics as a data warehouse:

1. Open your project and click Manage in the bottom left of the Databases panel.

The Manage Databases window opens.

2. Click the New toolbar button or click the Add new database link in the middle of the

window.

The New Data Warehouse dialog box opens.

3. From the Type drop-down list, select the desired data warehouse.

4. Enter the information as described below:

l

Connection input mode: Select Standard or Advanced.

If you selected Standard connection input mode, specify the following:

l

Server Name: The name of the Microsoft Azure Synapse Analytics server you

are using.

l

Port: The port number for the Microsoft Azure Synapse Analytics.

l

User Name: The user name of a registered Microsoft Azure Synapse Analytics

user.

l

Password: The password for the user entered in the User name field.

If you selected Advanced connection input mode, specify the following:

l

Password: The password for the user entered in the User name field.

l

ODBCConnection String: The string of parameters required to connect to the

Microsoft Azure SQL ODBCDriver.

l

JDBCConnection String: The string of parameters required to connect to the

Microsoft Azure SQL JDBCDriver.

Compose will concatenate the database name to the ODBC/JDBC

connection string.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

126

5 Data Warehouse projects

l

Warehouse Properties: Specify the following:

l

Database Name: The name of the target database. This must be the same as

the target endpoint defined in the Qlik Replicate task. For more information, see

Defining a Qlik Replicate task (page 33)

l

Data Warehouse Schema: The schema in which to create the data warehouse

tables.

l

Data Mart Schema: The schema in which to create the data mart.

l

More Options: Click to see or hide the following advanced options:

l

Additional JDBC Parameters: Any additional parameters you need to add to

the default JDBC connection string. The parameters should be separated by a

semi-colon.

Format:PARAM1=VALUE1;PARAM2=VALUE2

l

Additional ODBC Parameters: Any additional parameters you need to add to

the default ODBC connection string. The parameters should be separated by a

semi-colon.

Format:PARAM1=VALUE1;PARAM2=VALUE2

5. Click Test Connection to verify that Compose is able to establish a connection with the

specified data warehouse.

6. Click OK to save your settings.

The database is added to the list on the left side of the Manage Databases window.

Identifier labels

Several statements are tagged with an identifier label for troubleshooting 'problem queries' and

identifying possible ways to optimize database settings. The addition of labels to ELT queries

enables fine-grained workload management and workload isolation via Synapse WORKLOAD

GROUPS and CLASSIFIERS.

The identifier labels are as follows:

Table type Tag

Hubs CMPS_HubIns

Satellites CMPS_SatIns

Type1 dimensions CMPS_<data mart name>_DimT1_Init/CMPS_<data

mart name>_DimT1_Incr

Type2 dimensions CMPS_<data mart name>_DimT2_Init/CMPS_<data

mart name>_DimT2_Incr

Transactional facts CMPS_<data mart name>_FctTra_Init/CMPS_<data

mart name>_FctTra_Incr

State-oriented facts CMPS_<data mart name>_FctStO_Init

Aggregated facts CMPS_<data mart name>_FctAgg_Init

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

127

5 Data Warehouse projects

Using Google Cloud BigQuery as a Data Warehouse

This section describes how to set up Google Cloud BigQuery as a data warehouse in a Compose

project.

It contains the following topics:

l

Prerequisites (page 128)

l

Limitations and Considerations (page 128)

l

Supported Data Types (page 129)

l

Setting General Connection Properties (page 130)

Prerequisites

Before you can use Google Cloud BigQuery as a data warehouse in a Qlik Compose project, make

sure the prerequisites described below have been met.

Permissions

When you create your Service Account Key for Google Cloud, make sure to select BigQuery >

BigQuery Data Owner as the Role. Leave the default key type (JSON) unchanged.

As part of the Service Account Key creation process, a JSON file containing the connection

information will be downloaded to your computer. You will need to copy the contents of this file to

the Service account key field in the Data Warehouse settings.

Client Prerequisites

Both the Simba ODBC driver and the Simba JDBC driver need to be installed on the Compose

machine.

To do this:

1. Download the following drivers from https://cloud.google.com/bigquery/providers/simba-

drivers:

l

Simba ODBC driver 2.3.0 or later (MSI file)

l

Simba JDBC driver 1.2.2 or later (Zip file)

2. To install the ODBC driver, simply run the installer on the Compose machine.

3. To install the JBDC driver, extract

all

of the files to the <COMPOSE_INSTALL_

DIR>\java\jdbc folder on the Compose machine.

When installing driver versions later than 1.2.22.1026, after extracting the files to

the jdbc folder, you must delete the gson-<version>.jar file from the folder.

Otherwise, an error will occur.

4. Restart the Compose service.

Limitations and Considerations

The following limitations apply when using Google Cloud BigQuery as a data warehouse in a

Compose project.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

128

5 Data Warehouse projects

l

The dataset(s) specified in the connection settings must already exist before loading data

into Google Cloud BigQuery.

l

When ingesting data from a Replicate source that may have BIT columns (such as Microsoft

SQL Server), it is recommended to define a global data type transformation in Replicate to

convert BIT to STRING (1). Otherwise, Compose will convert BIT to VARCHAR (1) (as it does

not support BOOLEAN), which may cause a data type mismatch in the Landing Zone.

l

When discovering from a BigQuery landing database, BOOLEAN and FLOAT columns are not

supported and will be ignored. If you need such columns to be ingested to the data

warehouse, the following workarounds are available:

l

Discover from the source database

l

Convert these data types (which are not supported in Compose) to another type such

as VARCHAR (1) or INT

l

The data warehouse data set and landing data set must be in the same region.

l

As strings do not have length in BigQuery, when discovering from the Landing Zone,

Compose will assume a default length of VARCHAR(32767). From a practical perspective,

since these strings will also be created on BigQuery, they will have no runtime length either.

To keep things orderly however, best practice is to change strings of known length to their

actual expected length.

l

Commonly used BigQuery functions were added to the Compose Expression Builder.

BigQuery SQL commands that are not listed in the Compose Expression Builder can be

entered manually if required.

l

BigQuery does not support altering tables via standard DDL operations. To work around this

limitation, Compose creates a script that copies the data to a new table. After the data is

copied to the new table, make sure to delete the old table.

l

Aggregated fact and state oriented data mart are not supported.

l

Stored procedures in custom ETLs are not supported.

l

Clustering keys are not supported.

Supported Data Types

The following table shows the Google Cloud BigQuery data warehouse data types that are

supported when using Qlik Compose and the default mapping from Qlik Compose data types.

For information on how to view the data type that is mapped from the source, see the section for

the source database you are using.

Compose Data Type BigQuery Data Type

BYTES BYTES

DATE DATE

TIME TIME

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

129

5 Data Warehouse projects

DATETIME If scale <=6:

TIMESTAMP If scale >6:

STRING

BIGINT INTEGER

DECIMAL If the data can be stored in 38,9, then:

NUMERIC

If not, then:

STRING with length of original precision +2

VARCHAR STRING

INTEGER INTEGER

Unsupported Data Types

The following Google Cloud BigQuery data types are not supported:

GEOGRAPHY, STRUCT, and ARRAY.

Setting General Connection Properties

This section describes how to use a Google Cloud BigQuery database as a data warehouse in a Qlik

Compose project.

To define Google Cloud BigQuery as a data warehouse:

1. Open your project and click Manage in the bottom left of the Databases panel.

The Manage Databases window opens.

2. Click the New toolbar button or click the Add new database link in the middle of the

window.

The New Data Warehouse dialog box opens.

3. From the Type drop-down list, select the desired data warehouse.

4. Enter the information as described in the table below.

l

Connection input mode: Select Standard or Advanced.

If you selected Standard:

l

Paste the contents of the JSON file (including curly brackets) that was

downloaded when you created your BigQuery service account key, into the

Service account key field.

If you selected Advanced, specify the following:

l

ODBCConnection String: Enter a string of parameters required to connect to

BigQuery via the Simba ODBCDriver.

l

JDBCConnection String: Enter a string of parameters required to connect to

BigQuery via the Simba JDBCDriver.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

130

5 Data Warehouse projects

Compose will concatenate the dataset name to the ODBC/JDBC

connection string.

l

Region: Where to upload the dataset created by Compose.

l

Data Warehouse dataset: Specify the dataset in which to create the data warehouse

tables.

l

Data mart dataset: Specify the dataset in which to create the data mart.

5. Click Test Connection to verify that Compose is able to establish a connection with the

specified data warehouse.

6. Click OK to save your settings.

The database is added to the list on the left side of the Manage Databases window.

Managing databases

You can edit and delete databases as required. The table below describes the available options.

To Do this

Edit a

database

In the left side of the Manage Databases window, select the database that you

want to edit and then click the Edit toolbar button.

Delete a

database

In the left side of the Manage Databases window, select the database that you

want to delete and then click the Delete toolbar button.

Click Yes when prompted to confirm the deletion.

Database management options

5.5  Setting up Landing Zone and Data Source

connections

This section explains how to set up landing zone and data source connectivity in a Qlik Compose

project. Note that although you must configure landing zone connectivity, data source connectivity

is only required if you want to discover the source database defined in the Replicate task.

For a list of the pros and cons of discovering the source database as opposed to the landing zone,

see

Discovering the Source Database or Landing Zone (page 156)

Reserved column names and suffixes

The following section lists the reserved column names and suffixes. If the any of the discovered

tables contain columns with these names or suffixes, you need to rename them in Compose. For

information on renaming columns, see

Managing attributes (page 169)

Reserved column names:

l

BIR_MAPPING_NR - internal mapping identifier used in staging tables for ETL

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

131

5 Data Warehouse projects

l

ROWNR - internal row identifier used in staging tables for ETL

l

RUNNO_INSERT - The task run number for INSERT operations.

l

RUNNO_UPDATE - The task run number for UPDATE operations.

l

OBSOLETE__INDICATION - Used to mark OBSOLETE records in data mart objects. See also:

The "Obsolete" indicator (page 262)

l

TR_ID - The unique Transaction ID for a fact table record.

l

BID_OCCS - Internal column used in ETL processing.

l

FD - This column is added to tables that contain attributes (columns) with a History Type 2.

The column is used to delimit the range of dates for a given record version. The column name

can be changed in the project settings.

If you change the "From Date" name in the project settings, the new name will

become a reserved word.

l

TD - This column is added to tables that contain attributes (columns) with a History Type 2.

The column is used to delimit the range of dates for a given record version. The column name

can be changed in the project settings.

If you change the "To Date" name in the project settings, the new name will

become a reserved word.

l

FKNR - Foreign key number column used in logging tables to report missing references

captured via the data warehouse ETL

Reserved suffixes in data darts:

l

_OID

l

_VID

Permissions

This section lists the required permissions for the source landing zone and the source database

defined in a Qlik Replicate task.

l

Landing Zone permissions (page 132)

l

Source database permissions (page 133)

Landing Zone permissions

For proper operation, the user specified in the landing zone database connection settings must be

granted the following permissions:

l

Read metadata

l

Select from tables

l

Create tables (for error marts)

l

Insert to tables (error marts)

For information on the landing zone, see Landing Zone settings.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

132

5 Data Warehouse projects

Source database permissions

To generate the model by discovering the source database in the Replicate task, you need to define

a connection to the source database used in the Replicate task. The user defined in the Source

database connection settings must be granted the following permissions:

l

Read metadata (Columns, Primary Keys and Foreign Keys)

l

Select from tables

For more information, see Source Database Connection.

Data type mappings

This topic lists the data type mappings from the supported source databases or the supported

landing zone databases (where applicable) to the Qlik Compose data types. Note that as MySQL

and IBM DB2 for LUW are not supported as data warehouses in Compose, the mappings in those

sections are applicable to their role as source databases only.

In this topic:

l

Oracle data types (page 133)

l

Microsoft SQL Server data types (page 134)

l

MySQL data types (page 136)

l

Amazon Redshift data types (page 138)

l

IBM DB2 for LUW data types (page 138)

l

Microsoft Azure Synapse Analytics data types (page 139)

l

Google Cloud BigQuery data types (page 140)

Oracle data types

The Oracle database for Qlik Compose supports most Oracle data types. The following table shows

the Oracle source data types that are supported when using Qlik Compose and the default mapping

to Qlik Compose data types.

For information on how to view the data type that is mapped in the data warehouse, see the section

for the data warehouse database you are using.

Oracle data types Qlik Compose data types

CHAR Varchar

NCHAR(40) Varchar(80)

VARCHAR(2) VARCHAR

NUMBER Decimal

FLOAT Decimal(38,12)

REAL Decimal(38,12)

Data type mappings

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

133

5 Data Warehouse projects

Oracle data types Qlik Compose data types

DATE Date

TIMESTAMP(6) Date

TIMESTAMP(6) WITH LOCAL TIME ZONE Date

TIMESTAMP(6) WITH TIME ZONE Date

DOUBLE PRECISION Decimal(38,12)

Non-supported data types

Source Oracle tables with columns of the following Oracle data types are not supported and will be

ignored.

l

BLOB

l

CLOB

l

NCLOB

l

BFILE

l

BINARY_FLOAT

l

BINARY_DOUBLE

l

INTERVAL YEAR (2) TO MONTH

l

INTERVAL DAY (6) TO SECOND (5)

l

RAW

l

ROWID

l

UROWID

l

LONG

Microsoft SQL Server data types

The following table shows the Microsoft SQL Server source data types that are supported when

using Qlik Compose and the default mapping to Qlik Compose data types.

For information on how to view the data type that is mapped in the data warehouse, see the section

for the data warehouse database you are using.

Microsoft SQL Server data types Qlik Compose data types

char Varchar

nchar Varchar

bit Integer

tinyint Integer

smallint Integer

Data type mappings

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

134

5 Data Warehouse projects

Microsoft SQL Server data types Qlik Compose data types

INT Integer

BIGINT Bigint

decimal Decimal

numeric Decimal

smallmoney Decimal(11,4)

money Decimal(20,4)

float Decimal(38,12)

real Decimal(18,6)

datetime Date

datetime2 Date

smalldatetime Date

BINARY BYTE

date Date

time Varchar(16)

uniqueidentifier GUID

Non-supported data types

Source Microsoft SQL Server tables with columns of the following Microsoft SQL Server data types

are not supported and will be ignored:

l

BLOB

l

CLOB

l

NCLOB

l

VARCHAR(MAX)

l

TEXT

l

NVARCHAR(MAX)

l

NVARCHAR (LENGTH)

l

NTEXT

l

VARBINARY

l

IMAGE

l

DATETIMEOFFSET

l

TIMESTAMP

l

SQL_VARIANT

l

XML

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

135

5 Data Warehouse projects

MySQL data types

The following table shows the MySQL source data types that are supported when using Qlik

Compose and the default mapping to Qlik Compose data types.

For information on how to view the data type that is mapped in the data warehouse, see the section

for the data warehouse database you are using.

MySQL data types Qlik Compose data types

BIGINT Bigint

binary BYTE

bit bigint

char Varchar

date Date

datetime Date

DECIMAL Decimal

double Decimal(38,12)

ENUM('x-small', 'small', 'medium', 'large', 'x-large') Varchar(7)

FLOAT Decimal(38,12)

int integer

MEDIUMINT integer

MEDIUMTEXT Varchar(16777215)

nchar(36) Varchar(36)

NUMERIC Decimal

REAL Decimal(38,12)

set('a','b','c','d') Varchar(7)

SMALLINT integer

TEXT Varchar(65535)

time Date

timestamp Date

TINYINT integer

TINYTEXT Varchar(255)

year integer

Data type mappings

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

136

5 Data Warehouse projects

Non-supported data types

Source MySQL tables with columns of the following MySQL data types are not supported and will

be ignored:

l

GEOMETRY

l

GEOMETRYCOLLECTION

l

JSON

l

linestring

l

LONGblob

l

LONGTEXT

l

mediumblob

l

MULTILINESTRING

l

MULTIPOINT

l

MULTIPOLYGON

l

point

l

polygon

l

tinyblob

l

BIT(64)

l

BLOB()

l

BIGBLOB

l

MEDIUMBLOB

l

TINYBLOB

l

BLOB

l

varbinary (20)

Snowflake data types

The following table shows the Snowflake data types that are supported when using Qlik Compose

and the default mapping to Qlik Compose data types.

For information on how to view the data type that is mapped in the data warehouse, see the section

for the data warehouse database you are using.

Snowflake data types Qlik Compose data types

NUMBER DECIMAL

FLOAT DECIMAL

VARCHAR VARCHAR

BINARY BYTE

BOOLEAN VARCHAR (5)

DATE DATE

Data type mappings

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

137

5 Data Warehouse projects

Snowflake data types Qlik Compose data types

TIME TIME

TIMESTAMP_NTZ DATETIME(9)

TIMESTAMP_LTZ DATETIME(9)

TIMESTAMP_TZ DATETIME(9)

VARIANT JSON or XML as determined by the data source settings.

OBJECT N/A

ARRAY N/A

Amazon Redshift data types

The following table shows the Amazon Redshift data warehouse data types that are supported

when using Qlik Compose and the default mapping to Qlik Compose data types.

For information on how to view the data type that is mapped in the data warehouse, see the section

for the data warehouse database you are using.

Amazon Redshift data types Qlik Compose data types

SMALLINT INTEGER

INTEGER INTEGER

BIGINT BIGINT

DECIMAL DECIMAL

REAL DECIMAL (18,6)

DOUBLE PRECISION DECIMAL (38,12)

BOOLEAN INTEGER

CHAR VARCHAR

DATE DATE

TIMESTAMP DATETIME

Data type mappings

IBM DB2 for LUW data types

The following table shows the IBM DB2 for LUW source data types that are supported when using

Qlik Compose and the default mapping to Qlik Compose data types.

For information on how to view the data type that is mapped in the data warehouse, see the section

for the data warehouse database you are using.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

138

5 Data Warehouse projects

IBM DB2 for LUW data types Qlik Compose data types

DATE DATE

TYPE_TIMESTAMP DATE

TIMESTAMP DATE

TYPE_TIME DATE

TYPE_DATE DATE

REAL DECIMAL (18,6)

DOUBLE DECIMAL (18,6)

DECIMAL DECIMAL

SMALLINT INTEGER

INTEGER INTEGER

BIGINT BIGINT

WVARCHAR VARCHAR

CHAR VARCHAR (4000)

WCHAR VARCHAR (4000)

BINARY BYTE

Data type mappings

Microsoft Azure Synapse Analytics data types

The following table shows the Microsoft Azure Synapse Analytics data warehouse data types that

are supported when using Qlik Compose and the default mapping to Qlik Compose data types.

For information on how to view the data type that is mapped to the data warehouse, see the section

for the data warehouse you are using.

Microsoft Azure Synapse Analytics data types Qlik Compose data types

DATE DATE

DATETIME DATETIME

DATETIME2 DATETIME

SMALLDATETIME DATETIME

TIME TIME

CHAR VARCHAR

NCHAR VARCHAR

REAL DECIMAL (18,6)

Data type mappings

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

139

5 Data Warehouse projects

Microsoft Azure Synapse Analytics data types Qlik Compose data types

FLOAT DECIMAL (38,12)

DECIMAL DECIMAL

MONEY DECIMAL (20,4)

SMALLMONEY DECIMAL (11,4)

BIT INTEGER

TINYINT INTEGER

SMALLINT INTEGER

INT INTEGER

BIGINT BIGINT

VARBINARY BYTE

BINARY BYTE

Google Cloud BigQuery data types

The following table shows the Google Cloud BigQuery data types that are supported when

ingesting data from Google Cloud BigQuery and the default mapping to Qlik Compose data types.

For information on how to view the data type that is mapped in the data warehouse, see the section

for the data warehouse database you are using.

Google Cloud BigQuery data types Qlik Compose data types

DATE Date

TIME Time

TIMESTAMP Datetime

INT64 BigInt

NUMERIC(p,s) Decimal(p,s)

FLOAT64 Decimal(38,12)

STRING Varchar

BOOLEAN Varchar(5) - True or False

Data type mappings

Defining landing zones

In a Compose project, you can define any number of landing zones. Defining multiple landing zones

is necessary if the data that you eventually want to be available in your data mart(s) is located in

several different landing zones.

Before you can define a landing zone in Qlik Compose, you first need to define a data warehouse.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

140

5 Data Warehouse projects

For more information on adding data warehouses, see

Setting up a data warehouse connection

(page 110)

To add a landing zone:

1. Open your project and click Manage in the Databases panel.

The Manage Databases window opens.

2. Click the New toolbar button.

The New Data Source window opens.

3. Provide the following information:

Field Description

Content Type Choose whether the content in the landing zone is Full Load, Change

Processing or Full Load and Change Processing (according to the Qlik

Replicate task definition).

See also

Defining reusable transformations (page 191)

The following topics describe the Expression Builder:

l

Opening the expression builder (page 185)

l

Expression builder overview (page 185)

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

184

5 Data Warehouse projects

l

Building expressions (page 186)

l

Testing expressions (page 188)

Opening the expression builder

The Expression Builder enables you to create a transformation without needing to type anything

manually.

The Expression Builder can be opened in several places, depending on your needs. For more

information about where to create a transformation, see the table in

Creating expressions (page

184)

Expression builder

Expression builder overview

The following section provides an overview of the Expression Builder functionality.

The Expression Builder consists of the following panels:

l

Tabs on the left of the Expression Builder: These tabs contains elements that you can add

to an expression. Select elements and add them to the Build Expression pane to create an

expression. For more information, see

Building expressions (page 186)

The following tabs are available:

l

Parameters - Only displayed when opening the Expression Builder from within the

Reusable Transformations > Edit Transformation window.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

185

5 Data Warehouse projects

For information on reusable transformations, see

Defining reusable transformations

(page 191)

below.

l

Input Columns/Input Attributes - Columns/attributes that can be used to build your

expression.

l

Transformations - Contains a list of reusable transformations. The tab is not

displayed if no reusable transformations have been defined.

For information on reusable transformations, see

Defining reusable transformations

(page 191)

below.

l

Operators - Operators that can be used to build your expression.

l

Functions - Functions that can be used to build your expression.

The Operators and Functions displayed in the Expression Builder use SQL

format. As SQL support and implementation is different for each data

warehouse (i.e. database) type and version, the data warehouse being

used in your Compose project will determine which Operators and

Functions will be available. For example, functions introduced with

Microsoft SQL Server 2017 will not work if the database being used for the

data warehouse is Microsoft SQL Server 2015.

Additionally, the list of Operators and Functions displayed in the Expression

Builder is not comprehensive. However, you can use any Operators and

Functions supported by the data warehouse, even if they are not included

in the list.

For an explanation of the available Operators and Functions, refer to the

Help for your data warehouse.

l

Build Expression Pane: The Build Expression pane is where you build your expression. You

can add elements, such as columns or operators to the panel as well as type all or part of the

expression. For more information, see

Building expressions (page 186)

l

Parse Expression Pane: This pane displays the parameters for the expression. After you

build the expression, click Parse Parameters to list the expression parameters. You can then

edit the parameters, enter a value for each of the parameters and associate attributes with

them. For more information, see

Parsing expressions (page 187)

l

Test Expression Pane: This panel displays the results of a test that you can run after you

provide values to each of the parameters in your expression. For more information, see

Testing expressions (page 188)

Building expressions

The first step in using the Expression Builder is to build an expression in the Build Expression pane.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

186

5 Data Warehouse projects

To add operators to your expression, you can use the Operator tab on the left or the

Operator buttons located above the Build Expression pane or any combination of

these.

To build an expression:

1. Hover the mouse cursor over the element that you want to add to your expression

(expressions usually start with an Input Column) and click the arrow that appears to its right.

2. Add Operators additional Input Columns and Functions as required.

To add operators to your expression, you can use the Operator tab on the left or the

Operator buttons located above the Build Expression pane or any combination of

these.

Example:

To create an expression that combines the FirstName name and LastName columns, do the following:

1. Add the FirstName Input Column to the Build Expression pane.

2. Assuming that Microsoft SQL Server is the data warehouse, in the Operator toolbar above

the Build Expression pane, click the concatenate (+) operator.

3. Then add a space between single quote characters and click the concatenate (+) operator

again.

4. Add the LastName Input Column to the Build Expression pane.

The expression would look like this:

Parsing expressions

When you add operators to the expression, the expression’s parameters are usually added

automatically to the Parse Expression pane. However, when you complete your expression or edit

it, you may need to parse the expression see all of the parameters.

To parse the expression parameters:

l

Click the Parse Expression button below the Build Expression pane.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

187

5 Data Warehouse projects

If the expression is not valid, a red error message will appear at the bottom of the Expression

Builder window.

If the expression is valid, the expression parameters and attributes (Input Columns) will be

displayed in the in the Parse Expression pane. See

Testing an expression (page 189)

Editing parameter names

By default, the parameter name is the same as the input column name. However, you can change

the parameter name as needed and then associate it with an input column. This is useful, for

instance, when you need to shorten attribute names. For example, EstimatedTimeOfArrival can be

abbreviated to ETA.

To edit a parameter and associate it with an input column:

1. In the Parse Expression pane, edit the parameter name as required.

2. From the Attribute drop-down list, select the desired input column.

Testing expressions

You test your expression to check that results are as expected. The following figure is an example

of an expression that has been evaluated and tested.

Certain expressions may fail during runtime, even though clicking Test Expression in the

Expression Builder indicated that they were valid.

This is because clicking Test Expression runs a query whereas during runtime, the

expression is run as a sub-query. This issue arises partly because the rules that govern

queries are slightly different from the rules that govern sub-queries.

For example, a semi-colon (;) is allowed in a query but not in a sub-query.

Testing an expression that contains an analytic function will validate the syntax without

actually executing the function. Additionally, the test will only be performed on a single

record.

Compose does not check the data types of columns used in an expression for

compatibility. For example, if a column of type integer is used in an expression for a

column of type varchar, the expression will not be executed successfully.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

188

5 Data Warehouse projects

Testing an expression

To test an expression:

1. In the Expression Builder window, build an expression as described in

Building expressions

(page 186)

2. Click Parse Expression as described in

Parsing expressions (page 187)

3. View the parameters that are displayed. If your expression is not valid, an error message is

displayed.

4. Optionally edit the parameters name(s) as described in

Editing parameter names (page 188)

5. Type values for each parameter and then click Test Expression to see the expression result.

For example, using the expression in

Testing an expression (page 189)

, type Mike for

FirstName and Smith for LastName. The result displayed is Mike Smith.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

189

5 Data Warehouse projects

6. This step is only available for transformations created in the Edit Mappings window. When

you create a transformation in the Edit Mappings window, an additional button called Show

Data appears to the left of the Test Expression button. You can click this button to see how

your expression translates into actual data.

For example, clicking the Show Data button for the expression UnitPrice*Quantity will open

the following window.

For more information on the Edit Mappings window, see

Editing column mappings (page

205)

Creating and managing the data warehouse (page 192)

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

190

5 Data Warehouse projects

Defining reusable transformations

In a single Compose project there may be several processes that require similar data

transformations. For example a reusable transformation can be defined that concatenates first and

last names. This transformation could then be used both in the Customers mapping and in the

Employees mapping.

As opposed to stored functions or procedures which are environment dependent, reusable

transformations are environment agnostic, meaning that not only can they be used as required

within a Compose project, but they can also be used across different environments (using

Compose’s export/import function).

Centrally managed transformations increase efficiency by eliminating unnecessary duplication,

while at the same time, enabling the seamless propagation of changes to all transformation

instances.

To define a reusable transformation:

1. From the drop-down menu in the top right of the Model panel, select Reusable

Transformations.

The Reusable Transformations window opens.

The window is split into the following panes:

l

Upper pane - Lists the reusable transformations that have been defined.

l

Lower pane - Provides additional information about transformation instances such as

where they are in use (e.g. mappings, model, etc.) and the expression that was

created using the transformation.

Select a transformation to see the additional information.

2. Click the New Transformation toolbar button.

The New Transformation window opens.

1. In the Name field, specify a name for the transformation.

2. In the Category field, specify a category name. If the category name already exists it

will be displayed below the field when you start to type the name. To group the new

transformation in the same category, simply select the existing name (unless of course

you wish to create a new category with a similar name).

In the Expression Builder, transformations are grouped according to their category

name, making it easier to find the transformation you want to use. Therefore, when

specifying a category name, it is recommended to choose a name that reflects the

purpose of the transformation. For example, if you create several transformations that

concatenate data, it would make sense to group those transformations under a

category called "Join".

3. To add a parameter to the transformation, click the New button to the right of the

Parameters heading.

A new row is added to the Parameters list.

4. Specify a name for the parameter, select an appropriate data type, and optionally

provide a description.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

191

5 Data Warehouse projects

If you add multiple parameters, you can change a parameter’s position by

selecting the parameter and then using the Up/Down arrows (above the

Parameters list) to reposition it.

5. Click the Create Expression button below the Parameters list.

The Edit Transformation window opens.

6. In the Edit Transformation window, create an expression using the parameters you

defined earlier.

For information on creating expressions, see

Creating expressions (page 184)

7. Click OK to save the transformation.

The transformation is added to the list in the upper pane.

Once a transformation has been defined, it will be available for selection as needed in the

Expression Builder’s Transformations tab.

For information on creating expressions, see

Creating expressions (page 184)

Managing reusable transformations

You can manage reusable transformation as described in the table below.

To Do This

Delete a

transformation

Select the transformation and then click the Delete toolbar button. When

prompted to confirm the action, click OK.

If the transformation is in use, you first need to delete the

transformation instances.

Edit a

transformation

Double-click the transformation or select the transformation and then click

the Edit toolbar button. Continue as described in

Defining reusable

transformations (page 191)

Any changes you make to a transformation will be propagated to

all instances of that transformation.

Edit a parameter Open the Edit Transformation window as described in

Defining reusable

transformations (page 191)

. Then, select the parameter you want to delete

and click the Delete button above the Parameters list.

Reusable transformation management options

5.7  Creating and managing the data warehouse

Once your model is set up properly, the next step in the Compose workflow is to create the data

warehouse tables, generate the task(s) and run the data warehouse task.

In this section:

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

192

5 Data Warehouse projects

l

Data warehouse tasks (page 193)

l

Managing tasks (page 203)

l

Viewing and exporting task statements (page 219)

l

Modifying task settings (page 220)

l

Validating the data warehouse (page 226)

l

Clearing the data warehouse metadata cache (page 227)



Data warehouse tasks

This section, describes how to create the data warehouse tables, generate the task and run a data

warehouse task. It contains he following topics:

l

How Compose handles missing references in the data warehouse (page 193)

l

Creating the data warehouse tables (page 194)

l

Generating data warehouse tasks (page 196)

l

Controlling data warehouse tasks (page 198)

How Compose handles missing references in the data warehouse

Before running a data warehouse task, it is important to understand how Compose handles missing

references. Missing references may be involve records that are simply missing or records whose

arrival has been delayed. The latter might occur if data is ingested from two different systems (for

example, an ERP system and a CRM system), with each system having its own task.

Handling an early-arriving fact:

If a record references another record which does not exist yet, then Compose will do the following:

l

Insert a placeholder for the missing reference record. The placeholder record will only

include the business key and surrogate key. The rest of the columns will be set to NULL.

The fact being processed can already include a valid reference to the surrogate

key of the reference record.

l

Document the missing record in the TLOG_REF_ERRORS_VALUES table. The TLOG_REF_ERRORS_

VALUES table contains the following columns:

l

RUNNO - The task run number.

l

RELATIONNR - An internal number that can be used by Qlik Support to determine the

source entity.

l

NO_RELATIONS - The number of missing references. For example, if Customer A ordered

three different items (from the Orders table) and Customer A is missing, this number

will be three.

l

KEYVALUE1-20 - The missing record. Since the missing record is a Primary Key, which

may consist of several columns, there are 20 KEYVALUE columns.

Example:

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

193

5 Data Warehouse projects

If the "Orders" table references "SuperGlue" in the "Products" table, but "SuperGlue" does not exist

in that table, Compose will mark "SuperGlue" as a missing reference, insert a record with the key

value "SuperGlue" (assuming that the product name is the business key) to the "Products" table,

and insert NULL values in the remaining "Products" table columns.

When the missing reference eventually arrives, it will be mapped to the record created for it and the

NULL values will be replaced by the actual values.

If the record is defined as history type 2, the record with the NULL values will remain as a

historical record.

See also:

Viewing missing references (page 267)

Creating the data warehouse tables

Compose create two types of data warehouse tables: staging tables (indicated by the TSTG prefix)

and the actual data warehouse tables (indicated by the TDWH prefix).

In addition, Compose automatically creates views for the TDWH tables in the following format:

<schema_name>.VDWH_<entity_name>[satellite_number_if_several]

Example:

dbo.VDWH_Customers02

For each entity, Compose creates a single view containing both the satellite data and the

associated hub data (or only the hub data if the entity has no satellites). If an entity has several

satellites, then Compose will create a view for each of the satellite tables. In such a case, the view

name will be suffixed with the user-defined satellite number as in the example above.

Compose for Data Warehouses adds RUNNO_INSERT and RUNNO_UPDATE columns to both the

Data Warehouse tables and the data mart tables. These columns contains the ETL task run number,

which can be used (in the Run Details window or in the Details tab) to find out more information

about the task (e.g. the number of rows updated or inserted per table). Note that in hub tables and

type 1 dimensions, the RUNNO_UPDATE number will usually be higher than the RUNNO_INSERT

number as these tables do not contain any history. In satellite tables or type 2 dimension tables

however, the RUNNO_INSERT number and the RUNNO_UPDATE number will always be the same as

a new row is inserted for each update (i.e. history is retained).

Data Warehouse views that contain both hub and satellite data will contain two RUNNO_

INSERT and two RUNNO_UPDATE columns. The hub table RUNNO columns are

appended with an "_H" (e.g. RUNNO_INSERT_H) while the satellite table table RUNNO

columns are appended with an "_S" e.g. RUNNO_UPDATE_S).

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

194

5 Data Warehouse projects

To create the data warehouse tables:

1. Click the Create button in the bottom right of the Data Warehouse panel. The Creating Data

Warehouse window opens.

A progress bar indicates the current progress. For each stage of the Data Warehouse

generation process, a corresponding message appears in the Messages list.

When creating table in a Microsoft SQL Server data warehouse, you may

encounter the following error:

Data warehouse creation failed. Error: Cannot create a row of size 11272

which is greater than the allowable maximum row size of 8060.

The statement has been terminated.

This is a well-documented Microsoft SQL Server limitation. To work around this

limitation you need to split the offending table(s) into smaller tables.

2. When the "Data warehouse created successfully" message appears, click Close.

Viewing the data warehouse tables

After the data warehouse tables are created, you can view them by clicking the number to the left

of the Data Warehouse Tables Present text in the Data Warehouse panel.

Setup and User Guide - Qlik Compose, November 2023 and Service

Release 1

195

5 Data Warehouse projects

When you click the link, the Data Warehouse Tables window opens showing a list of all the tables

in your data warehouse.