Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

Situation

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.

Question

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.

share|improve this question

3 Answers 3

up vote 7 down vote accepted

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.

share|improve this answer
    
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 Feb 14 '11 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 Feb 14 '11 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.

share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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