Forum Discussion
How do I combine items with different names?
If you don't care about the name at time-of-sale, but only want to plot current name, then there's no need to deal with a type 2 SCD. Type 1 would be sufficient (how you track in the source is irrelevant to how you handle in the analysis and presentation layers).
If you can utilize [CustomerID] for all of the relationships in your model that have to do with customers, you can just store a single value for [CustomerName] in your customer dimension.
You can utilize SQL or Power Query to find the most recent name for a given [CustomerID]. If it's not important to preserve a specific, most recent name, you could even take a shortcut by using the 'Remove Duplicates' transformation in Power Query - this would non-deterministically remove all but one [CustomerName] associated with each [CustomerID]. It may even be deterministic with an appropriate sort order (unsure how the transformation is implemented in PQ).
CraigBlackman Since you have the account_id, I'd just create a mapping table which will be your dimension. Each account_id and the company name like greggyb suggests.
You could easily create a process after the initial table creation to check / add new companies going forward. (based on the assumption that the initial company name would stay going forward.)
Anonymous,
It is indeed a SQL backend
What would you like to know in terms of how it looks?
CraigBlackman I would recommend doing one of two things.
1) Depending on how your data is set up, and if you need to see the different company names over time - then you would want to look at slowly changing dimensions as @GTR suggests. That assumes alot about your backend though.
2) The reason I asked for a sample, was I don't know how the data is set up in the db. Do you have a relationship between the Company Name you want to use and the other company names? ie. Can you roll up the sales orders to the overall company? Or is this a case where the Company Name is just "known" on the business side?
Depending on the answer, there are a number of ways to accomplish this in a more re-usable way then doing it on the front end in Power BI.
Create a mapping table that updates via a process and use that as a dimension (AccountiD, CompanyName | DiffCompName)
Create a view that rolls up sales to the Company Name (if there is a relationship)
I would aim for a solution that allows you to do quick adds, rather than re-do the entire process and have to manually reload data.
Anonymous
All customer have a unique id, so albeit the actual name has changed, this is always retained. So there are a number of relationships we can employee.
If you don't care about the name at time-of-sale, but only want to plot current name, then there's no need to deal with a type 2 SCD. Type 1 would be sufficient (how you track in the source is irrelevant to how you handle in the analysis and presentation layers).
If you can utilize [CustomerID] for all of the relationships in your model that have to do with customers, you can just store a single value for [CustomerName] in your customer dimension.
You can utilize SQL or Power Query to find the most recent name for a given [CustomerID]. If it's not important to preserve a specific, most recent name, you could even take a shortcut by using the 'Remove Duplicates' transformation in Power Query - this would non-deterministically remove all but one [CustomerName] associated with each [CustomerID]. It may even be deterministic with an appropriate sort order (unsure how the transformation is implemented in PQ).
Simply I just want to roll up and use only 1 customer name per customer_id. A simple find and replace against the customer name in each order header is not sufficient as it leaves blanks.