Increment Refresh Clarifications

[GK600]

Hi,

Hope someone can help. I have read through a lot on incremental refresh but still stuck on many points. I don’t understand tech related stuff on data yet but progressing so apologies for any stupid one below.

I have run Web APIs to come up with 15 Queries (let’s call them “child queries”) from same data source (but different instances). Then I append all of them into 16th query (let’s call it “Parent query”) which I load and use for data analysis/visualisation. See my questions below.

• Can I apply filter (RangeStart & RangeEnd) on any Date column if data source doesn’t support API call for a Date Range (to that Date column field)
• Do I apply such filter on all child queries and Parent queries (or only Parent Query). I only applied to Parent one for now.
• The date fields are coming up as string. Is it OK to convert it into Date/Time data type first before applying parameter filters? I found conflicting info on this.
• 
• “View native query” shows as greyed out for Queries. Does that mean it doesn’t support query folding? Is it still worth trying for increment refresh?
• 
• After unticking “enable load” to child queries and loading parent query, I configured increment refresh. What does "Store rows where column "Date" is in the last:" 5 year mean? I assume it covers 25th Aug’2015 to 25th of Aug 2020 if I configure it today (and NOT full calendar years from 2014 to 2019)?
• 
• Now, after configuring I am not able to build report using recent data because I picked RangeEnd date parameter as 1st Jul’20 so I am missing almost 2 months’ worth of data. Is it OK to go back to Power Query and remove that RangeSTart and RangeEnd filter now?
• Do I have to first publish it to Power BI Service in order to let increment refresh work. I haven’t built my reports yet so does it make sense to publish it already?
• Most important: when I move back and forth between Power Query editor and Power BI desktop, it connects and make all 15 API calls again while “close and apply” step ,which is taking more than an hour. I am sure I am missing something which is still causing it to connect to web API for full data again and again ☹

Any advise or pointer will be much appreciated.

Thanks and regards,

Gaurav

[edhans]

Incremental Refresh requires query folding. Web APIs do not count. You would need an ODATA feed, or a true database like CDS, SQL Server, Azure SQL, Oracle, etc. And as you've discovered, if View Native Query is greyed out, you cannot do it. (There are very rare cases where that is not correct - greyed out and it still folds, but those are edge cases with SQL Server/Oracle, etc. and you can use your database profiling tool to see  that even though the PQ UI thinks folding has broken, it is really continuing. This is only for a few command. Retrieving top rows I believe will cause this glitch to manifest)

It requires query folding because the service partitions your data and will generate a different SQL statement for each one - so the current refresh data goes in one partition with one set of data, and the old stuff goes in another partition, then the service will move data between those as they age out of the "current" dataset.

[edhans]

Hi @GK600 do you have your answer? If not, post back and we'll try to help. If you do, can you mark the answer as such so we know this thread is done and other's searching will see the answer as well?

[GK600]

Hi @edhans ,

Apologies for delay in repsonse.

We dont have sql server etc. available at this time. Ideally, I need to be using API but thats taking huge time for simple transformations. It connects to API everytime I need to look at lets say values in a column. 

So, I started to explore importing and loading excel files (20-25 of them) which I will need to maintain manually. I checked at a high level and load time is about 5-10 minutes for 80% of data I could get easily so far. The files will reside on onedrive in that case. But sadly, I will need to maintain reporting manually every month and not real-time data. But I think thats best as of now.

What do you think? Do you suggest an alternative or think that's the right thing to do at this stage.

Thanks once again.

regards,

Gaurav 

[edhans]

That certianly doesn't sound ideal @GK600 but sometimes you have to go with what works to get a project moving. I would still try to figure out if there is a better way to get the data from the source. If the API doesn't work, could CSV files be dumped, or even get access to the source server behind the API. Don't know if you are accessing corporate data serving up the web API, or a 3rd party app, in which case they will not give you that kind of access.

[GK600]

Thanks for your response @edhans ,

Its a corporate data but I doubt if supplier may give access to data source server. I will check though. Not sure how will I use that though but may figure out if needed.

The data cant be pulled into csv yet (I am pushing for that to be done as I believe the size of files will be much shorter). Currently, thats available only in excel.

regards,

Gaurav

[edhans]

Do the Excel export then. That is same as CSV from Power Query's perspective on performance.

But ideally if you can get the data directly, that will be much better. You'll have to install a gateway most likely for the service to refresh, so it may be a bit of a hard sell initially if your IT department hasn't embraced Power BI/Power Platform and understand how data access works through the cloud.

[GK600]

Thansk @edhans  again.

As I suspected, supplier cant provide data in any other way. IT team doesnt have even time to look at any such requests as of now.

In order to proceed, I will have to follow manual process of importing data in excel or CSV then. I thought CSVs are lighter. Anyway, I at least know what to do now.

Thanks again for your time to respond.

Gaurav

[edhans · Accepted solution]

Yeah, sorry @GK600 - I understand. That is how it goes with self service projects. If you can get the system to export in to Excel format or CSV format, you can get things working, but there will be no incremental refresh on those either, the point of your original post.

If you have any problems going forward, start a new thread and we will jump in and help. In the mean time, please mark one or more of these posts as the solution so this thread can be marked as solved - even though not solved in the way you were hoping. 😉

[GK600]

Thansk for your response. Appreciated.

I will check about query folding from API supplier just in case. I may also need to explore other database etc. which I dont have much visibility on. Is it usually available wthin organizations? I will be checking with IT team and if they feel comfortable about providing access. They last told me that we have E5 license. Not sure if that grants some sort of database access.

By the way, is there a way I can smoothen the process with POwer BI as of now to transform and build reports. It loads everytime I click close and apply. That takes about 1.5 hours. I thought that the data is already pulled and there must be a way to store differently to not allow PQ to connect to API every time I make small transformation within it.

Any pointer will be appreciated.

Gaurav

[v-yingjl]

Hi @GK600 ,

If you want to create and connect databaselike Azure SQL etc, you need access/subscription from your organazation; if you want to connect to sql server, you can try an instance from here.

"is there a way I can smoothen the process with POwer BI as of now to transform and build reports"

If the connection mode is import or direct query, it is inevitable to use power query to transform data because it needs to create a data model in power bi; if the connection mode in Connect Live, although it will not use power query, it will have many limitations under this mode. You can refer to this document and choose the connection mode according to actual needs

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.