Data Replication 2
Results 1 to 6 of 6

Thread: Does not replicate data from multiple source tables to a single target table

  1. #1
    brian is offline Junior Member
    Join Date
    Nov 2014
    Posts
    5
    Rep Power
    0

    Does not replicate data from multiple source tables to a single target table

    I have a Dimension table within Redshift database. Where, I want to replicate data from multiple source tables exists within SQL Server database into a single Redshift table jointly. Even target schema not being created properly by the tool. I have tried many ways with the tool but unable to do so. is it possible with Attunity Replicate?

    Say, I have two tables in our OLTP system (SQL Server): Employees, and EmployeeTasks. And I have dimension table DimEmployee in data warehouse (Redshift). There are few attributes in DimEmployee: EmployeeName, EmployeeID, and TaskName where EmployeeName would come from Employee table and TaskName would come from EmployeeTasks table. Now in Attunity I don't find any option or feature that can join the two tables Employees and EmployeeTasks, take data from these two tables and copy in a single dimension table. Can someone please confirm if it is really possible in Attunity Replicate?

    Another is, Is it possible to build custom SQL Query for retrieving data from SQL server?
    Last edited by brian; 12-04-2014 at 02:44 PM.

  2. #2
    stevenguyen is offline Senior Member
    Join Date
    May 2014
    Posts
    297
    Rep Power
    6
    Quote Originally Posted by brian View Post
    I have a Dimension table within Redshift database. Where, I want to replicate data from multiple source tables exists within SQL Server database into a single Redshift table jointly. Even target schema not being created properly by the tool. I have tried many ways with the tool but unable to do so. is it possible with Attunity Replicate?


    Another is, Is it possible to build custom SQL Query for retrieving data from SQL server?

    Hello Brian,

    We can do Table Union, but not join.

    Replicate is designed to move data from source table to target table.

    Best is to create a view on the target that does the join and present the view to the end users rather than the individual tables.

    Thanks,
    Steve

  3. #3
    stevenguyen is offline Senior Member
    Join Date
    May 2014
    Posts
    297
    Rep Power
    6

    Data Enrichment

    Hello Brain,

    You can use Data Enrichment functions to join tables.

    below is an example:

    My two tables:
    Test1
    Customer_id (PK)
    First_Name
    Last_Name
    City_id

    Test2
    City_id (PK)
    City_Name

    1. Create a task with just the Test1 (customer infor) table.
    2. From the test1(customer infor) go into transformation and add a new column City_Name
    Name:  table1_sourcelookup.PNG
Views: 2139
Size:  65.5 KB
    3. For City_Name, click on the expression / Functions / Data enrichment and use source_lookup,
    For the source_lookup, example: source_lookup('NO_EXPIRATION','dbo','Test2','[City_Name]','[City_id]=?',$City_id)
    Name:  table1_sourcelookup_functions.PNG
Views: 2140
Size:  70.5 KB
    4. Save the task and run a full load.

  4. #4
    brian is offline Junior Member
    Join Date
    Nov 2014
    Posts
    5
    Rep Power
    0
    Thanks Steve for your replies.

    We have cases where we need to join many tables. Again sometimes we need outer join, sometimes inner join. So lookup is not the exact solution for us. Rather full flexibility with SQL could be more preferable.

    We planned to create Views to execute required join queries and then then use the views as source. But we are shocked because we don't find option to use database Views as Source, but only Tables. Can you please confirm this?

    For incremental load, we may use MS SQL Server 2012 CDC tables. If Views work properly then CDC may not be an issue. Otherwise we don't think Attunity Replicate can read transaction logs, where we can apply our complete transformation logic.

  5. #5
    stevenguyen is offline Senior Member
    Join Date
    May 2014
    Posts
    297
    Rep Power
    6
    Quote Originally Posted by brian View Post
    Thanks Steve for your replies.

    We have cases where we need to join many tables. Again sometimes we need outer join, sometimes inner join. So lookup is not the exact solution for us. Rather full flexibility with SQL could be more preferable.

    We planned to create Views to execute required join queries and then then use the views as source. But we are shocked because we don't find option to use database Views as Source, but only Tables. Can you please confirm this?

    For incremental load, we may use MS SQL Server 2012 CDC tables. If Views work properly then CDC may not be an issue. Otherwise we don't think Attunity Replicate can read transaction logs, where we can apply our complete transformation logic.
    Hello Brian,

    Replicate is designed to move data from source table to target table. View can not be use as Source.

    It would be best to have Replicate copy source tables to target, then create a view on the target that does the join and present the view to the end users rather than the individual tables.

  6. #6
    Hein is offline Senior Member
    Join Date
    Dec 2007
    Location
    Nashua, NH - USA.
    Posts
    152
    Rep Power
    12
    >> We planned to create Views to execute required join queries and then then use the views as source. But we are shocked because we don't find option to use database Views as Source, but only Tables. Can you please confirm this?

    Replicate works by mining the database change logs (Redo + Archive for Oracle; Active Tlog or a Backup Tlog for SQLserver).
    The databases record only low level information there, just enough to undo or redo a transaction.
    The databases will only record the changes to the base tables.
    For the database there is not need to tie that back into a view which might use the basetable, and so they don't.
    Thus Replicate can not know, from the transaction log.
    It just replays the change on the target table matching the base table.
    If the same, or a similar, view exists on the target, then that view too will see the updates, without de-normalizing the data in the process

    Options to explore are
    - triggers on the target side to update a 'materialized view'.
    - "store changes" instead of "apply changes" on the target to procedurally perform complex transformation on the target with all the resources it may have available.

    Regards,
    Hein

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •