Solved

Migrate DimTable from Old SQL Server DW to TX

  • 30 April 2023
  • 6 replies
  • 79 views

Badge

Hi Friends, 

I contact you because I have to migrate a Dim Table with SCD 2 from the Old SQL DW to TX and after that set the TX Dim to the following working, Is there any process to do that? 

Thanks  for your help 

Ignacio

icon

Best answer by Thomas Lind 1 May 2023, 13:54

View original

6 replies

Userlevel 6
Badge +5

Hi Carlos

Not really, we can upgrade projects/instances with a history table to a new version, but moving the actual data from one database to the other is not something we can automate.

If you move a project to another repository and want to keep the data in some history tables there is a specific procedure, that can be automated with the correct script, which I unfortunately do not have.

The procedure is as follows.

  1. Move or import the project to a new repository.
  2. Create new databases to store the data.
  3. Deploy, not execute, all tables.
  4. Locate one of the history tables in the new database and check the extended properties of the table.
  5. Copy the guids from there and overwrite the original databases extended property guids.

     

  6. Now you can connect to the original database in the new project.
  7. You will have to do this for all tables you have history  in.

There was a script that could do this, as it will take a while to do for all tables.

The other option is to export and import the data with a script, to valid should be enough.

 

Userlevel 6
Badge +5

HI @ignacio does Thomas’ comment answer your question? If so please help us by marking the best answer above. If you have any follow up questions please let us know 

Badge

Hi team!

I did the following task:

  1. Developed all the logic of the dimension table in TX
  2. Used the import data of SQL Server, and copi de source dim table to DSA. 
  3. The target dim table is in MDW with setting HI, I used source dim table in DSA as mapping, y ran the execute.
  4. After the load, I updated the values in  [SCD From DateTime], [SCD To DateTime],[SCD Is Current] to match the source dimension.

Let me know your thought

regards

Carlos Ignacio Aguero

Userlevel 6
Badge +5

Hi Carlos

If it worked like that it is a fine method. I haven’t tried it before as I would usually get errors about missing constraints or similar.

You used this option, right?

 

Badge

Hi Thomas, 

I would like to show you how I did it in a call. my email is ignacio@datamartin.ca.

Regards

Carlos Ignacio Aguero

 

Userlevel 6
Badge +5

Hi Carlos

Sure. I have sent an private message to you.

Reply