-1

I have a fact table called Fact_Employee_leave where each row refers to the Leave taken by each employee referencing to (dim_employee) which is starting and ending at a specific date referencing to (dim_date)

I designed the fact table as below: pk_employee_leave , fk_employee , fk_start_date , fk_end_date , quantityHours , leaveType , approvalStatus

As you can see , I added two string columns "Leave Type" which indicates the type of leave taken and ApprovalStatus (whether the leave is approved or rejected by the manager). However , according to Kimball's best practices, I cannot put string values in a fact table. In my case would it be correct to put it ?

userrr
  • 197
  • 7
  • Please do not [delete & repost](https://stackoverflow.com/q/76381028/3404097) questions, edit per feedback. You are wasting reader efforts & subverting site protocols. PS Poorly received posts count towards asking limits deleted or not & count more when deleted. – philipxy Jun 02 '23 at 09:18
  • It's "the same issue", it's the same question. – philipxy Jun 02 '23 at 09:22
  • Again: That is irrelevant. See my last 2 comments. Do not repost questions. [ask] [Help] [meta] [meta.se] – philipxy Jun 02 '23 at 09:30
  • 2
    You already know the answer. "according to Kimball's best practices, I cannot put string values in a fact table." So, either do what you want or follow Kimball. Either way, this is a question seeking opinions rather than a canonical answer. – Gilbert Le Blanc Jun 02 '23 at 11:05

1 Answers1

0

According to Kimball's dimensional modeling best practices, avoiding string values in the fact table is highly recommended. The fact table should contain numeric values that can be used for aggregation and analysis.

In your case, the "Leave Type" and "ApprovalStatus" columns are string values that indicate the type of leave taken and the approval status of the leave, respectively. We call them descriptive attributes since they provide additional context to the leave fact data.

You must create separate dimension tables for "Leave Type" and "ApprovalStatus" and then reference them using foreign keys in your fact table.

For example, you can create a "dim_leave_type" dimension table with columns like "leave_type_key" (primary key) and "leave_type_description". Similarly, you can create a "dim_approval_status" dimension table with columns like "approval_status_key" (primary key) and "approval_status_description".

Instead of storing the actual text values for "Leave Type" and "ApprovalStatus" in your fact table, you can use the foreign keys referencing the respective dimension tables. This way, you maintain the integrity of the dimensional model and enable efficient querying and analysis by leveraging the numeric keys.

Separating the descriptive attributes into dimension tables allows you to easily manage and update the values without modifying the fact table structure. It also allows for better flexibility and scalability in your data model.

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