Dynamic Metadata

FDW_CITI · Post by **FDW_CITI** » Thu Jun 24, 2010 5:58 am

We have a requirement is as below:

Table A:
Col1
Col2
Col3
Col4
Col5
Col6
Col7
Col8
Col9
Col10

All users require Col1 to Col5 to be loaded to the target table.

User1 need Col6 & Col7 also to be loaded in the target along with Col1 to Col5. This may change later as the user1 may require Col6, Col7 & Col9 to be loaded.

User2 need Col7 & Col10 to be loaded in addition to Col1 to Col5

User3 need Col8 to be loaded in addition to Col1 to Col5.

We have transformation defined only for Col1 to Col5. All other column values can be directly loaded into the target table.

Please let me know whether this can be achieved in DS using a single job.

Thanks in advance!

Regards,
Manju

ArndW · Post by **ArndW** » Thu Jun 24, 2010 6:51 am

Do users 1, 2 and 3 read different target tables? Or do you just have one target table but 3 different query requirements?

antonyraj.deva · Post by **antonyraj.deva** » Thu Jun 24, 2010 9:50 am

Hi Arulan,

As per my understanding of the post, the "Table A" which is the target table has already got 10 columns and Col1 to Col5 are using some transformation logic whereas Col6 to Col10 are just direct mappings.

So the confusing part is what's the problem in loading Col6 to Col10.

If there is an issue, then FDW_CITI hasn't said it yet.

ray.wurlod · Post by **ray.wurlod** » Thu Jun 24, 2010 4:48 pm

Constrain outputs based on the value of the user ID (perhaps retrieved through GetEnvironment() call).

FDW_CITI · Post by **FDW_CITI** » Thu Jun 24, 2010 11:53 pm

ArndW wrote:Do users 1, 2 and 3 read different target tables? Or do you just have one target table but 3 different query requirements? ...

The Target table is Single table with 3 different query requirements.

The users 1, 2 & 3 may select any columns from col6 to col10 for loading the target table.

The column selection is dynamic and this should be done using a single job

Regards,
Manju

ray.wurlod · Post by **ray.wurlod** » Fri Jun 25, 2010 2:16 am

FDW_CITI wrote:... and this should be done using a single job

Why?

To me the most eminently sensible, and most easily maintained, design is to use separate jobs with a sequence to make the decision about which one to run. These might be multi-instance so each user can run without needing to have regard to any other users who might be running it.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Fri Jun 25, 2010 3:08 am

Why don't you load all columns - irrespective of the user ?

You can include the username as an additional column and restrict the flow later on using this value.

ArndW · Post by **ArndW** » Fri Jun 25, 2010 3:26 am

If the target is just one table, then as Sainath has already stated it makes the most sense to put all the data into the table and then change the queries to ones appropriate for user queries of type 1, 2 or 3.

jcthornton · Post by **jcthornton** » Fri Jun 25, 2010 10:22 am

It sounds like there is a lack of separation in this problem. As I read it:

1. Different users need different query results for reports.
2. The defined solution is to perform the LOAD according to the ultimate output needed.

Wouldn't it make more sense to split up the Load and the Report requirements? Especially where the reporting requirements may change?

The other issue that comes to mind is what is the reason for the different needs? If it is just about what individual job responsibilities the single table/single job solution is good. If the difference is for security (maybe Col 7,8,9 are HIPAA or financially sensitive values...) then it would imply that the problem description is incomplete and something else needs to address the access.