{"id":502,"date":"2024-11-21T06:28:06","date_gmt":"2024-11-21T06:28:06","guid":{"rendered":"https:\/\/datadandies.nl\/?p=502"},"modified":"2025-01-12T06:13:39","modified_gmt":"2025-01-12T06:13:39","slug":"data-modeling-problem-events-in-progress-in-human-resources-data-dealing-with-employees-who-change-teams","status":"publish","type":"post","link":"https:\/\/datadandies.nl\/index.php\/2024\/11\/21\/data-modeling-problem-events-in-progress-in-human-resources-data-dealing-with-employees-who-change-teams\/","title":{"rendered":"Data modeling problem: Events in Progress in Human Resources data – Dealing with employees who change teams"},"content":{"rendered":"\n

I have a data modeling conundrum. It concerns records in a dimension that have start- and enddates.<\/p>\n\n\n\n

I\u2019ve heard people refer to this type of problem as the \u201cevents in progress\u201d-problem. My specific version of this problem goes like this:<\/p>\n\n\n\n

Imagine you have a sales fact table called FactSales with a date, an employee who made the sale and the quantity that was sold. It would look like this:<\/p>\n\n\n\n

Date | Employee | Sales<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

The fact table above can be joined with a employee dimension called DimEmployee. This dimension looks like this:<\/p>\n\n\n\n

Employee | Team | TeamStartDate | TeamEndDate<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Using SQL, you could join FactSales with DimEmployee table using the column \u201cEmployee\u201d.<\/p>\n\n\n\n

However, sometimes employees have more than one record in DimEmployee. This would lead to unwanted doubling of sales records. You can bypass this problem in SQL by using a multi-column join like below:<\/p>\n\n\n\n

<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
SELECT<\/span> <\/span>*<\/span> <\/span>FROM<\/span> FactSales<\/span><\/span>\n<\/span>\nLEFT JOIN<\/span> DimEmployee <\/span>ON<\/span> FactSales.Employee <\/span>=<\/span> DimEmployee.Employee<\/span><\/span>\n<\/span>\n                      <\/span>AND<\/span> FactSales.Date <\/span>BETWEEN<\/span> DimEmployee.TeamStartDate <\/span>AND<\/span> DimEmployee.TeamEndDate<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
This would lead to One Big Table with the correct team in each record.<\/p>\n\n\n\n
However, imagine you are not using SQL, but you are using Power BI. You want to create a report showing the amount of sales per employee, including in which team the employee was at the time of the sale. A matrix visual in Power BI could look like this:<\/p>\n\n\n\n
Team | Employee | Sales<\/p>\n\n\n\n
1. Using Power BI however, we run into a problem. If we were to load the tables as they are, a many-to-many relationship would be needed. I try to avoid many-to-many relationships in my semantic models because they could lead to ambiguity. Now, as I see it, there are several approaches to solve this problem:<\/p>\n\n\n\n
You filter the DimEmployee table: you only take the most recent records. This solution is far from ideal because now, only the most recent team will be present for each employee; NOT the team that the employee was a part of at the time of the sale. Not a big fan of this solution, but I\u2019ve seen it too many times in the wild to not mention it.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
2. You create a composite key. First you extend DimEmployee by creating a record for each day between start- and enddate that an employee was a part of a certain team. Then you create a new column containing a concatenation of the new column and the column \u201cEmployee\u201d. You create the same new composite key column (concatenation of columns Date and Employee) in the FactSales table. Then, you create a relationship between the two newly minted columns. This approach could work, but you table will start exploding when there are a lot dates between the start- and enddate. Especially when you use dates like 9999-12-31 as enddates for current statuses.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
3. You denormalize the data. You basically do the same thing in Power Query as we did using the SQL statement above.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Now if you are using another approach than the ones listed above, I would love to hear about it.<\/p>\n","protected":false},"excerpt":{"rendered":"
I have a data modeling conundrum. It concerns records in a dimension that have start- and enddates. I\u2019ve heard people refer to this type of problem as the \u201cevents in progress\u201d-problem. My specific version of this problem goes like this: Imagine you have a sales fact table called FactSales with a date, an employee who…<\/p>\n
Read More<\/a><\/p>\n","protected":false},"author":1,"featured_media":504,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[16,55,3,4],"class_list":["post-502","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","tag-datamodeling","tag-onebigtable","tag-powerbi","tag-sql"],"_links":{"self":[{"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/posts\/502","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/comments?post=502"}],"version-history":[{"count":2,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/posts\/502\/revisions"}],"predecessor-version":[{"id":586,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/posts\/502\/revisions\/586"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/media\/504"}],"wp:attachment":[{"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/media?parent=502"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/categories?post=502"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/tags?post=502"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}