Monitor and optimize on-premises data gateway performance - Microsoft createdpbix has missing fields

[stty1244]

I am following this article - https://learn.microsoft.com/en-us/data-integration/gateway/service-gateway-performance

and setting up the OnPremisesDataGatewayLogs.pbit

When I refresh and look at the visuals, it has various broken ones using these values - 

Query is QueryStartReport - with missing fields such as {ServiceName, Host Context Type, Dataflow Id, DatasetID, WorkspaceID}

I am pointing the pbix at C:\Windows\ServiceProfiles\PBIEgwService\AppData\Local\Microsoft\On-premises data gateway\Report

Is there something wrong with the template or what am I missing?

[Anonymous · Accepted solution]

Hi @stty1244 

Here are some steps for your reference:

Ensure that the 'ReportFilePath' in the configuration file matches the path where your log files are stored.

As part of the M-query, the Query "ServiceTraceContexts", which tries to create an new field named QueryType . As there is already a field named QueryType, This operation fails, so you can update the M-query, modify the 'QueryType' header to 'QueryTypes' in the M-query:

let
    Source = Folder.Files(#"Folder Path"),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Name], "QueryStartReport") and [Extension] = ".log"),
    #"CsvDoc" = Table.AddColumn(#"Filtered Rows", "csvDocColumn", each try Table.PromoteHeaders(Csv.Document([Content], {"GatewayObjectId", "RequestId", "DataSource", "QueryTrackingId", "QueryExecutionStartTimeUTC", "QueryType", "QueryText", "EvaluationContext"}))),
    #"Expanded csvDocColumn" = Table.ExpandRecordColumn(CsvDoc, "csvDocColumn", {"HasError", "Value"}, {"csvDocColumn.HasError", "csvDocColumn.Value"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded csvDocColumn", each [csvDocColumn.HasError] = false),
    #"Expanded csvDocColumn.Value" = Table.ExpandTableColumn(#"Filtered Rows1", "csvDocColumn.Value", {"GatewayObjectId", "RequestId", "DataSource", "QueryTrackingId", "QueryExecutionStartTimeUTC", "QueryType", "QueryText", "EvaluationContext"}, {"GatewayObjectId", "RequestId", "DataSource", "QueryTrackingId", "QueryExecutionStartTimeUTC", "QueryType", "QueryText", "EvaluationContext"}),
    #"Added Index" = Table.AddIndexColumn(#"Expanded csvDocColumn.Value", "Index", 1, 1),
    #"Removed Columns" = Table.RemoveColumns(#"Added Index",{"Content", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path", "csvDocColumn.HasError"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Name", "Source.Name"}}),
   #"JsonDoc" = Table.AddColumn(#"Renamed Columns", "jsonDocColumn", each try Json.Document([DataSource])),
    #"Expanded jsonDocColumn" = Table.ExpandRecordColumn(JsonDoc, "jsonDocColumn", {"HasError", "Value"}, {"HasError", "Value"}),
    #"Filtered Rows2" = Table.SelectRows(#"Expanded jsonDocColumn", each [HasError] = false),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows2",{"DataSource"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"Value", "DataSource"}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Renamed Columns1",{"HasError"}),
    #"Expanded DataSource" = Table.ExpandListColumn(#"Removed Columns2", "DataSource"),
    #"Parsed JSON1" = Table.TransformColumns(#"Expanded DataSource",{{"DataSource", Json.Document}}),
    #"Expanded DataSource1 - kind" = Table.AddColumn(#"Parsed JSON1", "DataSource.kind", each Record.FieldOrDefault([DataSource], "kind") ?? Record.FieldOrDefault([DataSource], "ConnectorType")),
    #"Expanded DataSource1 - path" = Table.AddColumn(#"Expanded DataSource1 - kind", "DataSource.path", each Record.FieldOrDefault([DataSource], "path") ?? Text.Combine(List.RemoveFirstN(Record.FieldValues([DataSource]), 1), ";")),
    #"Added Custom" = Table.AddColumn(#"Expanded DataSource1 - path", "QueryText1", each Binary.FromText([QueryText], BinaryEncoding.Base64)),
    #"Transformed Column" = Table.TransformColumns(#"Added Custom",{{"QueryText1", Text.FromBinary}}),
    #"Removed Columns3" = Table.RemoveColumns(#"Transformed Column",{"QueryText"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns3",{{"QueryText1", "QueryText"}}),
     #"Renamed Columns3" = Table.RenameColumns(#"Renamed Columns2",{{"QueryType", "QueryTypes"}}),
    #"Grouped Rows" = Table.Group(#"Renamed Columns3", {"Index", "GatewayObjectId", "Source.Name", "RequestId", "QueryTrackingId", "QueryExecutionStartTimeUTC",  "QueryTypes", "QueryText", "EvaluationContext"}, {{"Count", each Table.RowCount(_), type number}, {"DataSourceType", each Text.Combine([DataSource.kind],","), type text}, {"DataSource", each Text.Combine([DataSource.path],","), type text}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Grouped Rows",{{"Source.Name", type text}, {"GatewayObjectId", type text}, {"RequestId", type text}, {"DataSource", type text}, {"QueryTrackingId", type text}, {"QueryExecutionStartTimeUTC", type datetime}, {"QueryTypes", type text}, {"QueryText", type text}}, "en-us"),
    #"Merged Queries" = Table.FuzzyNestedJoin(#"Changed Type", {"QueryTrackingId"}, QueryExecutionReport, {"QueryTrackingId"}, "QueryExecutionReport", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=true]),
    #"Expanded QueryExecutionReport" = Table.ExpandTableColumn(#"Merged Queries", "QueryExecutionReport", {"QueryExecutionEndTimeUTC", "DataReadingAndSerializationDuration(ms)", "SpoolingDiskWritingDuration(ms)", "SpoolingDiskReadingDuration(ms)", "SpoolingTotalDataSize(byte)", "DataProcessingEndTimeUTC", "DataProcessingDuration(ms)", "Success", "ErrorMessage"}, {"QueryExecutionEndTimeUTC", "DataReadingAndSerializationDuration(ms)", "SpoolingDiskWritingDuration(ms)", "SpoolingDiskReadingDuration(ms)", "SpoolingTotalDataSize(byte)", "DataProcessingEndTimeUTC", "DataProcessingDuration(ms)", "Success", "ErrorMessage"}),
    #"Replaced Value" = let
#"step1"=Table.ReplaceValue(#"Expanded QueryExecutionReport","null","{""serviceTraceContexts"": [{""serviceName"": ""null"",""traceIds"": [{""key"": ""NoKey"",""value"": ""null""}]}]}",Replacer.ReplaceValue,{"EvaluationContext"}),
#"step2"=Table.ReplaceValue(#"step1",null,"{""serviceTraceContexts"": [{""serviceName"": ""null"",""traceIds"": [{""key"": ""NoKey"",""value"": ""null""}]}]}",Replacer.ReplaceValue,{"EvaluationContext"})
in #"step2",
    #"Parsed JSON" = Table.TransformColumns(#"Replaced Value",{{"EvaluationContext", Json.Document}}),
    #"Expanded EvaluationContext" = Table.ExpandRecordColumn(#"Parsed JSON", "EvaluationContext", {"serviceTraceContexts"}, {"EvaluationContext.serviceTraceContexts"}),
    #"Expanded EvaluationContext.serviceTraceContexts" = Table.ExpandListColumn(#"Expanded EvaluationContext", "EvaluationContext.serviceTraceContexts"),
    #"Expanded EvaluationContext.serviceTraceContexts1" = Table.ExpandRecordColumn(#"Expanded EvaluationContext.serviceTraceContexts", "EvaluationContext.serviceTraceContexts", {"serviceName", "traceIds"}, {"EvaluationContext.serviceTraceContexts.serviceName", "EvaluationContext.serviceTraceContexts.traceIds"}),
    #"Expanded EvaluationContext.serviceTraceContexts.traceIds" = Table.ExpandListColumn(#"Expanded EvaluationContext.serviceTraceContexts1", "EvaluationContext.serviceTraceContexts.traceIds"),
    #"Expanded EvaluationContext.serviceTraceContexts.traceIds1" = Table.ExpandRecordColumn(#"Expanded EvaluationContext.serviceTraceContexts.traceIds", "EvaluationContext.serviceTraceContexts.traceIds", {"key", "value"}, {"EvaluationContext.serviceTraceContexts.traceIds.key", "EvaluationContext.serviceTraceContexts.traceIds.value"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded EvaluationContext.serviceTraceContexts.traceIds1", List.Distinct(#"Expanded EvaluationContext.serviceTraceContexts.traceIds1"[EvaluationContext.serviceTraceContexts.traceIds.key]), "EvaluationContext.serviceTraceContexts.traceIds.key", "EvaluationContext.serviceTraceContexts.traceIds.value"),
    #"Renamed Columns4" = Table.RenameColumns(#"Pivoted Column",{{"EvaluationContext.serviceTraceContexts.serviceName", "ServiceName"}})
    //#"Removed Columns4" = Table.RemoveColumns(#"Renamed Columns4",{"NoKey"})
in
    #"Renamed Columns4"

Hope this can help,

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[stty1244]

Hi Zhengdong Xu,

thanks for your reply.

There is not a M-query called  "ServiceTraceContexts" in the pbix. I downloaded it from the Microsoft site as a pbit and it has run through OK with only those visuals I mentioned not working.

I only have these appearing - 

Should I add this as an additional M-query and change the visuals? Or have I got the wrong pbit?

I am adding it as a new M-query to my pbix. I am updating the visuals where they appear in the new M-query. There are still a couple missing - 

• Dataflow Id
• Host Context Type

i.e. 

Thanks for your help, its much appreciated.

Kind regards,

Tom