Q: Moving a DV from SQLServer to Teradata?

I was recently asked this question, about the best practices, thoughts, and if there is anything they need to watch out for when moving a Data Vault from SQLServer to Teradata.  This post covers a little bit of this information.

There really isn’t much you need to do when moving a Data Vault from SQLServer to Teradata, except, choosing the Primary Index (which as you know, lays out the partitioning in Teradata).  Ensure that the primary index of the link table, and the primary index of the Hub match (for the largest table), so the least amount of data moves across the nodes.

If you don’t / can’t lay out the Primary Index the same way, you will need to add a join-index to ensure the join capacity.

Other than that, turn on compression and watch the tables run with super high performance.  Parallel queries should make it easy to get the data out, and I would encourage you to expiriment with Virtual Marts (view structures).

Hope this helps,
Dan Linstedt

Tags: , , ,

One Response to “Q: Moving a DV from SQLServer to Teradata?”

  1. Juan Jose 2010/09/23 at 7:47 am #

    On the satellites attached to a hub you should use a Non Unique Primary Index on the (imported) Hub key. Then the hub id distribution of the hub and satellite are the same and joining them goes in parallel.

    O, and be sure to update statistics on small (control) tables. Teradata assumes big tables with lot’s of data!

    Use range partitioning in the satellites.

    Just my expriences.

Leave a Reply

*