Power BI- Loading PBI Log Files

There’s a reason that log analytics programs, like Splunk, Data Dog and Sumo Logic are so popular.  Even Microsoft has a Log Analytics product, but the important message here is log data is massive and parsing through it to find important information can be a bit of a pain.  The second word in Log Analytics IS “analytics”.  Due to this, the first thought when faced with the number of logs from many complex Power BI environments that people are building, (multiple data sources, multiple data centers, SSRS, Power BI, etc) was to load the logs into Power BI.

I’ve been working with trace files, but the log files should have been the first files I should have discussed, (my bad!)  Let’s correct that oversight right now.

1st Example- Power BI Desktop Logs

First we’ll start with the standard Power BI Desktop log, which can be found in C:\Users\<username>\appdata\local\Microsoft\Power BI Desktop\Traces\Performance.  This file keep track of the main processing performed by Power BI.  This log rotates out on a regular basis, so you’ll see more than one and the log will have the naming convention of:

PBIDesktop.<ID>.<date timestamp>.log

We will load this log file into Power BI Desktop by clicking on Get Data –> Text/CSV and then choose to view all files and navigate to the folder that contains the log files.  Choose the desktop log from the list of files available and click OK.

You’ll need to format the data to produce a working table, which the M query below will demonstrate:

let
    Source = Csv.Document(File.Contents("C:\Users\kegorman.NORTHAMERICA\Documents\Traces\PBIDesktop.2020.2018-08-23T18-20-38-862814.log"),5,"",null,1252),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", Int64.Type}, {"Column4", type text}, {"Column5", type text}}),
        #"Replaced Value" = Table.ReplaceValue(#"Changed Type","{Start:","",Replacer.ReplaceText,{"Column5"}),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value", "Column5", Splitter.SplitTextByDelimiter(",Action:", QuoteStyle.Csv), {"Column5.1", "Column5.2"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column5.1", type datetime}, {"Column5.2", type text}}),
        #"Replaced Value1" = Table.ReplaceValue(#"Changed Type1",",ProductVersion:2.56.5023.1043 (PBIDesktop)","",Replacer.ReplaceText,{"Column5.2"}),
        #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","}","",Replacer.ReplaceText,{"Column5.2"}),
        #"Split Column by Delimiter1" = Table.SplitColumn(#"Replaced Value2", "Column5.2", Splitter.SplitTextByEachDelimiter({"Duration:"}, QuoteStyle.Csv, true), {"Column5.2.1", "Column5.2.2"}),
        #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column5.2.1", type text}, {"Column5.2.2", type duration}}),
        #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Column5.2.2", "Duration"}, {"Column5.2.1", "Action"}, {"Column5.1", "Start Time"}}),
        #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Column4"}),
        #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Column3", "PID"}, {"Column2", "Type"}}),
        #"Replaced Value3" = Table.ReplaceValue(#"Renamed Columns1",":","",Replacer.ReplaceText,{"Type"}),
        #"Renamed Columns2" = Table.RenameColumns(#"Replaced Value3",{{"Column1", "Main Action"}})
    in
#"Renamed Columns2"

The table then results in a very workable data set that appears similar to this:

This offers you a dataset that you can load, reload and/or append to with log data that is available to report on.  As we’ve seen previously, you can create reports on resource usage, but this one would be used to search for “where type=’Error'” or look at the steps performed by the child TID #57, etc.

There weren’t any errors in my log, so it’s kind of difficult to demonstrate, as you’d only want it to display if there was a problem, but you could set up a report that only shows the Actions that take over duration of 5 seconds.  I decided instead to just simulate the data, displaying the Action and the TID for those that fell into a certain number range…:)

2nd Example-  Power BI Report Server

Second one is  inspecting the Reporting Server Portal log, (RSPortal**.log) that resides in C:\Program Files\Microsoft Power BI Report Server\PBIRS\LogFiles

We again load this log file via Get Data –> Text/CSV and then choose to view all files, as it won’t see the .log extension otherwise.  Choose the file and click on Edit.

The M query displays the changes I performed to format the data into something that can easily be worked with.  Because of the stagnated output of the data lines, this will format the error and warning messages, with the rest of the rows only having the Information Message fulfilled, the rest of the columns will be null:

let
    Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:\Program Files\Microsoft Power BI Report Server\PBIRS\LogFiles\RSPortal.log"), null, null, 1252)}),
        #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4"}),
        #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", Int64.Type}, {"Column1.4", type text}}),
        #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1.1", "Information Message"}, {"Column1.2", "Status"}, {"Column1.3", "Status Code"}, {"Column1.4", "Status Message"}})
    in
#"Renamed Columns"

We can then create a simple table in Power BI:

Values:  Status Message

Filters:  status = Error

You then will receive the following output:

Of everything included in the log file, this quickly isolates the error message and displays it in the report.

The deal is, this uses Power BI a bit differently than we originally considered it-  it’s not just for the business user, but it can be for the technical professional as well.  I’ve worked with this kind of data my entire career and if there’s a way I can display it the way I need to answer the questions the business needs answering from me, what better way than to use the tool they’re already using to answer their questions about the business every day? 🙂