Processing HL7 Messages

Configuring Advanced ETL Processor for HL7 Processing

General settings

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

Executing transformation

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

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.

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.

Editing resident tables in QlikView

Release of Q-Eye allows QlikView users view and even edit residential tables.

Here are some examples:

1. Viewing Data

SUB INSPECT (T)
 LET LocalAppDataPath = GetRegistryString('HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion', 'ProgramFilesDir');
 LET QViewerPath = '$(LocalAppDataPath)\DB Software Laboratory\Q-Eye\QEye.exe';
 STORE $(T) into [$(QvWorkPath)\~$(T).qvd] (qvd);
 EXECUTE "$(QViewerPath)" "$(QvWorkPath)\~$(T).qvd";
 EXECUTE cmd /c del "$(QvWorkPath)\~$(T).qvd";
ENDSUB

2. Editing Data

SUB EDIT (T)
 LET LocalAppDataPath = GetRegistryString('HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion', 'ProgramFilesDir');
 LET QViewerPath = '$(LocalAppDataPath)\DB Software Laboratory\Q-Eye\QEye.exe';
 STORE $(T) into [$(QvWorkPath)\~$(T).qvd] (qvd);
 EXECUTE "$(QViewerPath)" "$(QvWorkPath)\~$(T).qvd";
 DROP TABLE $(T);
 LOAD * FROM [$(QvWorkPath)\~$(T).qvd] (qvd);
 EXECUTE cmd /c del "$(QvWorkPath)\~$(T).qvd";
ENDSUB

If we want to edit the table we just

CALL EDIT('Log');

Or if we want to view the table we use this code

CALL INSPECT('Log');

Note: By default Q-Eye uses partial load, to avoid problems load entire QVD file before editing it 

This example is based on:
http://bi-review.blogspot.co.uk/2012/12/how-to-look-inside-resident-tables-at.html

Date Formats

Date/Time format strings control the conversion of strings into date time type.

Date Time Format Strings are composed from specifies that describe values to be converted into the date time value.

In the following table, specifies are given in lower case. Case is ignored in formats, except for the "am/pm" and "a/p" specifies.

Specifier          Description

d                      Day as a number without a leading zero (1-31).

dd                    Day as a number with a leading zero (01-31).

m                     Month as a number without a leading zero (1-12).

mm                  Month as a number with a leading zero (01-12).

mmm              Month as an abbreviation (Jan-Dec).

mmmm           Month as a full name (January-December).

yy                    Year as a two-digit number (00-99).

yyyy                Year as a four-digit number (0000-9999).

h                      Hour without a leading zero (0-23).

hh                    Hour with a leading zero (00-23).

n                      Minute without a leading zero (0-59).

nn                    Minute with a leading zero (00-59).

s                       Second without a leading zero (0-59).

ss                     Second with a leading zero (00-59).

zzz                   Fraction of Second with a leading zero (000-999).
                        (Works only for oracle time stamp fileds)

tt                      Uses the 12-hour clock for the preceding h or hh specifier,  'am' for any hour before noon, and 'pm' for any hour after noon.