Qlik Compose Setup and User Guide



Qlik Compose

May 2022

Last updated: December 21, 2023

HELP.QLIK.COM

trademarks and/or registered trademarks of the respective owners with which they are associated.

Setup and User Guide - Qlik Compose, May 2022 3

1 What's new? 9

1.1 What's new in Data Warehouse projects? 9

Keeping changes in the Change Tables 9

Referenced dimensions 9

Data mart enhancements 10

Microsoft Azure Synapse Analytics Enhancements 10

Uniform source consolidation 11

Environment variables 11

Support for data profiling and data quality rules when using Google Cloud BigQuery 12

Attributes case sensitivity support 12

Associating a Replicate task that writes to a Hadoop target 12

Performance improvements 12

Support for Redshift Spectrum external tables 13

Data mart UX improvement 13

Support for updating custom ETLs using the CLI 13

Support for defining a custom data mart schema in Microsoft Azure Synapse Analytics 13

1.2 What's new in Data Lake projects? 13

Support for excluding deleted records from ODS views 13

Improved Historical Data Store resolution 14

Associating a Replicate task that writes to a Hortonworks Data Platform target 14

Databricks projects 14

1.3 New features common to both Data Warehouse projects and Data Lake projects 15

New Project title setting 15

Support for Microsoft Edge Browser 15

Windows Server 2022 (64-bit) support 15

Security Hardening 15

Managing user and group roles using the Compose CLI 16

2 Introduction 17

2.1 Data warehouse projects 17

Data warehouse projects architecture 17

Key features 18

2.2 Data lake projects 18

Easy data structuring and transformation 18

Continuous updates 18

Historical data store 18

Data lake project architecture 19

3 Qlik Compose installation and setup 20

3.1 Preparing your system for Compose 20

Hardware prerequisites 20

Software and network prerequisites 21

Required permissions for the Compose service 21

Reserved system names 21

3.2 Installing or upgrading Compose 21

Installation Instructions 21

Upgrade Instructions 22

3.3 Installing and upgrading Compose silently 22

Silently installing Compose 23

Contents

Setup and User Guide - Qlik Compose, May 2022 4

Silently upgrading Compose 24

Silently uninstalling Compose 24

3.4 Determining the required number of database connections 24

3.5 Accessing Qlik Compose 25

4 Security considerations 26

4.1 Setting up HTTPS for the Compose console 26

Checking if an SSL certificate is installed 26

Using the self-signed certificate 27

Replacing the self-signed certificate on Windows 29

4.2 Setting the hostname and changing the HTTPS port 30

To set the hostname: 30

To change the HTTPS port: 30

4.3 Setting up HSTS on Compose 31

Enabling HSTS 31

Disabling HSTS 31

4.4 Setting Single Sign-On Authentication with Kerberos 32

4.5 Changing the master user password 32

5 Data Warehouse projects 35

5.1 Defining a Qlik Replicate task 36

Prerequisites 36

Limitations and considerations 36

Setting up the task 37

5.2 Adding and managing data warehouse projects 37

Adding data warehouse projects 38

Managing and monitoring projects 39

Project settings 40

Resetting projects 46

Project deployment 47

Migrating objects as CSV files 49

Exporting and importing projects using the CLI 78

Working with environment variables 88

Generating projects using the CLI 98

Exporting project documentation 99

Viewing and downloading DDL scripts 100

Project versioning 101

Creating a diagnostics package 103

5.3 Getting started with Data Warehouse projects 104

High-level flow 104

Console elements 104

Data warehouse project tutorial 107

5.4 Setting up a data warehouse connection 111

Using Microsoft SQL Server as a data warehouse 112

Using Oracle as a data warehouse 115

Using Snowflake as a data warehouse 118

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

Contents

Setup and User Guide - Qlik Compose, May 2022 5

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 162

Validating the model 163

Displaying the model 163

Managing the model 166

Creating expressions 181

Opening the expression builder 182

Defining reusable transformations 188

5.7 Creating and managing the data warehouse 190

Data warehouse tasks 191

Managing tasks 201

Viewing and exporting task statements 216

Modifying task settings 217

Validating the data warehouse 222

Clearing the data warehouse metadata cache 224

5.8 Creating and managing data marts 225

Adding data marts and star schemas 225

Displaying data in a pivot table 231

Managing data marts 234

Example of a Valid Table Creation Modifier 241

Example of a Valid Table Creation Modifier 246

Creating and managing custom ETLs 250

Viewing and exporting task statements 252

Validating and adjusting the data mart 252

Reloading the data mart 254

Modifying data mart settings 255

The "Obsolete" indicator 257

5.9 Creating and managing command tasks 258

Defining command tasks 258

Managing command tasks 259

Controlling and monitoring command tasks 259

5.10 Controlling and monitoring tasks and workflows 260

Viewing information in the monitor 260

Viewing missing references 262

Controlling tasks 264

Notifications 266

Workflows 268

Monitoring and controlling Qlik Replicate tasks 272

Contents

Setup and User Guide - Qlik Compose, May 2022 6

6 Data Lake projects 275

6.1 Defining a Qlik Replicate task 275

Prerequisites 275

Limitations and Considerations 276

Setting up the task 276

6.2 Adding and managing Data Lake projects 277

Prerequisites 277

Data Lake project guidelines 279

Adding data lake projects 280

Managing and monitoring projects 282

Project settings 283

Resetting projects 288

Project deployment 289

Exporting and importing projects using the CLI 290

Generating projects using the CLI 298

Viewing and downloading DDL scripts 299

Project versioning 300

Creating a diagnostics package 301

6.3 Getting started with Data Lake projects 302

High-level flow 302

Console elements 302

6.4 Setting up landing and storage connections 305

Defining a Storage Zone 305

Defining Landing Zones 314

Managing Landing and Storage connections 316

6.5 Selecting source tables and managing metadata 316

Reserved column names 316

Selecting and adding the source tables 317

Validating the metadata and storage 320

Managing the metadata 322

Schema evolution 327

Creating transformations 329

Reusable transformations 335

6.6 Creating and Managing Storage Zone Tasks 337

Defining and running data storage tasks 338

Managing task definitions 341

Clearing the metadata cache 350

Viewing and exporting task statements 351

Modifying task settings 352

6.7 Creating and managing command tasks 352

Defining Command tasks 353

Managing Command tasks 354

Controlling and monitoring Command tasks 354

6.8 Controlling and monitoring tasks and workflows 354

Viewing information in the monitor 355

Running and controlling tasks 356

Notifications 358

Contents

Setup and User Guide - Qlik Compose, May 2022 7

Workflows 360

Monitoring and controlling Replicate tasks 364

7 Managing Compose 366

7.1 License settings 366

License enforcement 366

Registering a license 366

7.2 Viewing a license 367

7.3 Logging settings 367

Setting the logging level 367

Setting automatic roll over and cleanup 368

Viewing and downloading Compose log files 369

7.4 Mail server settings 369

7.5 Running tasks on a remote Compose server 370

7.6 Replicate Server settings 370

7.7 User permissions 371

Default user permissions according to role 372

Granular access control 373

Managing user and group roles using the Compose CLI 375

Managing user permissions 377

7.8 Audit trails 380

Audit trail information 380

Exporting Audit Trail files 381

Configuring Audit Trail size and retention 382

Decoding an encoded payload 382

8 Setting up Compose on a Windows HA cluster 383

8.1 Step 1: Installing Compose in the cluster 383

Preparation 383

Primary node setup 383

Secondary node setup 384

8.2 Step 2: Adding the Compose service 385

8.3 Step 3: Defining the service dependencies 385

8.4 Step 4: Defining the URLfor the cluster 386

8.5 Upgrading Compose on the cluster 386

A Impact of DST change on Qlik Compose 388

B Support matrix 389

B.1 Supported Windows platforms 389

B.2 Supported browsers 389

B.3 Supported Qlik Replicate and Enterprise Manager versions 389

B.4 Supported Databases for Data Warehouse Projects 390

Supported data sources 390

Supported data warehouses 390

B.5 Supported hive distributions for Data Lake projects 392

C Cron format and examples 393

C.1 Cron format 393

C.2 Special characters 393

Contents

Setup and User Guide - Qlik Compose, May 2022 8

C.3 Usage examples 394

D Supported characters 396

E Glossary 397

Contents

1 What's new?

1  What's new?

The following section describes the enhancements and new features introduced in Qlik Compose May 2022.

The "What's new?" is cumulative, meaning that it also describes features that were already released

as part of Compose August 2021 service/patch releases. This is because customers upgrading from

initial release versions might not be aware of features that were released in interim service releases.

1.1  What's new in Data Warehouse projects?

The following section describes the enhancements and new features introduced in Qlik Compose Data

Warehouse projects.

Keeping changes in the Change Tables

This version introduces a new Keep in Change Tables option in the landing zone connection settings:

When you select the Keep in Change Tables option, the changes are kept in the Change Tables after they are

applied (instead of being deleted or archived). This is useful as it allows you to:

l

Use the changes in multiple Compose projects that share the same landing

l

Leverage Change Table data across multiple mappings and/or tasks in the same project

l

Preserve the Replicate data for auditing purposes or reprocessing in case of error

l

Reduce cloud data warehouse costs by eliminating the need to delete changes after every ETL

execution

Referenced dimensions

This version introduces support for referencing dimensions. To facilitate this new functionality, a new

Reference selected dimensions option has been added to the Import Dimensions dialog which, together

with the toolbar button, has been renamed to Import and Reference Dimensions.

Setup and User Guide - Qlik Compose, May 2022 9

1 What's new?

The ability to reference dimensions improves data mart design efficiency and execution flexibility by

facilitating the reuse of data sets. Reuse of dimension tables across data marts allows you to break up fact

tables into smaller units of work for both design and data loading, while ensuring consistency of data for

analytics.

Data mart enhancements

Data mart adjust

This version introduces the following enhancements:

l

The automatic data mart adjust feature has been extended to include DROP COLUMN and

ADDCOLUMN support.

l

In previous versions, adding a dimension which did not relate to any fact would require the data mart

to b e dropped and recreated. From this version, such dimensions can be adding using auto-adjust,

including Date and Time dimensions.

l

The generate_project CLI now supports automatic data mart adjust for specific objects. In

previous versions, Compose would adjust the data marts by dropping and recreating the tables,

regardless of the required change. This would sometimes take a lot of time to complete. From this

version, only the changes will be adjusted. For example, if a new column was added to a dimension,

only that specific column will be added to the data mart tables. To support this new functionality the --

stopIfDatamartsNeedRecreation parameter must be included in the command. I this parameter is

omitted and the data mart needs to be adjusted, Compose will drop and recreate the data mart tables

like it did in previous versions.

Data mart reloading

This version introduces the ability to reload the data mart or parts of the data mart without dropping and

recreating it, thereby eliminating costly and lengthy reloading of the data mart while maximizing data

availability. Such operations should usually be performed after a column with history has been added by the

automatic adjust operation.

To facilitate this, a new mark_reload_datamart_on_next_run CLI has been developed. The new CLI

allows users to mark dimensions and facts to be reloaded on the next data mart run. These can either be

specific dimensions and facts or multiple dimensions and facts (either from the same data mart or different

data marts) using a CSV file.

Microsoft Azure Synapse Analytics Enhancements

A number of changes related to statistics have been implemented. In addition, several statements are now

tagged with an identifier label for troubleshooting 'problem queries' and identifying possible ways to optimize

database settings. Moreover, 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

Setup and User Guide - Qlik Compose, May 2022 10

1 What's new?

Table type Tag

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

Uniform source consolidation

Uniform source consolidation as its name suggests allows you to ingest data from multiple sources into a

single, consolidated, entity.

To enable uniform source consolidation configuration, a new Consolidation tab has been added to the data

warehouse task settings.

When the Consolidate uniform sources option is enabled, Compose will read from the selected data sources

and write the data to one consolidated entity. This is especially useful if your source data is managed across

several databases with the same structure, as instead of having to define multiple data warehouse tasks (one

for each source), you only need to define a single task that consolidates the data from the selected data

sources.

Consolidation tab showing selected data sources

Environment variables

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

Setup and User Guide - Qlik Compose, May 2022 11

1 What's new?

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.

Excluding environment variables from export operations

An option has been added to replace environment-specific settings with the defaults when exporting projects

(CLI) or creating deployment packages.

To facilitate this functionality, the --without_environment_specifics parameter was added to the

export_project_repository CLI and a Exclude environment variable values option was added to the

Create Deployment Package dialog.

Support for data profiling and data quality rules when using Google

Cloud BigQuery

You can now configure data profiling and data quality rules when using Google Cloud BigQuery as a data

warehouse.

Attributes case sensitivity support

In previous versions, attempting to create several Attributes with the same name but a different case would

result in a duplication error. Now, such attributes will now be created with an integer suffix that increases

incrementally for each attribute added with the same name. For example: Sales, SALES_01, and Sales_02.

Associating a Replicate task that writes to a Hadoop target

You can now associate a Replicate task that writes to a Hadoop target with the Compose landing.

Performance improvements

This version provides the following performance improvements:

l

Validating a model with self-referencing entities is now significantly faster than in previous versions.

For instance, it now takes less than a minute (instead of up to two hours) to validate a model with 5500

entities.

l

The time it takes to "Adjust" the data warehouse has been significantly reduced. For instance, it now

takes less than three minutes (instead of up to two hours) to adjust a data warehouse with 5500

entities.

l

Optimized queries, resulting in significantly improved data warehouse loading and CDC performance.

l

Significantly improved the loading speed of data mart Type 2 dimensions with more than two entities.

In order to benefit from this improvement, customers upgrading with existing data marts needs to

regenerate their data mart ETLs.

l

Improved performance of data warehouse loading, by reducing statements executed when there is no

data to process. This change impacts cloud data warehouses such as Snowflake, Amazon Redshift,

Setup and User Guide - Qlik Compose, May 2022 12

1 What's new?

Google BigQuery, and so on.

Relevant from Compose May 2022 SR1 only.



Support for Redshift Spectrum external tables

Supported from Compose May 2022 SR1 only.



Customers who want to leverage this support need to create Redshift Spectrum external tables and discover

them. Additionally, when running a CDC task, the new Keep in Change Tables option described above needs

to be turned on.

Data mart UX improvement

The Data Mart Dimensions tree and the Star Schema Fact tab were redesigned to provide a better user

experience.

Support for updating custom ETLs using the CLI

This version introduces support for updating custom ETLs using the Compose CLI. This functionality can be

incorporated into a script to easily update Custom ETLs.

Supported from Compose May 2022 SR2 only.



Support for defining a custom data mart schema in Microsoft Azure

Synapse Analytics

Customers working with Microsoft Azure Synapse Analytics can now utilize the Create tables in schema

option (in the data mart settings) to define a custom schema for the data mart tables.

Supported from Compose May 2022 SR2 only.



1.2  What's new in Data Lake projects?

The following section describes the enhancements and new features introduced in Qlik Compose Data Lake

projects.

Support for excluding deleted records from ODS views

A Deleted records in ODS views section has been added to the General tab of the project settings, with the

following options:

l

Exclude the corresponding record from the ODS views - This is the default option as records

marked as deleted should not usually be included in ODS views.

Setup and User Guide - Qlik Compose, May 2022 13

1 What's new?

l

Include the corresponding record in the ODS views - Although not common, in some cases, you

might want include records marked as deleted in the ODS views in order to analyze the number of

deleted records and investigate the reason for their deletion. Also, regulatory compliance might

require you to be able to retrieve the past record status (which requires change history as well).

As this was the default behavior in previous versions, you might need to select this option to

maintain backward compatibility.

Improved Historical Data Store resolution

Supported from Compose May 2022 SR1 only.



In previous versions, HDS resolution was one second. This was problematic at times as multiple changes to a

Primary Key within a second resulted in only the last change appearing in the HDS. To view all the history,

customers were forced to review the landing.

From this version, all changes (history) will shown in the HDS, facilitating better support for auditing.

Associating a Replicate task that writes to a Hortonworks Data Platform

target

You can now associate a Replicate task that writes to a Hortwonworks Data Platform target with the Compose

landing connection (in a Cloudera Data Platform (CDP) Compose project).

Databricks projects

New Databricks versions

l

Databricks 9.1 LTS is now supported on all cloud providers (AWS, Azure, and Google Cloud Platform).

l

Databricks 10.4 LTS is now supported on all cloud providers (AWS, Azure, and Google Cloud Platform).

Databricks 10.4 LTS is supported from Compose May 2022 SR1 only.

SQL Warehouse compute and Parquet support

Supported from Compose May 2022 SR1 only.



Compose May 2022 SR1 introduces support for SQL Warehouse compute. To benefit from this support,

customers need to use the new Replicate Databricks (Cloud Storage) target endpoint, which is available from

Replicate November 2022. SQL Warehouse compute offers a lower cost alternative to clusters while also

allowing Parquet file format to be used in the Landing Zone.

Setup and User Guide - Qlik Compose, May 2022 14

1 What's new?

Support for Unity Catalog

This version introduces support for Databricks Unity Catalog. Customers working with Unity Catalog can now

specify a catalog name both in the Landing connection settings and in the Storage connection settings.

1.3  New features common to both Data Warehouse projects

and Data Lake projects

New Project title setting

A new Project title setting had been added to the Environment tab of the project settings. 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. Unlike the Environment title and Environment

type, which are unique for each environment, the project title is environment independent. This means that

the project title will always be retained, even when deploying to a different environment.

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

Support for Microsoft Edge Browser

This version introduces support for accessing the Compose console using Microsoft Edge.

Windows Server 2022 (64-bit) support

Windows Server 2022 support is available from Compose May 2022 SR1.

Security Hardening

For security reasons, command tasks are now blocked by default. To be able to run command tasks, a

Compose administrator needs to turn on this capability using the Compose CLI. For more information, see the

Compose online help.

This functionality only applies to command tasks created after a clean installation. If you upgrade to

this version, command tasks will continue to work as previously.

Setup and User Guide - Qlik Compose, May 2022 15

1 What's new?

Managing user and group roles using the Compose CLI

This feature is available from Compose May 2022 SR1 only.

You can set and update user and group roles using the Compose CLI. You can also remove users and groups

from a role in one of the available scopes (for example, Admin in All Projects). This is especially useful if you

need to automate project deployment.

Setup and User Guide - Qlik Compose, May 2022 16

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, May 2022 17

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 and other

time-oriented analytic data marts.

l

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

Setup and User Guide - Qlik Compose, May 2022 18

2 Introduction

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, May 2022 19

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 20)

l

Installing or upgrading Compose (page 21)

l

Installing and upgrading Compose silently (page 22)

l

Determining the required number of database connections (page 24)

l

Accessing Qlik Compose (page 25)

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

Memory

Additional memory may improve performance when

several ETL processes are running concurrently.

8 GB 16 GB 32 GB

Hardware component requirements

Setup and User Guide - Qlik Compose, May 2022 20

3 Qlik Compose installation and setup

Component

Basic

System

Large

System

Extra-Large

System

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 389).

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.

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.

Setup and User Guide - Qlik Compose, May 2022 21

3 Qlik Compose installation and setup

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 25).

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.

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 20).

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

Setup and User Guide - Qlik Compose, May 2022 22

3 Qlik Compose installation and setup

l

Silently installing Compose (page 23)

l

Silently upgrading Compose (page 24)

l

Silently uninstalling Compose (page 24)

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>]

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

Setup and User Guide - Qlik Compose, May 2022 23

3 Qlik Compose installation and setup

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 23)

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 23)



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.

Setup and User Guide - Qlik Compose, May 2022 24

3 Qlik Compose installation and setup

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 20).

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 377).

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 <ComputerName> 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 26).

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

Setup and User Guide - Qlik Compose, May 2022 25

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 26)

l

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

l

Setting up HSTS on Compose (page 31)

l

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

l

Changing the master user password (page 32)

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, May 2022 26

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, May 2022 27

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, May 2022 28

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 26).

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.

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

Setup and User Guide - Qlik Compose, May 2022 29

4 Security considerations

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

Where:

Setup and User Guide - Qlik Compose, May 2022 30

4 Security considerations

--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, May 2022 31

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

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.

Setup and User Guide - Qlik Compose, May 2022 32

4 Security considerations

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, May 2022 33

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, May 2022 34

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 36)

l

Adding and managing data warehouse projects (page 37)

l

Getting started with Data Warehouse projects (page 104)

l

Setting up a data warehouse connection (page 111)

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 190)

l

Creating and managing data marts (page 225)

l

Creating and managing command tasks (page 258)

l

Controlling and monitoring tasks and workflows (page 260)

Setup and User Guide - Qlik Compose, May 2022 35

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 390).

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

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.

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

Setup and User Guide - Qlik Compose, May 2022 36

5 Data Warehouse projects

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 208).

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 107) below and inAdding and

managing data warehouse projects (page 37).

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

5.2  Adding and managing data warehouse projects

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

In this section:

Setup and User Guide - Qlik Compose, May 2022 37

5 Data Warehouse projects

l

Adding data warehouse projects (page 38)

l

Managing and monitoring projects (page 282)

l

Project settings (page 40)

l

Resetting projects (page 46)

l

Project deployment (page 47)

l

Migrating objects as CSV files (page 49)

l

Exporting and importing projects using the CLI (page 78)

l

Generating projects using the CLI (page 98)

l

Exporting project documentation (page 99)

l

Viewing and downloading DDL scripts (page 100)

l

Project versioning (page 300)

l

Creating a diagnostics package (page 301)

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 280).

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

Compose license.

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 43).

Setup and User Guide - Qlik Compose, May 2022 38

5 Data Warehouse projects

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 111) 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 190).

8. Set up the data mart as described in Creating and managing data marts (page 225).

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.

Project management procedures

Setup and User Guide - Qlik Compose, May 2022 39

5 Data Warehouse projects

To Do this

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 47) (Data Warehouse projects) and Project

deployment (page 289) (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 After applying changes below.

Designated

Select whether the landing zone is a Database or a Schema. This should reflect the

target endpoint settings in the Qlik Replicate task.

When Oracle is the Data Warehouse, this field is read-only since the Oracle landing

zone is always designated by Schema.

For more information, see Defining a Qlik Replicate task (page 36).

Database

Name

This field is not applicable when Oracle is the Data Warehouse.

If the landing zone is designated by a Database, specify the database name. This

must be the same as the target database defined in the Qlik Replicate task.

When Microsoft Azure Synapse Analytics is the data warehouse, the landing zone

database must be the same as the database defined for the data warehouse,

although it should use a different schema.

For more information, see Defining a Qlik Replicate task (page 36).

Schema

Name

If a schema name was specified in the Qlik Replicate task settings, specify the same

schema name here.

When Oracle is the Data Warehouse, this must be the same as the schema defined

in the Oracle target connection string in the Qlik Replicate task.

For more information, see Defining a Qlik Replicate task (page 36).

Error Mart

Schema

Name

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.

For more information on error marts, see Defining and managing data quality rules

(page 211).

Data source fields

Setup and User Guide - Qlik Compose, May 2022 141

5 Data Warehouse projects

Field Description

After

applying

changes

Replicate creates Change Tables in the landing zone in which subsequent changes

to the original Full Load data are stored. If you selected Change Processing or Full

Load and Change Processing as the Content Type, you can determine what to do

with the Change Tables after the changes have been applied to the data warehouse

tables:

Choose one of the following:

l

Delete from Change Tables - Deletes the changes from the Change Tables

l

Keep in Change Tables - Keeps the changes in the Change Tables. This is

useful if you do not want all of the changes to be applied at the same time.

For more information, see Working with the Keep in Change Tables option

(page 143).

l

Archive the Change Tables - If you select Archive the Change Tables, you

also need to specify a Database name and Schema name in the relevant

fields.

Archived Change Tables do not contain a record of DDL changes. If DDL

changes were applied, you will need to update the archived tables

manually.

Discover the

VARIANT data

type as

(applies to

Snowflake

only)

As Compose does not support mapping directly to the Snowflake VARIANT data

type, you need to choose whether VARIANT columns will be created as JSON (the

default) or XML in the Snowflake database.

Setup and User Guide - Qlik Compose, May 2022 142

5 Data Warehouse projects

Field Description

Associate

with

Replicate

Task

Select this to associate your Compose project with the related Replicate task.

Replicate tasks replicate the relevant tables from the source database to the

landing zone in your data warehouse. Specifying the Replicate task name will

enable you to both discover the source tables' primary keys, and monitor and

control that task from within Compose.



However, before you can specify a Replicate task name, you first need to define the

connection settings to at least one Replicate Server machine. To do this, click the

Replicate Server Settings link below the Associate with Replicate task field and

then configure the settings as described in Replicate Server settings (page 370).

Once you have configured connectivity to at least one Replicate Server, you can

then proceed to select a Replicate task.

To select a Replicate task:

1. Click the browse button to the right of the Associate with Replicate task

field.

The Select Replicate Task window opens.

2. Select a Replicate Server from the Server drop-down list.

The Replicate Tasks list is populated with all tasks defined on the selected

server.

3. Select the task that is replicating the source tables to the landing zone and

then click OK.

The name of the selected task is shown as read-only in the Associate with

Replicate task field.

4. If you want to generate the model by discovering the source database in the Replicate task, leave the

New Data Source window open for now as you will need to define connectivity to the source database

in the Replicate task.

For instructions on how to do this, see Defining Replicate data source connections (page 147).

Otherwise, click OK to save your settings.

Working with the Keep in Change Tables option

When you select the Keep in Change Tables option described earlier, the changes are kept in the Change

Tables after they are applied (instead of being deleted or archived). This is useful as it allows you to:

l

Use the changes in multiple Compose projects that share the same landing

l

Leverage Change Table data across multiple mappings and/or tasks in the same project

l

Preserve the Replicate data for auditing purposes or reprocessing in case of error

l

Reduce cloud data warehouse costs by eliminating the need to delete changes after every ETL

execution

To facilitate this functionality, Compose keeps a "watermark" per table as a way of tracking which data has

been consumed and which data is yet to be consumed. The watermarks can be reset if needed, as described

in Deleting changes and resetting watermarks (page 146) below.

Setup and User Guide - Qlik Compose, May 2022 143

5 Data Warehouse projects

Use case

I have a table named Inventory in my landing that I would like to load into two separate tables in my data

warehouse for the purpose of tracking and analyzing changes. The tracking table needs to be updated every

15 minutes while the analysis table needs to be updated once a day.

To accomplish this, I do the following:

1. Set up a connection to my landing zone making sure to select the Keep in Change Tables option.

2. Discover the source tables from the landing zone as described in Discovering the Source Database or

Landing Zone (page 156).

3. Duplicate the Inventory table in my model so that I have two tables, and then rename the tables as

follows: Inventory_Frequent (for tracking) and Inventory_Snapshot (for analytics).

For instructions on how to duplicate entities, see Managing entities (page 167)

4. Validate the model as described in Validating the model (page 163).

5. Create the data warehouse tables as described in Creating the data warehouse tables (page 192).

6. Duplicate the Full Load and CDC tasks so that I have one set of tasks that populate and update the

Inventory_Frequent table, and another set of tasks that populate and update the Inventory_

Snapshot table.

Make sure when duplicating the tasks to select Full Load Only as the task type for Full Load

tasks and Change Tables Only as the task type for CDC tasks. See also Adding and

duplicating tasks (page 201).

7. Verify the correct mappings are selected and delete any redundant mappings that were created when

the tasks were duplicated.

Setup and User Guide - Qlik Compose, May 2022 144

5 Data Warehouse projects

For the source_Frequent and source_Frequent_CDC tasks, the Map_Inventory_Snapshot

mapping should not be selected. Conversely, for the source_Snapshot and source_

Snapshot_CDC tasks, the Map_Inventory_Frequent mapping should not be selected.

8. Generate and run the source_Frequent and source_Snapshot Full Load tasks.

9. Generate the source_Frequent_cdc and source_Snapshot_cdc tasks.

10. Schedule the source_Frequent_cdc task to run every 15 minutes and schedule the source_Snapshot_

cdc task to run at 20:00 every day.

Setup and User Guide - Qlik Compose, May 2022 145

5 Data Warehouse projects

Deleting changes and resetting watermarks

The following CLIoptions are available for managing watermarks.

Deleting changes from the Change Tables

You can delete the changes from the Change Tables if they are no longer required. Although this is not

required, you might want to incorporate this into your database maintenance plan.

Command syntax

ComposeCli.exe generate_watermark_scripts --project

project_name

Where:

--project is the name of the project.

Example

ComposeCli.exe generate_watermark_script --project MyProject

Resetting the watermark

Resetting the watermark might be required if you need to reapply changes from an earlier time period, for

example.

After resetting the watermark, on the next CDC run all of the Change Table records will be processed

again.

Command syntax

ComposeCli.exe reset_watermark --project

project_name

--landing

landing_name

[--table

table_

name

]

Parameters

Setup and User Guide - Qlik Compose, May 2022 146

5 Data Warehouse projects

Parameter Description

--project The name of the project.

--landing The name of the landing in Compose containing the Change Tables whose

watermarks you want to reset.

--table The logical name (i.e. without the_ct suffix) of a specific Change Table

whose watermark you want to reset. When omitted, watermarks for all

Change Tables will be reset.

Example

ComposeCli.exe reset_watermark --project MyProject --landing northwind_Landing

Limitations and considerations

Switching from Keep in Change Tables to Delete from Change Tables/Archive the Change Tables or vice

versa, requires you to regenerate the affected tasks. If you switch from Keep in Change Tables to Delete

from Change Tables/Archive the Change Tables, Compose need to re-read the changes and delete/archive

the older changes. In such a case, running the CDC tasks might take longer than usual, depending on the

amount of changes.

Defining Replicate data source connections

You can also generate the model by discovering the source database in the Replicate task. In this case, you

will also need to define connectivity to that database.

To define connectivity settings:

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. In the New Data Source window, select the Source database connection option.

4. Continue from one of the following topics as appropriate:

l

Using Oracle as a source (page 147)

l

Using Microsoft SQL Server as a source (page 149)

l

Using MySQL as a source (page 151)

l

Using IBM DB2 for LUW as a source (page 153)

Using Oracle as a source

This section describes how to set up connectivity to the Oracle database defined for the Replicate task. This is

required if you want to discover the tables and/or views from the source database as opposed to the landing

zone. For a list of the pros and cons of each method, see Discovering the Source Database or Landing Zone

(page 156).

It contains the following topics:

l

Prerequisites (page 148)

l

Oracle data types (page 133)

Setup and User Guide - Qlik Compose, May 2022 147

5 Data Warehouse projects

l

Defining the connection parameters (page 148)

Prerequisites

Before you can use Oracle as a source in a Qlik Compose project, make sure that the following prerequisites

have been met:

l

The Oracle database should be configured with the required Permissions (page 132) 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.

The Qlik Compose service needs to be restarted after installing the required components.

l

Install Oracle Instant Client 11.2.0.3.0 or later (Windows x64) 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\12.1.0\client_1

Defining the connection parameters

You can add an Oracle database to Qlik Compose to use as a source.

Setup and User Guide - Qlik Compose, May 2022 148

5 Data Warehouse projects

To add an Oracle source database to Qlik Compose:

1. In the New Data Source window, enter the information as described in the table below.

Field Description

Type Select Oracle.

Server

Name

Specify the name or IP address of the Oracle server machine.

Specify the TNS name.

Port If you specified a TNS name in the Server Name field, make sure that this field is

empty. Optionally, change the default port.

User Name Specify your user name for accessing the Oracle database.

The specified user must have read/write privileges on the Oracle database.

Password Specify your password for accessing the Oracle database.

SID If you specified a TNS name in the Server Name field, make sure that this field is

empty. Otherwise, specify the Oracle SID.

Schema Specify the schema containing the source tables.

Data source fields

2. Click Test Connection to verify that Compose is able to establish a connection with the specified

database.

3. Click OK to save your settings.

The database is added to the list on the left side of the Manage Databases window.



Using Microsoft SQL Server as a source

This section describes how to set up connectivity to the Microsoft SQL Server database defined as the source

endpoint for the Replicate task. This is required if you want to discover the tables and/or views from the

source database as opposed to the landing zone. For a list of the pros and cons of each method, see

Discovering the Source Database or Landing Zone (page 156).

It contains the following topics:

l

Prerequisites (page 149)

l

Working with Windows authentication (page 150)

l

Microsoft SQL Server data types (page 134)

l

Defining the connection parameters (page 152)

Prerequisites

Before you can use Microsoft SQL Server as a source in a Qlik Compose project, make sure that the following

prerequisites have been met:

Setup and User Guide - Qlik Compose, May 2022 149

5 Data Warehouse projects

l

Microsoft SQL Server should be configured with the required Permissions (page 132) and accessible

from the Compose machine.

l

Microsoft SQL Server Native Client must be installed on the Qlik Compose machine.

l

Qlik Compose supports the following Microsoft SQL Server editions.

l

Enterprise Edition

l

Standard Edition

l

Workgroup Edition

l

Developer Edition

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.

Defining the connection parameters

You can add a Microsoft SQL Server database to Qlik Compose to use as a source. You can also use the

Microsoft SQL Server source to specify connection details to a Microsoft Azure SQL Database.

When using Microsoft Azure SQL Database 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.

Setup and User Guide - Qlik Compose, May 2022 150

5 Data Warehouse projects

To add a Microsoft SQL Server source database to Qlik Compose:

1. In the New Data Source window, enter the information as described in the table below.

Field Description

Type Select Microsoft SQL Server.

Server Name Specify the name or IP address of the Microsoft SQL Server machine.

Port Optionally, change the default port.

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.

User Name Specify your user name for accessing the Microsoft SQL Server database.

The specified user must have read/write privileges on the Microsoft SQL Server

database.

Password Specify your password for accessing the Microsoft SQL Server database.

Database Name Specify the name of the Microsoft SQL Server database.

Schema Specify the schema containing the source tables.

Data source fields

2. Click Test Connection to verify that Compose is able to establish a connection with the specified

database and/or landing zone.

3. 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 MySQL as a source

This section describes how to set up connectivity to the MySQL database defined as the source endpoint for

the Replicate task. This is required if you want to discover the tables and/or views from the source database

as opposed to the landing zone. For a list of the pros and cons of each method, see Discovering the Source

Database or Landing Zone (page 156).

It contains the following topics:

Setup and User Guide - Qlik Compose, May 2022 151

5 Data Warehouse projects

l

Prerequisites (page 152)

l

MySQL data types (page 135)

l

Defining the connection parameters (page 152)

Prerequisites

Before you can use MySQL as a source in a Qlik Compose project, make sure that the following prerequisites

have been met:

l

The MySQL database should be configured with the required Permissions (page 132) and accessible

from the Compose machine.

The following MySQL editions are supported:

l

MySQL Community Edition

l

MySQL Standard Edition

l

MySQL Enterprise Edition

l

MySQL Cluster Carrier Grade Edition

l

MySQL ODBC 64-bit client must be installed on the same computer as Qlik Compose.

Cluster prerequisites

To be able to discover clustered (NDB) tables, the following parameters must be configured in the MySQL

my.ini (Windows) file.

Parameter Value

ndb_log_bin Must be:

ndb_log_bin=on

This ensures that changes in clustered tables will be logged to the binary log.

ndb_log_update_as_

write

Must be:

ndb_log_update_as_write=OFF

This prevents writing UPDATEs as INSERTs in the binary log.

ndb_log_updated_only Must be:

ndb_log_updated_only=OFF

Ensures that the binary log will contain the entire row and not just the changed

columns.

Cluster parameters

Defining the connection parameters

You can add a MySQL database to Qlik Compose to use as a source.

Setup and User Guide - Qlik Compose, May 2022 152

5 Data Warehouse projects

To add a MySQL source database to Qlik Compose:

1. In the New Data Source window, enter the information as described in the table below.

Field Description

Type Select MySQL.

Server Name Specify the name or IP address of the MySQL server machine.

Port Optionally, change the default port.

User Name Specify your username for accessing the MySQL database.

The specified user must have read/write privileges on the MySQL database.

Password Specify your password for accessing the MySQL database.

Database Name Specify the name of the MySQL database.

Schema Specify the schema containing the source tables.

Data source fields

2. Click Test Connection to verify that Compose is able to establish a connection with the specified

database and/or landing zone.

3. Click OK to save your settings.

The database is added to the list on the left side of the Manage Databases window.

Using IBM DB2 for LUW as a source

This section describes how to set up connectivity to the IBM DB2 for LUW database defined as the source

endpoint for the Replicate task. This is required if you want to discover the tables and/or views from the

source database as opposed to the landing zone. For a list of the pros and cons of each method, see

Discovering the Source Database or Landing Zone (page 156).

It contains the following topics:

l

Prerequisites (page 153)

l

IBM DB2 for LUW data types (page 138)

l

Defining the connection parameters (page 153)

Prerequisites

Before you begin to work with an IBM DB2 for LUW database as a source in Qlik Compose, make sure the

following prerequisites have been met:

l

The IBM DB2 for LUW database should be configured with the required Permissions (page 132) and

accessible from the Compose machine.

l

The IBM Data Server Driver for ODBC and CLI version 10.5 must be installed on the Qlik Compose

machine.

Defining the connection parameters

You can add an IBM DB2 for LUW database to Qlik Compose to use as a source.

Setup and User Guide - Qlik Compose, May 2022 153

5 Data Warehouse projects

To add an IBM DB2 for LUW source database to Qlik Compose:

1. In the New Data Source window, enter the information as described in the table below.

Field Description

Type Select IBM DB2 for LUW.

Server Name Specify the name or IP address of the IBM DB2 for LUW server machine.

Port Optionally, change the default port.

User Name Specify your username for accessing the IBM DB2 for LUW database.

The specified user must have read/write privileges on the IBM DB2 for LUW

database.

Password Specify your password for accessing the IBM DB2 for LUW database.

Database

Name

Specify the name of the IBM DB2 for LUW database.

Schema Specify the schema containing the source tables.

Data source fields

2. Click Test Connection to verify that Compose is able to establish a connection with the specified

database and/or landing zone.

3. 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.

Database management options

5.6  Creating and managing the model

This section describes how to create, import and manage the model.

The model serves as the basis for data warehouse generation in Compose. There are three way of creating the

model: Use Compose to derive a tentative model by reverse engineering the source database(s) (a process

also known as "discovering"); Import a model created in ERwin or create the model manually in Compose.

In this section:

Setup and User Guide - Qlik Compose, May 2022 154

5 Data Warehouse projects

l

Reserved column names (page 155)

l

Generating the model (page 155)

l

Model limitations (page 162)

l

Validating the model (page 163)

l

Displaying the model (page 163)

l

Managing the model (page 166)

l

Creating expressions (page 181)

l

Opening the expression builder (page 182)

l

Defining reusable transformations (page 188)

Reserved column names

The following section lists the reserved column names. If the any of the discovered tables contain columns

with these names, you need to rename them in Compose. For information on renaming columns, see

Managing attributes (page 323).

l

BIR_MAPPING_NR - internal mapping identifier used in staging tables for ETL

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 257)

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

Generating the model

This section explains how to generate a Business Model from a source database. You can generate the model

using any of the following methods:

Setup and User Guide - Qlik Compose, May 2022 155

5 Data Warehouse projects

l

Use Compose to discover the source database or landing zone

l

Import an ERwin model into Compose

l

Create the model manually in Compose

For information about importing a model created in ERwin, see Importing the model from ERwin (page 160).

Discovering the Source Database or Landing Zone

Discovery can either be performed on the source database defined for the Qlik Replicate task or in the landing

zone. The decision where to perform the discovery is determined by several factors, as explained in the

following table:

Factor

Discover the

Landing Zone

Discover the Source

Database defined for

the Qlik Replicate

task

The source tables selected in the Qlik Replicate task contain

foreign keys that you want to maintain in the Compose

project.

Qlik Replicate does not support foreign key

replication.

- ✔

The source database defined for the Qlik Replicate task is not

natively supported by Qlik Compose.

✔ -

The selected source tables contain keys that are not relevant

to the data warehouse (e.g. surrogate keys and business keys)

✔ -

A transformation defined for the Qlik Replicate task means

that not all of the columns will be replicated to the landing

zone. In this case, you should discover the landing zone since

this is the data that you eventually want to appear in your

data warehouse.

✔ -

Discovery factors

To generate the model by discovery:

1. Open your project.

2. To generate the model from within Compose:

1. In the Model panel, select Discover from the drop-down menu in the top right corner.

In the Manage Model window, click the Discover toolbar button.

The Discover window opens.

2. Select whether to discover the source database or the landing zone and then click OK.

Setup and User Guide - Qlik Compose, May 2022 156

5 Data Warehouse projects

Note that the suffix "_landing" denotes the landing zone whereas the actual source database

appears without the suffix.

When discovering directly from Microsoft SQL Server source, TIME will be discovered

as STRING(16). As well as being mapped this way in Replicate, this will also maintain

accuracy when a TIME column is defined with high precision.

The Source Tables/Views Selection - Name window opens.

3. Choose one of the following Search for options:

l

To list tables only, select Tables.

l

To list views only, select Views.

l

To list tables and views, select All.

4. If you also want the internal Qlik tables to be included in the search results, select the Show Internal

Qlik Tables check box. This may be useful for debugging, but is not usually not necessary.

5. To display all tables/views, click Search.

6. To only display tables/views whose names contain a specific string, type the string in the Name field

and then click Search.

The tables/views will be displayed in the Results list.

7. In the Results list, select the source tables and/or views on which to base the model or click the >>

button (Add All) to add all of the tables in the schema.

You can select multiple tables/views by holding down the [Shift] (sequential selection) or

{Ctrl] (non-sequential selection) button.

8. To add the selected tables/views, click the > (Add) button.

Setup and User Guide - Qlik Compose, May 2022 157

5 Data Warehouse projects

If you add a table that already exists in the model with the same name, then the new table is

added with the name: source_table_name_01 (or source_table_name_02 if the name

source_table_name_01 already exists, and so on).

If the table contains attribute domains that differ from existing ones but have the same

name, they will also be appended with the _01 suffix.

9. Click OK to generate the model from the selected tables/views.

The Generating Model from [model name] window opens.

A progress bar indicates the current model generation progress. For each stage of the model

generation process, a corresponding message appears in the Messages list.

10. After the model has been generated, click Close.

11. Repeat Steps 2-9 to discover additional sources.

Clearing the Landing Zone metadata cache

To improve performance when reading from the Landing Zone or from the Data Warehouse tables, Compose

caches the metadata from both the Landing Zone and the Data Warehouse tables. However, synchronization

issues may sometimes occur if the metadata structure of the Landing Zone or the Data Warehouse tables is

altered outside of the Compose project.

If you aware of external changes to the metadata or if you notice any data synchronization anomalies,

Compose enables you to clear the metadata cache, either using the UI or using the CLI.

You can clear the Landing Zone metadata cache using either the Compose web console or the Compose CLI.

Clearing the metadata cache using the web console

When using the web console to clear the metadata cache, the following methods are available:

Method 1:

1. Open the Source Table/View Selection - <Landing_Zone_Name> window as described in Discovering

the Source Database or Landing Zone (page 156).

2. Click the Clear Cache button located below the Show internal Qlik tables option.

Method 2:

1. Click the Manage button at the bottom left of the Data Warehouse panel.

The Manage Data Warehouse Tasks window opens.

2. In the Mappings tab, click Clear Landing Cache.

For information on clearing the Data Warehouse cache, see Clearing the data warehouse metadata cache

(page 224).

Clearing the metadata cache using the CLI

You can also clear the metadata cache using the CLI.

Setup and User Guide - Qlik Compose, May 2022 158

5 Data Warehouse projects

Command syntax:

ComposeCli.exe clear_cache --project

project_name

[--type landing|storage] [--landing_zone

source_name]

Parameters

Parameter Description

--project The name of the project.

--type Which type of metadata cache to clear. Possible values are:

l

landing

l

storage



If --type

landing

and you want to clear a specific landing zone,

you must set the --landing_zone parameter as well. To clear the

metadata cache in all landing zones, specify --type

landing

and

omit the --landing_zone parameter.

--landing_zone the name of the landing zone when --type landing_zone

Example

ComposeCli.exe clear_cache --project MyProject --type landing --landing_zone MySource1

Importing entities and mappings from another project

You can import entities and mappings from another project with the same data warehouse type. This is

especially useful within a development environment if you need to integrate a private developer's project with

the main project.

To import entities and mappings:

1. Open the Manage Model window as described in Managing the model (page 166).

2. In the Entities toolbar, click the Import from Project button.

3. The Import from Project wizard opens.

4. In the Entities tab:

a. Select a project from the Import from Project drop-down list.

b. Optionally, search for specific entities.

c. Select which entities to import or select Select All to import all entities.

5. Click Next to select which mappings to import.

To create new entities and mappings if the selected entities and mappings already exist,

clear the Replace existing entities and mappings check box.

The new entities/mappings will be named

<existing_name>_IMPORTED

(or

<existing_

name>_IMPORTED_<n+>

if the entity/mapping is imported more than once).

6. In the Mappings tab:

Setup and User Guide - Qlik Compose, May 2022 159

5 Data Warehouse projects

Either click Finish to import all mappings for the selected entities (the default).

Select which mappings you want to import and then click Finish to import the selected entities and

mappings.

If you do not wish to import any mappings, clear the Mappings check box before clicking

Finish.

Importing the model from ERwin

In order to import a model created in ERwin, you first need to export the model from ERwin to an XML file and

then copy the XML file to the Compose Server machine. Note that when you import a model from ERwin, you

need to create the Mappings ETL scripts manually. You can either do this by creating global mapping as

described in Managing global mappings (page 160) below or you can create the mapping ETL directly in the

Data Warehouse panel.

For more information on creating the ETL mapping(s) in the Data Warehouse panel, see Creating and

managing the data warehouse (page 190).

To import the model from ERwin:

1. Open your project.

2. To import a model created in ERwin, in the Model panel, select Import from ERwin from the drop-

down menu in the top right corner.

In the Manage Model window, select Import from ERwin from the Entities drop-down menu.

The Import from ERwin window opens.

3. Specify the full path to the ERwin XML file.

4. If you have set up global mappings, select the Use Global Mappings check box. For details, see

Managing global mappings (page 160).

5. Select one of the following Read entities from options:

l

Logical model - Allows you to import logical entities and attributes.

l

Physical model - Allows you to import physical entities and attributes, exactly as they appear

in the source database.

6. Select a source database and then click OK.

The Select Tables/Views window opens.

7. Continue from Step 4 in Generating the model (page 155).

Managing global mappings

Before you import a data model from ERwin, you can set up the global mappings from the logical ERwin

model (the entities and attributes) to the physical source database (the tables and columns). This is useful if

numerous entities in your model contain the same attribute. For example, lets assume that twenty source

entities contain an attribute called "BusinessKey". In the physical source tables however, this column (which

also appears in twenty tables) is called "Key". Using the Global Mappings feature, you only need to define the

"Key-to-BusinessKey" mapping once instead of twenty different times.

Setup and User Guide - Qlik Compose, May 2022 160

5 Data Warehouse projects

When you import from ERwin, you can then select the Use Global Mappings check box to apply these

mappings. See also Importing the model from ERwin (page 160).

You can add, edit, and remove entity and attribute mappings. If needed, you can also change the source

database referenced for the tables (if you have several different sources defined).

To manage global mappings:

1. In the Model panel, from the drop-down menu in the top right, select Global Mappings.

The Global Mappings window opens in the Tables to Entities tab.

2. Import the ERwin entities:

1. Click Import Entities to Mappings toolbar button.

The Import Entities window opens.

2. In the File Path field, enter the full path to the ERwin.xml file (on the Compose Server machine)

that includes the entities you want to import.

3. Click OK.

3. Verify that Qlik Compose is using the desired source database. The database name is displayed in

green at the bottom right of the toolbar.

To select a different source database:

1. Click Change Source Database.

2. In the Set Source Database window, select a different database and then click OK.

4. Add new entities, edit existing entities, or remove entities as described in the following table:

To Do This

Add a new

entity

1. In the Tables to Entities tab, click the New toolbar button.

2. Next to the Entity Name field, click the browse button.

The Unmapped Entities window opens, listing only entities that have not

yet been mapped.

3. Select an entity and click OK.

4. Next to the Table Name field, click the magnifying glass icon.

The Find Table for [Entity Name] window opens for the selected entity.

5. From the Tables drop-down list on the left, select the table to map to.

6. Click OK. Qlik Compose populates the Table Schema field automatically,

based on the table you selected.

7. Repeat these steps for all unmapped entities.

Edit an entity 1. Move the mouse cursor over the entity and click the Edit button (pencil

icon) that appears on the right.

2. Make the required changes and click OK.

Delete an

entity

1. Select the entity.

2. In the Entities toolbar, click Delete.

3. When prompted to confirm the deletion, click Yes.

Entity management options

Setup and User Guide - Qlik Compose, May 2022 161

5 Data Warehouse projects

To Do This

Search for an

entity

In the Search look-up field, start typing. Qlik Compose only displays entities that

match the search string.

5. Add, edit, or remove attributes as described in the table below:

To Do This

Add a new

attribute

1. In the Columns to Attributes tab, click the New toolbar button.

2. Provide a name and description (optional) for the attribute and the column.

3. Click OK.

Search for

attribute

In the Search look-up field, start typing. Only attributes that match the search string

will be displayed.

When searching for an attribute based on the attribute name, you must add

the prefix "

name:

". For example, if you want to search for an attribute that

contains “ar” in its name, type “

name: ar

” in the Search look-up field.

Edit an

attribute

1. Move the mouse cursor over the attribute and click the Edit button (pencil

icon) that appears on the right.

2. Make the required changes and click OK.

Remove an

attribute

1. Select the attribute.

2. Click the Delete toolbar button.

3. When prompted to confirm the deletion, click Yes.

Attribute management options

6. Click Close.

Model limitations

l

When Amazon Redshift is the data warehouse type, attribute names that contain the open parenthesis

character "(" are not supported. If any of your attribute names contain the "(" character, you should

remove it before creating the data warehouse tables.

For information on renaming attribute names, see Add an attribute to all Satellite tables and the Hub

table (page 170).

l

Discovering new tables does not affect existing entities in the model, even if there is a relationship

between the new entity and one of the existing entities. For example, in the source database, Table 1

has a Foreign Key that points to Table 2. If Table 1 is added to the model and then Table 2 is added

later, Table 1 will not be updated to contain the required Foreign Key.

l

The data warehouse needs to be "adjusted" when deleting a relationship/attribute from the model and

then adding the same relationship/attribute back to the model. However, the "Adjust" operation

deletes the data from the corresponding data warehouse column.

Setup and User Guide - Qlik Compose, May 2022 162

5 Data Warehouse projects

Validating the model

Once you have generated the model, you can easily check that it is valid. For example, for a model to be valid,

each of the tables must have a Business Key.

Validating the model does not recalculate expressions for historical data that has changed. Changes

in a dimension expression or lookup of a column in a dimension are not updated retroactively. In

order to update historical data, you would need to reload the data which could take a long time

depending on the number of records and their history.

To validate the model:

1. Either click the Validate button in the bottom right of the Model panel.

Select Validate from the drop-down menu in the top right of the Model panel.

The Validate Model window opens.

a. If the model is valid, a message will confirm the model’s validity. If the model is not valid, a list

of invalid tables/views will be displayed.

A message indicating why the entity is invalid will be displayed in the Message column.

b. To resolve the issue, click the Edit Entities button to the right of the entity.

The Edit Model window opens showing the invalid entity.

2. Resolve the issue (in this case, by adding a Business Key) and then click Close.

A message will confirm the model’s validity.

3. Click Close to close the Validate Model window.

Displaying the model

Displaying the model is a good way to see the relationships between the various tables and/or views in your

model.

To display the model:

Either click the Display button in the bottom right of the Model panel.

-OR-

Select Display from the drop-down menu in the top right of the Model panel.

The Display Model window opens showing the Diagram tab.

Setup and User Guide - Qlik Compose, May 2022 163

5 Data Warehouse projects

Diagram tab

In the Diagram tab, the following options are available:

You can select multiple entities by clicking them while holding down the [Ctrl] keyboard button.

l

Zoom - Increase or decrease the magnification using the slider at the top right of the screen. Click the

button to the right of the slider to restore the default size.

l

Search - The ability to search for entities is particularly useful in a large model. To search for an entity,

type a search string in the Search box. Compose lists the names of entities that match the search

string. Select the desired entity.

l

Drag the diagram - In addition to zooming, you can also drag the diagram by clicking the space

around the diagram and dragging. This is useful for very large diagrams where zooming out would

Setup and User Guide - Qlik Compose, May 2022 164

5 Data Warehouse projects

render the text unreadable. The guide at the bottom right of the window shows you which part of the

diagram is currently displayed.

l

Show/Hide all attributes for a selected entity - Select an entity and then select/clear the Attributes

check box in the top left of the window.

l

Show/Hide all business keys in the model - Select/Clear the Keys check box in the top left of the

window.

l

Show/Hide relationship attributes - Right-click an entity and select this option to to show/hide the

entity's relationship attributes.

l

Show/Hide business keys - Right-click an entity and select this option to to show/hide the entity's

business keys.

l

Change the Diagram Direction - Select one of the available options from the Direction drop-down list

at the top of the window.

l

Set as relationship source - See Creating and managing relationships (page 174).

l

Hide this node - Right-click an entity and select this option to show/hide the entity. To show the

entity, click the Hidden Nodes box in the left of the window.

l

Hide selected nodes - Right-click an entity and select this option to show/hide selected entities. To

show the hidden entities, click the Hidden Nodes box in the left of the window.

l

Hide non-selected nodes - Right-click an entity and select this option to show/hide non-selected

entities. To show the hidden entities, click the Hidden Nodes box in the left of the window.

l

Invert selection - Right-click an entity and select this option to highlight all entities except the

selected entity.

l

Select all - Right-click an entity and select this option to highlight all entities in the model.

l

Select path - To highlight the path to which an entity belongs, either hover your mouse cursor over

the entity or right-click the entity and select Select Path.

l

Select path and hide all other nodes - Right-click an entity and select this option to highlight the

entity’s neighbors.

l

Edit - Either double-click the entity or right-click an entity and select the Edit option to edit the

entity’s attributes.

l

Lineage - Right-click an entity and select this option to show/hide the entity’s lineage. For more

information on lineages, see Lineage and impact analysis (page 177).

Tree View tab

In the Tree View tab, the following options are available:

l

Search for an entity or attribute - To search for a specific entity or attribute, enter a part of the name

in the Search box. Entities that match the search string will be highlighted.

l

Expand/Collapse - Click the arrow to the left of a table to see its attributes or related tables. To show

or hide all sub-tables and table attributes, click the Expand All/Collapse All buttons at the top of the

Tree View tab.

l

Lineage - To see an entity or attribute's lineage, hover your mouse over a table or attribute and then

click the button that appears to its right.

Setup and User Guide - Qlik Compose, May 2022 165

5 Data Warehouse projects

For example, clicking the button next to the City attribute (shown in the image above) will open

the following window:

For more information on lineages, see Lineage and impact analysis (page 177).

Managing the model

You can manage the model according to your needs, as described in the following topics:

l

The Manage Model window (page 167)

l

Managing entities (page 167)

l

Creating and managing relationships (page 174)

l

Managing attributes (page 168)

l

Bulk Editing History types and Satellite numbers (page 177)

l

Lineage and impact analysis (page 177)

There are two ways of editing a model in Compose:

l

In the Manage Model window - Editing the model in the Manage Model window is preferable if you

need to make several changes to the model as it provides access to all of the model’s entities and

attributes. To display the results of your changes, open the Model Display window as described in

Displaying the model (page 163).

l

From the Model Display - Editing the model from the Model Display window is convenient if you only

need to edit one or two entities. Another advantage of this method is that it allows you to see the

result of your changes (in the entity relationship diagram) immediately.

To open the Manage Model window from the Model panel:

1. Click the Manage button at the bottom left of the Model panel or click the Entities link in the Model

panel.

The Manage Model window opens.

2. Edit the model according to the descriptions below.

Setup and User Guide - Qlik Compose, May 2022 166

5 Data Warehouse projects

To open the Manage Model window from the Model Display window:

1. Open the Model Display window as described in Displaying the model (page 163).

2. Double-click the entity you want to edit.

The Manage Model window opens.

3. Edit the model according to the descriptions below.

The Manage Model window

The Manage Model window is split into two tabs: The Logical Model tab and the Physical Model tab. The

Logical Model tab shows the entities and attributes as they appear in the model whereas the Physical Model

tab provides a preview of the actual tables (and columns) that will be created in the data warehouse. So, for

example, although the Categories table appears as a single entity in the Logical Model tab, it will appear as

two tables (TDWH_Categories_HUB and TDWH_Categories_S01) in the Physical Model tab. The reason for

this is because the logical Categories entity contains both Type 1 and Type 2 attributes. Type 1 attributes will

be created as columns in the HUB table while Type 2 attributes will be created as columns in the Satellite

table (S01). For more information on Type 1 and Type 2 attributes, see History.

All editing tasks are performed in the Logical Model tab, except for the following tasks which are performed in

the Physical Model tab:

l

Designate a Distribution Key Column (Amazon Redshift data warehouse only)

l

Designate a Distribution Method (Microsoft Azure Synapse Analytics only)

For more information, see Defining Table Creation Modifiers (page 179).

Managing entities

You can add, edit and remove entities from your model as described in the table below.

All of the options available in the toolbar are also available from the drop-down menu in the toolbar.

This is useful when you reduce the window size, since some of the toolbar buttons - or all of them

depending on how small you make the window - will be hidden. The only button that will not be

hidden regardless of the eventual window size is the drop-down menu button.

To Do This

Add an entity 1. Click the New Entity button in the Entities toolbar.

2. Provide a name and description (optional) for the entity and then click

OK.

Edit an entity 1. Select the entity you want to edit and then click the Edit button in the

Entities toolbar.

2. Edit the entity’s name and description (optional) and then click OK.

Entity management options

Setup and User Guide - Qlik Compose, May 2022 167

5 Data Warehouse projects

To Do This

Remove an entity 1. Click the Delete button in the Entities toolbar.

2. When prompted to confirm the deletion, click Yes.

Duplicate an entity 1. Select the entity you want to duplicate and then select Duplicate from

the drop-down menu in the Entities toolbar.

2. Edit the entity’s name and description (optional) and then click OK.

The duplicated entity is added to the Entities list.

Import entities from

another project

See Importing entities and mappings from another project (page 159).

Import entities from

ERwin

See Importing the model from ERwin (page 160).

Managing attributes

You can add, edit and remove attributes as required. All attributes in the model belong to the Attributes

Domain. When adding a new attribute, you can either select an existing attribute from the Attributes Domain

or create a new Attributes Domain. Both of these options are described in the table below.

Setup and User Guide - Qlik Compose, May 2022 168

5 Data Warehouse projects

To Do This

Add an

attribute from

the attributes

domain

1. Click the New Attribute button in the Attributes toolbar.

The New Attribute window opens.

2. To designate the attribute as a business key, select the Key check box.

3. From the Attribute domain drop-down list, select the attribute domain you wish

to add.

4. To edit the selected attribute domain on-the-fly, click the edit button located after

the Attribute domain drop-down list. This will open the Edit -

AttributeDomainName window. Then, continue from Step 2 in Edit an attribute

domain.

5. In the Attribute name field, optionally change the default instance name for the

attribute domain.

You can create multiple instances of a single Attribute Domain. This is especially

useful if you want to use the same Attribute Domain across multiple tables, with

each "instance" having its own unique name. This also allows you to edit the

properties of each attribute without affecting the other attributes, despite all the

Attribute Domain instances sharing a common Attribute Domain. For example, if

the Attribute Domain name is "ID", you could create one instance for it in the

"Categories" entity named "CategoryID" and another instance in the "Employees"

entity named "EmployeeID". If, however, you edit the parent Attribute Domain

attribute, all instances of that attribute will be updated as well.

6. To add a prefix to the attribute name, enter the desired prefix in the Prefix field.

Adding a prefix to an attribute name allows you to add multiple instances of the

same attribute domain. For example, the attribute "Employee" could become two

different attributes: "ReportsTo_Employee" and "HiredBy_Employee".

7. Set the History Type and Satellite number. When the History Type is set to 2, a

new record will be created in the data warehouse each time an attribute value

changes.

8. In the Satellite/Hub field, optionally change the satellite number. Note that the

satellite number can only be changed when the History Type is set to 2. For an

explanation of why this is so, see The Manage Model window (page 167).

9. To add an expression, click the fx button located after the Expression field and

then continue from Creating expressions (page 181).

10. Click OK to save your settings.

Attribute management options

Setup and User Guide - Qlik Compose, May 2022 169

5 Data Warehouse projects

To Do This

Create a new

attribute

domain and

add it to the

model

1. Click the New Attribute button in the Attributes toolbar.

The New Attribute window opens.

2. To designate the attribute as a business key, select the Key check box.

3. Click the plus sign to the right of the Attribute domain drop-down list.

The New Attribute Domain window opens.

a. Specify a Name for the attributes domain.

b. From the Type drop-down list, select one of the available data types.

c. If the selected data type requires further configuration, additional fields will

be displayed. For example, when Decimal is selected, the Length and Scale

fields will be displayed. Set the values as desired.

d. Optionally, specify a Description.

e. Click OK to add the newly created attribute domain to the Attribute

domain field and close the New Attribute Domain window.

4. Continue from Step 5 in Add an existing attribute domain above.

You can also add new attribute domains via the Manage Attribute

Domains window. For more information, see Managing the Attributes

Domain (page 173)

Add a

relationship

See Creating and managing relationships (page 174).

Add an

attribute to all

Satellite

tables and the

Hub table

You can use the Add to all Satellites and Hub option to define the same Primary Index

for the Hub table and all Satellite tables.

Select the desired attribute and then click the Add to all Satellites and Hub toolbar

button. The attribute is added to the Hub table and to all the Satellite tables.

Edit an

attribute

Method 1:

1. Select the attribute you want to edit and then click the Edit button in the

Attributes toolbar.

The Edit - AttributeName window opens

2. Continue from Step 2 of Add an attribute from the attributes domain above.

Method 2:

1. Double-click the attribute you want to edit.

The values in the attribute row become editable.

2. Continue from Step 2 of Add an attribute from the attributes domain above.

Setup and User Guide - Qlik Compose, May 2022 170

5 Data Warehouse projects

To Do This

Bulk edit

history types

and satellite

numbers

See Bulk Editing History types and Satellite numbers (page 177).

Show an

attribute's

lineage

See Lineage and impact analysis (page 177).

Remove an

attribute

1. Select the attribute(s) you want to delete.

2. Click the Delete button in the Attributes toolbar.

3. When prompted to confirm the deletion, click Yes.

Change the

attribute

order

Select the attribute you want to move and use the Move Up/Move to Top and Move

Down /Move to Bottom toolbar buttons to move the attribute.

Search for an

attribute

In the Search lookup field, start typing. Only attributes that match the search string will

be displayed.

When searching for an attribute based on the attribute name, you must add the

prefix "

name:

". For example, if you want to search for an attribute that contains

“ar” in its name, type “

name: ar

” in the Search look-up field.

Manage the

Attributes

Domain

See Managing the Attributes Domain (page 173).

Create an

expression for

an attribute

See Add an attribute from the attributes domain or Edit an attribute above.

Export the

attributes to a

CSV file

Select an entity from the Entities list on the left of the Manage Model window and then

select Export to CSV from the drop-down menu in the Attributes toolbar. Depending on

your browser settings, you will either be prompted to download the <entityname>_

Attributes.csv file or it will be downloaded to your default Downloads location.

The CSV format differs slightly from the CSV format when Exporting and

importing projects using the CLI (page 78).

Setting up derived attributes

Derived attributes are attributes whose data is "derived" from other attributes. For example, lets assume that

the OrderDetails entity contains the attributes Quantity and UnitPrice but does not contain the attribute

TotalPrice. To gain better insight into the annual sales figures, the organization would like to add the

TotalPrice attribute to the model and derive its data from the Quantity and UnitPrice attributes.

Setup and User Guide - Qlik Compose, May 2022 171

5 Data Warehouse projects

Assuming that the Northwind sample database is the model’s source, this could easily be done as follows:

1. Add the TotalPrice attribute domain to the model as described in Managing attributes (page 168).

2. After finalizing the model, create the data warehouse tables as described in Creating the data

warehouse tables (page 192).

3. Click the OrderDetails mapping as described in Editing column mappings (page 202).

Note that the TotalPrice attribute has no mapping as it was added after the Northwind source was

discovered:

4. Open the Expression Builder by clicking the fx icon to the right of the TotalPrice column name. Then,

in the Expression Builder, add the Quantity and UnitPrice columns to create the following

expression:

Quantity*UnitPrice

For more information on creating expressions, see Creating expressions (page 181).

5. Click OK to close the Expression Builder and save the expression.

The Quantity and UnitPrice landing zone columns are now mapped to the TotalPrice data

warehouse column. Notice that the mapping lines are gray, indicating that the mapping is the result of

an expression.

Setup and User Guide - Qlik Compose, May 2022 172

5 Data Warehouse projects

Hovering the mouse cursor over the gray lines highlights the derived column (TotalPrice) and the

columns from which its data is derived (Quantity and UnitPrice).

Managing the Attributes Domain

The Attributes Domain provides a list of all the attributes available in the Compose model, as well as their

data type. You can add, edit and delete attributes according to your data warehousing needs. The Attributes

Domain also allows you to see which entities each attribute belongs to, as a single attribute may be present in

several entities.

To manage the Attributes Domain

1. From the drop-down menu in the top right of the Model panel, select Attributes Domain.

2. Add, delete and edit attributes as describe in the table below.

To Do This

Add an

attributes

domain

1. Click the New Attributes Domain toolbar button.

The New Attribute Domain window opens.

2. In the Name field, specify a name for the attribute.

3. From the Type drop-down list, select one of the available data types.

4. If the selected data type requires further configuration, additional fields will be

displayed. For example, when Decimal is selected, the Length and Scale fields will

be displayed. Set the values as desired.

5. Optionally specify a Description.

6. Click OK to add the attribute and close the New Attribute Domain window.

Attribute domains names are case insensitive. For example, a project cannot

contain one attribute domain called date and another called DATE.

Attribute Domain management options

Setup and User Guide - Qlik Compose, May 2022 173

5 Data Warehouse projects

To Do This

Edit an

attribute

domain

1. Select the desired attribute and then click the Edit toolbar button.

The Edit: Name window opens.

2. Edit the attribute as described in steps 2-6 of Add an attributes domain above.

Note that the Edit: Name window also contains a Used in Entities list. Knowing

which entities the attribute is used in may affect the type of changes you make, as

the planned changes may not be appropriate for all entities.

Remove an

attribute

1. Select the attribute you want to delete and then click the Delete toolbar button.

2. When prompted to confirm the deletion, click Yes.

Creating and managing relationships

Similar to a foreign key, a relationship "attribute" is a special type of attribute that points to another entity in

the same model. Typically, the relationship replaces the key attributes that connect an entity to a related

entity. You can add, edit and delete relationships as required.

Possible reason for creating relationships are as follows:

l

If your model is derived from the landing zone (as opposed to the source database(s)), the model will

be created without any relationships

l

Ensure data integrity between related entities

You can create relationship from the Manage Model window or from the Display Model window. Both of

these methods are described below.

When converting existing columns in a table with a relationship to another table, historical values

may be lost and need to be loaded again or reinserted manually.

Adding relationships via the Manage Model window

1. Click the Manage button in the bottom left of the Model panel.

The Manage Model window opens.

2. Select an Entity in the Entities list.

3. Click the Add Relationship button in the Attributes toolbar.

The Add Relationship From: Name window opens.

4. From the Add Relationship to Entity drop-down list, select the entity to which you want to create a

relationship.

5. If the originating entity contains attributes that were foreign keys in the source database, you can

replace these attributes with Business Key attributes of the associated entity.

To do this:

a. Select the Replace Existing Attribute(s) check box.

The left column shows the Business Key Attributes of the Associated Entity.

b. From the Attributes of Originating Entity drop-down list on the right, select an attribute from

the originating entity that was meant to be a foreign key.

Setup and User Guide - Qlik Compose, May 2022 174

5 Data Warehouse projects

6. If you want the relationship attribute to be a Business Key, select the Business Key check box. This

option will only be displayed if the entity target can be designated as a Business Key.

7. Set the History Type.

Since the history type for Business Keys must be type 1, the option to change the history type

is unavailable when the Business Key check box is selected.

8. Set a Satellite Number.

Since the satellite number for Business Keys must be "0", the option to change the satellite

number is unavailable when the Business Key check box is selected.

9. Optionally, specify a prefix.

10. Optionally, enter a description.

11. Click OK to save your settings.

Adding relationships via the Display Model window

1. Click the Display button in the bottom left of the Model panel. The Display Model window opens.

2. Select one of the following methods:

l

Method 1: Right-click an entity and select Add Relationship.

The Add Relationship From: Name window opens.

l

Method 2: Right-click an entity and select Set as Relationship Source. This method is useful if

you need to search your model for the relationship target entity (since the source entity

remains selected while you search).

l

Method 3: Select two entities by clicking them while holding down the [Ctrl] key. Then, right-

click one of the entities and select the desired relationship from the context menu (according to

the entity that you want to be the relationship source), as shown in the following example:

3. If you selected Method 2, continue below. If you selected Method 1, continue from Step 4 in Adding

Relationships via the Manage Model window above. If you selected Method 3, continue from Step 5

in Adding Relationships via the Manage Model window above.

4. Right-click the relationship target entity and select Relationship Target for Relationship Source

Name.

The Add Relationship: Name window opens with the relationship target entity already selected.

Setup and User Guide - Qlik Compose, May 2022 175

5 Data Warehouse projects

5. If the originating entity contains attributes that were foreign keys in the source database, you can

replace these attributes with Business Key attributes of the associated entity.

To do this:

a. Select the Replace Existing Attribute(s) check box.

The left column shows the Business Key Attributes of the Associated Entity.

b. From the Attributes of Originating Entity drop-down list on the right, select an attribute from

the originating entity that was meant to be a foreign key.

6. If you want the relationship attribute to be a Business Key, select the Business Key check box. This

option will only be displayed if the entity target can be designated as a Business Key.

7. Set the History Type.

Since the history type for Business Keys must be type 1, the option to change the history type

is unavailable when the Business Key check box is selected.

8. Set a Satellite Number.

Since the satellite number for Business Keys must be "0", the option to change the satellite

number is unavailable when the Business Key check box is selected.

9. Optionally, specify a prefix.

10. Optionally, enter a description.

11. Click OK to save your settings.

Preventing naming conflicts

When a relationship from entity A to entity B is created, Compose implicitly adds entity B’s primary key

columns to table A. This means that if there are two or more relationships from entity A to entity B, a column

naming conflict will arise (as entity B’s primary key columns will be added to table A multiple times). Such

conflicts can easily be avoided by adding a meaningful prefix to the relationship attributes in entity A, which

will result in the prefix being added to the physical columns as well.

Example:

The Orders entity contains two attributes that are related to the People entity: the Customer and Seller

attributes. Therefore, Mike wants to create two relationships from the Orders entity to the People entity. The

primary key of the People table consists of the FirstName and LastName attributes. As there are two

relationships, the primary key columns of the People entity will be added twice to the Orders entity. To

prevent duplication errors, Mike adds the Customer_ and Seller_ prefixes to the relationship attributes in the

Orders entity, which results in the physical columns Customer_FirstName, Seller_FirstName, Customer_

LastName, and Seller_LastName.

Deleting relationships

1. Click the Manage button in the bottom left of the Model panel.

The Manage Model window opens.

2. Select the relationship attribute you want to delete.

3. Click the Delete button in the Attributes toolbar.

Setup and User Guide - Qlik Compose, May 2022 176

5 Data Warehouse projects

The Delete Relationship window opens.

4. To restore an attribute that was replaced when the relationship was created, select the Restore

original attribute(s) check box. For more information about replacing attributes, see Step 5 in Adding

relationships via the Manage Model window above.

5. Click Yes to delete the relationship attribute.

Bulk Editing History types and Satellite numbers

Use the Bulk Edit feature to edit the History type and Satellite number of multiple attributes.

To bulk edit history types and satellite numbers:

1. Select the attributes whose History type and/or Satellite number you want to change and click the

Bulk Edit toolbar button.

2. In the Bulk Edit window, change the History type and/or Satellite number as required.

3. Click OK close the Bulk Edit window and save your settings.

Lineage and impact analysis

Before editing an entity or attribute, you may want to see which other entities/attributes in the

entity’s/attribute’s lineage will be impacted by the change. For example, removing the "Discount" attribute

from a table will affect the "Total Price". Additionally, a single attribute may have different names depending

on its location.

Places where you can view lineage in Compose:

l

The Manage Model window described below.

l

The Display Model window described in Displaying the model (page 163).

l

When editing a data mart. For more information, see Managing data marts (page 234).

Top-level entities in the data mart fact will not be shown in the lineage. For example, if both the

Orders and Order Details entities are used in a Fact, the Model lineage for Orders will show Order

Details but not Orders.

To view the lineage of an entity or attribute:

1. Click the Manage button in the bottom left of the Model panel.

The Manage Model window opens.

2. Display the lineage as described below:

To Do This

Show an entity’s

lineage

Select the entity and select Show Lineage from the drop-down menu in the

Entity toolbar.

Show an attribute’s

lineage

Select the attribute and click the Show Lineage button in the Attribute toolbar.

Lineage procedures

Setup and User Guide - Qlik Compose, May 2022 177

5 Data Warehouse projects



Adding Date and Time entities to your model

Compose provides built-in Date and Time entities that you can add to your model. This facilitates access to all

attributes of date and time (such as day of the week, quarter, and so on) both in the BI reports and when

creating transformations in the data mart.

The Date entity contains a record for every day. Dates in the Date entity range from January 1st 1900 to

December 31st 2099.

The Time entity contains all the hours and minutes in a 24 hour period. When you create the data warehouse

tables, the Date and Time entities are automatically populated with relevant data. You can view this data as

described in Viewing the data warehouse tables (page 193).

Both the date and the time values are presented in multiple formats (e.g. 12 hour format or 24 hour format),

allowing you to choose which format will be displayed in your BI reports. Other format include abbreviated

forms of date and time, different month/year/day formats (e.g. 12/31/2017 as opposed to 2017-12-31), and so

on.

You can either add the entities to a new project (before you create the Data Warehouse tables) or to an

existing project. If you add them to an existing project’s model, you will also need to validate and adjust the

Data Warehouse as described in Validating the data warehouse (page 222).

You can even add custom date and time attributes to the entities from the tables in your landing zone. For

example, if one of your source tables lists all the working days and non-working days, you can add an "Is

Working Day" attribute to the Date entity and then load it from the relevant source table. Just like regular

entities, Compose knows how to merge the incoming data of working and non-working days into the existing

Date entity.

For an explanation of how to add attributes to an entity, see Managing attributes (page 168).

You cannot add relationships to the Date and Time entities. However, every date and time attribute has an

implicit relationship to the Date and Time dimensions, which allows you to select the relevant dimension

when creating your star schema in the data mart.

For information on working with Date and Time dimensions in the data mart, see Creating and managing data

marts (page 225).

For all of the supported data sources except Oracle, you can add both Date and Time entities to your

model. If you are using Oracle as your data source, you can only add the Date entity to you model.

This is because Oracle does not have a data type specifically for Time.

To add Date and Time entities to your model:

1. Open the desired Compose project.

2. From the drop-down menu in the top right of the Model panel, select Add Date and Time entities or

Add Date Entity if you data source is Oracle (see Note above).

Setup and User Guide - Qlik Compose, May 2022 178

5 Data Warehouse projects

3. When prompted to confirm the action, click Yes.

4. The Date and Time entities will be added to your model. By default, the Date and Time entities are

hidden from the model display (as they are not related to other entities in your model). If you want to

show them anyway, select the Date and Time model check box in the Data Warehouse Model window.

5. For information about displaying the model, see Displaying the model (page 163).

You can also delete the Date and/or Time entities if you no longer require them and add

them again later.

Defining Table Creation Modifiers

You can set table modifiers for individual entities in the Physical Model tab, thereby overriding the default

settings in the project settings'Table creation modifiers tab (page 45) Table modifiers allow you to append

additional table properties to the default Compose CREATETABLE statement.

The available options are located below the Columns list on the right of the tab, and are as follows:

l

Project settings default - When this option is selected (the default), the settings from the project

settings' Table creation modifiers tab (page 45) will be used.

l

Custom - This option is useful for appending additional table properties to the default Compose

CREATETABLE statement. Leveraging this option requires SQLcoding knowledge.

l

Custom distribution keys - This option is useful if you only need to define custom distribution keys

for individual entities. Although this can also be done using the Custom option (see below), the

Custom distribution keys option is more convenient as it does not require any prior SQL knowledge.

l

Supported with Microsoft Azure Synapse Analytics and Amazon Redshift only.

l

The default distribution key for all data warehouse tables is the ID column.

Setting table creation modifiers

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.

The procedure for settings table modifiers is as follows:

1. In the Physical Model tab, select the desired entity.

2. Select the Custom option.

3. Click the Edit button to open the Table Creation Modifier editor.

4. Enter the SQL parts you wish to append to the CREATE TABLE statement.

5. Optionally, but strongly recommended, validate the SQL in an external validation tool that supports

your specific database and version. For instance, if you are validating

Setup and User Guide - Qlik Compose, May 2022 179

5 Data Warehouse projects

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

the SQL before deploying in a production environment.

6. Click OK to close the editor and save your SQL parts.

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)

Setting Custom Distribution Keys

This section describes how to set a custom distribution key for tables created in Amazon Redshift and

Microsoft Azure Synapse Analytics. Note that depending on the selected Distribution Style (Amazon Redshift)

or Distribution Method (Microsoft Azure Synapse Analytics), some of the options may not be available.

Setting a distribution key for Amazon Redshift Data Warehouse

Select and entity and then set a distribution key for Amazon Redshift Data Warehouse according to the table

below.

To Do This

Set a distribution style From the Distribution Style drop-down, select Even, Key or All.

For more information on distribution styles, see:

Distribution styles - Amazon Redshift

Add a distribution key 1. Click the Add Distribution Key button.

A row is added to the table displaying a drop-down list.

2. Select one of the available columns.

Edit a distribution key 1. Double-click the row.

A drop-down list will be shown in the Column column.

2. Select one of the available columns.

Delete a distribution key Select the distribution key and then click the Delete button. The key is

deleted.

Change the position of a

distribution key

Select the distribution key and then click the "Up" or "Down" buttons to

move the key to the desired position.

Distribution key procedures

Setup and User Guide - Qlik Compose, May 2022 180

5 Data Warehouse projects

Setting a distribution key for Microsoft Azure Synapse Analytics

Select and entity and then set a distribution key for Microsoft Azure Synapse Analytics according to the table

below.

To Do This

Set a distribution method From the Distribution Method drop-down, select Hash, Round Robin or

Replicate.

For more information on the distributions options, see:

Guidance for designing distributed tables in Synapse SQL pool - Microsoft

Azure

Add a distribution key 1. Click the Add Distribution Key button.

A row is added to the table displaying a drop-down list.

2. Select one of the available columns.

Edit a distribution key 1. Double-click the row.

A drop-down list will be shown in the Column column.

2. Select one of the available columns.

Delete a distribution key Select the distribution key and then click the Delete button. The key is

deleted.

Change the position of a

distribution key

Select the distribution key and then click the "Up" or "Down" buttons to

move the key to the desired position.

Distribution key procedures

Creating expressions

Compose allows you to create data transformations in several different places according to your needs. A

transformation can either be a filter (i.e. excluding certain data) or an expression (i.e. manipulating a single

record). The table below lists the places where transformations can be created and provides reasons for

creating the transformation in each of the specified places.

Changes in a dimension expression or lookup of a column in a dimension are not updated

retroactively. In order to update historical data, you would need to reload the data which could take

a long time depending on the number of records and their history.

Setup and User Guide - Qlik Compose, May 2022 181

5 Data Warehouse projects

Where the

Transformation is

Created

Reasons to Create a Transformation There

When the Transformation is

Applied

Replicate

l

Filtering large amounts of data that is not

needed for the data warehouse (in the

present or the future)

l

Obfuscation due to regulatory reasons or

internal policies

l

Data type conversion (e.g. converting a

source data type that is not supported on

the data warehouse platform)

Before the data reaches the

landing zone.

Model

l

The default location if you are not sure

where to put it

l

General business logic

l

Needed for several sources or several data

marts

Applied as an update to the

staging tables after creating the

mappings.

Data Warehouse

l

Specific source preparation

l

Needed for merging several sources

Between the landing zone and

the staging zone.

Data Mart

l

Specific to a data mart

l

Managed by a data mart data specialist

Between the data warehouse

and the data mart.

Data transformation location comparison

See also Defining reusable transformations (page 188).

The following topics describe the Expression Builder:

l

Opening the expression builder (page 182)

l

Expression builder overview (page 183)

l

Building expressions (page 184)

l

Testing expressions (page 185)

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 181).

Setup and User Guide - Qlik Compose, May 2022 182

5 Data Warehouse projects

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 184).

The following tabs are available:

l

Parameters - Only displayed when opening the Expression Builder from within the Reusable

Transformations > Edit Transformation window.

For information on reusable transformations, see Defining reusable transformations (page 188)

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 188)

below.

l

Operators - Operators that can be used to build your expression.

l

Functions - Functions that can be used to build your expression.

Setup and User Guide - Qlik Compose, May 2022 183

5 Data Warehouse projects

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 184).

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 185).

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 185).

Building expressions

The first step in using the Expression Builder is to build an expression in the Build Expression pane.

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:

Setup and User Guide - Qlik Compose, May 2022 184

5 Data Warehouse projects

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

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 187).

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.

Setup and User Guide - Qlik Compose, May 2022 185

5 Data Warehouse projects

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, May 2022 186

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 184).

2. Click Parse Expression as described in Parsing expressions (page 185).

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 185).

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 187), type Mike for FirstName and

Smith for LastName. The result displayed is Mike Smith.

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

Setup and User Guide - Qlik Compose, May 2022 187

5 Data Warehouse projects

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 202) in

Creating and managing the data warehouse (page 190).

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.

Setup and User Guide - Qlik Compose, May 2022 188

5 Data Warehouse projects

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.

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 181).

Setup and User Guide - Qlik Compose, May 2022 189

5 Data Warehouse projects

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 181).

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 188).

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 188). 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:

l

Data warehouse tasks (page 191)

l

Managing tasks (page 201)

l

Viewing and exporting task statements (page 216)

l

Modifying task settings (page 217)

l

Validating the data warehouse (page 222)

l

Clearing the data warehouse metadata cache (page 224)



Setup and User Guide - Qlik Compose, May 2022 190

5 Data Warehouse projects

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 191)

l

Creating the data warehouse tables (page 192)

l

Generating the task (page 195)

l

Controlling data warehouse tasks (page 196)

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:

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.

Setup and User Guide - Qlik Compose, May 2022 191

5 Data Warehouse projects

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 262).

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

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.

Setup and User Guide - Qlik Compose, May 2022 192

5 Data Warehouse projects

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.

When you click the link, the Data Warehouse Tables window opens showing a list of all the tables in your

data warehouse.

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_

Setup and User Guide - Qlik Compose, May 2022 193

5 Data Warehouse projects

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

In the data mart tables, the RUNNO_INSERT/RUNNO_UPDATE column names are prefixed by the

table name e.g. ORDERS_RUNNO_UPDATE.

To view a specific table, simply double-click the table.

Apart from the Date and Time tables which are automatically populated on creation, the other

tables will be empty until you run the data warehouse task.

See Controlling data warehouse tasks (page 196) below for information on running a data

warehouser task.

In the <Table Name> window, you can perform the following tasks:

l

Choose how many rows to display from the Rows drop-down list.

l

Click the Column Settings button to choose to choose which columns will be displayed and the order

in which they will be displayed.

Creating a Change Processing task

You can create a CDC task by duplicating the Full Load task. This is useful if you currently have a Full Load

task only and the need (which wasn't previously anticipated) arises to capture changes from all of the tables

or only from certain tables.

To do this:

1. Select the Full Load task in the left pane of the Manage Data Warehouse Tasks window and click the

Duplicate toolbar button.

2. In the Duplicate <ETL_Set_Name> window, set the following properties:

l

Task name - The name for the task (e.g. employees_changes).

l

Select a landing zone - Select the same landing zone as the Full Load task.

l

Schemas - Select the same schema as the Full Load task.

l

Task type - Select Change Tables.

3. To only apply changes to selected tables, in the Mappings column, select the tables whose changes

you want to apply to the data warehouse.

Setup and User Guide - Qlik Compose, May 2022 194

5 Data Warehouse projects

If you want to modify a column's mapping, the table name displayed in the left pane of the

Edit Mappings window will not be appended with the "__ct" suffix (which is the default

naming format for Replicate Change Tables). However, the changes will still be taken from

the associated Change Table.

For each Primary Key there is one record. Changing a Primary Key on the source record will

cause a new record to be inserted in the data warehouse.

4. Click Generate and wait for the task statements to be generated.

5. To capture the changes and apply them to the data warehouse, click Run.

Reloading data from the source tables to the Landing Zone

In cases of inconsistencies or when metadata from the source tables is not replicated to the Landing Zone,

you may need to reload the data.

To reload data from the source tables:

1. Run the Qlik Replicate Full Load replication task again.

2. Run the Compose for Data Warehouses Full Load storage task.

3. Continue running the existing Compose Change Processing task.

No duplicates are created, as Compose compares the records before adding them to the

Data Warehouse.

Records that should be deleted in the new Full Load task are not deleted nor marked for

deletion (soft delete).

Generating the task

After the data warehouse tables have been created, you then need to generate the task that will be used in

the data warehouse task. The task contains the Mappings ETL (which is automatically created) and any

custom ETLs that you have defined. If you need to make changes to the Mappings or define custom ETLs,

continue from Managing tasks (page 201) and Creating and managing custom ETLs (page 197) respectively.

To generate the data warehouse task:

1. Click the Manage button in the bottom left of the Data Warehouse panel. The Manage Data

Warehouse Tasks window opens.

2. If you have more than one task, in the left pane, select the task that you want to generate.

3. Do one of the following:

Setup and User Guide - Qlik Compose, May 2022 195

5 Data Warehouse projects

l

To generate the task with all validations, click the Generate toolbar button .

l

To generate the task with basic validations, click the inverted triangle to the right of the

Generate button and select Basic validations from the drop-down menu.

By default, Compose generates the task with all validations. However, when many expressions and

lookups are defined, generating with all validations may take a long time. In such a case, you can try

generating with basic validations instead. With basic validations, validations that may take a while

(such as those that access databases to verify the existence of columns used in expressions and

lookups), will be skipped.



The Generating task for <Name> progress window opens. When the "Generate task finished

successfully" message is displayed, close the window.

Only mappings selected in the Manage Data Warehouse Tasks window will be generated.

Controlling data warehouse tasks

Once the data warehouse tables have been created and the task has been generated, you can then proceed to

run the data warehouse task. The data warehouse task extracts data from the landing tables, loads it into the

staging tables, and finally loads the data into the data warehouse tables.

Ingesting a historical record deletes any history that is later than the ingested record. For example, if

a data warehouse contains the following historical records:

2012 - Boston

2014 - Chicago

2015 - New Jersey

Ingesting the record

2013 - New York

will delete the

2014

and

2015

records.

Data warehouse tasks can be run manually, scheduled to run periodically or run as part of a workflow. The

section below describes how to run a data warehouse task manually. For information on scheduling data

warehouse tasks or including them in a workflow, see Controlling and monitoring tasks and workflows (page

260).

Data warehouse tasks cannot run in parallel with data mart tasks. Data warehouse tasks that

update the same tables cannot run in parallel.

To run a data warehouse task:

1. Click the Manage button in the bottom right of the Data Warehouse panel. The Manage Data

Warehouse Tasks window opens.

2. If you have more than one task, in the left pane, select the task that you want to generate.

3. Click the Run toolbar button. The window switches to Monitor view and a progress bar shows the

current progress in terms of percentage.

Setup and User Guide - Qlik Compose, May 2022 196

5 Data Warehouse projects

You can stop the task at any time by clicking the Abort toolbar button. This may be necessary if you

need to urgently edit the task settings due to some unforeseen development. After editing the task

settings, simply click the Run button again to restart the task.

Aborting a task may leave the data warehouse tables in an inconsistent state. Consistency

will be restored the next time the task is run.

4. When the progress reaches 100% completed, close the Manage Data Warehouse Tasks window.

Other monitoring information such as the task details (i.e. the number of rows inserted/updated) and the task

log files can be accessed by clicking the Run Details and Log buttons respectively.

Once the data warehouse has been successfully loaded into the data warehouse tables, you can then proceed

to the final part of the Compose workflow - defining and populating data marts. For more information, see

Creating and managing data marts (page 225).

Creating and managing custom ETLs

In addition to the Mappings ETL, you can define custom ETLs as required. User-defined ETLs can perform a

number of useful operations such as defining specific transformations, gathering statistics, performing

cleansing, and filtering data.

Common Table Expressions (CTEs) are not supported as well as some special clauses.

To create a custom ETL:

1. Click the Manage button in the bottom left of the Data Warehouse panel. The Manage Data

Warehouse Tasks window opens.

2. Select one of the following tabs according to your needs:

l

Pre Loading ETL - to define an ETL that will manipulate the data before it is loaded from the

landing tables to the data warehouse staging tables. When enabled, the Pre-loading ETL will be

run even if there are no mappings or Replicate-generated source data associated with it, which

is particularly useful for customer wanting to perform transformations on data generated by

third-party tools.

l

Multi Table ETL - to define an ETL for multiple tables.

l

Single Table ETL - to define an ETL for a single table.

l

Post Loading ETL - to define an ETL that will be executed after the data has been loaded from

the staging tables to the data warehouse.

3. If you selected Single Table ETL, select an entity in the Entity column and then click the New button

above the Entity list. For Multi Table and Post Loading ETLs, just click the New button.

4. Specify a name for your ETL and then click OK.

If you selected Single Table ETL, the ETL is added as a link to the User Defined ETL column. If you

selected Multi Table ETL or Post Loading ETL, the ETL is added as a link in their respective tabs.

5. Click the link to open the Edit ETL Instructions window.

Setup and User Guide - Qlik Compose, May 2022 197

5 Data Warehouse projects

6. If you selected Single Table ETL, select a column and click the arrow to the right of the selected

column to add it to the ETL.

If you selected Multi Table ETL or Post Loading ETL, select a table and a column and then click the

arrow to the right of the selected table/column to add it to the ETL. Repeat as necessary.

7. Use the Select, Delete, Insert and Update toolbar buttons at the top of the window to add SQL

statements to your ETL.

8. To run the ETL as a stored procedure (that already exists in the data warehouse):

a. Select the Execute as Stored Procedure check box.

b. Click the Stored Procedure toolbar button.

c. Replace STORED_PROCEDURE with the name of your stored procedure and replace(PARAM1,

PARAM2) with any parameters that it needs. Note that parameters must be separated by a

comma. If no parameters are required, use empty parenthesis or drop them altogether.

9. Use the Undo, Redo and Reset buttons at the bottom of the window if needed.

10. Optionally, specify a description in the Description box at the bottom of the window.

11. To save your ETL, click OK.

Single table example

The following example, based on the Data warehouse project tutorial (page 107) in Getting started with Data

Warehouse projects (page 104), demonstrates how to concatenate two columns called "First Name" and "Last

Name" into a single column called "FullName".

1. Click the Manage button in the Model panel. The Manage Model window opens.

2. Select Employees from the Entities list on the left.

3. Click the + (plus) toolbar button to add a new Attribute. A new row is added to the Attributes table.

4. Type any letter in the Column Name column to bring up the "Add New" option. Click the "Add New"

option when it appears.

The New Attribute Domain window opens.

5. In the Name field, type FullName. From the Type drop-down list, select Varchar. In the Length field,

enter 100.

6. In the History column, select Type 1 from the drop-down list.

7. Click OK to close the New Attribute Domain window and add the attribute to the Attributes table.

8. Then click OK again (below the newly added attribute) to exit edit mode.

Setup and User Guide - Qlik Compose, May 2022 198

5 Data Warehouse projects

9. Close the Edit Model window.

10. In the Data Warehouse panel, click the Create button.

11. After the Data Warehouse tables have been created, close the Creating Data Warehouse window.

12. Click the Manage button in the bottom left corner of the Data Warehouse panel. The Manage Data

Warehouse Tasks window opens.

13. To view the current mappings between the source columns and data warehouse columns, click the

Map_Employees_1 link in the Mappings column. A "Processing" icon is displayed while the mappings

are generated. After the mappings are generated, the Edit Mappings - Map_Employees_1 window

opens automatically.

Note that the FullName column has been added to the data warehouse columns, but is currently not

mapped to the source columns.

14. The next stage is to define an ETL that will map the First Name and Last Name source columns to the

Full Name data warehouse column.

15. Close the Edit Mappings - Map_Employees_1 window and then select the Single Table ETL tab on

the left.

16. Select Employees in the Entity column and then click the New button above the column. The Add

New Single Table ETL window opens.

17. Specify a name or leave the default name and then click OK.

18. Click the Edit button (represented by a pencil icon) at the end of the Employees row. The Edit Single

Table ETL: <Name> window opens.

19. In the editing pane on the right, enter the following instruction:

UPDATE dbo.TSTG_EMPLOYEES set

FullName = LASTNAME + FIRSTNAME

20. Click OK to save the ETL and close the window.

Setup and User Guide - Qlik Compose, May 2022 199

5 Data Warehouse projects

After Compose has finished populating the Data Warehouse, you can open the table in

Microsoft SQL Server Management Studio and verify that the new column has been added

with the correct data.

Updating custom ETLs

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

You can update custom ETLs using the Compose CLI. This functionality can be incorporated into a script to

easily update Custom ETLs.

Syntax:

composecli update_custom_etls --project name --infolder path

Where:

l

project is the name of the project with the custom ETLs you want to update

l

infolder is the full path to the folder containing the custom ETL files

Example:

composecli update_custom_etls --project my-project --infolder

c:\Compose\CustomETLs

The file names in the input folder must be identical to the custom ETL names in the specified project.

Otherwise, an error will occur. The file extension (for example, .txt) is not important, but the file must

be in SQL format.

ETLexecution sequence

The execution sequence of ETL scripts in Data Warehouse projects should be taken into consideration when

writing and ordering the scripts. A proper understanding of the ETL execution order is important for

preventing errors, such as those resulting from actions being performed on objects that do not yet exist.

ETL scripts are usually executed in the following order:

Custom Pre-Loading (Source of data: landing tables)

↓

Mappings (Source of data: landing table)

↓

Multi Table ETL (Source of data: staging tables)

Setup and User Guide - Qlik Compose, May 2022 200

5 Data Warehouse projects

Whereas it's possible to define a single table ETL in the Multi Table ETL script, the advantage of

defining it as a single table ETL script is that it will be able to run in parallel with other tables.

↓

Single Table ETL (Source of data: staging tables)

↓

Post Loading ETL (Source of data: data warehouse tables)

Within each of the above groups, the scripts are executed according to their numeric order (from lowest to

highest), which is set by the user-defined Sequence Number. The execution order of several scripts in a group

with the same sequence number will be random.

Managing tasks

a task contains the mappings between the columns in the landing zone tables and the columns in the logical

entities. The same mappings can be used by several tasks. You can create new tasks, duplicate tasks and edit

existing tasks as required.

The following options are available:

l

Adding and duplicating tasks (page 201)

l

Editing column mappings (page 202)

l

Creating and managing custom ETLs (page 197)

You must regenerate the task and then run a data warehouse task whenever the mappings are

modified or whenever custom ETLs are added or modified. Populating the data warehouse can

either be done manually as described in Controlling data warehouse tasks (page 196) or

automatically as described in Scheduling tasks (page 264).

If you have already run the data mart tasks, then you also need to regenerate the data mart ETLs

and run the tasks again as described in Creating and managing data marts (page 225).

Adding and duplicating tasks

As the default tasks are generated automatically, there is usually no reason to manually create or duplicate a

task. An exception to this is if you import your model from ERwin without first defining global mappings. In

such a case, you will need to manually add the task and create the mappings.

For more information on global mappings, see Managing global mappings (page 160).

One possible reason to duplicate a task is if your model contains different types of tables and you want to

manage them in separate ETLs.

Setup and User Guide - Qlik Compose, May 2022 201

5 Data Warehouse projects

Adding a new task

To add a new task:

1. Click the Manage button at the bottom left of the Data Warehouse panel. The Manage Data

Warehouse Tasks window opens.

2. Click the New toolbar button. The Add New task window opens.

3. Specify a name for the task and then click OK.

Task names cannot contain the following characters: /\,&#%$@=^*+"'`~?<>:;[]{} as well as all

non-printable characters (below 0x20). The task name can contain a single dot, but it cannot

be the first or last character.

4. Select the task name in the left pane and continue from Editing column mappings (page 202).

Duplicating a task

To duplicate an existing task:

1. Click the Manage button at the bottom left of the Data Warehouse panel. The Manage Data

Warehouse Tasks window opens.

2. Select the task you want to duplicate and then click the Duplicate toolbar button. The Duplicate

window opens.

3. Specify a Name for the new task.

4. Select a Landing Zone.

5. Optionally change the default Schema.

6. Select one of the available task types.

Do not select a task type that conflicts with you Replicate task. For instance, do not select

Change Tables Only if your Replicate task is Full Load only.

7. Click OK.

8. Select the task name in the left pane and continue from Editing column mappings (page 202).

Editing column mappings

The mappings show the current mapping between the landing zone tables and the logical entities. By default,

the columns names and data in the source tables and the logical entities will be identical. However, you can

manually change the mappings according to your needs, either by simply mapping a source column to a

different data warehouse column and/or by using an expression.

Setup and User Guide - Qlik Compose, May 2022 202

5 Data Warehouse projects

To edit column mappings:

1. Click the Manage button in the Data Warehouse panel.

2. In the Manage Data Warehouse Tasks window, select the Mappings tab. Each of the logical entities

has a corresponding mapping name.

3. In the Mappings column, click the mapping that you want to edit. The Edit Mapping: Name window

opens.

4. Edit the mapping as described below.

Mapping a landing zone table column to a staging area table column

The mapping procedure differs depending on whether you are in Standard View or Compact View.

For information on changing the view, see Changing the view (page 204).

In Standard View:

1. Hover the mouse cursor over the source column name as shown in the image below. A gray dot

appears to the right of the column name.

2. Drag the mouse cursor from the gray dot to the desired column in the logical entity.

3. When the dotted line turns green (as shown below), release your mouse button.

Note that if the dotted line turns red (instead of a green), you will not be able to map the source

column with the desired data warehouse column. A red dotted line indicates that the source and data

warehouse column data types are incompatible with each other.

In Compact View:

1. Switch to Compact View as described in Change the view.

2. Drag the source column to the cell located to the left of the target data warehouse column.

Setup and User Guide - Qlik Compose, May 2022 203

5 Data Warehouse projects

Auto-generating mapping

Click the Auto-Map toolbar button.

Removing all mappings

Click the Reset toolbar button.

Changing the view

To change the view, click the Change View toolbar button.

Changing to a more compact view is recommended for sources tables that have numerous columns. In

compact view, the table columns are organized in rows (instead of a single list), making it easier to locate

source columns and map them to the desired data warehouse columns. You can also use the search box to

filter out all columns that do not match the search string.

For information on creating mappings in Compact view, see Map a column in a landing zone table to a column

in a staging area table.

Selecting a different source database

Select a database from the Landing Zone Database drop-down list on the left of the window.

Selecting a different source schema

Select a schema from the Schema drop-down list on the left of the window.

Changing the entity type

Select Table, View or Query on the left of the window. If you choose the Query option, see also Define a

custom query.

Defining a custom query

When the entity type is set to Query, you can set a custom select query instead of using the existing source

tables/views.

To set a query:

1. Click the Set Query button. The Edit Mapping Select Query: <Mapping Name> window opens.

2. Hover the mouse cursor over a table and/or a column and then click the arrow to the right of the

highlighted table/column to add it to the Query.

3. Use the Select button at the top of the window to add select statements to your query.

Optionally use the Undo, Redo and Clear buttons as required.

4. Click OK to save your settings and close the window.

The query results will be displayed on the left of the Edit Mappings: <Name> window.

Selecting a different table

Select a table from the Table Name drop-down list on the left of the window.

Seeing the data of a selected table

Select a source table and then click the Show Source Data button on the left of the window.

Setup and User Guide - Qlik Compose, May 2022 204

5 Data Warehouse projects

Creating a table-level transformation (Filter)

1. Click the Filter toolbar button in the Edit Mappings:Name window. The Expression Builder opens.

2. Continue from Opening the expression builder (page 182).

When creating a filter for a table, the expression should return 1 for data that you want to

include and 0 for data that you want to exclude.

The filter will be applied after any Data Cleansing rules that are defined.

Updates to records excluded by a filter (even for records previously included by the filter) are

not processed while the records are filtered out. Updating of filtered out records would only

resume if the record(s) once again met the filter-in condition, but any changes made while

the records were filtered out would be lost.

Creating a column-level transformation

1. Hover the mouse cursor over the data warehouse column for which you want to create a

transformation and then click the fx button that appears to its right. The Expression Builder opens.

2. Continue from Opening the expression builder (page 182).

Adding, deleting and renaming mappings

You can add, rename and delete mappings as required. For example, if you want one of the logical entities to

contain columns from several tables in the landing zone, then you need to add a new mapping for each of the

landing zone tables.

When mapping "From Date" columns in the Landing Zone to the "FD" Staging column, make sure

that the dates in the Landing Zone columns are not earlier than the "Lowest Date" set in the Project

Settings. Otherwise, any data with a "From Date" earlier than the "Lowest Date" will be ignored.

If some of the dates in the Landing Zone column are earlier than the "Lowest Date" and cannot be

changed in the source, either change the "Lowest Date" set in the Project Settings - or - use a

transformation in Replicate to convert the source dates to dates that are within"Lowest Date" to

"Highest Date" range defined in your project.

To add, delete, and rename mappings:

1. Click the Manage button in the Data Warehouse panel. The Manage Data Warehouse Tasks window

opens.

2. In the left pane, select the task you want to add, delete, or rename.

3. Select the Mappings tab.

Setup and User Guide - Qlik Compose, May 2022 205

5 Data Warehouse projects

Adding a new mapping

To add a new mapping:

1. In the Logical Entities column, select the logical entity that you want to map.

2. Click the New button above the Logical Entities column. The New Mapping window opens.

3. Optionally change the default mapping name.

4. Click OK to save the mapping.

5. Enable the mapping.

Deleting a mapping

To delete a mapping:

1. In the Mappings column, hover the mouse cursor over the mapping you want to delete.

2. Click the Delete (x) button that appears to its right.

3. Click OK when prompted to confirm the deletion.

Renaming a mapping

To rename a mapping:

1. In the Mappings column, hover the mouse cursor over the mapping you want to rename.

2. Click the Rename (A) button that appears to its right. The Rename window opens.

3. Specify a new name for the mapping and then click OK.

Handling duplicate business keys

When two or more records in the data source have the same business key, you can select the Handle

Duplicates check box to prevent an error from occurring when the data warehouse task is run. When this

check box is selected, Compose will only add one of the records to the data warehouse.

Since Compose randomly chooses which record to add to the data warehouse, you may want to run

a data warehouse task first to see if there are any duplicate record errors. In the event that there

are, you can then modify the data source to remove records that have the same business key.

You should also select the Handle Duplicates check box in the following situations:

l

The Data Warehouse task type is either Full Load and Change Tables or Change Tables Only.

This is because the Change Tables may contain two records with the same business key: The old

record and the updated record. When the Handle Duplicates check box is selected, the updated

record will always be inserted/updated to/in the data warehouse.

l

When a single table in the data warehouse is derived from multiple landing zone tables, the same

business key will be set for each of the mappings. To prevent an error for occurring, you need to select

the Handle Duplicates check box.

Setup and User Guide - Qlik Compose, May 2022 206

5 Data Warehouse projects

Handling null updates

The default handling of null updates is set in the Advanced tab of the Settings - <ETL_Set_Name> window.

For each mapping, you can override the specified default.

To do this:

1. In the Manage Data Warehouse Tasks window, select the desired mapping.

2. Click the Null Updates toolbar button.

3. Select one of the available options. For a description of the options, see Handling Null Updates.

Using lookup tables

Lookup tables are useful for replacing source data with the actual data that you want to appear in the data

warehouse. For example, a lookup table could be used to replace a zip code with a full address or, conversely,

to replace a full address with a zip code.

To link a lookup table column to a logical entity column:

1. Click the link to the desired task in the Data Warehouse panel. The Manage Data Warehouse Tasks

window opens.

2. In the Mappings column, click the mapping for the logical entity containing the result column (with the

data that you want to replace). The Edit Mapping - Name window opens.

3. Hover the mouse cursor over the relevant data warehouse column and then click the Lookup button

that appears to the right of the column name. The Select Lookup Table window opens.

a. From the Database drop-down list, select the database containing the lookup table.

The database must reside in your data warehouse.

b. From the Schema drop-down list, select the schema containing your source lookup tables.

c. Select either Table or View according to the lookup table type.

d. From the Table drop-down list, select the lookup table.

The right side of the Select Lookup Table window displays the lookup table columns and their

data types. To view the data in the lookup table, click the Show Lookup Data button.

e. After you have selected the lookup table, click OK.

4. After selecting the lookup table, the Lookup Transformations - Table Name.Column Name window

opens. The window is divided into the following panes:

l

Upper pane: The upper part of the right pane (Condition) displays the condition expression,

which stipulates the condition(s) for performing the lookup.

l

Lower pane: The lower part of the right pane (Result Column) displays the column result

expression, which stipulates what data to replace in the target column.

5. To change the lookup table, click the Change Lookup Table button above the lookup table columns

and then perform steps a. to d. above.

6. To view the lookup table or landing table data, click the Show Lookup Data or Show Landing Data

buttons respectively.

Setup and User Guide - Qlik Compose, May 2022 207

5 Data Warehouse projects

7. To specify condition(s) for performing the lookup, click the Create Expression button (which changes

to Edit Expression after an expression has been created) above the Condition expression. The

Condition Expression - Column Name window opens.

You can create an expression using the landing and lookup table columns on the left.

For an example, see Lookup example (page 209). For information on creating expressions, see Creating

expressions (page 181).

8. To specify what data to replace or add if the lookup conditions are met, click the Create Expression

button (which changes to Edit Expression after an expression has been created) above the Result

Column expression. The Result Expression - Column Name window opens.

You can create an expression using the landing and lookup table columns on the left.

For an example, see Lookup example (page 209). For information on creating expressions, see Creating

expressions (page 181).

9. To preview the results, click the Preview Results button.

10. Click OK to save your settings and close the Lookup Transformations - Table Name.Column Name

window.

Using lookup tables that do not have a task for CDC mapping

When the Store Changes option is enabled in the Replicate task, Replicate creates Change Tables in the

landing zone. These tables contain only the changes to the original data. The Compose task CDC task reads

the changes from Change Tables and applies them to the target tables. However, if the landing zone contains

dedicated lookup tables (i.e. tables that are not associated with any Compose task), Compose will not be able

to apply changes to these tables.

There are two ways of handling such a scenario, both of which are described below.

Method 1

Define another Replicate task with the Apply Changes replication option enabled.

Method 2

1. Discover the landing site and add all the lookup tables to the Compose model without any relation

to/from other entities.

2. Either, define lookups from the data warehouse hub tables to the newly added entities.

Create relationships from the data warehouse hub tables to the newly added entities.

Creating relationships may not be a viable option when the lookup tables are complex.

3. Define a new data warehouse Change Tables Only task that updates the lookup tables.

4. Ensure that the new task runs before the data warehouse task.

The advantage of this method is twofold: a.) All the tables used in the mappings are managed by Compose,

and b.) Only one Replicate task needs to be defined (which also means that the database transaction logs are

read only once). The disadvantage is that you need to ensure that the task that updates the lookup entities

always runs before any data warehouse task.

Setup and User Guide - Qlik Compose, May 2022 208

5 Data Warehouse projects

Lookup example

The following example shows how a lookup table is used to concatenate a Dutch translation of the category

name (located in the lookup table) to the original category name located in the landing table.

The lookup could be defined using the following expressions:

1. Condition expression: ${Lookup.CategoryID}=${Landing.CategoryID}

Meaning: Perform the lookup only if the Category ID in the landing table and the lookup table are the

same.

2. Result column expression: ${Lookup.CategoryName} + ’is’ + ${Landing.CategoryName}

Meaning: Add the data in the CategoryName column in the lookup table to the data in the

CategoryName column in the landing table (separated by the word "is").

Example: 

Assuming the result column name is "Split Name", clicking the Preview Results button would display the

following table:

Split Name

Category Name

(Lookup)

Category Name

(Landing)

Category ID

(Lookup)

Category ID

(Landing)

dranken is Beverages dranken Beverages 1 1

Specerijen is

Condiments

Specerijen Condiments 2 2

Gebak is Confectionary Gebak Confectionary 3 3

Zuivelproducten is Dairy

Products

Zuivelproducten Dairy Products 4 4

Grains/Granen is

Grains/Cereal

Grains/Granen Grains/Cereal 5 5

Vlees/Gevolgete is

Meat/Poultry

Vlees/Gevolgete Meat/Poultry 6 6

Example table output

Dropping and recreating tables

You can drop and recreate tables in your data warehouse as required. If you change the model after the data

warehouse tables have already been created and loaded with data, you should adjust the data warehouse to

reflect the modified model (as described in Validating the data warehouse (page 222)). Some changes however

cannot be resolved by adjusting the data warehouse. In such cases, you can either revert the model to its pre-

modified state or drop and (optionally) recreate the data warehouse tables.

Note that dropping and recreating tables will delete all of the data in the tables and should only be performed

in lieu of a better option.

Setup and User Guide - Qlik Compose, May 2022 209

5 Data Warehouse projects

In some scenarios, you need to edit the CREATE table statements before they are run. This can be

done using the Generate DDL scripts but do not run them in Project settings (page 40). For example,

if your data warehouse tables contain partitions, you will need to edit the script to maintain the

partitions.

To drop and recreate tables:

1. In the Data Warehouse panel, select the Drop and Recreate Tables item from the menu in the top

right corner. The Drop and Recreate Tables window opens.

2. You can select to drop and/or recreate one or more of the following tables:

l

Data Warehouse & Data Marts - The data warehouse tables are derived from the model

whereas the data mart tables are derived from the data warehouse tables.

l

Logging - These tables are generated when the task runs and contain logging information. By

default, these tables are prefixed with the string "TLOG".

l

Intermediate - These tables are temporary tables that are created when the task runs. By

default, these tables are prefixed with the string "TTMP".

Intermediate tables are created dynamically and therefore cannot be recreated.

l

Error Mart - These are the data mart exception tables. Data that is rejected by data quality

rules will be copied to tables in the specified error mart schema. See also Error Mart.

l

Archive Tables - These are the tables that are created when the option to archive Change

Tables after the changes have been applied (to the data warehouse tables) is selected. For

more information, see Defining landing zones (page 140)

3. Click OK to perform the drop and/or recreate operation.

Data profiling

Data profiling is an analysis of the candidate data sources for a data warehouse to clarify the structure,

content, relationships and derivation rules of the data. In short, data profiling helps you understand your data

and model it correctly.

Qlik Compose enables you to profile the data in the landing zone tables before it is loaded into the data

warehouse. If you discover a problem with certain data, then you can either manually adjust the source tables

or create a rule for handling the data in question.

To profile the data:

1. Click the Manage button at the bottom of the Data Warehouse panel.

2. In the Manage Data Warehouse Tasks window, click the link in the Mapping column for the table you

want to profile.

3. In the Edit Mappings - <Name> window, click the Data Profiler toolbar button. The Profile <Table

Name> (Landing Zone) window opens. The following columns are displayed:

Setup and User Guide - Qlik Compose, May 2022 210

5 Data Warehouse projects

l

Column Name - The name of the table column

l

Nulls - The number of null values in the column

l

Count - The number of rows in the column.

l

Count Distinct - The number of unique rows in the column.

l

Duplicates - The number of duplicate values in the column.

Note that although Compose calculates the number of duplicate values by subtracting Count

Distinct from Count, the actual number of records displayed when you click the Duplicates

number will be higher. This is because Compose has no way of knowing which of the records

that share the same column value are legitimate duplicates (if any). It therefore displays all

records that share the same value so you can decide which of them to delete (if any).

For example, in the Employees table, there may be several employees that live in London (the

City column). Therefore duplicates of "London" are perfectly acceptable. However, two

employees with the same phone number and a different address, for example, may indicate

that the phone number in one of the records was entered incorrectly.

Duplicate values are quite common and usually do not indicate a problem. Where this feature is

particularly useful however, is for detecting duplicate Primary Key candidate columns.

l

Data Type - The column data type

l

Max - The highest data value

l

Max Length - The longest data value

l

Min - The lowest data value

l

Min Length - The shortest data value

4. For more information about a value, click the link in the column. A window opens showing the record

(s) containing the value. To add a Data Quality rule, click the Data Quality button and continue as

described in Defining and managing data quality rules (page 211).

5. To only show columns that are mapped to a logical entity column, select the Only show mapped

columns check box.

6. To change the number or rows sampled, select a different value from the Rows to sample drop-down

list. Note that the table may contain less rows than the selected value. The Sampled records value is

the actual number of rows sampled.

7. To see all the table data, click the Show Data button.

The table's Full Load data will always be shown, even for a mapping in a Change Processing

(CDC) task.

8. To recalculate the data, click the Recalculate button. This is useful if the data in the landing zone

tables is being constantly updated (for example, due to a Replicate Change Processing task).

9. To search for a particular value, start typing the value in the Search box. Only values that match the

search term will be shown.

Defining and managing data quality rules

There are many definitions of data quality but data is generally considered high quality if, "they are fit for their

intended uses in operations, decision making and planning." (Tom Redman<Redman, T.C. (2008). With

Compose, the data must be "fit" for use in a data mart.

Setup and User Guide - Qlik Compose, May 2022 211

5 Data Warehouse projects

Compose provides two ways of ensuring data quality: Data validation and data cleansing. As opposed to data

validation which usually results in data being rejected, data cleansing provides a means of replacing,

modifying, or deleting incomplete, incorrect or inaccurate data.

Data that is rejected by a rule will be copied to Error Mart tables in the Error Mart schema defined in the

Landing Zone database settings.

Details about rejected data can be viewed in the monitor's Error Mart tab. For more information, see Viewing

information in the monitor (page 260).

Defining data cleansing rules

Qlik Compose enables you to define data cleansing rules for each of a table’s columns. Each rule consists of a

data validation condition and a cleansing process that is performed as required (i.e. if the data is not valid).

Data Cleansing rules will be applied before any filters that are defined.

To add a rule:

1. Click the Manage button at the bottom of the Data Warehouse panel.

2. In the Manage Data Warehouse Tasks window, click the link in the Mapping column for the relevant

table.

3. In the Edit Mappings - <Name> window, click the Data Quality toolbar button. The Data Quality

Rules - <Table Name> window opens.

4. To add a new rule, click the New toolbar button. A row is added to the rules table.

5. In the Name column, specify a name for the rule.

6. From the drop-down list in the Column column, select the column to which the rule will be applied.

7. Hover the mouse-cursor over the Condition column and then click the fx button that appears on the

right.

8. In the Edit Condition Rule window, create a condition (using an expression) that the data in the

column must meet in order to be considered valid. For more information on creating expressions, see

Opening the expression builder (page 182).

See also Simple Example Rule (page 213) below.

9. From the drop-down list in the If Condition is False column, select Cleanse Silently.

10. Hover the mouse-cursor over the Correction column and then click the fx button that appears on the

right.

11. In the Edit Correction Rule window, create an expression to cleanse the data. For more information

on creating expressions, see Opening the expression builder (page 182).

See also Simple Example Rule (page 213) below.

12. In the Description column, enter a description for the rule.

13. In the Enabled column, select or clear the check box to enable (the default) or disable the rule

respectively.

Setup and User Guide - Qlik Compose, May 2022 212

5 Data Warehouse projects

Simple Example Rule

The condition expression on the left stipulates that the product ID number must be less than 100. If it is

greater than or equal to 100, the data will be corrected using the expression on the right.

${ProductID} < 100 ${ProductID} - 100

Defining data validation rules

Qlik Compose enables you to define data validation rules that are applied to the data before it is loaded into

the data warehouse. In addition to defining rules, you can also define what action should be taken when data

is rejected/accepted by Compose.

To add a rule:

1. Click the Manage button at the bottom of the Data Warehouse panel.

2. In the Manage Data Warehouse Tasks window, click the link in the Mapping column for the table you

want to profile.

3. In the Edit Mappings - <Name> window, click the Data Quality toolbar button. The Data Quality

Rules - <Table Name> window opens.

The default rule rejects primary keys that have a null value and reports the rows.

4. To add a new rule, click the New toolbar button. A row is added to the rules table.

5. In the Name column, specify a name for the rule.

6. Hover the mouse-cursor over the Rule column and then click the fx button that appears on the right.

7. In the Edit Data Quality Rule window create a rule using an expression. For more information on

creating expressions, see Opening the expression builder (page 182).

See also Simple Example Rule (page 213) below.

8. From the drop-down list in the Error Action column, select one of the following actions (performed

when the data does not meet the rule conditions):

l

Reject and report - Reject the data and send a report

l

Reject silently - Reject the data without sending a report

l

Reject and abort - Reject the data and abort the data warehouse task

l

Accept and report - Accept the data and send a report

l

When the "report" option is selected, the row is reported to the

<landing_table_

name>__ex

table in the data warehouse error mart.

l

When there are multiple data validation rules, Compose will stop evaluating the data

after the first error (and report only that error). Once the error is fixed, additional data

evaluation errors may be reported for the remaining rules, each time the data is

loaded.

9. In the Description column, enter a description for the rule.

10. In the Enabled column, select or clear the check box to enable (the default) or disable the rule

respectively.

Setup and User Guide - Qlik Compose, May 2022 213

5 Data Warehouse projects

A rule that is defined to reject or accept a non-null value (e.g. 2) in a given column will also

reject/accept NULL values that appear in the same column, but in different records. To

prevent this from happening, add the following condition to the rule: "and column value is

not null"

Example: LEN(${CName})<2 and (${CName} is not null)

Simple Example Rule

The following rule stipulates that the number of units in stock must be greater than 1.

${UnitsInStock}>1

Managing Data Quality rules

The following options are available for managing Data Quality rules.

Enabling/disabling a Data Quality rule

Select or clear the check box in the rule’s Enabled column.

Editing a Data Quality rule

Select the rule and edit it as described in Defining data cleansing rules (page 212) and Defining data validation

rules (page 213) respectively.

Deleting a Data Quality rule

Select the rule and then click the Delete button above the rules list. When prompted to confirm the deletion,

click Yes.

Searching for a Data Quality rule

Enter a search term in the Search box above the rules list.

Changing the order of Data Quality rules

The order of the rules is important since rules are applied in the order that they appear. For example, placing

Reject and abort rules first will prevent other rules from being applied if the data is rejected by the Reject

and abort rule.

To change the order of Data Quality rules, select the rule that you want to move and then use the arrows

above the rules list to change the position of the rule.

Viewing missing references

In some cases, incoming data is dependent on or refers to other data. If the referenced data cannot be loaded

for some reason, you can either decide to add the data manually or continue on the assumption that the data

will arrive before it is needed.

There are two ways you can view missing references in Compose. Either via the Monitor tab in the Manage

Data Warehouse Tasks window or by switching the console to Monitor view and selecting the Missing

References tab. The instructions below cover both of these methods.

Setup and User Guide - Qlik Compose, May 2022 214

5 Data Warehouse projects

To check for missing references in the Manage Data Warehouse Tasks window:

1. Click the Manage button in the lower left corner of the Data Warehouse panel.

2. Select the desired task in the left side of the Manage Data Warehouse Tasks window.

3. Switch to Monitor view by clicking the Monitor tab in the top right of the Manage Data Warehouse

Tasks window.

4. Click the View Missing References toolbar button. The Missing References - <task Name> window

opens.

The following information is displayed:

l

General information: The run number of the task, when it started and ended, the total number

of inserts and updates, and the number of reported rows (if any).

l

Missing references information:

l

Missing Records from Entity - The name of the entity with missing reference and the

number of missing references.

To see the missing record keys for the entity, click the number in parentheses to the

right of the entity name.

The Missing Record Keys for Entity - <Entity Name> window opens showing the list of

missing keys and the number of times each key is referenced per entity.

l

Referenced from Entity - The entities that are referencing the entity with missing

references.

l

Via Relationship - The name of the relationship in the Model.

5. To close the window, click Close.

To check for missing references in the Compose Monitor:

1. Switch the console to Monitor View.

2. Select the desired task.

3. Click the Missing References tab below the task list.

The following information is displayed:

l

General information: The run number of the task, when it started and ended, the total number

of inserts and updates, and the number of reported rows (if any).

l

Missing references information:

l

Missing Records from Entity - The name of the entity with missing reference and the

number of missing references.

To see the missing record keys for the entity, click the number in parentheses to the

right of the entity name.

The Missing Record Keys for Entity - <Entity Name> window opens showing the list of

missing keys and the number of times each key is referenced per entity.

l

Referenced from Entity - The entities that are referencing the entity with missing

references.

l

Via Relationship - The name of the relationship in the Model.

4. To close the window, click Close.

Missing references example

In the following example, Orders and Disputes both reference Customers.

Setup and User Guide - Qlik Compose, May 2022 215

5 Data Warehouse projects

Orders contains seven records pointing to Mr. Brown and one record pointing to Mr. Smith. Disputes contains

four records referencing Mr. Brown. Mr. Brown and Mr. Smith are "missing" from Customers.

The would be reflected as follows:

Missing Records from Entity Referenced from Entity Via Relationship

Customers (2) Orders (8) Customers

- Disputes (4) CustomerDisputes

Example table content

Clicking the number to the right of Customers (in the Missing Records from Entity column) would open the

following window:

Key Referenced from Entity Via Relationship

Mr. Brown Orders (7) Customers

- Disputes (4) CustomerDisputes

Mr. Smith Orders (1) Customers

Example table content

See also: How Compose handles missing references in the data warehouse (page 191).

Viewing and exporting task statements

You can view the task statements that were run during the data warehouse task. You can also export the task

statements to a CSV file for reviewing and sharing.

To view the task statements:

1. Click the Manage button at the bottom left of the DATA WAREHOUSE panel.

The Manage Tasks window open.

2. Click the Task Statements toolbar button.

3. The Task Statements - <Name> window opens in List View. Navigate through the commands using

the scroll bar or find specific commands using the Search box.

Click the Item View button and navigate through the commands using the navigation buttons at the

bottom of the Task Statements - <Name> window.

To jump to a specific command, type the command number in the Go To field at the bottom of the

window and then press [Enter].

Setup and User Guide - Qlik Compose, May 2022 216

5 Data Warehouse projects

To export the task statements to a CSV file:

1. In List View, click the Export to CSV File button located to the left of the search field.

2. A file named "<name>_ETL_Instructions.csv" will be saved to your default Downloads location or you

will be prompted to save it (according to your browser settings).

Modifying task settings

For each task, you can modify the settings according to your needs.

To open the Settings window:

1. Click the Manage button in the bottom left of the DATA WAREHOUSE panel.

2. Select a task in the left panel.

3. Click the Settings toolbar button.

A window opens displaying the following tabs: General, Advanced, and Consolidation.

General Tab

In the General tab, the following settings are available:

l

Log level: Select the log level granularity, which can be any of the following:

l

INFO (default) - Logs informational messages that highlight the progress of the ETL process at a

coarse-grained level.

l

VERBOSE - Logs fine-grained informational events that are most useful to debug the ETL

process.

l

TRACE - Logs finer-grained informational events than the VERBOSE level.

The log levels VERBOSE and TRACE impact performance. Therefore, you should only select them for

troubleshooting if advised by Qlik Support.

l

Default History Resolution: Choose the granularity of the "From Date" column value when a new

history record is inserted:

l

Minutes to update with the date and time. This is the default. When this option is selected, a

new record will be inserted each time the data is updated.

l

Days to update with the date only. When this option is selected, only one record (the most

recently updated) will be inserted at the end of the day.

These settings will be applied, regardless of the original source column (when mapped) or

Change Table [header__] timestamp column (when not mapped) granularity. So, for

instance, if a source column with date and time granularity is mapped to the "From Date"

column and Days is selected, then only one record (the most recently updated) will be

inserted at the end of the day.

Setup and User Guide - Qlik Compose, May 2022 217

5 Data Warehouse projects

l

When updating a non-null data warehouse column with a null value:

l

Do not change the target value: Select this to keep values unchanged between two mappings

for the same record. For instance, if the same record exists in two different source tables (A and

B), but the record in Table A has a null value for data that is present in Table B (e.g. ZIP Code).

In this case, if the record in Table A arrives after the record in Table B, the target value will be

set to null. Selecting this option will prevent such an occurrence.

When creating a new project, the default behavior is to write NULL instead of keeping

the values unchanged between the two mappings.

l

Set the target value to null: Select this if you want the source and target values to

correspond. This can be useful, for example, when a person moves address and one of the

column values (e.g. "State") changes to null.

When ingesting changes from an Oracle source, this option requires full supplemental

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

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

Advanced Tab

In the Advanced tab, the following settings are available:

l

Sequential Processing: Select this option if you want all the data warehouse tasks to run sequentially,

even if they can be run in parallel. This may be useful for debugging or profiling, but it may also affect

performance.

l

Maximum number of database connections: Enter the maximum number of connections allowed.

The default size is 10.

For more information, see Determining the required number of database connections (page 24).

l

JVM memory settings: Edit the memory for the java virtual machine (JVM) if you experience

performance issues. Xms is the minimum memory; Xmx is the maximum memory. The JVM starts

running with the Xms value and can use up to the Xmx value.

Only the following characters are supported (shown as a regular expression):

/^[-a-zA-Z0-9:]*$/

l

Position in default workflow: Select where you want the data warehouse tasks to appear in the

default workflow. For more information on workflows, see Workflows (page 268).

l

Optimize for initial load: Optimizes initial load in certain cases. Only select this option if the source

tables do not reference missing records, use lookups, map different source records to the same record,

do not contain Type 1 self-references, or contain historical records. Note also that when this option is

selected, the following features are not supported:

l

Data quality rules

l

Derived attributes

Setup and User Guide - Qlik Compose, May 2022 218

5 Data Warehouse projects

l

Consolidation of uniform sources (see Consolidation below)

l

The Handle duplicates option

In the event that the task is used for incremental loading (using query-based change processing), clear

the check box after the initial load task completes and regenerate the task.

l

Write task statement duration to the TLOG_PROCLOG table in the data warehouse: This option is

useful for troubleshooting performance issues with ETL processes as it records the duration of each

task statement in a special table (named TLOG_PROCLOG) in the data warehouse. You can then use

this information to locate task statements with abnormal duration times and modify them accordingly.

l

Do not create indexes for data warehouse tables: During the task, Compose creates an internal

index for each of the Data Warehouse tables (for query optimization). When running several

consecutive tasks (e.g. via a workflow) with a large volume of tables, this process can be extremely

time-consuming. In such a scenario, best practice is to select the check box for each of the tasks,

except the last one.

l

Do not truncate staging tables: Select this option if you want the ETL process to preserve the staging

tables. Only use for debugging.

l

Stop processing after populating the staging tables: Select if you do not want to proceed to

populating the warehouse. Only use for debugging.

l

Do not drop temporary tables: Select this option if you want to keep the temporary tables created

during the ETL process. Only use for debugging.

Consolidation Tab

Requires Compose August 2021 Service Release 03 or later.

When the Consolidate uniform sources option is enabled, Compose will read from the selected data sources

and write the data to one consolidated entity. This is especially useful if your source data is managed across

several databases with the same structure, as instead of having to define multiple data warehouse tasks (one

for each source), you only need to define a single task that consolidates the data from the selected data

sources.

Consolidation tab showing selected data sources

Setup and User Guide - Qlik Compose, May 2022 219

5 Data Warehouse projects

Editing the list of data sources requires you to regenerated the task.

The list of selectable data sources reflects the list of Source Databases that appears in the

Databases panel in Designer view.

To facilitate downstream processing, you might want to add a record identifier column (for example,

SourceID) to the primary key of all your entities. However, if one entity references another (for

example, Orders → Customers), a naming conflict will arise as the new column (SourceID) will then

appear in the referencing entity (Orders) twice. To prevent such conflicts from occurring, you should

add the column to each entity with a unique prefix derived from the entity name. So, continuing with

our Orders → Customers relationship example, the column name in the Orders entity should be

orders_SourceID while the column name in the Customers entity should be customers_SourceID.

Prerequisites

l

The structure of the tables in the selected sources must be identical.

l

Source type can be Table or View, but not Query.

The source data does not have to reside in tables only or in views only; it can be ingested

from a combination of views and tables. For example, the source data might be ingested

from tables A, B, and C in Landing 1, and views A, B, and C in Landing 2.

See also: Editing column mappings (page 202).

Limitations and considerations

l

The Optimize for initial load option is not supported with consolidation.

l

A selected data source cannot contain an asterisk (*) in its specified schema name (asterisks in schema

names are supported with Microsoft SQL Server only).

See also: Using Microsoft SQL Server as a source (page 149)

l

If you have existing Full Load and Change Tables (CDC) tasks, setting the consolidation settings for the

Full Load task will not automatically set the consolidation settings for the Change Tables task as well.

You need to do this manually.

See also:Adding and duplicating tasks (page 201) and Creating a Change Processing task (page 194)

l

Uniform consolidation settings will not be included in task settings that are exported to a CSV file.

See also: Migrating objects as CSV files (page 49)

l

Lineage andproject documentation will not reflect all of the selected sources.

See also: Exporting project documentation (page 99) and Lineage and impact analysis (page 177).

l

Custom ETLs (Pre Loading ETL, Multi Table ETL, Single Table ETL, and Post Loading ETL) will run only

once, regardless of how many sources are selected.

See also: Creating and managing custom ETLs (page 197).

l

Generating the ETLs will only validate the Landing Zone database(s) defined in the mappings, and not

all of the data sources selected in the Consolidation tab.

Setup and User Guide - Qlik Compose, May 2022 220

5 Data Warehouse projects

l

Error marts will be created for each Landing Zone database.



To see the number of reported rows in each error mart:

1. Open the Manage Data Warehouse Tasks window, and select the consolidation task in the left

pane.

2. Select the Monitor tab, and click the Total Reported Rows number.

Alternatively:

1. Switch to the main Monitor view and select the consolidation task.

2. In the Progress Status tab (below the tasks list), click the Total Reported Rows number.



The Error Mart - <task-name> window opens.



Error Mart window showing the number of rows reported for each error mart

For more information on error marts, see Viewing information in the monitor (page 260).

Monitoring tasks with consolidates sources

The monitor shows the sum total of all the records (for example, the total number of INSERTs) from all of the

selected sources.

Monitor showing a consolidation task with the total number of rows inserted from all data sources

Setup and User Guide - Qlik Compose, May 2022 221

5 Data Warehouse projects

Validating the data warehouse

Data warehouse validation should be performed each time the model is edited (after the data warehouse has

already been created). Validating the data warehouse allows you automatically resolve any differences

between the model and the data warehouse.

For a data warehouse to be considered valid, the tables defined in the data warehouse need to be identical to

the physical tables in terms of metadata. Depending on the change, this may require adjusting the physical

tables or dropping and recreating them (via Compose).

If the data warehouse is not valid, any tasks that you attempt to run will fail.

Changes to Distribution Keys cannot be validated (or adjusted). Such changes need to be applied

manually to the Data Warehouse tables.

Sometimes, however, the differences between the model and the data warehouse cannot be resolved

automatically. In such cases, you need to drop and recreate the tables as described in Dropping and recreating

tables (page 209).

Setup and User Guide - Qlik Compose, May 2022 222

5 Data Warehouse projects

To validate the data warehouse:

1. Click the Validate button at the bottom right of the Data Warehouse panel. The Validating the Data

Warehouse progress window opens.

If any differences are detected, the following message will be displayed: The data warehouse is different

from the model.

2. Click Close. The Model and Data Warehouse Comparison Report window opens.

3. Review the report and then click Adjust Automatically to resolve the differences automatically or

Generate Adjust Script to generate a script with the adjust commands.

l

The Adjust Automatically button will be disabled either if the Generate DDL scripts

but do not run them option is selected or if Compose is unable to automatically

adjust the data warehouse. In such cases, you should click Generate Adjust Script as

described below.

l

Due to Google Cloud BigQuery limitations, if Compose is unable to automatically

adjust the data warehouse, then the generated script may be not valid either.

Consequently, users should review the script carefully and adjust it manually (if

required) before running.

l

If you clicked Adjust Automatically, the Adjust Data Warehouse progress window opens.

When the "The data warehouse was adjusted successfully." message is displayed, you can close

the window. Note that adjusting the data warehouse may require you to update the data mart.

In such a case, an appropriate message will be displayed for each of the data marts that require

updating.

Cases where Compose is unable to automatically adjust the data warehouse are as

follows:

l

A data type change that is not supported by the database or a data type

change that may result in data loss.

l

A change in an entity’s business key or distribution key.

l

An attribute’s history type is Type 2 and the satellite table number in the

attribute’s settings has changed.

l

If you clicked Generate Adjust Script, the Generate DDL Scripts window opens showing the

progress of the script generation.

The generated scripts will be saved to:

<product_dir>\data\projects\<project_name>\ddl-scripts

Once the script(s) have been generated, you can close the Generate DDL Scripts window.

After you close the Generate DDL Scripts window, the DDL Script Files window opens

automatically displaying the generated scripts. The DDL Script Files provides a read-only view

that allows you to review the scripts and download them.

The scripts need to be executed directly in your data warehouse. Make sure that any

modifications that you make to the scripts are done prior to executing them.

Setup and User Guide - Qlik Compose, May 2022 223

5 Data Warehouse projects

When you run the adjust scripts, backup tables are created from the existing tables.

The backup table names are appended with an "_old" suffix and must be deleted

manually after the script completes.

Search for "TODO" in the script to locate the part of the script that needs modifying.

Clearing the data warehouse metadata cache

To improve performance when reading from the Landing Zone or from the Data Warehouse tables, Compose

caches the metadata from both the Landing Zone and the Data Warehouse tables. However, synchronization

issues may sometimes occur if the metadata structure of the Landing Zone or the Data Warehouse tables is

altered outside of the Compose project.

If you aware of external changes to the metadata or if you notice any data synchronization anomalies,

Compose enables you to clear the metadata cache, either using the UI or using the CLI.

Clearing the data warehouse metadata cache with the web console

To clear the metadata cache with the web console:

1. In the Data Warehouse panel, select Clear Metadata Cache from the menu in the top right corner.

A progress window opens.

2. After the metadata cache has been cleared, click Close to exit the progress window.

For information on clearing the Landing Zone metadata cache, see Clearing the Landing Zone metadata cache

(page 158).

Clearing the metadata cache with the CLI

The storage value for the --type parameter described below refers to the data warehouse

metadata cache.

You can also clear the metadata cache using the CLI.

Command syntax:

ComposeCli.exe clear_cache --project

project_name

[--type landing|storage] [--landing_zone

source_name]

Parameters

Parameter Description

--project The name of the project.

Setup and User Guide - Qlik Compose, May 2022 224

5 Data Warehouse projects

Parameter Description

--type Which type of metadata cache to clear. Possible values are:

l

landing

l

storage



If --type

landing

and you want to clear a specific landing zone,

you must set the --landing_zone parameter as well. To clear the

metadata cache in all landing zones, specify --type

landing

and

omit the --landing_zone parameter.

--landing_zone the name of the landing zone when --type landing_zone

Example

ComposeCli.exe clear_cache --project MyProject --type landing --landing_zone MySource1

5.8  Creating and managing data marts

This section explains how to create data marts from your data warehouse tables.

In this section:

l

Adding star schemas and dimensions (page 234)

l

Displaying data in a pivot table (page 231)

l

Managing data marts (page 234)

l

Creating and managing custom ETLs (page 250)

l

Viewing and exporting task statements (page 252)

l

Validating and adjusting the data mart (page 252)

l

Modifying data mart settings (page 255)

l

The "Obsolete" indicator (page 257)

Adding data marts and star schemas

This topic explains how to create and manage data marts and star schemas in Qlik Compose. Since a data

mart is essentially a subset of the data warehouse, you can create any number of data marts according to

your BI needs. You can also create multiple star schemas for a single data mart. Star schemas allow you to

reuse existing dimension tables within the same data mart, thereby saving space in the data warehouse while

at the same time improving query performance. For example, you could create one star schema with an Order

Details fact table and Customers and Products dimensions and another star schema with the same

dimensions but a different fact type (or the same fact type, but different dimensions). This also allows you to

generate BI reports using different facts that share the same dimensions. Additionally, in a star schema,

dimensions are linked with each other through one join path intersecting the fact table, facilitating accurate

and consistent query results.

Setup and User Guide - Qlik Compose, May 2022 225

5 Data Warehouse projects

l

If you edit an expression or a column lookup in a dimension, the changes will not be applied

to existing data. To apply such changes, you need to reload the data (which could take some

time, depending on the number of records and whether there are a lot of historical records).

l

Data warehouse tasks cannot run in parallel with data mart tasks.

A new data mart should be created in the following situations:

l

Setting up a Compose project for the first time.

l

To serve the needs of each individual business unit (different data marts can be used to obtain specific

information for various enterprise departments, such as accounting, marketing, sales, and so on).

To create a data mart with a star schema:

1. Click the New button located at the bottom of the Data Mart panel.

Click the Manage button and then click the New button located at the top of the Manage Data Marts

window. The New Data Mart window opens.

2. Optionally change the default name and provide a description.

Data mart names cannot contain the following characters: /\,&#%$@=^*+"'`~?<>:;[]{} as well

as all non-printable characters (below 0x20). The data mart name can contain a single dot,

but it cannot be the first or last character.

3. Make sure that the Start New Star Schema Wizard check box is selected (the default) and then click

OK. The New Star Schema wizard opens.

4. Provide a name and description (optional) for the star schema.

5. Select one of the available fact types:

l

Transactional - A star schema with a transactional fact table allows you to retrieve the desired

data, even if a dimension table contains multiple versions of the same record. To use an

example from the automotive industry, selecting "OrderDate" as the Transaction Date would

allow you to generate a report for the number of customers who bought cars in New York

between 2013 and 2016, even if a customer moved to a different city (which would also result in

a new record being added to the Customers dimension).

l

Aggregated - A star schema with an aggregated fact table allows you to make aggregate

calculations based on the fact table attributes. For instance, you could create an aggregated

fact that shows the total freight costs per shipping region and product category. Additionally,

the presence of a transaction date in the fact table makes it possible to retrieve the desired

data, even if a dimension contains multiple versions of the same record. To use an example

from the shipping industry, a shipper could use an aggregated fact to generate a report for the

total cost of shipping rice to Australia from 2015-2016.

l

State Oriented - A star schema with a state oriented fact supports Type 2 columns in the fact

table. This is useful in cases where the fact is not a singular event in time, but rather, consists of

multiple "states" or events that occur over time. Typical example of facts with multiple states

are insurance claims or flight reservations. There are also cases when the same entity is treated

Setup and User Guide - Qlik Compose, May 2022 226

5 Data Warehouse projects

as both a fact and a dimension - for example, Customers. In such cases, a report could be

generated that relates to the state of the fact, such as the time a claim was submitted to the

time it was approved.

6. Click Next.

7. In the Facts screen, choose one fact for the star schema and then click Next. The Dimensions screen is

displayed. The left pane lists the dimensions that can be selected while the right pane displays a

diagram of the star schema with the selected dimensions. You can view a dimension’s lineage by

selecting the desired dimension and then clicking the Lineage button. For more information on

lineage, see Lineage and impact analysis (page 177).

The left pane of the Dimensions screen contains the following areas:

l

Existing Dimensions - Lists the dimensions that already exist in your data mart. Note that only

dimensions that are relevant to the selected fact table will be displayed.

l

Create New Dimensions - Lists all of the dimensions that can be added to the star schema.

l

Date Dimensions - Lists all of the Date dimensions that can be added to the star schema. Note

that these dimensions will only be available for selection if you added the Date and Time

entities to your model. For an explanation of how to do this, see Adding Date and Time entities

to your model (page 178).

l

Time Dimensions - Lists all of the Time dimensions that can be added to the star schema. Note

that these dimensions will only be available for selection if you added the Date and Time

entities to your model. For an explanation of how to do this, see Adding Date and Time entities

to your model (page 178).

When adding dimensions using the wizard, if a root dimension already exists in the data

mart, any dimensions selected under the root dimension will be ignored.

Workaround: Edit the dimension and delete or add columns as required.

8. Choose which dimensions to include in the star schema and then click Next.

9. If you chose Star Schema with State Orientation as your star schema type, click Finish. Otherwise,

continue from Step Adding data marts and star schemas (page 225) below.

10. In the Transaction Date screen, choose which Transaction Date to include in the data mart fact table.

Selecting a Transaction Date enables you to retrieve the required data, even if the Dimension table

contains multiple versions of the same record.

For example, a car salesman wants to know how many customers bought cars in New York between

2013 and 2015. Selecting OrderDate as the Transaction Date for the Customers Dimension would make

it possible to retrieve this information, even if a customer moved to a different city (which would also

result in a new record being added to the data mart).

11. If you chose Transactional as your star schema fact type, click Finish. If you chose Aggregated as your

star schema fact type, continue from Step Adding data marts and star schemas (page 225) below.

12. In the Aggregated Fact screen:

a. Select one or more columns from the Fact table on the left of the screen.

You can select multiple columns by holding down the [Shift] (sequential selection) or

[Ctrl] (non-sequential selection) buttons while selecting the columns.

Setup and User Guide - Qlik Compose, May 2022 227

5 Data Warehouse projects

b. To add the column(s) to the Group By list on the right, either drag the columns to the list or

click the arrowhead button to the left of the Group By list. Note that each dimension has a

default "Group By" column that cannot be deleted.

c. To add the column(s) to the Aggregations list on the right, either drag the columns to the list

or click the arrowhead button to the left of the Aggregations list.

d. To add new columns to the Group By or Aggregations list, click the New button above the list.

In the New column window, specify a Name, Type, Description and Aggregation (when

adding a new aggregation column) and then click OK. The column is added to the list.

e. To add an expression, hover the mouse cursor over the table cell in the Expression column and

then click the fx button that appears on the right. The Edit Expression: <Name> window

opens.

For more information on creating expressions, see Creating expressions (page 181).

f. To delete a column, select the column in the list and then click the Delete button above the list.

You can select multiple columns for deletion by holding down the [Shift] (sequential

selection) or [Ctrl] (non-sequential selection) buttons while selecting the columns.

See also Aggregation example (page 231).

13. Click Finish. The newly created star schema is displayed below the Star Schemas heading, as shown

below.

Setup and User Guide - Qlik Compose, May 2022 228

5 Data Warehouse projects

14. Click the Create Tables toolbar button. The Creating Data Mart: Data Mart Name in Target progress

window opens. Wait for the "Create Data Mart tables finished successfully." message to be displayed

and then click Close.

After the data mart tables are created, the Create Tables button changes to Drop and

Recreate tables.

15. Click the Generate toolbar button. The Generating Statements for Task: Data Mart Name window

opens. Wait for the "Generating Statement for Data Mart No. <number> finished successfully." message

to be displayed and then click Close.

16. Click the Run toolbar button.The window switches to Monitor view and a progress bar shows the

current progress in terms of percentage.

Setup and User Guide - Qlik Compose, May 2022 229

5 Data Warehouse projects

When the Total ETL reaches 100 percent, data mart population is complete.

You can stop the task at any time by clicking the Abort toolbar button. This may be necessary if you

need to urgently edit the task settings due to some unforeseen development. After editing the task

settings, simply click the Run button again to restart the task.

l

Aborting a task may leave the data warehouse tables in an inconsistent state.

Consistency will be restored the next time the task is run.

l

In rare situations, the Monitor view in the Manage Data Marts window may not show

any tasks initially. To remedy this, refresh the browser window.

Other monitoring information such as the run details (i.e. the number of rows inserted/updated) and

the task log files can be accessed by clicking the Run Details and Log buttons respectively.

Should any errors occur, you can click the link at the end of the Failed bar for additional information

that may help you troubleshoot the problem.

Once your data mart has been loaded with data, you can check that the required data is available for

your BI tools. For more information, see Displaying data in a pivot table (page 231).

Understanding star schema icons

Compose displays various icons to indicate both the status and characteristics of the star schema tables.

These icons are displayed in the table below.

Icon Description

Indicates that although the structure for the star schema has been defined, all

or part of the dimension(s) and/or fact table do not physically exist in the data

warehouse. Click Create Tables to create the tables and/or click Validate to

see what needs to be adjusted.

Displayed when the dimension(s) and/or fact table physically exist in the data

warehouse.

Star schema icons

Setup and User Guide - Qlik Compose, May 2022 230

5 Data Warehouse projects

Icon Description

Indicates a conformed (shared) dimension.

Small squares indicate that there are denormalized tables under the root

dimension table. Each square represents a denormalized table, so in the image

on the left, the Orders root dimension has four denormalized tables. To view

the denormalized table names, hover the mouse cursor over each of the

squares.

Displayed when a dimension has a reference to itself.

Aggregation example

In the following example, Mike the organization’s data scientist, wants to create an aggregation table that

shows the total freight costs per shipping region and product category; for example, the total cost of shipping

rice to Australia in 2015.

To achieve this objective, he adds the CategoryName and ShipRegion attributes to the Group By list and then

adds the Freight attribute to the Aggregations list. As Mike is interested in the total freight cost, he selects

SUM as the Aggregation Type.

Displaying data in a pivot table

This section explains how you can use Compose to view the data in your star schema.

Setup and User Guide - Qlik Compose, May 2022 231

5 Data Warehouse projects

To view the data in a star schema:

1. Click the Manage button at the bottom of the Data Marts panel.

2. In the Manage Data Marts window, either:

Switch to Monitor view (by clicking the monitor icon) in the top right corner.

Remain in Design view and select a star schema.

3. Click the Pivot toolbar button. If you clicked the Pivot toolbar button in Monitor view and your data

mart contains several star schemas, you will be prompted to selected a star schema. The Select

columns for Pivot table window opens. The drop-down list at the top of the window contains the Fact

table and the Dimensions tables that were used to create the star schema.

The Fact table name is prefixed with "Fct_" while dimension table names are prefixed with "Dim_".

4. Make sure that "Fct_<FactName>" is selected in the drop-down list and then select which fact

column to add to the pivot table.

5. From the drop-down list, select a dimension and then select which dimension columns to add to the

pivot table.

If you added the Date and Time dimension tables to your data mart, you will be able to

select.

6. Optionally, repeat Step 5 to add columns from different dimensions to the pivot table.

When the same column is included in two different dimensions, the pivot table may show

incorrect data.

7. Click OK. The pivot table window opens.

The names of columns that you can use to generate the data will be displayed at the top of the

window.

8. To form the actual table, drag columns to the gray area below the column names (the X-axis) and to

the gray area on the left of the window (the Y-axis). In the following example, the ShippedDate column

has been dragged to the X-axis while the OrderID column has been dragged to the Y-axis.

Setup and User Guide - Qlik Compose, May 2022 232

5 Data Warehouse projects

In this example, the QTR column was selected from the Date dimension, allowing orders to be grouped

by quarter.

9. Change the table format, set aggregation, or perform additional actions as described in the table

below.

Setup and User Guide - Qlik Compose, May 2022 233

5 Data Warehouse projects

To Do this

Set the table

format

From the upper drop-down list in the left of the pivot table window, choose one of

the following:

l

Table

l

Table bar chart

l

Heatmap

l

Row heatmap

l

Col heatmap

l

Treemap

Aggregation

options

From the lower drop-down list in the left of the pivot table window, choose one of

the available options.

Note that additional drop-down lists may be displayed depending on the selected

aggregation option. For example, when Sum over Sum is selected, two additional

drop-down lists (containing column names) will appear below the aggregation

options. The Sum over Sum aggregate is calculated by selecting one column from

each of the drop-down lists.

Change the

columns

Click the Customize Columns button and continue from Step 3 above.

Additional actions

10. Click OK to close the window.

Managing data marts

This section describes the following management options:

l

Adding star schemas and dimensions (page 234)

l

Editing star schemas (page 238)

l

Editing dimensions (page 243)

l

Deleting data marts, schemas and dimensions (page 250)

Data marts pointing to different databases cannot contain tables with the same name.

Adding star schemas and dimensions

A data mart can contain any number of star schemas and dimensions. You can either add dimensions when

you create a new star schema or you can add them later and attach them to star schemas as needed.

Regardless of how they are added, dimensions can be reused across several star schemas as necessary.

To add a star schema:

1. Either click the New Star Schema toolbar button.

Right-click the Star Schemas or Dimensions items and select New Star Schema.

Setup and User Guide - Qlik Compose, May 2022 234

5 Data Warehouse projects

The New Star Schema wizard opens.

2. Perform steps4 to 13 in Adding data marts and star schemas (page 225). The star schema is added to

the Star Schemas list.

3. If you already created the data mart tables (as described in Adding data marts and star schemas (page

225)), you need to create the new star schema tables in the data mart. To do this, perform the

validation process described in Validating and adjusting the data mart (page 252).

Otherwise, perform steps 4 to 13 in Adding data marts and star schemas (page 225). If you also want to

run a data mart task, perform step 16 as well.

To add a dimension:

1. Select the dimension(s) you want to add to the data mart. Then click OK. The dimension(s) are added

to the Dimensions list.

2. If you already created the data mart tables (as described in Adding data marts and star schemas (page

225)), you need to create the new dimension table(s) in the data mart. To do this, perform the

validation process described in Validating and adjusting the data mart (page 252).

Otherwise, perform steps 14 and 15 in Adding data marts and star schemas (page 225). If you also want

to run a data mart task, perform step 16 as well.

3. Select the dimension(s) you want to add to the data mart. Then click OK. The dimension(s) are added

to the Dimensions list.

4. If you already created the data mart tables (as described in Adding data marts and star schemas (page

225)), you need to create the new dimension table(s) in the data mart. To do this, perform the

validation process described in Validating and adjusting the data mart (page 252).

Otherwise, perform steps 14 and 15 in Adding data marts and star schemas (page 225). If you also want

to run a data mart task, perform step 16 as well.

To attach a newly added dimension to a star schema:

1. Perform Steps 1-2 described in To add a dimension: (page 235) above.

2. Select the dimension(s) you want to add to the star schema and then click the Add Dimension to Star

Schema toolbar button. The Add Dimension <Name> to Star Schema window opens.

3. Select which star schema(s) you want to add the dimension to and then click OK. The dimension is

attached to the selected star schema(s).

4. If you already created the data mart tables (as described in Adding data marts and star schemas (page

225)), you need to create the new dimension table(s) in the data mart. To do this, perform the

validation process described in Validating and adjusting the data mart (page 252).

Otherwise, perform steps 14 and 15 in Adding data marts and star schemas (page 225). If you also want

to run a data mart task, perform step 16 as well.

Importing and referencing dimensions

You can import dimensions or reference existing dimensions as needed.

Importing dimensions

You can import dimensions from other data marts in the same project. This is especially useful if:

Setup and User Guide - Qlik Compose, May 2022 235

5 Data Warehouse projects

l

Several developers are working on the same data mart, developing different complex dimensions

l

You need to use a dimension from another data mart and modify it slightly

To import dimensions

1. Open the Manage Data Marts window and click the Import or Reference Dimensions toolbar button.

2. From the Source data mart drop-down list, select the data mart containing the dimensions to import.

3. Select Import the selected dimensions.

4. Select which dimensions to import and then click OK.

Only dimensions that do not already exist in the current data mart (with same name) are

available for selection.

The dimensions are imported to your data mart.

Referencing dimensions

The ability to reference dimensions improves data mart design efficiency and execution flexibility by

facilitating the reuse of data sets. Reuse of dimension tables across data marts allows you to break up fact

tables into smaller units of work for both design and data loading, while ensuring consistency of data for

analytics.

Throughout this section, a dimension that references another dimension will be referred to as a

"referencing dimension" where a dimension that is referenced by another dimension will be referred

to as a "referenced dimension".

To add a referencing dimension:

1. Open the Manage Data Marts window and click the Import or Reference Dimensions toolbar button.

2. In the Import or Reference Dimensions window, select the Source data mart and then select

Reference the selected dimension.

3. Select which dimensions you want to reference, then click OK.

The dimensions are added to the data mart.

Referencing Dimension names have the following format: <dimension name>_<data

mart name>

Setup and User Guide - Qlik Compose, May 2022 236

5 Data Warehouse projects

Data mart with referencing dimension

Referencing dimensions are read-only.

4. To add the newly added dimension to the star schema, right-click the dimension and then select Add

to Star Schema.

The Add Dimension <name> to Star Schema window opens.

5. Select which star schema(s) you want to add the dimension to and then click OK.

After adding the referencing dimension to the star schema, you might see a icon next to

the star schema name. This means that you need to validate and adjust the data mart

containing the referenced dimension.

Working with referenced dimensions

It's important to be aware of the limitations and considerations when referencing other dimensions as well as

the best practice guidelines.

Limitations and considerations

l

Referenced dimensions cannot be deleted from the source data mart.

l

Date and time dimensions cannot be referenced.

l

Data lineage will not show all of the referenced dimensions.

l

Deleting a dimension that references another referenced dimension should be done with caution. For

example, If dimension X is referencing dimension Y which in turn is referencing dimension Z, deleting

dimension Y will affect dimension X as well.

l

Referenced dimensions must be created in the same database as the star schema or fact using them.

They can be in a different schema however.

Best practices

l

To prevent data inconsistencies, make sure that the source data marts ( i.e. the data marts containing

the original dimensions) are processed before any data marts referencing those dimensions.

Setup and User Guide - Qlik Compose, May 2022 237

5 Data Warehouse projects

l

To ensure correct processing of referenced dimensions, it is preferable to avoid circular references. An

example of a circular reference is if Data Mart B references Dimension A in Data Mart A and Data

Mart A references Dimension B in Data Mart B.

In some cases, it is okay to use circular references. If, for example, both Data Mart A and

Data Mart B are incrementally updated, then any updates to Data Mart A will use the

current version of Data Mart B, and vice versa.

l

Conformed referenced dimensions that are used by one or more data marts should be grouped into a

single data mart, without fact tables.

l

Transactional fact tables should be grouped into data marts, based on processing requirements.

l

Aggregate and State-oriented star schemas (fact tables) are typically processed during batch windows

as they require complete rebuilds. It is therefore recommended practice to separate Aggregate and

State-oriented fact tables from Transactional fact tables. Doing so, allows Transactional fact tables to

be processed incrementally throughout the day as required, while allowing Aggregate and State-

oriented fact tables to be processed during batch windows.



Editing star schemas

You can edit a star schema according to your needs. Editing options include adding columns, adding

attributes and defining filters.

To edit a star schema (fact table):

1. Click the Manage button in the bottom left of the Data Mart panel. The Manage Data Marts window

opens.

2. In the left pane, select the data mart containing the star schema you want to edit.

3. Expand the list of start schemas and select the star schema you want to edit. Then either click the Edit

button in the lower toolbar or right-click the star schema and select Edit.

The Edit Star Schema - Name window opens. The following tabs are displayed:

l

General tab: In the General tab, you can edit the star schema name, the fact table name, the

fact view name and the description.

The following option is also available for transactional and aggregated facts:

l

Update fact with changes to Type 2 data warehouse entities - Select this option (the

default) if you want the fact table to always be updated with the last record version of

any Type 2 data warehouse entities the star schema contains.

Example:

Assuming the data warehouse has the following Type 2 entities:

l

Orders

l

Order Details

l

Address



Setup and User Guide - Qlik Compose, May 2022 238

5 Data Warehouse projects

And the data mart consists of the following:

l

Fact = Orders and Order Details

l

Transaction date = Order Date in Orders

l

Dimension = Address (Type 2)



Then the last version of Orders and Order Details will always used and Address will be

updated according to the Oder Date.