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天的数据，在按时间搜索报告"中也看不到它，而无论选择的时间段如何，它都只能看到1天.

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屋！