Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Myagi-Do
Helper I
Helper I

Connecting Log Analytics to Power BI with authorisation token

Hi there,

 

we are trying to load log analytics data into power bi.

 

We generated the query via export to Power BI in LogAnalytics and set the set notruncation option and a timeout of 59 minutes.

 

We do a direct import because of the authorisation token and the the row count is larger than 500000. The data is expected to increase daily by 500000+ rows. 

 

 

AnalyticsQuery =
let 
    Source = Json.Document(Web.Contents("https://api.loganalytics.io/v1/****/query", 
    [Headers=[Authorization=authToken],
   
    Query=[#"query"="
    set notruncation;
    ApiManagementGatewayLogs
    | where ProductId == ""ABC""
    | where City != ""berlin""
    "
    ,#"x-ms-app"="AzureFirstPBI", #"prefer"="ai.response-thinning=true", 
     #"scope"="hierarchy"],Timeout=#duration(0,0,59,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

 

 

How can we setup the infrastructure porperly for this? Do we need to use another Azure service instead of Power BI to collect the data.

 

Any suggestion is appreciated (with or without power bi premium).

 

Thanks

 

 

 

 

3 REPLIES 3
Adamboer
Responsive Resident
Responsive Resident

It sounds like you are facing issues with loading large amounts of data from Log Analytics into Power BI. One approach you could consider is using Azure Data Factory to move your Log Analytics data into Azure Blob Storage or Azure Data Lake Storage, and then use Power BI to connect to the storage account. This can help with data transformation and management before loading it into Power BI. Alternatively, you could explore using Azure Stream Analytics to stream the data into Power BI in near real-time. Both of these options require some additional setup and configuration, but can help with performance and scalability.

So I've gotten a bit further with my research and it looks like we can change the named user in the Power BI Service to the service principal (this was the reason we needed a token). However this is one step ahead of what I want to do. Is there a way of implementing a token within the kusto query?

v-rzhou-msft
Community Support
Community Support

Hi @Myagi-Do ,

 

Here I suggest you to refer to below offical blogs to learn more details.

For reference:

Using Azure Log Analytics in Power BI (Preview)
Configuring Azure Log Analytics for Power BI (Preview)

Integrate Log Analytics with Power BI

 

Best Regards,
Rico Zhou

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors