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.

We are trying to implement some kind of Data Warehouse at my current employer.

Most of our data is in a SQL Server 2005, but we might also integrate some Access databases and some databases in a SQL Server 2008 Runtime version (it might matter because of the functionality it lacks). We would like the data to be replicated around once every hour. I wonder if there are people out that already have worked out a solution for this.

Our users would like to make queries to the warehouse from Excel or other apps using ODBC.

Also our amount of data is around 150 GB.

share|improve this question
add comment

2 Answers

You have "data warehouse" in quotes, which could mean one of two things: you're either looking for an actual data warehouse or you aren't (yay, truisms!).

If you're looking for an actual data warehouse, there's an entire Microsoft BI stack available to you as part of your already-purchased SQL Server licensing: SQL Server Integration Services for ETL, SQL Server Analysis Services for creating cubes, and SQL Server Reporting Services and PowerPivot for analyzing data. Doing a data warehouse right is not a trivial activity, even for somebody with a great deal of experience designing and developing transactional databases. The big problem is not so much learning the tools and languages (though MDX is so different from T-SQL that it's in its own world), but getting the right mindset. If you do go down this route, start with Ralph Kimball's Data Warehouse Toolkit. It's a fairly long book and was not written for any particular technology stack, but instead tries to give you an understanding of what makes for a successful data warehousing project. Doing it right could possibly take years even if you already know the tools, because the single most important thing about data warehousing is understanding the end user. You really have to know what the end users (the business people) want, how they want it, how they can understand what they're seeing, and how what data and systems you have can get translated into what they want in a single, consistent manner.

If you're trying to do something "on the cheap," there's a much higher likelihood of failure, to be honest. This is why I'm a little concerned about why you put "data warehouse" in quotes. Yeah, there are easy-to-develop non-warehouse models you could come up with (such as possibly replicating all of the tables over to another server), but they're typically going to fall into one (or more) of a few categories:

  • Too complex for end users. End users typically don't understand normalized data and shouldn't be expected to know SQL or the details of a GROUP BY clause. Your best outcome in that scenario is that IT people continue writing the reports against this new system; the more likely outcome is that IT people continue writing the reports against the old systems and everybody forgets that the warehouse exists at all...
  • Inconsistent data. As you go through this, you might find System 1 says that Order 37 includes an order of a dozen ink pens, but System 2 says it was 14 pens. And System 3 had a free-form text field where instead of putting in a number, somebody typed in "dog." If they are all supposed to represent the same thing, which is correct? Finding (and working with the business on creating rules to solve) these data inconsistencies is a key part of building a good warehouse model.
  • Inconsistent grain. If one system has order lines and a second only has orders, there are ways to reconcile this, but if you simply throw the tables out there and leave it to the end user, they may come up with the wrong method of reconciliation (i.e., one which gives incorrect results) without knowing it.

These are among the many problems you'll run into in the world of business intelligence. Good data warehousing (be it Kimball-style, Inmon-style, some hybrid, or something else) doesn't avoid these problems, but instead gives you a mindset where you can understand and solve them. Rolling a cheap-and-easy solution may let you paper over the problems in development, but we don't measure success simply through getting out of development; people have to use it for a warehouse of any type to have value.

Hourly updates isn't necessarily that big of a problem for a good warehouse, as long as you have the hardware necessary to do the job. You can create SSIS packages to run on SQL Agent jobs (or through any other kind of scheduler, honestly) which perform the necessary tasks (pulling data from source systems, doing any transformations, doing any necessary staging, and building Analysis Services cubes), so scheduling isn't a problem if the individual processes are fast enough.

share|improve this answer
    
Kimball's books looks like a very good reference. I used Data Warehouse in quotes because we mainly want to replicate the data stored in our different servers to a central one that we can access to create our reports and occasional queries. The main reason for that is that we can take some load off our production servers. That's why I believe is not a real Data Warehouse. –  tou Nov 2 '12 at 16:35
    
Yeah, that's kind of what I was a bit afraid of... What you're talking about definitely wouldn't be a real data warehouse, but it could be useful when you simply need to scale out. You might use Replication (msdn.microsoft.com/en-us/library/ms151198.aspx) on your 2005 & 2008 databases, though keep in mind the things that can break replication. You'd also need a separate Access solution, maybe using SSIS jobs. This won't give end users easier access to data, but could help performance. You could also use a local report schema & SSIS if the problem is locking and not high CPU use. –  Kevin Feasel Nov 2 '12 at 16:52
1  
For the last part, if your reports/queries are causing excessive locking and you've already tuned them to the point that you can't go further, you could use SSIS to create a mini-schema for reporting, one which may be a bit more denormalized and fits the reports better. Only SSIS would update the reporting schema, so the denormalization wouldn't be a big problem. That would reduce the burden on your primary tables (and could go on a separate instance if need be), and if you do go ahead with a real warehouse, you could pull from these tables instead of the write-heavy tables. –  Kevin Feasel Nov 2 '12 at 16:58
add comment

You will need a way to differentiate from the different sources but if you did that, you could always use replication with a filter. That way you could merge all of the data from different SQL sources without stepping on each other. I actually set something like this up but it never made it past testing phase and even then only with 10 different databases but it worked.

Another method that I used was using Merge in a SSIS package. The merge statement is only available for SQL 2008 but the merge in the data flow transformations biggest advantage is that you could use different sources. This will be about the only way you can use your Access databases [just typing that leaves a bad taste in my mouth!]

The third method that we use is good old fashion complicated sprocs attached to jobs that pull from multiple sources. Its messy but it works.

Personally my favorite is the replication method. It's fairly easy, runs on its on, isn't prone to failures. I have hundreds of different merge and transactional replication jobs set up on our servers and I have yet to encounter an real issue.

I do understand the "data warehouse" in quotes, that is what we currently have where I work and I would love to move to a real warehouse but we will have to wait until bigger projects finish up. Building an OLAP system takes time and education, not only of the IT staff but also all of the "C" management levels.

share|improve this answer
add comment

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.