With Translytical task flows you can enable datapoint annotation directly within your report.
For example, in the report below you can add, edit or delete annotations about each month’s sales data. Here we can see that to add a new data annotation, you select the datapoint, input your comment, and then submit, and it appears immediately on the report.
Data annotation scenarios may consist of up to three user data functions to:
- Add Annotation: Add a new datapoint annotation
- Edit Annotation: Update an exisiting annotation
- Delete Annotation: Delete a specific annotation
Here is the user data function for the Add Annotation scenario:
import fabric.functions as fn
import logging
import calendar
udf = fn.UserDataFunctions()
@udf.connection(argName="sqlDB",alias="Translytical")
@udf.function()
def AddAnnotation(sqlDB: fn.FabricSqlConnection, date: str, commentdate: str, comment: str, user: str) -> str:
logging.info('Python UDF trigger function processed a request.')
# month abbr to YYYY-MM-DD
month_num = list(calendar.month_abbr).index(date)
formatted_date = f"{2023}-{month_num:02d}-10"
data = (commentdate, formatted_date, user, comment)
# Establish a connection to the SQL database
connection = sqlDB.connect()
cursor = connection.cursor()
logging.info("Adding comment ... ")
# Insert data into the table
insert_query = "INSERT INTO [dbo].[DataReasoning] ([Date_Created],[Date_Month],[User],[Comment]) VALUES (?, ?, ?, ?);"
cursor.execute(insert_query, data)
logging.info("Comment was added")
# Commit the transaction
connection.commit()
# Close the connection
cursor.close()
connection.close()
return "Comment was successfully added"
Here is the user data function for the Edit Annotation scenario:
import fabric.functions as fn
import logging
import calendar
udf = fn.UserDataFunctions()
@udf.connection(argName="sqlDB",alias="Translytical")
@udf.function()
def EditAnnotation(sqlDB: fn.FabricSqlConnection, comment: str, commentdate: str, user: str, newcomment :str ) -> str:
logging.info('Python UDF trigger function processed a request.')
data = (newcomment, commentdate, user, comment)
# Establish a connection to the SQL database
connection = sqlDB.connect()
cursor = connection.cursor()
# Insert data into the table
logging.info("Updating comment")
update_query = " UPDATE [dbo].[DataReasoning] SET [Comment] = ?, [Date_Created] = ?, [User] = ? WHERE [Comment] = ?;"
cursor.execute(update_query, data)
logging.info("Comment was updated")
# Commit the transaction
connection.commit()
# Close the connection
cursor.close()
connection.close()
return "Comment was successfully updated"
Here is the user data function for the Delete Annotation scenario:
import fabric.functions as fn
import logging
udf = fn.UserDataFunctions()
@udf.connection(argName="sqlDB",alias="Translytical")
@udf.function()
def DeleteAnnotation(sqlDB: fn.FabricSqlConnection, comment: str ) -> str:
logging.info('Python UDF trigger function processed a request.')
# Establish a connection to the SQL database
connection = sqlDB.connect()
cursor = connection.cursor()
# Delete comment
logging.info("Deleting comment ... ")
delete_query = "DELETE FROM [dbo].[DataReasoning] WHERE [Comment] = ?"
cursor.execute(delete_query,comment)
logging.info("Comment was deleted")
# Commit the transaction
connection.commit()
# Close the connection
cursor.close()
connection.close()
return "Comment was successfully deleted"
Feel free to use this code as inspiration for your data annotations scenarios!
