I'm fairly green when it comes to this field. I'm a data analyst at a small hospital 8 months now, out of a 2 year degree in a new career field for me.


We have a Cobol "database" (I use the term database lightly...). It's a proprietary Hierarchical Database. We have a secondary software solution that provides ODBC SQL layer on top of it for outside access via a standard'ish interface. It's got what I think is SQL 99 compliant commands.

COBOL Database to SQL leaves a lot to be desired. Index's are set in stone. So if I want to do any query that isn't keyed on the one or two fields that are indexed (Index = Patient numbers. So if I want to query on Admit/Discharge Dates... forgetaboutit)... it's dog slow. Joins turn a 5 minute query into a 45 minute query. To many queries slow the REST of the system down causing bad things.

While the system DOES have an internal query system, it is by no means something I'd consider complete. It has it's strengths and it's weaknesses, but the main problem is co-operation with the outside world that I need to send reports too.


What I'm trying to build... is a TSQL Data Warehouse. I want to create a process that pull the data into a SQL environment that I can control (add index's, use modern stuff like case statements, doesn't take 15 minutes for "simple" queries).

I'm wondering what are some good resources, tutorials, tools that the experts would suggest. My ideal situation would be a "historical" load of all data into a T-SQL database, followed by weekly loads of new data. It wouldn't be ideal for "real time" reporting, but the bulk of my reporting needs would be met with "slightly" old data.

I've been messing with ##Temp Tables, Merge into, update, etc trying to see what the times are for loads and updates and what not... but before I get too deep, I'd like to just get some general advice.

3 Answers 3


I would recommend Building a Data Warehouse With Examples in SQL Server by Vincent Rainardi, as this covers SQL Server specifics. The Data Warehouse Toolkit is also an excellent and practical guide, but isn't platform specific.

  • I've had very good luck with Apress, so I'll have to give that a look at. I also like the idea of a platform agnostic approach, although we do use SQL-Server in house.
    – WernerCD
    Commented Feb 14, 2011 at 19:41
  • I hadn't thought to look for books specific to Data-warehousing, amazingly enough. Thanks for pointing out the obvious to me.
    – WernerCD
    Commented Feb 14, 2011 at 20:11

I expect the way to do this is with SSIS, connecting over your existing ODBC. It's tailor-made for pulling data from diverse sources into SQL Server, for subsequent consumption by, for example, SSRS. My advice would be to do a bit of background reading on these tools and see if they can be applied to your situation, if not you will at least then have a clearer idea of what features you need that it doesn't have and can go shopping with that.


Knight's Microsoft Business Intelligence 24-Hour Trainer is a book DVD combo. This is another good place to get started in Microsoft BI.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.