Accounting Process Flow Automation by Translytical Flow
Sales Commission Process in a Marketing Company In an organization offering marketing solutions to lending institutions, the commission process for a sales order was complex, relying on shared spreadsheets with inputs and approvals from multiple departments. Implementing a live dashboard with a translytical flow streamlined the process, reducing the workflow time from 10 days to 2 days. Commissions are paid based on: # of sales appointments Other data from Zohobooks Approval from CFO Final Processing of Payment by finance/accountant With a set of dash boards powered by an SQL database with translytical functions , we made the recording of sales appointments and approvals at multiple steps and the subsequent sales commission payment kickoff and notification seamless. Direct query is used for quick reflection of the write backs (apointment times and apporval status changes). User data functions are called from power BI buttons to achieve this. Power automate flow is used to send email notifications which is invoked by a button click from dashbaord. The users were most thrilled because they could see the commission outflow immediately reflected in the Cashflow forecasts. Process Flow: Sales appointments enetered by respective sales people ->Approval from CFO ->Processing of sales commission payment by accountant and sending email notifications User Data Function used given here: import fabric.functions as fn import logging from datetime import datetime udf = fn.UserDataFunctions() @udf.connection(argName="sqlDB1", alias="TWFDB") @udf.function() def create_appointment( sqlDB1: fn.FabricSqlConnection, fullname: str, startdate: str, starttime: str, durationminutes: int ) -> str: if sqlDB1 is None: raise fn.UserThrownError("Connection injection failed. Check alias 'TWFDB' in UDF connections.") # Validate / normalize if not fullname or fullname.strip() == "": raise fn.UserThrownError("Full name is required.") try: dur = int(durationminutes) except ValueError: raise fn.UserThrownError("Duration must be a whole number of minutes.") if dur <= 0: raise fn.UserThrownError("Duration must be > 0.") t = (starttime or "").strip() or "09:00:00" if t.count(":") == 1: t += ":00" elif t.count(":") != 2: raise fn.UserThrownError("Time must be HH:mm or HH:mm:ss.") try: start_datetime = datetime.strptime(f"{startdate} {t}", "%Y-%m-%d %H:%M:%S") except ValueError: raise fn.UserThrownError("Invalid date or time format. Expected YYYY-MM-DD and HH:mm[:ss].") created_date = datetime.now() conn = cur = None try: conn = sqlDB1.connect() conn.autocommit = False cur = conn.cursor() # Upsert salesperson cur.execute("SELECT SalespersonID FROM dbo.Salesperson WHERE FullName = ?", (fullname,)) row = cur.fetchone() if row: sp_id = int(row[0]) else: try: cur.execute( "INSERT INTO dbo.Salesperson (FullName) OUTPUT INSERTED.SalespersonID VALUES (?)", (fullname,) ) sp_id = int(cur.fetchone()[0]) except Exception: cur.execute("SELECT SalespersonID FROM dbo.Salesperson WHERE FullName = ?", (fullname,)) sp_id = int(cur.fetchone()[0]) statusvar = "Entry" # Insert appointment cur.execute( """ INSERT INTO dbo.Appointment (SalespersonID, StartDate, DurationMinutes, Status, CreatedDate) OUTPUT INSERTED.AppointmentID VALUES (?, ?, ?, ?, ?) """, (sp_id, start_datetime, dur, statusvar, created_date) ) appt_id = int(cur.fetchone()[0]) conn.commit() return f"✅ Appointment {appt_id} created for {fullname} at {start_datetime} ({dur} min)." except fn.UserThrownError: raise except Exception as e: logging.exception("create_appointment failed") try: if conn: conn.rollback() except: pass raise fn.UserThrownError(f"❌ Failed to create appointment: {str(e)}") finally: try: if cur: cur.close() except: pass try: if conn: conn.close() except: pass @udf.connection(argName="sqlDB1", alias="TWFDB") @udf.function() def approve_appointment_by_cfo( sqlDB1: fn.FabricSqlConnection, appointmentid: int ) -> str: if sqlDB1 is None: raise fn.UserThrownError("Connection injection failed. Check alias 'TWFDB' in UDF connections.") conn = cur = None try: conn = sqlDB1.connect() conn.autocommit = False cur = conn.cursor() # Check if appointment exists cur.execute("SELECT AppointmentID FROM dbo.Appointment WHERE AppointmentID = ?", (appointmentid,)) if not cur.fetchone(): raise fn.UserThrownError(f"No appointment found with ID {appointmentid}.") # Update status to CFOApproved cur.execute( """ UPDATE dbo.Appointment SET Status = 'CFOApproved' WHERE AppointmentID = ? """, (appointmentid,) ) conn.commit() return f"✅ Appointment {appointmentid} status updated to CFOApproved." except fn.UserThrownError: raise except Exception as e: logging.exception("approve_appointment_by_cfo failed") try: if conn: conn.rollback() except: pass raise fn.UserThrownError(f"❌ Failed to update appointment: {str(e)}") finally: try: if cur: cur.close() except: pass try: if conn: conn.close() except: passCustom AI integration
You can enable custom-tailored AI assistance directly on your report or kick off AI-powered custom workflows. Example, you can integrate Azure OpenAI into your report to help plan your next proposal while factoring in context from the report. You simply select the influencer and click the ‘Generate AI Suggestion’ button, which runs a Fabric User data function that instantly provides an Azure OpenAI response based on a fully customizable prompt. Here is the Python code for the user data function that powers this scenario: import fabric.functions as fn import logging import openai udf = fn.UserDataFunctions() @udf.connection(argName="sqlDB",alias="Translytical") @udf.function() def AISuggestion(sqlDB: fn.FabricSqlConnection, company: str) -> str : logging.info('Python UDF trigger function processed a request.') # Establish a connection to the SQL database connection = sqlDB.connect() cursor = connection.cursor() #Get offer status for the company SQL_read_Command = "SELECT * FROM [dbo].[CompanyStatus] WHERE Company = ?" cursor.execute(SQL_read_Command, company) record = cursor.fetchone() customer_name = record[0] last_comment = record[10] #Submit prompt to Azure OpenAI and get an AI suggestion prompt = "Respond with a short plan that is under 240 characters: I work at Contoso Outdoors, and we collaborate with influencers by offering them offers for custom designed bikes. Pretend we want to collab with the following influencer: " + customer_name +" from company: " + company + ". Here's a comment about their latest feedback " + last_comment + "." deployment = "gpt-4o" openai_client = openai.AzureOpenAI( api_key='<API Key here>', api_version = "2025-01-01-preview", azure_endpoint = "https://sico-oai-eus2.openai.azure.com/openai/deployments/gpt-4o/chat/completions?api-version=2025-01-01-preview" ) response = openai_client.chat.completions.create( model=deployment, messages=[ {"role": "user", "content": prompt} ] ) result = response.choices[0].message.content #Check if there is an exisiting AI suggestion for the company SQL_read_Command = "SELECT * FROM [dbo].[AISuggestions] WHERE Company = ?" cursor.execute(SQL_read_Command, company) if cursor.fetchone(): #if there is an existing AI suggestion record for the company, update just the AI suggestion SQL_update_command = "UPDATE [dbo].[AISuggestions] SET [AI_suggestion] = ? WHERE [Company] = ?;" Existing_Suggestion = (result, company) cursor.execute(SQL_update_command, Existing_Suggestion) else: #if there is NOT an existing AI suggestion record for the company, add a new record SQL_insert_command = "INSERT INTO [dbo].[AISuggestions](Name, Company, AI_suggestion) VALUES(?, ?, ?);" New_Suggestion = (customer_name, company, result) cursor.execute(SQL_insert_command, New_Suggestion) # Commit the transaction connection.commit() # Close the connection cursor.close() connection.close() return f"Generated Azure OpenAI suggestion for collaboration ideas with " + customer_name + " from " + company + "." Feel free to use this code as inspiration for your own custom AI integration scenarios!Intelliventra – Smart Event Management Application
Intelliventra lets participants easily browse all sessions and add them to their personal agenda. Attendees can view session details such as time, room, track, and speaker. The page also allows attendees to add,remove sessions. It is designed to be simple, clear, and works for any event, from small workshops to large conferences.Handling Multi-Row Inputs for Translytical Task Flows
This post offers a practical guide to passing multiple rows of data from a Power BI report to a User-Defined Function (UDF) using translytical task flows. The solution leverages the TOJSON() DAX function to overcome the limitation of scalar-only inputs from slicers. Translytical Task Flows At their core translytical task flows allow buttons within your Power BI reports to trigger a UDF. Within the button definition you map a button, list, or text slicer; a data field; or measures from the report to the input parameters of the UDF. However, a key limitation is that when using a slicer, you can only pass a single (scalar) value. If a user selects multiple items, the button becomes inactive, preventing the UDF from being called. But with a measures, you are able to pass tabular data a string, with the help of the seldomly used DAX function TOJSON(). Sudoku This example uses a Sudoku game built with Power BI to demonstrate the solution. The game state is stored in a Fabric SQL database, and UDFs are used to manage game actions. The game UI consists of several components: Game Selection: A slicer to select a game template. Game Grid: A button slicer representing the 81 cells of the Sudoku board. Each button uses a DAX-generated SVG to display numbers and pencil marks. Number Selector: A button slicer for selecting the number to enter (1-9). Pen/Pencil Buttons: Buttons that trigger UDFs to enter numbers or pencil marks into the selected cell(s). The architecture is as follow: The Pen and Pencil buttons are mapped to the position from the grid button slicer and the value from the value selector button slicer. This works perfectly for a single cell selection. Generally in Sudoku, you want to mark two or more cells simultaneously when entering pencil marks. However when we select more than one cell, the Pen and Pencil buttons becomes inactive: Using TOJSON() to Pass Tabular Data To enable multi-cell input, we need a method to generate a single string that encapsulates information about multiple selected positions. For those familiar with REST APIs, calling the UDF in this context is analogous to a POST request, where the JSON format is the preferred payload. Fortunately, DAX provides a perfect solution: the built-in TOJSON() function. This function generates a JSON string from a given table. We can create a DAX measure that generates a JSON string containing all the selected grid positions. Selected Positions = IF( ISFILTERED( 'Grid Position'[Position] ), TOJSON( VALUES( 'Grid Position'[Position] ) ) ) This provides the following output: We can update the mapping from the position provided by the grid button slicer, to the DAX measure: Handling the JSON in the UDF The Python code in the UDF can then parse this string back into a list of positions to be processed. import fabric.functions as fn import json from datetime import datetime udf = fn.UserDataFunctions() @udf.connection(argName="sqlDB", alias="Game") @udf.function() def toggle_pencil_mark(sqlDB: fn.FabricSqlConnection, gameId: int, position: str, pencilValue: int) -> str: """ Toggle a pencil mark in a Sudoku cell Args: gameId: The game ID position: JSON string with cell positions pencilValue: Pencil mark value (1-9) Returns: JSON string with operation result """ # Parse positions from JSON positions_data = json.loads(position) positions = [pos[0] for pos in positions_data["data"]] ## Open connection and SQL database and write values to cells conn = sqlDB.connect() cursor = conn.cursor() processed_positions = [] last_action_type = None last_is_active = False for pos in positions: # Skip cells with values cursor.execute("SELECT CurrentValue FROM GameState WHERE GameId = ? AND Position = ?", (gameId, pos)) if cursor.fetchone()[0] is not None: continue # Toggle pencil mark cursor.execute("EXEC sp_TogglePencilMark ?, ?, ?", (gameId, pos, pencilValue)) conn.commit() # Check if added or removed cursor.execute("SELECT IsActive FROM PencilMarks WHERE GameId = ? AND Position = ? AND PencilValue = ?", (gameId, pos, pencilValue)) is_active = cursor.fetchone()[0] last_action_type = "added" if is_active else "removed" last_is_active = is_active processed_positions.append(pos) cursor.close() conn.close() return json.dumps({ "status": "SUCCESS", "message": f"Pencil mark {pencilValue} {last_action_type} at position(s) {processed_positions}", "game_id": gameId, "positions_processed": processed_positions, "pencil_value": pencilValue, "is_active": last_is_active, "action_type": last_action_type, "action_time": datetime.now().isoformat() }) By parsing the JSON string, the UDF can now iterate through the list of positions and perform the required database operations for each one. This allows players to mark multiple cells with the same pencil value simultaneously. This approach is highly versatile and can be used as a template for any scenario where you need to pass multiple selected items from a Power BI report to a UDF. More in-depth details can be found at these blog posts: Translytical Sudoku Handling Multi-Row Inputs for Translytical Task Flows Hope this example is useful and sparks some ideas!Approval workflows
You can allow users to kick-off approval workflows to enable admins or specific users to approve or reject various requests. For example, on the report below non-admin users can propose a discount and submit it as request such that an admin will be notified of the request. The admin will get the discount request in a Teams channel, and when they click approve, they are redirected to a separate admin report that is automatically filtered to the right data and shows the requested discount percentage. The admin can do the final review and apply the requested discount. This approval workflow scenarios requires two user data functions to: Send a request : Request for an admin to approve a discount Approve a request: Admin can apply the discount Here is the user data function for the Send a request scenario: import fabric.functions as fn import logging import requests # Initialize the UserDataFunctions udf = fn.UserDataFunctions() #This function is designed for sending an approval request message to a specific Microsoft Teams channel using the Microsoft Graph API @udf.function() def RequestDiscount(user: str, comment: str, discount: float, revenue: float, URLfilter: str) -> str : logging.info('Python UDF trigger function processed a request.') try: # Define the endpoint URL for Microsoft Graph API to send a message to Teams team_id = "<team id>" # Replace with your actual team ID channel_id = "<channel id>" # Replace with your actual channel ID url = f"https://graph.microsoft.com/v1.0/teams/{team_id}/channels/{channel_id}/messages" # Define the headers including the authorization token headers = { "Authorization": "<auth token here>", "Content-Type": "application/json" } Discountpercent = str(discount) + "%" Discountdollars = '${:,.2f}'.format(((discount/100) * revenue)) ApprovalURL = "<Report URL here>" + URLfilter + "%20and%20DiscountValues%2FValues%20eq%20" + str(discount) #add your report URL here # Define the message body with HTML links #I only configured an approval flow for demo purposes. You will need to add reject action as well or remove this option. message_body = f""" <div> <strong>Discount Request from {user}</strong><br> <p>Discount percentage: {Discountpercent}</p> <p>Discount total: {Discountdollars}</p> <p>Comment: {comment}</p> <p> <a href={ApprovalURL} target="_blank">Approve</a> | <a href="https://example.com/reject" target="_blank">Reject</a> </p> </div> """ # Define the payload payload = { "body": { "content": message_body, "contentType": "html" } } # Make the POST request to the Microsoft Graph API response = requests.post(url, headers=headers, json=payload) # Check if the request was successful if response.status_code == 201: return "Approval request successfully posted in Teams." else: raise fn.UserThrownError("Failed to post approval request.", {"Status code": response.status_code}, {"Response": response.text}) except Exception as e: raise fn.UserThrownError("We ran into an issue.", {"Error:": str(e)}) Here is the user data function for the Approve a request scenario: import fabric.functions as fn import logging udf = fn.UserDataFunctions() @udf.connection(argName="sqlDB", alias="Translytical") @udf.function() def ApproveDiscount(sqlDB: fn.FabricSqlConnection, opportunities: str, discountinput: float, comment: str)->str : logging.info('Python UDF trigger function processed a request.') connection = sqlDB.connect() cursor = connection.cursor() if (discount < 0): raise fn.UserThrownError("Discount cannot be negative") if (discount > 1): discount = discount / 100 if (discount > 0.50): raise fn.UserThrownError("Discount cannot exceed 50%") # SQL update command SQL_update_command = "UPDATE [dbo].[Opportunity] SET [Discount] = " + str(discount) + " WHERE Opportunity_Number IN (" + opportunities + ")" cursor.execute(SQL_update_command) # Commit the transaction connection.commit() # Close the connection cursor.close() connection.close() try: # Define the endpoint URL for Microsoft Graph API to send a message to Teams team_id = "<team id>" # Replace with your actual team ID channel_id = "<channel id>" # Replace with your actual channel ID url = f"https://graph.microsoft.com/v1.0/teams/{team_id}/channels/{channel_id}/messages" # Define the headers including the authorization token headers = { "Authorization": "<auth token here>", "Content-Type": "application/json" } # Define the message body with comment message_body = f""" <div> <strong>Approved Discount Request</strong><br> <p>Comment: {comment}</p> </div> """ # Define the payload payload = { "body": { "content": message_body, "contentType": "html" } } # Make the POST request to the Microsoft Graph API response = requests.post(url, headers=headers, json=payload) # Check if the request was successful if response.status_code == 201: return "Discount has been approved and applied. Team has been notified." else: raise fn.UserThrownError("Failed to post approval request.", {"Status code": response.status_code}, {"Response": response.text}) except Exception as e: raise fn.UserThrownError("We ran into an issue.", {"Error:": str(e)}) Feel free to use this code as inspiration for your approval workflow scenarios!Augment data on the fly
You can programmatically fetch additional data related to the data within your report. Example, you can use this report to not only track and update the status of various collaborations, but you can also fetch additional data via API, such as the latest contact information for a registered partner company. Here is the Python code for the user data function that powers this scenario: import fabric.functions as fn import logging import requests udf = fn.UserDataFunctions() @udf.connection(argName="sqlDB",alias="Translytical") @udf.function() def GetContactInfo(sqlDB: fn.FabricSqlConnection, company: str, status: str, date:str, comment: str) -> str: logging.info('Python UDF trigger function processed a request.') # Error handling for no status selected or no company input if(status=="" or len(status) < 1): raise fn.UserThrownError("The status isn't valid.", {"status:": status}) if(company=="" or len(company) < 1): raise fn.UserThrownError("The company isn't valid.", {"company:": company}) #Call External API to get contact info within registered partner company url = "https://dummy-json.mock.beeceptor.com/users" response = requests.get(url) if response.status_code == 200: Jsondata = response.json() for i in Jsondata: #Check if company contact is registered in the external system if i['company'] == company: # Combine timestamp with comment comment_value = date + " - " + comment # Get customer contact and put in format based on if updating an record or adding new record Existing_Partner_data = (i['name'], i['username'], i['email'], i['address'], i['zip'], i['state'], i['country'], i['phone'], status, comment_value, company) New_Partner_data = (i['name'], company, i['username'], i['email'], i['address'], i['zip'], i['state'], i['country'], i['phone'], status, comment_value) # Establish a connection to the SQL database connection = sqlDB.connect() cursor = connection.cursor() #Check if there is a status record for the company SQL_read_Command = "SELECT * FROM [dbo].[CompanyStatus] WHERE Company = ?" cursor.execute(SQL_read_Command, company) if cursor.fetchone(): #if there is a status record for the company, update the status and contact info SQL_update_command = "UPDATE [dbo].[CompanyStatus] SET [Name] = ?, [Username] = ?, [Email] = ?, [Address] = ?, [Zip] = ?, [State] = ?, [Country] = ?, [Phone] = ?, [Status] = ?, [Comment] = ? WHERE [Company] = ?;" cursor.execute(SQL_update_command, Existing_Partner_data) else: #if there is not a status record for the company, add new record SQL_insert_command = "INSERT INTO [dbo].[CompanyStatus](Name, Company, Username, Email, Address, Zip, State, Country, Phone, Status, Comment) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);" cursor.execute(SQL_insert_command, New_Partner_data) # Commit the transaction connection.commit() # Close the connection cursor.close() connection.close() return "Collab with " + company + " is now " + status.lower() + "." #if reached here in the code, then the company contact is NOT registered in the external system, throw error raise fn.UserThrownError("The company is not a registered partner.", {"company:": company}) Feel free to use this code as inspiration for your own data ops + write back scenario!Vacation Tracker
Code Repo : https://github.com/NandanHegde15/MSFTFabric-Projects/blob/main/Vacation%20tracker/User%20Data%20Function.py Work Flow: The code implements a Vacation Tracker workflow in Microsoft Fabric using User Defined Functions (UDFs). It enables employees to submit and the Supervisor of that employee to approve, or reject vacation requests based on notification received. validates requests, checks public holidays, uses Azure OpenAI for recommendations, and sends notifications (via email + SMS). Employee │ │ (Submit Request: EmployeeId, StartDate, EndDate, Reason, Priority) ▼ Vacation Request UDF (vacation_tracker_request) │ ├─► Validate input (dates, reason, priority flag) │ ├─► SQL MERGE into VacationTracker │ ├─► Fetch Employee & Supervisor details from SQL │ ├─► Call Nager.Date API → Get holidays │ ├─► Query SQL → Past 12 months leave history │ ├─► Build prompt → Call Azure OpenAI GPT │ │ │ └─► AI Suggestion (approve/reject context) │ ├─► Notify Supervisor: │ • Email via Logic App (Approve / Reject buttons → Power BI report link) │ • SMS via Twilio (if priority = Yes) │ ▼ Supervisor Action (Approve / Reject) │ ├─► If Approve → vacation_tracker_approval UDF │ • Update SQL (status = Approved, reason, modified date) │ • Notify Employee (Approval Email via Logic App) │ └─► If Reject → vacation_tracker_rejection UDF • Update SQL (status = Rejected, reason, modified date) • Notify Employee (Rejection Email via Logic App) Employee Page : Governed by RLS (Every employee would see his/her own details and can raise a Vacation request) #Data Function 1 Supervisor would be able to see all details of his resources : Supervisor would receive an email and in case of High priority receive an SMS with AI sugesstion based on past data and public holiday in range : Based on Approve or Reject decision, Supervisor would be redirected to either Approval page or rejection page #Data Function 2 #Data Function 3 Based on approval/Rejection, Backend database and the Direct Query report would be updated and the employee would receive an email notification :Dynamic Power BI Experiences
I created a Power BI learning game to demonstrate how Translytical and UDF capabilities can transform user journey and user experience in Power BI to boost adoption. I focused on the following Translytical use cases: 1. Registration and State Management The game begins with an engagement action : players choose to start and see their email recognized. This creates commitment to the learning experience. A UDF populates the SQL database table with active players, notifications, timestamps, avatars, progress, and points. Using DAX to control transparency based on written columns, the registration menu "disappears" for active players but reappears for new users opening the Power BI report. 2. Notification Management I created personalized progress with guided steps to direct users through the prepared journey. Notifications attract player attention and explain game rules. This approach applies to any business report (eg: pushing notifications about report readiness, data accuracy, issues, guidance, etc. significantly boosts adoption). Users can dismiss notifications to avoid spam from already-consumed information. 3. Custom Time-Based Scoring To demonstrate this functionality, users are guided to click a specific item and select an avatar. Avatar selection triggers a UDF that calculates progression speed and assigns scores based on time spent. This enables progress tracking and maintains learning engagement. Result: These three combined UDFs showcase new possibilities for user experience, storytelling and user journey design, accessibility and engagement levels that boost adoption. The report is fully personalized based on each player's progress, allowing players to start from where they left off. This showcases an example of personalized journey tracking. Impact: While gamification provides an excellent confidence boost and aids change management, these techniques extend far beyond gaming scenarios. By combining state management, notification handling, and adaptive scoring, organizations can create engaging user journeys that drive adoption and sustained usage. The techniques showcased here provide a foundation for any scenario requiring user engagement, progress tracking, and personalized experiences. UDF used: TRANSLYTICAL GAMING FRAMEWORK - UDF #1 of 3 Purpose: User registration and state management for personalized Power BI experiences Business Value: Enables session persistence and user journey tracking insert_gaming_user import fabric.functions as fn from datetime import datetime # Create the UserDataFunctions instance udf = fn.UserDataFunctions() @udf.connection(argName="sqlDB", alias="AlexSQL") @udf.function() def insert_gaming_user(sqlDB: fn.FabricSqlConnection, userPrincipalName: str) -> str: """ Insert a new gaming user record with default values including timestamp Args: sqlDB: Connection to the Fabric SQL database userPrincipalName: The user's principal name from DAX (e.g., from USERPRINCIPALNAME()) Returns: str: Success message for Power BI """ try: # Input validation if not userPrincipalName or userPrincipalName.strip() == "": raise fn.UserThrownError("User principal name cannot be empty.") # Clean the input userName = userPrincipalName.strip() # Get current timestamp for when UDF is executed current_timestamp = datetime.now() # Establish connection to the SQL database connection = sqlDB.connect() cursor = connection.cursor() # Check if user already exists (optional - remove if you want duplicates) check_query = "SELECT COUNT(*) FROM [dbo].[Gaming] WHERE [UserName] = ?" cursor.execute(check_query, userName) user_count = cursor.fetchone()[0] if user_count > 0: cursor.close() connection.close() raise fn.UserThrownError(f"User '{userName}' already exists in the gaming table.") # Generate next ID (since you're using INT NOT NULL PRIMARY KEY) # Get the maximum existing ID and add 1 id_query = "SELECT ISNULL(MAX([Id]), 0) + 1 FROM [dbo].[Gaming]" cursor.execute(id_query) next_id = cursor.fetchone()[0] # Insert the new gaming user record with extended columns including Points and Avatar ID insert_query = """ INSERT INTO [dbo].[Gaming] ( [Id], [UserName], [BeginID], [NotificationStart], [TimeStamp], [Level1], [L1_TimeStamp], [Level2], [L2_TimeStamp], [Points], [ChallengePoints], [Avatar ID] ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) """ # Execute the insert with specified values # TimeStamp = current datetime, Points and ChallengePoints = 0, Avatar ID = 2, other new columns = NULL cursor.execute(insert_query, ( next_id, # Id userName, # UserName 1, # BeginID 0, # NotificationStart current_timestamp, # TimeStamp (current datetime) None, # Level1 (NULL) None, # L1_TimeStamp (NULL) None, # Level2 (NULL) None, # L2_TimeStamp (NULL) 0, # Points (set to 0) 0, # ChallengePoints (set to 0) 2 # Avatar ID (set to 2) )) # Commit the transaction connection.commit() # Close connections cursor.close() connection.close() return f"Successfully added user '{userName}' to gaming table with ID {next_id} and Avatar ID 2 at {current_timestamp.strftime('%Y-%m-%d %H:%M:%S')}" except fn.UserThrownError: # Re-raise user errors as-is raise except Exception as e: # Handle any other database errors raise fn.UserThrownError(f"Database error occurred: {str(e)}") TRANSLYTICAL GAMING FRAMEWORK - UDF #2 of 3 Purpose: Notification management and user engagement flow control Business Value: Enables dismissible notifications and prevents information spam, applicable to any business report requiring guided user experiences update_notification_status import fabric.functions as fn # Create the UserDataFunctions instance udf = fn.UserDataFunctions() @udf.connection(argName="sqlDB", alias="AlexSQL") @udf.function() def update_notification_status(sqlDB: fn.FabricSqlConnection, userPrincipalName: str) -> str: """ Update the NotificationStart column to 1 for an existing user Args: sqlDB: Connection to the Fabric SQL database userPrincipalName: The user's principal name from DAX (e.g., from USERPRINCIPALNAME()) Returns: str: Success message for Power BI """ try: # Input validation if not userPrincipalName or userPrincipalName.strip() == "": raise fn.UserThrownError("User principal name cannot be empty.") # Clean the input userName = userPrincipalName.strip() # Establish connection to the SQL database connection = sqlDB.connect() cursor = connection.cursor() # Check if user exists check_query = "SELECT COUNT(*) FROM [dbo].[Gaming] WHERE [UserName] = ?" cursor.execute(check_query, userName) user_count = cursor.fetchone()[0] if user_count == 0: cursor.close() connection.close() raise fn.UserThrownError(f"User '{userName}' does not exist in the gaming table.") # Update the NotificationStart column to 1 update_query = """ UPDATE [dbo].[Gaming] SET [NotificationStart] = 1 WHERE [UserName] = ? """ # Execute the update cursor.execute(update_query, userName) # Commit the transaction connection.commit() # Close connections cursor.close() connection.close() return f"Successfully updated NotificationStart to 1 for user '{userName}'" except fn.UserThrownError: # Re-raise user errors as-is raise except Exception as e: # Handle any other database errors raise fn.UserThrownError(f"Database error occurred: {str(e)}") TRANSLYTICAL GAMING FRAMEWORK - UDF #3 of 3 Purpose: Custom time-based scoring and progress tracking with avatar personalization Business Value: Measures user engagement speed and rewards timely interactions, adaptable for training completion, task performance, and user adoption metrics complete_level1_challenge import fabric.functions as fn from datetime import datetime # Create the UserDataFunctions instance udf = fn.UserDataFunctions() @udf.connection(argName="sqlDB", alias="AlexSQL") @udf.function() def complete_level1_challenge(sqlDB: fn.FabricSqlConnection, userPrincipalName: str, selectedAvatar: int) -> str: """ Complete Level 1 challenge, assign points based on time difference, and update Avatar ID Args: sqlDB: Connection to the Fabric SQL database userPrincipalName: The user's principal name from DAX (e.g., from USERPRINCIPALNAME()) selectedAvatar: The selected avatar ID from DAX measure Returns: str: Success message with points awarded for Power BI """ try: # Input validation if not userPrincipalName or userPrincipalName.strip() == "": raise fn.UserThrownError("User principal name cannot be empty.") if selectedAvatar is None: raise fn.UserThrownError("Selected Avatar cannot be empty.") # Clean the input userName = userPrincipalName.strip() # Get current timestamp for L1_TimeStamp current_timestamp = datetime.now() # Establish connection to the SQL database connection = sqlDB.connect() cursor = connection.cursor() # Check if user exists and get their initial TimeStamp check_query = """ SELECT [TimeStamp], [L1_TimeStamp] FROM [dbo].[Gaming] WHERE [UserName] = ? """ cursor.execute(check_query, userName) result = cursor.fetchone() if not result: cursor.close() connection.close() raise fn.UserThrownError(f"User '{userName}' does not exist in the gaming table.") initial_timestamp = result[0] existing_l1_timestamp = result[1] # Check if Level 1 challenge already completed if existing_l1_timestamp is not None: cursor.close() connection.close() raise fn.UserThrownError(f"User '{userName}' has already completed Level 1 challenge.") # Check if initial TimeStamp exists if initial_timestamp is None: cursor.close() connection.close() raise fn.UserThrownError(f"User '{userName}' does not have an initial TimeStamp.") # Calculate time difference time_diff = current_timestamp - initial_timestamp total_minutes = time_diff.total_seconds() / 60 # Determine points based on time difference # GAMING MECHANICS: Time-based scoring rewards faster completion # < 10 min = 20 pts (expert), < 1 hour = 10 pts (good), etc. if total_minutes < 10: points = 20 time_category = "< 10 minutes" elif total_minutes < 60: # < 1 hour points = 10 time_category = "< 1 hour" elif total_minutes < 1440: # < 1 day (24 hours * 60 minutes) points = 5 time_category = "< 1 day" else: # more than 1 day points = 3 time_category = "> 1 day" # Update L1_TimeStamp, Level1 points, and Avatar ID update_query = """ UPDATE [dbo].[Gaming] SET [L1_TimeStamp] = ?, [Level1] = ?, [Avatar ID] = ? WHERE [UserName] = ? """ # Execute the update cursor.execute(update_query, (current_timestamp, points, selectedAvatar, userName)) # Commit the transaction connection.commit() # Close connections cursor.close() connection.close() return f"Level 1 completed for '{userName}': {points} points awarded ({time_category}), Avatar ID updated to {selectedAvatar} at {current_timestamp.strftime('%Y-%m-%d %H:%M:%S')}" except fn.UserThrownError: # Re-raise user errors as-is raise except Exception as e: # Handle any other database errors raise fn.UserThrownError(f"Database error occurred: {str(e)}") If this Translytical entry resonates with you, I'd love to connect! Please give it a ❤️ if you found it valuable, and feel free to reach out on LinkedIn Alexandru Badiu | LinkedIn Best regards, Alexandru BadiuModify data record(s) in a SQL table
This translytical task flow can be used as a template / guide for how to modify data records within a SQL table. For example, you can use translytical task flows to modify the discount value seen in the table without ever leaving the report. You simply enter in the new value in the text slicer and click the ‘Submit discount’ button, which runs a Fabric User data function that instantly updates the data source records that match the applied filters. Here is the Python code for the user data function that powers this scenario: import fabric.functions as fn import logging udf = fn.UserDataFunctions() @udf.connection(argName="sqlDB", alias="Translytical") @udf.function() def UpdateDiscount(sqlDB: fn.FabricSqlConnection, quantity: int, risklevel: str, dealexpiration: int, discount: float)->str : logging.info('Python UDF trigger function processed a request.') sqlConnection = sqlDB.connect() cursor = sqlConnection.cursor() if (discount < 0): raise fn.UserThrownError("Discount cannot be negative") if (discount > 1): discount = discount / 100 if (discount > 0.50): raise fn.UserThrownError("Discount cannot exceed 50%") risk = get_risk(risklevel) if risk == 0: return f"This opportunity cannot be changed since it is either in Won or Lost state." days_to_close_sql = get_days_to_close_sql_string(dealexpiration) query = f"UPDATE [dbo].[Opportunity] SET [Discount] = ? WHERE Rating = ? {days_to_close_sql} AND Quantity = ?" params = (discount, risk, quantity) # Convert the query to a properly formatted T-SQL statement tsql_query = query.replace("?", "{}").format(*[f"'{p}'" if isinstance(p, str) else p for p in params]) # Print the actual T-SQL statement logging.info(f"Executing SQL Query: {tsql_query}\n") cursor.execute(query, params) sqlConnection.commit() sqlConnection.close() return f"Opportunities with {risklevel} are updated." def get_risk(risklevel:str)->int: match risklevel: case "High risk": return 1 case "Medium risk": return 2 case "Low risk": return 3 case _: return 0 def get_days_to_close_sql_string(dealexpiration: int)->str: match dealexpiration: case 60: return "AND Days_To_Close <= 69" case 300: return "AND Days_To_Close >= 300" case _: return f"AND Days_To_Close >= {dealexpiration} AND Days_To_Close <= {dealexpiration + 9}" Feel free to use this code as inspiration for your writeback scenarios!Wallet Watch Analytics: Expense Tracker
Introduction: WalletWise Analytics is a smart personal expense tracking application that combines the power of Microsoft Fabric and Power BI to create an intelligent expense tracking system. What It Does: - Track Expenses in Real-time: Add your daily expenses directly from a Power BI dashboard and watch your balance update instantly - Monitor Account Balance: See your current balance on a live card that updates every time you spend money - Email Notifications: Get email alerts sent to your inbox when your balance gets dangerously low - Monthly Salary Management: Automatically adds $8,000 monthly salary to keep your account funded - Expense Control: View all your expenses in a table and visualize the spending - Write-back Functionality: Enter expense data through Power BI that immediately saves to your database Full Demo Video: Click Here Steps Used: Step 1: Database Setup - Created two tables in Fabric SQL Database: Expense table and Bank table - Added sample data to test the system Step 2: User Data Functions - Built smart functions that can add expenses, add salary, and send email alerts - Connected these functions to the database to read and write data Step 3: Power BI Dashboard - Created a user-friendly dashboard with cards showing balance and alerts - Added input controls where users can type expense details - Built a table showing all expenses Step 4: Write-back Buttons - Added "Add Expense" button that saves data to the database when clicked - Added "Add Salary" button that adds $8,000 monthly credit - Added "Send Email" button for email alerts Step 5: Smart Notifications - Set up email notifications for critical balance levels - Made the system update in real-time when changes happen Why I Came Up With This: I created this project to solve a common problem many people face: Losing track of their spending and running out of money unexpectedly. This project showcases the power of translytical flows - where analytical tools like Power BI can write data back to databases, creating a seamless experience between viewing data and updating it. UDF: Adding Expense import fabric.functions as fn from datetime import datetime udf = fn.UserDataFunctions() @udf.connection(argName="sqlDB", alias="YOUR_CONNECTION_ALIAS") @udf.function() def write_expense(sqlDB: fn.FabricSqlConnection, expenseDate: str, description: str, amount: float, category: str) -> str: # Error handling if amount <= 0: raise fn.UserThrownError("Amount must be greater than 0", {"Amount": amount}) if len(description) > 255: raise fn.UserThrownError("Description too long (max 255 characters)", {"Description": description}) # Establish connection connection = sqlDB.connect() cursor = connection.cursor() try: # Insert into Expense table expense_query = "INSERT INTO Expense (ExpenseDate, Description, Amount, Category) VALUES (?, ?, ?, ?)" cursor.execute(expense_query, expenseDate, description, amount, category) # Insert corresponding debit into Bank table bank_query = "INSERT INTO Bank (TransactionDate, Description, Amount, TransactionType) VALUES (?, ?, ?, ?)" bank_description = f"Expense: {description}" cursor.execute(bank_query, expenseDate, bank_description, -amount, 'Debit') # Commit transaction connection.commit() return f"Expense ${amount} for '{description}' added successfully" except Exception as e: connection.rollback() raise fn.UserThrownError(f"Failed to add expense: {str(e)}") finally: cursor.close() connection.close() Add $8000 Monthly Salary @udf.connection(argName="sqlDB", alias="YOUR_CONNECTION_ALIAS") @udf.function() def add_salary(sqlDB: fn.FabricSqlConnection) -> str: connection = sqlDB.connect() cursor = connection.cursor() try: # Add fixed $8000 salary to Bank table with current date current_date = datetime.now().strftime('%Y-%m-%d') salary_query = "INSERT INTO Bank (TransactionDate, Description, Amount, TransactionType) VALUES (?, ?, ?, ?)" cursor.execute(salary_query, current_date, 'Monthly Salary', 8000.00, 'Credit') connection.commit() return "Monthly salary $8000 added successfully" except Exception as e: connection.rollback() raise fn.UserThrownError(f"Failed to add salary: {str(e)}") finally: cursor.close() connection.close() Send Email when balance drops below $100 Configuration Setup (Detailed Steps) Part A: Get SendGrid API Key Create SendGrid Account: - Go to https://sendgrid.com/ - Click "Start for Free" or "Sign Up" - Fill out registration form with your details - Verify your email address Access SendGrid Dashboard: - Log into your SendGrid account - You'll see the main dashboard Create API Key: - In the left sidebar, click "Settings" - Click "API Keys" - Click "Create API Key" button - Choose "Restricted Access" (recommended) - Give it a name like "Fabric-Expense-Tracker" - Under "Mail Send", select "Full Access" - Click "Create & View" - IMPORTANT: Copy the API key immediately (it won't be shown again) - Store it securely (you'll need this for the code) Part B: Update Your Function Code import fabric.functions as fn import logging from flask import Flask from flask_mail import Mail, Message from datetime import datetime udf = fn.UserDataFunctions() # Flask Mail Configuration app = Flask(__name__) app.config['MAIL_SERVER'] = 'smtp.sendgrid.net' app.config['MAIL_PORT'] = 587 app.config['MAIL_USERNAME'] = 'apikey' app.config['MAIL_PASSWORD'] = 'YOUR_SENDGRID_API_KEY_HERE' # Replace with your actual API key app.config['MAIL_USE_TLS'] = True app.config['MAIL_DEFAULT_SENDER'] = '[email protected]' # Replace with your email mail = Mail(app) @udf.connection(argName="sqlDB", alias="YOUR_CONNECTION_ALIAS") # Replace with your alias @udf.function() def check_balance_and_notify(sqlDB: fn.FabricSqlConnection, recipientemail: str) -> str: """Function to manually check balance and send notification if needed""" connection = sqlDB.connect() cursor = connection.cursor() try: # Get current balance balance_query = "SELECT SUM(Amount) FROM Bank" cursor.execute(balance_query) current_balance = cursor.fetchone()[0] or 0 if current_balance < 100: send_low_balance_email(current_balance, recipientemail) return f"🚨 CRITICAL: Balance is ${current_balance:.2f}. Email notification sent to {recipientemail}" else: return f"✅ Balance OK: ${current_balance:.2f}. No notification needed." except Exception as e: raise fn.UserThrownError(f"Failed to check balance: {str(e)}") finally: cursor.close() connection.close() def send_low_balance_email(balance: float, recipient_email: str): """Send low balance alert email - SIMPLIFIED VERSION""" try: current_time = datetime.now() # Simple text email instead of complex HTML email_text = f""" CRITICAL: Low Balance Alert! Your account balance has dropped below $100! Current Balance: ${balance:.2f} Date: {current_time.strftime('%Y-%m-%d')} Time: {current_time.strftime('%H:%M:%S')} Please add funds to your account immediately to avoid any issues. """ with app.app_context(): msg = Message( subject="🚨 CRITICAL: Account Balance Alert - Action Required", recipients=[recipient_email], body=email_text ) mail.send(msg) logging.info(f"Low balance email sent to {recipient_email}. Balance: ${balance:.2f}") except Exception as e: logging.error(f"Failed to send email: {str(e)}") raise fn.UserThrownError(f"Failed to send notification email: {str(e)}") Other Use Cases That Can Be Implemented: - Student Budget Manager: Track tuition, books, food, and living expenses for students - Travel Expense Tracker: Monitor trip expenses, daily spending limits, and travel budgets - Investment Portfolio Monitor: Track investments, gains, losses, and portfolio alerts - Healthcare Expense Tracker: Track medical bills, insurance claims, and health-related spending
