SQL database and sending Power BI data alerts email to different recipients

realtimer
realtimer used Ask the Experts™
on
I am hoping to use Power BI (on which I have no expertise) to connect to a SQL database and send email alerts to specific users when certain data threshold is met. Suppose it is a ticketing system. When an open ticket ages more than 15 days, I would like to automatically email the ticket owner and notify them of the issue. When the ticket ages more than 30 days, I would like it to email the ticket owner and the supervisor. When the ticket becomes over 45 days old, I would like it to email the ticket owner, the supervisor, and the manager. Can this be accomplished with Power BI? If so, Does Power BI Desktop have that capability or do I need to use the cloud version? Does it require specific licensing level? Can the recipient email addresses be read from another SQL table?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Power BI is a data visualization tool, not au automation tool.

Your description is of automated escalations in a ticketing system, this would usually be done within the ticketing system, and I would expect the ticketing system to also show that the escalations have been run and acknowledged by the escalatees. Is this a comercial (purchased software) ticketing system ?

Author

Commented:
Thank you. I used "ticketing system" as an example to illustrate what we are trying to do but it is actually just a table in a SQL database. The data contains user name and dates along with other information. If the date in a record becomes older than a certain number of days, then we want to email that user.
Chinmay PatelChief Technology Ninja
Distinguished Expert 2018

Commented:
Hi realtimer,

Power BI supports sending email based on a schedule but not based on condition. You might want to look at Power Automate(Previously Flow) to "trigger" an email by using a Data Alert (For example, https://docs.microsoft.com/en-us/power-bi/service-flow-integration).

There are other ways around this as well but if I have to choose, I will go with Power Automate - hands down.

Regards,
Chinmay.

Author

Commented:
Thank you. I apologize for very basic questions but I am a complete newbie at this. According to Power BI data alerts documentation, it appears that I can only set alerts on gauges, KPI's and cards on reports and each one needs to result in a single numeric value. In our case, I would like to monitor an entire column of data (dates) in the table and search for any record that is older than a threshold. Then the alert email recipient must be determined dynamically from the record (the user). Is that possible with this Power BI + Power Automate combination?
Chinmay PatelChief Technology Ninja
Distinguished Expert 2018

Commented:
I would suggest you look at Power BI Connectors, https://preview.flow.microsoft.com/en-us/connectors/shared_powerbi/power-bi/

If that does not fit the bill, you can have a look at https://preview.flow.microsoft.com/en-us/connectors/shared_sql/sql-server/

Basically, you use Flow to monitor SQL database(bypassing Power BI) and send out the email if the records are more than certain days old.

Author

Commented:
Thank you for the additional information.
Is it possible to dynamically set the recipient email addresses from the data? Suppose we have a SQL table with 2 columns: user and ticket age. If the ticket age is above a threshold, then we would like to email that particular user.
I have tried the SQL connector from Flow but it only seems to allow static list of recipients.
Chief Technology Ninja
Distinguished Expert 2018
Commented:
The SQL part can be tricky. For how long the ticket can stay in the system? And how many rows you have in your SQL Database?
There are couple of techniques we can sue to make your data compatible with your requirements.

Using Power Automate you can set dynamic email. The Email Action accepts everything (To, CC, BCC, Body) as parameters.

Author

Commented:
Thank you again. We currently have a total of 340,000 records. The records are not deleted and data is appended once a week. Each week we add 3000 to 5000 records (open "tickets") and we are only interested in the data for the week. We will have multiple tables: (1) main table, (2) user vs. email address table, (3) user vs department table, etc. The main table includes user names but not email addresses. Once we identify the tickets that need attention, we will need to reference the user vs. email address table to find the address to insert into the "To" field in Power Automate.

Do you have any link to documentation on how to use Power Automate customization or dynamic contents? I have been searching for "Power Automate send email to dynamic recipients" or "Power Automate send email to addresses in SQL table" and many other variations but all the search results are slightly off and I cannot figure out what queries and parameters are allowed in SQL Get Rows (v2) or Send Email actions.

Author

Commented:
I have figured out how to use a variable and fill it with the data from a second SQL table.
Thanks for your help.
Chinmay PatelChief Technology Ninja
Distinguished Expert 2018

Commented:
Hi realtimer,

I am glad you sorted it out. I wanted to highlight couple of things. In case you do not want to burden your SQL Database, you can bring the limited set of Data (on which your mails are to be sent) to the CDS store. After that, you can perform n number of operations - independent of SQL Database, pull out reports, run flows and even create apps (using PowerApps) very easily.

Regards,
Chinmay.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial