Modeling role-playing dimension

[valpassos]

Hi community,

I'm in need of some light regarding a what I believe is a role-playing dimension scenario.
Imagine I have something like a Employee-Manager relationship, in which a Manager is an employee as well.

In my visualizations, I need to see certain fact for my Employees, and at the same time I need to see the same fact for the respective Managers.

How do I accomplish this? Knowing that I only have an Employee dimension, and the relationship between Employee-Manager is in a Relationship dimension (because I can have multiple relationship types between a Employee and his Manager), in which I've created an hierarchy. So, when I select the employee (the child), I can see who is his manager (father).

My Employee dimension and Relationship dimension are linked by the %EmployeeKey, and my fact table is linked to the Employee dimension by the %EmployeeKey as well.

In essence: how do I access the Manager's facts in my visualizations?

Thank you!,
Lisa

[valpassos]

Thanks, @kuba_michalik ! I will give your solution a try when I have the proper time, but let me post here my data model to clarify the situation:

My EmployeeRelationships table is where I have my hierarchy created, so I have all my IDs there.

If I understand your solution correctly, you are not distinguishing the different fact types in the fact table, but in the very Employee dimension? Is that correct?

Thank you!

Lisa

[kuba_michalik]

Exactly that - those are the same facts after all, only in a different context (by employee or by employee manager). The EMP/MGR field is added so it's possible to switch between those contexts by using set analysis.

I think creating this context in a separate dimension table is better, because then you can still use the normal employee dimension, where employee is just the employee and you don't need to worry about setting the context in every formula.

[valpassos]

Hi @kuba_michalik ,

So, I did try out your solution and the FactLinkageType={'MGR'} doesn't work when I'm seeying by employee.

The expected behaviour is: when I select an Employee, I want to see his fact in a KPI, as well as his Manager fact in a KPI expression. The same goes for the table. In a table I want to see:

Employee | Manager | Employee Date | Manager Date

The FactLinkageType={'MGR'} works fine in a aggregated level, but when I select a employee it gives me null (-).

Do you know why that happens?

Thanks,

Lisa

[kuba_michalik · Accepted solution]

Hi @valpassos ,

Yeah, I guess I do - you are making selections in the original Employee dimension (which only links employees to their own facts - so after you make selections there, the manager data becomes excluded and that's why it does not show anything).

I can think of three ways to resolve that, but all have cons:

- don't create the links to manager facts in a separate dimension table, but instead concatenate it to the main Employee dimension. Con: you need to set up set analysis everywhere, or formulas would mix data for employee and their manager together

- clear the selection for Employee in the formula, and "copy" it to the new Employee dimension (the one with the link types). Syntax for such a set would be something like {<FactLinkageType={'MGR'}, Employee=, Employee_new_dim=$::Employee>}. Con: formulas become a little confusing 😉

- change your approach completely. Instead of creating links, just take manager facts and re-join them to the fact table (so you would have 2 columns: EventDate and ManagerEventDate), then build your expressions on the appropriate columns. Con: it is data duplication, and it is less flexible if you would have to account for multiple relationship types or multiple hierarchy levels at some point

Depending on your preferences, I think the second or third option would be best.

[valpassos]

Hi @kuba_michalik !

That is so precious help. I haven't had the time to deeply explore each and one of your solutions, but at first sight this is what comes to my mind regarding them:

1. Concatenating my Manager facts to the main Employee dimension. That actually makes sense to me, as they share the exact same attributes (the relationship in this case is nothing more than a hierarchy in a recursive way - a manager is an employee himself). But isn't this in concept the same thing as creating the facts in a separate dimension table? Because it would require set analysis as well, with the FactLinkageType to distinguish Employee from Manager. Right?
2. Copying it the Employee selection in the new Employee dimension. I like this one, the syntax doesn't scare me as long as I know what I'm doing. I'll give it a try.
3. Taking manager facts and re-joining them to the fact table. That was actually the first approach when I first faced the challenge. I would  only replicate my fact table, renaming every column with Manager_ prefix, and adding a FactType flag "ManagerFact". I don't know why I haven't go down the road with that solution - I quit at some moment in time. Will explore that.

I am now faced with a more existential crisis regarding my employee-manager relationship: I want to know how many managers don't have employees. To put it in another way: managers that are only managers, not having employees under their remmit.

I have my Relationship table that gives me every possible Relationship type between a Manager and Employee, like:

Employee Name | Manager Name | Relationship Type
Anna | Anthony | Supervisor
Britanny | Anthony | Team Leader
Chris | Jessica | Supervisor

…

This table implies that every Manager is only a Manager because it exists as a Manager role (Supervisor, Team Leader,…) in relation to its Employee, but it's actually possible to be a Manager without having any Employee under his remmit. Do you have any idea how would I count for that?

Thank you again,

Lisa