Creating a DF with variable table names

[Former Member]

Hi,

I have a need to create some data flows that use a varying input table name. The structure remains the same from month to month, but the underlying ERP system creates a unique, but easily deduced, name for the transactional table every month. I know I can create a script that can assemble the name of the table each month at the WF stage and assign it to a variable, but is it possible to pass this into a DF to make it use that name as the source table?

I am using Data Services XI 3.0 on Windows running against a Storis system (retail ERP running on UniData DB).

Regards,

Derek Stobbart.

[Former Member]

Hi Ben,

The idea of creating a view is not a viable one, unfortunately. So we went with the SQL transform idea. At what point will the SQL transform carry out the substitution? Will it only happen at runtime?

I ask because using the "Update Schema" button in the SQL transform properties produces an error (understandably) because it simply submits the variable as the table name.

I guess my question is: Will the SQL transform require an "Update Schema", or can I just submit this as is?

Derek.

[Former Member]

You will need the schema so that subsequent transforms (or the target table) can use this information. And you are right, the global variable's value is only known at run-time and not at design-time...

So what you need to do is in the SQL, use the correct table name (just one table with the correct schema), click update schema and next replace the table name with the variable name again.

[werner_daehn]

Option 1: SQL Transform

All the benefit of using an ETL tool is lost, pushdown of SQL, impact lineage information, performance etc

Option 2: Create Synonym/View

At the beginning of the job there is a script with a "create or replace view myview as select * from [$tablename]" statement. Therefore, ever dataflow reading from the table "myview" will actually read from the table $tablename.

All benefits of an ETL tool is used, when being careful even partitioning information.

Option 3: Union all view

Whenever a new table is added to the database, the myview gets extended.

create or replace view myview as

select 200801 as year_month, tab1.* from tab1

union all

select 200802 as year_month, tab2.* from tab2

union all

select 200803 as year_month, tab3.* from tab3

union all

select 200804 as year_month, tab4.* from tab4

...

...

All benefits of an ETL tool is used, all data can be read via the myview object - not just one month of data. No performance impact as long as you select the data with the where year_month=xxxxx as the database optimizer will recognice that only one view segment actually fullfills that (for Oracle I know for sure).

Option 4: Change the source to be a partitioned table

This is the same as Option 3, just that the database deals with that. So instead of creating a new table in the source, you "alter table add partition...". In Oracle 11 even that is done automatically.

Obviously no option in case you do not have control over the source system.

Option 5: Using schemas and aliases

We do not allow table aliases, but owner aliases. So if you have 12 tables, you could create 12 schemas, each pointing to one table by using 12 views or synonyms. When you develop your dataflow you use the standard owner but when executing one of the 12 different aliases are used.

No real solution for your particular usecase but could be an option for others.

[Former Member]

If this is for reading only from a table with variable name, the solution is to use the SQL transform, and in the SQL transform use a global variable for the table name in the FROM clause :

e.g.

SELECT * FROM [$G_TABLE] 

You can set the $G_TABLE variable in a script prior to the data flow or pass the value when you execute the job.

The other alternative would be to create a view with a fixed name (something like CREATE VIEW AS SELECT * FROM $G_TABLE) in a script and use the view as regular source (or target) in the data flow.

Edited by: Ben Hofmans on Oct 10, 2008 12:40 PM - used in order to get the square brackets displayed in the SQL code.