Data Warehousing

Is anyone doing it?

[cfx211]

Over the last three years with my current company, I have seen my department grow from me to three. When I first got here, there was only our production database to run queries against. We soon set up a query only database that was synched up to the production database nightly so that it was mirror image as of midnight. As time went on, we realized that we had a lot of data on our production database that was no longer needed for the site to function so we got a third database to store this historical data. Thus our data warehouse was born.

In the two years since we have gotten the third database, I have added a series of summary tables to it that breakdown a lot of our historical data and put it into a more usable form. These tables grew slowly over time from basic counts to more complex user based summarization. These tables at first were somewhat isolated and built to accomplish specific tasks, but as more were added everything started to connect to each other.

The connection of these tables and the ability to cross one thing like where did an account come from to how often do they visit the site is where this effort really pays off. I used to manually create a lot of these tables everytime I wanted to do some analysis, and now the time savings allows me to spend more time figuring out the site.

At this point, we are ready to make a serious commitment to data analysis to grow the business and I am about to fill in the gaps in our data warehouse, plus get ready to handle a redesign of our tracking system. Since I don't have any experience with data warehousing except for what we have grown into, I wanted to see if anyone else out there has had similar experiences or wanted to talk about building out structures to easily understand what is happening with your site.

[iblaine]

Data analysis is a broad subject and there are any number of ways you can go about reaching your goal. If your company is less than 50 people then my suggestion is build your own tools. Here are some basic basics.

There are two big parts to data warehousing, 'heavy lifting' and CRM tools. Heavy lifting refers to the process of reading log files, sorting data and aggregating data into tables. This turns into a science when you need to deal with thousands of records a minute and apply complex business logic to each transaction. Examples are sql loader and informatica.

Then there are CRM tools. Your basic data anlysis tools are going to pre package sql queries and give the user a gui so anyone, without any training, can hit your database for reports. Then there are more complicated data analysis tools that will pivot tables and create cubes. Examples are business objects and cognos, which is a ridiculously powerful and expensive tool.

At my company we built everything ourselves and I think this is the way to go for most people on WebmasterWorld. You can do a lot with sql loader and a cheesy gui tool that allows you to easily create, edit and execute sql queries. I guess it all comes down to your requirements, budget, and time.

[bufferzone]

I don’t, but I wish I could.

There is no doubt in my mind that logging and analyzing the data is a potential goldmine if you get it right. Unfortunately you need a certain volume of data before a data warehouse solution becomes feasible and economically doable.

My advice is. If you can afford it and have the data volumes for it to make sense, go for it , I’m sure it will be worth it in the (not so) long run

[cfx211]

We build everything in house, and our collection of data is pretty advanced. A lot of the problem here is putting all of that data into a usable format.

CRM or other OLAP tools are not the answer for us, at least not yet. I feel that they are a layer on top of information that is not necessary in a lot of companies, and that resources should be better spent organizing the data into summary tables first.

While we have about 100 people in the company, data requests only come from a few small groups and go through my team which writes and runs all the queries for the company.

At this point, we need to do a better job of summarizing all the data we collect so that it is easier to query.