What are the Pros and Cons while joining tables at DF and Universe level

[Former Member]

Hi Experts,

I am new to Data federator designer. I need help on the below.

Could you please let me know the Pros and Cons while joining the source tables in data federator

And While joining the DF target tables in universe designer.

Regards,

Gana

[amitrathi239]

Hi,

You can design universe on top of Source or Target tables. but the recommendation is to use Target tables because you can merge data from multiple sources in one Target table and you can create more objects in the Target table.You can use DF functions only in target table objects not in source tables.

may be your requirement is meet with source tables right now but in future you need to add some more columns with some manipulation or merging data from two sources to the single table.

Create selected target tables based on the source tables which meet your universe ,reports requirement.

Thanks,

Amit

[Former Member]

Thank for your response.

I have done two scenarios.

1. I have created target tables based on source tables with one to one mapping and then join all target tables in universe.

Ex: Source tables: Infocube text tables, fact tables and 3rd party data base table

Target tables:Target tables are same as source tables

2. Created single target table with all objects of source tables and merged all sources tables data.

Ex: Source tables: Infocube text tables, fact tables and 3rd party data base table

Target table: Single table.

Can you tell me What are the Pros and Cons for the above two scenarios.

[amitrathi239]

Hi,

1. I have created target tables based on source tables with one to one mapping and then join all target tables in universe.

Ex: Source tables: Infocube text tables, fact tables and 3rd party data base table

Target tables:Target tables are same as source tables

--- Yes this is the way to create target Tables and join them in the universe.These target Tables gives you the flexibility like in future your requirement is to add one more object based on some calculation which is not possible in the universe so that time you can create one more column in the target table and do the calculation. at the same time if you are using source table you can not do anything.

2. Created single target table with all objects of source tables and merged all sources tables data.

Ex: Source tables: Infocube text tables, fact tables and 3rd party data base table

Target table: Single table.

--- This is bit complex structure to merge all tables data in the one target table.in this situation you have to put more efforts and it is more complex.basically this type of target table is useful when you are merging data by multiple source into single or creating one target table based on the union of tables by adding two mapping rules or more and you can not join tables in universe.

Better approach is first 1. create target tables based on the source tables and join them in the universe.

Thanks,

Amit

[Former Member]

Thank you so much Amit.

I have been used a word merge but I want to join two or more tables.

Instead of merging if I want to join two or more tables then which is the better scenario.

Regards,

Gana

[amitrathi239]

Hi,

In any case target tables are better instead of source tables. even some time both tables are same but the only difference you can not use DF functions on source tables.

Creating one single Target table based on the multiple source tables is based on the requirement.

Even joining source tables and creating single target table is bit complex .The main advantage of DF is you can create virtual warehouse from multiple sources and can access in the designer and use them and set the joins between.

simple thing if you can join tables in the designer then better do there instead of creating one single target table based on the multiple source tables.

In DF you can simply replicate source table to target with same mapping.

Thanks,

Amit

[Former Member]

Thank you amit