# Introduction Codatum is a next-generation collaborative data notebook.
## **What is Codatum?** Codatum is a powerful tool for data preprocessing and analysis, focused on SQL. It allows you to seamlessly perform tasks on a data warehouse, enabling quick and accurate analysis of large datasets. With Codatum, data analysis becomes collaborative, enjoyable, and secure. Enjoy your data analysis journey with Codatum! ## **What you can achieve with Codatum** Codatum significantly enhances your team's analysis capabilities with the following key benefits: * Unrestricted Deep Analysis: * As an SQL-based tool, Codatum allows for complex and detailed analysis without any limitation often found in GUI-based tools. This flexibility enables you to uncover deeper insights from your data. * Efficient Workflow with Keyboard-First Design: * Codatum integrates SQL blocks, data tables, and charts into a flow-based editor. This keyboard-first approach lets you write SQL queries and analyze results swiftly, making your data exploration more efficient and intuitive. * Secure and Versatile Sharing: * With a variety of sharing options and detailed permission settings, Codatum ensures that you can share your analysis securely according to different requirements. Collaborate with team members or share results with stakeholders effortlessly. * Real-Time, Fresh Data: * By connecting directly to your data warehouse, Codatum ensures that your data is always up-to-date and reliable. This direct access to the source of truth guarantees the accuracy and freshness of your analysis. * Accelerated Analysis Cycle: * Codatum allows you to perform data preprocessing, modeling, and analysis in one place. This integration speeds up your analysis cycle, enabling iterative adjustments and continuous insights. By leveraging these benefits, Codatum transforms your data analysis process, making it more flexible, efficient, and collaborative. ## What's next? * [**Quick Start**](/getting-started/quick-start) * New to Codatum? Set up your workspace and start right away with this guide. * [**Concept (Further reading)**](/getting-started/concept-further-reading) * Curious about the ideas behind Codatum? Dive deeper into the core concepts and challenges it addresses. # Basic Terminology and Operations This page explains Codatum's basic screen operations and terminology. The Codatum interface consists of three main areas: **Navigation (①)** on the left, **Sidebar (②)** in the center, and **Main Area (③)** on the right. ![Codatum Interface Overview](/files/pbnJUT3thBR2XB4s5rwC) ## Basic Terminology
TermDescription
WorkspaceA working space separated by team or project. You can organize by department or project unit. You can create folders and notebooks within them for purpose-based classification. One person can belong to multiple workspaces, and you can switch between workspaces using the icon in the upper left.
Data SourceA database where your company's data is stored (BigQuery, Snowflake, etc.).
ConnectionConnection settings that link data sources to CODATUM. Administrators configure authentication information and connection destinations, and by sharing with workspace members, data can be used in notebooks and catalogs. Multiple connections can be used interchangeably.
CatalogA feature to search and browse available tables. You can preview table contents and check column descriptions. Use this when you want to "explore what data is available."
NotebookThe main workspace for data analysis. You can combine queries (data retrieval), charts (graphs), and text (descriptions) into one document. Real-time collaborative editing is supported.
Document PageOne of the notebook page types. Document format where you can write SQL and text, suitable for recording analysis processes and creating reports with explanations.
Grid PageOne of the notebook page types. Tile format where you can freely arrange charts and tables, suitable for creating dashboards and KPI monitoring screens.
ExplorerA feature to easily explore table data. You can filter, aggregate, and create graphs without creating a notebook. Can be opened directly from the catalog.
TableA table where data is organized and stored. One data source contains multiple tables.
SQL BlockThe unit of SQL elements in a notebook. You can edit and execute queries, display results, check statistics, and create charts.
ReportA feature to publish and share notebooks as a "final version." Read-only with the option to set up periodic automatic updates.
EmbedA feature to embed notebooks or reports into external websites or apps. You can share data outside CODATUM, such as internal portals or customer-facing dashboards. Available for enterprise plan users.
WorkflowA feature to automate data processing and notifications. You can set up complex automation scenarios such as periodic notebook execution, conditional processing, and coordination of multiple tasks.
AI AssistantAn AI assistant that supports data analysis. Assists with SQL creation, data trend explanations, optimal graph suggestions, and more to streamline analysis work.
## Navigation The narrow dark gray area on the far left of the screen. Use this to switch between Codatum's main features.
IconNameDescription
NotificationsNotificationsCheck new notifications. A red dot appears when there are unread items
Quick SearchQuick SearchQuickly search for notebooks and tables
NotebookNotebookUse notebooks to perform data analysis, generate graphs, and create reports
QueryQueryCreate general-purpose queries that can be used across multiple notebooks (for intermediate to advanced users)
CatalogCatalogSearch tables and manage metadata. Use when you want to know what data is available or view table contents
WorkflowWorkflowUse for data processing automation and periodic tasks
ReportReportView created reports
AI FeaturesAI FeaturesUse AI assistant for analysis support
SettingsWorkspace SettingsUser management, theme changes, connection settings, plan confirmation, and more
## Sidebar The area that displays the contents of the feature selected in the navigation in more detail. | Name | Description | | ---------------------- | ----------------------------------------------------------------------------------------- | | All Members\_Workspace | Displays notebooks accessible to everyone in the workspace. Can be organized with folders | | Shared Folder | Notebooks shared with users or groups to which the user belongs | | Private | Displays notebooks accessible only to you. Other accounts cannot see them | ## Main Area The largest area where actual work content is displayed. On the home screen, you can see the latest information about currently logged-in members, recently viewed notebooks, notebooks you created, and more. # Quick Start Whether you're an analyst, data scientist, or business user, Codatum simplifies connecting, analyzing, visualizing, and sharing your data and insights. ## Connect to Your Data To begin, you will need to connect Codatum to your data source. We currently support Google BigQuery and Snowflake, allowing you to access and analyze large datasets directly from the cloud.
**How to Create a Connection** 1. **Navigate to Connections**: Go to `Workspace settings` at the bottom left of any page, then navigate to `Connections` and click `New connection`. 2. **Upload Credentials**: Upload the JSON key file of a Service Account and enter the Connection Details. 3. **Sync Targets**: Add your Google Cloud Project ID and select the dataset for metadata sync. Once your data is connected, you are ready to dive into analysis. Refer [here](/connect-and-manage-data/supported-warehouses) for detailed steps on creating a connection and troubleshooting issues. ## Analyze Your Data Codatum’s notebook is designed to make your analysis SQL-centered and easy to understand. It combines SQL's flexibility with markdown's clarity, allowing you to build and share insights directly in one tool.
#### How to Analyze Your Data 1. **Create a notebook**: Start by creating a new notebook. 2. **Create a SQL Block**: In the body of the page, create a new SQL Block by typing `/sql` and selecting it from the menu that appears. Make sure to select the correct connection in the top right corner of the SQL Block. 3. **Write and Execute Queries**: Use the SQL Block to write and execute queries. Codatum's integrated query editor provides syntax highlighting and autocomplete to streamline your workflow. 4. **Explore Query Results**: View the results displayed below the SQL Block. You can reference these results in other SQL Blocks to build more complex queries while keeping each block simple and readable. 5. **Document Your Analysis**: Use markdown within the same document to annotate your SQL queries, explain your logic, and provide context. This makes sharing your analysis with colleagues and stakeholders easier. Once your analysis is complete, you are ready to create compelling visualizations. ## Add Visualizations Visualizing data helps uncover patterns and make decisions. Codatum offers intuitive tools to help you create charts and dashboards that bring your data to life. #### How to Add Visualizations 1. **Create a Chart**: Convert query results into charts using the top bar of the query results. Choose your preferred chart type and customize it as needed.
2. **Build a Dashboard**: Organize your visualizations into a dashboard using the [Grid Page](/data-exploration/notebook/grid-page). Add a Grid Page from the `+` icon at the top left of the notebook. With your data visualized, you are now equipped to make data-driven decisions and share insights effectively. ## **Share Insights** Sharing your work in Codatum is flexible, allowing you to choose the level of access depending on the audience: * **Share for Real-Time Collaboration**: Share the notebook with team members who will actively collaborate with you. * **Share as a Completed Interactive Report for External Users**: If you need to share an interactive but finalized version of your notebook with external users (e.g., stakeholders outside your team), create a [Report](/sharing/report). * **Share as a Public link**: If you need to share a static, read-only document without requiring authentication, generate a [Public link](/sharing/public-link-and-embed) that anyone can access who knows the URL. By tailoring your sharing options, you can ensure that your data and insights are accessible to the right people with the appropriate permissions. ## **Invite Your Team Members** Invite your team members to collaborate on Codatum by following these steps: 1. **Navigate to `Workspace settings`**: Go to the `Users` section. 2. **Invite Members**: Click `Invite users` and send an invitation via link or email. Additionally, you can set up automatic joining for your workspace by specifying `allowed email domains`. Anyone with an email address that matches one of the specified domains will be able to join your workspace automatically. # Concept (Further reading) Deep dive into the concept of Codatum.
## The Problem: Data Fragmentation and Stagnation Modern enterprises hold vast amounts of data in a different formats. This data needs to be reshaped, transferred, downsized, and accessed by different teams through different interfaces. This process makes it challenging to access all the data, return to the original data, and coordinate tasks across teams, leading to stagnation in the analysis cycle. ## The Solution: Unified User Interface To solve this, a unified user interface capable of handling everything from large-scale to small-scale data, and from reshaping to analyzing data, is essential. #### SQL Challenges and Codatum’s Solution SQL has long been a code-based interface for data and should have functioned as this unified interface. However, SQL faces several challenges, such as difficulty in previewing, saving, sharing, and dividing queries. By seamlessly integrating SQL with a modern user interface and modern concept, we believe that Codatum can overcome the fragmentation of data, processes, and teams, significantly accelerating the analysis cycle for enterprises. ## How Codatum Solves These Challenges #### **"Deep" analysis** Many BI tools are designed for ease of use, targeting business users for simple tasks. However, data analysis is inherently complex. Existing tools often fail to provide the depth needed for meaningful insights, and are too restrictive for data scientists and engineers. {% hint style="success" %} With Codatum: * It goes beyond simple tasks, requiring some technical knowledge like coding, and supports deeper analysis. * It enables technically skilled team members to fully engage in data analysis. {% endhint %} #### **Development, management, and sharing of SQL** SQL is central to data analysis but challenging to manage and share, especially when dealing with complex queries. {% hint style="success" %} With Codatum: * A keyboard-centric UI lets you modularize SQL and develop while viewing results, making complex data easier to handle. * It enhances collaboration, allows for folder-based management, and enables the creation of beautiful dashboards for sharing. {% endhint %} #### **Fresh and Correct Data** Jupyter Notebook has long been a staple in data analytics, but its traditional workflow, involving data transfers from Data Lakes to Data Marts and then into Python sessions, is becoming outdated for real-time analysis. These tools, focused less on SQL, often blur the boundaries necessary for efficient data handling. {% hint style="success" %} With Codatum: Access fresh, accurate source-of-truth data directly from Data Lakes or Data Warehouses, facilitating more precise data analysis. {% endhint %} #### **Fast cycle of modeling and analysis** Collecting and organizing quality data is crucial for complex analyses, which should be iterative rather than linear. Codatum enables seamless SQL integration for both data modeling and analysis on the same platform, reducing unnecessary delays during analysis cycle. {% hint style="success" %} With Codatum: Preprocessing, modeling, and SQL subquery management are integrated on a single platform, speeding up the data preparation and analysis cycle. {% endhint %} #### **Detailed security features for analytical flexibility** Security and privacy are critical in data analysis, requiring tight access control to minimize risks while maximizing data utility. Without detailed permissions, analytical flexibility is severely limited, diminishing the value of data assets. {% hint style="success" %} With Codatum: Granular permission controls enhance security and allow for expanded data access, ensuring that data reaches only those who need it without compromising privacy. {% endhint %} ## Key Takeaways Codatum is designed to leverage the power of data to strengthen teams. By eliminating data fragmentation and promoting collaboration across teams, Codatum maximizes your team’s potential with a data-driven approach. Enjoy the benefits of using Codatum! ## References Read more about our choice of block editor over cell-based editor here. [Why did we a choose Block Editor over Cell-based Editor?](/getting-started/concept-further-reading/why-did-we-choose-block-editor-over-cell-based-editor) # Why did we a choose Block Editor over Cell-based Editor? Reason why we chosen the Block Editor for Codatum At Codatum, we believe that data analysis should be flexible and easy to use without being limited by the tools we use. Our goal is to allow users to construct their thoughts and analyses as freely as they would in a notebook. By using a block editor, users can intuitively build their content and freely move blocks around, much like organizing thoughts in a memo pad. Furthermore, the block editor allows for continuous work with unified keyboard operations (including some special shortcuts for SQL execution, etc.). It is fundamentally constructed with the same text format, enabling users to stay focused on their work without interrupting their thought process.
Since the 2000s, block editors like Evernote and Notion have evolved rapidly. These modern interfaces offer an unparalleled combination of intuitive operation and flexibility, allowing users to manage their thoughts effortlessly. While cell-based editors have their own advantages, such as clear segmentation of code and outputs, they present a slightly older approach. This method can sometimes disrupt the flow of thought by imposing a rigid structure. Additionally, cell-based editors are often weaker in real-time collaboration, making it difficult for teams to work together effectively. Data analysis is not something that is done alone; it involves working together and sharing knowledge. At Codatum, we prioritize real-time collaboration. By adopting a modern block editor approach, multiple users can edit the document simultaneously, with changes reflecting instantly and without conflicts, making teamwork smooth and efficient. At Codatum, we have chosen the modern block editor approach to ensure that the thought process is smooth and that real-time collaboration is seamless. This allows our users to focus on their data analysis without unnecessary distractions, making the process as smooth and efficient as possible. # Connection Settings to connect Data Warehouses and manage metadata of tables
Connection is used to manage access for Data Warehouses.\ It includes authentication credentials uploaded by users to execute queries and retrieve table information. ## Key Features * Run queries: * Issue queries with authentication in [Notebooks](/data-exploration/notebook) , [Reports](/sharing/report) and Queries * A caching function works to prevent unncessary job execution. For more information, please check [(Appendix) Query Execution and Cache](/data-exploration/appendix-cache) * Aggregate metadata: * Aggregate schema metadata using [Table Sync](/connect-and-manage-data/connection/sync-table-metadata) to maintain up-to-date and comprehensive data records. * Access control: * Manage access control to restrict which users and groups can execute queries and retrieve the results of the execution. See details in [Access Control for Connection](/access-control/access-control-for-resources/access-control-for-connection). ## How to Set Up * Create and configure the settings, including uploading authentication information. Please refer to the setup guide for each warehouse. * [Supported Warehouses](/connect-and-manage-data/supported-warehouses) * Trigger synching to retrieve and save the metadata of tables. * [Sync Table Metadata](/connect-and-manage-data/connection/sync-table-metadata) # Sync Table Metadata Syncing table metadata within connections By syncing table metadata, information about tables and columns is periodically saved to Codatum, ensuring up-to-date data records. {% hint style="info" %} Scheduled updates run weekly. For immediate updates, please execute manual sync. {% endhint %} The results of the query execution are output independently of table metadata sync status. If the execution results are not updated, cached results may be displayed. For details, please check [(Appendix) Query Execution and Cache](/data-exploration/appendix-cache). ## Key Features * Aggregate table metadata: * Table structures including: * Dataset names * Schema names * Table names * Table descriptions * Row count * Table size (bytes) * Table creation and update date * Column names, types, and descriptions * Automatically run sync jobs: * Interval: Weekly * Manual sync: * Trigger syncing manually from admin panel * Real-time sync status tracking: * Monitor the sync status in real-time with statuses such as: * QUEUED * PROCESSING * SUCCESS * WARNING * FAILED ## Where Table Metadata is Displayed Synced table metadata is utilized in the following features: * [Catalog](/connect-and-manage-data/catalog) * [SQL Block](/data-exploration/notebook/sql-block) * Auto-completion * Column information displayed in table overviews across various features ## Permissions * Access to synced table metadata is limited to users who have read permissions for the connections associated with those tables. * If multiple connections reference the same table, they are treated as a single table metadata entry. If read permissions are granted to any of the connections, the table metadata can be accessed. ## Limitation * The number of tables: depends on the plan. * * Tables that can be synced per schema: Up to 1000. * Columns that can be synced per table: Up to 1000. ## Troubleshooting: Can not find the table in our data warehouses 1. Check the sync status and ensure there are no errors. 2. Verify the database and tables selected for syncing. 3. Ensure the connection credentials have the necessary permissions to reference the table. 4. Perform a manual sync if necessary. # Supported Warehouses List of data warehouses supported by Codatum Currently we support the following data warehouses. Please see the following page for information on how to connect to the data warehouse. If you would like to connect other data sources, please contact us at `support@codatum.dev`. {% content-ref url="/pages/ofXZ6wnCFY7b1Y9EVK7X" %} [BigQuery](/connect-and-manage-data/supported-warehouses/bigquery) {% endcontent-ref %} {% content-ref url="/pages/yJ5sA9q3eV80oq5GcOcX" %} [Snowflake](/connect-and-manage-data/supported-warehouses/snowflake) {% endcontent-ref %} {% content-ref url="/pages/m79ocQwTbueD7MGJi3MU" %} [Redshift](/connect-and-manage-data/supported-warehouses/redshift) {% endcontent-ref %} {% content-ref url="/pages/07YLUmQjJLlkqWaz0hsg" %} [Databricks](/connect-and-manage-data/supported-warehouses/databricks) {% endcontent-ref %} {% content-ref url="/pages/A3Jre6m61noaxT5yqfL8" %} [Other Warehouses](/connect-and-manage-data/supported-warehouses/other-warehouses) {% endcontent-ref %} # BigQuery How to set up the BigQuery connection and additional notes on using it
With Codatum, you can connect, query, and manage data of BigQuery. ## How to Set Up ### Steps in BigQuery First, perform the following operations in the BigQuery console: * Generate a service account and service account key * Grant the following roles or permissions to the target project: * Role `BigQuery Job User` or the following permissions: * `bigquery.jobs.create` * Role `BigQuery Read Session User` or the following permissions: * `bigquery.readsessions.create` * `bigquery.readsessions.getData` * `bigquery.readsessions.update` * Save the target`Project ID` as it is necessary for the later [steps in Codatum](#steps-in-codatum) * Grant the following roles or permissions to the target datasets: * Role `BigQuery Data Viewer` or the following permissions: * `bigquery.tables.getData` * `bigquery.datasets.get` * `bigquery.tables.get` * `bigquery.tables.list` * (Optional) Grant the following permissions to the target project if you want to avoid entering the Project ID each time you sync the dataset table information * `resourcemanager.projects.get` ### Steps in Codatum Finally, create Connection in Codatum 1. In the `Workspace Settings` -> `Connections`, select `Add Connection` 2. Enter a `Connection Name` 3. Select the `Access Level` 4. Upload the service account key using `File upload` 5. Enter the `Project ID` * Use the `Project ID` you saved in the steps above. 6. Add datasets to the `Sync target` ## Use INFORMATION\_SCHEMA for Searching If you grant the `bigquery.admin` permission to the target project, you can utilize INFORMATION\_SCHEMA tables to search any tables in non-synced datasets. # Snowflake How to set up the Snowflake connection and additional notes on using it
With Codatum, you can connect, query, and manage data of Snowflake. ## How to Set Up ### 1. Prepare a Key-Pair and Snowflake User Currently, only key-pair authentication is supported. Refer to the [Snowflake user-guide key-pair-auth](https://docs.snowflake.com/en/user-guide/key-pair-auth) for instructions on creating a key pair and associating the public key with the Snowflake user you plan to use for the connection. ### 2. Grant the Necessary Privileges You must grant privileges to the role assigned to the Snowflake user: * Prepare a Role * Create a new role or use an existing one, then assign it to the user you created. * Warehouse Privileges * Grant the `USAGE` privilege on the warehouse you want to use to that role. * Database Privileges * Grant the `USAGE` privilege on the database you want to use to that role. * Schema Privileges * Grant the `USAGE` privilege on the schema you want to use to that role. * Table Privileges * Grant the `SELECT` privilege on the table you want to use to that role. ### 3. Register the Snowflake Connection in Codatum Finally, create Snowflake Connection in Codatum 1. In the `Workspace Settings` -> `Connections`, select `Add Connection` 2. Enter a `Connection Name` 3. Select the `Access Level` 4. Enter the `Organization name` * The name of your Snowflake organization. See [Snowflake user-guide admin-account-identifier](https://docs.snowflake.com/en/user-guide/admin-account-identifier#label-account-name-find) for more details. 5. Enter the `Account name` * The name of your Snowflake account. (Be careful not to confuse this with the full account identifier, which includes the organization name.) See [Snowflake user-guide admin-account-identifier](https://docs.snowflake.com/en/user-guide/admin-account-identifier#label-account-name-find) for more details. 6. Enter the `User name` * Use the User name you prepared in the step1. 7. Enter the `Role` * Use the Role name you prepared in the step2. 8. Enter the `Warehouse name` * Use the Warehouse name you prepared in the step2. 9. Enter the `Database name` * Specifies the default database in which statements are executed (like “namespace”). This setting does not limit which tables or schemas you can query. 10. Enter the `Private key` * Use the Private key you prepared in the step1. * Paste the entire private key, including the lines `\-----BEGIN PRIVATE KEY-----` and `-----END PRIVATE KEY-----` 11. Add datasets to the Sync target ## Network and Firewall Restrictions Currently, Codatum does not provide a fixed IP address for connections. If you need to whitelist IP addresses in Snowflake for security reasons, please contact Codatum Support to discuss possible options. # Redshift How to set up the Redshift connection and additional notes on using it With Codatum, you can connect, query, and manage data of Redshift. {% hint style="warning" %} This feature is currently in preview. Functionality and availability may change without notice. We'd love your feedback. {% endhint %} ## How to Set Up Redshift integration targets RA3 node clusters and serverless configurations. ### 1. Necessary preparations in AWS Console #### 1. Grant the Necessary Privileges Create a role for integration and grant privileges. * Grant `USAGE` privilege on the schema you want to use to that role * Grant `SELECT` privilege on the tables you want to use to that role * Grant `SELECT` privilege on the system view `svv_table_info` to that role #### 2. Create Secret in Secrets Manager * Specify the Data Warehouse you want to connect to and create a Secret * Add a tag named `RedshiftDataFullAccess` to the created Secret (Value can be empty) #### **3. Create IAM User and Grant Policies** * Create an IAM user for integration and grant the following policy ```json { "Version": "2012-10-17", "Statement": [ { "Sid": "DataAPIPermissions", "Effect": "Allow", "Action": [ "redshift-data:BatchExecuteStatement", "redshift-data:ExecuteStatement", "redshift-data:CancelStatement", "redshift-data:ListStatements", "redshift-data:GetStatementResult", "redshift-data:DescribeStatement", "redshift-data:ListDatabases", "redshift-data:ListSchemas", "redshift-data:ListTables", "redshift-data:DescribeTable" ], "Resource": "*" }, { "Sid": "SecretsManagerPermissions", "Effect": "Allow", "Action": [ "secretsmanager:GetSecretValue" ], "Resource": [ "Specify the Secret ARN created in step 1-2" ], "Condition": { "StringLike": { "secretsmanager:ResourceTag/RedshiftDataFullAccess": "*" } } }, { "Sid": "ServiceLinkedRole", "Effect": "Allow", "Action": "iam:CreateServiceLinkedRole", "Resource": "arn:aws:iam::*:role/aws-service-role/redshift-data.amazonaws.com/AWSServiceRoleForRedshift", "Condition": { "StringLike": { "iam:AWSServiceName": "redshift-data.amazonaws.com" } } } ] } ``` #### **4. Additional Steps Required Only for Serverless** * Add a tag named `RedshiftDataFullAccess` to the Workgroup you want to integrate with (Value can be empty) * Create the following policy in IAM and grant the following policy ```json { "Version": "2012-10-17", "Statement": [ { "Sid": "ServerlessWorkgroupInfo", "Effect": "Allow", "Action": [ "redshift-serverless:GetWorkgroup" ], "Resource": "*" } ] } ``` ### 2. Register Redshift connection in Codatum Finally, create a Redshift connection in Codatum. * Select `Add Connection` from `Connections` in `Workspace Settings` * Enter `Connection Name` * Select `Access Level` * Enter `Account ID` * This is your AWS account ID. * Enter `Region` * This is the region where your Redshift resources are located. * Select `Connection Type` * If you select Cluster: * Enter `Cluster Identifier` * Enter `Default Database` * This is the target database for connection. This setting does not restrict queryable databases, schemas, or tables. * If you select Serverless: * Enter `Serverless Namespace` * Enter `Serverless Workgroup Name` * Enter `Default Database` * This is the target database for connection. This setting does not restrict queryable databases, schemas, or tables. * Enter `AWS Access Key` * Use the access key of the IAM user prepared in Step 1. * Enter `AWS Secret Access Key` * Use the secret access key of the IAM user prepared in Step 1. * Enter `Secret ARN` * Use the Secret ARN of the Secret prepared in Step 1. Once you have completed the input, perform a connection test and then save the connection. # Databricks How to set up the Databricks connection and additional notes on using it With Codatum, you can connect, query, and manage data of Databricks. {% hint style="warning" %} This feature is currently in preview. Functionality and availability may change without notice. We'd love your feedback. {% endhint %} ## How to Set Up Databricks integration targets Serverless SQL Warehouses. ### 1. Necessary preparations in Databricks Console Creat a service principal for integration and assign the appropriate permissions. #### Account Admin Page * From the menu, go to User Management > Service Principals and create a service principal for integration * In the service principal's Credentials and Secrets tab, generate an OAuth secret. Save the client ID and secret at this time * In the service principal's Roles tab, turn OFF all settings * From the menu, select Workspaces, choose the workspace to integrate, go to the Permissions tab, and add the created service account #### Workspace Admin Page * Grant the service principal "Can Use" permission for the SQL Warehouse to connect * From the menu, go to SQL Warehouses and select the warehouse to integrate * Click the Permissions button in the upper right and grant the service principal `Can Use` permission * Grant the service principal permissions to use the catalog * From the menu, go to Catalogs and select the catalog to integrate * In the Permissions tab, click the Grant button, select the service principal, and grant `SELECT`, `USE CATALOG`, and `USE SCHEMA` permissions ### 2. Register Databricks connection in Codatum * From `Workspace Settings`, go to `Connections` and select `New Connection` * Enter the `Connection Name` * Enter the `Access Level` * Enter the `Server Host Name` * In Databricks Workspace Console, go to SQL Warehouses from the menu, select the warehouse to integrate, and find the Server Hostname in the Connection Details tab (e.g., `.cloud.databricks.com`) * Enter the `HTTP Path` * In Databricks Workspace Console, go to SQL Warehouses from the menu, select the warehouse to integrate, and find the HTTP Path in the Connection Details tab (e.g., `/sql/1.0/warehouses/`) * Enter the `Warehouse ID` * In Databricks Workspace Console, go to SQL Warehouses from the menu, select the warehouse to integrate, and find the ID next to the Name in the Overview tab * Enter the `Client ID` * The client ID of the service principal created in step 1 * Enter the `Client Secret` * The client secret of the service principal created in step 1 Once you've entered all the information, run a connection test and save the connection. # Other Warehouses Other data warehouses
We plan to expand support for other data warehouses in the future. If you have a specific data warehouse you would like us to prioritize, please contact us at `support@codatum.dev`. # Catalog Extend data metadata manually and improve data searchability
The catalog is designed to manage metadata for Tables and Queries, making it easier to organize and retrieve your data. ## Key Features * Annotation: * Add descriptions and tags to tables and columns to provide more context and improve understanding. * Enhance searchability: * Use annotations or tags to improve keyword searches, making it easier to find the data you need quickly. * Popularity: * Track table popularity, calculated by the number of referenced queries in Notebooks, helping you identify the most frequently used tables. * References: * Identify which notebooks and jobs refer to the table, and achieve a clear understanding of data dependencies and usage. # Quick Search Search anything in Codatum
You can search for various resources, such as Notebooks, Tables, Queries, across the Workspace. ## Key Features * Full-text search: * Notebook: Searches within the notebook name and the body, including SQL statements * Table and Query: Searches its name, schema information, and the description assigned via the Catalog # Data Exploration Overview Analyze and visualize data easily using Notebooks, Charts, Explorer, and Parameters. In Codatum, you can perform ad-hoc data analysis in a notebook by incrementally building queries using SQL blocks, where each block can reference queries defined in other blocks. Additionally, these results can be displayed in various charts, and by using features like Parameters and Explorer, you can easily delve deeper into the data through a GUI-based interface. ## Key Tools and Features * Notebook: * Notebooks support real-time collaborative editing, allowing multiple users to work together simultaneously, add comments, and mention other users. * Using the flow-based block editor (Doc Page) and SQL blocks, you can conduct ad-hoc data extraction and complex data analysis while inserting interpretative text results. Additionally, you can create visually appealing charts and arrange them neatly in tile-based documents (Grid Page) to observe and share analysis results. * Chart: * The results of data analysis extracted using notebooks can be expressed clearly and beautifully using various charts. You can choose from different chart types to best represent your data and insights. * These charts can then be shared through reports or embedded directly into websites and blogs for broader dissemination. * Explorer: * In notebooks and reports, you can use drag-and-drop and spreadsheet-like UI to easily filter, extract, aggregate, and modify data without coding. * Explorer provides a graphical interface that simplifies complex data operations, making it accessible for users of all skill levels. * Parameter: * Using the Parameter Widget in Notebooks, you can make Notebooks and Reports dynamic and interactive. This allows you to control embedded SQL through parameters without directly accessing SQL, and obtain tailored outputs as needed. # Notebook A collaborative space for real-time data analysis and visualization.
Notebook supports real-time collaborative editing, enabling multiple users to work together, add comments, and mention others. It also facilitates ad-hoc data extraction and complex analysis using flow-based documents (Doc Page) and SQL Blocks, allowing the creation of visually appealing charts arranged neatly in tile-based documents (Grid Page). ## Key Features * Real-time Collaboration: * Edit collaboratively in real-time, add comments, and mention users. * Two Page Types: * [Doc Page](/data-exploration/notebook/doc-page) * Conduct ad-hoc analyses and keep track of the analysis process. * [Grid Page](/data-exploration/notebook/grid-page) * Arrange charts attractively. * [SQL Block](/data-exploration/notebook/sql-block) * A powerful editor for intuitive SQL writing. * [Versions](/data-exploration/notebook/version) * Save and restore analysis states automatically and manually. * Lock: * Lock the completed notebook to prevent accidental editing. * There are three types of locks: * Unlocked (default): All editing is allowed. * Query only: Allows editing of parameters and query execution. * Locked: Allows only editing of comments. * [Notebook Theme](/data-exploration/notebook/notebook-theme) * Customize the appearance of notebooks, including chart colors, font sizes, and font families. # Doc Page A keyboard-first editor for seamless and efficient ad-hoc analysis and visualization.
Doc Page is a page in notebook where you can extract, explore, and visualize data analysis. It allows for quick ad-hoc analysis and keeps a record of your process. The entire workflow can be managed smoothly using a keyboard-first user interface. ## Key Features * Flow-style Block Editor: * Enables seamless and uninterrupted writing with a vertically flowing editor, allowing users to focus on their work without switching between the keyboard and mouse frequently. * Diverse Block Types: * Construct pages using various block types, such as SQL, chart, image, layout, toggle, etc. Easily accessible with a powerful slash command. * Seamless Integration with Code: * Ensures smooth connection with SQL. * Keyboard-first Design: * Optimized for keyboard use, making it developer-friendly. * Full-text Search: * Allows comprehensive search capabilities within the document. * Parameter: * Supports the use of parameters to control the embedded SQL and other dynamic elements. # Grid Page A Grid Page allows you to arrange SQL results and charts in a customizable grid layout. A Grid Page is a page where you can easily arrange an execution result of the SQL and Chart created in a Doc Page in a grid layout. ## Key Features * Intuitive GUI manipulation: * Allows users to freely arrange, reorder, resize, and drag & drop various components, including chart components. * Realtime-collaboration: * Reflects changes made by other users on the grid immediately. (Note: Chart settings, etc., are not shared until explicitly saved.) * Enables users to add comments. * Parameter: * Enables users to place parameters and operate them easily. * Explorer: * Allows users to use the Explorer to gain further insights into their data. * Cross Filter (Beta): * Enables linking common legends and axes across multiple charts to synchronize data display targets. * For configuration constraints and other details, see [Cross Filter](/data-exploration/notebook/grid-page/cross-filter). ## Type of Components The components that can be placed in the grid are as follows: * Heading * A heading component with three levels can be used to separate titles and sections. * Text * Rich text that can be used for documentation. * SQL result table * A table that displays SQL results created in a Doc Page. * Chart * Allows users to place any chart created in a Doc Page. ## About Synchronization of Component Settings Certain settings are synchronized between the Grid Page and Doc Page, while others remain independent. * Synchronized with Doc Page: * Chart settings (e.g., axes fields, chart type) * Not Synchronized with Doc Page: * Component settings (e.g., header display, component title) # Cross Filter Link common legends and axes across multiple charts to synchronize data display targets. {% hint style="warning" %} This feature is in beta. The specifications may change without notice. {% endhint %} The Cross Filter feature allows you to synchronize the display targets of multiple charts when legends or axes are filtered on a specific chart. Filtering is performed on data loaded in the frontend, so changing filters does not trigger additional job execution. ## Behavior * Legends and axes selection status is synchronized across configured charts * When a legend on a chart is selected: Only data for the selected legends is displayed * When hovering over chart content: Highlighting of the hovered legend is synchronized * When selecting a range on an axis: Only data within the selected range is displayed ## Configuration * Select Cross Filter from the menu that appears in the top-right corner when hovering over a chart on the Grid Page * Set a `Key` * Set the same `Key` for charts that you want to synchronize with the same value * Depending on the selected chart, you can set `Keys` for both "Axis" and "Legend" * For table charts, set a `Key` for each column you want to target ## Specifications * Supported Charts * XY Chart * XYZ Chart * Pie Chart * Funnel Chart * Table Chart * Selection states from other charts are reflected in table charts, but filtering content from table charts is not reflected in other charts * Data Filtering Scope * Filtering is performed on data loaded in the frontend * SQL is not re-executed, so filtering data by time period on an XY chart axis cannot be reflected in pie chart aggregations * Legend Filtering * Filtering is performed based on the "string or numeric" values of the selected legend names * Axis Filtering * Range filtering is performed based on the "string or numeric" lower and upper bounds of the selected axis ## Notes * Legend → Axis Coordination * Data display targets for axes are specified by range * Therefore, when multiple legends are selected on the legend side, not only the selected legends but also targets included in the range of the selected legends will be displayed * Axis → Axis Coordination * When axes are set as strings, range specification is affected by display order, so if display orders are different, they may not coordinate correctly # SQL Block Professional SQL Editor with Powerful Support in Codatum.
SQL Block is a block within the notebook that allows executing SQL. Within the SQL Block, you can edit and run queries, display the results, view statistical information, and create charts. ## Key Features * Professional SQL Editor with Powerful Support: * Includes features like function and reserved word suggestions, auto-completion, syntax highlighting, and formatting. * Instant Result Preview: * Execute queries and preview results in real-time using Ctrl + Return. * Table Search: * Use the `/` command within SQL blocks to search for tables within datasets and their connections. You can also search by metadata and field names in the catalog. * Code Snippets: * Create code snippets for personal use or share them across the workspace. Customize settings like triggers and cursor positions after completion. * Rich Shortcut Keys: * Utilize a various shortcut keys, which can also be customized for personal use. * SQL Chain * Reference other SQL Blocks within an SQL Block. This helps manage complex SQL by breaking it down into more understandable and reusable components. * Field Stats: * Easily retrieve statistical information for each field, such as data type, presence of null values, unique count, average value, quantile, frequency, and histogram. ## How to Use * Creating an SQL Block: * While focused on an empty paragraph in a Doc Page, use the slash command (`/`) to select and create an SQL Block. * Creating References: * Within the SQL Block, you can use the slash command to create references to other SQL Blocks and tables. * You can also create references to Parameters and Saved queries. # Version Save and restore analysis states automatically and manually Versions can be saved and restored automatically or manually. If your data becomes corrupted or you need to revert to a previous state, you can restore it from a saved version. ## Key Features * Automatic Versions: * Automatically create versions when changes are made or after a certain time. * Old versions are automatically deleted to save space. * Manual Named Versions: * Manually create versions and give them names. * Manually created versions are maintained separately and are not overwritten by automatic versions. * Restore notebook: * Create a new notebook from a version (existing notebooks cannot be overwritten). * User-specific Parameters: * In a notebook, parameter values and their linked execution results are shared among multiple users. However, in a version, these can be held individually by each user. * This can also be realized in Reports. * Difference Comparison Feature * "Compare with current notebook" allow you to check changes between the currrent notebook and saved versions. * This feature is also accessible from the “Share” menu and allows comparison with published reports and public links. ## Parameter-URL Synchronization When you change parameters within a version, the changed parameter information is synchronized to the URL. Therefore, you can share parameter values with other users by sharing the URL. For details, see [How to Specify Parameters from URLs](/data-exploration/parameter-overview/set-parameters-via-url). ## SQL Result Cache Handling As long as the SQL Block Cache has not expired, you can access SQL execution results and charts. Even after losing the SQL Block Cache, you can re-execute the SQL Blocks in the snapshot, which will update the execution results and charts. ## **Frequency of Automatic Snapshots** Changes are not saved as individual snapshots for every edit. Instead, multiple changes are grouped together and saved in a single snapshot. Typically, changes made over a certain period, such as an hour, are saved together. # Theme Customize the appearance of notebooks By configuring the notebook theme, you can customize chart colors, font sizes, and font families within notebooks. ## Customizable Items ### Common Items * Base Font Size * The size specified here determines the text size and the size of each element in input forms * However, if "Base Font Size" is specified in the chart's "Advanced Settings", that takes precedence * Font Family * The font family specified here is applied to text within notebooks and text within charts ### Items for Light Theme and Dark Theme * Chart Colors * You can configure series colors for differentiating data series and gradient colors for gradients * Status Colors * You can configure three colors: success, warning, and danger * These colors are used for status expressions in charts and error messages * Primary Color * Focus Color * Link Color * Background Color * Text Color * Border Color Note: The items that can be customized are limited depending on the plan ## How to Configure ### Creating and Editing Notebook Themes * You can create and edit themes from "Workspace Settings" > "Notebook Theme" * You need "[Workspace Owner](/access-control/access-control-for-workspace)" permissions to create and edit notebook themes ### Applying Notebook Themes * You can apply existing notebook themes from "Notebook Theme" in the three-dot menu in the upper right corner of the notebook page * [Notebook Edit](/access-control/access-control-for-resources/access-control-for-notebook) permissions are required ## Scope of Impact * Notebook themes are also applied to the following resources created from notebooks: * [Report](/sharing/report) * [Public Link](/sharing/public-link-and-embed) * [Signed Embed](/sharing/signed-embed) * When you modify an existing notebook theme, the changes are reflected in all notebooks where that notebook theme is applied * However, for the above resources published from notebooks, changes are reflected when the "Publish" process is performed for the resource * The default notebook theme settings are applied to newly created notebooks and do not affect existing notebooks # Notebook FAQ important specifications related to Codatum notebook ## Notebook #### Are notebooks created by a user still accessible after the user is deleted? Private notebooks become inaccessible, but other notebooks remain accessible. #### Are changes to parameters and charts independent for each user? Changes to parameters, SQL, and charts are immediately reflected and displayed to all users viewing the same Page. These changes are saved as the state within the notebook. However, settings related to the appearance of the Page, such as toggle states of SQL Blocks or List items, are not synchronized between users. #### Is the cache used when executing `Run all` ? Available caches are used when executing `Run all.` For more details, please refer to the [SQL Block Cache](/data-exploration/appendix-cache) page. If you do not wish to use the cache, for instance, after updating the data source, please execute all SQL Blocks from the top-right corner of the page by selecting `Run all without cache`. #### Are there any limitations on Notebooks? The maximum size per notebook is **10MB**. Having a large amount of content may impact performance, particularly **loading times and conflict resolution**. To maintain smooth performance, we recommend keeping notebooks concise. If a notebook becomes too large, consider **creating a new notebook** instead of adding more Pages. #### Recommended ways to manage large Notebooks: * **Export a notebook and import pages into a new notebook**\ If a notebook is growing too large, you can export the original notebook, create a new notebook, and import some pages there. When moving pages, ensure that **referenced SQL Blocks** are properly managed to avoid breaking dependencies. * **Archive old notebooks**\ If you no longer need to edit a large notebook but still want to keep its content, you can **duplicate it** and set the duplicate as **read-only** for archival purposes. After that, you may **delete unnecessary pages** from the original notebook to reduce its size. ## Doc Page #### When the content of a Doc Page becomes lengthy, how do I know when to split it into multiple Pages? As the content of a Doc Page becomes lengthy, the difficulty in understanding the analysis increases. As SQL Blocks can reference SQL Blocks from other pages, you can continue the analysis by splitting the Doc Page at any time. Similarly, parameters can also be created and referenced across different pages. #### As the number of Pages increases, how do I know when to split the notebook? When the number of pages increases, finding where specific analyses were done becomes difficult. Additionally, as the number of elements within a page or the number of pages increases, the performance of the entire notebook may slow down. If you want to share SQL statements across notebooks, you can store them using the Query and refer to them from multiple notebooks. If the number of pages increases considerably, save only the reusable SQL statements as query and create a new notebook. ## SQL Block #### Column names of tables referenced within an SQL Block are not being suggested. Column name suggestions, along with metadata and schema displays, are limited to Table Ref or executed SQL Block Ref. Please note that suggestions and schema displays are not available when using table IDs as strings or when referring to Saved Queries # Chart Beautiful and Diverse Charts Codatum offers a wide variety of chart types to accommodate diverse data. Additionally, each chart comes with numerous settings, allowing you to tailor the visuals to your data. ## Chart Types ### [Big Number](/data-exploration/chart/bignumber) Choose from text or gauge display, and set options such as prefix, suffix, value formatter, and conditional styles. | BigNumber Text | BigNumber Gauge | | ----------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------ | ### [XY Chart](/data-exploration/chart/xy-chart) Choose from line, bar, stacked bar, area, stacked area, 100% stacked bar, 100% stacked area, scatter, and bubble charts. Composite charts are also available. | XY Line | XY Bar | XY Stacked Bar | | ------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | XY Area | XY Stacked Area | XY Percent Bar | | XY Scatter | XY Bubble | XY Percent Area | ### [Radial Chart](/data-exploration/chart/radial-chart) Choose from pie, donut, and radar charts. | Radial Pie | Radial Donut | Radial Radar | | ------------------------------------------------------------------------------------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------- | ### [XYZ Chart](/data-exploration/chart/xyz-chart) Choose from scatter, bubble, and heatmap charts. | XYZ Scatter | XYZ Bubble | XYZ Heatmap | | -------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------- | ### [Hierarchy Chart](/data-exploration/chart/hierarchy-chart) Choose from tree, treemap, sunburst, and hierarchy bar charts. | Hierarchy Tree | Hierarchy Treemap | Hierarchy Sunburst | | ----------------------------------------------------------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | Hierarchy Bar | | | ### [Graph Chart](/data-exploration/chart/graph-chart) Choose from graph charts and sankey diagrams. | Graph Chart | Sankey Diagram | | -------------------------------------------------------------------------------------------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------- | ### [Funnel Chart](/data-exploration/chart/funnel-chart) Choose from vertical and horizontal funnel charts. | Funnel | | --------------------------------------------------------------------------------------------------------------------------------------------------------- | ### [Waterfall Chart](/data-exploration/chart/waterfall-chart) Displays cumulative increases and decreases by item. | Waterfall | | ------------------------------------------------------------------------------------------------------------------------------------------------------------ | ### [Table Chart](/data-exploration/chart/table-chart) Select the fields you want to display, apply filters, and show data in aggregated form. | Table Raw | Table Aggregated | | ------------------------------------------------------------------------------------------------------------------------------------------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ### [Pivot Table](/data-exploration/chart/pivot-table) Aggregate data by rows/columns and display it as a pivot table. | Pivot Table | | -------------------------------------------------------------------------------------------------------------------------------------------------------------- | ### [Text Panel](/data-exploration/chart/text-panel) Display text in Markdown format. Combined with generative AI, it can be used to display dynamic [AI-generated summaries](/best-practices/ai-generated-summary) on dashboards. | Text Panel Markdown | Text Panel Raw | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------- | ### [Map Chart](/data-exploration/chart/map-chart) Display data on a map based on latitude/longitude, geohash, and other data. | Map Pin | Map Bubble | Map Heatmap | | -------------------------------------------------------------------------------------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------- | | Map Flow | Map Area | Map Mesh | | Map Polygon | Map Polyline | | ### [Word Cloud](/data-exploration/chart/word-cloud) Display a word cloud (tag cloud). | Word Cloud | | ------------------------------------------------------------------------------------------------------------------------------------------------------------- | # Chart Common Common concepts and tips for charts ## Data Processing in Charts ### Dimensions and Metrics Chart data aggregation uses the concepts of **dimensions** and **metrics**. * **Dimension**: Values used to group data. Categories, dates, etc. - determines "what to group by" * **Metric**: Aggregated numerical values. Sums, averages, counts, etc. - determines "what to measure" In SQL terms, dimensions correspond to columns used in `GROUP BY`, while metrics correspond to columns calculated using aggregate functions like `SUM()` or `COUNT()`. ### Date Field Transformation (Truncate/Extract) When selecting a date column for dimension fields in charts (X-axis, grouping, etc.), you can transform the date before using it. * Configuration Method * In the chart edit screen "Basic Settings", select a date column and click "Edit". Choose the transformation mode and transformation unit. * Transformation Modes * Truncate: Truncates the date to the specified unit * Example: 2025-01-15 truncated to MONTH → 2025-01-01 * Use case: Monthly, weekly, or yearly aggregation * Extract: Extracts only a specific part from the date * Example: Extracting DAY from 2025-01-15 → 15 * Use case: Pattern analysis by day within a month, time period analysis (same days from different months are aggregated) ### In-memory Processing When the query result for chart data processing is within 1,000 rows and no custom SQL is used, equivalent processing is performed in the user's browser without issuing a new query. This is called "in-memory processing". You can disable it from the chart's "Basic" > "Disable in-memory processing". ## Tips ### Frame Feature In some charts, you can set a specific column as a frame to **split the chart by each value of that column**. The display mode can be selected from the "Frame Layout" option: either "Timeline" or "Grid". The frame feature lets you visually explore time-series changes or compare categories across frames. For easier comparison across frames, the Y-axis range, bubble size scale, heatmap color scale, and similar settings are shared across all frames by default. You can change this behavior via "Advanced" > "Frame" > "Independent scale". #### Timeline Layout In timeline layout, frame controls appear at the bottom of the chart, allowing you to switch frames manually or play frame animation. | XY Frame Timeline | Graph Frame Timeline | | -------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- | #### Grid Layout Grid layout displays all frames in a grid format (**up to 16 charts**). The number of columns is automatically adjusted by default to produce a layout as close to square as possible (e.g., 3 charts → 2×2, 5 charts → 3×2). You can specify the number of columns via "Advanced" > "Frame" > "Columns" — note that the column count may be reduced automatically if there are fewer charts than columns specified. | XY Frame Grid | Radial Frame Grid | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------- | #### Charts that support the frame feature | Chart | Support | | ---------------------------------------------------------- | ------------------------------------------------ | | [XY Chart](/data-exploration/chart/xy-chart) | Supported | | [Radial Chart](/data-exploration/chart/radial-chart) | Supported | | [XYZ Chart](/data-exploration/chart/xyz-chart) | Supported | | [Hierarchy Chart](/data-exploration/chart/hierarchy-chart) | Supported | | [Graph Chart](/data-exploration/chart/graph-chart) | Partial ("Bar chart (drill-down)" not supported) | | [Funnel Chart](/data-exploration/chart/funnel-chart) | Supported | | [Waterfall Chart](/data-exploration/chart/waterfall-chart) | Supported | | [Word Cloud](/data-exploration/chart/word-cloud) | Supported | ### Value Formatting By default, the format of values and labels displayed in charts is **optimized according to your account's language settings**. You can also change the formatter via the chart's "Advanced Settings." If a formatter is manually changed, the specific formatter settings will take precedence over the language settings. #### Configuration Examples For example, in the case of an XY Chart, you can specify formatters as follows: * **X-axis/Y-axis Label Format** * Specify via "Advanced" > "X Axis / Y Axis" > "Value formatter" * **Tooltip Format** * Specify via "Advanced" > "Series" > "(Series Name)" > "Value formatter" * **Value Label Format** (if "Value label" are enabled) * Specify via "Advanced" > "Series" > "(Series Name)" > "Value label" > "Value label formatter" #### Number Formatter The number formatter uses [d3-format](https://d3js.org/d3-format) syntax for formatting. For example, you can specify settings as shown below: | Setting Value | Value | Format Result | | ------------- | ------------ | ------------- | | `.0f` | `12345.6789` | `12346` | | `,.1r` | `12345.6789` | `10,000` | | `-.1%` | `0.5` | `50%` | Additionally, you can enclose the d3-format syntax in `{` and `}` to add prefixes or suffixes before or after the value.\ (Note: This is a custom extension, not standard d3-format syntax. Please refer to the examples below.) | Setting Value | Value | Format Result | | --------------- | --------- | ------------- | | `{,d} USD` | `12345.6` | `12,346 USD` | | `Approx. {,d}%` | `50` | `Approx. 50%` | #### Date Formatter The date formatter uses [Day.js](https://day.js.org/docs/en/display/format) syntax for formatting.\ (In some cases, you can toggle between the Number Formatter and Date Formatter using the button on the left edge of the input field.) For example, you can specify settings as shown below: | Setting Value | Date | Format Result | | ------------- | ------------ | -------------- | | `YYYY-MM-DD` | `2025-01-01` | `2025-01-01` | | `YY/M/D` | `2025-01-01` | `25/1/1` | | `MMMM YYYY` | `2025-01-01` | `January 2025` | ### Adjusting Axis Label Display Width You can limit the display width of labels for each axis by setting the maximum width (px) of axis labels. Enter a numeric value (e.g., `100`, `200`) in the chart settings screen "Advanced" > "Axis label max size(px)". This can be useful when you want to display long label text with ellipsis. # Big Number Display key metrics ## Render Types Big Number has two render types: Text and Gauge. | Text BigNumber Text | Gauge BigNumber Gauge | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | ## Specifications ### Aggregation Big Number [aggregates the selected metric without specifying dimensions](/data-exploration/chart/chart-common#dimension-metric) and displays it as a single value. | Item | Role | Notes | | ---------------- | ------ | ------------------------------------------------ | | Metric | Metric | Displays the aggregated result as a single value | | Secondary metric | Metric | Used as the maximum value for gauge | #### Example Configurations * Show "Total sales overall" as text * Metric: Sum of sales * Show "Achievement rate" as a gauge * Metric: Sum of actuals * Secondary metric: Sum of targets ### Gauge * The gauge achievement rate is calculated as "Metric ÷ Secondary metric × 100" * Secondary metric is required and must be numeric ## Tips ### Conditional styles By configuring "Conditional styles" in "Advanced", you can change the font color based on the metric value. For example, you can color the value as danger when it is 80 or less, and success when it is 100 or more. # XY Chart Aggregate Y-axis values by X-axis categories ## Render Types XY charts support the following render types, as well as composite charts that combine them. | Line XY Line | Bar XY Bar | Stacked Bar XY Stacked Bar | | --------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | Area XY Area | Stacked Area XY Stacked Area | 100% Stacked Bar XY Percent Bar | | Scatter XY Scatter | Bubble XY Bubble | 100% Stacked Area XY Percent Area | ## Specifications ### Aggregation In XY charts, the [X-axis is a dimension and the Y-axis is a metric](/data-exploration/chart/chart-common#dimension-metric), and Y-axis values are aggregated for each X-axis value. (In [XYZ charts](/data-exploration/chart/xyz-chart), the X-axis and Y-axis are dimensions and the Z-axis is a metric, so Z-axis values are aggregated for each combination of X and Y.) | Item | Role | Notes | | ----------- | --------- | ----------------------------------- | | X-axis | Dimension | Aggregation unit (aggregated per X) | | Y-axis | Metric | Adds a series per metric | | Grouping | Dimension | Splits series by value | | Bubble size | Metric | Only for bubble rendering | #### Example Configurations * Show "Monthly total sales" as a line chart * X-axis (Dimension): Month * Y-axis (Metric): Total sales * Show "Total sales by product category" as a bar chart * X-axis (Dimension): Product category * Y-axis (Metric): Total sales ### X-Axis Specifications In XY charts, the X-axis type is determined by the **X-axis data type** and the **render type composition**. | X-axis data type | Render type composition | X-axis classification | | ---------------- | ------------------------- | --------------------- | | Date/Datetime | All | Time axis | | Numeric | Scatter/Bubble/Line only | Numeric axis | | Numeric | Includes Bar/Area/Stacked | Categorical axis | | Other | All | Categorical axis | #### Categorical Axis In a categorical axis, even if the specified values are numeric or date/datetime types, they are rendered as discrete values at equal intervals. **Specifying Axis Upper and Lower Limits** When specifying upper or lower limits for a categorical axis, the specified values are treated as indices of the data order (0th, 1st, 2nd...). | Index | X-axis value | | ----- | ------------ | | 0th | `1` | | 1st | `10` | | 2nd | `20` | | 3rd | `30` | | ... | ... | | 10th | `100` | In this case, if you set the axis upper limit to `10`, data up to the 10th index will be displayed, which means X-axis values up to `100` will be shown. #### Time Axis In a time axis, the specified date/datetime type data is rendered as a continuous time series. * Axis labels are dynamically determined based on the specified date/datetime data * When datetime data with a timezone is specified, it is converted to the timezone specified in the browser for rendering * Axis labels are displayed based on the browser time zone. Therefore, if the browser time zone does not match the data time zone, axis labels and data labels may be misaligned * Converting to a datetime format without a time zone can also prevent the browser time zone from affecting the dates and times #### Numeric Axis In a numeric axis, the specified numeric type data is rendered as continuous numeric values. ## Tips ### Multiple Series When you configure multiple series, a "Indepedent scale" option appears in the series settings. Turning it on gives that series its own Y-axis scale, drawn on the right. Configure labels and other options for independent Y-axes under "Advanced" > "Independent axes". | XY chart with multiple series and independent Y-axes | | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | When you configure multiple series, a "Split by series" option appears below the "Series" settings. Turning it on gives each series its own Y-axis scale and stacks the charts vertically, one per series. Enabling "Split by series" disables the [frame feature](#frame-feature). The "Indepedent scale" option is also hidden, and every series is treated as having an independent axis. Configure per-series Y-axes under "Advanced" > "Independent axes". | XY chart with split view by series | | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ### Frame Feature XY charts support the frame feature. See [Frame Feature](/data-exploration/chart/chart-common#frame-feature) for details. ### Reference Line In XY Charts and XYZ Charts, you can add reference lines to the X-axis and Y-axis. By displaying reference lines or regions such as target values, acceptable ranges, or danger zones, you can visually support data evaluation. Y-axis reference lines are displayed horizontally, and X-axis reference lines are displayed vertically. | XY Reference Line | | -------------------------------------------------------------------------------------------------------------------------------------------------------------------- | * Configuration Method * In the chart edit screen "Advanced Settings", select the axis where you want to display the reference line, then click "Reference Line" > "Add". Enter the type and value of the reference line you want to display. * What You Can Do * Line: Display a dashed line at a specific value (e.g., sales target, threshold, specific date marker) * Band (Range): Display a semi-transparent band for a specific range (e.g., acceptable range, danger zone, caution period) * Color and Label Settings: Choose colors from success (green), warning (yellow), danger (red), and add labels as needed ### Trend In XY Charts and XYZ Charts, you can display trend lines (such as regression lines or moving averages) for series. Trend lines are added as separate series from the original data, helping you visually understand data trends. | XYZ Trend | | ------------------------------------------------------------------------------------------------------------------------------------------------------------ | * Configuration Method * In the chart edit screen "Advanced Settings", click "Series" > "Trend" > "Display" and select the type of trend you want to display. * Trend lines are displayed as dashed lines, and for trend types other than moving average, the equation and R² (coefficient of determination) are displayed on the chart. (The coefficient of determination can be hidden.) #### Trend Types | Trend Type | Description | Additional Settings | | -------------- | ------------------------------------------------- | -------------------------- | | Linear | Displays linear regression (y = mx + c) | None | | Polynomial | Displays polynomial regression | Specify degree from 2 to 5 | | Moving Average | Displays moving average | Specify window size | | Exponential | Displays exponential regression (y = ae^(bx)) | None | | Logarithmic | Displays logarithmic regression (y = a + b ln(x)) | None | | Power | Displays power regression (y = ax^b) | None | Note: Trend lines will not be displayed if the data count is insufficient or if applying the specified trend type fails. ### Statistics In XY Charts and XYZ Charts, you can display statistical values (mean, median, maximum, minimum) of series as dashed lines. | XY Statistics | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------- | * Configuration Method * In the chart edit screen "Advanced Settings", click "Series" > "Statistics" > "Display" and select the statistical values you want to display. * The dashed lines will display the statistics type and its value as labels. #### Difference from Trends | Feature | Statistics | Trend | | ------------------ | ------------------------------------------------ | ------------------------------------------- | | Display Format | Displayed as the same series | Added as a separate series (trend line) | | When Zooming Chart | Recalculated only with data in the visible range | Not recalculated (always based on all data) | | In Stacked Charts | Appears as a stacked differential | Not stacked | # Radial Chart Visualize composition ratios and comparisons between items ## Render Types Radial charts support the following render types. | Pie Radial Pie | Donut Radial Donut | Radar Radial Radar | | ----------------------------------------------------------------------------------------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ## Specifications ### Aggregation Radial charts [aggregate values (metrics) for each label (dimension)](/data-exploration/chart/chart-common#dimension-metric). | Item | Role | Notes | | -------- | --------- | ----------------------------------- | | Label | Dimension | Segment label | | Value | Metric | Aggregated per label | | Grouping | Dimension | Splits series by value (radar only) | #### Example Configurations * Show "Total sales by product category" as a pie chart * Label (Dimension): Product category * Value (Metric): Sum of sales * Show "Customer satisfaction by evaluation item, compared by department" as a radar chart * Label (Dimension): Evaluation item * Value (Metric): Average satisfaction score * Grouping (Dimension): Department ### Render Type Specifications * Pie and donut charts are rendered as a single series of "Label × Value" * Radar charts use labels as axes and split series by grouping values ## Tips ### Frame Feature Radial charts support the frame feature. See [Frame Feature](/data-exploration/chart/chart-common#frame-feature) for details. # XYZ Chart Aggregate Z-axis values by X-axis and Y-axis categories ## Render Types XYZ charts support the following render types. | Scatter XYZ Scatter | Bubble XYZ Bubble | Heatmap XYZ Heatmap | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ## Specifications ### Aggregation In XYZ charts, the [X-axis and Y-axis are dimensions and the Z-axis is a metric](/data-exploration/chart/chart-common#dimension-metric), and Z-axis values are aggregated for each combination of X and Y. (In [XY charts](/data-exploration/chart/xy-chart), the X-axis is a dimension and the Y-axis is a metric, so Y-axis values are aggregated for each X.) | Item | Role | Notes | | -------- | --------- | ----------------------------------- | | X-axis | Dimension | X values become the axis | | Y-axis | Dimension | Y values become the axis | | Z-axis | Metric | Used for bubble and heatmap | | Grouping | Dimension | Scatter/Bubble only (splits series) | #### Example Configurations * Show "Total sales by product category × region" as a heatmap * X-axis (Dimension): Product category * Y-axis (Dimension): Region * Z-axis (Metric): Total sales * Show "Relationship between ad spend and sales" as a bubble chart * X-axis (Dimension): Ad spend * Y-axis (Dimension): Sales * Z-axis (Metric): Profit ### X-Axis Specifications In XYZ charts, the handling of the X-axis and Y-axis changes depending on the render type. * For heatmaps, both the X-axis and Y-axis are categorical axes * For scatter and bubble charts, the X-axis is categorical/time/numeric based on the data type, and the Y-axis is a numeric axis For axis behavior (categorical/time/numeric display rules), see [Categorical Axis, Time Axis, Numeric Axis](/data-exploration/chart/xy-chart#x-axis) in the XY chart page. ## Tips ### Frame Feature XYZ charts support the frame feature. See [Frame Feature](/data-exploration/chart/chart-common#frame-feature) for details. ### Reference Line Reference lines are also available in XYZ charts. See [Reference Line](/data-exploration/chart/xy-chart#reference-line) in the XY chart page. ### Trend Trends are also available in XYZ charts. See [Trend](/data-exploration/chart/xy-chart#trend) in the XY chart page. ### Statistics Statistics are also available in XYZ charts. See [Statistics](/data-exploration/chart/xy-chart#statistics) in the XY chart page. # Hierarchy Chart Visualize hierarchical data ## Render Types Hierarchy charts support the following render types. | Tree Hierarchy Tree | Treemap Hierarchy Treemap | Sunburst Hierarchy Sunburst | | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | Bar (Drill Down) Hierarchy Bar | | | ## Specifications ### Aggregation Hierarchy charts [aggregate values (metrics) for each hierarchy level (dimensions)](/data-exploration/chart/chart-common#dimension-metric) and render a hierarchy structure. | Item | Role | Notes | | --------- | --------- | ---------------------------------------------------------------------- | | Hierarchy | Dimension | Specify from higher to lower levels (e.g., Region → Prefecture → City) | | Value | Metric | Used as weight if specified | #### Example Configurations * Show "Sales composition by Region → Prefecture → City" as a sunburst * Hierarchy (Dimension): Region, Prefecture, City * Value (Metric): Sum of sales * Show "Counts by organizational hierarchy" as a tree * Hierarchy (Dimension): Division, Section, Team * Value (Metric): None (aggregated by row count) ### Render Type Specifications * Tree, Treemap, and Sunburst render the hierarchy as-is * Bar (Drill Down) starts at the top level, and clicking a bar drills down to the next level * Specify two or more levels and set the render type to "Bar (Drill Down)" ## Tips ### Frame Feature Hierarchy charts(Tree, Treemap, Sunburst) support the frame feature. See [Frame Feature](/data-exploration/chart/chart-common#frame-feature) for details. # Graph Chart Visualize relationships and transitions between items ## Render Types Graph charts support the following render types. | Graph Graph Chart | Sankey Sankey Diagram | | -------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | ## Specifications ### Aggregation Graph charts [aggregate weights (metrics) for each source and target (dimensions)](/data-exploration/chart/chart-common#dimension-metric) to visualize relationships between nodes. | Item | Role | Notes | | ------ | --------- | ----------------------------------------- | | Source | Dimension | Source node | | Target | Dimension | Target node | | Weight | Metric | Reflected in edge thickness and node size | #### Example Configurations * Show "Page transition counts" as a Sankey diagram * Source (Dimension): From page * Target (Dimension): To page * Weight (Metric): Sum of transition counts * Show "Mention relationships between users on social media" as a graph * Source (Dimension): Mentioning user * Target (Dimension): Mentioned user * Weight (Metric): Sum of mention counts ### Render Type Specifications #### Graph Draws a graph where sources and targets are nodes, connected by edges weighted by metric values. * Directed/Undirected switching * In undirected mode, A→B and B→A are merged * Frame feature * Split the chart by specified column values and switch using a slider. Useful for visualizing time-series changes or category-based comparisons. * Heatmap display * Change colors based on node weight to visually represent importance. #### Sankey Draws a Sankey diagram where sources and targets are nodes, connected by edges weighted by metric values. Flow direction is visually clear. ## Tips ### Frame Feature Graph charts support the frame feature. See [Frame Feature](/data-exploration/chart/chart-common#frame-feature) for details. # Funnel Chart Visualize changes in step-by-step processes Displays a funnel chart. | Funnel | | --------------------------------------------------------------------------------------------------------------------------------------------------------- | ## Specifications ### Aggregation Funnel charts [aggregate values (metrics) for each label (dimension)](/data-exploration/chart/chart-common#dimension-metric) and display them in stages. | Item | Role | Notes | | ----- | --------- | ---------------------------------- | | Label | Dimension | Stage names in the funnel | | Value | Metric | Displayed as values for each stage | #### Example Configurations * Show "Visits → Sign-ups → Add to cart → Purchase counts" as a funnel * Label (Dimension): Stage names * Value (Metric): Sum of counts ### Sort Order * When sorting is set to "Label order", no reordering is performed and percentages are calculated based on the first value * When sorting is set to "Value ascending/descending", percentages are calculated based on the maximum value ## Tips ### Frame Feature Funnel charts support the frame feature. See [Frame Feature](/data-exploration/chart/chart-common#frame-feature) for details. # Waterfall Chart Displays cumulative increases and decreases by item Renders a waterfall chart. | Waterfall | | ------------------------------------------------------------------------------------------------------------------------------------------------------------ | ## Specifications ### Aggregation The waterfall chart [aggregates values (metrics) for each label (dimension)](/data-exploration/chart/chart-common#dimension-metric) and displays them cumulatively. | Item | Role | Notes | | ----- | --------- | --------------------------------------------------- | | Label | Dimension | Item names for each bar in the waterfall | | Value | Metric | Displayed as the increase or decrease for each item | #### Example Configurations * Display "Opening balance → Revenue → Costs → Other" as a waterfall (e.g., P\&L or cash flow breakdown) * Label (Dimension): Opening balance, Revenue, Costs, Other, Closing balance, etc. * Value (Metric): Amount (increase/decrease) for each item ## Tips ### Frame Feature Waterfall charts support the frame feature. See [Frame Feature](/data-exploration/chart/chart-common#frame-feature) for details. # Table Chart Create table-style charts ## Render Types Table charts support the following render types. In the raw table, you can select fields and filter rows. In the aggregated table, you can also aggregate values. | Raw Table Table Raw | Aggregated Table Table Aggregated | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | ## Specifications ### Source Types Table charts have two source types. Depending on the selected source type, the aggregation and display behavior differs. | Source Type | Description | Column Roles | | ---------------------------------- | ---------------------------- | ----------------------------------------------------------------------------------------------- | | Raw data (raw table) | Display retrieved rows as-is | Select columns to display | | Aggregated data (aggregated table) | Group and aggregate | [Group (dimension) and values (metrics)](/data-exploration/chart/chart-common#dimension-metric) | ## Tips ### Image Display By specifying an image URL in a column and turning on "Enable" in "Advanced" > "Column Settings" > "Image URL Preview", the image at the specified URL will be displayed in the table chart. | Table Image Preview | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ### Link Display By specifying a URL in a column and enabling "Enable link URL" under "Advanced" > "Column Settings", the table chart will display the URL as a link that opens in a new tab. | Table Link URL | | ----------------------------------------------------------------------------------------------------------------------------------------------------------------- | #### Creating Parameterized Links from Table Charts to Reports By dynamically generating URLs in SQL queries, you can pass data from each row of the table as parameters to another report page. 1. Creating a report and obtaining the URL * Set parameters in the destination report and obtain the URL from the browser's address bar. * `Example URL: https://app.codatum.com/workspace/{workspace_id}/report/{report_id}/{page_id}` * How to check parameter ID: Note the ID of the parameter widget in the report (e.g., `68f369edf5e6f69036b8872d`) 2. Creating a URL column in SQL * Use the CONCAT function to generate a URL containing parameters from each row's data and insert it into the chart. * Example with a single parameter (country name): ```sql CONCAT( 'https://app.codatum.com/workspace/{workspace_id}/report/{report_id}/{page_id}?cdm.params.{parameter_id}=', country --- Column name to pass as parameter ) AS link ``` 3. Enabling links in table chart * Table chart settings → Column settings → Target column → Turn on "Enable link URL". Notes * Replace the `country` part with the database column name you want to pass as a parameter * You cannot reference the current value of parameter widgets in SQL (only fixed values or column values) * When a user clicks a cell, the report opens in a new tab * For detailed information on parameter settings, see [here](/data-exploration/parameter-overview/set-parameters-via-url) # Pivot Table Create two-dimensional pivot tables Aggregate data by rows/columns and display it as a pivot table. | Pivot Table | | -------------------------------------------------------------------------------------------------------------------------------------------------------------- | ## Specifications ### Field Roles Pivot tables aggregate [rows and columns as dimensions, and values as metrics](/data-exploration/chart/chart-common#dimension-metric), then display the results in a table format. | Item | Role | Notes | | ------- | --------- | ------------------------------- | | Rows | Dimension | One or more | | Columns | Dimension | Optional | | Values | Metric | Supports numeric/string metrics | ## Tips ### Drill Down Pivot tables can display rows hierarchically and allow collapsing them. How to Configure 1. Set multiple row fields * In chart settings "Rows", set two or more fields such as major category and minor category. * Example: Prefecture (major category) → City/Town/Village (minor category) 2. Switch to tree display * "Advanced" > "Layout" > "Default hierarchy type" > Select "Tree". * Grid: Traditional table format * Tree: Display in hierarchical structure, expandable/collapsible with expand/collapse icons 3. Set initial state to collapsed (optional) * Check "Start collapsed" to show all hierarchies collapsed in the initial display. Notes * This setting is only available when there are two or more row fields * Report viewers can freely expand and collapse ### Processing Differences When using pivot tables, the results may differ depending on the data processing method. Pivot tables use [in-memory processing](/data-exploration/chart/chart-common#in-memory-processing) and [query processing](/data-exploration/chart/chart-common#query-processing). * In-memory processing * No row or column limits, so all data can be displayed * Query processing * Limited to 30 rows and 30 columns * If it exceeds 30, rows or columns are grouped into `Others` # Text Panel Display the first value of the selected column ## Render Types Text panels display the first value of the selected column in the specified format. This is intended for showing text output from query results. Combined with generative AI, it can also be used to display dynamic [AI-generated summaries](/best-practices/ai-generated-summary) on dashboards. | Markdown Text Panel Markdown | Text Text Panel Raw | | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ## Specifications ### Aggregation Text panels do not aggregate data and instead display the first value of the selected column. ### Render Type Details You can choose the render type from "Rendering Mode" in "Advanced". * Markdown: Renders the output text as Markdown * Text: Displays the output text as-is # Map Chart Map charts can display data on a map using latitude/longitude, geohash, geocodes, and GeoJSON ## Data Formats Map charts can display the following data formats on a map. ### Latitude/Longitude Draws objects at the specified latitude and longitude coordinates. Latitude and longitude should be specified as numeric columns. Available for the following render types: | Pin Map Pin | Bubble Map Bubble | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | Heatmap Map Heatmap | Flow\* Map Flow | \* Flow requires two sets (source and target) of latitude/longitude information. ### Geohash Draws objects at positions specified in [Geohash](https://en.wikipedia.org/wiki/Geohash) format. For the following render types, objects are drawn at the center of the specified geohash: * Pin * Bubble * Heatmap * Flow For the following render type, the area of the specified geohash is filled and drawn: (The size of the filled area varies depending on the precision of the specified geohash) | Mesh Map Mesh | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------- | #### Geohash Examples Below are examples of geohashes at different precision levels. | Geohash | Precision | Example Location | Area Size (approx.) | | -------- | ------------ | ---------------------- | ------------------- | | xn | 2 characters | Tokyo area | 630km × 500km | | xn7 | 3 characters | Central Tokyo | 78km × 78km | | xn76 | 4 characters | Tokyo Station area | 20km × 10km | | xn76u | 5 characters | Around Tokyo Station | 2.4km × 2.4km | | xn76ur | 6 characters | Near Tokyo Station | 610m × 300m | | xn76urx | 7 characters | Tokyo Station vicinity | 76m × 76m | | xn76urxk | 8 characters | Around Tokyo Station | 19m × 9m | #### BigQuery Usage Example In BigQuery, you can use the `ST_GEOHASH` function to convert latitude and longitude data into geohashes. By converting to geohashes, you can efficiently aggregate and group data by area, rather than handling latitude and longitude individually. Below is an example of using BigQuery's `ST_GEOHASH` function to convert latitude and longitude data into geohashes. ```sql SELECT ST_GEOHASH(ST_GEOGPOINT(longitude, latitude), 6) AS geohash, COUNT(*) AS station_count FROM `bigquery-public-data.new_york_citibike.citibike_stations` GROUP BY geohash ORDER BY station_count DESC ``` ### Geocode Fills and draws the area associated with a geocode. Available for the following render type: | Area Map Area | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------- | Geocodes should be specified as strings in the following format: | Granularity | Geocode Format | Geocode Example | | ------------------------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | --------------- | | Country |

2-letter country code
(ISO3166-1, uppercase)

| US | | Prefecture (Japan only) |

"JP-"+2-digit prefecture code
(JIS X 0402)

| JP-13 | | Municipality (Japan only) |

"JP-"+5-digit municipality code
(JIS X 0402)

| JP-13101 | | First 2 digits of postal code (Japan only) | "JP-POST-"+first 2 digits of postal code | JP-POST-10 | | First 3 digits of postal code (Japan only) | "JP-POST-"+first 3 digits of postal code | JP-POST-100 | The geospatial data used for filling is a lightweight version processed from the following data: * Global country border data: [Natural Earth](https://www.naturalearthdata.com/) * Japanese administrative boundary data: ["National Land Numerical Information (Administrative Area Data)" (Ministry of Land, Infrastructure, Transport and Tourism)](https://nlftp.mlit.go.jp/ksj/gml/datalist/KsjTmplt-N03-2025.html) * Postal code boundary data: [Postal Code Boundary Data (Map Geography Sandbox)](https://hanishina.net/maps/yubindata.html) Note that even if a geocode format is valid, geocodes not included in the above data cannot be drawn. #### Geocode Samples The following are sample geocodes. For a list of available geocodes, please refer to the [Geocode List CSV file](https://static.codatum.com/v/latest/public/geojson/codes.csv). | Geocode | Name | | ----------- | -------------------------- | | US | United States | | JP | Japan | | JP-01 | Hokkaido | | JP-02 | Aomori Prefecture | | JP-03 | Iwate Prefecture | | JP-01100 | Sapporo City | | JP-01101 | Sapporo City, Chuo Ward | | JP-01102 | Sapporo City, Kita Ward | | JP-01103 | Sapporo City, Higashi Ward | | JP-01202 | Hakodate City | | JP-01203 | Otaru City | | JP-01204 | Asahikawa City | | JP-POST-10 | 〒10X-XXXX | | JP-POST-100 | 〒100-XXXX | ### GeoJSON Draws GeoJSON-formatted geometry data on a map. Available for the following render types: | Polygon Map Polygon | Polyline Map Polyline | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | Specify JSON of the following geometry types as string type: * `Point` * `LineString` * `Polygon` * `MultiPoint` * `MultiLineString` * `MultiPolygon` * `GeometryCollection` #### GeoJSON Sample ```json { "type": "Polygon", "coordinates": [ [ [-73.9812, 40.7681], [-73.9581, 40.7681], [-73.9581, 40.8007], [-73.9812, 40.8007], [-73.9812, 40.7681] ] ] } ``` #### BigQuery Usage Example In BigQuery, you can use the `ST_ASGEOJSON` function to convert geometry data to GeoJSON format. The following example converts the `county_geom` column from the `bigquery-public-data.geo_us_boundaries.counties` table to GeoJSON format. ```sql SELECT * EXCEPT (county_geom), ST_ASGEOJSON(county_geom) AS county_geom FROM `bigquery-public-data.geo_us_boundaries.counties` WHERE state_fips_code = '36' -- New York ``` If the size of the geometry data to be drawn is large, it may exceed the query result size limit and cause an error. In such cases, you can use the `ST_SIMPLIFY` function to simplify the geometry data and reduce its size. (We recommend simplifying large geometry data to the minimum necessary size, as it can also affect rendering performance) ```sql SELECT * EXCEPT (county_geom), ST_ASGEOJSON(ST_SIMPLIFY(county_geom, 1000)) AS county_geom FROM `bigquery-public-data.geo_us_boundaries.counties` WHERE state_fips_code = '36' -- New York ``` ## Usage Limitations Since map charts use WebGL to render maps, the following limitations apply: * Cannot be rendered in browsers that do not support WebGL * Due to WebGL context number limitations, when attempting to render many map charts simultaneously, previously rendered charts may be discarded * They can be re-displayed by re-rendering, but please be careful not to have too many map charts per page # Word Cloud Displays keyword frequency and importance visually Renders a word cloud (tag cloud). | Word Cloud | | ------------------------------------------------------------------------------------------------------------------------------------------------------------- | ## Specification ### Aggregation In word clouds, values (metrics) are [aggregated per word (dimension)](/data-exploration/chart/chart-common#dimension-metric) and displayed. | Item | Role | Notes | | ----- | --------- | ------------------------------------------- | | Word | Dimension | Each word in the word cloud | | Value | Metric | Value that determines the size of each word | #### Usage example * Display content tags in a word cloud * Word (dimension): Tag name * Value (metric): Number of content items with the tag ## Tips ### When not all words are displayed Word clouds draw words in order of value (largest first), and **words that do not fit in the display area are automatically hidden**. Therefore, words with smaller values may not be shown. If you have many words and not all of them are visible, try adjusting the "Font size range" or "Word spacing", or resizing the chart area. Enabling "Auto-shrink to fit" will automatically adjust the font size of words that do not fit in the display area. However, with many words, font size recalculation can make the chart sluggish. For this reason, this option is disabled by default. When enabling it, be mindful of the number of words. ### Frame Feature Word cloud supports the frame feature. See [Frame Feature](/data-exploration/chart/chart-common#frame-feature) for details. # Explorer Drag-and-drop and spreadsheet-like UI to easily filter, extract, aggregate, and modify data without coding.
Explorer allows you to perform common data transformations via a graphical user interface while viewing the data. Explorer can be used on the execution results of the SQL Block or Charts in notebook and report. ## Key Features * Spreadsheet-like UI and Drag-and-drop UI: * Sort, filter, and select values from column headers just as in a spreadsheet. * Select fields, apply filters, and sort data using drag-and-drop. * Aggregation: * Perform simple aggregations (Group by) using drag-and-drop. * Field Statistics: * Check statistical information from column headers. * Chart Recommendation: * Generate recommended charts based on the type and form of the operation results. ## Where Explorer is Available Since Explorer interacts directly with the original data, it’s available only in certain contexts. * Notebook (DocPage/GridPage) * Report * When `Report type` is `Interactive report`, and `Enable explorer` is selected in the Options * Signed Embed * When `Enable explorer` is selected in the Options Please refer to this section for further details. {% content-ref url="/pages/q3VlGEYQbem4hJ652fGL" %} [Sharing Overview](/sharing/sharing-overview) {% endcontent-ref %} ## In-Memory Processing * In Explorer, if the target data is within 1000 rows and no custom SQL is used, the in-memory processing processes data on the user's browser without issuing a new query. * This processing can be disabled by checking the `Disable in-memory processing` checkbox. # Parameter Create dynamic notebooks and reports.
By using the Parameter defined in Notebooks, you can make Notebooks and Reports dynamic and interactive. This allows you to control embedded SQL through parameters without directly accessing SQL, and obtain tailored outputs as needed. ## Key Features * Make notebooks and reports dynamic: * Make Notebooks and Reports dynamic and interactive. * [Various types of parameters](/data-exploration/parameter-overview/parameter-types): * Choose from various data types and associated input widgets. * [Escape and dynamic reference generation](/data-exploration/parameter-overview/escape-and-dynamic-reference): * The escape processing changes according to the context in which the parameter is used in SQL. * Two types of scopes: * Choose parameters used across the notebook or on a page-by-page basis. * Parameter overwrite: * Fine-grained control by overwriting parameters when chaining SQL. ## How to Use Once you create a parameter, you can embed a reference to it within an SQL block in the notebook by typing `/param/{param_name}`. For example, if you create a parameter named `param1` as a string parameter and input the value `val1`, the SQL: ```sql SELECT {{param1}} ``` will be converted to: ```sql SELECT 'val1' ``` ## Parameter Scopes Parameters have two types of scopes: * `Notebook common` * Parameters shared across all pages in the notebook * Parameter definitions are shared across the notebook, but values are retained on each page * `Page specific` * Parameters specific to each page For parameters used in SQL blocks that are referenced from multiple pages, use `Notebook common` parameters. Otherwise, use `Page specific` parameters. For example, when a chart created on a document page is referenced on a grid page, the SQL block serving as the chart’s data source should use `Notebook common` parameters. ## Parameter Overwrite
When chaining SQL, you may want to cut off the parameter dependencies. For example: * Setting unique parameters for each page when chaining SQL across pages. * Using different parameters for different SQLs derived from the same base SQL. Parameters can be overwritten on a popup displayed by hovering over the referenced SQL in SQL Blocks. ## Parameter Value Synchronization **Parameter values and execution results in notebooks are synchronized among users**. However, using notebook version or reports ensure that changes to parameter values and execution results are not synchronized between users, reflecting changes only to the user who changes the values. For more details, please refer to the [Notebook Version](https://github.com/codatum/support-site-gitbook/blob/main/en/data-exploration/parameter/notebook/version.md). ## Notebook Common Parameters in Report Feature You can synchronize configured parameters across pages in a report. When synchronization is enabled in the report feature, changes to notebook-shared parameter values are synchronized across pages. Note that page-specific parameters are not synchronized. For more details, please refer to [Report](https://github.com/codatum/support-site-gitbook/blob/main/en/data-exploration/sharing/report.md). # Parameter Types There are several types of parameters, depending on the type of input form generated and the data type. ## 1. Text input (`STRING`) Simple string input field. * Format: * Escaped as a string in SQL. * Ex: Converted to `'val1'` * If empty: * `''` ## 2. Text multi input (`STRING[]`) A field for entering multiple strings. * Format: * Escaped as a comma-separated string in SQL. * Ex: Converted to `'val1', 'val2'` * If empty: * `''` ## 3. Text select (`STRING`) A dropdown to select a single string value. * Source options: * Fixed list or table values or query results. * Format: * Escaped as a string in SQL. * If empty: * `''` ## 4. Text multi select (`STRING[]`) A dropdown for selecting multiple string values. * Source options: * Fixed list or table values or query results. * Format: * Escaped as a comma-separated string in SQL. * Ex: Converted to `'val1', 'val2'` * If empty: * `''` ## 5. Number input (`NUMBER`) A simple numeric input field. * Options: * min, max, steps (intervals) * If empty: * `0` ## 6. Number select (`NUMBER`) A dropdown for selecting a single number. * Source options: * Fixed list or table values or query results. * If empty: * `0` ## 7. Date input (`DATE`) A simple date input field. Allows selection from a calendar or by entering a date. * Options: * `Unit`: * Date, Week, Month, Year * `Start day of week` (First day displayed in calendar): * Sun, Mon, Tue, Wed, Thu, Fri, Sat * `Relative valid range`: * `Days before today`: Dates earlier than this limit become unselectable. For example, entering '90' will make dates earlier than 90 days unselectable. * `Days after today`: Dates beyond this limit become unselectable. For example, entering `0` will make dates from the next day onwards unselectable, and entering `-1` will make dates from today onwards unselectable. * `Absolute valid range`: * `Lower date`: Dates before this specified date become unselectable. * `Upper date`: Dates after this specified date become unselectable. * Format: * Expanded in `yyyy-MM-dd` format as a string in SQL. * Ex: Converted to `'2024-01-01'` * If empty: * `'1970-01-01'` * You can set [relative dates](/data-exploration/parameter-overview/relative-date-behavior) using default values. ## 8. Date range (`[DATE, DATE]`) Specifies a start and end date as a pair. * Options: * Identical to those for Date Input (DATE) * Syntax for SQL reference: * Start Date: `{{param_name}}.StartDate` * End Date: `{{param_name}}.EndDate` * If empty: * Start date and end date are both `'1970-01-01'` * You can set [relative dates](/data-exploration/parameter-overview/relative-date-behavior) using default values. ## 9. Checkbox (`BOOLEAN`) Simple Boolean checkbox. * Format: * Expanded as `true` or `false` in SQL. # How to Use Relative Date How to access the latest date data using parameters By setting default values for "Date Input" or "Date Range" parameters, you can easily set relative dates such as "today," "yesterday," and "last week" to parameters. ## Parameters That Support Relative Dates * [Date Input](/data-exploration/parameter-overview/parameter-types#date-input) * [Date Range](/data-exploration/parameter-overview/parameter-types#date-range) ## How to Set Relative Dates * Click the three-dot icon in the upper right corner of the parameter input form * Select and configure `Relative date` or `Relative date range` from the `Default value` options ## When Relative Dates Are Calculated Relative date calculations are performed when the parameter value is changed to the default value.\ The timing when parameter values are changed to default values for each feature is shown below. ### Notebook * When a user performs the following operations in the parameter input form: * When `Reset parameter values to default values` is performed for the entire form * When `Reset to default` is performed for individual parameters {% hint style="info" %} Since notebook parameter changes are shared with other users in real-time, changes to default values require explicit user action. Relative date calculations cannot be performed when opening a notebook. {% endhint %} ### Public Link Since parameters cannot be changed, the relative date feature is not available. ### Report * Relative dates can be applied when opening a report by configuring the following settings: * Select `Interactive report` in the `Report type` * Select `Enable automatically run on load` and `With defalut parameter values` in the options * If "with default parameter values" is not enabled, parameter values will be **the values at the time the report was published** * Operations in the parameter input form are the same as in the notebook case {% hint style="info" %} Report parameter changes are **not shared** with other users. {% endhint %} ### Signed Embed * Relative dates can be applied when opening a signed embed by configuring the following settings: * Specify parameters as `Client-side parameters` (do not specify as `Server-side parameters`) * In the `Client-side parameters` section of `Embed iframe` specify `Use the default value as the initial value` * This requires changes to the script that generates the signed embed * If `Use the default value as the initial value` is not enabled, parameter values will be **the values at the time the signed embed was published** * If parameters are specified as `Server-side parameters`, the relative date feature is not available, and relative date calculation processing must be performed when specifying dates on the server side. * Operations in the parameter input form are the same as in the notebook case * However, if the input form is hidden, it will not be subject to default value reset processing, so relative date calculations will not be performed {% hint style="info" %} Signed embed parameter changes are **not shared** with other users. {% endhint %} ## Frequently Asked Questions ### Q: What is the reference date and time for calculating relative dates? **A:** Relative dates are calculated based on the current time in the browser. Since the timezone follows the browser settings, the calculated relative dates may differ even at the same time if the timezone is different. # Escape and Dynamic Reference Parameter escaping and dynamic table/column reference The parameter system handles SQL injection protection and value escaping based on the parameter's context. This mechanism not only provides security but also enables dynamic table/column reference. ## Parameter Escaping ### 1. String Parameters in Regular Context * Parameters are escaped as a string in SQL. * The escape is performed according to the SQL syntax of the selected connection. ```sql -- If parameter value is: user's "data" WHERE name = {{str_param}} -> WHERE name = 'user\'s \"data\"' -- If parameter value is: O'Reilly's "Book" SELECT name = {{company}} -> SELECT name = 'O\'Reilly\'s \"Book\"' ``` ### 2. String Parameters in Quoted Context * If a parameter is placed within quotes (such as single or double quotes), it will be properly escaped to preserve the original SQL syntax and prevent breaking the quoted context. * The escape is performed according to the SQL syntax of the selected connection. ```sql -- Below is an example using a BigQuery connection -- Inside double quotes (escapes both " and ') -- If parameter value is: user"s 'data' SELECT "{{str_param}}" as alias -> SELECT "user\"s \'data\'" as alias -- Inside single quotes (escapes both " and ') -- If parameter value is: user"s 'data' SELECT '{{str_param}}' as alias -> SELECT 'user\"s \'data\'' as alias -- Inside backticks (escapes only `) -- If parameter value is: user"s 'data` SELECT `{{str_param}}` as alias -> SELECT `user"s 'data\`` as alias ``` ### 3. Date Parameters Special Processing * Regular context: Dates are formatted as `'YYYY-MM-DD'` * However, when using a BigQuery connection, dates are formatted as `YYYYMMDD` (without hyphens) within backticks (\`). ```sql -- Below is an example using a BigQuery connection -- Regular date parameter SELECT {{date_param}} as normal -> SELECT '2024-10-02' as normal -- Date in quotes follows the same escaping rules SELECT '{{date_param}}' as single_quote -> SELECT '2024-10-02' as single_quote SELECT "{{date_param}}" as double_quote -> SELECT "2024-10-02" as double_quote -- Special case: Table name generation in BigQuery SELECT * FROM `table_{{date_param}}` -> SELECT * FROM `table_20241002` ``` ### 4. Other Parameters * Number and boolean parameters are not escaped and are expanded as is. ## Dynamic Identifier Generation * By placing parameters within quotes, you can safely generate SQL identifiers like table or column names. ```sql -- Dynamic table name SELECT * FROM `table_{{table_suffix}}` -> SELECT * FROM `table_sales_2024` -- Dynamic field selection SELECT "column_{{field_type}}" -> SELECT "column_revenue" ``` {% hint style="warning" %} Escaping is applied based on the surrounding quotes to ensure the SQL syntax remains valid. However, since **string parameters can accept arbitrary values**, depending on how the SQL is written, it's possible for unintended tables or columns to be referenced. When generating identifiers dynamically, consider applying safeguards such as **fixing prefixes or suffixes**, or writing the SQL in a way that it **results in an error when unexpected values are provided**. {% endhint %} # Dynamic Select Options [Text select](/data-exploration/parameter-overview/parameter-types#text-select) and [Text multi select](/data-exploration/parameter-overview/parameter-types#text-multi-select) parameters support three types of select options: ## 1. `Fixed List` Used when you want to select from fixed values. * Settings: * CSV Text: * Define options in CSV text format, with each item on a new line. To specify a display name, include it on the same line with a comma (,). For example: ```csv value1,Display Name 1 value2,Display Name 2 ``` ## 2. `Table Value` Used when you want to generate selectable values from a specific column in a table. * Settings: * `Source table`: Select the table from which values are drawn. * `Value column`: Specify the column to be used as the option value. * `Label column`: Specify the column to be used as the display label. ## 3. `Query Result` Used when you want to generate options based on the results of a specified SQL query. * Settings: * `Connection`: * Choose the database connection to use for the query. * `SQL`: * Input the SQL query that returns the option values. If the query returns multiple columns, the first column will be used as the option value, and the second column (if available) as the display label. {% hint style="info" %} SQL queries for options are executed frequently, so avoid using slow or resource-intensive queries for optimal performance. {% endhint %} ## Execution Timing of the SQL Queries When using SQL queries for parameter options, it's important to understand when these queries are executed: 1. **When a notebook page is loaded**: * SQL queries for parameters used on the current page are executed during page initialization * Parameters not used on the current page are not initialized and their SQL queries are not executed 2. **When a parameter dropdown is opened**: * SQL query is executed when the dropdown menu is first opened 3. **When typing in the search field**: * After the initial query execution, subsequent filtering is performed on the client-side using the already fetched data * SQL is not re-executed for every keystroke ## Caching Behavior SQL queries for parameters use the same caching mechanism as regular notebook SQL blocks: * Results are cached on the backend for up to 24 hours * Identical queries (including parameter values) will use cached results * The frontend also maintains a memory cache during the current session This means even though parameter select dropdowns might be opened frequently, the actual query is only executed once within the cache period, significantly improving performance. # How to Specify from URLs How to specify parameters when creating links from external sites When creating links from external sites to this service, you can override existing parameter values by specifying parameters in the URL. ## Target Features The following features allow you to specify parameters from URLs.\ In these features, the URL is updated each time parameters are changed, and **you can share parameter values with other users by sharing the URL**. * Notebook [versions](/data-exploration/notebook/version) * [Reports](/sharing/report) * Must select `Interactive Report` as the report type * If the `Enable automatically run on load` setting is enabled, it will automatically execute after these parameters are merged * Even if the `with default parameter values` setting is enabled, **parameters specified in the URL take priority** * Parameter updates specified in the URL are applied after the entire parameters are reset to default values ## How to Specify Parameters Parameters are synchronized with the `cdm.params` query parameter in the URL.\ The `cdm.params` query parameter has the following format: ```typescript // Parameters are stored for each page ID to maintain parameters during page transitions type Params = {[pageId: string]: {[paramId: string]: string}}; // Each parameter value is specified as a string using JSON.stringify (details below) // pageId and paramId are actually strings like 68783eb9c4b4b71483a1d617 const params: Params = { "page1": { "param1": JSON.stringify("value1"), "param2": JSON.stringify(123) }, "page2": { "param3": JSON.stringify(true), "param4": JSON.stringify(["value1", "value2"]) } }; const query = `?cdm.params=${encodeURIComponent(JSON.stringify(params))}`; ``` ## Shortcut Format Specification If it's difficult to directly generate the `cdm.params` query parameter when creating links from external sites, you can also specify parameters by providing keys and values in the format `cdm.params.${paramId}`. When both `cdm.params` and `cdm.params.${paramId}` are specified, the `cdm.params.${paramId}` value takes priority, and a new `cdm.params` query parameter is automatically specified with both values merged. ```typescript const paramId = "param1"; // In shortcut format, parameter values don't need JSON.stringify stringification (details below) const value = "string_value"; const query = `?cdm.params.${paramId}=${encodeURIComponent(value)}`; // In shortcut format, you can set a parameter to empty state by specifying an empty string const queryEmpty = `?cdm.params.${paramId}=`; ``` ## How to Specify Parameter Values Parameter values are specified as follows according to the parameter type.\ When specifying in shortcut format, `JSON.stringify` stringification is not required, but `encodeURIComponent` encoding is necessary when specifying in URLs. | Type | Specification Method | Shortcut Format | | --------------------------------------------- | ------------------------------------------------- | ----------------------- | |

Text input,
Text select

| `JSON.stringify("string_value")` | `string_value` | |

Text multi input,
Text multi select

| `JSON.stringify(["item1", "item2"])` | `item1,item2` | |

Number input,
Number select

| `JSON.stringify(123)` | `123` | | Date input | `JSON.stringify("2025-01-01")` | `2025-01-01` | | Date range | `JSON.stringify(["2025-01-01", "2025-01-02"])` | `2025-01-01,2025-01-02` | | Checkbox | `JSON.stringify(true)` or `JSON.stringify(false)` | `true` or `false` | ### Specification Method Details * Array-type parameters are specified by separating with `,` in shortcut format. * For date input and date ranges, please specify in `YYYY-MM-DD` format * If invalid parameter values are specified, the display will switch to an error screen # Query(Beta) Create SQL queries that can be used across multiple notebooks {% hint style="warning" %} This feature is in beta. Specifications may change without notice. {% endhint %} By using "Queries", you can save general-purpose queries that can be used across multiple notebooks on a per-connection basis. ## How to Use * Creating "Queries": * Creating from the "Queries" screen * Create new from "Create Query", select a connection, and "Save". * Creating from a notebook * Execute "Save SQL" from the menu at the top right of the SQL block to save the content of the SQL block to "Queries". * References to other SQL blocks are saved in their expanded state. * Parameters are saved with their values replaced. * Uses of "Queries" * You can share queries across notebooks. ## Notes * Parameter embedding and references to other "Queries" are not supported. # (Appendix) Query Execution and Cache Additional information on query execution and caching In various scenes, we create and utilize caches with each query execution to prevent redundant background jobs. This helps in reducing the amount of data scanned and in obtaining execution results faster. ## **Query Execution** Queries are executed in the following features: * [Doc Page](/data-exploration/notebook/doc-page) * Run all, Run all with fresh data * [SQL Block](/data-exploration/notebook/sql-block) * Run, Run with fresh data * [Grid Page](/data-exploration/notebook/grid-page) * Run all, Run all with fresh data * [Report](/sharing/report) & [Signed Embed](/sharing/signed-embed) * Refresh * Automatically run * Query * Run * Table preview features across various functions ## **Warehouse-side Cache** If the SQL statement does not change, the warehouse-side cache (e.g., [cached query results in BigQuery](https://cloud.google.com/bigquery/docs/cached-results)) is used. When using SQL Chain, intermediate SQL results are automatically rewritten to use the cache table. ## **Small Cache for Notebooks and Reports** * SQL results of up to 1000 rows are stored as a cache with the notebook. Charts and other components can utilize these results up to 1000 rows. * If the same SQL is executed again within 24 hours, the saved display-ready data is retrieved and shown without running a new job. * To execute with a new job, select `Run all with fresh data` to update the results. * Report cache expiration time can be changed from the report settings. {% hint style="info" %} Table preview features retrieve data each time they are viewed, so cache is not utilized. {% endhint %} # Codatum CLI A command-line tool for operating Codatum from your local environment together with an AI agent {% hint style="warning" %} This feature is currently in beta. Specifications are subject to change without notice. {% endhint %} Codatum CLI is a command-line tool that lets you operate Codatum's features from your local environment together with an AI agent. From fetching, editing, running, and pushing Notebooks to executing SQL and searching data sources, you can complete a full workflow from the command line alone, without opening a browser. Since Notebooks can be fetched as plain text files in Markdown format (`.cnb.md`), you can edit them in your favorite editor, version them with Git, and review changes via Pull Requests — enabling a "BI as Code" workflow. Codatum CLI is also designed with AI agent integration in mind: agents can reference Codatum's specifications directly, so you can confidently delegate Notebook editing to agents such as Cursor or Claude Code. For installation instructions and command details, see the Codatum CLI documentation. {% embed url="" %} # Codatum Agent (Beta) An AI agent that explores data and business information across services to support research, analysis, and external integrations {% hint style="warning" %} This feature is currently in beta. Specifications are subject to change without notice. {% endhint %} Codatum Agent is an AI agent for investigating data, performing analyses, and carrying out business operations across Codatum knowledge and external services through natural language. It references context from the catalog, Notebooks, the web, and connected services and selects the information and tools needed for the objective. Creating and editing Notebooks is one of the methods Codatum Agent can use to organize and share analysis and research results. ## Key features * Explore tables and columns * Create and run SQL * Analyze query results * Create and edit Notebooks * Add and edit SQL blocks and charts * Reference Notebooks, tables, and saved queries * Search the web and fetch web pages * Connect to external services with [Agent Connectors](/ai-assist/agent-connector) Available features depend on the user's permissions, the selected AI Profile, the operation mode, and the page currently being viewed. ## Availability and enablement Codatum Agent is available on plans that include the AI Agent feature. A workspace administrator can enable it under `Workspace Settings` > `Options` > `Codatum AI`. The model and permissions used by AI Agent are configured in an [AI Profile](/ai-assist/ai-profile). ## Display modes ### Side panel Open Codatum Agent from the AI Agent icon. When a Notebook is open, the current Notebook and page can be used as context. ### Full page Display Codatum Agent on a dedicated page. This mode is useful for data exploration that does not start from a particular Notebook or for reviewing a long conversation. You can continue the same chat session between the side panel and full-page views. ## Operation modes ### Ask mode Performs data analysis, including query execution, but does not create or edit Notebooks. Use this mode to investigate data without modifying a Notebook. ### Agent mode Includes all operations available in Ask mode and can also create and edit Notebooks, SQL blocks, and charts. Users without Notebook write permission are restricted to Ask mode. ## Context Codatum Agent can use the following information as context: * The page and Notebook currently being viewed * Tables, Notebooks, and saved queries specified with `@` in a message * SQL blocks and charts available in the current Notebook * Automatically loaded Agent Context * Supplemental text, Notebooks, and folders configured in the AI Profile Open context usage in the input area to review automatically loaded context and its usage. See [Agent Context](/ai-assist/agent-context) for loading conditions and the recommended structure. ## Permissions The operations available to Codatum Agent are limited to the intersection of the following conditions: 1. **User access** * The Agent can only use Notebooks, tables, and connections that the user is allowed to view or edit. 2. **Operation mode** * Ask mode cannot use tools that create or edit Notebooks. 3. **AI Profile permissions** * Notebook scope, connections, domains for web search and fetch, and Agent Connectors can be restricted. 4. **Tool approval** * Approval can be required before executing SQL or performing an operation in an external service. AI Profile permissions do not extend the user's own access. ## Tool execution and approval Approval may be required before executing SQL, calling an MCP tool, or sending an HTTP request through an Agent Connector. The following auto-approval settings are available: * SQL execution * Configure it for all connections or individual connections. * For BigQuery, set a maximum scan size based on dry-run results. * `MCP Tool Execution` * `Connector HTTP Request` {% hint style="warning" %} When auto-approval is enabled, queries and external service operations can run without an additional confirmation. Review the AI Profile permissions and the permissions granted at the destination before enabling it. {% endhint %} ## Agent screen and settings The Agent screen provides the following operations: * Start a new chat * View chat history * Review session start time and token usage * Switch between side-panel and full-page views * Send feedback to the Codatum team The settings menu in the input area provides the following options: * Ask mode or Agent mode * Preferred connection * Auto-approval for SQL, MCP tools, and Connector HTTP requests * Auto-loading of personal, Teamspace, and Notebook Agent Context * AI Profile * Response language * Enter key behavior When starting an unrelated analysis, begin a new chat to avoid using context on conversation history that is no longer needed. ## Usage Workspace administrators can review session counts, token usage, and other information under `Workspace Settings` > `Agent Usage`. Usage data is retained for 30 days. If conversation message logging is disabled in the AI Profile, usage statistics are recorded, but message content is not displayed. ## Data handling The external LLM services used by Codatum contractually guarantee that submitted data is not used to train their models. The following information may be sent to the selected LLM service so that the Agent can analyze data and perform operations: * Messages entered by the user * Notebook and page content * References specified with `@` * Table schemas and data * SQL query results * Agent Context Codatum developers record usage time, user and workspace identifiers, errors, approximate token usage, and processing time for service quality improvement. Prompt and response content, references added with `@`, SQL results, and raw data from data sources are not recorded. Before using Codatum Agent, review your organization's AI usage policy. ## Related pages * [Agent Context](/ai-assist/agent-context) * [Agent Connectors](/ai-assist/agent-connector) * [AI Profile](/ai-assist/ai-profile) # Agent Context Specifications for automatically loaded Agent Context and context configured in AI Profiles Agent Context is background information automatically provided to Codatum Agent. It can contain metric definitions, Notebooks to reference, and analysis considerations so that users do not need to repeat the same information in every message. {% hint style="info" %} Agent Context does not change access permissions. The information and operations available to Codatum Agent remain limited by the permissions of the user and the [AI Profile](/ai-assist/ai-profile). {% endhint %} ## Context types Codatum Agent automatically loads the following types of context: | Type | Configuration | When it is loaded | | ---------- | -------------------------------------------------------------------------- | ----------------------------------------- | | Personal | `.agent` folder directly under the private folder | Whenever Codatum Agent is used | | Teamspace | `.agent` folder directly under a Teamspace | When viewing a Notebook in that Teamspace | | Notebook | `.agent` page in a Notebook | When viewing that Notebook | | AI Profile | Supplemental text, context Notebooks, and context folders in an AI Profile | When using that AI Profile | For `.agent` folders under a private folder or Teamspace, all Notebooks in the folder and its subfolders are loaded. When a folder is configured in an AI Profile, all Notebooks in that folder and its subfolders are also loaded. ## `.agent` folders Open the add menu at the root of a private folder or Teamspace and select `New Agent Context folder`. This creates a `.agent` folder and an `Agent Context Index` Notebook. The generated Notebook contains a template based on the recommended structure below. A `.agent` folder can be created in the following locations: * Directly under a private folder * Directly under a Teamspace It cannot be created directly under the workspace folder or inside a regular subfolder. Notebooks in a `.agent` folder can contain text, SQL blocks, and saved results. SQL blocks can provide the Agent with standard queries or instructions for calculating metrics. ## Notebook `.agent` pages Create a page named `.agent` in a Notebook to load its content whenever that Notebook is open. Use a `.agent` page for assumptions and considerations specific to a Notebook. Use a `.agent` folder for information shared across a user or Teamspace. ## AI Profile context An AI Profile can contain the following context: * Supplemental text * Context Notebooks * Context folders To reference a context Notebook or folder, the user must have permission to view it. A user cannot use an AI Profile if it contains a Notebook that the user cannot access. You can manage editors and viewers by placing shared context Notebooks and folders in a dedicated Teamspace. ## Auto-loading settings The settings menu in the Agent input area allows users to enable or disable automatic loading for the following Agent Context types: * Personal * Teamspace * Notebook Context configured in the selected AI Profile is loaded as part of that profile. Open context usage in the input area to review the Notebooks and pages that are currently selected for automatic loading. ## Always-loaded and on-demand context There is no setting that enables lazy loading. Separate information that should always be loaded from information that should only be referenced when needed by choosing where to place the Notebooks and whether to attach them to an AI Profile. | Loading behavior | Configuration | Result | | ------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------- | | Always load personal context | Place Notebooks in a `.agent` folder directly under the private folder and enable `Personal` auto-loading | All Notebooks in the folder and its subfolders are loaded whenever Codatum Agent runs | | Load Teamspace context | Place Notebooks in a `.agent` folder directly under a Teamspace and enable `Teamspace` auto-loading | All Notebooks in the folder and its subfolders are loaded when a Notebook in that Teamspace is open | | Load Notebook-specific context | Create a `.agent` page in a Notebook and enable `Notebook` auto-loading | The `.agent` page is loaded when that Notebook is open | | Always load context for an AI Profile | Configure plain text, context Notebooks, or context folders in the AI Profile | The context is loaded whenever the AI Profile is used. A configured folder also includes Notebooks in its subfolders | | Reference details only when needed | Place only the index in one of the locations above, store detailed Notebooks in regular folders, and link to the target pages from the index | The index is loaded automatically, while detailed Notebooks are referenced through their links based on the question | Disabling `Personal`, `Teamspace`, or `Notebook` auto-loading prevents that context from being loaded automatically. Disabling auto-loading does not convert the context to lazy loading. These settings also do not apply to context configured in an AI Profile. For the Agent to reference detailed Notebooks on demand, the user must be able to view them and the AI Profile's Notebook access must be set to `All accessible notebooks`. When it is set to `Current notebook only`, the Agent cannot reference detailed information stored outside the current Notebook. ## Recommended structure We recommend placing only an index Notebook in a `.agent` folder instead of storing all detailed knowledge there. Notebooks in a `.agent` folder are automatically loaded during Agent execution. Placing extensive specifications or past analyses in the folder consumes context for information unrelated to the current question and makes it harder to identify the relevant source. Store detailed information in regular Notebooks and folders. In the index Notebook, describe: * What information is available * When each source should be referenced * A page link to the canonical Notebook * The location of the Notebook * Keywords that can be used to find it * What to do when the information cannot be found or a decision cannot be made With this structure, Codatum Agent initially loads only the index and follows the relevant page links when detailed information is needed. {% hint style="warning" %} Always include a link to the target page, not only the Notebook name. With only a Notebook name or location, Codatum Agent cannot directly identify a page outside the `.agent` folder and must rely on search results. Open the target page in Codatum, copy its URL, and insert it as a link in the index. {% endhint %} ### Example index Notebook ```markdown # Agent Context Index ## Shared rules * Use the "KPI Definitions" Notebook as the canonical source for metric definitions * If a definition cannot be found, ask the user instead of making an assumption * Confirm the target period and time zone before creating SQL ## Knowledge Map ### Sales and orders * Source: [Sales and Order Metrics](https://app.codatum.com/workspace/WORKSPACE_ID/notebook/NOTEBOOK_ID/PAGE_ID) * Location: "Data Knowledge" Teamspace * Use for: Analyses related to sales, orders, and cancellations * Search keywords: sales, order, cancellation, GMV ### Customers * Source: [Customer Definitions](https://app.codatum.com/workspace/WORKSPACE_ID/notebook/NOTEBOOK_ID/PAGE_ID) * Location: "Data Knowledge" Teamspace * Use for: Analyses related to new customers, existing customers, and retention * Search keywords: customer, new, existing, retention ``` Replace `WORKSPACE_ID`, `NOTEBOOK_ID`, and `PAGE_ID` in the example with the URL of the actual target page. Include the Teamspace or folder name in addition to the link so that users and the Agent can understand the purpose and ownership of the source. ## Structures to avoid Avoid the following structures: * Placing all detailed specifications and past analyses in a `.agent` folder * Duplicating the same metric definition across multiple contexts * Providing only summaries without identifying the canonical source * Including rules whose update date or scope is unknown * Attempting to control access or tool execution through context instructions Configure access and tool availability through Notebook and Teamspace permissions and AI Profile permissions. ## Related pages * [Codatum Agent](/ai-assist/ai-agent) * [AI Profile](/ai-assist/ai-profile) # Agent Connectors Connect Codatum Agent to external APIs and MCP servers Agent Connectors allow Codatum Agent to connect to external APIs and MCP (Model Context Protocol) servers such as GitHub, Slack, and Notion. You can add a built-in connector or register a custom MCP server or REST/GraphQL API. ## Key features * **Built-in connectors** * Add connectors for services including GitHub, Slack, Notion, Atlassian, Salesforce, HubSpot, Linear, Airtable, PagerDuty, Sentry, Stripe, Datadog, dbt Cloud, Google Drive, and Workato. * **Custom connectors** * Register an MCP server or REST/GraphQL API endpoint that is not included as a built-in connector. * **Credential management** * Configure OAuth credentials, API keys, and other credentials at workspace or personal scope. * **Tool discovery** * Review the tools provided by an MCP connector. * **AI Profile access control** * Configure connector access as off, selected connectors only, or all connectors for each AI Profile. ## Connector types ### MCP Connects to an external MCP server and allows Codatum Agent to discover and execute its tools. This type is used for services that provide an MCP server, including Notion, Atlassian, Salesforce, Sentry, Stripe, and Datadog. ### Direct API Sends requests directly to an external REST or GraphQL API using a registered API key or other credential. {% hint style="info" %} The built-in GitHub connector accesses the GitHub REST API directly instead of using an MCP server. Configure a GitHub Personal Access Token or another supported credential. {% endhint %} ## Built-in connectors | Connector | Type | Description | | ---------------- | ---------- | -------------------------------------------------------------------- | | GitHub API | Direct API | Access repositories, Issues, Pull Requests, users, and organizations | | Slack Web API | Direct API | Access channels, messages, users, and other Slack resources | | HubSpot API | Direct API | Access CRM contacts, companies, deals, and tickets | | Linear API | Direct API | Access Issues, projects, teams, and cycles | | Airtable API | Direct API | Access bases, tables, records, and fields | | PagerDuty API | Direct API | Access incidents, services, and on-call schedules | | Notion MCP | MCP | Search and edit pages and content in a Notion workspace | | Atlassian MCP | MCP | Connect to Jira Issues and Confluence knowledge bases | | Salesforce MCP | MCP | Access Salesforce records and queries | | Sentry MCP | MCP | Access Issues, projects, events, and debugging information | | Stripe MCP | MCP | Access customers, invoices, and payments | | Datadog MCP | MCP | Access logs, metrics, traces, and incidents | | dbt Cloud MCP | MCP | Access the Semantic Layer, Discovery, and development context | | Google Drive MCP | MCP | Access files and documents in Google Drive | | Workato MCP | MCP | Access recipe-based tools and workflow automation context | ## Configuration 1. Go to `Workspace Settings` > `Agent Connectors` and select `Add connector`. 2. Select a built-in connector or configure a custom connector with an MCP server URL or API endpoint. 3. Select workspace or personal credential scope. 4. Configure OAuth or enter an API key or other secret under connector credentials. 5. Allow the connector in the AI Profile's connector access settings. After configuration, Codatum Agent can discover and use the connector's tools or APIs when needed during a conversation. ## Approval Connector tool calls and API requests require user approval by default. Users can enable auto-approval for `MCP Tool Execution` and `Connector HTTP Request` in their Agent settings. {% hint style="warning" %} Review the requested operation before approving it. Depending on the connector and tool, the operation may modify data in the external service. {% endhint %} ## Limitations * The server URL of an MCP connector cannot be changed after creation. Delete and recreate the connector to use a different URL. * Connector access must be explicitly allowed in the AI Profile. The default is off. * Restrictions are applied to external destinations to prevent unintended access to internal networks and other protected addresses. # AI Profile Configure models, context, and permissions used by Codatum Agent ## Overview An AI Profile combines the AI model, context, tool permissions, and other settings used by Codatum Agent. Create profiles for different purposes and select the appropriate profile from the Agent screen. ## Types ### Managed AI Profile Uses an AI model managed by Codatum. No API key is required. You can select the model tier to use. The AI provider and endpoint cannot be specified. Use a Custom AI Profile if your security requirements require a particular provider or credential. ### Custom AI Profile Uses a BYOAI (Bring Your Own AI) model with an API key or service account provided by your organization. You can configure the AI provider, model, reasoning effort, and related connection settings. When using Google Vertex AI, grant the `Vertex AI User` role to the service account. Registered secrets are stored encrypted. {% hint style="info" %} Custom AI Profile is a paid option. For details, contact us via chat or at `support@codatum.com`. {% endhint %} ## Configuration Go to `Workspace Settings` > `Options` > `AI Profile`, select `Create AI Profile`, and create a Managed AI Profile or Custom AI Profile. Available settings depend on the profile type and your plan. ## Context An AI Profile can include the following context: * **Plain text** * Add assumptions, terminology, and interpretation guidelines. * **Notebook** * Load the content of a selected Notebook. * **Folder** * Load all Notebooks in the selected folder and its subfolders. Notebooks and folders are loaded only when the user running the Agent can view them. A user cannot use an AI Profile if it contains a Notebook that the user cannot access. {% hint style="warning" %} Notebooks and folders configured in an AI Profile are not loaded for Guest Report Agents or Workflow executions. Plain text is applied to Guest Report Agents but is not used in Workflow executions. {% endhint %} If the Agent does not need to load every document in a folder, configure only an index Notebook and allow the Agent to search for the information it needs. See [Agent Context](/ai-assist/agent-context) for the recommended structure. ## Tool permissions Configure the information and tools that Codatum Agent can use for each AI Profile. ### Notebook access * `All accessible notebooks` * `Current notebook only` ### Connection access * `All accessible connections` * `Allow only selected` * `Deny selected` ### Web search and web fetch Configure web search and web fetch separately: * `Off` * `Allow specific domains` * `On` When allowing specific domains, enter domains in the form `example.com` or `*.example.com`. ### Agent Connectors * `Off` * `Allow only selected connectors` * `All connectors` See [Agent Connectors](/ai-assist/agent-connector) for connector configuration. {% hint style="info" %} AI Profile permissions do not extend the user's own access. The Agent can only use resources and tools allowed by both the user's permissions and the AI Profile. {% endhint %} ## Sharing Configure who can use and manage an AI Profile for all workspace members, a group, or an individual user. * `Owner` * Can modify the AI Profile. * `User` * Can use the AI Profile in Codatum Agent. Workspace owners are always owners of every AI Profile. ## Conversation logs When `Save conversation message content to logs` is enabled, workspace administrators can view user and assistant messages for Agent sessions that use the AI Profile under `Agent Usage`. This setting does not share conversation message content with Codatum developers. See [Data handling in Codatum Agent](/ai-assist/ai-agent) for the information recorded by Codatum developers for service quality improvement. ## Custom behavior and start screen Depending on your plan, you can configure Agent behavior and the chat start screen for each AI Profile. * **Custom behavior** * Add instructions for how the Agent should ask questions, explain results, and perform analyses. * **Default behavior** * Disable default instructions that should not apply to the AI Profile. * **Start screen** * Configure a welcome title, description, and suggested prompts. # AI Assistant Powerful assistant for SQL generation, modification, fixing, and completion with LLM {% hint style="info" %} In workspaces where the AI Agent feature is available, [Codatum Agent](/ai-assist/ai-agent) is displayed instead of AI Assistant. In addition to SQL generation, Codatum Agent supports data exploration, query execution, and Notebook creation and editing. {% endhint %}
AI Assistant is a feature that understands your intent and generates the necessary SQL for you. By telling the analysis you want to perform or the SQL you want to generate in natural language, AI Assistant generates or modifies SQL accordingly. ## Availability AI Assistant is available in workspaces whose plan does not include the AI Agent feature. On plans that include AI Agent, AI Agent is displayed in the same locations and AI Assistant cannot be selected separately. ## How to Set Up Go to `Workspace Settings` > `Options` > `Codatum AI` and enable AI Assistant. After enabling it, select an SQL Block and use the AI icon in its upper-right corner. ## Key Features and How to Use * SQL Generation: * After creating an SQL Block, select the table to be referenced by the Assistant and specify the analysis you want to perform. * SQL Modification: * Select the SQL Block you want to modify and instruct the Assistant on how to modify it. * SQL Auto Fix: * By selecting `Fix with AI` on an SQL Block that has an error, the Assistant will perform corrections based on the SQL statements that need fixing and the error messages. ## Data Privacy These requests are sent to each LLM platform you choose but are not used for training purposes. The data included in key features are as follows: * SQL statement within the SQL block * The schema of the referenced table and the schema of the result of the SQL execution, as specified in: * Tables and SQL Blocks referenced within the SQL block * Tables explicitly referenced in the GUI * Request messages input by the user {% hint style="info" %} To use SQL Assistant feature with your own API key, please contact us via chat or `support@codatum.com` {% endhint %} # Workflow(Beta) Automatically execute queries based on schedule settings and send notifications. {% hint style="warning" %} This feature is currently in beta and under development. Specifications may change without notice. {% endhint %} By configuring schedule settings, you can periodically execute queries and integrate data with external systems. Codatum's workflow allows you to use query execution results in Liquid format for external notifications and other purposes. ## Main Features * Schedule Settings * Can be configured for the following intervals: * Every hour * Every day * Every week * Every month * Timezone configuration is available * Codatum Actions * Query Execution * Select and execute queries saved in ["Queries"](/data-exploration/query) * Up to 20 rows of executed query results are retrieved and can be used in subsequent processes in Liquid format * Run Report * Select and execute a saved report * When "Use Default Parameters" is enabled, parameters are reset to the default values configured in the report before execution * When "Update Report After Execution" is enabled, the execution results are saved back to the report * Screenshot * Captures a specific page of a report in PNG format * Theme (light/dark) and locale (English/Japanese) can be configured * The generated image is output as a file URL and can be used as an attachment in subsequent Email or Slack actions * By placing this step after a "Run Report" step, you can capture the page with the latest data reflected * Email * Send emails to team members * Email body can be configured in HTML format * Emails are sent from `noreply@workflow.codatum.com` with a Codatum footer * LLM * Call LLM to perform natural language processing * By providing query execution results in Liquid format, you can use it for purposes such as having AI create data summaries for sending * The AI to be used can be configured in [AI Profile](/workflow/workflow/ai-profile) * Execution Conditions (Optional) * You can configure conditions for whether to execute each action * By setting `failure()`, you can configure actions to be executed when an error occurs in the workflow * When an error occurs in the workflow, only actions with `failure()` configured will be executed, and other actions will not be executed * Integrations * Slack * By creating and selecting a Slack integration, you can notify Slack of workflow execution results * The content to output in Slack messages can be configured in Liquid format based on query execution results * For detailed specifications regarding notation such as mention settings, please refer to [Slack Developers: Formatting message text](https://docs.slack.dev/messaging/formatting-message-text/#basic-formatting) Codatum actions and integrations other than those listed above will be added sequentially. ## How to Use * Creating a Workflow * You can create it in two ways: * Create from "Create Workflow" on the workflow screen * Create from the right sidebar of a query saved in "Queries" * It will be created with the saved query already selected for query execution ## Workflow Management From the workflow list, you can configure and check the following: * Workflow Settings * Owner Permission Settings * Only members with owner permissions can view and edit workflows * Check Last Execution Time * Check Workflow Execution Details * You can check the executed queries and the content sent by integrations * Execution History * Past execution history is displayed * Can be used to check at which step a failure occurred ## Liquid Templates In Codatum's workflow settings, you can select variables to use from the UI using "\ (Insert Variable)", but understanding the Liquid format enables more advanced configurations. Most of the Liquid template is available, so please refer to [Liquid Template Language](https://shopify.github.io/liquid/) for details. # Adding Integrations Add integrations to use in workflows. * Integrations can be added from "Integrations" in "Options" under "Workspace Settings" ## Slack ### Adding Flow * Select "Slack" from "Create Integration" * Click "Connect" to display the Slack authentication screen, select "Allow" and authenticate Codatum * If you are not signed in to the Slack workspace, you will need to sign in to the Slack workspace * The Codatum app will be added to your Slack workspace ### Notes * To send Slack messages, you need to add `Codatum` to the channel where you want to send messages * Add `Codatum` to the channel using /invite # Use Cases Practical examples of using Codatum workflows. ## Sending Notifications to Slack ### Scenario: Notify about recent account creations Create a query to count accounts created within the last day, and create a workflow that sends notifications to Slack only when new accounts are created. #### Creating a Query * Output data for the last day based on the creation date column ```sql SELECT * FROM `codatum-example.example.accounts` WHERE created_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY) ORDER BY created_at DESC ``` #### Schedule Settings * Configure to execute daily at 10:00 AM * Repeat interval: `Every day` * Start time: `10:00 (GMT+09:00) Japan Standard Time` #### Step 1: Run Query * Select the query created above * If you create a workflow from the "Queries" screen, the workflow configuration will start with the selected query already entered in Step 1 #### Step 2: Slack (Codatum) * [Add the integration](/workflow/workflow/add-integration) and add the App to the Slack channel you want to notify in advance * Channel: `notification_codatum` * Channels with the App added will be listed * Message body: * You can check how to enter variables to use in the message based on the "First row" * Selecting account\_name will be entered as `{{ query.rows[0].account_name}}` * To retrieve multiple rows of query execution results and display them row by row, select "\ Rows" * Loop processing will be automatically entered in Liquid notation * In each loop, the value is entered in `row`, so select the variable you want to enter in the form `{{ row.account_name }}` * Thread * When sending Slack in multiple steps, you can send messages in a thread to the message sent in the previous step * Include link to Codatum * You can add a link to the workflow * Viewing the workflow requires owner permissions for the workflow * Execution Conditions (Optional) * Check whether there are output results in the query of Step 1 with the following conditional expression * `{{ query.rowCount }} > 0` * It can be entered by selecting "Row Count" on the GUI from "\ (Insert Variable)" * `query` stores the output results of "Run Query" * If you have configured multiple "Run Query", an identification number such as `query_1` will be added * If the condition is not met, the action will not be executed ```liquid Number of accounts created within the last day: {{ query.rowCount }} Recently created accounts (displaying up to 5) {% for row in query.rows limit:5 %} Account name: {{ row.account_name }} Created date: {{ row.created_at }} {% endfor -%} ``` # AI Profile Configure AI profiles to use in workflows. * AI profiles can be created from "AI Profile" in "Options" under "Workspace Settings" ## Adding Flow * Click "Create AI Profile", enter the following settings, and click "Create" * Name * Provider * OpenAI, Anthropic, and Google Gemini are available * Model * API Key * Please obtain from each provider's console # Sharing Overview Overview of the methods for sharing Notebooks.
Notebook contents can be shared with other users and externally in the following ways: 1. Granting notebook permissions * Change the base permissions of the notebook * Base permissions for notebooks are determined by the (root) folder where the notebook is stored: * `Workspace`: All members in the workspace can access * [`Teamspace`](/sharing/teamspace): Members belonging to the teamspace can access * `Private`: Only the notebook owner can access * Base permissions can be changed by moving the notebook to a different folder * Set the additional permissions of the notebook * By setting additional permissions, you can [share notebooks](/sharing/shared-notebook) with specific members or groups within the workspace * Notebooks in `Workspace` folders are already shared with the entire workspace, so additional permissions cannot be set * [Share notebooks](/sharing/shared-notebook) with specific users or groups within the workspace 2. Creating and sharing snapshots from notebooks * [Report](/sharing/report): Convert notebooks to reports and share with users in the workspace, including workspace guests * When inviting external users as guests, the invited users must have a Codatum account * [Public Link](/sharing/public-link-and-embed): Make it publicly accessible on the internet to anyone who knows the URL * [Signed Embed](/sharing/signed-embed): Integrate into external applications and share with users of those applications ## Summary of Differences in Each Sharing Methods
MethodEditableQuery executionParameterExplorerEmbeddableGuest
access		Login requiredConnection requiredAPI auth required
Permission GrantingtrueYesYes (values shared)Yestruefalsetruetruefalse
ReportfalseSelectableSelectable (values per user)Selectabletruetruetruefalsefalse
Public LinkfalseNoNoNotruetruefalsefalsefalse
Signed EmbedfalseSelectableSelectable (values per user)Selectabletruefalsefalsefalsetrue
* Editable: Indicates if users can edit the notebook. * Query Execution: Shows whether query execution is possible. * [Parameter](/data-exploration/parameter-overview): Indicates whether parameters are enabled and specifies if parameters are shared or user-specific. * Embeddable: Determines if content can be embedded in other platforms using HTML iframes. * Guest access: Indicates if Workspace Guests can access the shared content. * Login required: Shows if login to Codatum is required to access the content. * Connection required: Indicates if connection permissions are required to execute queries and view results. * API auth required: Indicates if API authentication is required to access the content. ## Guidelines for Choosing a Sharing Method Choose the appropriate sharing method according to the situation. For example: ### Permission Granting * For collaborative notebook editing ### Report * When you don't want to share notebook changes in real-time * If you want to make parameter changes separately, consider using the notebook's [version feature](/data-exploration/notebook/version) * When sharing with members in the workspace who don't have connection permissions * When you want to share only specific notebook contents without sharing other workspace data * Invite users as Workspace Guests and grant viewing permissions * If you have previously logged in with another account, the sign-up screen may not appear when clicking the link in the invitation email. In such cases, please try signing up using incognito mode ### Public Link * For marketing purposes when you want to share with a large audience * Embedding in blogs * Sharing on social media like Twitter * When you want to embed data in internal tools like Notion * Consider using Reports if you want to enforce login ### Signed Embed * When you want to integrate into external applications and share with users of those applications * When you want to allow access only to users authenticated through your company's authentication system # Teamspace Sharing notebooks with the members of a teamspace
Teamspace is a feature that allows you to collaboratively edit notebooks and folders within a specific team (members and groups). By setting permissions for teamspace, access restrictions can be applied to all notebooks and folders within the teamspace, thus ensuring consistent access control. ## Key Features * Centralized Collaboration: * Bring team members together in a dedicated space for collaborative editing and data analysis. * Access Control: * Assign specific permissions to users and groups within the Teamspace to ensure appropriate access levels. * Folder and notebook Management: * Create and organize folders and Notebooks within the Teamspace for structured data management. ## How to Set Up 1. Create a Teamspace [on this page](https://app.codatum.com/workspace/last-accessed/setting/teamspaces). * Assign permissions to users or groups for the teamspace. 2. Create folders or Notebooks under the Teamspace. ## FAQ > What happens if I grant viewing permissions for a notebook within a teamspace to a member or group that does not have access permissions to the teamspace? Users or groups without assigned permissions to the teamspace will see the notebook under the `Shared` folder and will be able to access it. > Is it possible to move notebooks into or out of a teamspace? Yes, it is possible. When moving a notebook, its access permissions will be updated according to the new location (Workspace/Teamspace/Private). # Sharing per notebook Sharing by additional permissions You can share notebooks directly with specific members or groups by granting additional permissions. ## Key Features * Instant Sharing: * Easily share individual notebooks with specific users or groups, enabling quick collaboration. * Real-time Collaboration: * Collaborate in real-time with team members, making edits and updates seamless. * Access Control: * Choose `Notebook Editor` or `Notebook Viewer` roles for each shared notebook, ensuring appropriate access levels. * Private Sharing: * Share private notebooks securely with selected users or groups. * Visibility in `Share` button: * Shared notebooks are easily accessible from the `Shared` tab, allowing quick and organized access. ## How to Set Up 1. Select notebook * Choose a notebook that is in an access scope other than workspace. 2. Set Permissions * Click `Share` in the header. * In `Additional permissions per notebook`, add the members or groups you want to share with and set the required roles (`Notebook Viewer` or `Notebook Editor`). References: {% content-ref url="/pages/PsaNRWVToM9HwZNMP5u7" %} [Access Control for Notebook](/access-control/access-control-for-resources/access-control-for-notebook) {% endcontent-ref %} {% hint style="info" %} Connection permissions are independent of notebook permissions. Please set connection permissions as needed. {% endhint %} # Report Convert a notebook into a report for sharing with Guests.
Report is a snapshot of a notebook designed to allow users only to view the content. By creating a report, you can safely share analysis results with other members and guests without granting connection permissions. ## Key Features * Focus on Important Pages: * Specific pages can be selected. * Static and View-Only: * Report allows creators to share original data without worrying about it being altered. Viewers can access and review important information without altering the content. This ensures creators and viewers that the shared data remains intact and secure. * Guest Access: * Guest is a special workspace role that can only view the report. They do not need access to the notebook or connection, making it possible to share with a broader range of users. * No Connection Role Required: * You can share aggregated statistical data and charts without granting access to the original data. * High Performance: * Data is pre-fetched and saved as a snapshot, resulting in faster display speeds. * Report type for your purpose: * You can select whether to allow query re-execution and parameter changes for report viewers. * Data Access Control with Fixed Parameters: * Fix parameter values when publishing a report to restrict the data range accessible to viewers. This hides parameter input fields and prevents value modification, ensuring secure data isolation in multi-tenant environments. * Parameter-URL Synchronization: * When you change parameters within a report, the changed parameter information is synchronized to the URL. Therefore, you can share parameter values with other users by sharing the URL. For details, see [How to Specify Parameters from URLs](/data-exploration/parameter-overview/set-parameters-via-url). ## How to Set Up * Open the notebook * Open the notebook you want to share. * Click the `Share` button in the header and select `Report` tab. * Click `Create report` button. * Select `Publish pages` * Choose the pages you want to publish. * Select `Report type` from the following: * `Interactive report` * Allows parameter changes and query re-execution. * You can select options such as data download and explorer function usage. * `Refreshable report` * Allows data updates with query re-execution. * `Static report` * Displays data at the time of report publication. * Set options according to `Report type` (except for `Static report`) * You can further restrict the range of data that can be viewed by specifying `Fixed parameters`. * Publish * Click the `Publish` button. * (The system will check in the background if you have the necessary permissions to publish the notebook. Once verified, the report creation is complete.) * Set Permissions for the report * Open the report and click `Report settings` in the top right corner. * Add users or groups in the `Permissions` tab to grant permissions to users in the workspace. * Add external users in the `Invite` tab to invite them as workspace guests. * `Report settings` * Additional options can be configured: * Auto-execute * When enabled, queries are automatically executed when the page loads. * Use parameter default values * This can only be enabled when "Auto-execute" is enabled. * When enabled, queries are automatically executed using parameter default values. By setting dynamic parameters such as relative dates as parameter default values, dynamic data display can be performed automatically. * Synchronize parameter changes across pages * When enabled, changes to notebook-shared parameter values are synchronized across pages. * Changes are applied in the following order: "Fixed parameters > Parameter synchronization > Parameter default values". ## Notes Operations related to reports, especially publishing, require complex role combinations. Please refer to the documentation: {% content-ref url="/pages/fmRYdpVxmykf3ncFDHcR" %} [Access Control for Report](/access-control/access-control-for-resources/access-control-for-report) {% endcontent-ref %} # Public Link Share notebook publicly via a link accessible to anyone with the URL.
Public Link allows you to share your notebook widely by creating a public link or embed code. Anyone with the link can access the shared content without needing a login. ## Key Features * Public Accessibility: * Anyone with the link can view the content, facilitating easy sharing without access restrictions. * Embed Code: * Easily embed the notebook into websites or blogs using the generated embed code. * SEO Control: * Public links are set to “no-index” to prevent search engines from indexing the content, ensuring privacy and control over the shared data. * Customizable Expiration: * Set an expiration date for the published content to automatically delete after a specified number of days, providing an extra layer of security. * High Performance: * Data is pre-fetched and saved as a snapshot, resulting in faster display speeds. ## How to Set Up 1. Open the notebook * Open the notebook you want to share. 2. Set Permissions * In the header, click the `Share` button. Select `Public link` tab. * Click `Create public link` button. * Choose the specific pages you want to include in the public link. 3. Set Expiration (Optional) * If you want to set the expiration, enable the expiration option and set the number of days after which the published content will be deleted. 4. Publish * Click the `Publish` button to generate the public link. 5. Distribute * Copy the link or embed code and distribute it as needed. # Signed Embed Integrate Notebooks into your application. Signed Embed allows you to directly embed Codatum Notebooks into your own applications while utilizing your existing authentication system. This integration enables your application users to access Codatum Notebooks and Dashboards without leaving your application environment. {% hint style="info" %} This feature may be referred to as "White Label Embedding" or "Embedded Analytics" on other platforms. {% endhint %} {% hint style="warning" %} This feature is an optional feature for the Enterprise plan. If you wish to use this feature, please contact Codatum Support. {% endhint %} ## Key Features * Secure Embedding in Existing Applications: * Secure access control using authentication tokens * User authentication leveraging your existing system without requiring Codatum accounts * Multi-tenant Data Access: * Restrict each customer's data access to their own data subset by configuring server-side fixed parameter values for different clients * Create a single notebook that serves multiple client organizations with appropriate data isolation * Interactive Experience: * Enable parameter adjustments for personalized analysis * In-depth exploration with Explorer functionality * Data download capabilities * Flexible Integration: * Exchange messages between your application and embedded Notebooks ## Configurable Options * Publish Settings * Page Selection: * Select specific pages to include in the embedded view * Access Control: * Enable/disable data download * Enable/disable Explorer functionality * Parameters: * Server-side parameters: * Fixed value: Secure parameter that cannot be modified by the user * Initial value: Initial value is set server-side, but modification by the user is allowed * Client-side parameters: * Parameters other than server-side parameters, which can be specified dynamically when rendering the embed * Security: * API keys: Configure which API keys are available for authentication per embed * Allowed origins: Restrict domains permitted to embed the notebook * Display Options: * Theme (light/dark), language settings, and other options can be customized ## Multi-tenant Implementation Tip Multi-tenant data access allows a single notebook to serve multiple customers while ensuring each customer only sees their own data. Using "Fixed value" parameters, you can create data segregation by filtering data or switching tables/schemas based on client identifiers. ## Next Steps For setup instructions, please refer to the next page. {% content-ref url="/pages/gumAHkJfKYJUAcz6BB3y" %} [How to Set Up Signed Embed](/sharing/signed-embed/integration) {% endcontent-ref %} # How to Set Up Signed Embed Seamlessly Integrate notebook into external services. ## How to Set Up ### Prepare signed embed * Prepare API Key and Secret * Open `Workspace settings` and click menu `API Keys`. * Click the `Add API Key` button and create new API Key. * Make a note of the API Key and API Secret (they will be used later for API calls). * Open the notebook * Open the notebook you want to share. * Click the `Share` button in the header and select `Signed Embed` tab. * Publish signed embed * Choose the pages you want to publish. * Set the options for publishing. * Click the `Publish` button. * Configure signed embed settings. * Add API Key to use in `Permitted API Keys` section. * If you want to set parameter values when issuing a token, configure them in the `Server-side parameters` section. ### Issue token from server * Use the following API to issue a token * The required arguments for calling the API can be found on the `Setup guide` tab. * Perform the API call on the server side to prevent the **API Secret** from being exposed externally. * Issue a token for each user session, and do not reuse it (it expires after 1 hour by default). {% openapi src="" path="/api/notebook/issueToken" method="post" %} {% endopenapi %} ### Embed in frontend * Use [Codatum Embed SDK](https://github.com/codatum/codatum-embed-js) to embed in your frontend * The `embedUrl` required for embedding can be found on the `Setup guide` tab * Set `tokenProvider` to the API that issues tokens on the server side * For all other configurable options, see the [SDK README](https://github.com/codatum/codatum-embed-js/tree/main/packages/embed#readme) ## Appendix ### Frequently Asked Questions #### Getting "Missing param: xxx" error when issuing a token * To issue a token, all server-side parameters must be included in the request * This error occurs when a parameter with the ID "xxx" is not included in the request #### Getting "The following parameters must be set by the server" error when sending client-side parameters * You are trying to change server-side parameters when sending client-side parameters * To change server-side parameters, a new token must be issued #### Parameters not updating when sending client-side parameters * Check that the parameter you are sending is not specified as a server-side parameter * Verify that `param_value` is in the correct JSON string format * Confirm that `param_id` matches an existing parameter ID # Use Cases Use cases for signed embed ## Case: Utilizing Embedded Dashboards in Retail Support SaaS ### Scenario In a SaaS platform that supports retail store operations, we assume a need to embed dashboards within the SaaS to understand sales performance and KPIs for each store. This functionality allows store managers and area managers to view only information related to their stores when they log in. ### Notebook Parameters The notebook that serves as the source for signed embed is assumed to have the following parameters configured: * `tenant_id`: * Tenant ID to which the logged-in user belongs * "Text input" type parameter * `store_id`: * Store ID for which data is to be displayed * "Text select" type parameter, where options allow selection of store IDs within the tenant specified by `tenant_id` * `date_range`: * Data aggregation period * "Date range" type parameter with a default value of relative dates spanning 7 days from the previous day * `product_category`: * Product category for narrowing down data aggregation targets * "Text multi select" type parameter, with SQL conditions specified to display all product categories when none are selected ### Configuration Example A: Using Signed Embed Forms #### Prerequisite Notebook Design * Security of data accessible to users is ensured by fixing `tenant_id` * SQL for data extraction always specifies `tenant_id` as a filter condition * For example, when extracting store data, both `store_id` and `tenant_id` are specified as filter conditions, preventing extraction of data from stores belonging to different tenants * `date_range` and `product_category` are designed to allow arbitrary values #### Parameter Configuration * `tenant_id`: **Fixed value for a server-side parameter** * `store_id`: Initial value for a server-side parameter * `date_range`: Client-side parameter * `product_category`: Client-side parameter #### Flow to Display 1. Token issuance on server-side * Specify the user's `tenant_id` as a fixed value for a server-side parameter * Specify the `store_id` to display in the initial state as an initial value for a server-side parameter, selected from among the `store_id` values associated with `tenant_id` 2. Initial rendering process on front-end * When sending the `SET_TOKEN` message, send the following client-side parameters together: * `date_range` is specified to reset to default value for relative date recalculation * `product_category` is specified with an empty array as the initial value 3. User dashboard operations * Users can change `store_id`, `date_range`, and `product_category` from the dashboard form ### Configuration Example B: Managing Parameters on SaaS Front-end #### Prerequisite Notebook Design The same design as "Configuration Example A" #### Parameter Configuration * `tenant_id`: **Fixed value for a server-side parameter** * `store_id`: Client-side parameter * `date_range`: Client-side parameter * `product_category`: Client-side parameter #### Flow to Display 1. Token issuance on server-side * Specify the user's `tenant_id` as a fixed value for a server-side parameter 2. Initial rendering process on front-end * When sending the `SET_TOKEN` message, send the following client-side options together: * Since parameters are managed on the SaaS side, specify the option to hide the parameter form (`displayOptions.hideParamsForm = true`) * Specify values managed on the SaaS side for `store_id`, `date_range`, and `product_category` 3. User dashboard operations * When changing dashboard display conditions due to changes in conditions managed on the SaaS side, follow these steps: * **Reuse** the token used in the initial rendering process and resend the `SET_TOKEN` message with updated client-side options * By resending the `SET_TOKEN` message, the dashboard is redrawn based on the newly sent client-side options * The client-side parameters sent at this time must include all items (`store_id`, `date_range`, `product_category`), not just the differences * When the same user uses the same fixed value for a server-side parameter, token reuse is possible (note the expiration time) ### Configuration Example C: Updating Server-side Fixed Values #### Prerequisite Notebook Design * Security of data accessible to users is ensured by fixing both `tenant_id` and `store_id` * Unlike "Configuration Example A", this assumes a case where `store_id` needs to be fixed on the server-side * `date_range` and `product_category` are designed to allow arbitrary values, same as "Configuration Example A" #### Parameter Configuration * `tenant_id`: **Fixed value for a server-side parameter** * `store_id`: **Fixed value for a server-side parameter** * `date_range`: Client-side parameter * `product_category`: Client-side parameter #### Flow to Display 1. Token issuance on server-side * Specify the user's `tenant_id` and the `store_id` to display in the initial state as fixed values for server-side parameters 2. Initial rendering process on front-end * When sending the `SET_TOKEN` message, send the following client-side parameters together: * `date_range` is specified to reset to default value for relative date recalculation * `product_category` is specified with an empty array as the initial value 3. User dashboard operations * Since `store_id` is a fixed value for a server-side parameter, it cannot be changed from the dashboard form, and the SaaS side needs to manage `store_id` and provide a form for changing the target store * When redrawing the dashboard by changing `store_id` based on user operations, follow these steps: * **Issue a new token** to the server-side specifying the new `store_id`, and send the `SET_TOKEN` message with the new token and client-side parameters * To change fixed values for server-side parameters, token reissuance is required * Client-side parameters must include all items (`date_range`, `product_category`), not just the differences * To preserve `date_range` or `product_category` changed by user operations, record the `PARAM_CHANGED` messages sent from the iframe and send them during redrawing * For `date_range` and `product_category`, users can change the display content from the dashboard form # Account Overview of Codatum user accounts and their workspace relationships. In Codatum, an account represents a user who can access and use Codatum. The following features are available for accounts: ## Key Features * Access to multiple workspaces: * An account can belong to multiple workspaces * For example, it's possible to belong to both a personal workspace and a company workspace * Switching between workspaces can be easily done from the workspace selector in the top-left of the UI * Workspace Roles and Access Control: * An account always has one [Workspace Role](/access-control/access-control-for-workspace) for each workspace it belongs to * [Resource Roles](/access-control/access-control-for-resources) can be assigned directly to an account or through a group * [Groups](/workspace-and-user-management/user-management/group) provide an efficient way to manage multiple Accounts together * For detailed information about Workspace Roles, Resource Roles, and access control configuration, please refer to the [Access control](/access-control/access-control-overview) page. # Account Settings Customize your Codatum account preferences and authentication methods. In Codatum, you can personalize your profile (e.g., avatar and nickname), adjust interface preferences (such as theme and language), and manage authentication options from your account settings. {% hint style="warning" %} The settings in account settings are shared across all workspaces you belong to. (Notification preferences can be configured for each workspace.) {% endhint %} ## Key Features ### Profile Customization: * Full name: The name displayed to other users * Nickname: A shorter name used when abbreviated in notebooks and other content * Profile image: An image used as your avatar ### Interface Preferences: * Theme: Choose between light mode, dark mode, or system preferences * Language: Language settings apply to the following elements: * UI display: Currently supports English and Japanese (if you select a language other than Japanese, English will be displayed) * Chart: Numeric and date formatting is reflected ### Authentication Methods: * Google Login: Single sign-on using a Google account * Email + Password: Authentication using email and password * Multi-factor Authentication (MFA): Strongly recommended for Email + Password authentication * Authenticator app support (Google Authenticator, Microsoft Authenticator) * SMS verification via registered phone number ### SQL Editor Preferences: * Tab size: Adjust the number of spaces for indentation * Upper/Lower case: Set whether SQL functions and keywords display in uppercase or lowercase ### Keyboard Shortcuts Customization: * Global: * Quick search * Toggle sidebar visibility * Open AI Assistant * Doc Page: * Search and replace text * Text formatting (Bold, Highlight, etc.) * SQL editor operations (Execute SQL, Format, Comment/Uncomment) * Grid Page: * Undo and Redo operations ### Notification Preferences: * Notification channels: Choose between in-app notifications and email notifications * Configurable notification events: * Comments on Notebooks and Reports * Publishing of reports or public links that include connections you own * Notifications when connection sync jobs fail ## How to Access Select `Account settings` from the profile icon in the bottom-left corner of the screen. # Workspace Understand the concept and management of workspaces, the fundamental units in Codatum A workspace is the basic unit for using Codatum. Connections, notebooks, reports, and other resources are managed within workspaces, and you can link resources to users appropriately using the [Access Control](/access-control/access-control-overview) feature. ## Key Features * Basic Management Unit: * Organize resources by workspace * Billing occurs per workspace * Manage multiple workspaces * Resource Sharing: * Share resources with team members * Secure access through permission settings ## Workspace General Settings * Workspace name: Name of your workspace * Organization name: Organization’s full legal name for contracts and billing * Country: Legal location for billing and contracts * [Allowed email domains](/workspace-and-user-management/user-management/invitation): Allow users with specified email domains to join your workspace # User Management Efficiently manage users, groups, and access within your Workspace. Codatum provides flexible options to manage Workspace access and organize your team. * [Invitation](/workspace-and-user-management/user-management/invitation): Learn different methods to invite users to your Workspace. * [Group](/workspace-and-user-management/user-management/group): Create and manage user groups for streamlined permission management. * [**Workspace Roles**](#workspace-roles): Understand the different roles and permission levels available in your Workspace. ## Workspace Roles Codatum offers several role types to control what users can do within your Workspace: * **Workspace Owner**: Full administrative control over the Workspace, including user management and billing. * **Workspace Editor**: Can create and modify content, including reports, notebooks, and connections. * **Workspace Viewer**: Can view content but cannot modify it. * **Workspace Guest**: Limited access to specific resources (Report) that have been explicitly shared. For detailed permissions and capabilities of each role, see [Access Control for Workspace](/access-control/access-control-for-workspace). {% hint style="info" %} In Codatum, the terms Workspace Owner, Workspace Editor, and Workspace Viewer are collectively referred to as “Member”. When including the Workspace Guest role, the collective term used is “User”. This distinction, while not critical, can be helpful to keep in mind. {% endhint %} # Invitation Learn how to invite users to your Workspace through various methods. This page explains the different ways to invite team members to your Workspace. ## Invitation Methods * Individual Invitations: * Send invitation emails to specific email addresses * Recipients join by explicitly accepting the invitation * Recipients can register with any email address, not just the invited one * Allowed Email Domains: * Anyone with email addresses from the allowed domains will automatically be able to join the workspace * Set default roles and groups by domain * Still able to invite users with other domains manually * Streamline onboarding for organization members * Invitation Links: * Create shareable signup links * Users who sign up through these links join the Workspace automatically * Users joining via invitation links are assigned the Editor role # Group User groups for streamlined access permission management within the workspace. Groups are a feature that allows you to collectively manage multiple users and efficiently assign access permissions to resources. ## Key Features * Efficient User Management: * Group multiple users together * Organize by departments, teams, or projects * Users can join multiple groups * Simplified Access Control: * Assign permissions at group level * Avoid setting permissions individually * Add new members to groups for instant access * Improved Management Efficiency: * Make bulk permission changes * Match groups to your organization * Auto-assign groups using email domains # Security Overview Manage security settings and monitor access to your Workspace resources. The Security section provides tools to enhance your Workspace security and track who is accessing resources and from where. * [Audit logs](/security/audit-logs): View detailed records of user actions and system events within your Workspace. * [IP Access Control (beta)](/security/ip-access-control): Restrict Workspace access to specific IP addresses or ranges. * [Multi-factor authentication control (beta)](/security/multi-factor-authentication): Require multi-factor authentication for password-based login users. * [**Public Links Monitoring**](#public-links-monitoring): View all published public links, including who created them and when they were shared. ## Public Links Monitoring Workspace Owners can view a complete list of all public links that have been created to share resources from the Workspace. The list includes information about: * Which resource was shared * Who created the public link * When the link was created * Expiration date (if set) This visibility helps maintain security by ensuring all shared content is authorized and appropriate. # Audit logs Monitor user activities and system events across your Workspace Audit logs provide a comprehensive record of actions taken within your Workspace. They help you track who did what and when, making them valuable for security monitoring, compliance requirements, and troubleshooting. ## Key Features * Comprehensive Logging: * Records important user actions and system events (API-based operations) * Provides visibility across your entire Workspace * Consistent Format: * Each log entry includes time, status, IP address, and log details * Log Details: Contains information about: * Operation (e.g., resource creation, update, deletion) * User (includes API key) who performed the action * Affected resource (e.g., notebook, connection, workspace) * Resource-specific properties relevant to the action * Privacy Protection: * Account email addresses are partially masked (only domain portion remains visible) * Current Log Scope: * Logs currently record resource metadata rather than full content * For example, notebook IDs and names are logged, but not the notebook content * Note: The specific data included in logs may change in future updates * Filtering Capabilities: * Filter logs by partial text match * Narrow down results to find specific activities * Retention Period: * Audit logs are retained for 1 year ## Accessing Audit Logs You can access all logs from Workspace Settings > Audit logs. But the audit logs which related to each resource types are accessable from multiple locations: * **Workspace Settings**: * `General` > `Change logs` * `Users` > `Change logs` * `Groups` > `Edit` > `Change logs` * `Teamspaces` > `Edit` > `Change logs` * `API keys` > `Change logs` * `Connections` > `Access level and permissions` > `Edit` > `Change logs` * **Report**: * Each report page > `Report settings` > `logs` * **Notebook**: * Each notebook page > `Share` > `Public link` > `Change logs` ## Audit Log Categories Operations in the audit logs are categorized into six main types to help you understand the nature of activities: * **Workspace Management**: Operations related to workspace administration * **Access Control**: Operations related to permission settings and role assignments * **Data Permission**: Operations involving data access rights and configurations * **View Data**: Operations for data viewing and querying * **Edit Content**: Operations modifying content (in notebooks, manually written content, or files) * **View Content**: Operations involving content viewing without modification ## Audit Log Operation List The following is a partial list of operations that are logged in the audit system. This list may not be exhaustive and is subject to change as new features are added to Codatum: | Resource | Operation | Log Category | | ---------------- | -------------------------------------------- | ----------------------- | | Workspace | Create a workspace | Workspace Management | | | Delete a workspace | Workspace Management | | | Update workspace settings | Workspace Management | | Workspace User | Invite user to workspace | Access Control | | | Delete workspace invitation | Access Control | | | Resend workspace invitation | Access Control | | | Accept workspace invitation | Access Control | | | Decline workspace invitation | Access Control | | | Join workspace via invite link | Access Control | | | Generate workspace invite link | Access Control | | | Join workspace via allowed domain | Access Control | | | Change workspace account role | Access Control | | | Remove user from workspace | Access Control | | | Add permission to user | Access Control | | | Change resource role for user | Access Control | | | Remove permission from user | Access Control | | Group | Create a group | Access Control | | | Add user to group | Access Control | | | Remove user from group | Access Control | | | Delete a group | Access Control | | | Add group owner | Access Control | | | Remove group owner | Access Control | | | Add permission to group | Access Control | | | Change permission role for group | Access Control | | | Remove permission from group | Access Control | | API Key | Create an API key | Access Control | | | Delete an API key | Access Control | | | Retrieve API key secret | Access Control | | | Create API key secret | Access Control | | | Delete API key secret | Access Control | | | Use API key | View Data, View Content | | Connection | Create a connection | Data Permission | | | Delete a connection | Data Permission | | | Change connection access level | Data Permission | | | Add connection owner | Data Permission | | | Remove connection owner | Data Permission | | | Execute query job | View Data | | | Get job metadata | View Data | | | Get job result | View Data | | | Download job result | View Data | | | Cancel query job | View Data | | | Get table metadata | View Data | | | Get table rows | View Data | | Teamspace | Create a teamspace | Access Control | | | Delete a teamspace | Access Control | | | Add teamspace owner | Access Control | | | Remove teamspace owner | Access Control | | Notebook | Create a notebook | Edit Content | | | Delete a notebook | Edit Content | | | Rename a notebook | Edit Content | | | Open a notebook | View Content | | | Update a notebook | Edit Content | | | Duplicate a notebook | Edit Content | | | Restore a notebook from version | Edit Content | | | Move a notebook | Access Control | | | Add notebook share permission | Access Control | | | Change notebook share permission role | Access Control | | | Remove notebook share permission | Access Control | | Notebook Version | Create notebook version | Edit Content | | | Delete notebook version | Edit Content | | File | Upload media file | Edit Content | | | Get media file signed URL | View Content | | Report | View a report | View Data, View Content | | | Publish a report | Data Permission | | | Publish report changes | Data Permission | | | Delete a report | Data Permission | | | Add report share permission | Access Control | | | Remove report share permission | Access Control | | | Share report to guest | Access Control | | | Delete report invitation | Access Control | | | Get job metadata in report | View Data | | | Get job result in report | View Data | | | Get table metadata in report | View Data | | | Get table rows in report | View Data | | | Get media file signed URL on report | View Data | | Public Link | Publish public link notebook | Data Permission | | | Publish public link notebook changes | Data Permission | | | Delete public link notebook | Data Permission | | Signed Embed | Publish signed embed notebook | Data Permission | | | Publish signed embed notebook changes | Data Permission | | | Delete signed embed notebook | Data Permission | | | Update signed embed notebook allowed origins | Access Control | | | Add API key to signed embed notebook | Access Control | | | Remove API key from signed embed notebook | Access Control | | | Add parameter to signed embed notebook | Access Control | | | Remove parameter from signed embed notebook | Access Control | | | Issue token for signed embed notebook | View Data, View Content | | | Open signed embed notebook | View Data, View Content | | | Get media file signed URL on signed embed | View Content | ## Important Notes * Export functionality is not currently available (please contact support if needed) # IP Access Control (beta) Restrict workspace access to specific IP addresses for enhanced security. {% hint style="warning" %} This feature is currently in beta and under development. Specifications may change without notice. {% endhint %} IP Access Control allows you to limit access to your Workspace based on IP addresses. Only users connecting from the specified IP addresses or ranges will be able to access your Workspace. ## How to Set Up * Navigate to `Workspace settings` > `Security` * Check the `Enable IP Access control` checkbox * Enter the allowed IP addresses in the `Allowed IP addresses` input field * Click `Save` to apply the restrictions ## Supported Formats * CIDR notation (e.g., `192.168.1.0/24`) * Single IP addresses (e.g., `203.0.113.15`) * Both IPv4 and IPv6 addresses are supported ## Scope of IP Restrictions IP Access Control affects different types of access in the following ways: * **Workspace Resources**: All resources within your Workspace (...) are subject to the IP restrictions, including: * Notebooks * Queries * Connections * Workspace settings * etc.. * **Reports**: Even reports shared with external Guests are subject to IP restrictions, as they exist within the Workspace. * **Public Link**: Public links shared with internet users are NOT subject to IP restrictions. * **Signed Embed**: Signed Embed content is NOT subject to IP restrictions. ## Important Notes * Changes take effect immediately after saving * Multiple IP addresses or ranges can be saved * To save changes, you must be accessing from at least one of the IP addresses you've specified (this prevents you from accidentally locking yourself out) * If you lose access by setting incorrect IP restrictions, contact Codatum support for assistance # Multi-factor authentication control (beta) You can require multi-factor authentication for users within the workspace. {% hint style="warning" %} This feature is currently in beta and under development. Specifications may change without notice. {% endhint %} Enabling multi-factor authentication control will make multi-factor authentication mandatory for password logins. ## How to Set Up * Navigate to `Workspace settings` > `Security` * Check the `Require Multi-factor authentication` checkbox * To include guest users in multi-factor authentication controls, check the `Include guests` checkbox * Click `Save` to apply the restrictions ## Scope of multi-factor authentication control * If users have selected Google login, they are not subject to multi-factor authentication controls. # Access Control Overview
Codatum has a simple yet powerful role-based access control system. Users can hold roles, such as Viewer or Editor, for a workspace or individual resources (like Notebooks or Connections). Authorizations for various operations are determined based on the combination of roles a user holds. ## **Terminology** * **User** * An account belonging to a Workspace. * **Group** * A group of users that can be managed together and granted multiple Resource roles at once. * **Resource** * Represents various objects within Codatum and their types. * Example: notebook, connection, teamspace, etc. * **Operation** * Various actions that can either be linked to individual resources or to the workspace as a whole. * Example: Inviting users, Editing a notebook, etc. * **Role** * **Workspace roles** * A role assigned to a user within the Workspace, (required for every user). Determines the permissions for workspace-level operations. * Example: Workspace.Owner → Determines the permissions for managing workspace settings and inviting new members. * **Resource roles** * A role linked to individual resources. Can be assigned to groups or individual users. * Example: notebook.Viewer → Determines the permissions for viewing operations on the notebook. * **Permission** * The combination of Resource roles or Workspace roles with users or groups, granting authorization to perform specific operations that are linked to the resource or workspace. ## Access Control Rule Basics Access rights are determined by the combination of roles a user holds, following a defined calculation method. * Roles are hierarchical, so if a lower role has an access right, a higher role will also have that access right. * Both Workspace and Resource roles must allow access for an operation to be permitted (evaluated with logical AND). * If a user has multiple Resource roles for the same resource, either assigned directly or through groups, permission is granted if any one of those Resource roles allow it (evaluated with logical OR). Below is a conceptual diagram illustrating these principles.
This diagram illustrates the pattern with an operation linked to a specific resource A. There is also a pattern with an operation linked solely to the workspace, and not to any specific resource. This pattern is simple, as shown below:
In this pattern, access rights are determined solely by the Workspace role. These principles ensure a robust and flexible access control system within Codatum. # Access Control for Workspace Workspace roles, operations, access control rules.
## Workspace Roles There are four levels of Workspace Roles: | Role Name | Description | Summary | | ---------------- | ------------------------------------------------------------- | --------------------------------------------------------------------------------------------------- | | Workspace Owner | Account that has permissions for managing workspace settings. | Can change workspace settings and invite new members to join the workspace. | | Workspace Editor | Account that has permissions for editing several resources. | Can edit notebooks and create connections. Cannot change workspace settings and invite new members. | | Workspace Viewer | Account that has permissions for viewing several resources. | Can browse notebooks. Cannot edit notebooks or create connections. | | Workspace Guest | Account that has permissions just for Reports. |

Can browse only shared reports.
Guest cannot belong to any group.

| {% hint style="info" %} In Codatum, the terms Owner, Editor, and Viewer are collectively referred to as “Member”. When including the Guest role, the collective term used is “User”. This distinction, while not critical, can be helpful to keep in mind. {% endhint %} ## Workspace Operations and Access Control Rules Here we detail the specific actions that can be executed in the workspace and the necessary roles needed to perform them. | Operations | category | Required Workspace-Role | | -------------------------------------------------------------------------------- | -------------- | ----------------------- | |

Invite and remove users

Change user account roles

| user | Workspace Owner | | View audit logs and user activity history | audit logs | Workspace Owner | | View information about the workspace | workspace info | Workspace Viewer | | View information of all groups | group | Workspace Viewer | |

Create, delete, and edit groups

Add and remove users to/from groups

| group | Workspace Owner | # Access Control for Resources Access control for various resources The roles, operations, and access control rules associated with resources are explained here. There are five types of resources related to access control: Group, Teamspace, Notebook, Connection, and Report. {% content-ref url="/pages/Wpf0F9I0DrSxPxbWNJlq" %} [Access Control for Group](/access-control/access-control-for-resources/access-control-for-group) {% endcontent-ref %} {% content-ref url="/pages/LryKA4uMs1NxLR5suUG1" %} [Access Control for Teamspace](/access-control/access-control-for-resources/access-control-for-teamspace) {% endcontent-ref %} {% content-ref url="/pages/PsaNRWVToM9HwZNMP5u7" %} [Access Control for Notebook](/access-control/access-control-for-resources/access-control-for-notebook) {% endcontent-ref %} {% content-ref url="/pages/0vwqWkbGz7Z0ZvlTJ7o9" %} [Access Control for Connection](/access-control/access-control-for-resources/access-control-for-connection) {% endcontent-ref %} {% content-ref url="/pages/fmRYdpVxmykf3ncFDHcR" %} [Access Control for Report](/access-control/access-control-for-resources/access-control-for-report) {% endcontent-ref %} # Access Control for Group Group roles, operations, access control rules. ## Resource Roles in Groups The following roles determine the operations users can perform in groups. | Role Name | Summary | | ------------ | ----------------------------------------------- | | Group Owner | Can manage groups without workspace owner role. | | Group Member | - | ## Group Operations and Access Control Rules The operations that can be performed to manage groups and the corresponding roles required to perform them are listed here. | Operation | Required Role | | ----------------------------------------------------------------------------------------- | ---------------------------------- | | View a list of groups within the workspace. | Workspace Viewer | | Add a group to the workspace. | Workspace Owner | |

Delete a group.
Edit settings of a group.
Add and remove members to a group.

| "Workspace Owner" OR "Group Owner" | # Access Control for Teamspace Teamspace roles, operations, access control rules. ## Resource Roles for Teamspaces Owner role is different from Editor/Viewer roles and they are not mutually exclusive. When roles overlap, the higher role takes precedence. | Role Name | Summary | | ---------------- | -------------------------------------------------- | | Teamspace Owner | Can manage teamspaces without workspace owner role | | Teamspace Editor | Can edit notebooks and folders in the teamspace | | Teamspace Viewer | Can view notebooks and folders in the teamspace | ## Teamspace Operations and Access Control Rules The operations that can be performed to manage teamspaces and the corresponding roles required to perform them are listed here. | Operations | Required Roles | | --------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------- | | View all teamspace names | Workspace Viewer | | Add a teamspace to the workspace | Workspace Editor | |

Rename a teamspace;
Manage teamspace owners;
Manage teamspace permissions;

|

"Workspace Owner"
OR ("Workspace Viewer"
AND "Teamspace Owner") \*

| \*: This operation can be performed by a Workspace Viewer if the Workspace Viewer is the Teamspace Owner of the respective teamspace. # Access Control for Notebook Notebook access scopes, roles, operations, access control rules. ## Access Scopes for Notebook The access control for notebooks vary depending on the (root) folder of the notebook. There are four types of access scopes for notebooks: | Scope | Description | | --------- | --------------------------------------------------------------------------- | | Workspace | All users in the workspace can access | | Teamspace | Users in the teamspace can access | | Private | Only the owner can access | | Shared | Users and groups who have been granted by additional permissions can access | ## Resource Roles for Notebooks The following roles determine the operations users can perform on notebooks. | Role Name | Summary | | --------------- | ------------------ | | Notebook Editor | Can edit notebooks | | Notebook Viewer | Can view notebooks | ## Notebook Operations and Access Control Rules Below are the operations that can be performed with notebooks and the corresponding roles required for each scope. {% hint style="warning" %} Executing SQL written in a Notebook and retrieving its results are determined by the access rights on the Connection, regardless of the access right on the Notebook itself. Therefore, please refer to the [Access Control for Connection](/access-control/access-control-for-resources/access-control-for-connection). {% endhint %} {% hint style="warning" %} The access rights to notebooks in a teamspace are determined by the access rights granted in the teamspace. For more details, please refer to [Access Control for Teamspace](/access-control/access-control-for-resources/access-control-for-teamspace). {% endhint %}
Operationsscope: Workspacescope: Teamspacescope: Privatescope: Shared

View contents

Comment

Workspace Viewer"Workspace Viewer"
AND "Teamspace Viewer"		Workspace Editor"Workspace Viewer"
AND "Notebook Viewer"
Edit notebooks (exclude running SQL).Workspace Editor"Workspace Editor"
AND "Teamspace Editor"		Workspace Editor"Workspace Editor"
AND "Notebook Editor"

Create, move, delete notebooks

Manage folders

Workspace Editor"Workspace Editor"
AND "Teamspace Editor"		Workspace EditorN/A
Share directly with specific users or groups.N/A"Workspace Viewer"
AND "Teamspace Editor"		Workspace EditorN/A
# Access Control for Connection Connection roles, operations, access control rules. ## Access Levels for Connections The access control for connections vary depending on the access level. There are three types of access levels for connections: | Level | Description | | --------- | ------------------------------------------------------------------------------------------------------------------------ | | Workspace | All users can access. | | Protected |

Only users who have been granted access can access.
Workspace owners can edit permissions.

| | Private |

Only users who have been granted access can access.
Workspace owners cannot edit permissions.

| ## Resource Roles for Connections The following roles determine the operations users can perform on connections. | Role Name | Summary | | ----------------- | ------------------------------------------------------------------------------------------------------------------ | | Connection Owner | Can edit and delete connections and change permission settings | | Connection User |

Can execute queries and view query results and tables
Can publish reports associated with the connection

| | Connection Viewer | Can view query results and tables | ## Connection Operations and Access Control Rules Here are the operations that can be performed on connections and the roles required for each level.
OperationsLevel: "Workspace"Level: "Protected"Level: "Private"
Create a connection.Workspace EditorWorkspace EditorWorkspace Editor
View a list of connection names.Workspace ViewerWorkspace Viewer"Workspace Viewer"
AND "Connection Viewer"
Edit and delete a connection."Workspace Owner"
OR "Connection Owner"		"Workspace Owner"
OR ("Workspace Viewer"
AND "Connection Owner")		"Workspace Viewer"
AND "Connection Owner"
Change permission settings of a connection."Workspace Owner"
OR "Connection Owner"		"Workspace Owner"
OR ("Workspace Viewer"
AND "Connection Owner")		"Workspace Editor"
AND "Connection Owner"
Execute any SQL
Download SQL job results		"Workspace Editor""Workspace Editor"
AND "Connection User"		"Workspace Editor"
AND "Connection User"
Get SQL job results and table information."Workspace Viewer""Workspace Viewer"
AND "Connection Viewer"		"Workspace Viewer"
AND "Connection Viewer"
# Access Control for Report Report roles, operations, access control rules. ## Resource Roles for Reports Reports are read-only documents, so there is only a view permission. Also, executing SQL and retrieving the results of the execution are determined by the report's permission, not the connection's permission. | Role name | Summary | | ------------- | ----------------------------------------------------------------------------------------- | | Report Viewer | Can view the report and refresh the query results if the report allows refresh execution. | ## Report Operations and Access Control Rules | Operations | Required Roles | | ----------------------------------------- | -------------- | |

View report contents

Comment

| Report Viewer | Operations related to publishing a report are influenced by the access scope of the original notebook from which the report is created.
Operationsscope: Workspacescope: Teamspacescope: Privatescope: Shared

Publish reports

Change permissions of reports

"Workspace Editor"
AND "Connection User" *		"Teamspace Editor"
AND "Connection User" *		"Workspace Editor"
AND "Connection User" *		N/A
Delete reports.Workspace EditorTeamspace EditorWorkspace EditorN/A
Edit report settings.Workspace EditorTeamspace EditorWorkspace EditorN/A
\*: The `Connection User` role is required for all connections within the publishing pages of the report. # Advanced Control Structures in SQL Learn how to implement template engine-like functionality securely using Codatum's parameter features There are situations where you might want to branch or repeat processes based on parameter input or reuse the same process functionally. In such cases, many SQL tools use template engines like Jinja to generate dynamic SQL queries. However, in Codatum, security concerns such as SQL Injection are prioritized, and template engines cannot be used. By maintaining syntax parsing, Codatum ensures: * Accurate escaping based on context, reducing security risks * Easier debugging by pinpointing the exact location of errors * Context-based suggestions for improved usability Most of these cases can be addressed by utilizing standard SQL syntax. This article introduces methods for implementing advanced control structures in Codatum. Before reading this section, please read the [Parameter](/data-exploration/parameter-overview) and [Escape and Dynamic Reference](/data-exploration/parameter-overview/escape-and-dynamic-reference) sections. {% hint style="info" %} **Note:** The Template Engine examples in this article are based on the syntax of the Jinja2/Jinja template language. Actual tools may use similar but different syntax or proprietary extensions. {% endhint %} ## 1. Conditional Data Access (If/Else) Controls which fields or data to SELECT based on parameters. ### Use Cases * Apply different business logic by region/country * Change data aggregation granularity dynamically * Restrict data access based on user permission levels * Provide different data views by department/team ### Codatum Approach Use CASE expressions or UNION with parameters: ```sql -- Region-specific business logic example SELECT order_id, CASE {{region}} WHEN 'EU' THEN amount * 1.20 -- EU: 20% VAT ELSE amount END as final_price, CASE {{region}} WHEN 'EU' THEN 'EU' ELSE 'Other' END as tax_region FROM orders -- Aggregation granularity example SELECT CASE {{granularity}} WHEN 'daily' THEN DATE_TRUNC('day', timestamp) WHEN 'weekly' THEN DATE_TRUNC('week', timestamp) ELSE DATE_TRUNC('month', timestamp) END as period FROM events -- Permission-based restriction example SELECT * FROM ( SELECT sensitive_column as data FROM sensitive_data WHERE {{is_admin}} = TRUE UNION ALL SELECT public_column as data FROM filtered_data WHERE {{is_admin}} = FALSE ) t ``` ### Template Engine Approach (not in Codatum) For reference, this would be implemented in a template engine (Jinja2) as: ```sql -- Region-specific business logic example {% raw %} {% if region == 'EU' %} SELECT order_id, amount * 1.20 as price_with_vat, -- EU: 20% VAT 'EU' as tax_region FROM orders {% else %} SELECT order_id, amount as price_without_vat, 'Other' as tax_region FROM orders {% endif %} -- Aggregation granularity example SELECT {% if granularity == 'daily' %} DATE_TRUNC('day', timestamp) as period {% elif granularity == 'weekly' %} DATE_TRUNC('week', timestamp) as period {% else %} DATE_TRUNC('month', timestamp) as period {% endif %} FROM events -- Permission-based restriction example {% if user.is_admin %} SELECT * FROM sensitive_data {% else %} SELECT public_columns FROM filtered_data {% endif %} {% endraw %} ``` ## 2. Optional Filter Conditions (If/Else) Controls which conditions to include in WHERE clause based on parameters. ### Use Cases * Allow users to combine search conditions flexibly * Change report filters dynamically * Apply filters only when specific conditions are provided ### Codatum Approach Use empty parameter handling: ```sql SELECT * FROM orders WHERE 1=1 AND (order_date >= {{start_date}} OR {{start_date}} = '1970-01-01') AND (status = {{status}} OR {{status}} = '') ``` ### Template Engine Approach (not in Codatum) For reference, this would be implemented in a template engine (Jinja2) as: ```sql SELECT * FROM orders WHERE 1=1 {% raw %} {% if start_date %} AND order_date >= {{start_date}} {% endif %} {% if status %} AND status = {{status}} {% endif %} {% endraw %} ``` ## 3. Repeat by Multi Parameters (for Loop) Apply repetition patterns using Multi Input/Select parameters. ### Use Cases * Aggregate multiple metrics simultaneously * Display only user-selected metrics * Apply similar calculation logic to multiple columns ### Codatum Approach Use Text Multi Input parameter with CASE expressions to control which metrics to aggregate: ```sql SELECT SUM(CASE WHEN 'revenue' IN ({{selected_metrics}}) THEN revenue ELSE NULL END) as revenue_total, SUM(CASE WHEN 'cost' IN ({{selected_metrics}}) THEN cost ELSE NULL END) as cost_total, SUM(CASE WHEN 'profit' IN ({{selected_metrics}}) THEN profit ELSE NULL END) as profit_total FROM sales ``` While this approach requires more code than template engines, it provides better reliability and maintainability: * Each metric is explicitly defined, making it easier to detect errors at compile time * Column names and types can be validated by the SQL engine * Changes to table schema are less likely to break the query ### Template Engine Approach (not in Codatum) For reference, this would be implemented in a template engine (Jinja2) as: ```sql SELECT {% raw %} {% for metric in metrics %} SUM({{metric}}) as {{metric}}_total, {% endfor %} {% endraw %} FROM sales ``` ## 4. Dynamic Identifier Generation Controls which table to query based on parameters. ### Use Cases * Select tables by API key * Access date-partitioned tables * Generate schema or table names dynamically ### Codatum Approach Use quoted parameters: ```sql -- API key-based table selection SELECT * FROM `api_logs_{{api_key_suffix}}` -- e.g., api_logs_v1, api_logs_v2 -- Date-partitioned table access SELECT * FROM `events_{{date_suffix}}` -- e.g., events_20240131 FROM `sales_{{region}}_{{date_suffix}}` -- e.g., sales_us_20240131 -- Dynamic column names SELECT "{{metric_name}}" as metric, -- e.g., revenue_usd, revenue_eur "amount_{{currency}}" as amount -- e.g., amount_usd, amount_eur FROM transactions ``` Note: In BigQuery connections, date parameters in table names are automatically formatted as `YYYYMMDD`. ### Template Engine Approach (not in Codatum) For reference, this would be implemented in a template engine (Jinja2) as: ```sql -- API key-based table selection SELECT * FROM `api_logs_{{api_version}}` -- Date-partitioned table access SELECT * FROM `events_{{date.strftime('%Y%m%d')}}` -- Dynamic column names SELECT {{metric_name}} as metric, amount_{{currency}} as amount FROM transactions ``` ## 5. Macro/Function Reuse Encapsulates reusable calculation logic into functions. ### Use Cases * Standardize domain-specific calculation logic * Reuse calculation logic across multiple queries * Encapsulate complex conditional calculations ### Codatum Approach (Using BigQuery UDF) ```sql -- LTV calculation function CREATE TEMP FUNCTION calculate_ltv(revenue FLOAT64, acquisition_cost FLOAT64, retention_months INT64) RETURNS FLOAT64 AS ( CASE WHEN retention_months = 0 THEN 0.0 ELSE (revenue - acquisition_cost) * POWER(0.95, retention_months) END ); SELECT user_id, calculate_ltv(total_revenue, acquisition_cost, months_active) as ltv, FROM user_metrics ``` {% hint style="info" %} UDFs are database-specific features, and notation and capabilities differ between databases. {% endhint %} ### Template Engine Approach (not in Codatum) For reference, this would be implemented in a template engine (Jinja2) as: ```sql {% raw %} {% macro calculate_ltv(revenue, acquisition_cost, retention_months) %} CASE WHEN {{retention_months}} = 0 THEN 0 ELSE ({{revenue}} - {{acquisition_cost}}) * POWER(0.95, {{retention_months}}) -- Apply 5% decay rate END {% endmacro %} {% endraw %} SELECT user_id, {{ calculate_ltv('total_revenue', 'acquisition_cost', 'months_active') }} as ltv FROM user_metrics ``` ## Benefits of Codatum's Approach While template engines offer more flexibility, Codatum's parameter-based approach provides: 1. SQL injection protection through proper escaping 2. Maintainable SQL structure 3. Better query analysis and optimization 4. Type safety for parameters 5. Clear separation between dynamic values and SQL structure When template engine functionality is required, consider: * Restructuring queries to use standard SQL features * Breaking complex queries into multiple simpler queries * Using CTEs for query organization * Leveraging database-specific features when available # AI Generated Summary Learn how to add AI-Generated Summaries useing Codatum's Text Panel feature and Vertex AI. The text summary accompanying the dashboard helps users understand the data. However, summaries written by humans cannot reflect user actions such as parameter specifications. This article introduces how to use Vertex AI via BigQuery to generate summaries based on data specified by users using parameters. This can also be achieved by using AI functions that support connections other than BigQuery. Codatum has a Text Panel for displaying text in Markdown notation, so here we will use the Text Panel to display the chart. ## How to Set up * Setting up Vertex AI * Model creation * Transferring aggregated data to AI models * Select the Text Panel chart and display the data Following the above steps, you can add summaries that change each time the user modifies the parameters and executes the query. ## Implementation Example * Use the Chicago Taxi Trip public data from BigQuery * Generate a summary each time the data target is changed via parameters, and create a summary corresponding to the displayed data ### Model Creation * This task can also be performed using the BigQuery query editor ```sql CREATE OR REPLACE MODEL `codatum-example.example.vertex_ai` REMOTE WITH CONNECTION `us.vertex_ai_test` OPTIONS( ENDPOINT = 'gemini-2.0-flash' ) ``` After completing the above query, the `vertex_ai` model will be added to the `example` dataset in BigQuery. ### Data Aggregation * Aggregate the number of rides, ride time, ride distance, and ride fare by company and payment method on a monthly basis * Set company name and payment method to be changeable in [parameters](/data-exploration/parameter-overview) ```sql SELECT DATE_TRUNC(trip_start_timestamp, month) as month, company, payment_type, COUNT(distinct unique_key) as trip_count, SUM(trip_seconds) / 60 as total_trip_min, AVG(trip_seconds) / 60 as average_trip_min, MAX(trip_seconds) / 60 as max_trip_min, MIN(trip_seconds) / 60 as min_trip_min, SUM(trip_miles) as total_trip_miles, AVG(trip_miles) as average_trip_miles, MAX(trip_miles) as max_trip_miles, MIN(trip_miles) as min_trip_miles, SUM(fare) as total_fare, AVG(fare) as average_fare, MAX(fare) as max_fare, MIN(fare) as min_fare FROM `bigquery-public-data.chicago_taxi_trips.taxi_trips` WHERE trip_start_timestamp >= '2017-01-01' AND trip_start_timestamp < '2018-01-01' -- Data from 2017 --outlier exclusion AND trip_seconds != 0 AND fare != 0 AND trip_miles != 0 AND trip_seconds < 21600 AND CASE {Company} WHEN '' THEN TRUE ELSE company = {Company} END AND CASE {PaymentType} WHEN '' THEN TRUE ELSE payment_type = {PaymentType} END GROUP BY DATE_TRUNC(trip_start_timestamp, month), company, payment_type ORDER BY month ``` ### Transferring data to AI models Codatum allows you to reference the results of other SQL blocks, so you can manage SQL blocks that aggregate data and SQL blocks that transfer data separately. * Convert aggregated data to JSON format ```sql SELECT TO_JSON_STRING(STRUCT( average_fare,average_trip_miles,average_trip_min,company,max_fare,max_trip_miles,max_trip_min,min_fare,min_trip_miles,min_trip_min,month,payment_type,total_fare,total_trip_miles,total_trip_min,trip_count )) as input_text FROM Monthly_basis_summaries ``` * Combine JSON data for each row into a single JSON object ```sql SELECT CONCAT('[', STRING_AGG(input_text, ','), ']') AS all_input_texts FROM JSON_formatted_summaries ``` * Pass the combined JSON data to the AI model * Combine the prompt and JSON data and pass them to the created model ```sql SELECT JSON_VALUE(ml_generate_text_result.candidates[0].content.parts[0].text) as output, FROM ML.GENERATE_TEXT( MODEL `codatum-example.example.vertex_ai`, ( SELECT CONCAT( 'The following data is a summary of monthly taxi ride data by company and payment type.', 'Summarize the trends by payment method and month, and create a report in Markdown format.', 'After explaining the overview of the data, such as the total number of rides and the month with the most rides, explain the distinctive items in separate sections.' 'There is no need to enclose the entire Markdown.', 'Please use various Markdown elements.', 'Please ensure that the generated summary is complete. No further editing of the summary will be required. However, additional analysis suggestions are possible.', 'Since code execution is not possible within the summary, please do not embed any code.' 'Payment methods for the taxi ride data in question is ', CASE WHEN {PaymentType} = '' THEN 'All' ELSE {PaymentType} END,', ', 'company is ', CASE WHEN {Company} = '' THEN 'all' ELSE {Company} END, '. Input data：', all_input_texts ) as prompt, *, FROM All_JSON_formatted_summaries ), STRUCT( 0.3 AS temperature, 1000 AS max_output_tokens) ) ``` * The stability of the summary varies depending on the content of the prompt given to the model, so please try different approaches. ### Adding a Text Panel chart * Select `Text Panel` from the chart type selection and add it * The text panel displays the value in the first row, so if there are multiple text data, only one will be displayed ## Notes * If Markdown is enclosed, it cannot be expanded properly in the text panel, so please ensure that the AI output does not enclose Markdown # Setting Up Vertex AI Setting up Vertex AI on Goolge Cloud Platform ## How to Set Up Perform the following operations in the BigQuery console: * Add an External connections * Add `Vertex AI > BigQuery federation` from Add data in Explorer * Add access credentials to the External connection * Grant access permissions from IAM and admin * Enter the Service account ID of the added External connection in Add principals * Assign the `Vertex AI User` role * Add the following role to the Codatum connection: * `BigQuery Connection User` * There are two ways to add: * Add a principal to the External conneciton * Enter the Service account used for the Codatum connection in Add principals * Add a role to the Service account set for the Codatum connection * Create a dataset * Create a dataset to register the model * (Optional) Add a model * Add a model from the BigQuery query editor. Reference:： * You can add models on Codatum # System requirements ## Recommended Environment ### Operating Systems * **Windows** (latest version) * **macOS** (latest version) ### Browsers * **Windows**: Google Chrome, Microsoft Edge (latest versions) * **macOS**: Google Chrome, Safari (latest versions) ## Notes * **Impact of Browser Extensions**\ Certain browser extensions may affect the performance of the Codatum notebook. If you encounter issues, please try disabling extensions. * **Known extensions causing issues** * [DeepL Translator (Chrome Extension)](https://chromewebstore.google.com/detail/deepl-ai-translator-and-w/cofdbpoegempjloogbagkncekinflcnj) * [Microsoft Translator (Edge Add-on)](https://microsoftedge.microsoft.com/addons/detail/microsoft-translator-bui/fbhhdpcmomckhopgphnkegobjdggdfhe)