Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Business Intelligence or Data Warehouse Expand / Collapse
Author
Message
Posted Tuesday, May 23, 2006 4:46 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 1:29 AM
Points: 117, Visits: 155

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.




Post #282246
Posted Tuesday, May 23, 2006 5:08 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 2:47 PM
Points: 132, Visits: 114

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

Post #282251
Posted Tuesday, May 23, 2006 7:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 26, 2006 11:29 AM
Points: 4, Visits: 1
Article is very good.I have worked at the places were people don't even know they are seating on the Bussiness Intelligence system.
Post #282276
Posted Wednesday, May 24, 2006 6:11 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 17, 2014 7:55 AM
Points: 2,805, Visits: 3,067

Building a data warehouse is not just building a couple tables.  It needs data analysis and data modeling. The modeling will help to find the hierarchy of the data.  For example, State -> County-> City -> zip.  The big thing on data warehouse is meta data.  It explains the data in the data warehouse, where they come from, how they get calculated and standardize the data.  That is another thing you need.

There is a book called The data warehouse toolkit by Ralph Kimball.  It is very helpful.

Post #282375
Posted Wednesday, May 24, 2006 10:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 2:47 PM
Points: 132, Visits: 114

Handling the metadata was the really big challenge for our DW. 250 trials, each with 30-40 distinct tables, adding a new trial a week, I had to develop a dynamic ETL tool that was fed by a metadata repository. Finally, the metadata of an individual trial could and would sometimes change in the middle of a trial.

Unfortunately, Kimball was worse than no help at all. His methodologies assume certain things, like static metadata, a small number of source systems, etc.

Post #282528
Posted Wednesday, May 24, 2006 12:53 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 13, 2006 7:56 AM
Points: 11, Visits: 1

"Handling the metadata was the really big challenge for our DW. 250 trials, each with 30-40 distinct tables, adding a new trial a week, I had to develop a dynamic ETL tool that was fed by a metadata repository. Finally, the metadata of an individual trial could and would sometimes change in the middle of a trial."

Steven, I think you are correct that the first versions of the data warehouse model ignored metadata almost completely. In his article, "Information Management: DW 2.0 - Architecture for the Next Generation of Data Warehousing" Bill Inmon addresses this problem in the his latest version of data warehousing, what he calls "DW 2.0". In it he states: "Metadata is the glue that holds the data together over its different states. Amazingly, the first generation of data warehousing omitted metadata as part of the infrastructure."

I have to agree that without sufficient (and automated) metadata together with proper release mechanisms, schema maintenance can and will become a full time occupation.

 

Post #282565
Posted Wednesday, May 24, 2006 1:26 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 2:47 PM
Points: 132, Visits: 114

Unfortunately, one of the realizations I came to when creating the dynamic ETL tool was that there is a real limit to how much you can automate metadata maintenance. If you have a DW with a lot of heterogeneous metadata, and that metadata is not "slowly changing", then yes, management should either realize that schema maintenance is indeed a full-time job and pay for someone to do that, or just don't warehouse your data.

Sometimes, the right answer is "forget it".

Post #282573
Posted Monday, May 14, 2007 12:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 19, 2007 12:41 AM
Points: 4, Visits: 1
OLAP Cubes:
I have a cube that is giving incorrect data. If i do a sum and group by query in enterprize manager, i get the correct results, but in the cube via excel it is wrong. The relation ships are setup correctly in the cube editor.
Can anyone please give me some ideas?

Post #365496
Posted Friday, December 28, 2007 1:51 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, January 14, 2013 4:24 AM
Points: 81, Visits: 188
Hi Danie, possible causes if the relational query is different from the cube are:
1. Named Query in the Data Source View
2. Measure properties such as AggregateFunction and MeasureExpression
3. Dimension perperties such as IsAggregatable, CustomRollup, RootMemberIf
Regards,
Vincent
Post #436933
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse