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
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).
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.)
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.
Anonymous, greggyb
I have over thought this to an extent as there is already a customer dimension which I have now added a relationship too and that has sorted out my problem.
Thanks you all for your input, really really appreciated.