CODEWITHSUNDEEP
powerbipowerbi-desktop
variable
variable

Reputation: 9714

How to perform incremental refresh when a table is converted into star schema using the duplicate table approach?

I have a table (sale) in Power BI 's Power query editor containing saleamt,saledate,productname,saleregion.

I have duplicated this table to create dimension tables. Then I add an index column to the dim table and use merge to bring to being the id value into the fact table.

So finally I have productdim, regiondim, and sale (fact) tables.

How can I configure Incremental refresh in this situation? Since the dim tables are created by duplicating the original sale table I need to apply incremental refresh to dim and fact tables. Is this possible?

Upvotes: 1

Views: 240

Answers (1)

Muppet Bear
Muppet Bear

Reputation: 152

If you are just dim'ing on the values themselves - I would make your dims as DAX-calculated Distinct tables from your fact. This approach works well for moderate size models and reasonable length dim string values because the dictionary overhead in that case will be minimal.

For giant models and/or sizable dim values - You'd want to pre-index via calculated columns in the fact, and then do the distinct-calculated dims on both the index and dim value.

Either way, this offloads creation of DIMS to DAX/Model layer, after PQ handles the incremental refresh.

Upvotes: 0

Related Questions