0

I am going to create one Fact table which is actually at transactional grain. In table we have only text fields ,no numeric measure & 22 fields of this table actually referencing one lookup table in Relational Database. For example Patient first Name and its associated field (Lets say Reason field) which basically is required to fill If patient name is absent( i.e. any one of the fields will be filled) In the Reason field we have 7 different options (Name Unknown, Not Asked, Masked etc.) and this reason field is pointing to one lookup table. Similarly we have 22 fields which are associated with Lookup Table. In the fact table now if we transform the data i.e. if patient name is not null then use patient name else use Reason field (decoded value from lookup table).If we decode the value then if there is any change in the value of the lookup table then fact table needs to be refreshed full .But if we want to avoid it then we can have 22 reason field as is in Fact table and all fields pointing to the same lookup table. This also doesn't look good to me. Is there any other way to handle this situation?

Thanks in advance

Nit
  • 1
  • 1
  • Can you please list some of the others from those 22 fields? This will help readers to understand your data model better. But from what i can understand, You wont refresh the whole fact table when value of look up table changes, You just add anew row to the Fact table with new timestamp, Yes that is the 23rd filed you should have on the Fact table. – shankar_pratap Apr 01 '23 at 00:26

0 Answers0