… to borrow a phrase from the old Monty Python show. Not that working with OpenSim hasn’t been a lot of fun, but a new opportunity has come along which promises to provide an escape from Programmer Hell, so that is the course I intend to pursue for the near future. There seem to be quite a few really good blogs on the subject of OpenSim right here on WordPress, so you should find the subject covered pretty well. Work on OpenSim is not going to be forgotten here, but it is going to have to be put on a somewhat lower priority now.
For the foreseeable future, this space is going to be devoted to implementing databases in websites, together with supporting data warehouses. There will be a lot to say about the magic buzz abbreviations OLTP and OLAP, what they mean and how they can be made to work together. We’ll be looking at doing this with ASP.NET and SQL Server, but MySQL and its new companion data warehouse software are going to get a lot of exposure as well.
Those of you who are out scrambling in the job market are likely to be asked the question, “So what process did you use on your last job?” They want you to say Rational Unified Process, or, God Forbid, The Agile Process (a.k.a Total Chaos). Generally these, or some other named processes are an excuse not to think. However, in the data warehouse field, there actually is a truly rational process for getting the job done. It is known as the Kimball Life Cycle. It is not the only process favored by the data warehouse crowd, but it is a really organized way to get the job done, and it is going to be mentioned a lot here.
Data warehouses need a lot of data to be useful. The source for one of the projects discussed here will be day prices for some Nasdaq listed stocks. Another will be some data gathered online at Second Life. The stock project will come first.
For those of you not particularly familiar with data warehouses, here is a bit of a preview. In the ordinary course of doing business, On Line Transaction Processing (OLTP) is the collecting of data as business is being transacted. It’s the recording of your purchase on Amazon.com, it’s the entry of your account information as you sign up for an account on the local toll road authority, and it’s the buying and selling of virtual land you do on Second Life. It is the capturing of minute to minute business data during the business day. On Line Analytic Processing (OLAP) is the process of going back over past recorded data and extracting and understanding trends in the data so as to improve how business is done.
The usual examples provided in OLAP studies involve retail sales. It is understanding which segment of the customer base favors which product. It is which sales region moves which product. Retail sales benefits from this kind of analysis, but so do plenty of other types of activity.
In the case of web based businesses, the OLTP is likely to be done with a database on the web server, as transactions are made. OLAP on the other hand, is best done offline on a separate database system. The complete process must include structuring the data warehouse, and setting up the process for regularly extracting, transforming, and loading data from the online database to the offline data warehouse. SQL Server 2005 has done a pretty good job of integrating the functionality to do this, and it works well with Visual Studio 2005 in creating that functionality. We’ll see how that works in the first project.
So, to those of you who will be moving on to other blogs, sorry to see you go, but sometime perhaps you will come back for a visit. For those of you who stay, and the new folks we pick up along the way, here’s hoping you enjoy your stay.
Tags: Data Warehouse/Business Intelligence, Kimball Life Cycle, MySQL, SQL Server
