-2

I have a fact table called Fact_Employee_Ratings, these are reviews about a company scrapped from Glassdoor. The review is actually divided into two parts, employees would give what they liked and what they disliked about the company. This is how I modeled the fact table but I'm not sure if I can include textual columns in the fact table "review_cons" and "review_pros" as follows because according to many websites, the fact table could only contain foreign keys and numerical measures :

fk_employee (int)

fk_publish_date (int)

review_pros (varchar)

review_cons (varchar)

engagement_score (int)

review_rating (int)

Sujith Kumar
  • 872
  • 6
  • 19
userrr
  • 197
  • 7
  • You can put any information you want in a fact table. However, asking if it's useful to store certain information in a fact table is a valid question. Is anyone ever going to query review cons across a district? Over a 5 year period? That's why your textbook says fact tables contain foreign keys and numeric values. – Gilbert Le Blanc Jun 01 '23 at 10:51
  • 1
    If the text contains analytically valuable content then I would extract that and model it into your star schema. Otherwise just keep in the long text values in a separate table and link them to your fact table as appropriate – NickW Jun 01 '23 at 10:53

1 Answers1

1

I would go for @NickW 's suggestion and consider the following :

  • Employee dimension: Contains information about employees (employeeSK, name, department)
  • Review dimension: Contains details about the review itself (reviewSK,review_pros, review_cons)
  • Date dimension

Meanwhile in the fact table, you can still consider including measures such as the engagement_score,review_rating

JFY, this type of fact tables is called transactional fact table since it focuses on capturing individual events or transactions within the business process.

Amira Bedhiafi
  • 8,088
  • 6
  • 24
  • 60