ETL for Ordinary People

With our ETL software you can automate your business processes in couple of hours, than you can concentrate on what is important: running the business.

Our prices start only from 100 usd per licence.

Today 95 percent of the businesses are small and medium size companies. Most of ETL and business automation software is designed for large companies, it requires highly skilled staff, difficult to support and far too expensive.

Our ETL software was especially designed for ordinary users.

It takes seconds to install it, works straight out of the box, easy to use and easy to support. The trial is fully functional for 30 day and if necessary the trial period can be extended.

Our support team is always happy to connect remotely and assist

Transformation sample


Download Visual Importer ETL Enterprise

Download Advanced ETL Processor Enterprise

Which ETL tool is right for me?

Small business automation

95 percent of the businesses are small and medium sized enterprises, All of them want to automate routine business processes so they can concentrate on what is important: running the business.

We offer Automation software which is easy to use and it works right now. There is no need to hire highly skill staff to deliver automation solution.

With our ETL software complex processes can be automated with days not weeks.

Current BI trends:

Everyone is taking about BIG DATA, as a result of it everything is geared up towards large corporations with big budgets. Most of the solutions are difficult to implement, use and support plus they require high end hardware and large BI teams. It is very expensive and difficult to implement something simple, like automatically sending price list updates to the customers.

Fork as ETL?

Our prices start only from 100 usd per licence.

Our ETL software was especially designed for ordinary users.

It takes seconds to install it, works straight out of the box, easy to use and easy to support. The trial is fully functional for 30 day and if necessary the trial period can be extended.

Our support team is always happy to connect remotely and assist

Transformation sample


Download Visual Importer ETL Enterprise

Download Advanced ETL Processor Enterprise

Which ETL tool is right for me?

Tired of SSIS

Advanced ETL processor vs SSIS.

Development Efficiency against wasting time

One of our Insurance customers has contacted us recently and asked for immediate assistance. They were tryig to implement XML files processing for number of weeks using SSIS.
Our consultant was able to deliver working solution within one day using Advanced ETL Processor.

Here is what he did:

Background

The XML files show up every day.  The client places them on FTP server (which means that the file is locally found on our network via a UNC path. )
Each nested XML tag should be a new “related” table.
Main XML tag is "Contracts", Other relevant tags are Billing,Coverage,Person,Risk and Underwriting Question.
There are a couple of data issues as bad dates from the customer. They should be ignored and inserted into the table as a Null.
The process should be scheduled to check an UNC folder on the network at least 3 times per day.
If 1 or more XML files exist, they need to be loaded into the server.
Once loaded an Email should be sent to me indicating success, failure or nothing to do.  
There are some duplicated data due to the relationships of the data.
For example, each contract has Agency information.  So any contract MAY use a previously inserted Agency In this case Update would be appropriate.
Updating data, if same conract is loaded second time all relevant data must be deleted first

Solution

For every nested XML tag a relevant data transformation was created.
On the the main problem was that XML format was not stable some of the files have missing tags or additional tags.
That was addressed by using custom XSLT transformations

SSIS Package Alternative

Than package was created to combine all those transformations together

SSIS Package Alternative

We have been fighting this one with SSIS for about 2 weeks and got really behind schedule. Then I remembered we own Advanced ETL Processor so I thought we should give it a shot. We are just so far behind that we need this ASAP and I did not want to mess it up. The result was far beyond our expectations. They were able to do more in one day than entite team in two weeks.

If you are struggling with SISS or any other tools and have very tight deadline contact us and we will to help you today.

Note

In order to speed up the process please provide as much information as you can.

  • Several data examples
  • table creation scripts
  • Remote access details
  • Have someone immediately available for questions

Calculating checksum in datawarehousing

Checksum is a calculated value that is used to determine the integrity of data. Checksum serves as a unique identifier for the data (a file, a text string, or a table row). If the data changes then so does the checksum value. This makes it easy to verify the integrity of the data.

Checksum is very useful in data warehousing especially when working with very wide slow changing dimensions. It takes less processing power to compare one checksum field than to compare all fields within dimension.

dbSL ETL software can calculate check sum for entire file, string or row of data. it supports following algorithms CRC16, CRC24, CRC32, Adler32, CRC64, eDonkey, eMule, MD4, MD5, RIPEMD160, SHA1, SHA224, SHA256, SHA384, SHA512,Whirlpool. It can return the data in HEX or BASE64 format.

Calculating checksum

Time dimension for PosgreSQL based data warehouse

This sql script will create an populate Time dimension for PosgreSQL based data warehouse


CREATE TABLE time_dim
(
time_key integer NOT NULL,
time_value character(5) NOT NULL,
hours_24 character(2) NOT NULL,
hours_12 character(2) NOT NULL,
hour_minutes character (2)  NOT NULL,
day_minutes integer NOT NULL,
day_time_name character varying (20) NOT NULL,
day_night character varying (20) NOT NULL,
CONSTRAINT time_dim_pk PRIMARY KEY (time_key)
)
WITH (
OIDS=FALSE
);

COMMENT ON TABLE time_dim IS 'Time Dimension';
COMMENT ON COLUMN time_dim.time_key IS 'Time Dimension PK';

insert into  time_dim

SELECT  cast(to_char(minute, 'hh24mi') as numeric) time_key,
to_char(minute, 'hh24:mi') AS tume_value,
-- Hour of the day (0 - 23)
to_char(minute, 'hh24') AS hour_24,
-- Hour of the day (0 - 11)
to_char(minute, 'hh12') hour_12,
-- Hour minute (0 - 59)
to_char(minute, 'mi') hour_minutes,
-- Minute of the day (0 - 1439)
extract(hour FROM minute)*60 + extract(minute FROM minute) day_minutes,
-- Names of day periods
case when to_char(minute, 'hh24:mi') BETWEEN '06:00' AND '08:29'
then 'Morning'
when to_char(minute, 'hh24:mi') BETWEEN '08:30' AND '11:59'
then 'AM'
when to_char(minute, 'hh24:mi') BETWEEN '12:00' AND '17:59'
then 'PM'
when to_char(minute, 'hh24:mi') BETWEEN '18:00' AND '22:29'
then 'Evening'
else 'Night'
end AS day_time_name,
-- Indicator of day or night
case when to_char(minute, 'hh24:mi') BETWEEN '07:00' AND '19:59' then 'Day'
else 'Night'
end AS day_night
FROM (SELECT '0:00'::time + (sequence.minute || ' minutes')::interval AS minute
FROM generate_series(0,1439) AS sequence(minute)
GROUP BY sequence.minute
) DQ
ORDER BY 1

Based on on information provided here

http://wiki.postgresql.org/wiki/Date_and_Time_dimensions

Advanced ETL Processor generates GUID's

New version of Adnvaced ETL Processor introduces support for GUID generation, Random number generation, and base 64 Encoding/Decoding.

GUID generation A globally unique identifier (GUID) is a unique reference number used as an identifier in computer software. The term GUID also is used for Microsoft's implementation of the Universally Unique Identifier (UUID) standard.

The value of a GUID is represented as a 32-character hexadecimal string, such as {21EC2020-3AEA-1069-A2DD-08002B30309D}, and is usually stored as a 128-bit integer. The total number of unique keys is 2128 or 3.4×1038. This number is so large that the probability of the same number being generated randomly twice is negligible.
Random Number Generation Random returns a random number within the range 0 <= X < Range
Encode Base 64
Decode Base64
Base64 is a group of similar encoding schemes that represent binary data in an ASCII string format by translating it into a radix-64 representation. The Base64 term originates from a specific MIME content transfer encoding.

Base64 encoding schemes are commonly used when there is a need to encode binary data that needs be stored and transferred over media that are designed to deal with textual data. This is to ensure that the data remains intact without modification during transport. Base64 is commonly used in a number of applications including email via MIME, and storing complex data in XML.

Active Table Editor support for ADO

New version Active Table Editor is available for download. In this version we have introduced support for OLE DB.

Active Table Editor Support for ADO

About Active Table Editor

Active Table Editor is designed to edit data in any database. plus it can create reports and sophisticated data entry screens

More Articles...