Dynamic Organization (Span) View

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.