Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Business Intelligence or Data Warehouse


Business Intelligence or Data Warehouse

Author
Message
kenno_rules
kenno_rules
SSC-Enthusiastic
SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)

Group: General Forum Members
Points: 122 Visits: 173

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.





Stephen Hirsch
Stephen Hirsch
SSC-Enthusiastic
SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 Visits: 161

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


nvb
nvb
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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.
Loner
Loner
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2914 Visits: 3324

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.


Stephen Hirsch
Stephen Hirsch
SSC-Enthusiastic
SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 Visits: 161

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.


ThomasLN
ThomasLN
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
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.


Stephen Hirsch
Stephen Hirsch
SSC-Enthusiastic
SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 Visits: 161

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


Danie van der Walt-439654
Danie van der Walt-439654
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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?
VincentRainardi
VincentRainardi
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 191
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search