SQL Server Guru's - How Do I Warehouse A Database

[cotton31]

Apologies if I'm using the incorrect terminology.

Our main application has been warehoused by a third party and I want to learn how to do this.

Is there a specific application I need to use? What are the general guidelines?

A step by step guide would be brilliant!

Thanks!

 

[Ranked1_of_1]

When you say that your main application is being "warehoused" I am guessing that you mean that they are going to create a Data Warehouse with it. Can I ask what your main application is? (ERP System?)

A Data Warehouse is a database that has been designed with reporting in mind. It can be used separate the reporting function of a database from the day to day transactional side. It could also be used to house old data when you are changing to a new "main application". It can be used to house data from multiple systems into one database - again to improve reporting. It can used to facilitate advanced reporting such as Business Intelligence.

Step by step guide - more like a few years experience required.

 

[kierenharrold]

You need to completely design a database from scratch with data warehousing in mind. This is the easy part. The hard part is the ETL process, extract transform and load the data from one database into the data warehouse.

There is no step by step guide sadly, you will also need to have a good understanding of programming as you will need to create scripts to transform data, fill in missing data and load in back into the database. You can't do this manually unless it's a really small database.

 

[cotton31]

Ok I think I need to back up a bit.

We already have a data warehouse which I use to create reports etc. but I didn't implement it. This contains data from one of our main systems.

I'd say I'm compentent in SQL, but I've never been exposed to the need to 'warehouse' a database myself, so I'd like to learn.

I do understand for the mostpart what a warehouse is, so I suppose a step by step guide was not the right thing to ask for.

I suppose I'm asking for the methods behind it e.g. is it really as 'simple' as scheduling them to run as jobs in SQL Server Agent?

Thanks,

 

[durbs]

I suppose I'm asking for the methods behind it e.g. is it really as 'simple' as scheduling them to run as jobs in SQL Server Agent?

Thanks,

No, thats just copying data elsewhere. Data warehousing is a HUGE subject and its architecture depends on the individual business requirements so there is no generic 'this is how data warehousing works' explanation. In the current Co i'm doing work for there are dozens to hundreds of different applications and transfer mechanisms involved in their warehousing systems. For example there are apps to generate financial hierachies from the various operational systems, transfer those hierarchies, clean the hierarchies, validate the hierachies, split the hierarchies to form new hierarchies, merge hierachies to form new hierarchies, move those hierarchies between marts, report against those hierarchies etc etc etc.

In its simplest form, yes, it could just involve a SQL job moving and transforming data to a seperate reporting SQL instance. In the real world though, it generally means paying SAP contractors silly money

 

[Dj_Jestar]

"data warehousing" is simply the replication of data onto a system used for reporting.

You absolutely, 100% do NOT need to start from scratch, that is the ladt thing anyone will want to do. In fact it is the complete antithesis for making a DW in the first place.

The entire point is to leave the operational DB normalised and optimised for operations, whilst the DW is normalised (or, often as is the case in reality, not normalised at all) for reporting and analysis.

The sole purose of a DW to is stop reporting and analysis queries, which will often be "expensive" queries, and locking queries, from locking the operational tables which will prevent users from completing tasks.

The simplest form of a DW is to just replicate the database. More complex forms exist, such as event subscription (i.e. Only add events from operations that I care about to my db).

 

[cotton31]

Slight thread revival!

That's my reason behind wanting to know more really, we pay someone silly money to to warehouse our main application for reporting purposes and I want in!

So to start myself off, we have another smaller application which isn't warehoused, and I'd like to have a crack at doing so.

I've started with some SSIS packages and transferred a few tables across, but wondered if there must be more to it than this e.g. performance tweaks, configs, naming conventions etc.