Import Mode with SP - Number of call in SQL Server Trace

[Anonymous]

Hello everyone,

I have a problem when using the Import mode on Power BI. 

I use a Stored Procedure for import and by studying the SQL Server trace on this SP, I was able to observe that for data import, the SP seems to be executed 3+ times by Power BI for import. 

I understand that it can be called once for the preview and a second time for the import but I don't understand why there are so many calls (and therefore multiplying the base load).

Below is an overview of the trace. (confidential data has been deleted) and an overview of my Power BI data model. 

Is there a way to optimize and limit the number of calls? (While keeping the import in Power BI).

Could it be linked with my subsequent merge after the SP import? 

Thank you. 

let

Source = Sql.Database("Server", "Database", [Query="EXEC MYSP @PId = 6774,#(lf)#(tab)#(tab)@PIncId = 908,#(lf)#(tab)#(tab)@FromDate = N'2019-01-01',#(lf)#(tab)#(tab)@ToDate = N'2019-12-31'#(lf)"]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date q", type datetime}, {"Date dd", type datetime},{"Date rp",type datetime}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"S"}, InternalNameQuery, {"txtValue"}, "Query1", JoinKind.LeftOuter),
#"Expanded Query1" = Table.ExpandTableColumn(#"Merged Queries", "Query1", {"IName"}, {"Query1.InternalName"}), /*Merge with an other SQL Query*/
#"Renamed Columns" = Table.RenameColumns(#"Expanded Query1",{{"Query1.InternalName", "Analytical Service Internal Name"}}),
#"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"clientCode", Order.Ascending}, {"Type AT", Order.Ascending},{"Date de retrait qualiticien",Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1),
#"Merged Queries1" = Table.NestedJoin(#"Added Index", {"Analytical Service Internal Name"}, #"MapingSA-AT", {"Service A."}, "MapingSA-AT", JoinKind.LeftOuter),/*Merge with a Excel File*/
#"Expanded MapingSA-AT" = Table.ExpandTableColumn(#"Merged Queries1", "MapingSA-AT", {"TypeAT"}, {"MapingSA-AT.TypeAT"}),
#"Merged Queries2" = Table.NestedJoin(#"Expanded MapingSA-AT", {"Code visite"}, EPDM, {"Visit Code"}, "EPDM", JoinKind.LeftOuter),
#"Expanded EPDM" = Table.ExpandTableColumn(#"Merged Queries2", "EPDM", {"PUID"}, {"EPDM.PUID"}),/*Merge with an Excel File*/
#"Renamed Columns1" = Table.RenameColumns(#"Expanded EPDM",{{"EPDM.PUID", "Pot De Mission PUID"}})
in
#"Renamed Columns1"

[Anonymous]

Okay. So it seems that this is inherent to Power BI and that there is no way to "force" Power BI to execute only once the request for import? Am I right?

Thank you. 

Romain

[v-lid-msft]

Hi @Anonymous ,

Based on my test, just one source query will cause multi nuber of call in SQL Server Trace for the essentials information. You can just run your first two queries to test the result.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.