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.

You say that clinical data has no hierarchy. Clinical data might not, but clinical infomation surely does. Information, as far as I am concerned, is when data is brought together and structured in a way that facilitates decision making. 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? 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.

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. 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.

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.