Re: Top Ten Errors In Data Wareousing

> Cynic that I am, I suspect that data warehousing is just another jazzed up > buzzword for something we have been doing all along. Sort of like voice > mail is the new term for answering machine. Not quite the same thing, I > know, but not significantly different. I had the saem feeling back in October when I was asked to build one. I sat through a 4 hour video-tape on the subject by Richard Irwin and was quite impressed as well as jazzed on the whole topic by the time it was done. Essentially there are a number of problems with 'operational' systems - systems which handle day-to-day tasks. 1 - they are designed to do a job, not to satisfy user queries. 2 - they are built with well-delineated query requirements (37 reports e.g.) 3 - getting a different slice of data out of them is a pain. 4 - they are 'functional silos' in a company such as ours there are all sorts of different functions who throw data over the wall to the next function. That may work very well for what they are doing, but when it comes time to take a look at all of the data together to try and draw out trends or make decisions - its very difficult. Especially given that they use different applications, different OS's etc. 5 - Definition and ownership of data elements. Part_number is one thing to one person and something else to another. Component_number, product_number are the same thing, but not always. Who owns it and who is responsible for it? With multiple non-communicating systems these things get pretty out of whack. 6 - Time element. Data from system a is as of day x, from system b, as of day y. You run a report one day and the data change the next. Why did you make the decision that way? Where is yesterday's data? 7 - 'Operational' systems as a whole are geared to producing something, not stepping back and looking at how/what the company is doing. 8 - Priorities. 'Operational' systems are 'transaction processing' oriented. They care about the speed of transactions. A data warehouse user doesn't care if their request takes 15 minutes, they just need to be able to formulate a request easily, quickly and without knowing 87 different arcane rules. I haven't stated those very well, I've asked Informix to let me present a paper at the WW user conference by which time I hope to have a better definition of the problems. The data warehouse is a collection of data from diverse systems which is integrated (definition AND data), Write-only (so old data never goes away), geared towards user requests, NOT transaction processing. Instead of forcing the user to join fourteen tables with 'x' criteria, you build a view just for them. If you've set things up in a warehouse style environment, this is not as difficult as from transactional systems. As the user dreams up new ways of demanding data, a warehouse can respond more effectively. The best example I can give FOR a data warehouse is a user who came to me back in September with a request. "Give me a total for failures for these components across product lines." Getting that alone was interesting and took a day or two - first figuring out where the data lived (not my system), then figuring out how to not select the data which he didn't want. Then letting the query run once (45 minutes), twice, 5 times while I worked out the kinks, then he comes back with 'Interesting - I don't believe it' - sure enough I still had a kink in there - then 'well can you split out shop-floor failures vs returns failures?' another couple of days. By then I was beginning to figure out that I had better start pulling the base data he needed into its own table just to deal with him. Ok, now break it out by component, type of failure - ya-di-da-di-da. Finally - put it into a format I can import into Excel. When all was said and done it took three weeks. Half of the problem is that he couldnt figure out what he wanted until he saw what I had to offer him. In a data warehouse environment, the user gets a nice GUI interface (whatever it is - I don't care) - he gets to point and click to join things together, If what he needs doesn't exist, he can build it or make a simple request to 'the warehouse' and it will be built for him. His queries will take half-an-hour to run, but he'll be able to get the data together that he needs by the end of the day, not the end of the month. As you can guess there is a lot of work involved. There is also a lot of disk involved. I've been working on one here and hope to share my experiences with y'all in San Hosie. I've only just got the proof-of-concept one working and now have some time to sit down and actually try to describe the whole thing. I would welcome anyone who cares to start a discussion on the topic. I've invented 90% of what we've done here and think its right, but know there are things I haven't thought of yet. What I've seen to date is that it's real easy to take short-cuts and do things wrong. I've tried to stay on the straight and narrow and most of the work to date has been conceptual - not coding, although the little code that there is to drive this thing can be rather deep. cheers j. _____________________________________________________________________________ Jack Parker - Hewlett Packard, BSMC Boise, Idaho, USA jparker@hpbs3645.boi.hp.com _____________________________________________________________________________ Subtlety is the art of saying what you think and getting out of the way before it is understood. _____________________________________________________________________________ Any opinions expressed herein are my own and not those of my employers. _____________________________________________________________________________