Do I need a warehouse?

Hi chaps,

I have the following scenario and aren't sure of what approach I need to take to solve it. Any advice would be welcome (im very new to warehousing).

Scenario: I have a high transaction OLTP database (SQL Server) containing 20+ inter-related tables. It is all highly normalised. Over a year I am looking to accumulate something like 10+ million rows of information (most likely more).

The database is used extensively by an application (ASP.NET) which allows users to view (and in some cases update) the information in the database. The majority of the data is inserted by BizTalk.

Once an entire transaction is completed (which may take for example 2 weeks), it will not be changed in the future, but will still need to be reported on. I.e. View All Orders in March 07

Problem: As my data volumes grow, the performance of this database will undoubtedly begin to crawl, for both selects and inserts/updates. To solve this, I would like to move the completed transactions into an achieve (i.e. my datawarehouse) that will store the information in a denormalised state which the application can continue to use to perform queries (which I hope will be very fast).

I understand this isn't a typical use for a warehouse, namely I am not summing numerical figures or interesting in aggregating information based on measures/hierarchies.

Is what I want to do a correct approach, or can someone provide a better solution?

Any help would be greatly appreciated,
TM

Hello. You can build your data warehouse as highly normalized as you prefer. You can control the aggregation of information in one or several data marts that you can build from views or in tables in a new database.

Data warehouse and data marts(aggregated denormalized) informations do not have to be the same.

In the data warehouse you will normally remove some tables in order to keep the number of relations down to a minimum. You will also translate OLTP-columns to more business like names.

Still, if you build a copy of the source system in the data warehouse you will be able to remove historical transactions from the source. That will help performance in the source system.

Also, analytical queries will not compete for resources with transaction registration.

HTH

Thomas Ivarsson

|||

So Thomas,

If I were to build a denormalized warehouse could I then build a UDM cube on top of it without building an OALP fact/dimension type structure?

|||

Hello JohnSLG. Yes, I think you can. You can build cubes on top of OLTP-systems.

You can have all information like dimensions, hierarchies and measures in one table and build a cube on that.

Best practice, however, points to a data mart on top of a data warehouse.

Kind Regards

Thomas Ivarsson

|||

Thank you for your response, greatly appreciated.

Could you also point me to where I can get a grounding in general warehousing theory? Then build onto this with Analysis Serviecs.

Kind regards

TM

|||

Here is a good link: www.kimballgroup.com

Regards

Thomas Ivarsson

Distributed SSIS server setup

I work in my organization's warehouse team and we are rearchitecting our hardware. One of the ideas on the table is to devote a server to running ETL. There would be several beefy database servers, and a separate server that would run the ETL (SSIS). I'm wondering if that actually hinders the process.

The DB servers will still do all of the query processing, so that isn't off loaded to the ETL server. Then the recordsets I think would need to go over the network to the ETL server for the SSIS packages to work on. And then another trip over the network back to their destination. Would it be better to place the ETL process on either the source or destination DB server? What could be gained by using hardware in this way as described here?

Thanks.

Hi, its a good question but at the same time there is no perfect answer. It all depends. In a perfect world everything on 1 server that is understressed would be screamingg fast. Unlimited hard drive space (and high RPMs), bucket loads of free RAM, minimal network interaction.

So it depends a lot on how stressed you feel the box would be, AND its expected growth.

Are you running ETL jobs during most of the day?

Are they small but frequent or infrequent but intensive?

Capture performance with perfmon or some other tool of even basic stuff like cpu utilization and free memory for even a day and see where are the peaks and duration of the peaks.

Eggs in your basket and downtime are other things to consider. So a huge down side of the single server is if it smoked out you lose everything.

If an isolated ETL box dies, then all your other operaitons keep going. So now a quest ion for your question is, what is your recovery/backup plan/needs/desires?

Do you have the hardware to have a duplicate of each type of server that you can work into production quickly if need be? Perhaps a good approach is you have the DBs and ETL process on multiple boxes but one is the primary DB server the other the ETL...but if something happened to the ETL server, you could in a pinch get the ETL process up and running fast on the DB box because all the needed bits are there but you just need to get it 'live'. Similarly assume your ETL box has mirrored (in some wya) data of whats on the DB server and then if the DB server has problems you could get that process live quickly from the ETL box.

Again, there is no 1 perfect answer. Hope this was useful food for thought.

|||

A distributed SSIS farm will quite well for micro-batch ETL (sub-day processing). Using Craig's terms, the job resource utilization profiles are "small (data wise), frequent AND intensive", meaning not much RAM per job (200MB), lots of CPU, and lots of jobs (thousands to millions per day). This is a generalizaton, since the there are certainly some packages which run on a less frequent schedule which have different resource utilization profiles.

The distributed SSIS server architecture can scale out nicely too. There is custom work up front to get it going, because there needs to be some type of queue-ing (database table, msmq, service broker queue, file system) mechanism which farms out the work, which operates outside of and interacts with the SSIS infrastructure.

As far as mirroring dimensional data out to the SSIS servers (in an effort to reduce network traffic) something to consider is the use of SQL Compact. SQL Compact is not 64 bit compatible (yet), but it looks like a good option if and when it does have a 64 bit version. If not, something that works right now, is a local SQL installation together with SSIS which each ETL server uses to "assist" in areas like caching of stock (relatively non-volatile) data.