Degenerated dimension for my scenario ?

Hello

I have a fact table representing protocole used for paramedic's intervention (the grain is the protocole). I have some dimensions attached to it and everything work great !

Now, the departement user's browsing the olap cube would like to have 3 new value added in the cube. In the source OLTP, its just 3 free text field where user can enter only numbers (0-99). The problem that I have is that those numbers are only used when a specific protocol is used.

How can I integrate that in my actual fact table ? Should I make them as degenerated dimensions ? Should I create 3 seperated dimensions for those 3 value (I really dont like that idea). .... ? If they are added to the fact table with the protocole grain, I'm gone have NULL values ?

Those numbers are not measures ... they will be mainly used for filtering purpose.

Thansk for any tip !

Richard

Make them a junk dimension.

Do these numbers mean something? Are there descriptions that go along with them?

I'm assuming yes to both... Save yourself further rework. If the numbers do not have descriptions now (or the users say they don't need them), they probably will soon. Do not make them degenerate dimensions. Use a junk dimension (as B&L suggests) or 3 dimensions.

ngalemmo wrote:Do these numbers mean something? Are there descriptions that go along with them?

I'm assuming yes to both... Save yourself further rework. If the numbers do not have descriptions now (or the users say they don't need them), they probably will soon. Do not make them degenerate dimensions. Use a junk dimension (as B&L suggests) or 3 dimensions.

Hi

Well, they mean something. But the 3 fields are used to put a score for the evaluation of the patient state. There is no description in the OLTP source system that could be used. The user just know what, for exemple, 24 means or 12 or 10. The 3 field are related in a way (one field is the score of the paramedic, one field is the score of the reviewer and 1 field is the official final score)

Thanks again guys for the usefull tips ! I really appreciate !

Richard

If they are scores, then they are measures, not dimensions. One would assume at some point they may want to do analysis of the scores.

ngalemmo wrote:If they are scores, then they are measures, not dimensions. One would assume at some point they may want to do analysis of the scores.

Hummm ;-) well maybe I did not use the right term to explain what they are exactly. Its a scale to evaluate how bad the patient is. Based on that "scale" or "number" ... the hospital can be prepared to do some specific action when the patient is brought to the emergency room.

For example : a minor trauma is a number of 3 or less and a major trauma is a number of 4 or more.

User will want to know the number of dead patient with a "score" of 12 in 2010, or the number of patient who passed away at the emergency with a "score" of 2 in 2010 or the average time spent by paramedic on crash site for patient with a "score" of 16 in 2010.

Do you still think it should be a measure ? Those number are only used for a specific protocol (grain of my fact table)

Ok, understood. Based on your description, there would not be a mathematical relationship... (ie. a 4 isn't twice as good/bad as an 8, and an average would mean much) then they would be dimensions.

ngalemmo wrote:Ok, understood. Based on your description, there would not be a mathematical relationship... (ie. a 4 isn't twice as good/bad as an 8, and an average would mean much) then they would be dimensions.

Well, I'm pretty sure its the case ... but just to be really really sure ... I'm gone double and triple check with with the expert ;-)
cause it can be a pain in the *ss if I do that the wrong way ;-)

Thanks again !

Have a great day !

A junk dimension with a short roll up might be useful.

I think it would be worth discovering if the scores were based on some scale. Then you could create a roll up where scores 0-10 mean "Low" , and 90-99 mean "severe". If someone wanted to compare how the various people scored the same patient, 77 vs 78 would be meaningless but "Critical" vs "Severe" would be more telling. Plus, a crosstab on the 2 scores would be 100x100, kind of hard to make much sense of it.