[resolved] Creating Data Mart Structure from tables in Data Warehouse

[Anonymous]

Hi,
I'm running Talend Open Studio 5.5 (community version) and I have a task I need some help with, since I'm new to Talend.
We have a Data Warehouse were we would like to create dynamicly Data Marts from different parameters. Everything is hosted on Amazon Redshift.
Our idea so far is the following:
1: Take all content from the tables were eg. countries = "value (eg. contryname)" and move content and structure of table to data mart.
"FromSchemaName.FromTableName" - "ToSchemaName.ToTableName WhereClauseColumnName1" etc.
2: Talend Job DROP and CREATE TABLE from the structure of "FromSchemaName.FromTableName"
3: Talend Job does INSERT INTO "ToSchemaName.ToTableName SELECT * FROM FromSchemaName.FromTableName"
How is this done, and is there any better way to do it?
It's basicly "Take table structure and data from certain tables in data warehouse and then create and insert structure and data into local data mart"

[Anonymous]

Hi,
Have you thought about data federation?
Do you really need to drop from structure?
- Other than data federation, you can design talend jobs to do this task... This is simple and straight forward and talend is specialized for such data transfer activities.
Vaibhav

[Anonymous]

Thanks for the reply,  sanvaibhav
My major problem is actually how I can make Talend jobs that does that. Just a simple example or structure reference would help get me started

[Anonymous · Accepted solution]

One of the simple flow would be
tPreJob-->tDBConnectionInput-->tDBConnectionOutput
tDBInput-->tDBOutput
|
OnSubjobOk
|
tDBRow (to perform delete action if you need)
- This is a very basic job, but to perform this task in more elegant way, this task is extended to around 5-10 different tasks, logging, auditing etc....
Check the example below for mysql output, your input will be database in place of flatfile/fixedflow component..but the logic would remain same
https://help.talend.com/search/all?query=tMysqlInput&content-lang=en
https://help.talend.com/search/all?query=tMysqlOutput&content-lang=en
Hope you got an idea..
Thanks
Vaibhav

[Anonymous]

Thanks for the help, much appreciated