Forum Discussion
Moving Customer Name from Fact Table to Customer Dimension Table in Power BI Star Schema
Hello,
I'm currently working on a Power BI project with a star schema database, and I need some assistance with a specific task. In my fact table, I have both Customer ID and Customer Name among other fields. However, I want to optimize my data model and move the Customer Name information from the fact table to the customer dimension table.
Here's the scenario:
Fact Table:
- Contains fields such as Sale ID, Product ID, Date, Customer ID, Customer Name, and more.
Customer Dimension Table:
- Contains fields like Customer ID, Customer Region, and Customer Age.
I want to remove the Customer Name from the Fact Table and have it exclusively in the Customer Dimension Table to create a more efficient and normalized data model.
I'm seeking advice on the best practices and steps to accomplish this in Power BI. How can I efficiently move the Customer Name information from the Fact Table to the Customer Dimension Table while ensuring data integrity and optimizing performance?
Any guidance, tips, or step-by-step instructions from experienced Power BI users or data modeling experts would be greatly appreciated. Please share your insights and suggestions to help me achieve this task successfully.
Thank you in advance for your assistance!
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
3 Replies
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
Hi SofiaTeles ,
You are totally on the right track!
The Customer Name shall be found on the Customer Dimension and not on the FactTable. However, it should not be your responsibility as a Power BI Developer to outsource the attribute from the FactTable to the CustomerDimension. You mentioned that you are using a star schema database, is that your source? If so, ask the DBA or Data Engineer to add the attribute to the CustomerDimension in the source and at the same time remove the CustomerName from the FactTable. That might be quite an easy one as you might already be fetching the data from views in that database.
If - out of some reason - this approach is not doable, you could potentially merge the Customer Table with the FactTable to get the corresponding name for each customer in Power Query. But this is problematic as you will probably end up with duplciates (then you would need to run a distinct on top) and you will get missing data for the customers that did not have any rows in that FactTable.
So, I really would push that challenge upstream.
Hope this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/Many thanks! It's just what I was looking for.
