Data Migration

Can I get some advice guys?
We have a de-normalized database that is currently fed from Siebel
(CRM). We want to pump in data from Pivotal but there is a slight
problem.
We used the Siebel Table=E2=80=99s identity column for PK in each
corresponding table (contact, account, etc). The record is then pushed
to our Data Warehouse and used for the corresponding dimension=E2=80=99s PK.
My original thought was to simply multiply Pivotal=E2=80=99s identity column
by something like 100,000,000 in the ETL and just pump the data in.
After taking my first look at Pivotal, I noticed that Pivotal does not
use identity columns. Pivotal uses a binary field to uniquely identify
each record.
This is a real mess and I get to be the one to fix it! =EF=81=8C.
We have several cubes and reports in production so I want to try and
minimize the overall impact.
Can you guys help me out with damage control and point me in the right
direction?I don't see a problem. You should be able to generate a new surrogate
key on insert, either in the ETL process or when you load the
dimension. In fact it's conventional practice always to allocate new
surrogate keys in the data warehouse. Of course the surrogate should
never be the only key in a dimension so there should be no problem
uniquely identifying the data row. But maybe I misunderstood what you
are asking.
David Portas
SQL Server MVP
--|||You are going to have to change how your keys are generated, and stop using
the Siebel key in your own DB.
Suggest generating new surrogate keys in your DB for Both Siebel and
Pivotal, and keep the key "mapping" in a separate table,
Create Table KeyMap (SiebelPK Integer Null, PivotalPK Binary(xx) Null,
NewPK Primary Key Identity Integer Not Null)
And then Populate it with all the current existing Siebel records to start
off with:
Set Identity_Insert KeyMap On
Insert KeyMap(SiebelKey, NewPK)
Select Distinct SiebelKey, SiebelKey
From ExistingTable
Change your import process to insert into the key map first, then use
created Identity for the insert into the rest of your data tables...
"daveg.01@.gmail.com" wrote:

> Can I get some advice guys?
> We have a de-normalized database that is currently fed from Siebel
> (CRM). We want to pump in data from Pivotal but there is a slight
> problem.
> We used the Siebel Table’s identity column for PK in each
> corresponding table (contact, account, etc). The record is then pushed
> to our Data Warehouse and used for the corresponding dimension’s PK.
>
> My original thought was to simply multiply Pivotal’s identity column
> by something like 100,000,000 in the ETL and just pump the data in.
> After taking my first look at Pivotal, I noticed that Pivotal does not
> use identity columns. Pivotal uses a binary field to uniquely identify
> each record.
> This is a real mess and I get to be the one to fix it! ?.
> We have several cubes and reports in production so I want to try and
> minimize the overall impact.
> Can you guys help me out with damage control and point me in the right
> direction?
>|||Oh, DOn;t forget to turn off Identity_Insert after you insert all the
existing Siebel Records...
Set Identity_Insert KeyMap Off
"daveg.01@.gmail.com" wrote:

> Can I get some advice guys?
> We have a de-normalized database that is currently fed from Siebel
> (CRM). We want to pump in data from Pivotal but there is a slight
> problem.
> We used the Siebel Table’s identity column for PK in each
> corresponding table (contact, account, etc). The record is then pushed
> to our Data Warehouse and used for the corresponding dimension’s PK.
>
> My original thought was to simply multiply Pivotal’s identity column
> by something like 100,000,000 in the ETL and just pump the data in.
> After taking my first look at Pivotal, I noticed that Pivotal does not
> use identity columns. Pivotal uses a binary field to uniquely identify
> each record.
> This is a real mess and I get to be the one to fix it! ?.
> We have several cubes and reports in production so I want to try and
> minimize the overall impact.
> Can you guys help me out with damage control and point me in the right
> direction?
>|||Oh, Don't Forget to tuen off the Identity_Insert after you insert all the
existing Siebel Records..
Set Identity_Insert KeyMap Off
"daveg.01@.gmail.com" wrote:

> Can I get some advice guys?
> We have a de-normalized database that is currently fed from Siebel
> (CRM). We want to pump in data from Pivotal but there is a slight
> problem.
> We used the Siebel Table’s identity column for PK in each
> corresponding table (contact, account, etc). The record is then pushed
> to our Data Warehouse and used for the corresponding dimension’s PK.
>
> My original thought was to simply multiply Pivotal’s identity column
> by something like 100,000,000 in the ETL and just pump the data in.
> After taking my first look at Pivotal, I noticed that Pivotal does not
> use identity columns. Pivotal uses a binary field to uniquely identify
> each record.
> This is a real mess and I get to be the one to fix it! ?.
> We have several cubes and reports in production so I want to try and
> minimize the overall impact.
> Can you guys help me out with damage control and point me in the right
> direction?
>|||Thanks for the suggestion!
It sounds like that is exacally what I need to do.