Forum Discussion

SofiaTeles's avatar
SofiaTeles
Frequent Visitor
2 years ago
Solved

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 oth...
Need Help
  • BA_Pete's avatar
    BA_Pete
    2 years ago

    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

     

tackytechtom's avatar
tackytechtom
Most Valuable Professional
2 years ago

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/