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.
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:
pass
