Daily Mapping table

[Anonymous]

Hi Everyone,

I'm currently in a challenge with creating a daily mapping table , the scenario is :

I'm creating a employee daily snap shot for HR in Power BI, I have 1 fact table(exported daily from the system showing all the employees and their Team) and 1 dimension table (mapping table). 

What I'm currently doing is to add new lines everyday to the mapping table to make sure any team/department structure changes are reflected in the Power BI dashboard while keep the historical mapping if a ealier date is filter in the dashboard (e.g. Team 1 belongs to Dep-A on 1 Aug but changed to Dep-B the very next day).

The problems for this method is , if there is no structure changes I still have to add new lines everyday to keep the relationship btw thost two tables via Helper column which is not very efficient .

Could anyone think of a better way to do this ?  Thank you in advance!

[hashtag_pete]

Hello, 

where do you get the department information from? In your first post, you say that you have the fact table automatically from a system, but based on what input do you know if Team 1 is working on a specific date for Department A or B?

BR

hashtag_pete

[Anonymous]

Hi @hashtag_pete , I maintain a Excel spreadsheet with the Team/Dep info and it is updated daily

[hashtag_pete]

And this is your manual work?

My point is: how can you systemwise know the department? If someone has to ask this and then manually enter it, you can't have an automatic solution. 

If you get it from an system, you can solve the problem with a calculated table and summarizecolumns

[Anonymous]

Hi @hashtag_pete , I don't think where you get the mapping for team/dep(whether from system or a spreasheet) is important  in this question. 

Any decision for structure changes is made by human, not systems. Once we know the changes we can either enter into the system, or a spreasheet, both manually. I don't think any tech can read a human's mind and automatically enter it into a system.

[Anonymous]

Thanks @Anonymous  for the advise, however we have thousands of employees so it will be a huge job to set up and maintane the dimention table based on employee instead of teams.

Another thing I'm not sure how this works, do you need to create a new employeeID for the same employee everytime there is a sturcture change? Normally the employeeID will be associated with the same  employee for their whole career  in our company , if you create a new employee ID it won't create a relationship with the fact  table since in the fact table there will only be one fixed employeeID for each employee. For example in fact table the employee ID for Tom is 1001, if you create another employee ID 1005 for Tom in  dimention table it will not map to the fact table since they have different employee ID.

[Anonymous]

Hi @Anonymous 

Can you provide more details on needing to "add new lines everyday to keep the relationship btw those two tables".

If i understood your post correctly, i don't see the need for creating new lines everyday. If there is a need by HR do have a daily, monthly quarterly it is possilble. But you would need to change your mapping key to teams as using date is not advisable as the dates are infinite values .

BR

Dax_Noob

[Anonymous]

Thanks @Anonymous , I have highlighted the lines  I added for 3 Aug and I will need to do this everyday otherwise the relation will broken(via Helper column)

If use Teams as mapping key instead of  creating a Helper column, it won't keep the track record of the mapping correctly, especially for historical date.  For example if I simply change Team1 from Dep-A to Dep-B in the mapping table , then all the employees under Team 1 will be mapped to Dep-B from the time I made this change.  If we filter the dashboard to show headcounts as at 1 Aug, anyone under Team1 will be mapped to Dep-B which is not correct since Team 1 should be under Dep-A as at 1 Aug.

[Anonymous]

Hi @Anonymous ,

JCan you provide a screenshot of your report which some blurred out data. As that would help to understand your use case.

BR

Dax_Noob

[Anonymous]

@Anonymous , here is a quick mock up chart, basically we just select a date and it will show the count of employees for each Deparment as at selected date

[Anonymous]

Hi @Anonymous ,

Imo, would suggest to keep the dimension table small without duplicates. Since each employee is unique you can create a employeeid in the dimension table as a unique key but that would require you to modify your dimension and fact table. 

Step 1: Modify your dimension table to the below,  A new row is created whenever structural change happens (Note: if you do not want to use employee id you could use employee name as the mapping, but not advisable as some employees have same first name)

Step 2: Map Employee Id to Employee id, Or employee name to employee name

Step 3: Visualize

I hope that helps, if it does please mark that as a solution

BR

Dax_Noob