Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I have been tasked with redesigning, or at worst optimising the structure of a database for a data warehouse.

Currently, the database has 4 other source databases (which is due to expand to X number of others), all of which have their own data structures, naming conventions etc. At the moment an overnight SSIS package pulls the data from the various source and then for each source coverts the data into a standardised, usable format. These tables are then appended to each other creating a 60m row, 40 column beast!.

This table is then used in a variety of ways from an OLAP cube to a web front end.

The structure has been in place for a very long time, and the work I have been able to prove the advantages of normalisation, and this is the way I would like to go. The problem for me is that the overnight process takes so long I don't then want to spend additional time normalising the last table into something usable.

Can anyone offer any insight or ideas into the best way to restructure or optimise the database efficiently?

Edit:

All the databases are MS SQL Server 2008 R2

Thanks in advance

CM

share|improve this question
    
SQL Server 2088? WOW! Even in the future they have these kinds of problems! ;) –  FrustratedWithFormsDesigner Mar 28 '12 at 15:26
1  
You might also ask here: dba.stackexchange.com/questions (but don't cross-post! ask the admins to migrate) but I'm not sure this actually needs to be migrated... Other DB questions have been answered here, as well as on stackoverflow.com –  FrustratedWithFormsDesigner Mar 28 '12 at 15:27
    
Good spot! Ill leave it here for a while and see what happens, or bow the greater wisdom of the community and ask for it to be migrated. –  CatchingMonkey Mar 28 '12 at 15:30
1  
First thing I would look at is the SSIS pacakge. SSIS Is faster than fast when you do it right and horrible when you do it wrong. Look for things like merge joins. Look to see if you are recreating all the data insted of only the data that is new or changed. –  HLGEM Mar 28 '12 at 17:48

3 Answers 3

For OLAP databases, normalization is often not the best approach - this is completely different from classical OLTP databases. The structure of your tables should be optimized for the queries you are going to run. I recommend the Wikipedia articles about star schema or snowflake schema, those are patterns for a good OLAP database design.

Here is a book about the topic I can recommend:

http://www.amazon.com/The-Data-Warehouse-Toolkit-Dimensional/dp/0471153370

Something you did not write (but really ask yourself) is why you actually want to restructure the system. Just because it is denormalized and you think this is not "best practice"? Or do you suffer from real performance or storage problems? If it is only the first reason, you should first read something more about good OLAP db design before changing the system.

share|improve this answer
    
Or you should hire someone who has actual knowldge of OLAP design. This is not something for the amateur SQL developer. –  HLGEM Mar 28 '12 at 17:45
    
Thankfully we do have a specialist and experienced OLAP developer. –  CatchingMonkey Mar 28 '12 at 20:47
1  
@CatchingMonkey: so what are your goals for your redesign - you wrote "normalization", but why? Better maintainability? Better extensibility? Less use of disk space? Increased speed of the ETL process? Increased speed of the queries? What kind of optimization do you have in mind? From your comments I can only make some wild guessing what you are after, and what problems you currently might have. –  Doc Brown Mar 28 '12 at 21:25

Hard to say without knowing more details, so here's some suggestions:

  • Does the extract from other sources HAVE to happen overnight? Is it possible that at least some of the data will be consistent enough at various points throughout the day? That might let you do several smaller data pulls 3 or 4 times a day on a schedule, leaving more free time in the overnight to normalize. Or, you could even start the process of building the OLAP cube in little bits, assuming it's possible with this data. Or go even further and make it a near-continuous process throughout the day.

  • Is the big table over-indexed? Maybe too many indices is slowing down inserts.

  • Would partial normalization be OK? Maybe only normalize one or two important columns.

  • Would it be OK if the OLAP cube weren't available until later than it currently is?

  • Can you get a budget to buy better (for this job) hardware, assuming there's a bottleneck that can be resolved by hardware upgrades?

share|improve this answer
    
1) Yes, it does have to happen overnight. The other servers are production servers used for modelling and reporting and their DBAs get angry if we touch them during business hours. 2) There is currently only a PK on the large table. 3) This is certainly a possibility I have been thinking about, but I'm not sure if this is the most efficient way to let the data to consumed by the cubes and web app. 4) The servers are certainly powerful enough, I think its just the lack of best practice over the years causing inefficiencies. –  CatchingMonkey Mar 28 '12 at 15:41
    
@CatchingMonkey: Fair enough. Where do you think the primary souce of inefficiencies are? In the table structures? In the code that extracts from source and populates the cube? Somewhere else? –  FrustratedWithFormsDesigner Mar 28 '12 at 15:44
    
Primarily in the fact that all the data needs to be standardised I would say. But then the whole system has flaws of varying severities, lack of intelligent indexing, table structure, code etc. My thinking was to take a step back, and try and implement something extensible, with best practice in mind, rather than fixing and making do. –  CatchingMonkey Mar 28 '12 at 15:49
1  
Would it be possible to normalize the data during the extract/insert-into-cube operation, instead of doing it as a separate stage? I imagine that normalizing incoming data in the ETL code would be much more efficient than a single, big post-insert "normalize/update" step. –  FrustratedWithFormsDesigner Mar 28 '12 at 15:52
1  
I thought of that, but would it noto be difficult to say normalise one source during the ETL, then add to those normalised tables during the next sources ETL, and the same for the next etc. It sounded like an odd way to go about it. Could be wrong of course! –  CatchingMonkey Mar 28 '12 at 15:56

Since your time restriction is only on connecting to the production databases, pull the data from them into some sort of staging area with as little processing as possible.

Then you can start the process of normalizing the data as needed.

There are some drawbacks here:

  1. Storage space required for the staging area (of course you can dump this when finished).
  2. This may take longer, so consumers of this database have to wait longer.
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.