migrating our DW from DB2 platform to SQL Server

[shell_r]

My company has plans to migrate our data warehouse from a DB2 environment to SQL Server 8 over the next few years.  We're using Cognos 10.1 currently for all our BI needs, plans to upgrade to 10.2 next year. 

I'm wondering if anyone has experience with this type of migration and did you run into any major issues with your FM models?  Did you have to make a lot of changes?  Were there a lot of data type issues, calculations?  We're trying to get an idea as to how much work we need to plan for in our resource planning and I'm having trouble finding information on this just to give us an idea of what to look for.

Thanks in advance! 

[cognostechie]

I think the major chunk of the problem would not just be FM but also the ETL side and that would also depend on which ETL tool is used. The tools behave differently in terms of NULLS, Blanks, data types etc. which can result in data issues, wrong surrogate keys etc. and troubleshooting that may take significant amount of time.

For FM, if the model was created as per the practices recommended by IBM then there shouldn't be any problem but I have seen very few environments which have followed those practices. Most people do modeling with the approach 'Let's just make it work for now so that I can score points with my boss, who the hell cares about tomorrow. We can always say that our environment is very complicated and hence the best practices will not work for us'. If FM has query subjects which have embedded codes specific to DB2 then those may need to be changed. Try to keep the Business Layer same. That will reduce the migration effort to the minimum

[bdbits]

Data types will be the biggest data-specific thing to watch out for. I agree that you should allocate lots of time for ETL rework. If the database layer is doing "select * from sometable" and you were not calling on DB2-specific functions, you may not have a lot of direct modeling issues.

"SQL Server 8" - you mean SQL Server 2008? Version naming changed after version 7 and incorporates a year into the name. If you can, since you are migrating anyway I would try to migrate to the latest version, or at least go to 2012. Mainstream support from MS for 2008 has already ended.