Power BI Desktop Trace Logs Analyser

In this post I will show you how to analyse Power BI Desktop diagnostic trace files in a more visual way than notepad Smile

First you need to collect some diagnostics by enabling tracing on Power BI Desktop, go to: File –> Options –> Diagnostics –> Enable Tracingimage

If you click on “Open Traces folder”:

image

It will open the trace folder with all the trace logs:

image

PS – Trace log are only generated after you test your power bi report, do some refresh and interactions first to create the trace logs

Now to analyse these logs you could off course open them in notepad:

image

But is not very easy to read, so what better way to process and visualize this huge amount of text data??? Power BI off course!!!

So I created a Power BI Desktop to process and visualize the trace logs, that will allow you to quickly visualize things like:

  • Errors
  • Duration of queries
  • Performance issues
  • etc

image

image

Instructions of usage:

  • Download and open the Power BI Desktop file
  • “Edit Queries” and change the variable “VAR_LogFolder” to point to the trace logs folder:

image

image

  • Refresh the Report

image

Advertisements

10 thoughts on “Power BI Desktop Trace Logs Analyser

  1. This is fantastic Rui. My trace is dominated by PackageStorage: any idea what that is (I can guess…)? And how it might be optimised (i.e. reduced)? I’m working with a complex Query that used to run fast but now gets bogged down and takes 30mins or so to complete (running across 20GB of data of course…)

  2. Shannon Lowder says:

    Are there any documents online for what the Action Details are actually doing? I’m trying to dig into why some of my Power BI solutions are slower than others.

  3. What error you are getting? it quite simple as explained above. Edit query and change the value of var_logfolder. i’m interested to know how he is actually parsing the log file. I wanted to parse different log file. Even when i tried to read the Power Bi trace file i couldn’t do it. the file is not a comma separated.. I think he is using DAX skill to parse this file can you help us explain this party i got this code and i think you are using this to parse file isn’t ?

    let
    Source = Folder.Files(VAR_LogFolder),
    #”Filtered Rows” = Table.SelectRows(Source, each ([Extension] = “.log”)),
    #”Removed ColumnsA” = Table.RemoveColumns(#”Filtered Rows”,{“Extension”, “Date accessed”, “Date created”, “Attributes”, “Folder Path”}),
    #”Renamed ColumnsP” = Table.RenameColumns(#”Removed ColumnsA”,{{“Name”, “Log File”}, {“Date modified”, “Log File Date”}}),
    #”Added Custom” = Table.AddColumn(#”Renamed ColumnsP”, “LogText”, each Lines.FromBinary([Content])),
    #”Removed ColumnsX” = Table.RemoveColumns(#”Added Custom”,{“Content”}),
    #”Expanded LogText” = Table.ExpandListColumn(#”Removed ColumnsX”, “LogText”),
    #”Split Column by Delimiter” = Table.SplitColumn(#”Expanded LogText”,”LogText”,Splitter.SplitTextByEachDelimiter({” : “}, QuoteStyle.None, false),{“Column1.1”, “Column1.2″}),
    #”Split Column by Delimiter1″ = Table.SplitColumn(#”Split Column by Delimiter”,”Column1.1″,Splitter.SplitTextByEachDelimiter({“:”}, QuoteStyle.Csv, false),{“Column1.1.1”, “Column1.1.2″}),
    #”Split Column by Delimiter2″ = Table.SplitColumn(#”Split Column by Delimiter1″,”Column1.1.1”,Splitter.SplitTextByEachDelimiter({“.”}, QuoteStyle.Csv, false),{“Column1.1.1.1”, “Column1.1.1.2″}),
    #”Changed Type” = Table.TransformColumnTypes(#”Split Column by Delimiter2″,{{“Column1.1.1.1”, type text}, {“Column1.1.1.2”, type text}, {“Column1.1.2”, Int64.Type}, {“Column1.2″, type text}}),
    #”Removed Columns” = Table.RemoveColumns(#”Changed Type”,{“Column1.1.1.1″}),
    #”Split Column by Delimiter3″ = Table.SplitColumn(#”Removed Columns”,”Column1.1.1.2″,Splitter.SplitTextByEachDelimiter({” “}, QuoteStyle.Csv, false),{“Column1.1.1.2.1”, “Column1.1.1.2.2″}),
    #”Changed Type1″ = Table.TransformColumnTypes(#”Split Column by Delimiter3”,{{“Column1.1.1.2.1”, type text}, {“Column1.1.1.2.2″, type text}}),
    #”Removed Columns1″ = Table.RemoveColumns(#”Changed Type1”,{“Column1.1.1.2.1″}),
    #”Renamed Columns” = Table.RenameColumns(#”Removed Columns1″,{{“Column1.1.1.2.2”, “Level”}, {“Column1.1.2”, “Trace Id”}}),
    #”Parsed JSON” = Table.TransformColumns(#”Renamed Columns”,{{“Column1.2″, Json.Document}}),
    #”Expanded Column1.2″ = Table.ExpandRecordColumn(#”Parsed JSON”, “Column1.2”, {“Start”, “Action”, “ProductVersion”, “ActivityId”, “Process”, “Pid”, “Tid”, “Duration”, “DbProviderName”, “Exception”, “CommandText”, “ResponseFieldCount”, “evaluationID”}, {“Start”, “Action”, “ProductVersion”, “ActivityId”, “Process”, “Pid”, “Tid”, “Duration”, “DbProviderName”, “Exception”, “CommandText”, “ResponseFieldCount”, “evaluationID”}),
    #”Changed Type2″ = Table.TransformColumnTypes(#”Expanded Column1.2″,{{“Start”, type datetime}, {“Duration”, type duration}}),
    #”Calculated Total Seconds” = Table.TransformColumns(#”Changed Type2″,{{“Duration”, Duration.TotalSeconds}}),
    #”Changed Type3″ = Table.TransformColumnTypes(#”Calculated Total Seconds”,{{“evaluationID”, Int64.Type}, {“ResponseFieldCount”, Int64.Type}, {“Tid”, Int64.Type}, {“Pid”, Int64.Type}}),
    #”Added Index” = Table.AddIndexColumn(#”Changed Type3″, “Index”, 1, 1),
    #”Renamed Columns1″ = Table.RenameColumns(#”Added Index”,{{“Index”, “Log Id”}, {“evaluationID”, “EvaluationId”}}),
    #”Duplicated Column” = Table.DuplicateColumn(#”Renamed Columns1″, “Action”, “Action – Copy”),
    #”Renamed Columns2″ = Table.RenameColumns(#”Duplicated Column”,{{“Action – Copy”, “Action Detail”}}),
    #”Reordered Columns” = Table.ReorderColumns(#”Renamed Columns2″,{“Log File”, “Log File Date”, “Level”, “Trace Id”, “Start”, “Action”, “Action Detail”, “ProductVersion”, “ActivityId”, “Process”, “Pid”, “Tid”, “Duration”, “DbProviderName”, “Exception”, “CommandText”, “ResponseFieldCount”, “EvaluationId”, “Log Id”}),
    #”Split Column by Delimiter4″ = Table.SplitColumn(#”Reordered Columns”,”Action”,Splitter.SplitTextByDelimiter(“/”, QuoteStyle.Csv),{“Action.1”, “Action.2”, “Action.3”, “Action.4”, “Action.5”, “Action.6″}),
    #”Changed Type4″ = Table.TransformColumnTypes(#”Split Column by Delimiter4”,{{“Action.1”, type text}, {“Action.2”, type text}, {“Action.3”, type text}, {“Action.4”, type text}, {“Action.5”, type text}, {“Action.6″, type text}}),
    #”Duplicated Column1″ = Table.DuplicateColumn(#”Changed Type4”, “Start”, “Start – Copy”),
    #”Changed Type5″ = Table.TransformColumnTypes(#”Duplicated Column1″,{{“Start – Copy”, type time}}),
    #”Renamed Columns3″ = Table.RenameColumns(#”Changed Type5″,{{“Start – Copy”, “Hour”}, {“Start”, “Date”}}),
    #”Changed Type6″ = Table.TransformColumnTypes(#”Renamed Columns3″,{{“Date”, type date}}),
    #”Filtered Rows1″ = Table.SelectRows(#”Changed Type6″, each ([Level] null and [Level] “”))
    in
    #”Filtered Rows1″

  4. Skype has opened up its online-structured consumer beta to
    the entire world, soon after starting it largely within the U.S.

    and U.K. previously this four weeks. Skype for Web also now can handle Chromebook and Linux for immediate text messaging conversation (no voice and
    video however, those need a connect-in set up).

    The expansion in the beta brings support for an extended listing of
    spoken languages to assist reinforce that
    international user friendliness

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s