I have a query orignally written in excel power query targetting an sql table for Microsoft Navision. Recently I have been instructed to start using an Odata feed of the same table. The tables have the same data with slightly different columns (sql has more) and headings. I created an OData version of the query that produces the same output. Within Excel Power Query the SQL version updates in 2.5min the oData 6.5min. I therefore imported both models into Power BI Desktop and now get 1min and 6.5 min respectively (admittedly the excle version is crunch a few reports i havent duplicated in desktop)
So my questions are:
1. is there some known issue with OData refreshes being much slower than SQL or is this specific to the query?
2. Why is the refresh in Power BI Desktop noticably faster than in PQ (for SQL) (I estimate the workbook refresh is 30s of the 2.5min). Again is this usual
Is this somethnig to do with Query folding? Thanks for any advice as never dealt with OData before and new to PBI Desktop
Mike
My 2 queries are below
SQL
let
Source = Sql.Database("ino-sql-02", "INO_Production"),
#"dbo_Production$G_L Entry" = Source{[Schema="dbo",Item="Production$G_L Entry"]}[Data],
#"Renamed Columns" = Table.RenameColumns(#"dbo_Production$G_L Entry",{{"G_L Account No_", "G_L Account No"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"G_L Account No", Int64.Type}, {"Posting Date", type date}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"System-Created Entry", "Prior-Year Entry", "Job No_", "Business Unit Code", "Journal Batch Name", "Reason Code", "Document Date", "External Document No_", "Additional-Currency Amount", "Add_-Currency Debit Amount", "Add_-Currency Credit Amount", "Close Income Statement Dim_ ID", "IC Partner Code", "FA Entry Type", "FA Entry No_", "Prod_ Order No_", "Reversed by Entry No_", "Reversed Entry No_", "Reversed", "Use Tax", "Tax Group Code", "Tax Liable", "Tax Area Code", "timestamp"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Amount", "Amount raw"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns1", "Amount", each [Amount raw]*-1),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Debit Amount", "Credit Amount"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Entry No_", "G_L Account No", "Posting Date", "Document Type", "Document No_", "Description", "Bal_ Account No_", "Amount", "Amount raw", "Global Dimension 1 Code", "Global Dimension 2 Code", "User ID", "Source Code", "Quantity", "VAT Amount", "Gen_ Posting Type", "Gen_ Bus_ Posting Group", "Gen_ Prod_ Posting Group", "Bal_ Account Type", "Transaction No_", "Source Type", "Source No_", "No_ Series", "VAT Bus_ Posting Group", "VAT Prod_ Posting Group", "Dimension Set ID"}),
#"Removed Columns2" = Table.RemoveColumns(#"Reordered Columns",{"Amount raw"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns2",{{"Amount", Currency.Type}}),
#"Renamed Columns2" = Table.RenameColumns(#"Changed Type1",{{"Posting Date", "Posting Date Raw"}}),
#"Added Custom1" = Table.AddColumn(#"Renamed Columns2", "Posting Date", each if [Posting Date Raw]>DateTime.Date(DateTime.LocalNow()) then DateTime.Date(DateTime.LocalNow()) else [Posting Date Raw]),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Posting Date", type date}}),
#"Reordered Columns1" = Table.ReorderColumns(#"Changed Type2",{"Entry No_", "G_L Account No", "Posting Date Raw", "Posting Date", "Document Type", "Document No_", "Description", "Bal_ Account No_", "Amount", "Global Dimension 1 Code", "Global Dimension 2 Code", "User ID", "Source Code", "Quantity", "VAT Amount", "Gen_ Posting Type", "Gen_ Bus_ Posting Group", "Gen_ Prod_ Posting Group", "Bal_ Account Type", "Transaction No_", "Source Type", "Source No_", "No_ Series", "VAT Bus_ Posting Group", "VAT Prod_ Posting Group", "Dimension Set ID"}),
#"Removed Columns3" = Table.RemoveColumns(#"Reordered Columns1",{"Posting Date Raw", "Global Dimension 2 Code", "Transaction No_", "Source Type", "Source No_", "No_ Series", "VAT Bus_ Posting Group", "VAT Prod_ Posting Group", "Dimension Set ID"})
in
#"Removed Columns3"OData
let
Source = OData.Feed("http://192.168.1.6:7748/NORTH/OData/Company('Production')"),
G_L_Entry = Source[G_L_Entry],
#"Changed Type" = Table.TransformColumnTypes(G_L_Entry,{{"Posting_Date", type date}, {"Entry_No", Int64.Type}, {"G_L_Account_No", Int64.Type}, {"Quantity", type number}, {"Amount", Currency.Type}, {"VAT_Amount", Currency.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Entry_No", "Entry No_"}, {"Document_No", "Document No_"}, {"G_L_Account_No", "G_L Account No"}, {"Posting_Date", "Posting Date"}, {"Document_Type", "Document Type"}, {"Gen_Prod_Posting_Group", "Gen_Prod_Posting Group"}, {"Global_Dimension_1_Code", "Global Dimension 1 Code"}, {"Bal_Account_No", "Bal_Account No_"}, {"User_ID", "User ID"}, {"Source_Code", "Source Code"}, {"VAT_Amount", "VAT Amount"}, {"Gen_Posting_Type", "Gen_Posting Type"}, {"Gen_Bus_Posting_Group", "Gen_Bus_Posting Group"}, {"Bal_Account_Type", "Bal_Account Type"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Job_No", "Global_Dimension_2_Code", "IC_Partner_Code", "FA_Entry_Type", "FA_Entry_No", "ETag", "Reversed_by_Entry_No_Link", "Reversed_Entry_No_Link"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Amount", "Amount raw"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns1", "Amount", each [Amount raw]*-1),
#"Renamed Columns2" = Table.RenameColumns(#"Added Custom",{{"Posting Date", "Posting Date Raw"}}),
#"Added Custom1" = Table.AddColumn(#"Renamed Columns2", "Posting Date", each if [Posting Date Raw]>DateTime.Date(DateTime.LocalNow()) then DateTime.Date(DateTime.LocalNow()) else [Posting Date Raw]),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Posting Date", type date}}),
#"Reordered Columns" = Table.ReorderColumns(#"Changed Type2",{"Entry No_", "Posting Date", "Posting Date Raw", "Document Type", "Document No_", "G_L Account No", "G_L_Account_Name", "Description", "Global Dimension 1 Code", "Gen_Posting Type", "Gen_Bus_Posting Group", "Gen_Prod_Posting Group", "Quantity", "Amount", "Amount raw", "Additional_Currency_Amount", "VAT Amount", "Bal_Account Type", "Bal_Account No_", "User ID", "Source Code", "Reason_Code", "Reversed", "Reversed_by_Entry_No", "Reversed_Entry_No"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"Amount", Currency.Type}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"Posting Date Raw", "Amount raw", "Reason_Code", "Reversed", "Reversed_by_Entry_No", "Reversed_Entry_No"})
in
#"Removed Columns1"
