Join Key Propagation for Referencing Worksheets
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Users, especially non-administrators, face challenges in fully utilizing data stored in cloud-based databases due to the complexity of manipulating and accessing data, as few can generate referencing worksheets that effectively utilize join keys across multiple tables without administrative access.
Innovation Solution
A method for join key propagation that involves receiving a request to generate a referencing worksheet linked from a data source worksheet, retrieving the join key and metadata, determining additional columns to include, and creating a database query to propagate the join key, allowing non-admins to reuse and create join keys for referencing worksheets.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Ease of operation
If non-administrators attempt to manipulate data or create referencing worksheets with join keys, then data access functionality is limited, but system security and data integrity are maintained
Solution Approach 1:
The system segments user permissions into administrators and non-administrators, with each having distinct capabilities. Non-administrators can create and reuse referencing worksheets with propagated join keys but cannot modify the underlying data or create new join keys, thus maintaining security while enabling operational functionality.
Solution Approach 2:
The system introduces an intermediary mechanism (automated join key propagation) that allows non-administrators to work with joined tables without needing administrative privileges. The system automatically retrieves and applies join keys from source worksheets, enabling data access functionality without granting full manipulation capabilities.
2Adaptability or versatility
If join keys are manually created and managed by each user, then data access flexibility increases, but system complexity and time consumption increase
Solution Approach 1:
Join keys are predetermined and stored in the system when source worksheets are created. When non-administrators create referencing worksheets, the system automatically retrieves these pre-stored join keys and applies them, eliminating the need for users to manually create or configure join keys each time.
Solution Approach 2:
The system copies join keys from source worksheets to referencing worksheets automatically. Instead of requiring users to recreate join key configurations, the system retrieves the existing join key metadata and replicates it in the new referencing worksheet, significantly reducing setup time and effort.
3Reliability
If administrators manage all join key creation and propagation, then data integrity is ensured, but system usability and ease of operation decrease
Solution Approach 1:
The system enables self-service for non-administrators by allowing them to create and reuse referencing worksheets with automatically propagated join keys. The system handles the complex join key management tasks automatically, maintaining data integrity through automated validation while enabling users to work independently without administrator intervention.
Solution Approach 2:
The join key propagation mechanism serves multiple functions: it maintains data integrity through automated validation, enables user autonomy for worksheet creation, and reduces the burden on administrators. This universal mechanism benefits both administrators (by reducing manual work) and non-administrators (by enabling independent operation).
Data Source
AI summary
Join key propagation including receiving a request to generate a referencing worksheet linked from a data source worksheet, wherein the data source worksheet is a presentation of a data set organized in columns from two data source tables joined using a join key, and wherein the referencing worksheet presents at least a subset of the data set presented by the data source worksheet; retrieving data source worksheet metadata and the join key using a reference to the data source worksheet metadata in the request; determining an additional column from the two data source tables to add to the referencing worksheet, wherein the additional column is excluded from the data source worksheet; and creating, using the data source worksheet metadata and the join key, the referencing worksheet presenting the columns from the two data source tables with the additional column.