Incremental Refresh Question & SQL Views

[varelapablo90]

Hello community

I have some questions on Incremental Refresh that honestly after going through the documentation, questions already published here & youtube channels I still dont get exactly the answer behind.

I've build a report for my organization to see the current status of an Invoice using incremental refresh, thefore I've loaded a lot of data (millions of rows) for for the main table with most information on invoice details, and some other dimension tales with all my suppliers, and payment information, etc.

Question 1

For the invoice details the source is a SQL view (not a table) I made querying data from multiple table (invoice,goods, payments) therefore here my understanding here is that incremental refresh does not work with SQL Views, can someone confirm? is there a workaround to make it work?

Question 2

For some of the dimension table that contains also a lot of rows (some million suppliers) I've appended a row to each of them so I could have a "XXXX - No Selection" selected for the very first time.

Example of how I did this:

Table A -> SQL source with incremental refresh

Table B -> Manual input data in Power Query

Final Table -> Dax union of Table A & B (no incremental refresh)

The question here, is the incremental refresh on my Table A  working?

The supplier information is information that could change at any point in time (adresss, telephone, contact person) so. For it and all the incremental refreshes I am using my "updated_on" column from SQL that contains the date-time the row was modified for the last time.

Question 3 (About incremental refresh in general)

I've set archieve data for the last 10 years because actually I dont want to archve any data from this table.

Incremental refresh last 10 days, this means that we query only the last 10 days from my sql database from my field "updated_on" and compare it to the same rows in PBI and if something changes automatically is updated in PBI (I assume PBI recognizes the KEY columns from SQL, is this understanding correct?)

- Rows that already exist are updated

- Rows that do not exist are append

- What is the behaviour of rows that were deleted? I do not store deleted items in my database

And very last question, in MS documentation, blogs and videos it says you shouldnt use the same column used for incremental refresh in the detect data changes. 

Can someone explain further the background of this? what would happen if I use it?

Should I add 2 columns in my database with the same information to avoid this issue?

Thanks a lot! look forward to hearing from you!

[varelapablo90]

Hello?

[varelapablo90]

Anyone there to support? 🙂