topic Re: Modeling role-playing dimension in App Development

Modeling role-playing dimension

valpassos — Sat, 16 Nov 2024 19:30:59 GMT

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

Re: Modeling role-playing dimension

miskinmaz — Thu, 05 Dec 2019 12:44:00 GMT

You can follow this link:

https://community.qlik.com/t5/QlikView-Documents/Hierarchy-function-easy-and-Efficient-with-example/tac-p/1485768

Re: Modeling role-playing dimension

valpassos — Thu, 05 Dec 2019 13:38:05 GMT

Hi @miskinmaz !

Thanks for your reply. I have already created the hierarchy between Employee/Manager, but I believe that doesn't solve all my problems.

I want to be able to, for example, in accessing a particular event date in an expression, refer to the Employee event date, as well as the Manager event date. Like:

MAX({<...>} Particular_Event_Date)

Both Employee and Manager will have different dates, and I want to compare them.

Lisa

Re: Modeling role-playing dimension

valpassos — Thu, 05 Dec 2019 13:44:33 GMT

Forgot to clarify: right now, when I calculate the MAX(... Event_Date) in my chart expression, I'm accessing the Employee event date, as that is the one linked to my fact table. I need to access the Manager event date as well.

Re: Modeling role-playing dimension

valpassos — Thu, 05 Dec 2019 16:50:34 GMT

Anyone? 😃

Re: Modeling role-playing dimension

miskinmaz — Fri, 06 Dec 2019 11:01:28 GMT

Based on Manager ID you can again link your employee table with different field name to get the fact related information of manager.

Re: Modeling role-playing dimension

valpassos — Fri, 06 Dec 2019 11:33:54 GMT

Hi @miskinmaz ,

You mean this?

LEFT JOIN (Employee) LOAD EmployeeID, ManagerID RESIDENT HierarchyTable;

Or do you mean creating another Employee dimension with the Manager ID instead of Employee ID?

But then how do I link it to my fact table? How do I differentiate between Employee and Manager when creating my expressions?

Thanks,

Lisa

Re: Modeling role-playing dimension

miskinmaz — Fri, 06 Dec 2019 12:44:43 GMT

For example

Load

EMP ID

Manager id,

From your hierarchy table.

Load

EMP ID as Manager id,

EMP Name as ManagerName,

EMP Desgn as ManagerDesgn

...

from emp_table.

Re: Modeling role-playing dimension

valpassos — Fri, 06 Dec 2019 14:12:51 GMT

@miskinmaz ,

Still not getting how exactly will I distinguish between Employee facts and Manager facts in my front-end expressions 😕

How are you linking those 2 tables to the fact table?

Lisa

Re: Modeling role-playing dimension

kuba_michalik — Fri, 06 Dec 2019 16:45:43 GMT

I would model it like this, for this specific isolated case - not sure how well it would gel with the rest of the data model and all the other things that need to be shown.

First I'd create a new table for the chart dimension:

EmpNewDim:
Load
Employee as Employee_new_dim,
'EMP' as FactLinkageType,
%EmployeeKey
Resident Employee;

Then, add links to manager facts for each employee:

Concatenate (EmpNewDim)
Load
Employee as Employee_new_dim,
'MGR' as FactLinkageType,
%MgrKey as %EmployeeKey
Resident Relationship;

I don't know if you have the necessary fields in the Relationship table, but you should be able to add them easily. The idea here is to take the hierarchy and connect it to the data in reverse. Typically hierarchies work so that manager connects to the facts through their employees; what we want is for every employee to connect to the data through themselves, and additionally through their managers. We are adding the FactLinkageType field to be able to distinguish the two types of linkage from each other.

In the chart you use Employee_new_dim as dimension (which of course has all the same values as original Employee dimension) and the formulas
MAX({<FactLinkageType={'EMP'}>} Particular_Event_Date)
MAX({<FactLinkageType={'MGR'}>} Particular_Event_Date)
to get the employee event and manager event shown by employee.

Everything should work just fine as long as users won't be making selections in Employee_new_dim expecting it to be just the same as regular Employee dimension 😉

I assumed you just want to have this for a single hierarchy level (so only for the employee - direct manager relationship)

Re: Modeling role-playing dimension

valpassos — Fri, 06 Dec 2019 17:41:02 GMT

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

Re: Modeling role-playing dimension

kuba_michalik — Fri, 06 Dec 2019 18:17:33 GMT

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.

Re: Modeling role-playing dimension

valpassos — Sun, 08 Dec 2019 23:07:17 GMT

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

Re: Modeling role-playing dimension

kuba_michalik — Mon, 09 Dec 2019 16:30:03 GMT

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.

Re: Modeling role-playing dimension

valpassos — Tue, 17 Dec 2019 10:17:34 GMT

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:

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?
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.
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:

…

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