Setting up incremental refresh for a dimension table

[PowerRon]

Hi,

my experience with incremental refresh is just daily updating transactions. Most of the time that is just adding transactions to a fact table in the Power BI semantic model. Dimensions are fully refreshed. Biggest dimension is 8 million rows.

Now I have another challenge. We have an existing fact table with 8 billion rows, which is not fully a star scheme. This fact table also contains dimensional data. We want to create to views on top on this fact table: one for the facts, one for the dimension.

In this strongly simplified example I process 5 days (excel attached).

https://www.dropbox.com/scl/fi/gebynmuf2tlu2zlj66cjl/incremental.xlsx?rlkey=0ojlu1quvsxzy2y9gmvy7ouk... 

So we want to create a ‘fact’ view, containing the facts and a relation to the dimension.
So this ‘fact’ view will consist of creation-date, amount, number of transactions, card-code and card-sequence. These last two, card-code and -sequence, are the logical key of the dimension.

The ‘dimension’ view will consist of card-code, card-sequence, card-owner and card-pincode.

I know in Power BI you can only have one column to base a relationship on.
So in both views I will ad a column card-relation, that consists of a concatenation of card-code and sequence.
How does Power BI by the way knows what makes a row in a query unique? Based on the defined relationships in the semantic model?

My questions in these.

Question 1
Must I fully refresh the ‘dimension’ view each time or can I do an incremental refresh on it?

Question 2
Will beneath happen on the subsequent days if I do an incremental refresh on both views?

Question 3
Probably creation-date has to be part of the dimension view as well?

Question 4
So, in the end I have 5 rows in the fact table and two in the dimension, whereby the pincode of jason is 4321?

Hope someone can explain.

Regards
Ron

[PowerRon]

@lbendlin thnx for you answer. I dived a little more in it, things are more clear now.

[lbendlin · Accepted solution]

How does Power BI by the way know what makes a row in a query unique? 

Power BI doesn't know that, and doesn't care.  It will aggregate as much as it can. It is your job to prevent the aggregation by adding the unique columns to the visual and/or filter context.

Q1:  If your Creation Date for the dimension items is immutable then an incremental refresh is possible.

Q2-Q4:  not clear what you are asking.

Q5:  Ideally you should have different Creation Date columns for your facts and your dimensions.