Unique visitors

Dear colleages,

I am using a line item fact table for laboratory results in a clinical dw, as in the parent/child schema of purchase/line item, but it is laboratoryTest/result.

I have two questions:

1- Where can I introduce an attribute that specifies that is the first laboratory test done to the patient? It is not an attribute of the patient dimesion and there is no lab test dimension. But it is important to filter the facts depending whether it is the first test done to the patient. Currently, I have a flag in the fact table. Is it right?

2- It is also important to be able to show in an olap cube (I am using mondrian), the results for unique patients. That is, I can find a result several times for the same patient but count as only one. I have in the fact table the laboratory test number as Degenerate Dimension, but the group have to be done with patient. It is the same as saying how many unique visitors have a web page. Again, I have solved this problem very easily introducing the patient natural key in the fact table as a Degenerate Dimension. Is it right?

Any proposal for the two problems are very wellcome.

Regards,
Manuel.

1. Yes, that is fine.

2. It depends. If patient is a type 1 dimension, the key itself should be sufficient to identify uniques. If it is a type 2, standard practice is to retrieve the patient ID attribute from the dimension. The reason for that is to avoid storing the value on the fact to keep the fact table as thin as possible. This is primarily for performance reasons and assumes other patient attributes would be needed for the query. But, your milage may vary… if you already have the value in the fact, try queries with and without the dimension join and see if there is a significant difference in query times. If the join doesn't slow things down much, you are better off not creating the degenerate column.

Thank you very much. I will try with and without NK.