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.