asked
adding a one to many related table to a fact table
I need some advise on how to model , data warehouse using a one to many relationship without having the wrong aggregation values when it is put in a cube. My problem is I have a fact and the business wants to add to it that has many records for that fact. For instance, a fact record that makes it unique has another table that has multiple records related to that fact record. How would I add the many table to my fact where I won't have inaccurate aggregated vales when aggregated? I thank you for any answer you may be able to provide.
Can you give an actual example - it might make the question clearer.
Sure I have a fact table that has many dims and measures, but what makes the the record unique (other than the PK) is patient account what really is a visit. I want to add another table that also has a patient account that contain notes about that visit. There can either be one note or thousands for that patient account. Example: fact table factid patientaccount dim id dim id dim id...ect measures measures...ect new table patientacccount note
If Patient Visit is the granualarity, then you should have notes about the visit in the same fact table and not in another fact table. or If you have multiple notes for each visit(child), make it as the granularity of fact table and move the visit related attributes(parent) to this child. Refer to Header-Child pattern from Kimball
http://www.kimballgroup.com/2007/10/02/design-tip-95-patterns-to-avoid-when-modeling-headerline-item-transactions/
+1 I'd also add that notes are not the kind of data you would add into a cube - it's fine to store them in the data warehouse, but not a cube - you don't aggregate on notes
Thank you for your reply however, I understand what your saying but I don't think that is going to work in my case. There amount of notes in one visit may very. However the good news is that the note is a static note and is not something that a user can put in. They must select from a list. What are your thoughts on the following? 1. Create a table that has a grouping ID for each different combination of notes to visit and adding that grouping Id to the fact table. 2. (Thinking while I type) Or concatenating each note id to a delimited varchar field and joining back to the table using a list to table function. Obviously there would be a dim table of notes. My Main goal is to not duplicate any data when aggregating this in a report or in a cube. I do agree with you on this should not be in the cube, but unfortunately selling that to the business may be problematic at best.
14 People are following this question.
