topic Re: Transpose transformation in DI with dynamic columns number in Developers

Transpose transformation in DI with dynamic columns number

vcxcv — Tue, 17 Sep 2024 13:56:59 GMT

Hi,

i use Data integration Studio 4.905.

I want to use the transpose transformation to convert value from a column (nationality_id), to new columns.

Basically the number of "nationality_id" by group (=user_id) can vary from 1 to 5, so 5 new nationality_id_x columns will be created for each user_id.

But i want to create some "spare" columns in case of new set of data bring a user_id with more than 5 differents nationnality_id.

So in the mapping window of the transpose transformation, i've created for the target table, 2 new metadata columns named nationality_id_6 and nationality_id_7.

I've put the option "Update the table metadata for the target tables" to No, acccording the documentation that i fnd there (bullet in the Overview section)

https://documentation.sas.com/doc/en/etlug/4.904/p1lj6ps6h3xv7yn1qfr17lifevwb.htm

When i try to use the (work)output table in a following "extract" transformation, i've got this error:

ERROR: The following columns were not found in the contributing tables: Nationality_id_6, Nationality_id_7.

How i can deal with that?

Thank you very much.

Re: Transpose transformation in DI with dynamic columns number

LinusH — Wed, 18 Sep 2024 09:33:07 GMT

Untested, but maybe if you can add post code to your transformation that creates the output with all your defined columns.

That being said, it is awkward to work with a metadata tool like DIS and dynamic table layout. It's usually preferred to have long/narrow data structures in your ETL for your bronze and silver layers, and maybe transpose at the last step to a specific data mart.

Re: Transpose transformation in DI with dynamic columns number

vcxcv — Wed, 18 Sep 2024 07:15:07 GMT

Hello LinusH,

Thank you very much for your reply.

In fact my table will not be dynamic, the number of columns will remain the same, but some will be empty.

Yes, of course, I can add post-code, and even use a ‘user-written code’ transformation, but
1° I try to avoid it as much as possible
2° I've followed the advice in the sas documentation, but it doesn't work, so maybe I haven't understood the documentation properly?

I'm building a dimension for a data warehouse, in a star schema model, so already in the gold layer.

Cheers

Re: Transpose transformation in DI with dynamic columns number

Patrick — Wed, 18 Sep 2024 12:02:16 GMT

It's quite a long time since I've used DIS but they approach that normally worked for me: Investigate the SAS log and the DIS generated code to understand what causes the error. Once identified "play" with the transformation (change something) and check the change to the generated code until you (eventually) find a way that it creates valid code.

I personally never liked the transpose transformation in DIS because it doesn't guarantee stable target table structures. I wouldn't hesitate to implement using custom code or if needed more than once a custom transformation.

Re: Transpose transformation in DI with dynamic columns number

LinusH — Wed, 18 Sep 2024 13:09:37 GMT

The help in the link you provided doesn't really cover your scenario/setup. It's more of generic help to initialize metadata via a data driven approach.

The Transpose transormation is not clever enough to read the metadata and generate the defined output structure.

This is a Generated Transformation, which means you can make a copy of it and tweak it to meet your needs, which would might feel better than using post-code or User Written Code, especially if this is a repetetive problem. When copying it, make sure you tick "Generate column mapping macros" on the tab Inputs/Outpus. Then you can pick up the output column structure in your code, create an empty output table, and then insert your transpose output and map any non-existent column with MISSING.