Dynamic Organization (Span) View

Ankur

DOMO - Dynamic Leader View Example.xlsx

I am trying to find a way by which when a user view the Dashboard, then based on his/her PDP, s/he must see his/her own score and must see his/her Direct Reports entire span/ org score.

I have attached a dummy data creating the scenario and along with it I have added Org Chart and the Views that is expected.

Any help in this will be highly appreciated.

Regards,

Ankur Goswami

ArborRose

Setup your data in a way that is structured to include columns for user scores and direct reports.

Leader	View	Score	Direct Report 1	Direct Report 2	Direct Report 3
Regan	Regan's View	85.8%	Rosy	Suzy
Rosy	Rosy's View	80.3%	Paul	Jhonny
Paul	Paul's View	93.8%	Josh	Kate
Jhonny	Jhonny's View	86.0%	Jackie	Linda	Tyson
Suzy	Suzy's View	89.0%	Teressa
Teressa	Teressa's View	73.5%	Lora	Tom

Then create PDP policy to define a policy for the leader and repeat the process for each leader.

Leader = 'Regan'
Direct Report 1 = 'Rosy'
Direct Report 1 = 'Suzy'

I think you could do this in a Magic ETL. Select Columns (Leader, Score, DirectReport1, DirectReport2, DirectReport3). Add a constant or formula tile to add a View Type column. Use filter tile to filter the data based on the leader adn direct reports.

To get Regan's view, filter where leader='Regan' and DirectReport1='Rosy' or DirectReport2='Suzy'.

ArborRose

If its survey data and anonymous, you may need a rule in there to account for groups smaller than X. If there are fewer than X, you aren't supposed to reveal the findings - something like that.

ArborRose

Setup your data in a way that is structured to include columns for user scores and direct reports.

Leader	View	Score	Direct Report 1	Direct Report 2	Direct Report 3
Regan	Regan's View	85.8%	Rosy	Suzy
Rosy	Rosy's View	80.3%	Paul	Jhonny
Paul	Paul's View	93.8%	Josh	Kate
Jhonny	Jhonny's View	86.0%	Jackie	Linda	Tyson
Suzy	Suzy's View	89.0%	Teressa
Teressa	Teressa's View	73.5%	Lora	Tom

Then create PDP policy to define a policy for the leader and repeat the process for each leader.

Leader = 'Regan'
Direct Report 1 = 'Rosy'
Direct Report 1 = 'Suzy'

I think you could do this in a Magic ETL. Select Columns (Leader, Score, DirectReport1, DirectReport2, DirectReport3). Add a constant or formula tile to add a View Type column. Use filter tile to filter the data based on the leader adn direct reports.

To get Regan's view, filter where leader='Regan' and DirectReport1='Rosy' or DirectReport2='Suzy'.

Ankur

Hi @ArborRose ,

Thanks for the guidance. But the only challenge is that our actual data contains 131 rows and 1+ million rows. We will have around 30k Leaders and multiple layers. Some migth have 5 level of down hierarchy and some might have 14 layers of down hierarchy. So, we want a solutin which will work for all and at any level. Thinking to get some Beast Mode calculation approach, I shared a simple data sample. We don't have scores in percentance, rather it's in Likert scale. Also we have several questions (around 30+). So to get a numeric value to convert the data into row level. Further we will have predefined Topic Classification at the data level and all that translates into creating multiple rows per employee.

Do you think we can handle this at Beast Mode level rather than restructuring the schema.

ArborRose

Sorry, I did not see the first two sheets in the file. Hopefully some others will chime in. The following is just an idea, others may have better theories.

Me……I would not attempt what I see in your spreadsheet. I did something like this a few years ago. My data was anonymized survey data for a large school district with thousands of employees. And the vendor that gave me the data wouldn't/couldn't tell me who the people were. Only which ids reported up.

I started by representing the data using a small sample…and I was doing it in SQL. Work through the problem, verify it worked with the small dataset, then fed my process the larger data. Even today, my go-to is SQL and I apply it to the tools I have in Domo.

I started with a sort of linked list of direct reports. Who reports to whom. I did not tackle multiple levels at once. Each entry was a person and who they reported to. That was the user side to establish hierarchy. In the case of a company with known values and lots of people, you could possibly build it as an extraction from active server or an HR system.

From that I would attempt a flat view that includes fields for each employee and their hierarchy levels. Let's see if I can lay this out in a better explanation.

1. Start with a Small Sample

• Benefits: Working with a small sample allows you to test your approach and verify that it works before scaling up. This is crucial for debugging and understanding how your data transformations will behave with real data.
• Implementation: Use a subset of your data to create a simple hierarchy. Ensure that your SQL queries or ETL processes correctly represent the relationships and produce the expected results.

2. Represent Hierarchical Relationships

• Initial Setup: Create a dataset that represents direct reporting relationships. This is essentially a linked list where each entry (employee) points to their direct supervisor.

CREATE TABLE DirectReports (

    Employee_ID INT,

    Supervisor_ID INT

);

3. Build a Flat View for Hierarchy Levels

• Transformation: After establishing direct reporting relationships, build a flat view that incorporates hierarchical levels. This helps in aggregating scores and visualizing data at different hierarchy levels.
• SQL Example: To create a flat view that includes hierarchy levels, you might use a recursive Common Table Expression (CTE) in SQL:

WITH RECURSIVE Hierarchy AS (

    SELECT

        Employee_ID,

        Supervisor_ID,

        1 AS Level

    FROM

        DirectReports

    WHERE

        Supervisor_ID IS NULL  -- Top level (e.g., highest leader)


    UNION ALL


    SELECT

        d.Employee_ID,

        d.Supervisor_ID,

        h.Level + 1 AS Level

    FROM

        DirectReports d

    INNER JOIN

        Hierarchy h

    ON

        d.Supervisor_ID = h.Employee_ID

)

SELECT

    Employee_ID,

    Supervisor_ID,

    Level

FROM

    Hierarchy;

There's a new SQL tile coming soon in Domo. Though I start this way {above} in theory, I'd try to replicate in Magic ETL for speed. Definitely not in Domo SQL the way it works for ETLs. It's terribly slow.

4. Aggregate Data

• Direct Reports Aggregation: For each employee, calculate scores for their direct reports. This might involve joining your hierarchy view with the score dataset and then aggregating the results.
• Recursive Aggregation: If you need to aggregate scores at multiple levels (e.g., entire span of the organization), you can extend your recursive queries or use ETL processes to sum up or average scores.

5. Verification and Scaling Up

• Verify Small Dataset: Before applying your solution to the full dataset, ensure that your transformations and calculations work correctly on the smaller sample.
• Scale Up: Once verified, apply the process to the full dataset. Ensure your system can handle the volume of data and that performance is acceptable.

6. Additional Considerations

• Performance: For large datasets, consider performance optimizations such as indexing and efficient querying. Magic ETLs for speed, not SQL ETLs.
• Security and Privacy: Ensure that privacy is maintained and that no sensitive information is exposed.

7. Visualization and Dashboard

• Build Visuals: Use your flattened hierarchical dataset to create visualizations in Domo. Display individual scores, direct reports' scores, and aggregated scores for the organizational span.
• Dynamic Filtering: Implement dynamic filtering in your dashboard to ensure that users see relevant data based on their own position in the hierarchy.

By starting small, verifying results, and then expanding, you ensure a more reliable and effective implementation for handling complex hierarchical structures.

Ankur

Thanks @ArborRose for the tip. Let us give it a try. Will get back to you.

Just one clarification - Our survey is also anonymous. We too just know the respondent as an Unique ID but the reporting hierarchy of the respondent is available.

Anyways, we have some direction to work on now.

Thanks once again for your time and effort.

ArborRose

If its survey data and anonymous, you may need a rule in there to account for groups smaller than X. If there are fewer than X, you aren't supposed to reveal the findings - something like that.

Ankur

@ArborRose, Yes we do have such rule and that is taken care.