Extracting Azure Analysis Services Metrics from your Log Analytics workspace into PowerBI
I've been working in and around SQL for over 3 decades.
I am currently working on making our business intelligence systems self-driven by using an event-based architecture. This involves providing curated data in some of the largest SQL Azure Analysis Services cubes ever built (over 6 billion records in 1 table alone).
I've developed several APIs leveraging a serverless Azure architecture to provide real-time data to various internal processes and projects.
Currently working on Project Dormouse; Durable select messaging queue that enables polling processes to extract specific messages.
We have numerous Azure Analysis Services servers running in the cloud and needed a way to have near-real time monitoring of the queries being run. This KQL query will enable you to pull all the user/query information necessary to manage these servers, into a PowerBI report.
/* The exported Power Query Formula Language (M Language ) can be used with Power Query in Excel and Power BI Desktop. For Power BI Desktop follow the instructions below:
Download Power BI Desktop from https://powerbi.microsoft.com/desktop/
In Power BI Desktop select: 'Get Data' -> 'Blank Query'->'Advanced Query Editor'
Paste the M Language script into the Advanced Query Editor and select 'Done' */
let AnalyticsQuery = let Source = Json.Document(Web.Contents("https://api.loganalytics.io/v1/subscriptions/your subscription here/query", [Query=[#"query"="AzureDiagnostics | where ResourceProvider == 'MICROSOFT.ANALYSISSERVICES' and Resource in ('server1','server2') and OperationName =='QueryEnd' and NTDomainName_s == 'AzureAD' | order by StartTime_t",#"x-ms-app"="AzureFirstPBI",#"timespan"="PT1H",#"scope"="hierarchy",#"prefer"="ai.response-thinning=true"],Timeout=#duration(0,0,4,0)])), TypeMap = #table( { "AnalyticsTypes", "Type" }, { { "string", Text.Type }, { "int", Int32.Type }, { "long", Int64.Type }, { "real", Double.Type }, { "timespan", Duration.Type }, { "datetime", DateTimeZone.Type }, { "bool", Logical.Type }, { "guid", Text.Type }, { "dynamic", Text.Type } }), DataTable = Source[tables]{0}, Columns = Table.FromRecords(DataTable[columns]), ColumnsWithType = Table.Join(Columns, {"type"}, TypeMap , {"AnalyticsTypes"}), Rows = Table.FromRows(DataTable[rows], Columns[name]), Table = Table.TransformColumnTypes(Rows, Table.ToList(ColumnsWithType, (c) => { c{0}, c{3}})) in Table in AnalyticsQuery
The highlighted section requires you to input your values. In the above example, this will monitor 2 servers. You can add more or split this into individual servers/query.
