Dynamic Organization (Span) View
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
Best Answers
-
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'.** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 -
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.
** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0
Answers
-
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'.** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 -
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.
0 -
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.
** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 -
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.
0 -
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.
** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 -
@ArborRose, Yes we do have such rule and that is taken care.
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 621 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 743 Beast Mode
- 58 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 395 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 6 Software Integrations
- 124 Manage
- 121 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive