Difference between the two logs generated by PowerBi.

SushmaMudayappa — Mon, 16 Oct 2023 09:20:06 GMT

Hi Team,
Wanted clarity with the two logs attached. First log has the query with limit clause , second log doesn't have the limit clause.
But we see a change in the flow. Can someone briefly explain about.

#first log

ObjectCacheSessions/Data/CacheStats/Size {"Start":"2023-10-09T06:21:52.9058892Z","Action":"ObjectCacheSessions/Data/CacheStats/Size","entryCount":"1","totalSize":"24","ProductVersion":"2.121.169.0 (23.09)","ActivityId":"00000000-0000-0000-0000-000000000000","Process":"Microsoft.Mashup.Container.NetFX45","Pid":32112,"Tid":1,"Duration":"00:00:00.0008726"} ObjectCacheSessions/Data/CacheStats/Access {"Start":"2023-10-09T06:21:52.9086791Z","Action":"ObjectCacheSessions/Data/CacheStats/Access","hits":"0","requests":"1","hitrate":"0","ProductVersion":"2.121.169.0 (23.09)","ActivityId":"00000000-0000-0000-0000-000000000000","Process":"Microsoft.Mashup.Container.NetFX45","Pid":32112,"Tid":1,"Duration":"00:00:00.0000482"} SimpleDocumentEvaluator/GetResult/Compile {"Start":"2023-10-09T06:21:53.0454434Z","Action":"SimpleDocumentEvaluator/GetResult/Compile","HostProcessId":"23320","PartitionKey":"Section1/Query1/Source","Expression":"let\r\n _t = Section1!Query1\r\nin\r\n if _t is table then Table.FirstN(_t, 1000) meta Value.Metadata(_t)\r\n else if _t is list then List.FirstN(_t, 1000) meta Value.Metadata(_t)\r\n else _t","ProductVersion":"2.121.169.0 (23.09)","ActivityId":"9ed0e21c-b30b-4d20-80f3-2b68379bf0d2","Process":"Microsoft.Mashup.Container.NetFX45","Pid":32112,"Tid":1,"Duration":"00:00:00.4476034"} ObjectCache/Data/CacheStats/Size {"Start":"2023-10-09T06:21:54.0170048Z","Action":"ObjectCache/Data/CacheStats/Size","HostProcessId":"23320","entryCount":"1","totalSize":"836","ProductVersion":"2.121.169.0 (23.09)","ActivityId":"9ed0e21c-b30b-4d20-80f3-2b68379bf0d2","Process":"Microsoft.Mashup.Container.NetFX45","Pid":32112,"Tid":1,"Duration":"00:00:00.0000298"} ObjectCache/Data/CacheStats/Access {"Start":"2023-10-09T06:21:54.0170701Z","Action":"ObjectCache/Data/CacheStats/Access","HostProcessId":"23320","hits":"0","requests":"2","hitrate":"0","ProductVersion":"2.121.169.0 (23.09)","ActivityId":"9ed0e21c-b30b-4d20-80f3-2b68379bf0d2","Process":"Microsoft.Mashup.Container.NetFX45","Pid":32112,"Tid":1,"Duration":"00:00:00.0000096"} ObjectCache/Data/CacheStats/Size {"Start":"2023-10-09T06:21:54.1384327Z","Action":"ObjectCache/Data/CacheStats/Size","HostProcessId":"23320","entryCount":"2","totalSize":"1179","ProductVersion":"2.121.169.0 (23.09)","ActivityId":"9ed0e21c-b30b-4d20-80f3-2b68379bf0d2","Process":"Microsoft.Mashup.Container.NetFX45","Pid":32112,"Tid":1,"Duration":"00:00:00.0000159"} ObjectCache/Data/CacheStats/Access {"Start":"2023-10-09T06:21:54.1384755Z","Action":"ObjectCache/Data/CacheStats/Access","HostProcessId":"23320","hits":"0","requests":"4","hitrate":"0","ProductVersion":"2.121.169.0 (23.09)","ActivityId":"9ed0e21c-b30b-4d20-80f3-2b68379bf0d2","Process":"Microsoft.Mashup.Container.NetFX45","Pid":32112,"Tid":1,"Duration":"00:00:00.0000090"} Engine/IO/Odbc/Command/Prepare {"Start":"2023-10-09T06:21:59.3674716Z","Action":"Engine/IO/Odbc/Command/Prepare","ResourceKind":"AmazonAthena","ResourcePath":"Simba Athena","HostProcessId":"23320","PartitionKey":"Section1/Query1/Source","CommandText":"select keycolumn,column1 from AwsDataCatalog.sen.wlongvarchar_table limit 1000","Statement":"2805389631136","ProductVersion":"2.121.169.0 (23.09)","ActivityId":"9ed0e21c-b30b-4d20-80f3-2b68379bf0d2","Process":"Microsoft.Mashup.Container.NetFX45","Pid":32112,"Tid":1,"Duration":"00:00:05.6333227"}

#second log

ObjectCacheSessions/Data/CacheStats/Size {"Start":"2023-10-11T10:39:39.0618907Z","Action":"ObjectCacheSessions/Data/CacheStats/Size","entryCount":"1","totalSize":"24","ProductVersion":"2.121.169.0 (23.09)","ActivityId":"00000000-0000-0000-0000-000000000000","Process":"Microsoft.Mashup.Container.NetFX45","Pid":18204,"Tid":1,"Duration":"00:00:00.0006009"} ObjectCacheSessions/Data/CacheStats/Access {"Start":"2023-10-11T10:39:39.0638633Z","Action":"ObjectCacheSessions/Data/CacheStats/Access","hits":"0","requests":"1","hitrate":"0","ProductVersion":"2.121.169.0 (23.09)","ActivityId":"00000000-0000-0000-0000-000000000000","Process":"Microsoft.Mashup.Container.NetFX45","Pid":18204,"Tid":1,"Duration":"00:00:00.0000370"} SimpleDocumentEvaluator/GetResult/Compile {"Start":"2023-10-11T10:39:39.1635262Z","Action":"SimpleDocumentEvaluator/GetResult/Compile","HostProcessId":"16136","PartitionKey":"Section1/Query1/Source","Expression":"let\r\n _t = Section1!Query1\r\nin\r\n if _t is table then Table.FirstN(_t, 1000) meta Value.Metadata(_t)\r\n else if _t is list then List.FirstN(_t, 1000) meta Value.Metadata(_t)\r\n else _t","ProductVersion":"2.121.169.0 (23.09)","ActivityId":"4e07d457-4fc2-4405-957e-35e21fd65597","Process":"Microsoft.Mashup.Container.NetFX45","Pid":18204,"Tid":1,"Duration":"00:00:00.3046526"} ObjectCache/Data/CacheStats/Size {"Start":"2023-10-11T10:39:39.8194814Z","Action":"ObjectCache/Data/CacheStats/Size","HostProcessId":"16136","entryCount":"1","totalSize":"480","ProductVersion":"2.121.169.0 (23.09)","ActivityId":"4e07d457-4fc2-4405-957e-35e21fd65597","Process":"Microsoft.Mashup.Container.NetFX45","Pid":18204,"Tid":1,"Duration":"00:00:00.0000170"} ObjectCache/Data/CacheStats/Access {"Start":"2023-10-11T10:39:39.8195639Z","Action":"ObjectCache/Data/CacheStats/Access","HostProcessId":"16136","hits":"0","requests":"2","hitrate":"0","ProductVersion":"2.121.169.0 (23.09)","ActivityId":"4e07d457-4fc2-4405-957e-35e21fd65597","Process":"Microsoft.Mashup.Container.NetFX45","Pid":18204,"Tid":1,"Duration":"00:00:00.0000080"} ObjectCache/Data/CacheStats/Size {"Start":"2023-10-11T10:39:39.9025869Z","Action":"ObjectCache/Data/CacheStats/Size","HostProcessId":"16136","entryCount":"2","totalSize":"823","ProductVersion":"2.121.169.0 (23.09)","ActivityId":"4e07d457-4fc2-4405-957e-35e21fd65597","Process":"Microsoft.Mashup.Container.NetFX45","Pid":18204,"Tid":1,"Duration":"00:00:00.0000688"} ObjectCache/Data/CacheStats/Access {"Start":"2023-10-11T10:39:39.9026820Z","Action":"ObjectCache/Data/CacheStats/Access","HostProcessId":"16136","hits":"0","requests":"4","hitrate":"0","ProductVersion":"2.121.169.0 (23.09)","ActivityId":"4e07d457-4fc2-4405-957e-35e21fd65597","Process":"Microsoft.Mashup.Container.NetFX45","Pid":18204,"Tid":1,"Duration":"00:00:00.0000193"} SimpleDocumentEvaluator/GetResult/Evaluate {"Start":"2023-10-11T10:39:39.4682107Z","Action":"SimpleDocumentEvaluator/GetResult/Evaluate","HostProcessId":"16136","PartitionKey":"Section1/Query1/Source","ProductVersion":"2.121.169.0 (23.09)","ActivityId":"4e07d457-4fc2-4405-957e-35e21fd65597","Process":"Microsoft.Mashup.Container.NetFX45","Pid":18204,"Tid":1,"Duration":"00:00:00.4608203"} SimpleDocumentEvaluator/GetResult<IPreviewValueSource> {"Start":"2023-10-11T10:39:39.1583981Z","Action":"SimpleDocumentEvaluator/GetResult<IPreviewValueSource>","HostProcessId":"16136","PartitionKey":"Section1/Query1/Source","ProductVersion":"2.121.169.0 (23.09)","ActivityId":"4e07d457-4fc2-4405-957e-35e21fd65597","Process":"Microsoft.Mashup.Container.NetFX45","Pid":18204,"Tid":1,"Duration":"00:00:00.8461171"} FirewallDocumentEvaluator/GetResult<Microsoft.Mashup.Evaluator.Interface.IPreviewValueSource> {"Start":"2023-10-11T10:39:39.1276870Z","Action":"FirewallDocumentEvaluator/GetResult<Microsoft.Mashup.Evaluator.Interface.IPreviewValueSource>","HostProcessId":"16136","ProductVersion":"2.121.169.0 (23.09)","ActivityId":"4e07d457-4fc2-4405-957e-35e21fd65597","Process":"Microsoft.Mashup.Container.NetFX45","Pid":18204,"Tid":1,"Duration":"00:00:00.8026896"} ObjectCacheSessions/Data/CacheStats/Size {"Start":"2023-10-11T10:40:56.4290056Z","Action":"ObjectCacheSessions/Data/CacheStats/Size","entryCount":"2","totalSize":"871","ProductVersion":"2.121.169.0 (23.09)","ActivityId":"00000000-0000-0000-0000-000000000000","Process":"Microsoft.Mashup.Container.NetFX45","Pid":18204,"Tid":1,"Duration":"00:00:00.0000174"} ObjectCacheSessions/Data/CacheStats/Access {"Start":"2023-10-11T10:40:56.4290435Z","Action":"ObjectCacheSessions/Data/CacheStats/Access","hits":"0","requests":"2","hitrate":"0","ProductVersion":"2.121.169.0 (23.09)","ActivityId":"00000000-0000-0000-0000-000000000000","Process":"Microsoft.Mashup.Container.NetFX45","Pid":18204,"Tid":1,"Duration":"00:00:00.0000075"} SimpleDocumentEvaluator/GetResult/Compile {"Start":"2023-10-11T10:40:56.4330101Z","Action":"SimpleDocumentEvaluator/GetResult/Compile","HostProcessId":"16136","PartitionKey":"Section1/Query1/Source","Expression":"let\r\n _t = Section1!Query1\r\nin\r\n if _t is table then Table.FirstN(_t, 0) meta Value.Metadata(_t)\r\n else if _t is list then List.FirstN(_t, 0) meta Value.Metadata(_t)\r\n else _t","ProductVersion":"2.121.169.0 (23.09)","ActivityId":"cf640b47-e18c-4e65-938c-af2ccdf48f0f","Process":"Microsoft.Mashup.Container.NetFX45","Pid":18204,"Tid":1,"Duration":"00:00:00.0100961"} ObjectCache/Data/CacheStats/Size {"Start":"2023-10-11T10:41:01.0612561Z","Action":"ObjectCache/Data/CacheStats/Size","HostProcessId":"16136","entryCount":"1","totalSize":"480","ProductVersion":"2.121.169.0 (23.09)","ActivityId":"cf640b47-e18c-4e65-938c-af2ccdf48f0f","Process":"Microsoft.Mashup.Container.NetFX45","Pid":18204,"Tid":1,"Duration":"00:00:00.0000148"} ObjectCache/Data/CacheStats/Access {"Start":"2023-10-11T10:41:01.0612949Z","Action":"ObjectCache/Data/CacheStats/Access","HostProcessId":"16136","hits":"0","requests":"6","hitrate":"0","ProductVersion":"2.121.169.0 (23.09)","ActivityId":"cf640b47-e18c-4e65-938c-af2ccdf48f0f","Process":"Microsoft.Mashup.Container.NetFX45","Pid":18204,"Tid":1,"Duration":"00:00:00.0000080"} Engine/IO/Odbc/Command/GetTables {"Start":"2023-10-11T10:41:01.0640178Z","Action":"Engine/IO/Odbc/Command/GetTables","ResourceKind":"AmazonAthena","ResourcePath":"Simba Athena","HostProcessId":"16136","PartitionKey":"Section1/Query1/Source","CatalogName":"%","SchemaName":"","TableName":"","TableType":"","Execution":"00:00:00.0240137","Result":"00:00:00.0098332","ProductVersion":"2.121.169.0 (23.09)","ActivityId":"cf640b47-e18c-4e65-938c-af2ccdf48f0f","Process":"Microsoft.Mashup.Container.NetFX45","Pid":18204,"Tid":1,"Duration":"00:00:00.0341164"} DiskCache/Data/CacheStats/Size {"Start":"2023-10-11T10:41:01.1082753Z","Action":"DiskCache/Data/CacheStats/Size","HostProcessId":"16136","entryCount":"1","totalSize":"1024","ProductVersion":"2.121.169.0 (23.09)","ActivityId":"cf640b47-e18c-4e65-938c-af2ccdf48f0f","Process":"Microsoft.Mashup.Container.NetFX45","Pid":18204,"Tid":1,"Duration":"00:00:00.0000135"} DiskCache/Data/CacheStats/Access {"Start":"2023-10-11T10:41:01.1083065Z","Action":"DiskCache/Data/CacheStats/Access","HostProcessId":"16136","hits":"3","requests":"5","hitrate":"0.6","ProductVersion":"2.121.169.0 (23.09)","ActivityId":"cf640b47-e18c-4e65-938c-af2ccdf48f0f","Process":"Microsoft.Mashup.Container.NetFX45","Pid":18204,"Tid":1,"Duration":"00:00:00.0000161"} ObjectCache/Data/CacheStats/Size {"Start":"2023-10-11T10:41:01.1083436Z","Action":"ObjectCache/Data/CacheStats/Size","HostProcessId":"16136","entryCount":"2","totalSize":"823","ProductVersion":"2.121.169.0 (23.09)","ActivityId":"cf640b47-e18c-4e65-938c-af2ccdf48f0f","Process":"Microsoft.Mashup.Container.NetFX45","Pid":18204,"Tid":1,"Duration":"00:00:00.0000024"} ObjectCache/Data/CacheStats/Access {"Start":"2023-10-11T10:41:01.1083498Z","Action":"ObjectCache/Data/CacheStats/Access","HostProcessId":"16136","hits":"0","requests":"8","hitrate":"0","ProductVersion":"2.121.169.0 (23.09)","ActivityId":"cf640b47-e18c-4e65-938c-af2ccdf48f0f","Process":"Microsoft.Mashup.Container.NetFX45","Pid":18204,"Tid":1,"Duration":"00:00:00.0000170"} Engine/IO/Odbc/Command/Prepare {"Start":"2023-10-11T10:41:03.3001873Z","Action":"Engine/IO/Odbc/Command/Prepare","ResourceKind":"AmazonAthena","ResourcePath":"Simba Athena","HostProcessId":"16136","PartitionKey":"Section1/Query1/Source","CommandText":"select keycolumn,column1 from AwsDataCatalog.sen.wlongvarchar_table","Statement":"2820449281040","ProductVersion":"2.121.169.0 (23.09)","ActivityId":"cf640b47-e18c-4e65-938c-af2ccdf48f0f","Process":"Microsoft.Mashup.Container.NetFX45","Pid":18204,"Tid":1,"Duration":"00:00:07.5069672"}

Thanks,

Sushma

Re: Different between the two logs generated by PowerBi.

lbendlin — Sat, 14 Oct 2023 00:46:30 GMT

The first one can come from a developer working in Power Query editor, the second one from the dataset. What problem are you trying to solve?

Re: Different between the two logs generated by PowerBi.

SushmaMudayappa — Sat, 14 Oct 2023 15:06:22 GMT

Hi Ibendlin,

I have designed a custom connector which supports native query, now I would like to add support for limit clause. I have done changes refering to this Parameters for the Odbc.DataSource function - Power Query | Microsoft Learn but I still dont see the Limit clause getting appended to the query I pass.

AstVisitor = [ LimitClause = (skip,take) => if (skip > 0) then error "Skip/Offset not supported" else [ Text = Text.Format("LIMIT #{0}", {take}), Location = "AfterQuerySpecification" ] ],

I was going through the logs, at first the query got appened with limit, later it didnt.
FYI:
I havent made any code changes further.

Re: Different between the two logs generated by PowerBi.

SushmaMudayappa — Mon, 16 Oct 2023 10:32:36 GMT

We have cleared cache as well.
Under --> Options -->Global --> Data Load

At first, limit clause got appened with the query we pass. But later on, it didnt behave as expected.

Could you suggest any changes that needs to be done in power query (.pq) file.

Regards,

Sushma

Re: Different between the two logs generated by PowerBi.

lbendlin — Mon, 16 Oct 2023 15:03:42 GMT

I am not aware of a way to tell Power Query not to use the cache. Maybe set the cache size to zero?

Re: Different between the two logs generated by PowerBi.

SushmaMudayappa — Tue, 17 Oct 2023 14:45:19 GMT

Yes, I tried that. It didnt work either.
But could you suggest, how do we actually desgin a custom connector which supports limit under native query.

Re: Different between the two logs generated by PowerBi.

lbendlin — Tue, 17 Oct 2023 15:15:43 GMT

Sorry, that's outside my area of expertise. Haven't learned about custom connectors yet.

Re: Different between the two logs generated by PowerBi.

Anonymous — Fri, 10 Nov 2023 07:57:24 GMT

HI @SushmaMudayappa,

For enabled 'Native Query' support in custom connector, you can refer the following links:

Implement Native Query support in your Power Query Custom Connectors | Microsoft Power Query

Native query support in the Power Query SDK - Power Query | Microsoft Learn

REference link about how to create custom connector:

Creating your first connector - Hello World - Power Query | Microsoft Learn

Regards,

Xiaoxin Sheng

topic Re: Different between the two logs generated by PowerBi. in Developer

Difference between the two logs generated by PowerBi.

Re: Different between the two logs generated by PowerBi.

Re: Different between the two logs generated by PowerBi.

Re: Different between the two logs generated by PowerBi.

Re: Different between the two logs generated by PowerBi.

Re: Different between the two logs generated by PowerBi.

Re: Different between the two logs generated by PowerBi.

Re: Different between the two logs generated by PowerBi.