Hi All in this thread (especially thanks to Hkusulja for starting this thread - I have been looking for long time for these discussions).
To do prober and detailed Sales Reporting, I also have the need to exctact all salesInvoiceLines into a Power BI table. I don't want to go the BC Pages webservice way, since in a Danish BC the names changes and I like the new API 2.0 (except that I don't get custom fields without having a BC consultant writing a new custom API :( ).
I have a lot of salesInvoiceLines and this made load times loooong for me, so I tried to do some extensive testing. CONCLUSION: the Business Central connector outperforrmed the oData connector, if you filter wisely. BUT check out my oData expant/select/filter options, which increased performance significantly and took me a lot of time to format correct (I can post my inspiration articles if you like).
Hope you could use my tips until Microsoft makes salesInvoiceLines standard API able to get all lines instead of only one at a time
Option 1: BUSINESS CENTRAL CONNECTOR
- It loaded 92.000 rows in 3:38 minutes
- I hate the idea in the Business Central connector that we cannot filter and select columns in the initial step (it load all data from the table as the Dataverse connector also do)
- Important to filter before you expand - this is crucial to performance!!
let
Source = Dynamics365BusinessCentral.ApiContents(null, null, null),
XXXXX = Source{[Name="XXXXX"]}[Data],
#"YYYYY" = XXXXX{[Name="YYYYY"]}[Data],
v2.0 = #"YYYYY"{[Name="v2.0"]}[Data],
salesInvoices_table = v2.0{[Name="salesInvoices",Signature="table"]}[Data],
#"Renamed Columns" = Table.RenameColumns(salesInvoices_table,{{"id", "id_salesInvoice"}}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns",{"id_salesInvoice", "postingDate", "salesInvoiceLines"}),
#"Filtered Rows2" = Table.SelectRows(#"Removed Other Columns1", each [postingDate] >= #date(2021, 1, 1)),
#"Expanded salesInvoiceLines" = Table.ExpandTableColumn(#"Filtered Rows2", "salesInvoiceLines", {"sequence", "itemId", "lineType", "quantity", "discountAmount", "discountPercent", "amountExcludingTax", "netAmount", "shipmentDate"}, {"sequence", "itemId", "lineType", "quantity", "discountAmount", "discountPercent", "amountExcludingTax", "netAmount", "shipmentDate"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded salesInvoiceLines", each ([lineType] = "Item")),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [quantity] <> 0)
in
#"Filtered Rows1"
Option 2: ODATA CONNECTOR:
- It loaded 92.000 rows in 4:17 minutes after the following optimizations/adjustments
- Using select only worked if I replaced [Implementation="2.0"] with null (otherwise it returned Error for all columns not selected making refreshing data fail)
- My idea was to use select and filter to reduce columns and rows
- Notice that I filter on both the salesInvoice table AND the expanded salesInvoice table
- filtering on the expanded table only gave minor improvements in load time, and I'm not sure that this would always be a good idea
- without 17 minutes!
- Notice I could only make it work by including the $expand in the query (otherwise I got error from BC: 'OLE DB or ODBC error: [DataSource.Error] OData: Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host'
let
Source = OData.Feed("api.businesscentral.dynamics.com/.../XXXXX/api/v2.0" & "/salesInvoices" & "?$select=id,number,postingDate,salesInvoiceLines" & "&$filter=postingDate ge 2021-01-01T00:00:00Z" & "&$expand=salesInvoiceLines($select=itemId,lineType,quantity,discountAmount,discountPercent,amountExcludingTax,netAmount,shipmentDate;$filter=lineType eq 'Item' and quantity ne 0)", null, null),
#"Removed Other Columns" = Table.SelectColumns(Source,{"id", "number", "postingDate", "salesInvoiceLines"}),
#"Expanded salesInvoiceLines" = Table.ExpandTableColumn(#"Removed Other Columns", "salesInvoiceLines", {"itemId", "lineType", "quantity", "discountAmount", "discountPercent", "amountExcludingTax", "netAmount", "shipmentDate"}, {"itemId", "lineType", "quantity", "discountAmount", "discountPercent", "amountExcludingTax", "netAmount", "shipmentDate"})
in
#"Expanded salesInvoiceLines"
Share
Report
50
All responses (
Answers (
