Write-back capabilities have transformed the way we interact with data in BI tools, ushering in dynamic changes and real-time analytics. These features not only enhance decision-making but also empower users to directly influence datasets. However, as with any powerful tool, there's a learning curve. From my experience delivering successful write-back products across various BI tools, I've observed that a primary challenge users face is comprehending the data model. Specifically, they often grapple with designing the write-back tables to seamlessly integrate with existing data.
In this blog series, I'll navigate you through prevalent use cases and guide you on structuring the optimal data model for each scenario. Let's kick things off by delving into a foundational task: incorporating commentary into an application.
For those wishing to follow along, it's essential to have an intermediate grasp of BI tools. Ideally, you should be adept at creating tables in Snowflake and importing them into Astrato. While prior experience crafting dashboards in Astrato is a plus, it's not a strict prerequisite. I'll do my best to guide you through every step of the process.
Basic commentary
In the business landscape, the ability to annotate and comment directly on data visualizations or reports is invaluable. Imagine a sales dashboard where regional managers can directly comment on monthly sales figures, providing context like local events or marketing campaigns that influenced those numbers. Such direct commentary enhances understanding and fosters collaborative insights.
For those just starting with data write-backs, creating a basic commentary feature is a pivotal first step. Using Snowflake, a leading data warehousing service, we'll lay the foundation for this interactive commentary structure.
Step 1: Setting up the Table
Begin by creating a table in Snowflake named COMMENTS_WRITE_BACK in Snowflake:
CREATE OR REPLACE TABLE COMMENTS_WRITE_BACK (
ID INT IDENTITY(1,1),
COMMENT VARCHAR,
CREATED_BY VARCHAR(320),
CREATED_AT timestamp default current_timestamp()
);
Column | Description |
ID | It's a standard practice to include an ID column in your write-backs. Though not mandatory for this scenario, having an ID is beneficial for potential future enhancements. Using IDENTITY(1,1), the ID starts at 1 and auto-increments by 1 for each new record. When performing a write-back, exclude the ID so that Snowflake can automatically append the next number in the sequence. |
COMMENT | This column stores the text of the user's comment. |
CREATED_BY | This column captures the user ID of the person submitting the comment. |
CREATED_AT | By leveraging the current_timestamp() function as its default value, this column will automatically capture the timestamp of when the comment was created. Like the ID, during write-back, exclude the CREATED_AT to let Snowflake populate the precise timestamp of the action. |
Step 2: Setting up Table Permissions
When setting up write-back tables, one of the pivotal considerations is permissions. A key thing to remember is that permissions are primarily managed in the data source (in this case, Snowflake), not in Astrato. So, who can insert data? How do you ensure that only specific users or roles have write-back capabilities? Addressing these concerns, Snowflake provides a granular system to manage permissions.
For those wanting to grant INSERT privileges at the schema level to all tables, the following SQL command serves that purpose:
-- Grant INSERT privileges to users with the ROLE analyst to all tables in schema
GRANT INSERT ON ALL TABLES IN WRITE_BACK_101 TO analyst;
On the other hand, if you want to be more specific and grant INSERT privileges only to the COMMENTS_WRITE_BACK table within the WRITE_BACK_101 schema, use this command:
-- Grant INSERT privileges to users with the ROLE analyst to COMMENTS_WRITE_BACK table in schema WRITE_BACK_101
GRANT INSERT ON TABLE COMMENTS_WRITE_BACK IN WRITE_BACK_101 TO analyst;
For a comprehensive understanding of permissions, roles, and the various privileges available in Snowflake, I highly recommend referring to the official Snowflake documentation on permissions: https://docs.snowflake.com/en/sql-reference/sql/grant-privilege
Step 3: Integrating with Astrato Workbook
After adding the table to your Astrato workbook's data model, proceed to create the following components:
A table to display comments.
A text-area input for inputting new comments.
A button to submit comments.
Creating Variables
Before storing them in the data source with write-back, we must first create a holding area. We do this by setting up a text variable named Comment.
Link this variable to your text-area input so that it captures and retains the commentary. Once complete, your workbook's layout should look something like the picture below..
Using Actions
To effectively link together the various components of our workbook, we'll integrate logic with the controls, inputs, and visualizations. In Astrato, this integration is orchestrated through the "Actions" feature. To initiate this process, follow these steps:
Accessing the Actions Panel:
There are two ways to access Actions in Astrato.
On the left toolbar, locate and click on the "Actions" button, which is positioned at the bottom of the available buttons.
Alternatively, you can also access the Actions panel via any control object's property panel—specifically at the bottom of the data tab.
Configuring the Logic
Once the actions panel appears in full screen, you'll have the workspace to implement the logic that facilitates comment write-backs upon clicking the 'Add comment' button.
Setting Up Action Blocks
Now, let's start building our action sequence. The table below illustrates the action blocks needed. Kick off the process by dragging over a When control block.
When Button emits On Click do |
Purpose | Write-back comments |
Actions Used | Writeback; Controls, Conditions, Operators; Variables; General; Data |
|
To see your workbook in action, hit the "Preview" button. Enter a comment in the text-area and then click the "Add comment" button. The animated gif below demonstrates this process using the workbook we've built.
It's crucial to understand that actions, including write-backs, are not visible or executable directly within the editor. This distinction might be overlooked, so always ensure you're in "Preview" mode to fully experience and validate your workbook's interactive capabilities.
Congratulations! You've successfully set up your first write-back workbook. In the next part of this article series we will delve deeper and explore more advanced functionalities.