Datawarehouse Restructure.

Kai

Howdy,

I work with a medium sized database with various different databases assigned to specific data. In the last year its gone from a small simple ad-hoc reporting datawarehouse for reports to a large monster of a thing.

So as one of the SQL guys in the office ive decided to call a halt on things for a week in order to sort this out into something simpler and more manageable.

What im going to try do is break all the larger dbs which contain several datasources down into smaller more concise dbs each contain one datasource / type of data. Hopefully this will make our report building process much more simple and straight forward , allow us to better manage the data we already have and also provide scalability for future datasources coming our way in the future.

Without going into too much detail thats the situation. Now what im wondering is, Is there any online or other sources of information which give advice on this type of thing. Im sure there must be some kind of standard e.g Naming standard that will help. I dont need help with the nitty gritty stuff its more of an Overall plan of how an efficient system works.

Anyone have any suggestions ?
Sorry if im being vague, i can provide more detail if needed.

democrates

I've never heard of any industry standard, seems each rdbms vendor has advice and many large users also publish standards for their institutions. You can google for these and see what looks like a good fit so I won't try to replicate any here.

From my experience the dba bible is the data dictionary. Not necessarily the one in any particular rdbms which is for that product, but one which you use to cover all your systems. It can be as simple as a few web pages on your intranet which developers can easily refer to. Any naming convention is better than none, just define naming/datatype standards for all your databases, tables, columns, views, stored procedures etc. Then programmers should validate all incoming data to the destination column specs for example.

If it's not feasible to change some existing systems naming due to licencing, a large codebase, or paper documents etc, you can still document how one schema maps to another. It's also handy to have good mapping docs when assimilating new systems.

If any of this is to work properly you need management buy-in to the concept of dba standards which all developers must adhere to. The other side of course is to sell it to the developers. Think about the benefits for the business and developers, and make sure they all buy in. Without this, you can do a big cleanup now and find yourself back in the same boat in a couple of years time.

Good luck, and enjoy.

theexis

Have you considered an OLAP solution for your reporting rather than restructing existing datasources? You don't mention what DBMS you're using but most have various OLAP solutions available for them out of the box.