"Stephen, sounds to me that what you have built is not a datawarehouse, rather a collection of source systems (clinical trials) on a level playing field in terms of storage technology, for the purpose of spitting out reports which each relate to one specific trial."
That is my point. It was indeed a DW, just not a DW/BI.
"You say that clinical data has no hierarchy. Clinical data might not, but clinical infomation surely does."
By my definition, data and information are exact synonyms. YMMV.
"Information, as far as I am concerned, is when data is brought together and structured in a way that facilitates decision making."
I would call that a report, or, more specifically, a report dataset.
"Data is just numbers. One number compared with another number (eg Target Weight Loss vs Actual Weight Loss, Revenue This Year vs Revenue Last Year, Blood sugar levels pre- vs post-trial) becomes information. This information could no doubt be classed in a hierachical fashion. Is there no one in your organisation that might want to analyse trial results of drugs relating to a particular bodily system, perhaps drilling down further to a particular organ, ultimately drilling down to specific disease/condition?"
Again, that is my point. I should have been more specific with the definition of hierarchy. Clinical subjects have clinical visits, where various data is collected. Some of the data belongs to the clinical subject, some to the clinical visit, so there's that hierarchy. However, there is no drill down hierarchy in clinical data. What you are talking about makes no sense. They do statistical analyses, which are very different from the slice and dice of financial or marketing data.
"Using a well structured datawarehouse, the user that wants to know this should be able to find it out pretty easily, without scouring through all the tables for each trial, which are all very much separate, despite residing perhaps in the same database."
The point of the clinical data warehouse was to enable reporting across trials. The current SAS reports did intra-trial reporting just fine.
"You say that the results are output as SAS datasets. This to me further highlights that you have designed a way to access very low-level data, and only low-level data."
Exactly. And I still hold that it is a datawarehouse, albeit w/o common BI functionality. Don't forget, you're not looking at BI-type information. You're looking at vital signs, alcohol history, arithmetic tests, creatinine levels, ECG's, etc.
"This is the key difference between your system and a datawarehouse. A DW/BI system may provide access this information if required, but will likely do so from a more top-down perspective. The executive user (or the poor sap preparing a report for your shareholders) might want to see 'Number of trials due for completion this year relating to heart disease', or 'Total Trials in Pipeline'. He/she may want to then find out what the trials are in the pipeline, then get further info on the trial itself if they want."
What you are talking about there is trial administration data, which does fit very well into the DW/BI model very well.
"I suspect that the failure of your project should not be blamed on the OLAP/BI framework, rather perhaps the DW consultant that did not correctly grasp the reporting needs of the target audience. They wanted reams of numbers, he wanted to give aggregated scorecards. A good consultant should have worked that part out very early on in the piece, thus being able to deliver what you wanted."
The DW didn't grasp the needs of the users, for sure, but the OLAP/BI framework was the wrong tool for the job.
My point in writing this was to show people that the DW/BI is not the only way to warehouse data.