If you’ve upgraded to the October 2020 release of Power BI Server, you might be seeing the following error when you refresh some of your reports:
This error appeared when we install the November 18th patch for Power BI Server (15.0.1104.264) and wasn’t present in the original October 2020 release. The especially frustrating part was that reports refreshed just fine in the Power BI Desktop client, and the issue only appeared after publishing the report up to Power BI Server, making it much more difficult to troubleshoot.
However, the resolution ended up being pretty straightforward – there are two different ways to define a connection to a SQL Server database, and if you use both methods in the same data model and then merge the results, Power BI Server has a problem with it (and Power BI Desktop does not). To correct this issue, you need to pick a method and ensure that all the SQL Server connections in your data model use this single method to connect to the database.
One method is used when you specify a database server, but don’t specify a database:
This results in the following M query, with the connection taking up the first two lines (one for server, one for database):
If you specify the database in the connection window instead, it merges the connection statement onto a single line and uses Sql.Database (singular) instead of Sql.Databases (plural). Since you’re required to specify a database name when you provide a SQL query, so a custom query always uses this one-line method to connect:
Regardless of whether you enter the database name (and have the one-line connection method) or don’t enter a database (and use the two-line method), it looks exactly the same in the step window in Power Query:
I’d recommend using method two for all your connections, as that’s what’s needed to run a specific SQL query – if any of your data sources and query-based and not just selecting a table, you’ll have to use this approach.
I hope this helps somebody else! While I’d love it if Power BI Server avoided errors like this one, I’d also really appreciate it if Power BI Server and Desktop used the same code for shared functionality so that you can be aware of issues before you publish your reports and they fail to refresh.
A script to generate failed report refresh alerts in Power BI Server and can be scheduled with SQL Agent.
Power BI Server has it’s use cases, but it also has some gaps where it falls short of the functionality in the Power BI Service (app.PowerBI.com), but one of the major gaps is that it doesn’t notify anybody when a report refresh fails. Report owners are left either reviewing the reports every time they’re scheduled to ensure the data is refreshed, creating a report that shows refresh status (and hoping it actually refreshes itself), or implementing their own homebrew notification solution.
I opted for #3 – creating a custom notification process – and I want to share it with anybody else that’s wondering how to set up notifications for their own internal Power BI Server instance. If you just want the script, jump to the end for the link and variable instructions.
The process has a few high-level parts:
Query for failed report refreshes. Check the report catalog and refresh history to find reports where the last refresh attempt failed within the monitoring window. If a report has already been refreshed again and succeeded, or is currently being refreshed, I chose not to alert on these – this meant somebody was already on it, and we wanted to see reports that needed attention and were currently failed, not the actual failures themselves.
Create the email notification text. This is a multi-step process that inserts a header, builds a table with details on the reports, and then a footer with instructions and additional details.
Actually send the email. Don’t forget to do this part 🙂 We’ll use DBMail to send it.
SQL Agent job that runs the notification code. This is the recurring schedule that checks for failures. We chose to run it every 5 minutes, so that’s what I have it set up for below (it can be customized as needed for any interval).
CAVEAT BEFORE WE START: We’ll be querying the PBI Report Server database, which could change at any time and is totally unsupported. I’ve never seen any issues caused by it and I’ve never had a table/view change from under code I’ve written to do this, but you’ve been warned. However, we’re only reading – don’t make any changes or write to this database or you could break your PBIRS installation. If you want to learn more about this in general, check out this write-up.
Query for failed report refreshes
We’ll start by all the last successful refreshes for our reports. The process doesn’t actively use this, but it does keep it so that reports that have never been refreshed or where they’ve not been refreshed successfully for X time can be called out in the future:
,max([EndTime]) as LastSuccessTime
WHERE Status = 0 -- Success ("Completed Data Refresh")
GROUP BY SubscriptionID
The [SubscriptionHistory] table contains all the records for all scheduled refreshes, regardless of success or failure. In this case, we’re filtering for “Status = 0”, which is success – other numbers are error codes.
To grab a list of all the reports and their refresh status, we’ll use two more tables: [Catalog] (which contains a list of all the reports) and [Subscriptions] (which contains the scheduled refresh jobs). Where [SubscriptionHistory] is the log of all the job executions, [Subscriptions] contains a list of every scheduled refresh that’s set up. One of the nice things about this table is that it also contains details about the current state of the schedule – if the last run was successful or a failure, and details on the last error if there was one.
[Subscriptions] also contains a column called [LastRunTime] with a timestamp when the schedule was last run. What’s handy here is that it’s a timestamp for the last STATUS of the schedule, not the time the last refresh started. Because of that, it will contain the timestamp of the end of execution in case of a failure. Here’s how they link together (the boxes in orange match, or match close enough to show that it’s end time and not start time):
When we join those together, here’s what we get (you’ll start to see some variables here – there script has a large block at the top where you can use customize the script behavior):
select LEFT(c.Path, LEN(c.Path) - LEN(c.Name)) as Path,
@URLRootDataRefresh + Path as DataRefreshURL,
s.Description, s.LastStatus, LastRunTime, ls.LastSuccessTime
from [dbo].[Subscriptions] s
join dbo.[Catalog] c
on s.Report_OID = c.ItemID
where s.LastStatus not in ('Completed Data Refresh', 'New Scheduled Refresh Plan', 'Refreshing Data')
and c.path not like @IgnorePath
and DATEDIFF(ss, LastRunTime, GETDATE()) < @ThresholdSeconds
The first filter in the WHERE clause filters our reports that are either refreshing now or last refreshed successfully. The second allows you to use a variable to exclude a path (we keep our retired reports in a hidden folder and didn’t want them triggering an alert) – this is optional. The third filter only returns reports that failed within the last X seconds – this should match how often the job runs to check for new failures so that it’s only getting failures since the last execution. A more advanced version could check to see when the SQL Agent job last ran and get failures since then, but that wasn’t needed in our case.
There’s also a column called DataRefreshURL for each report – this will link directly to the “Scheduled Refresh” settings page for the report, and uses the report management page under (yourserver)/reports/manage/catalogitem/listcaching_pbi/(yourreport). This will allow you to go right from the email to the page that lets you view error details and refresh the report again if the error is temporary.
Create the email notification text
Now that we have a temp table (#WorkingTable) that’s holding our failed reports, let’s build an email!
I wanted to line things up neatly in columns, so the first step was figuring out how wide each column should be:
SET @LongestName = (SELECT MAX(LEN(Name)) FROM #workingtable)
SET @LongestPath = (SELECT MAX(LEN(path)) FROM #workingtable)
set @LongestLink = (SELECT MAX(LEN(DataRefreshURL)) FROM #workingtable)
With those lengths set, we can build the headers. For each of the three columns, we’ll include the column title and then enough spaces to justify each column evenly – we’ll use the same formula for the detail lines as we will for the header:
This uses both SPACE and REPLICATE to include a certain number of characters to ensure the final text is evenly column justified. There’s also an @CRLF variable that’s set at the beginning of the script – whenever I’m building multi-line text, I always create this variable as it makes breaking lines apart much, much easier further down and allows you to keep formatting consistent.
The line items are built much the same way, though here’s another handy trick that may be new to you. In the SELECT below, the @Body variable is getting appended by every single row in the resultset – the product of this is a variable that contains one additional line for every report in our table. This is also useful when you want to create a variable that contains everything in a set from a table – not just when building multi-line text, but you can consolidate anything from a list to a variable.
SELECT @Body = @Body + Name + SPACE(@LongestName - LEN(Name) + 2)
+ Path + SPACE(@LongestPath - LEN(Path) + 2)
ORDER BY Name, Path
We’re also padding these values based on the “Longest” variables we set earlier – the result is something that looks like this:
At the end, I encourage you to add a line that includes the server that’s sending the email and, if applicable, a link to any documentation – I’ve gotten into a situation before where we were getting an alert every few minutes that was being generated by some T-SQL code somewhere and we had to go through every job on every one of dozens of servers to find it. We eventually resolved the cause, but not before we’d gotten over 1000 emails each about how it was broken! Here’s something simple:
SET @Body = @Body + 'This email was generated by ' + @@SERVERNAME + ' at ' + CONVERT(VARCHAR, GETDATE(), 120)
Actually send the email
This part is simple and uses the sp_send_dbmail system stored procedure. It’s pretty straightforward – everything you need is defined in a variable earlier in the script, so you just need to call the SP:
Since you’ll be running this code from a SQL Agent job, make sure that the account you’re using for SQL Agent (or the proxy account if you’re configured one) has this permission granted. From the Microsoft docs about sp_send_dbmail:
Execute permissions for sp_send_dbmail default to all members of the DatabaseMailUser database role in the msdb database.
If you don’t have those permissions, you’ll get a moderately cryptic error and your mail won’t send.
For the “Steps Tab” from that walk-through, you’ll want to paste the script you’ve set up (once you update all the variables to match your environment). For the “Schedules Tab” step, set the job to run every X seconds, where X is the same number of seconds you set in your variables (the default in the script is 300 seconds, or 5 minutes). The notification/alerts are optional, but you can set those up if you like.
Cool – so what do I need to do?
To use the script, the minimum you need to do is set the variables at the beginning to match your environment and then schedule the agent job. Here’s the default variable block from the script:
Here’s what these are used for and when you should make changes:
@ThresholdSeconds. This is how far back you want to check for report failures. This setting should match how often you run the SQL Agent job – if they values don’t match, then you run the risk of missing some failed reports.
@ToAddresses. A list of people you want to email that should probably do somethign about this, separated by semi-colons.
@CopyAddresses. Like To, but more of a heads up – leave this NULL if not applicable.
@Blind_Copy_Recipients. We include this so we can send an email to our team’s slack channel using email integration, but we don’t want this email address to get out to anybody that gets copied. Normally NULL, but some may have a special need like this.
@URLRootDataRefresh. This should be the root of your Power BI Server address, including the “/reports” at the end. If you visit this URL, you should be redirected to the root of your reporting site to browse reports. This URL is appended with the location of the subscription settings so you can browse directly to the details of a failure.
@IgnorePath. We had a folder where we store offline reports – failures here should be ignored. If you have multiple folders like this, you can modify this variable to add them or you can modify the query in the script to exclude these folders explicitly.
@DBMailProfile. The name of the profile for sending mail. If you haven’t set up DB Mail, refer to the documentation to get this set up and name your profile. Here another good walk-through.
@Subject. Make this sufficiently scary so that recipients pay attention!
@SendMail. This is set up so you can test, and it’s defaulted to 0 (meaning the script will print your mail body rather than sending it). Once you’re happy with your settings, change this to 1 so the mail actually gets sent.
Feel free to add a comment below – I’ll update this code as people make suggestions or it improves through my own experience using it, and I hope it helps some others who miss this functionality in Power BI Server. Share below if you end up using it – I’d love to hear about it!
To see who has folder/report level permissions in SSRS and Power BI Server, you have to join a couple of tables together – I started with a query I found at StackOverflow and built onto it – here’s my query (it pulls a bit more detail and shows not just permissions granted, but those not granted):
select case E.Path
when '' then '/'
end as ItemPathAndName,
when len(E.Path)-len(E.Name) = 0 then 0
else len(E.Path)-len(E.Name)-1 end),'/'
) as ItemPath,
E.Name as ItemName,
when 1 then 'Folder'
when 13 then 'PBI Report'
else concat('Unknown (',Type,')')
end as ItemType,
when 0 then 'Inherited'
when 1 then 'Customized'
end as PermissionSource,
--D.Description, -- Uncomment for role description
convert(bit, case when
a.id is null then 0
end) as ValidPermission
from dbo.Catalog E
cross join dbo.Users C
cross join dbo.Roles D
join dbo.PolicyUserRole A
on E.PolicyID = A.PolicyID
and A.RoleID = D.RoleID
and A.UserID = C.UserID
order by Path, C.UserName
Rather than just selecting the permissions, this query actually shows all possible permissions, and shows where users have or don’t have them. In my case, I wanted to see a list of users on the server that lacked access to specific reports and the “show me the permissions” query wouldn’t do that – additionally, this query can show you all the places that your permissions are manually set, rather than inherited.
To interpret them, I copied the results to Excel, created a pivot table, and then filtered using PermissionSource=Customized (to see all the custom permissions) or ValidPermission=0 (to see the places where people didn’t have permission to view an item).
The crossjoins can make the resultset a bit large, but it worked for me. If you want to filter things down a bit, you can add a WHERE clause near the end to folder those columns for the specific situation you’re looking for.
After replacing an expired SSL certificate on Power BI Server, we were no longer able to browse the site – we received an error that “The report server isn’t configured properly… The report server Web Portal URLs and Web Service URLs don’t match”:
We hadn’t changed the Power BI Server settings aside from updating the certificate, but now couldn’t load the site without getting this error message.
We were able to resolve it by manually updating both URLs (Web Service and Web Portal) in Report Server Configuration Manager to the Fully Qualified Domain Name (FQDN) – normally, the HTTP version of the URL is set to “All Assigned” (and shows the server name) and the HTTPS version shows the name on the certificate (in our case, both the old and new certificate had the FQDN). This is how it looked on the other servers that worked:
No obvious errors, but manually changing the HTTP URL for both Service and Portal to FQDN fixed the Power BI Server site – things started to work again instantly. This bothered me, though – none of the other servers needed this setting changed, and they were all perfectly fine with the server short name in the HTTP URL.
The issue ended up being Internet Information Services installed on the same server – even though the primary site was disabled, it was bound to port 80 on all IPs without a host name, so it was claiming the “HTTP://+:80” that Power BI Server was also trying to bind. Because this bind was failing, Power BI couldn’t register matching URLs and gave us the error.
To resolve it, we added a phony hostname to the Default Web Site in IIS (even though it was stopped and shouldn’t have caused an issue) so that that it didn’t claim everything at Port 80:
Once this was done, we set the Power BI configuration back to “All Assigned” and it re-registered its own URLs, and then things started working perfectly again. I’m not sure if IIS was installed by our server admins during our certificate update, or if some other configuration change caused IIS to change how it registered the URL, but this resolved this issue.
In creating an accounting report, the vendor names we get back from our credit card processor needed some major clean-up: “52334SOUTHWESTAIR1234”, “ABD2343-BLUE DINER 843”, and so on. I initially found a great function for this from Pinal Dave:
He leaves numbers in the string and I’d like to remove them
I’d like to combine multiple spaces in a row into a single space, but leave spaces intact
The first is pretty easy to do – just remove the [0-9] and add a space to the PATINDEX. The second one uses a trick from another post I did a few years ago.
Here’s the modified version:
CREATE FUNCTION dbo.UDF_ParseAlphaChars2
RETURNS VARCHAR(8000) WITH SCHEMABINDING
DECLARE @IncorrectCharLoc SMALLINT
SET @IncorrectCharLoc = PATINDEX('%[^ A-Za-z]%', @string)
WHILE @IncorrectCharLoc > 0
SET @string = STUFF(@string, @IncorrectCharLoc, 1, '')
SET @IncorrectCharLoc = PATINDEX('%[^ A-Za-z]%', @string)
-- Trim groups of spaces into single space
SET @string = LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(@string,' ','<>'),'><',''),'<>',' ')))
SELECT dbo.UDF_ParseAlphaChars2('ABD2343-BLUE DINER 843')