Data Reader ETL issue with Text file

Dinesh Chand

Hello All,

 

I have a data reader that reads an ascii tab delimited file. It updates several cubes correctly. However, I have an issue with one of the transformations. I am reading text values and converting this to an integer based on the text value in the source file.

 

=IF(TRIM(UPPER(C10))="FILLED",1,IF(TRIM(UPPER(C10))="VACANT",2,IF(TRIM(UPPER(C10))="CLOSED",3,9)))

I have used various versions of the formula above including E & F columns to make the formula simpler. These formulas perform the correct transformation when I preview the values in the data reader e.g.

However, when I execute the data reader, the cube ends up with the default value (9 in this case). Has anyone else experienced this issue?

 

Thanks,
Dinesh

Previous Member

Hi Dinesh Chand,

 

have you tried to assign for your cube a numerical field instead of a text field? I guess your target cube is a numerical cube type?! Since you try to fill it in the first place by a text value, the datareader cannot fill the cube in the first place - ETL is always active after the dataset has been read.

Hope this might help you.

 

Best regards,

Bettina

Dinesh Chand

Hi Bettina Clausen

You are absolutely correct! When I load to a text cube, the transformation works. Not sure if this how I envisaged the ETL function to work. I thought the transformation layer would transform the data before loading to the target cube. Instead, from what you say and what I have just experienced,  it loads to cube first and then updates the cube by applying the transformation logic. So it is more an ELT  rather than ETL!

 

In fact before I got your reply, my workaround was to load the data as is to a text cube and the have a data flow to transform to codes and store in another cube.

 

Thanks,
Dinesh

Davide Genini - fyi