Moving Customer Name from Fact Table to Customer Dimension Table in Power BI Star Schema

Hi SofiaTeles ,

I would create a dynamic dimension table. If your tables are coming from a foldable source this will be fairly low-effort for the server, but will also limit the dimension values to only those you need in the model, optimising end-user performance:

-1- Disable load for your existing customer dimension (let's call it "dimCustomer_old")

-2- Duplicate your fact table, rename it to "dimCustomer".

- Multi-select (Ctrl+click) [Customer ID] and [Customer Name], right-click on one of the selected column headers and choose 'Remove Other Columns'.

- Hit Ctrl+A to select the whole table, then go to the Home tab > Remove Rows > Remove Duplicates.

=> On a foldable source this will all be written into the SQL Native Query, so will take an instant to load.

-3- Merge the other customer columns from dimCustomer_old onto dimCustomer on:

dimCustomer[Customer ID] = dimCustomer_old[Customer ID] to get the additional customer dimension columns that you want.

=> Again, on a foldable source, this merge will be passed back to the server as an SQL join, so lightning fast.

That's it. You now have a dimCustomer table that dynamically includes only the customers that appear in the fact table, so no redundant data.

Pete