Azure 搜索统计信息 Power BI [英] Azure Search statistics Power BI

问题描述

我正在使用存储帐户收集 Azure 搜索统计信息，当前设置超过一周

I'm using storage account to gather Azure Search statistics with current settings by more than one week

和 Power BI 查看报告.问题是，即使我导入了 20 天的数据，我也无法在搜索时间报告"中看到它，但无论我选择的时间段如何，都只能看到一天.

And Power BI to view reports. The issue is that even when I'm importing data for 20 days I can't see it in "Search over time report", but only for one day regardless on period I'm selecting.

在这种报告上，数据导入或/和日期范围选择的正确方法是什么?谢谢

What is right way of data import or/and date range selecting on this kind of report? Thank's

推荐答案

问题是由于某些查询的搜索词包含无效的转义序列(^ 和 ~)，导致 JSON 解析器失败.

The problem is caused by some queries with search terms that contain invalid escape sequences (^ and ~), which cause the JSON parser to fail.

这是 Power BI 内容包的限制.使其工作的选项如下:

This is a limitation of the Power BI content pack. The options to make it work are the following:

• 在发送查询之前删除反斜杠
• 在发送查询之前转义反斜杠 ()
• 从 JSON blob 本身中删除有问题的字符

如果这些选项不适合你，你将无法使用 Power BI 内容包，但你仍然可以在 Power BI Desktop 中分析数据.下面是一个为 JSON 解析器添加错误处理的查询.这种方法的局限性在于，即使只有一个有问题的查询，它也会忽略完整的文件(1 小时的数据)

If those options don't work for you, you won't be able to use the Power BI content pack, but you can still analyze the data in Power BI Desktop. Below is a query that add error handling for the JSON parser. The limitation of this approach, is that it will ignore full files (1 hour of data) even if there's only one problematic query

Power BI Desktop 查询:

Power BI Desktop query:

• ACCOUNTNAME:您的存储帐户的名称
• DAYS数据天数

• ACCOUNTNAME: name of your storage account
• DAYS number of days of data

    let Source = AzureStorage.Blobs("https://ACCOUNTNAME.blob.core.windows.net"),
    #"insights-logs-operationlogs" = Source{[Name="insights-logs-operationlogs"]}[Data],
    #"Sorted Rows" = Table.Sort(#"insights-logs-operationlogs",{{"Date modified", Order.Descending}}),
    #"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each Date.IsInPreviousNDays([Date modified], DAYS) or DateTime.Date([Date modified]) = DateTime.Date(DateTimeZone.UtcNow())),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}),
    #"Parsed JSON" = Table.TransformColumns(#"Removed Columns",{},Json.Document),
    #"Replaced Errors1" = Table.ReplaceErrorValues(#"Parsed JSON", {{"Content", ""}}),
    #"Filtered Rows1" = Table.SelectRows(#"Replaced Errors1", each [Content] <> null and [Content] <> ""),
    #"Expanded Content" = Table.ExpandRecordColumn(#"Filtered Rows1", "Content", {"records"}, {"records"}),
    #"Expanded records" = Table.ExpandListColumn(#"Expanded Content", "records"),
    #"Expanded records1" = Table.ExpandRecordColumn(#"Expanded records", "records", {"time", "resourceId", "operationName", "operationVersion", "category", "resultType", "resultSignature", "durationMS", "properties"}, {"time", "resourceId", "operationName", "operationVersion", "category", "resultType", "resultSignature", "durationMS", "properties"}),
    #"Expanded properties" = Table.ExpandRecordColumn(#"Expanded records1", "properties", {"Description", "Query", "IndexName", "Documents"}, {"Description", "Query", "IndexName", "Documents"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded properties",{{"time", "Datetime"}, {"resourceId", "ResourceId"}, {"operationName", "OperationName"}, {"operationVersion", "OperationVersion"}, {"category", "Category"}, {"resultType", "ResultType"}, {"resultSignature", "ResultSignature"}, {"durationMS", "Duration"}}),
    #"Added Custom2" = Table.AddColumn(#"Renamed Columns", "QueryParameters", each Uri.Parts("http://tmp" & [Query])),
    #"Expanded QueryParameters" = Table.ExpandRecordColumn(#"Added Custom2", "QueryParameters", {"Query"}, {"Query.1"}),
    #"Expanded Query.1" = Table.ExpandRecordColumn(#"Expanded QueryParameters", "Query.1", {"search", "$skip", "$top", "$count", "api-version", "searchMode", "$filter"}, {"search", "$skip", "$top", "$count", "api-version", "searchMode", "$filter"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Query.1",{"OperationVersion"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns1",{{"Datetime", type datetimezone}, {"ResourceId", type text}, {"OperationName", type text}, {"Category", type text}, {"ResultType", type text}, {"ResultSignature", type text}, {"Duration", Int64.Type}, {"Description", type text}, {"Query", type text}, {"IndexName", type text}, {"Documents", Int64.Type}, {"search", type text}, {"$skip", Int64.Type}, {"$top", Int64.Type}, {"$count", type logical}, {"api-version", type text}, {"searchMode", type text}, {"$filter", type text}}),
    #"Inserted Date" = Table.AddColumn(#"Changed Type", "Date", each DateTime.Date([Datetime]), type date),
    #"Duplicated Column" = Table.DuplicateColumn(#"Inserted Date", "ResourceId", "Copy of ResourceId"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column","Copy of ResourceId",Splitter.SplitTextByEachDelimiter({"/"}, null, true),{"Copy of ResourceId.1", "Copy of ResourceId.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Copy of ResourceId.1", type text}, {"Copy of ResourceId.2", type text}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Changed Type1",{"Copy of ResourceId.1"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns2",{{"Copy of ResourceId.2", "ServiceName"}}),
    #"Lowercased Text" = Table.TransformColumns(#"Renamed Columns1",{{"ServiceName", Text.Lower}}),
    #"Added Custom" = Table.AddColumn(#"Lowercased Text", "DaysFromToday", each Duration.Days(DateTimeZone.UtcNow() - [Datetime])),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"DaysFromToday", Int64.Type}}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type2", {{"search", null}, {"$skip", null}, {"$top", null}, {"$count", null}, {"api-version", null}, {"searchMode", null}, {"$filter", null}}),
    #"Filtered Rows2" = Table.SelectRows(#"Replaced Errors", each true)
in
    #"Filtered Rows2"

这篇关于Azure 搜索统计信息 Power BI的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！