Power BI Report Server Production Debugging

Recently I came across many issues in the production environment where hundreds of reports are running PowerBI Report Server 2016.The environment is up and running for 24X7. Our users are located in multiple Geographies. The biggest challenge is that at certain point in time (may be in the middle of night) they suddenly see an error popping up in the screen. And these errors can be very random and intermittent. So if you run the same report again the error may not come. How do we understand the root cause of the error and resolve it? These errors can be from SSAS cube or PowerBI itself and sometimes the main culprit may be SQL Server but unless we know for sure who is the real cause of the problem, we cannot take any action. So, there are multiple questions:

  1. How do we collect the error logs?
  2. Which tool is throwing this error?
  3. Is there any way to reproduce the error? Unless you know the issues you can not fix it?

Solution: The solution to this issue would be to collect the log at the time of error. So first step would be to find the time when the error popped up in the user screen along with the error correct message. Also note down the report name.

Steps to execute troubleshoot the error

  1. The best way to collect the to log is LogParser Install the log parser on the Report server.
  2. Now create a PowerShell script as provided below.
$passwordFi1e = "C:\[path]\paswordfile.txt"
$username = "SERVICE  ACCOUNT WHICH HAS A LOGIN PERMISSION ON THE REMOTE POWERBI SERVER IT SHOULD BE IN THIS FORMAT :DOMAIN NAME\USER NAME”

# First time create password file 
if ( (Test-Path $passwordFi1e))
{
Read-Host -AsSecureString | convertfrom-securestring	| out-file SpasswordFile
}

$password = cat $passwordFile convertto-securestring

$credential =New-object -typename System.Management.Automation.PSCredential -argumentlist $username $password 

$servers = "HOST NAME OF THE SERVER WHERE POWERBI IS INSTALLED"

Invoke-Command -ComputerName $servers -Credential $credential -ScriptBlock {
$query =@"
select * into ERR0R.csv from 'C:\Program Fi1es\Microsoft Power BI Report Server\PBIRS\LogFiles\ReportingServicesService_2018_06_06*.log'
"@
$result = & 'C:\Program Files (x86)\Log Parser 2.2\LogParser.exe' $query -i:textline -o:CSV 
$result
}

This is how this PowerShell script works

  • First it saves the password in the text file so you do not have to enter it multiple times.
  • It connects to the remote server.
  • Collect the log from the PowerBI Log file directory.

3. Now use this Powershell Script. This script will copy the file generated from Log Parser tool:

$password = "C:\[path]\paswordfile.txt"
$username = "SERVICE  ACCOUNT WHICH HAS A LOGIN PERMISSION ON THE REMOTE POWERBI SERVER IT SHOULD BE IN THIS FORMAT :DOMAIN NAME\USER NAME"

$password =Get-Content $passwordFile convertto-securestring -AsPlainText -force
$credential= New-object -typename System.Management.Automation.PSCredential -argumentlist $username, $password
$fileLIst = "File name used in the Above Script"
 
 #read-host | Out-File C:\share\pas.txt +
 $source = "c:\Program Files (x86)\Log Parser 2.2\" +$fileLIst
 # The destination location you want the file/folder(s) to be copied to
 $destination = "\\[YourMachineName]\Share" #This should be some shared path in your local system so program which is running the script can access it.
 $servers = "HOSTNAME OF POWERBI REPORT SERVER"
 Copy-Item \\$server\$source -Destination $destination -Verbose

There are many ways you can tweak this script. So if you want to generate all the Errors for a specific day you can tweak the query like this:

@”select * into ERR0R.csv from ‘C:\Program Fi1es\Microsoft Power BI Report Server\PBIRS\LogFiles\ReportingServicesService_2018_06_06*.log’ 
where text like ’%Error%’
“@

I hope this post helps!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.