Skip to main content

​
Working with Tables and Views

Understanding Tables Tables in Elementum are dynamic, real-time data views that automatically stay synchronized with your data sources. They transform raw data into actionable business insights through an intuitive interface. Data Sources for Tables:
  • CloudLinks - Connect to external data warehouses (Snowflake, BigQuery, etc.)
  • Apps - Data from your custom applications
  • Elements - Shared data across teams and departments
  • Tasks - Workflow steps and progress tracking

​
Why Tables Matter for Your Business

​
Single Source of Truth

  • Everyone works with the same, current data
  • No more emailing spreadsheets around
  • Updates happen in real-time
  • Eliminate data copy-paste errors

​
Custom Views for Different Needs

  • Sales team sees revenue metrics
  • Support team sees customer issues
  • Finance sees payment status
  • Leadership sees high-level summaries
  • All from the same underlying data

​
Configuring Data Sources

​
Snowflake View Configuration

When connecting to Snowflake or other data warehouses, you’ll configure: Database Selection
  • Choose your target database from available options
  • Example: ACME_ANALYTICS_PROD
Schema Selection
  • Select the appropriate schema within your database
  • Example: DATA
View Name
  • Define a meaningful name for your view
  • Example: DET (Deal Execution Table)
Proper naming conventions help team members quickly identify and locate the right data views.

​
Table Features

​
1. Joins - Combining Data Made Easy

Elementum provides four join types with visual representations to help you understand how data will be combined. Each join type serves different business needs: Join Configuration:
  • Source Field: Select the field from your primary table
  • Joined Object Field: Select the matching field from the secondary table
  • The system guides you through field selection with dropdown menus
What it does: Shows only records that exist in both tables with matching values.Visual representation: Two overlapping circles, showing only the intersection.Business use cases:
  • Active customers with orders: Show only customers who have made purchases
  • Employees with assigned projects: Display staff members who are actively working on projects
  • Products with sales data: View only products that have been sold
  • Vendors with active contracts: List suppliers who currently have agreements
Example scenario:
Customer Table: 1000 customers
Orders Table: 500 orders from 300 customers
Inner Join Result: 300 customers (only those who placed orders)
When to use: When you need to focus on records that have relationships and want to exclude unmatched data.
What it does: Keeps all records from the left (primary) table, plus matching records from the right table. Visual representation: Left circle completely filled, with matching portion from right circle. Business use cases: - All customers and their orders: Show every customer, including those who haven’t ordered - Complete employee roster: Display all staff, whether they’re on projects or not - Full product catalog: Show all products, including those never sold - Marketing campaign analysis: View all contacts, showing who responded to campaigns Example scenario: Customer Table: 1000 customers Orders Table: 500 orders from 300 customers Left Join Result: 1000 customers (700 show no orders, 300 show order data) When to use: When your primary concern is the complete dataset from your main table, but you want to enrich it with available data from another source.
What it does: Keeps all records from the right (secondary) table, plus matching records from the left table. Visual representation: Right circle completely filled, with matching portion from left circle. Business use cases: - All orders with customer details: Show every order, even if customer data is missing - Complete transaction log: Display all payments, including those without customer profiles - Full support ticket history: Show all tickets, even if user accounts were deleted - Inventory movements: Track all stock changes, including anonymous transactions Example scenario: Customer Table: 1000 customers Orders Table: 500 orders (50 from deleted/unknown customers) Right Join Result: 500 orders (450 with customer data, 50 without) When to use: When you need complete data from your secondary table and want to see what primary table data is available to enrich it.
What it does: Keeps all records from both tables, whether they match or not.Visual representation: Both circles completely filled, showing all data from both sources.Business use cases:
  • Complete data audit: See all customers AND all orders, identifying gaps
  • System migration: Compare old and new systems to ensure no data loss
  • Reconciliation reports: Match financial records from different systems
  • Master data management: Combine multiple data sources into one comprehensive view
Example scenario:
Customer Table: 1000 customers
Orders Table: 500 orders from 300 customers (50 from unknown customers)
Full Outer Join Result: 1200 records (700 customers with no orders, 300 customers with orders, 50 orders without customer data)
When to use: When you need to see the complete picture from both data sources and identify what data exists, what’s missing, and what’s orphaned.
Choosing the right join: Start by asking yourself: “Do I need ALL records from my primary table, or only the ones that have matching data?” This will help you decide between inner joins (matches only) and outer joins (keep everything).

​
2. Table Details and Configuration

General Settings:
  • Table Name: Define a clear, descriptive name
  • Category: Organize tables by type (Data, Analytics, etc.)
  • Source: Links to your original data source
Cloud Details:
  • Cloud Provider: Shows connected service (Porter, Snowflake, etc.)
  • Connection Status: Verify active data connections
Business Intelligence:
  • Snowflake View: Configure and manage your BI connections
  • Performance Monitoring: Track query performance and usage
Snowflake View for External BI ToolsThe Snowflake View feature allows you to expose your Elementum data to external business intelligence tools like PowerBI, Tableau, or Looker. Here’s how it works:
  1. Create your Elementum table from any data source (Apps, Tasks, Elements, or CloudLinks)
  2. Configure the Snowflake View in the Business Intelligence section
  3. Connect external BI tools directly to your Snowflake view
  4. Build dashboards and reports using your Elementum data in familiar BI platforms
This is particularly useful when you need to:
  • Combine Elementum data with other enterprise data sources
  • Create executive dashboards in existing BI infrastructure
  • Enable self-service analytics for business users
  • Maintain data governance while providing external access
The view automatically stays synchronized with your Elementum data, ensuring your BI reports always reflect the latest information.

​
Snowflake BI View Permissions

When using the Snowflake View feature, you need to configure permissions for two distinct purposes: allowing Elementum to create and manage the views, and allowing your users or BI tools to query the views.

​
Elementum Platform Permissions

The Snowflake user account configured in your CloudLink connection needs permissions to create and manage BI views in your desired database and schema.
Required for BI View Creation: These permissions must be granted to the Elementum role before you can create BI views. Without these permissions, the platform cannot create views in your specified Snowflake database and schema.
Grant these permissions to enable Elementum to manage BI views: 
-- Replace placeholders with your actual values:
-- <DB_NAME> - Database where BI views will be created
-- <SCHEMA_NAME> - Schema within the database for BI views
-- <ELEMENTUM_USER_ROLE_NAME> - Role assigned to the Elementum user (typically "ELEMENTUM")

USE ROLE ACCOUNTADMIN;

-- Grant USAGE on the database
GRANT USAGE ON DATABASE <DB_NAME> TO ROLE <ELEMENTUM_USER_ROLE_NAME>;

-- Grant USAGE on the schema
GRANT USAGE ON SCHEMA <DB_NAME>.<SCHEMA_NAME> TO ROLE <ELEMENTUM_USER_ROLE_NAME>;

-- Grant the ability to manage views in the schema
GRANT CREATE VIEW ON SCHEMA <DB_NAME>.<SCHEMA_NAME> TO ROLE <ELEMENTUM_USER_ROLE_NAME>;
Example: 
-- Example using common values
USE ROLE ACCOUNTADMIN;

GRANT USAGE ON DATABASE ANALYTICS_DB TO ROLE ELEMENTUM;
GRANT USAGE ON SCHEMA ANALYTICS_DB.BI_VIEWS TO ROLE ELEMENTUM;
GRANT CREATE VIEW ON SCHEMA ANALYTICS_DB.BI_VIEWS TO ROLE ELEMENTUM;
Best Practice: Create a dedicated schema for your BI views (e.g., BI_VIEWS or ELEMENTUM_BI) to keep them organized and separate from other data structures.

​
End User Query Permissions

After Elementum creates a BI view, you need to grant permissions to the Snowflake roles used by your BI tools, analysts, or other users who need to query the views. Grant these permissions to enable users to query BI views: 
-- Replace placeholders with your actual values:
-- <DB_NAME> - Database used in the creation of the BI view
-- <SCHEMA_NAME> - Schema used in the creation of the BI view
-- <VIEW_NAME> - Name used in the creation of the BI view
-- <ROLE_NAME> - Role assigned to users that need access to query the BI view

USE ROLE ACCOUNTADMIN;

-- Grant USAGE on the database
GRANT USAGE ON DATABASE <DB_NAME> TO ROLE <ROLE_NAME>;

-- Grant USAGE on the schema
GRANT USAGE ON SCHEMA <DB_NAME>.<SCHEMA_NAME> TO ROLE <ROLE_NAME>;

-- Grant SELECT on the specific view
GRANT SELECT ON VIEW <DB_NAME>.<SCHEMA_NAME>.<VIEW_NAME> TO ROLE <ROLE_NAME>;
Example for a specific view: 
-- Example granting PowerBI role access to a sales dashboard view
USE ROLE ACCOUNTADMIN;

GRANT USAGE ON DATABASE ANALYTICS_DB TO ROLE POWERBI_USERS;
GRANT USAGE ON SCHEMA ANALYTICS_DB.BI_VIEWS TO ROLE POWERBI_USERS;
GRANT SELECT ON VIEW ANALYTICS_DB.BI_VIEWS.SALES_DASHBOARD TO ROLE POWERBI_USERS;

​
Granting Access to All Future Views

If you prefer to grant permissions on all future views in a schema (so you don’t need to grant permissions for each new view individually), use this approach: 
-- Replace placeholders with your actual values:
-- <DB_NAME> - Database used for BI views
-- <SCHEMA_NAME> - Schema used for BI views
-- <ROLE_NAME> - Role assigned to users that need access

USE ROLE ACCOUNTADMIN;

-- Grant USAGE on database and schema
GRANT USAGE ON DATABASE <DB_NAME> TO ROLE <ROLE_NAME>;
GRANT USAGE ON SCHEMA <DB_NAME>.<SCHEMA_NAME> TO ROLE <ROLE_NAME>;

-- Grant SELECT on all future views in the schema
GRANT SELECT ON FUTURE VIEWS IN SCHEMA <DB_NAME>.<SCHEMA_NAME> TO ROLE <ROLE_NAME>;
Example: 
-- Automatically grant access to all future BI views for the analytics team
USE ROLE ACCOUNTADMIN;

GRANT USAGE ON DATABASE ANALYTICS_DB TO ROLE ANALYTICS_TEAM;
GRANT USAGE ON SCHEMA ANALYTICS_DB.BI_VIEWS TO ROLE ANALYTICS_TEAM;
GRANT SELECT ON FUTURE VIEWS IN SCHEMA ANALYTICS_DB.BI_VIEWS TO ROLE ANALYTICS_TEAM;
Recommended Approach: Using FUTURE VIEWS grants streamlines access management. When Elementum creates new BI views, users with this grant automatically have access without requiring additional permission changes.

​
Permission Setup Workflow

Follow this workflow when setting up BI views:
1

Grant Elementum Platform Permissions

First, grant the Elementum role permissions to create and manage views in your target database and schema.
2

Create BI View in Elementum

Configure your Elementum table and enable the Snowflake View feature through the Business Intelligence section.
3

Grant User Query Permissions

Grant SELECT permissions to the roles used by your BI tools, analysts, or other users who need to query the view.
4

Connect BI Tools

Configure your BI tools (PowerBI, Tableau, Looker) to connect to the Snowflake view using the appropriate role.
Common Issue: If users report “Object does not exist or not authorized” errors when querying BI views, verify that:
  1. The view was successfully created by Elementum (check in Snowflake)
  2. The user’s role has USAGE grants on both the database and schema
  3. The user’s role has SELECT grant on the specific view or FUTURE VIEWS
Remember that grants must be applied at all three levels: database, schema, and view.

​
3. Column Management

Visible Columns
  • Show/hide columns based on user needs
  • Drag and drop to reorder columns
  • Search through available columns
  • Lock important columns in place
Hidden Columns
  • Store additional data without cluttering the view
  • Quick toggle to show/hide groups of columns
  • Maintain data integrity while optimizing display
Column Features:
  • Calculated Columns: Create formulas like Total Revenue = Quantity × Price
  • Data Types: Automatic detection and formatting
  • Sorting: Click column headers to sort data
  • Filtering: Use advanced filters for precise data selection

​
Calculated Columns - Advanced Data Transformation

Understanding Calculated Columns Calculated columns are one of the most valuable features in Elementum tables. They allow you to create new data columns based on existing table data using Excel-like formulas. This transforms your static data into dynamic insights. What You Can Do:
  • Mathematical Operations: Revenue = Quantity × Price
  • Date Calculations: Days_Open = TODAY() - Created_Date
  • Conditional Logic: Risk_Level = IF(Amount > 10000, "High", "Low")
  • Text Manipulation: Full_Name = CONCAT(First_Name, " ", Last_Name)
  • Aggregations: Total_Orders = COUNT(Orders.ID)
Column Configuration:
  • Column Name: Give your calculated column a descriptive name
  • Function: Enter your formula using Excel-like syntax
  • Format Options:
    • Format as percentage
    • Format as percentage and multiply by 100
    • Format as currency
    • No formatting (default)
Available Functions: Elementum supports a comprehensive set of calculation functions similar to Excel, including:
  • Mathematical: SUM, AVERAGE, MIN, MAX, ROUND, POWER, SQRT
  • Logical: IF, AND, OR, NOT
  • Date/Time: DATE, NOW, DATEDIF, WEEKDAY, YEAR, MONTH, DAY
  • Text: CONCAT, LEFT, RIGHT, MID, UPPER, LOWER, TRIM
  • Lookup: VLOOKUP, COUNTIF, SUMIF, SEARCH, FIND
For a complete list of functions and their syntax, see our Calculations Reference. Real-World Examples: Sales Analytics: 
Commission = IF(Deal_Value > 50000, Deal_Value * 0.05, Deal_Value * 0.03)
Days_in_Pipeline = DATEDIF(Created_Date, NOW(), "D")
Deal_Size_Category = IF(Deal_Value > 100000, "Enterprise", IF(Deal_Value > 10000, "Mid-Market", "SMB"))
Customer Analysis: 
Customer_Lifetime_Value = SUM(Orders.Total_Amount)
Last_Order_Days = DATEDIF(MAX(Orders.Order_Date), NOW(), "D")
Customer_Status = IF(Last_Order_Days > 365, "Inactive", "Active")
Financial Reporting: 
Profit_Margin = (Revenue - Cost) / Revenue * 100
Quarterly_Growth = (Current_Quarter - Previous_Quarter) / Previous_Quarter * 100
Budget_Variance = Actual_Amount - Budget_Amount
Performance Consideration: While calculated columns are flexible, creating too many complex calculations can impact table performance. Each calculated column requires processing time when the table loads. For optimal performance:
  • Limit complex calculated columns to essential business logic
  • Use simple calculations when possible
  • Consider pre-calculating values in your data source for frequently used complex formulas
  • Monitor table load times and optimize as needed
  • Increase warehouse size for better performance with complex calculations and large datasets
Excel-Like Syntax: If you’re familiar with Excel formulas, you’ll feel right at home with Elementum’s calculation syntax. The functions work similarly to Excel, making it easy to create advanced data transformations without learning new syntax.
Filter Options:
  • Field Selection: Choose any column for filtering
  • Operators: Contains, equals, greater than, less than, etc.
  • Values: Direct input or dropdown selection for known values
  • Condition Groups: Combine multiple filters with AND/OR logic
Filter Management:
  • Add Condition: Build complex filter logic
  • Condition Groups: Organize related filters
  • Clear All: Reset filters quickly
  • Save Views: Store frequently used filter combinations

​
5. Data Operations

Join Operations
  • Visual interface for combining tables
  • Real-time preview of join results
  • Field mapping assistance
  • Performance optimization suggestions
Data Cleaning
  • Fix formatting issues automatically
  • Standardize values across columns
  • Remove duplicates
  • Handle missing data gracefully

​
Real Business Examples

​
Sales Pipeline View

Tables: Opportunities + Accounts + Contacts
Join: Opportunity.AccountID = Account.ID
Calculated: Potential Revenue = Amount × Probability
Filter: Stage = "Proposal" AND Region = "West"
Result: Active opportunities with contact details and revenue potential

​
Customer 360 View

Tables: Customers + Support Tickets + Orders
Join: Customer.ID = Tickets.CustomerID = Orders.CustomerID
Calculated: Lifetime Value = SUM(Order.Total)
Filter: Last Contact < 90 days
Result: Complete customer history with support and purchase context

​
Operations Dashboard

Tables: Inventory + Orders + Shipping
Join: Orders.ProductID = Inventory.ProductID
Calculated: Stock Level = Inventory.Quantity - Orders.Pending
Filter: Stock Level < Reorder Point
Result: Real-time inventory status with reorder alerts

​
Best Practices

​
Performance Optimization

Join Strategy:
  • Start with your primary data source
  • Join only necessary tables
  • Use indexed fields for joins when possible
  • Limit joins to 3-4 tables for optimal performance
  • Increase warehouse size for better performance with complex multi-table joins
Each join multiplies data processing requirements. More than 4 joins can significantly slow down your views. Consider creating pre-joined summary tables for complex views you use frequently.
Query Optimization:
  • Apply filters before joins when possible
  • Only display columns you need
  • Use calculated columns sparingly
  • Archive old data regularly
  • Increase warehouse size for better performance with large datasets and complex operations

​
Data Governance

Naming Conventions:
  • Use clear, descriptive table names
  • Include data source and purpose
  • Example: Sales_Pipeline_Q4_2024
Access Control:
  • Configure appropriate permissions
  • Document table purposes and owners
  • Regular access reviews
Data Quality:
  • Validate join results
  • Monitor for data inconsistencies
  • Set up alerts for missing data

​
User Experience

View Design:
  • Keep views focused and simple
  • Group related columns together
  • Use meaningful column headers
  • Provide context through descriptions
Performance Monitoring:
  • Track query execution times
  • Monitor user engagement
  • Optimize frequently used views
Remember: Tables and views are your window into business data. They transform raw information into actionable insights, helping everyone make better decisions faster while maintaining data integrity and performance.