Welcome to my Data Warehouse Blog! To illustrate the motivations to come up with this blog I’d like to tell you a little story first.
Back around 1995 I was working for a software company, which had been the market leader for Sales Force Automation (SFA) and Electronic Territory Managment Systems (ETMS) software for the pharmaceutical industry in Germany. As the leader of the server development team I was in charge of the programs and the underlying Oracle database, which guaranteed a multi-directional data flow between the different sales reps and the head office.Reps were reporting sales calls and additional data about their activities as well as certain characteristics of the doctors, pharmacies, and hospitals they visited. Additionally, pharmaceutical companies were buying turnover and sales volumes data from external providers based on geographical segments, time, and different levels of a self-defined “product hierarchy”.
All this valuable data was residing in the database on the server, which obviously triggered the desire for analytical applications.
Long before I had been responsible for the server, an analytical application had been developed by a another team. This application did no longer satisfy the growing demands of the very heterogeneous user base and thus, we decided to abandon it and to develop a new suite of analytical applications, based on a real data warehouse, from scratch.
Then, someone had the very smart idea to check the market for out-of-the-box solutions! Plus, some of our customers were already using BI systems like Cognos. After contacting Cognos we agreed to hold a three day in-house workshop as a proof of concept and soon, three Cognos staffers showed up at our office in Heidelberg: The unavoidable sales guy, a senior pre-sales consultant, and a quite attractive and very nice young woman, who seemed to be new to the company and apparently doing some training on the job. I can’t recall her name today, so let’s call her Lucy.
It seemed as if the Cognos people thought: “Yet another prospect with some data to load, to aggregate, and to render in a couple of fancy reports. That’s just right for Lucy to get some more hands-on experience. We have already taught her how to deal with multi-dimensional data and how to customize the system according to the client’s requirements.”
To make a long story short, here is what happened: At the end of the third day we were all heading to our conference room, where Lucy was expected to present what she had been working on for the last three days. As we entered the room we found Lucy in a confused state. She was crying, unable to say a word. We all looked at each other and everybody was really shocked. It was a quite embarrassing situation.
Lucy explained to us, that she couldn’t even manage to model the product hierarchy, let alone to aggregate any data. She had asked almost every expert at her company and even though we also tried to supply as many information and help as possible, she couldn’t figure out a solution. The truth was, that there was no solution. The Cognos system simply was not ready for this kind of product hierarchy, which in turn is common for almost every pharmaceutical company in the world. The same holds true for the organizational and geographical dimensions of a pharmaceutical sales force.
I’m going to describe the above mentioned dimensions and hierarchies in great detail in the upcoming posts, but, in a nutshell, their main characteristics are:
There are no fixed hierarchy levels, the hierarchies are purely parent-child
Each knot can be either the parent or the child of a knot of any type
Each knot can have any number of parents
Raw data can occur at any knot in the hierarchy
the data value of a knot can be the result of an aggregation of the data values of its children
the data value of a knot can be raw data from an external data source, deviating from the aggregate of the children
Very often, the data values (facts) are non-additive
After it had been clear to me, that there was not a single out-of-the-box solution available which could cope with these hierarchies, I dedided to pick up the original plan to develop a data warehouse model from scratch.
In the course of the next couple of weeks or even months I’m going to describe the statical and dynamical data warehouse model, which now has matured over the last ten years. The hierarchies described above are only one of several features, that can severely risk the success of a data warehouse project. Many of those features will be covered in the upcoming posts (e.g. time variance, completeness of meta data, ETL tools, skills of the team members, hardware, etc.).
Finally, I can’t resist to tell you what I found out just a few weeks ago. I was doing a presentation of the prototype of an OLEDB for OLAP provider I have developed recenty (more on that in an upcoming post). It took place in the German head office of one of the world’s leading players in the phama market. They are using Cognos as their BI platform and obviously I asked them how they deal with the organizational sales force hierarchy. In a mixture of sarcasm and embarrassment they replied: “C’mon, you know it! We have to tell users not to look at any numbers above a certain level in the organization, because they are all wrong!”.
I don’t know what happened to Lucy after our workshop more than ten years ago. I hope she is doing as well as Cognos is doing today.