How to Model and Transform Data in Qlik

Qlik Date Conversion for SAP

What is Data Modeling in Qlik?

The Qlik product family offers two methods to model and transform data: (a) using Qlik Compose, a data warehouse automation tool, and (b) using the Data Manager in Qlik Sense.  Qlik Compose is a more formal tool for creating and managing data warehouse schemas.  The Data Manager in Qlik Sense is an inline interface to load and model data for use within a Qlik app.

Qlik Compose

Qlik Compose is a tool for data warehouse automation that automates and manages the entire data warehouse lifecycle.  With Qlik Compose, data engineers can create new data models, add new sources, and provision new data marts. The tool integrates with the Qlik Data Integration to extract and load the data in the data warehouse and with Qlik Catalog to catalog and govern the data models it creates and manages.

With Qlik Compose, there are three places where data is managed within the data warehouse: a landing area, warehouse, and data marts.  With Qlik Compose, you perform the following steps to create and model your data warehouse and data marts:

  • You use Qlik Replicate to define data pipelines that extract data from sources and load the raw data into a landing area.
  • You then model the data using Qlik Compose, creating a data vault style data model for your overall warehouse within the data warehouse.
  • Finally, you define and create individual data marts to support different analytics apps for the business.  The resulting data marts have star-schema data models.

The user experience for Qlik Compose is very much like a formal data modeling tool that adheres to data vault and star-schema methodologies.  Data models for your warehouse must be a data vault, and those for your data marts must be star-schemas (facts and dimensions).

Qlik Compose lets you create all your data models graphically without writing code.  If you need to customize fields or enrich the data with additional fields, you do so via a formula builder, which translates into SQL expressions.  You can also add data quality rules for cleansing, validation, filters, etc.

Qlik Compose also automates the end-to-end process of data warehouse modeling, creation, and management.

Qlik Data Manager

Qlik Sense contains a tool called Data Manager which allows a user (analyst) to define what data to use within a Qlik app, then model and transform it to get the data in the proper use for the app.  Within Data Manager and a Qlik app, you form data tables representing the data you want to use.

With Data Manager, you can:

  • Add and load data tables from data sources using a data connection or by importing files,
  • Edit data tables to remove unneeded columns, add new columns via formulas, and perform simple transformations,
  • Create associations between data tables and perform JOINs and concatenations to combine data tables into new ones.

Data Manager generates data loading scripts that are run within the Qlik app.  You can also define data transformations by creating or editing data loading scripts.  You perform the scripting in the Data Load editor.  To perform more than simple data transformations such as JOINs or calculated columns, you will need to use the Data Load editor and write scripts.

The Data Load editor is an inline tool that looks like an IDE with an editor to define your data loading and transformation scripts.  Within these scripts, you can do more sophisticated transformations such as dropping or adding new calculated fields, translating coded fields, joining tables, aggregating values, pivoting, and data validation.  But the important thing to remember is: to perform more than simple data transformations, you must write scripts .

What Qlik Tools Are Good For and What They Are Not

The Qlik tools for data modeling and transformation are designed for their specific purposes:

  • Qlik Compose is well suited for data warehouse data modeling and automation.  It is an excellent tool to simplify and accelerate large data warehouse projects that can potentially take months.  It is designed for data engineers.  What Qlik Compose is not good for is analyst self-service data modeling and transformation.  The tool is far too complicated and complex for that purpose.
  • Data Manager and Data Load editor are designed for very simple data modeling within Qlik Sense.  Performing anything more than simple data transformations requires writing code (scripts).  Also, data loading scripts are contained and run within the Qlik app, making them not shareable across projects.

The best approach to data modeling and transformation for your Qlik apps is to use a third-party, no-code, in-cloud data warehouse data transformation tool.

badge icon

Data Modeling and Transformation Best Practices

Whether for Qlik apps or other analytics applications, there are certain best practices data and analytics teams should adhere to for data modeling and transformation.  To learn more about these best practices, read our definitive guides:

What is Datameer Spectrum (ETL++)? icon

Datameer Data Modeling and Transformation

Datameer is a powerful SaaS data transformation platform that runs in Snowflake – your modern, scalable cloud data warehouse – that combines to provide a highly scalable and flexible environment to transform your data into meaningful analytics.  With Datameer, you can:

  • Allow your non-technical analytics team members to work with your complex data without the need to write code using Datameer’s no-code and low-code data transformation interfaces,
  • Collaborate amongst technical and non-technical team members to build data models and the data transformation flows to fulfill these models, each using their skills and knowledge.
  • Fully enrich analytics datasets to add even more flavor to your analysis using the diverse array of graphical formulas and functions,
  • Generate rich documentation and add user-supplied attributes, comments, tags, and more to share searchable knowledge about your data across the entire analytics community,
  • Use the catalog-like documentation features to crowd-source your data governance processes for greater data democratization and data literacy,
  • Maintain full audit trails of how data is transformed and used by the community to further enable your governance and compliance processes,
  • Deploy and execute data transformation models directly in Snowflake to gain the scalability you need over your large volumes of data while keeping compute and storage costs low.
about informatica

Why Datameer for Qlik?

Datameer provides a number of key benefits for your modern data stack and Qlik apps, including:

  • Creating a highly efficient data stack that reduces your data and analytics engineering costs,
  • Allowing you to share the data transformation workload across your broader data and analytics team,
  • Fostering collaboration among the data and analytics team to produce faster, error-free projects,
  • Efficiently using your Snowflake analytics engine for cost-effective data transformation processing,
  • Enabling you to crowd-source your data governance for more effective and efficient governance processes, and
  • Improving data literacy to expand knowledge and effective use of your data.

In addition, Datameer in-Snowflake data models and transformations would be available to your entire suite of BI apps and tools, not just Qlik apps.  Most organizations have multiple BI tools.

conclusion icon

Conclusion

The Qlik tools for data modeling and transformation – Qlik Compose and Qlik Data Manager/Load Editor – are purpose-built for highly specific tasks (data warehouse automation and data loading).  These tools are not general-purpose no-code data modeling and transformation tools that can meet the needs of your entire set of personas (data engineers, data analysts, and data scientists) and allow them to collaborate on projects.

Datameer’s explicit focus on in-Snowflake data transformation makes it much more applicable across multiple analytics projects and tools.  It offers a much more inclusive and easier user experience that supports multiple personas, collaboration among team members, a much deeper set of searchable, catalog-like data documentation, and transforms directly in Snowflake, using its powerful engine and keeping data and models secure.

Are you interested in seeing Datameer in action?  Contact our team to request a personalized product demonstration .

No-Code Analytics Built for Snowflake

Book Demo