By using this website, you agree to our Terms of Use (click here)
I just tried this tip on a Power BI Desktop data model that was taking an average of 56 seconds to refresh (I actually tried it three times: 58 seconds, 53 seconds, and 56 seconds).
Using this tip, I was able to get it down to an average 25 seconds to refresh (I also tried it three times: 24 seconds, 25 seconds, and 25 seconds).
The key is not even that it's faster, but that I can use $select and $filter in the OData parameter for more control over what data gets returned by Acumatica.
Here is the code snippet:
let
Source =
Json.Document(
Web.Contents(
"https://mycompany.acumatica.com/odata/production/PBI-ARInvoiceLines",
[RelativePath="?$format=json&" & "$select=DocType,RefNbr,LineNbr"]
)
),
value = Source[value],
#"Converted to Table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"
Just replace the following in the previous code snippet:
1. mycompany with your Acumatica Company URL
2. PBI-ARInvoiceLines with your Generic Inquiry name
3. Stuff after $select= with the columns that you want to return from the Generic Inquiry
4. (Optional) You can add &$filter= for an OData filter clause
For additional $filter parameter ideas, checkout this post:
You can read my original post on this, including the problem that I was having:
Thanks @hazenm for sharing this tip with me
Try as I may I can't get the $filter parameter to work. Whatever I tried just results in no data being returned at all. Have you actually successfully used it?
It looks like you can't filter on calculated fields - at least according to this:
How nuts.
Yeah. Unfortunately you can't use a calculated field. You'd have to create a SQL View as a published customization.
I posted this on the Acumatica forum post as well, but when filtering by date, here's another helpful tip:
When using the $select filter for dates: When I’m entering a static date, I just make sure to use "yyyy-MM-ddTHH:mm:ss" format. But if I want to use a relative date, I’ll use this template:
DateTime.ToText(Date.AddDays(DateTime.LocalNow(),-9),"yyyy-MM-ddTHH:mm:ss")
Replace “-9” with number of days from today you want the relative filter.
I am fairly familiar with OData filters using both v2 and v4, however when I add the ?$filter= to the select statement it ignores the filter. Has anyone had any luck with this. By the way this method is way faster that other methods Ive tired. If I can solve this filter problem I'll use this all the time.
Another elegant way to handle Acumatica OData into reporting tools of your choice is by using DataSelf ETL+ or DataSelf SQL Mirroring. These tools easily replicate (and transform if needed) Acumatica DAC tables into a MS SQL Server database where you can use any reporting tools. The initial data load can take time if some tables are large, but then, ETL+ provides UPSERT delta refresh that UPdates modified rows and inSERT new ones into the target tables. We have clients with tables north of 60M rows where the refresh only takes a minute or so to complete and makes Tableau and Power BI reporting fly. One doesn't need to fuss with OData URL filters - it's all encapsulated by ETL+ no-code, low-code, full-code features — more time to analyze data and less fussing with data pipeline headaches and slowness. Cheers!