Forum Discussion
How do I combine items with different names?
I have a report that shows the sales of customers. Over time the name of the customer has changed however our database retains the name at the time of the order being placed for histroical purposes.
So I have 3 different company names over the last 5 years, however they are all one and the same customer, just different headers.
I have tried simply replacing the values for the different names to the same, however I then get a lot of blank records.
Any other suggestions?
Thanks
Craig
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.)
12 Replies
CraigBlackman You could create a seperate Dimension table that rolls the names up to the parent company, in that respect it might be easier to determine missing companies that may be causing your blanks as well. So every company would be in your dimension table, and some would roll up to a parent and others would roll up to themselves...
Something like:
company Parent
A 1
B 1
C 2
D 3
And how would I do that exactly?
Thanks for you help.
What is your backend, SQL?
Can you show a sample of what your data looks like now?
Ideally, I would try to do this on the database side, is that possible in this case?
Use the Querry Editor to replace the name in the data after it is uploaded. Use the customer account number to pull the numbers. Create a calculated column building on an if statement so that all other customer names are pulled in without change. That customer's name is changed. Then, use that column for building reports displaying name.
I would also suggest looking into type 2 slowly changing dimensions in your data model. Hopefully this leads you in the right direction as an alternative if the other ideas do not work out.
