Switching from Production to development environment

In latest version we have introduced support for Global Variables
They are stored in the registry, this allows to use different settings on different computers.

Here is very simple example: two computers sharing same repository using different server for transformations
Production  Development 
  DataBaseOptions

Note:

Please be very careful with this option it is very use to make mistake and run transformation agains wrong database

Q-Eye performance boost

Today we have a reason to celebrate, our development team has managed to boost loading data from QVD files by 100 percent. Q-Eye version 3.1 can load 2.3 million records in just 16 seconds, that is 140k records per second

 

Q-EyeP

Working with large lists of values

Advanced ETl Processor's "Is In list" validation function is used to check if value exists in the database or file.

Is In List

IfIn List

But In some situations it is not very efficient.

  • The list of values can be too big and it would not possible to fit it into the memory.
  • Pulling entire lookup may take to much time
  • We may want to process just 10 records but our lookup has 10 million records

In this case there is better alternative "Is value in database" and "If value in database" transformation functions

Is value in database

If value in database

The way it works is very simple: during execution <value> is replaced with actual value If number of records returned is more than zero it returns success otherwise failure

If value in database Prop

Using web services a practical example

Based on the customer feedback in the latest version of Advanced ETL Processor we have introduced support for the web services

What are the web services.

The simplest way to describe it as a way of communicating with the web servers. The application sends a request to the web server using XML and receives back a reply as XML. One of the examples would be converting Fahrenheit to Celsius or getting current currency exchange rate. A lot of modern websites such as Ebay, Amazon are using web services as a standard API for developers.

Converting Fahrenheit to Celsius

In order to communicate with temperature conversion web service we need to build HTTP Request Header and HTTP Request Body dynamically, There are several ways of doing it the easiest way is to use "In Place Replace transformation" function

Generating HTTP Request Header

Header

 

During execution #value# is replaced with body length

HTTP Request Body

Body

 

During execution #value# is replaced with temperature

Web Service URLProperties

Transformation

Transformation1


 Note: There is a detailed example in the default repository

 

 

Advanced ETL Processor QlikView automation package

Problem:

We have a scenario where we have a large datasource that is currently used to produce a single QVW. What we would like to do is produce a number of smaller QVWs using e.g. Publisher - however, we would like to define the reload schedule and destination folders that these smaller QVWs end up in from an external MySQL table (or config file or similar), rather than through the Publisher GUI. Has anyone done anything similar, or got any recommendations on how to approach this?

Original Post on LinkedIn

Solution: Advanced ETL Processor QlikView automation package

Package is executed from the lef to the right starting from "Init Variables" object

Package

First of all we've created the following table to store parameters, it can be extended if necessassry

table

Table data:

TableDetails

Step 1: Init variables.

Variables are used to replace one string with another, for example anywhere in the package where <RoorDirectory> is found it will be repalced with c:\Customers.

Variables1

Step 2: Inc

This script ins increasing variable <loop variable> by one (two times)

Script

Step 3: Get Variables

This step executes the following SQL to get variables form the databases, before execution <loop variable>  is replaced with actual value

Lookup

 SetVariables

Step 4: Create directory

This step creates directory if it does not exists

CreateDirectory

Step 5: Copy dashboard

This step copies Dashboard to just created directory, so it is easy to distribute updates

CopyDashboard

Step 6: Generate QVD files

This step is used to create QVD file, if it necessary to create several files we can just add more Export steps or for complex cases we can use transformation object to create QVD files

Export1

 Export2

 

Step 7: Refresh Dashboard

This step is used to Refresh QlikView dashboard, data is loaded from the same directory where dashboard is stored

RefreshDashBoard

Step 8: Compress Dashboard

CompressDashboard

 

Step 9: Email Dashboard

Email

 Back to Step 1

We did spend 40 minutes designing this package, so can you

Learn More Download  Buy Now

Processing HL7 Messages

Configuring Advanced ETL Processor for HL7 Processing

General settings

HL71
 

HL72

IP Address

IP address of data reader must be the same as the computer it is being run on.
Use ipconfig to get IP Address

HL73

HL74
Executing transformation

Click plus
Select transformation to run
Enter correct computer name
Select how often -> once

HL75

HL76

To run HL7 Transformation Press green arrow.
Note:

Only one HL7 transformation can listen to the port at the same time.

Checking if transformation is running.

HL77

Status must be running

Stopping transformation

Check “Abort Execution” wait for the transformation to abort

Making changes

To apply changes stop and start execution again.

Performance considerations

Writing Thousands of HL7 messages into same directory can slow down the process.
Creating new folder for every day or hour will help to keep performance high
Disk space/database space usage must be contantly monitored

 

Avoiding common problems when working with ETL

Introduction.

First of all, we do have documentation and recommended settings are described in the documentation, plus we have online tutorials.

Getting help fast.

The most important thing is to follow the rules and provide all necessary information first time.

Bad support request example:

Hi,
We have just downloaded your software and we have an error message.
Please help it is urgent

Which software? Which Error?

Repository.

Problem: Strange access violations when executing the packages

We do not recommend using MS Access Repository for production. It works fine for development, but over time it tends to get corrupted. Plus it has 2 GB database limit. When Access Repository  is corrupted users may get strange access valuations. If you wish to use Access as Repository do compact and repair once a week.

  1. Stop the agent
  2. Close Application
  3. Open repository in MS access
  4. Do “Compact and Repair”

Problem: Some of the data is not being saved into the repository when working with MySQL repository

Check settings for ODBC Dsn and described in the documentation
Increase max_allowed_paket_size
 
Problem: Not able to use PostreSQL repository

Check settings for ODBC Dsn and described in the documentation and make sure that they are exactly the same.

Mapping

Problem: We have added new field to the table and now our mapping is invalid and nothing work. Do we have to remap everything?

The mapping is position based to solve the problem you can

  1. Add field to the end
  2. Add another transformation before the writer and use automap

Problem: Not able to load data into Date fields.

We use standard ODBC format for date fields ‘yyyy-dd-mm hh:mm:ss’
If the data is already in this format no modifications are required.
If not use “Format Date” function in Advanced ETL Processor or apply format in Visual Importer

Avoiding loosing repository data.

  1. Make regular backups
  2. Use integrated version control.

Performance

Problem:I am working with bad data and transformation is very slow my logs are huge.

Too much logging slows down transformation.

More Articles...