How to update a datawarehouse after migrating to sql2005 from sql2000

Question

Hi Guys, I apologise for asking basic questions but I'm new to Analysis Services and dimensional data.

My current situation is, I have a sql server2000 database with a warehouse which was built by a previous developer. The warehouse and the transactional DB are on the same physical server. The warehouse is filled nightly by stored procedures.

I have a new sql2005 database /server which I copied this warehouse onto. So now the warehouse I want to use to report from is on a separate server from the database and is sql2005.

My question is, what is the easiest/best way of populating the warehouse...Is BI integration services useful? What is the best method of reporting this data, I intend creating cubes, do I do these in Visual Studio?

Can you recommend any good tutorials?

I believe there are issues with linking 64bit sql2005 and 32 bit sql2000, which restricts the use of linked servers for SP's??

Thanks

Answer 1

Whoa there SQL cowboy, that's about ten questions in one ;-)

To summarise:

Yes, SQL Server Integration Services is indeed the best/simplest way to go in my opinion. Provided there is not an existing import process (perhaps based on Stored Procedures) that you can use/update.

With regard to tutorials, Microsoft has available detailed tutorials, both for Integration Services and Analysis Services.

SISS Tutorial http://msdn.microsoft.com/en-us/library/bb522537.aspx

Analysis Services Tutorial http://technet.microsoft.com/en-us/library/ms170208.aspx

My recommendation to you is that you get yourself a copy of “The Microsoft Data Warehouse Toolkit”. It provides a complete Data Warehouse project walkthrough, including how you go about using SQL Server Integration Services to manage the ETL tasks. Having read the book myself, I can vouch that this will be a significant help to you.

http://www.amazon.co.uk/Microsoft-Data-Warehouse-Toolkit-Intelligence/dp/0471267155/ref=sr_1_1?ie=UTF8&s=books&qid=1242893751&sr=8-1

Once you have digested this information and get to a point where you have specific questions, let me know and I will be more than happy to help.