Sandbox (test database) to Live Database tricks

Lately I’ve been spending quite a bit of time with a client moving to multi-company use of Vision.

We are moving from one company to two.

For this process I have set up two test databases to play with, one which I went through the painful process of going multi-company with and got all the way to where there were no errors on the analysis report, have posted some time to some projects that crossed companies etc…

and one database to test the entire process I created for moving to multi-company quickly. I’ve done this because while the first time around it took a few weeks of calendar time to work out all the cleaning up needing to be done, the configurations and make decisions about how the process should be done, we will only have a two days to move their entire database from where it is now to multi-company so that come Monday morning the process is complete, flawless, and bug free.

One of the items that needs to be done when going multicompany is to assign new org values to profit centers (organizations). It can be quite tedious to hand enter all those profit center values when you’re moving some to company A and others to company B. If you go through the Vision interface, you have to do the changes by hand… this process is not only tedious, but is also error prone, especially when you’re dealing with 50+ organizations.

So how can you write a script to populate the key conversion table so that it only takes a second, is less error prone, and takes 15 seconds to do the second or third time around as you’re testing your migration process?

You do it like this:

1. using the Vision interface, set up 2 or 3 profit centers to be converted, save changes, do not run it.

2. from query analyzer, inspect the “keyconvertwork” table to see what Deltek generates. It usually looks something like this:

Notice how the “oldkey” field is the existing profitcenter (organization), and newkey field is the value you want it to be.

the Entity field tells the key conversion engine what you’re converting.

the PKey field is just a newid() value that sql generates for you automatically.

Now if you can devise a formula that establishes the new key value from the old, then you can write an insert statement that populates your keyconversion table instead of having to do it by hand.

In our case it would have looked something like this:

insert keyconvertwork(pkey, entity, oldkey, newkey)
select newid(), ‘Org’, org
, case when right(org,2) = ‘IB’ then ‘CAP:’+org else ‘FMV’+org end as ‘NewKey’
from organization

After you run that statement, you open up your utilities/key conversions/profit center (organization) console and it should all be nicely populated with the values you need…. this is of course if you have established the correct formula for converting your old org values to your new ones.

But… what if you don’t have a formula that will work, and you HAVE to just do it by hand?

Then you can still save time and not have to do it twice.  What you do is this:

1. in your test database, set up your keyconversions without running them and get them all tidy the way you like it.

2. archive the setup by storing the results in some other table like this:
select *
into ‘x_keyconversion_archive_20091204’
from keyconvertwork

3. run your key conversion in your test database if you need to

4. when you’re ready to go live and do the key conversions in your live database (assuming that the one you did in the test database worked perfectly) then you just do this:
insert [live database].dbo.keyconvertwork
select * from [test datbase].dbo. x_keyconversion_archive_20091204

So what you’ve done is archived your key conversion then just moved it over to live when you’re ready.  Boom!  30 seconds needed.

Click run, go get coffee.

Have a great day!

Comments or questions for Loren?

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s