Power BI Desktop Trace Logs Analyser

Posted in Power BITagged , ,

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

26 thoughts on “Power BI Desktop Trace Logs Analyser”

  2. 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…)

    Reply

  4. 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.

    Reply

  5. Hi,
    I am unable to change the datsource in Var_LogFolder variable and also in DataSource Settings.
    Please help!

    Reply

  7. 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″

    Reply

  8. 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

    Reply

  13. Hi there,
    I’m getting the following error after changing the var_logfolder variable: “Expression.Error: Couldn’t convert a value of type List in type Text.”
    The path the logs are being saved by Power BI is C:\Users\eleonora\Microsoft\Power BI Desktop Store App\Traces.
    Any idea of how to fix it?
    Thanks,
    Eleonora.

    Reply

  14. Is this only error logs or do they contain data regarding usage and the user as well?
    Thanks
    Erik Liffner

    Reply

  17. Hi, I’m getting following error while doing refresh (after setting my trace path)

    “We cannot convert a value of type List to type Text.”

    Reply
  18. Pingback: Chris Webb's BI Blog: Power BI Diagnostics, Trace Logs And Query Execution Times (Again) Chris Webb's BI Blog

  20. Have looked in both Windows explorer\file and Outlook 365\file. There is no diagnostics link that I can find. Any other ideas?

    Reply

Leave a comment